import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import '../models/trip.dart';
import '../models/photo.dart';
import '../models/place.dart';
import '../models/participant.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('live_vacation_memories.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
      onConfigure: _onConfigure,
      onOpen: _onOpen,
    );
  }

  Future _onConfigure(Database db) async {
    await db.execute('PRAGMA foreign_keys = ON');
  }

  Future _onOpen(Database db) async {
    try {
      await db.execute('ALTER TABLE trips ADD COLUMN password TEXT');
    } catch (_) {
      // Column probably already exists
    }
  }

  Future _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE trips (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        startTimeUtc INTEGER,
        finishTimeUtc INTEGER,
        createdAtUtc INTEGER NOT NULL,
        status TEXT NOT NULL,
        ownerParticipantId TEXT NOT NULL,
        shareCode TEXT,
        timezoneId TEXT,
        coverPhotoId TEXT,
        password TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE participants (
        id TEXT PRIMARY KEY,
        tripId TEXT NOT NULL,
        displayName TEXT NOT NULL,
        deviceId TEXT NOT NULL,
        isOwner INTEGER NOT NULL,
        joinedAtUtc INTEGER NOT NULL,
        FOREIGN KEY(tripId) REFERENCES trips(id) ON DELETE CASCADE
      )
    ''');

    await db.execute('''
      CREATE TABLE photos (
        id TEXT PRIMARY KEY,
        tripId TEXT NOT NULL,
        authorParticipantId TEXT NOT NULL,
        contentUri TEXT NOT NULL,
        displayName TEXT NOT NULL,
        mimeType TEXT NOT NULL,
        sizeBytes INTEGER NOT NULL,
        dateTakenUtc INTEGER,
        dateAddedUtc INTEGER,
        latitude REAL,
        longitude REAL,
        altitude REAL,
        width INTEGER,
        height INTEGER,
        orientation INTEGER,
        cameraMake TEXT,
        cameraModel TEXT,
        relativePath TEXT,
        ownerPackageName TEXT,
        sha256 TEXT,
        perceptualHash TEXT,
        isLocalOriginal INTEGER NOT NULL,
        sourceDeviceId TEXT NOT NULL,
        importState TEXT NOT NULL,
        shareState TEXT NOT NULL,
        FOREIGN KEY(tripId) REFERENCES trips(id) ON DELETE CASCADE
      )
    ''');

    await db.execute('''
      CREATE TABLE places (
        id TEXT PRIMARY KEY,
        tripId TEXT NOT NULL,
        dayNumber INTEGER NOT NULL,
        name TEXT,
        latitude REAL NOT NULL,
        longitude REAL NOT NULL,
        arrivalTimeUtc INTEGER,
        departureTimeUtc INTEGER,
        description TEXT,
        FOREIGN KEY(tripId) REFERENCES trips(id) ON DELETE CASCADE
      )
    ''');
  }

  // --- TRIP CRUD ---

  Future<int> insertTrip(Trip trip) async {
    final db = await instance.database;
    return await db.insert('trips', trip.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<Trip?> getTrip(String id) async {
    final db = await instance.database;
    final maps = await db.query('trips', where: 'id = ?', whereArgs: [id]);
    if (maps.isNotEmpty) {
      return Trip.fromMap(maps.first);
    }
    return null;
  }

  Future<List<Trip>> getAllTrips() async {
    final db = await instance.database;
    final result = await db.query('trips', orderBy: 'createdAtUtc DESC');
    return result.map((json) => Trip.fromMap(json)).toList();
  }

  Future<int> updateTrip(Trip trip) async {
    final db = await instance.database;
    return await db.update(
      'trips',
      trip.toMap(),
      where: 'id = ?',
      whereArgs: [trip.id],
    );
  }

  Future<int> deleteTrip(String id) async {
    final db = await instance.database;
    try {
      final photos = await getPhotosForTrip(id);
      for (final photo in photos) {
        final uri = photo.contentUri;
        if (!uri.startsWith('content://') && !uri.startsWith('http://')) {
          final file = File(uri);
          if (await file.exists()) {
            await file.delete();
          }
        }
      }
    } catch (e) {
      // Ignore or log errors
    }
    return await db.delete('trips', where: 'id = ?', whereArgs: [id]);
  }

  // --- PARTICIPANT CRUD ---

  Future<int> insertParticipant(Participant p) async {
    final db = await instance.database;
    return await db.insert('participants', p.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<List<Participant>> getParticipantsForTrip(String tripId) async {
    final db = await instance.database;
    final result = await db.query('participants', where: 'tripId = ?', whereArgs: [tripId]);
    return result.map((json) => Participant.fromMap(json)).toList();
  }

  // --- PHOTO CRUD ---

  Future<int> insertPhoto(Photo photo) async {
    final db = await instance.database;
    return await db.insert('photos', photo.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<List<Photo>> getPhotosForTrip(String tripId) async {
    final db = await instance.database;
    final result = await db.query('photos', where: 'tripId = ?', whereArgs: [tripId], orderBy: 'dateTakenUtc ASC');
    return result.map((json) => Photo.fromMap(json)).toList();
  }

  Future<bool> photoExistsBySha256(String sha256) async {
    final db = await instance.database;
    final result = await db.query('photos', where: 'sha256 = ?', whereArgs: [sha256]);
    return result.isNotEmpty;
  }

  Future<int> deletePhoto(String id) async {
    final db = await instance.database;
    return await db.delete('photos', where: 'id = ?', whereArgs: [id]);
  }

  // --- PLACE CRUD ---

  Future<int> insertPlace(Place place) async {
    final db = await instance.database;
    return await db.insert('places', place.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<List<Place>> getPlacesForTrip(String tripId) async {
    final db = await instance.database;
    final result = await db.query('places', where: 'tripId = ?', whereArgs: [tripId], orderBy: 'dayNumber ASC, arrivalTimeUtc ASC');
    return result.map((json) => Place.fromMap(json)).toList();
  }

  Future<int> deletePlacesForTrip(String tripId) async {
    final db = await instance.database;
    return await db.delete('places', where: 'tripId = ?', whereArgs: [tripId]);
  }
}
