MobilApp/lib/services/app_database.dart

import 'dart:io';

import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart' as p;
import 'package:terepi_seged/enums/note_type.dart';
import 'package:terepi_seged/models/imported_layer_meta.dart';
import 'package:terepi_seged/models/measured_point.dart';
import 'package:terepi_seged/models/note_item.dart';
import 'package:terepi_seged/models/note_item_audio.dart';
import 'package:terepi_seged/models/note_item_photo.dart';
import 'package:terepi_seged/models/track.dart';
import 'package:uuid/uuid.dart';
import '../models/project.dart';

class AppDatabase {
  AppDatabase._();
  static final instance = AppDatabase._();
  static Database? _db;

  Future<Database> get database async {
    _db ??= await _open();
    return _db!;
  }

  Future<Database> _open() async {
    final oldDb = p.join(await getDatabasesPath(), 'terepi_seged.db');
    if (await File(oldDb).exists()) {
      await File(oldDb).delete();
    }

    final directory = await getExternalStorageDirectory();
    final dbDir = Directory(p.join(directory!.path, 'database'));

    if (!await dbDir.exists()) {
      await dbDir.create(recursive: true);
    }
    final path = p.join(dbDir.path, 'terepi_seged.db');

    return openDatabase(path,
        version: 1, onCreate: _onCreate, onUpgrade: _onUpgrade);
  }

  Future<void> _onCreate(Database db, int _) async {
    // ── Projects ────────────────────────────────────────────────────
    await db.execute('''
      CREATE TABLE IF NOT EXISTS projects (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        uuid        TEXT    NOT NULL UNIQUE,
        name        TEXT    NOT NULL,
        client      TEXT    NOT NULL DEFAULT '',
        description TEXT    NOT NULL DEFAULT '',
        crs         TEXT    NOT NULL DEFAULT 'eov',
        color       TEXT    NOT NULL DEFAULT '#185FA5',
        status      TEXT    NOT NULL DEFAULT 'active',
        is_default  INTEGER NOT NULL DEFAULT 0,
        is_local_only INTEGER    NOT NULL DEFAULT 1,
        last_synced_at TEXT,
        created_at  TEXT    NOT NULL,
        updated_at  TEXT    NOT NULL
      )
    ''');

    // ── Bemért pontok ────────────────────────────────────────────────
    await db.execute('''
      CREATE TABLE IF NOT EXISTS measured_points (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id  INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
        name        TEXT    NOT NULL,
        eov_y       REAL,
        eov_x       REAL,
        eov_z       REAL,
        latitude    REAL,
        longitude   REAL,
        altitude    REAL,
        accuracy    REAL,
        fix_quality INTEGER,
        timestamp   TEXT    NOT NULL,
        note        TEXT    NOT NULL DEFAULT ''
      )
    ''');
    await db
        .execute('CREATE INDEX idx_mp_project ON measured_points(project_id)');

    // ── Track-ek ─────────────────────────────────────────────────────
    await db.execute('''
      CREATE TABLE IF NOT EXISTS tracks (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id    INTEGER REFERENCES projects(id) ON DELETE CASCADE,
        name          TEXT    NOT NULL,
        start_time    TEXT    NOT NULL,
        end_time      TEXT,
        status        TEXT    NOT NULL DEFAULT 'recording',
        source        TEXT    NOT NULL DEFAULT 'Telefon GPS',
        distance_m    REAL    NOT NULL DEFAULT 0,
        point_count   INTEGER NOT NULL DEFAULT 0,
        is_local_only INTEGER NOT NULL DEFAULT 0,
        sync_status   TEXT    NOT NULL DEFAULT 'pending',
        supabase_id   TEXT
      )
    ''');
    await db.execute('CREATE INDEX idx_tracks_project ON tracks(project_id)');

    // ── Track pontok ─────────────────────────────────────────────────
    await db.execute('''
      CREATE TABLE IF NOT EXISTS track_points (
        id        INTEGER PRIMARY KEY AUTOINCREMENT,
        track_id  INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
        latitude  REAL    NOT NULL,
        longitude REAL    NOT NULL,
        altitude  REAL,
        accuracy  REAL,
        speed     REAL,
        heading   REAL,
        timestamp TEXT    NOT NULL
      )
    ''');
    await db.execute(
        'CREATE INDEX idx_tp_track ON track_points(track_id, timestamp)');

    // ── Terepbejárás elemek ──────────────────────────────────────────
    await db.execute('''
      CREATE TABLE IF NOT EXISTS note_items (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id   INTEGER REFERENCES projects(id) ON DELETE CASCADE,
        type         TEXT    NOT NULL,
        points_json  TEXT    NOT NULL,
        color        TEXT    NOT NULL DEFAULT '#185FA5',
        opacity      REAL    NOT NULL DEFAULT 0.5,
        stroke_width REAL    NOT NULL DEFAULT 3.0,
        stroke_color TEXT    NOT NULL DEFAULT '#FFD700',
        label        TEXT    NOT NULL DEFAULT '',
        created_at   TEXT    NOT NULL
      )
    ''');
    await db
        .execute('CREATE INDEX idx_notes_project ON note_items(project_id)');

    await db.execute('''
  CREATE TABLE IF NOT EXISTS note_item_photos (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    note_item_id  INTEGER NOT NULL REFERENCES note_items(id) ON DELETE CASCADE,
    local_path    TEXT    NOT NULL,
    storage_path  TEXT,
    caption       TEXT    NOT NULL DEFAULT '',
    latitude      REAL,
    longitude     REAL,
    created_at    TEXT    NOT NULL
  )
''');
    await db.execute(
        'CREATE INDEX idx_photos_note ON note_item_photos(note_item_id)');

    await db.execute('''
  CREATE TABLE IF NOT EXISTS note_item_audios (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    note_item_id     INTEGER NOT NULL REFERENCES note_items(id) ON DELETE CASCADE,
    local_path       TEXT    NOT NULL,
    caption          TEXT    NOT NULL DEFAULT '',
    duration_seconds INTEGER NOT NULL DEFAULT 0,
    latitude         REAL,
    longitude        REAL,
    created_at       TEXT    NOT NULL
  )
''');
    await db.execute(
        'CREATE INDEX idx_audios_note ON note_item_audios(note_item_id)');

    await db.execute('''
      CREATE TABLE IF NOT EXISTS pending_points (
        id               INTEGER PRIMARY KEY AUTOINCREMENT,
        point_number     INTEGER NOT NULL,
        gnss_number      TEXT,
        latitude         REAL    NOT NULL,
        longitude        REAL    NOT NULL,
        altitude         REAL,
        height_of_geoid  REAL,
        eov_x            REAL,
        eov_y            REAL,
        pole_height      REAL,
        horizontal_error REAL,
        vertical_error   REAL,
        description      TEXT,
        is_deleted       INTEGER NOT NULL DEFAULT 0,
        project_id       INTEGER NOT NULL DEFAULT 2,
        created_at       TEXT    NOT NULL,
        sync_status      TEXT    NOT NULL DEFAULT 'pending'
      )
    ''');
    await db.execute(
      'CREATE INDEX IF NOT EXISTS idx_pp_status '
      'ON pending_points(sync_status)',
    );

    await db.execute('''
  CREATE TABLE IF NOT EXISTS imported_layers (
    id           TEXT    PRIMARY KEY,
    name         TEXT    NOT NULL,
    source_type  TEXT    NOT NULL,
    local_path   TEXT    NOT NULL,
    storage_path TEXT,
    is_visible   INTEGER NOT NULL DEFAULT 1,
    project_id   INTEGER,
    imported_at  TEXT    NOT NULL,
    synced_at    TEXT
  )
''');
    await db.execute(
        'CREATE INDEX idx_imp_layers_project ON imported_layers(project_id)');

    // Alap projekt létrehozása az első indításhoz
    final now = DateTime.now().toIso8601String();
    await db.insert('projects', {
      'uuid': const Uuid().v4(),
      'name': 'Alapértelmezett projekt',
      'is_default': 1,
      'is_local_only': 0,
      'status': 'active',
      'created_at': now,
      'updated_at': now,
    });
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {}

  // ── Projects CRUD ─────────────────────────────────────────────────

  Future<int> insertProject(Project p) async =>
      (await database).insert('projects', p.toMap());

  Future<void> updateProject(Project p) async => (await database)
      .update('projects', p.toMap(), where: 'id = ?', whereArgs: [p.id]);

  Future<void> archiveProject(int id) async => (await database).update(
      'projects',
      {'status': 'archived', 'updated_at': DateTime.now().toIso8601String()},
      where: 'id = ?',
      whereArgs: [id]);

  Future<List<Project>> listProjects({bool includeArchived = false}) async {
    final rows = await (await database).query(
      'projects',
      where: includeArchived ? null : "status = 'active'",
      orderBy: 'updated_at DESC',
    );
    return rows.map(Project.fromMap).toList();
  }

  Future<Project?> getProject(int id) async {
    final rows = await (await database)
        .query('projects', where: 'id = ?', whereArgs: [id], limit: 1);
    return rows.isEmpty ? null : Project.fromMap(rows.first);
  }

  // Projekt statisztikák — a listázáshoz
  Future<Map<String, int>> getProjectStats(int projectId) async {
    final db = await database;
    final points = Sqflite.firstIntValue(await db.rawQuery(
            'SELECT COUNT(*) FROM measured_points WHERE project_id = ?',
            [projectId])) ??
        0;
    final tracks = Sqflite.firstIntValue(await db.rawQuery(
            'SELECT COUNT(*) FROM tracks WHERE project_id = ?', [projectId])) ??
        0;
    final notes = Sqflite.firstIntValue(await db.rawQuery(
            'SELECT COUNT(*) FROM note_items WHERE project_id = ?',
            [projectId])) ??
        0;
    return {'points': points, 'tracks': tracks, 'notes': notes};
  }

  Future<int> insertTrack(Track track) async {
    final db = await database;
    return db.insert('tracks', track.toMap());
  }

  Future<void> updateTrack(Track track) async {
    final db = await database;
    await db.update(
      'tracks',
      track.toMap(),
      where: 'id = ?',
      whereArgs: [track.id],
    );
  }

  Future<void> deleteTrack(int id) async {
    final db = await database;
    await db.delete('tracks', where: 'id = ?', whereArgs: [id]);
  }

  Future<List<Track>> listTracks() async {
    final db = await database;
    final rows = await db.query('tracks', orderBy: 'start_time DESC');
    return rows.map(Track.fromMap).toList();
  }

  Future<Track?> getTrack(int id) async {
    final db = await database;
    final rows = await db.query(
      'tracks',
      where: 'id = ?',
      whereArgs: [id],
      limit: 1,
    );
    return rows.isEmpty ? null : Track.fromMap(rows.first);
  }

  // ═══════════════════════════════════════════════════════════════
  // TRACK POINTS
  // ═══════════════════════════════════════════════════════════════

  Future<void> addPoint(TrackPoint point, double newDistance) async {
    final db = await database;
    try {
      await db.transaction((txn) async {
        await txn.insert('track_points', point.toMap());
        await txn.rawUpdate('''
        UPDATE tracks
        SET distance_m = ?,
            point_count     = point_count + 1
        WHERE id = ?
      ''', [newDistance, point.trackId]);
      });
    } catch (e) {
      print(
          'addPoint hiba: $e  -  trackId=${point.trackId}   dist=$newDistance');
    }
  }

  Future<List<TrackPoint>> getPoints(int trackId) async {
    final db = await database;
    final rows = await db.query(
      'track_points',
      where: 'track_id = ?',
      whereArgs: [trackId],
      orderBy: 'timestamp ASC',
    );
    return rows.map(TrackPoint.fromMap).toList();
  }

  Future<List<({double lat, double lon})>> getLatLons(int trackId) async {
    final db = await database;
    final rows = await db.query(
      'track_points',
      columns: ['latitude', 'longitude'],
      where: 'track_id = ?',
      whereArgs: [trackId],
      orderBy: 'timestamp ASC',
    );
    return rows
        .map((r) => (
              lat: r['latitude'] as double,
              lon: r['longitude'] as double,
            ))
        .toList();
  }

  // ═══════════════════════════════════════════════════════════════
  // PENDING POINTS (szinkron queue)
  // ═══════════════════════════════════════════════════════════════

  Future<int> insertPendingPoint(Map<String, dynamic> point) async {
    final db = await database;
    return db.insert('pending_points', {
      'point_number': point['pointNumber'],
      'gnss_number': point['gnssNumber'],
      'latitude': point['latitude'],
      'longitude': point['longitude'],
      'altitude': point['altitude'],
      'height_of_geoid': point['heightOfGeoid'],
      'eov_x': point['eovX'],
      'eov_y': point['eovY'],
      'pole_height': point['poleHeight'],
      'horizontal_error': point['horizontalError'],
      'vertical_error': point['verticalError'],
      'description': point['description'],
      'is_deleted': (point['isDeleted'] == true) ? 1 : 0,
      'project_id': point['projectId'] ?? 2,
      'created_at': DateTime.now().toIso8601String(),
      'sync_status': 'pending',
    });
  }

  Future<List<Map<String, dynamic>>> getPendingPoints() async {
    final db = await database;
    // JOIN a projects táblával — lokális projektek kiszűrve
    return db.rawQuery('''
    SELECT pp.*
    FROM   pending_points pp
    LEFT JOIN projects pr ON pr.id = pp.project_id
    WHERE  pp.sync_status = 'pending'
      AND  (pr.is_local_only = 0 OR pr.is_local_only IS NULL)
    ORDER BY pp.id ASC
  ''');
  }

  Future<int> getPendingCount() async {
    final db = await database;
    final result = await db.rawQuery(
      "SELECT COUNT(*) AS cnt FROM pending_points "
      "WHERE sync_status = 'pending'",
    );
    return (result.first['cnt'] as int?) ?? 0;
  }

  Future<void> markPointSynced(int id) async {
    final db = await database;
    await db.update(
      'pending_points',
      {'sync_status': 'synced'},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<void> markPointError(int id) async {
    final db = await database;
    await db.update(
      'pending_points',
      {'sync_status': 'error'},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Sikeresen szinkronizált pontok törlése (takarítás)
  Future<void> purgeSyncedPoints() async {
    final db = await database;
    await db.delete(
      'pending_points',
      where: 'sync_status = ?',
      whereArgs: ['synced'],
    );
  }

  // -------------------  Terepbejárás pontok, vonalak, területek

  /// Elem mentése - visszaadja a kapott AQLite id-t
  Future<int> insertNoteItem(NoteItem item) async {
    final db = await database;
    return db.insert('note_items', item.toMap());
  }

  /// Elem frissítése (szín, label, koordináták módosítása után).
  Future<void> updateNoteItem(NoteItem item) async {
    final db = await database;
    await db.update(
      'note_items',
      item.toMap(),
      where: 'id = ?',
      whereArgs: [item.id],
    );
  }

  /// Egy elem törlése.
  Future<void> deleteNoteItem(int id) async {
    final db = await database;
    await db.delete(
      'note_items',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Projekt összes eleme — opcionálisan típus szerint szűrve.
  Future<List<NoteItem>> listNoteItems(int? projectId, {NoteType? type}) async {
    final db = await database;

    String? where;
    List<Object?> whereArgs = [];

    if (projectId != null) {
      where = type != null ? 'project_id = ? AND type = ?' : 'project_id = ?';
      whereArgs = type != null ? [projectId, type.name] : [projectId];
    } else {
      // Projekt nélküli elemek
      where = type != null ? 'type = ?' : null;
      whereArgs = type != null ? [type.name] : [];
    }

    final rows = await db.query(
      'note_items',
      where: where,
      whereArgs: whereArgs,
      orderBy: 'created_at ASC',
    );
    return rows.map(NoteItem.fromMap).toList();
  }

  /// Egyetlen elem lekérése id alapján.
  Future<NoteItem?> getNoteItem(int id) async {
    final db = await database;
    final rows = await db.query(
      'note_items',
      where: 'id = ?',
      whereArgs: [id],
      limit: 1,
    );
    return rows.isEmpty ? null : NoteItem.fromMap(rows.first);
  }

  /// Projekt összes elemének törlése.
  Future<void> deleteAllNoteItems(int projectId) async {
    final db = await database;
    await db.delete(
      'note_items',
      where: 'project_id = ?',
      whereArgs: [projectId],
    );
  }

  // -------- NoteItemPhoto

  Future<int> insertNotePhoto(NoteItemPhoto photo) async {
    final db = await database;
    return db.insert('note_item_photos', photo.toMap());
  }

  Future<void> updateNotePhoto(NoteItemPhoto photo) async {
    final db = await database;
    await db.update(
      'note_item_photos',
      photo.toMap(),
      where: 'id = ?',
      whereArgs: [photo.id],
    );
  }

  Future<void> deleteNotePhoto(int id) async {
    final db = await database;
    await db.delete('note_item_photos', where: 'id = ?', whereArgs: [id]);
  }

  Future<List<NoteItemPhoto>> listNotePhotos(int noteItemId) async {
    final db = await database;
    final rows = await db.query(
      'note_item_photos',
      where: 'note_item_id = ?',
      whereArgs: [noteItemId],
      orderBy: 'created_at ASC',
    );
    return rows.map(NoteItemPhoto.fromMap).toList();
  }

  Future<void> deleteAllNotePhotos(int noteItemId) async {
    final db = await database;
    await db.delete('note_item_photos',
        where: 'note_item_id = ?', whereArgs: [noteItemId]);
  }

  Future<int> countNotePhotos(int noteItemId) async {
    final db = await database;
    final res = await db.rawQuery(
      'SELECT COUNT(*) FROM note_item_photos WHERE note_item_id = ?',
      [noteItemId],
    );
    return Sqflite.firstIntValue(res) ?? 0;
  }
  // --------------   NoteItemAudio

  Future<int> insertNoteAudio(NoteItemAudio audio) async {
    final db = await database;
    return db.insert('note_item_audios', audio.toMap());
  }

  Future<void> updateNoteAudio(NoteItemAudio audio) async {
    final db = await database;
    await db.update('note_item_audios', audio.toMap(),
        where: 'id = ?', whereArgs: [audio.id]);
  }

  Future<void> deleteNoteAudio(int id) async {
    final db = await database;
    await db.delete('note_item_audios', where: 'id = ?', whereArgs: [id]);
  }

  Future<List<NoteItemAudio>> listNoteAudios(int noteItemId) async {
    final db = await database;
    final rows = await db.query(
      'note_item_audios',
      where: 'note_item_id = ?',
      whereArgs: [noteItemId],
      orderBy: 'created_at ASC',
    );
    return rows.map(NoteItemAudio.fromMap).toList();
  }

  Future<int> countNoteAudios(int noteItemId) async {
    final db = await database;
    final res = await db.rawQuery(
      'SELECT COUNT(*) FROM note_item_audios WHERE note_item_id = ?',
      [noteItemId],
    );
    return Sqflite.firstIntValue(res) ?? 0;
  }

  // -----------  Layer meta adatok
  Future<void> insertImportedLayer(ImportedLayerMeta meta) async {
    final db = await database;
    await db.insert('imported_layers', meta.toMap(),
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<void> updateImportedLayer(ImportedLayerMeta meta) async {
    final db = await database;
    await db.update('imported_layers', meta.toMap(),
        where: 'id = ?', whereArgs: [meta.id]);
  }

  Future<void> deleteImportedLayer(String id) async {
    final db = await database;
    await db.delete('imported_layers', where: 'id = ?', whereArgs: [id]);
  }

  Future<List<ImportedLayerMeta>> listImportedLayers({int? projectId}) async {
    final db = await database;
    final rows = await db.query(
      'imported_layers',
      where: projectId != null ? 'project_id = ?' : null,
      whereArgs: projectId != null ? [projectId] : null,
      orderBy: 'imported_at DESC',
    );
    return rows.map(ImportedLayerMeta.fromMap).toList();
  }

  // ------------   Bemért pontok

  Future<int> insertMeasuredPoint(MeasuredPoint point) async {
    final db = await database;
    return db.insert('measured_points', point.toMap());
  }

  Future<void> deleteMeasuredPoint(int id) async {
    final db = await database;
    await db.delete('measured_points', where: 'id = ?', whereArgs: [id]);
  }

  Future<void> deleteAllMeasuredPoints(int projectId) async {
    final db = await database;
    await db.delete('measured_points',
        where: 'project_id = ?', whereArgs: [projectId]);
  }

  Future<List<MeasuredPoint>> listMeasuredPoints(int projectId) async {
    final db = await database;
    final rows = await db.query(
      'measured_points',
      where: 'project_id = ?',
      whereArgs: [projectId],
      orderBy: 'timestamp ASC',
    );
    return rows.map(MeasuredPoint.fromMap).toList();
  }
}