import 'package:get/get.dart';
import 'package:logger/Logger.dart';
import 'package:sigap/src/cores/services/supabase_service.dart';
import 'package:sigap/src/features/map/data/models/models/demographics_model.dart';

class DemographicsRepository extends GetxController {
  static DemographicsRepository get instance => Get.find();

  final _supabase = SupabaseService.instance.client;
  final _log = Get.find<Logger>();

  // Get demographic data by district ID
  Future<List<DemographicModel>> getDemographicsByDistrictId(
    String districtId,
  ) async {
    try {
      final response = await _supabase
          .from('demographics')
          .select('*, districts(*)')
          .eq('district_id', districtId)
          .order('year', ascending: false);

      final data = response as List;
      return data.map((json) => DemographicModel.fromJson(json)).toList();
    } catch (e) {
      _log.e('Error fetching demographic data for district $districtId: $e');
      throw Exception('Failed to load demographic data: $e');
    }
  }

  // Get demographic data for specific year
  Future<List<DemographicModel>> getDemographicsByYear(int year) async {
    try {
      final response = await _supabase
          .from('demographics')
          .select('*, districts(*)')
          .eq('year', year);

      final data = response as List;
      return data.map((json) => DemographicModel.fromJson(json)).toList();
    } catch (e) {
      _log.e('Error fetching demographic data for year $year: $e');
      throw Exception('Failed to load demographic data: $e');
    }
  }

  // Get demographic data by district ID and year
  Future<DemographicModel?> getDemographicByDistrictAndYear(
    String districtId,
    int year,
  ) async {
    try {
      final response =
          await _supabase
              .from('demographics')
              .select('*, districts(*)')
              .eq('district_id', districtId)
              .eq('year', year)
              .maybeSingle();

      if (response == null) return null;
      return DemographicModel.fromJson(response);
    } catch (e) {
      _log.e(
        'Error fetching demographic data for district $districtId and year $year: $e',
      );
      throw Exception('Failed to load demographic data: $e');
    }
  }

  // Get years with available demographic data
  Future<List<int>> getAvailableYears() async {
    try {
      final response =
          await _supabase.from('demographics').select('year');

      final data = response as List;
      return data.map<int>((json) => json['year'] as int).toList()
        ..sort((a, b) => b.compareTo(a)); // Sort descending
    } catch (e) {
      _log.e('Error fetching available demographic years: $e');
      throw Exception('Failed to load available years: $e');
    }
  }

  // Add or update demographic data
  Future<DemographicModel> upsertDemographic({
    String? id,
    required String districtId,
    required int population,
    required int numberOfUnemployed,
    required double populationDensity,
    required int year,
  }) async {
    try {
      final data = {
        if (id != null) 'id': id,
        'district_id': districtId,
        'population': population,
        'number_of_unemployed': numberOfUnemployed,
        'population_density': populationDensity,
        'year': year,
      };

      final response =
          await _supabase
              .from('demographics')
              .upsert(data)
              .select('*, districts(*)')
              .single();

      return DemographicModel.fromJson(response);
    } catch (e) {
      _log.e('Error saving demographic data: $e');
      throw Exception('Failed to save demographic data: $e');
    }
  }

  // Get districts with highest population density
  Future<List<DemographicModel>> getHighestPopulationDensityDistricts(
    int year, {
    int limit = 10,
  }) async {
    try {
      final response = await _supabase
          .from('demographics')
          .select('*, districts(*)')
          .eq('year', year)
          .order('population_density', ascending: false)
          .limit(limit);

      final data = response as List;
      return data.map((json) => DemographicModel.fromJson(json)).toList();
    } catch (e) {
      _log.e('Error fetching highest population density districts: $e');
      throw Exception('Failed to load population density data: $e');
    }
  }

  // Get districts with highest unemployment rate
  Future<List<DemographicModel>> getHighestUnemploymentDistricts(
    int year, {
    int limit = 10,
  }) async {
    try {
      // Calculate unemployment rate as number_of_unemployed/population
      final response = await _supabase.rpc(
        'get_unemployment_rates',
        params: {'selected_year': year, 'limit_count': limit},
      );

      final data = response as List;
      return data.map((json) => DemographicModel.fromJson(json)).toList();
    } catch (e) {
      _log.e('Error fetching highest unemployment districts: $e');
      throw Exception('Failed to load unemployment data: $e');
    }
  }
}
