import 'package:flutter_riverpod/flutter_riverpod.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:rogapp/features/data/checkpoint.dart';
import 'package:rogapp/features/data/user_location.dart';

final dataProviderProvider = Provider<DataProvider>((ref) {
  return DataProvider();
});

// Convert a Feature object to a Map
Map<String, dynamic> featureToMap(Feature feature) {
  var properties = feature.properties;

  try {
    return {
      'id': feature.id,
      'type': feature.type,
      'name': properties.name ?? '',
      'address': properties.address ?? '',
      'phone': properties.phone ?? '',
      'email': properties.email ?? '',
      'webcontents': properties.webcontents ?? '',
      'videos': properties.videos ?? '',
      'category': properties.category ?? '',
      'series': properties.series,
      'lat': feature.geometry.latitude,
      'lon': feature.geometry.longitude,
      'list_order': properties.listOrder,
      'photos': properties.photos ?? '',
      'checkin_radious': properties.checkinRadious,
      'sub_loc_id': properties.subLocId,
      'auto_checkin': properties.autoCheckin! ? 1 : 0,
      'selected': properties.selected == null
          ? 0
          : properties.selected!
              ? 1
              : 0,
      'checkedin': properties.checkedin == null
          ? 0
          : properties.checkedin!
              ? 1
              : 0,
      'cp': properties.cp,
      'checkin_point': properties.checkinPoint,
      'buy_point': properties.buyPoint,
      'hidden_location': properties.hiddenLocation,
      'checkin_image': properties.checkinImage,
      'buypoint_image': properties.buypointImage,
      'forced_checkin': properties.forcedCheckin == null
          ? 0
          : properties.forcedCheckin!
              ? 1
              : 0,
      'tags': properties.tags ?? '',
      'location_id': properties.locationId,
    };
  } catch (e) {
    //print("--- feature to map Error: $e");
    return {};
  }
}

// Convert a Map to a Feature object
Feature mapToFeature(Map<String, dynamic> map) {
  Feature feat;
  //print("mapToFeature id --- ${Properties.fromJson(map)}");
  //print("mapToFeature --- ${map['type']}");
  feat = Feature(
    id: map['id'],
    type: map['type'],
    geometry: Geometry(
      type: map['type'] ?? 'Point',
      coordinates: [
        [map['lon'], map['lat']]
      ],
    ),
    properties: Properties.fromJson(map),
  );

  //print("mapToFeature --- $feat");

  return feat;
}

class DataProvider {
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await initDB();
    return _database!;
  }

  initDB() async {
    var documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "GameDB.db");
    return await openDatabase(path,
        version: 1, onOpen: (db) {}, onCreate: _onCreate);
  }

  _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE Checkpoints (
      location_id INTEGER PRIMARY KEY,
      id INTEGER,
      type TEXT,
      name TEXT,
      address TEXT,
      phone TEXT,
      email TEXT,
      webcontents TEXT,
      videos TEXT,
      category TEXT,
      series INTEGER,
      lat REAL, 
      lon REAL, 
      list_order INTEGER,
      photos TEXT,
      checkin_radious REAL,
      sub_loc_id TEXT,
      auto_checkin INTEGER,
      selected INTEGER,
      checkedin INTEGER,
      cp REAL,
      checkin_point REAL,
      buy_point REAL,
      hidden_location INTEGER,
      checkin_image TEXT,
      buypoint_image TEXT,
      forced_checkin INTEGER,
      recipt_times INTEGER,
      tags TEXT
      )
    ''');
    await db.execute('''
      CREATE TABLE GPSLocations (
        timestamp TEXT PRIMARY KEY,
        latitude REAL,
        longitude REAL,
        event_code TEXT,
        team_name TEXT,
        user_name TEXT,
        attempt_count INTEGER
      )
    ''');
    await db.execute('''
      CREATE TABLE GameState (
        key TEXT PRIMARY KEY,
        value TEXT,
        event_code TEXT,
        team_name TEXT,
        user_name TEXT,
        attempt_count INTEGER
      )
    ''');
    await db.execute('''
      CREATE TABLE CheckpointVisits (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        checkpointId INTEGER,
        photoTaken BOOLEAN,
        receiptPhotoTaken BOOLEAN,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        event_code TEXT,
        team_name TEXT,
        user_name TEXT,
        attempt_count INTEGER DEFAULT 1,
        FOREIGN KEY (checkpointId) REFERENCES Checkpoints(location_id)
      )
    ''');
  }

  Future<void> recordCheckpointVisit(
      int locationId, String userName, String teamName, String eventCode,
      {bool photoTaken = false, bool? receiptPhotoTaken}) async {
    final db = await database;

    // Fetching buy_point to determine if a receipt is required
    var checkpointQueryResult = await db.query(
      'Checkpoints',
      columns: ['buy_point'],
      where: 'location_id = ?',
      whereArgs: [locationId],
    );

    var buyPoint = checkpointQueryResult.isNotEmpty
        ? checkpointQueryResult.first['buy_point'] as double?
        : null;
    var requiresReceipt = buyPoint != null && buyPoint > 0.0;

    await db.insert(
      'CheckpointVisits',
      {
        'checkpointId': locationId,
        'user_name': userName,
        'team_name': teamName,
        'event_code': eventCode,
        'photoTaken': photoTaken ? 1 : 0,
        'receiptPhotoTaken':
            requiresReceipt ? (receiptPhotoTaken == true ? 1 : 0) : null,
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<Map<String, dynamic>> getIncompleteTasks(int locationId,
      String userName, String teamName, String eventCode) async {
    final db = await database;
    var checkpoint = await db.query(
      'Checkpoints',
      where: 'location_id = ?',
      whereArgs: [locationId],
    );

    var buyPointValue =
        checkpoint.isNotEmpty ? checkpoint.first['buy_point'] as double? : null;
    var requiresReceipt = buyPointValue != null && buyPointValue > 0.0;

    // Fetch visits considering all relevant identifiers
    List<Map> visits = await db.query(
      'CheckpointVisits',
      where:
          'checkpointId = ? AND user_name = ? AND team_name = ? AND event_code = ?',
      whereArgs: [locationId, userName, teamName, eventCode],
    );

    if (visits.isNotEmpty) {
      var lastVisit = visits.last;
      return {
        'photoTaken': lastVisit['photoTaken'] == 1,
        'receiptPhotoTaken':
            requiresReceipt ? lastVisit['receiptPhotoTaken'] == 1 : true,
      };
    }

    // Default return values when no visits found
    return {
      'photoTaken': false,
      'receiptPhotoTaken': requiresReceipt ? false : true,
    };
  }

  Future<void> insertMarker(Feature marker) async {
    //print("--- inserting ${featureToMap(marker)} ---");
    final db = await database;
    try {
      await db.insert(
        'Checkpoints',
        featureToMap(marker),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    } catch (e) {
      print("--- ${e.toString()} ---");
    }
  }

  Future<List<Feature>> getMarkers() async {
    final db = await database;
    var res = await db.query('Checkpoints');
    List<Feature> list =
        res.isNotEmpty ? res.map((c) => mapToFeature(c)).toList() : [];
    //print("--- checkpoints ${res} ---");
    return list;
  }

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

  Future<void> insertGPSLocation(UserLocation location) async {
    final db = await database;
    await db.insert(
      'GPSLocations',
      location
          .toMap(), // Implement a method in UserLocation to convert the object to a Map
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<List<UserLocation>> getGPSLocations() async {
    final db = await database;
    var res = await db.query('GPSLocations');
    List<UserLocation> locations =
        res.isNotEmpty ? res.map((c) => UserLocation.fromMap(c)).toList() : [];
    return locations;
  }

  Future<void> updateGameState(
    String key,
    String value,
    String userName,
    String teamName,
    String eventCode,
  ) async {
    final db = await database;
    await db.insert(
      'GameState',
      {
        'key': key,
        'value': value,
        'team_name': teamName,
        'event_code': eventCode,
        'user_name': userName,
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<String?> getGameState(
    String key,
    String userName,
    String teamName,
    String eventCode,
  ) async {
    final db = await database;
    List<Map<String, dynamic>> results = await db.query(
      'GameState',
      where: 'key = ? AND team_name = ? AND event_code = ? AND user_name = ?',
      whereArgs: [key, teamName, eventCode, userName],
    );

    if (results.isNotEmpty) {
      return results.first['value'] as String?;
    }
    return null;
  }
}