<?php

namespace App\Http\Controllers;

use App\Models\DataJemaat;
use App\Models\DataKl;
use App\Models\DataSektor;
use App\Models\DataWilayah;
use App\Services\DataWargaService;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class LaporanController extends Controller
{
    /**
     * Get comparison data between current year and previous year
     */
    public function comparison(Request $request)
    {
        // Get year from request, default to current year if not provided
        $year = $request->input('year', Carbon::now()->year);
        $previousYear = $year - 1;

        // Get current year data (copy exact structure from Dashboard)
        $currentData = $this->getYearData($year);

        // Get previous year data
        $previousData = $this->getYearData($previousYear);

        // Calculate comparisons
        $comparisons = $this->calculateComparisons($currentData, $previousData);

        return response()->json([
            'status' => true,
            'current_data' => $currentData,
            'previous_data' => $previousData,
            'comparisons' => $comparisons,
            'year' => $year,
            'previous_year' => $previousYear
        ]);
    }

    /**
     * Get data for a specific year using year-suffixed tables
     */
    private function getYearData($year)
    {
        // Determine table names based on year
        $jemaatTable = "data_jemaat_" . $year;
        $keluargaTable = "data_kl_" . $year;
        $sektorTable = "data_sektor";
        $wilayahTable = "data_wilayah";

        // Check if year-suffixed tables exist, fallback to base tables for current year
        $currentYear = Carbon::now()->year;
        if ($year == $currentYear || !DB::select("SHOW TABLES LIKE '$jemaatTable'") || !DB::select("SHOW TABLES LIKE '$keluargaTable'")) {
            $jemaatTable = "data_jemaat";
            $keluargaTable = "data_kl";
        }

        $dataKK = DB::table($keluargaTable)->where("status","1")->count();
        $dataKKAktif = DB::table($keluargaTable)->where("status","1")->where("STATUS_AKTIF","1")->count();
        $dataJemaatAktif = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->count();
        $dataKKTidakAktif = DB::table($keluargaTable)->where("status","1")->where("STATUS_AKTIF","0")->count();
        $dataJemaatTidakAktif = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","0")->count();
        $dataPasangan = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","2")->count();
        $dataAnak = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","3")->count();
        $dataSaudara = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","4")->count();
        $dataOrangTua = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","5")->count();
        $dataMenantu = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","6")->count();
        $dataCucu = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","7")->count();
        $dataLainnya = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","8")->count();
        $dataJemaat = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->count();
        $dataDisabilitas = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("DISABILITAS","1")->count();
        $dataBaptisDewasa = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("PINDAHAN_AGAMA_LAIN","1")->whereYear("TGL_BAPTIS",$year)->count();
        $dataPindahanKeKristen = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("PINDAHAN_AGAMA_LAIN","1")->whereYear("TGL_AGAMA_LAIN",$year)->count();
        $dataPindahanDariKristen = DB::table($jemaatTable)->where("status","4")->where("PINDAHAN_DARI_KRISTEN","1")->whereYear("TGL_DARI_KRISTEN",$year)->count();
        $dataAnakL = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","3")->where("jk","L")->count();
        $dataAnakP = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran","3")->where("jk","P")->count();
        $dataTitipan = DB::table($jemaatTable)->where("status","2")->count();
        $dataJemaatL = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("jk","L")->count();
        $dataJemaatP = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("jk","P")->count();
        $dataKeluarga = DB::table($keluargaTable)->where("status","1")->count();
        $dataSektor = DB::table($sektorTable)->count();
        $dataWilayah = DB::table($wilayahTable)->count();
        $dataUltah = DB::select("SELECT NAMA, CONCAT(DAY(TGL_LAHIR),' ',MONTHNAME(STR_TO_DATE(MONTH(TGL_LAHIR),'%m'))) as lahir, CONCAT(MONTH(TGL_LAHIR),'-',DAY(TGL_LAHIR)) as ordering FROM $jemaatTable WHERE (STATUS != 5 OR STATUS != 6) AND STATUS_AKTIF = 1 AND (STR_TO_DATE( CONCAT(YEAR(CURDATE()), '-', MONTH(TGL_LAHIR), '-', DAY(TGL_LAHIR) ), '%Y-%m-%d' ) between curdate() and curdate() + INTERVAL 1 WEEK) ORDER BY ordering asc");
        $dataJumlahSektor = DB::select("SELECT data_sektor.*, (SELECT COUNT(*) FROM $jemaatTable WHERE $jemaatTable.SEKTOR = data_sektor.id AND $jemaatTable.STATUS = 1 AND $jemaatTable.STATUS_AKTIF = 1) AS TOTAL FROM data_sektor");
        $dataJumlahSektorKosong = DB::select("SELECT COUNT(*) AS sektor_kosong FROM $jemaatTable WHERE ($jemaatTable.SEKTOR IS NULL OR $jemaatTable.SEKTOR = 0) AND $jemaatTable.STATUS = 1 AND $jemaatTable.STATUS_AKTIF = 1")[0]->sektor_kosong;
        $dataJumlahWilayah = DB::select("SELECT data_wilayah.*, (SELECT COUNT(*) FROM $jemaatTable WHERE $jemaatTable.WILAYAH = data_wilayah.id AND $jemaatTable.STATUS = 1 AND $jemaatTable.STATUS_AKTIF = 1) AS TOTAL FROM data_wilayah");
        $dataJumlahWilayahKosong = DB::select("SELECT COUNT(*) AS wilayah_kosong FROM $jemaatTable WHERE ($jemaatTable.WILAYAH IS NULL OR $jemaatTable.WILAYAH = 0) AND $jemaatTable.STATUS = 1 AND $jemaatTable.STATUS_AKTIF = 1")[0]->wilayah_kosong;
        $dataJumlahKeluargaSektor = DB::select("SELECT data_sektor.*, (SELECT COUNT(*) FROM $keluargaTable WHERE $keluargaTable.SEKTOR = data_sektor.id AND $keluargaTable.STATUS = 1) AS TOTAL FROM data_sektor");
        $dataJumlahKeluargaSektorKosong = DB::select("SELECT COUNT(*) AS sektor_kosong FROM $keluargaTable WHERE ($keluargaTable.SEKTOR IS NULL OR $keluargaTable.SEKTOR = 0) AND $keluargaTable.STATUS = 1")[0]->sektor_kosong;
        $dataJumlahKeluargaWilayah = DB::select("SELECT data_wilayah.*, (SELECT COUNT(*) FROM $keluargaTable WHERE $keluargaTable.WILAYAH = data_wilayah.id AND $keluargaTable.STATUS = 1) AS TOTAL FROM data_wilayah");
        $dataJumlahKeluargaWilayahKosong = DB::select("SELECT COUNT(*) AS wilayah_kosong FROM $keluargaTable WHERE ($keluargaTable.WILAYAH IS NULL OR $keluargaTable.WILAYAH = 0) AND $keluargaTable.STATUS = 1")[0]->wilayah_kosong;
        $dataLahirTahunIni = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->whereYear("TGL_LAHIR",$year)->count();
        $dataMeninggalTahunIni = DB::table($jemaatTable)->where("status","5")->whereYear("TANGGAL_MENINGGAL",$year)->count();
        $dataDuda = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("jk","L")->where("STATUS_PERKAWINAN","Duda/Janda")->count();
        $dataJanda = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("jk","P")->where("STATUS_PERKAWINAN","Duda/Janda")->count();
        $dataSendiri = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("peran", "1")->where("STATUS_PERKAWINAN","Belum Kawin")->count();
        $dataKeluargaMasuk = DB::table($keluargaTable)->where("status","1")->where("TANGGAL_MASUK","!=",NULL)->whereYear("TANGGAL_MASUK",$year)->count();
        $dataKeluargaKeluar = DB::table($keluargaTable)->where("TANGGAL_KELUAR","!=", NULL)->whereYear("TANGGAL_KELUAR",$year)->count();
        $dataAttestasiMasuk = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("PINDAHAN","!=",NULL)->where("PINDAHAN","!=","")->where("PINDAHAN","!=","-")->whereYear("TANGGAL_PINDAH",$year)->count();
        $dataAttestasiKeluar = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("PINDAHAN_KE","!=",NULL)->where("PINDAHAN_KE","!=","")->where("PINDAHAN_KE","!=","-")->whereYear("TANGGAL_PINDAH",$year)->count();
        $totala = 0;
        $totalb = 0;

        // Get data for completeness calculation using dynamic table names
        $list = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->get()->toArray();
        $list2 = DB::table($keluargaTable)->where("status","1")->get()->toArray();

        foreach ($list as $a) {
            foreach ((array)$a as $b){
                if($b == "-" || $b == "0" || $b == "" || $b == null){
                    $totalb += 1;
                } else {
                    $totala += 1;
                }
            }
        }
        foreach ($list2 as $a) {
            foreach ((array)$a as $b){
                if($b == "-" || $b == "0" || $b == "" || $b == null){
                    $totalb += 1;
                } else {
                    $totala += 1;
                }
            }
        }
        $total = $totala+$totalb;
        $terisi = round(($totala/$total)*100, 1, \PHP_ROUND_HALF_UP);
        $kosong = round(($totalb/$total)*100, 1, \PHP_ROUND_HALF_UP);
        $totalSidi = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->whereNotNull("TGL_SIDI")->whereYear("TGL_SIDI",$year)->where("TGL_SIDI","!=","0000-00-00")->count();

        $ranges = [ // the start of each age-range.
            '0-5' => 0,
            '6-11' => 6,
            '12-14' => 12,
            '15-17' => 15,
            '18-23' => 18,
            '24-29' => 24,
            '30-59' => 30,
            '60-69' => 60,
            '70+' => 70
        ];

        // Get age data using raw SQL for better performance with dynamic table names
        $umurData = DB::select("
            SELECT
                CASE
                    WHEN TGL_LAHIR IS NULL OR TGL_LAHIR = '' OR TGL_LAHIR = '0000-00-00' THEN 'unknown'
                    ELSE
                        CASE
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 0 AND 5 THEN '0-5'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 6 AND 11 THEN '6-11'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 12 AND 14 THEN '12-14'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 15 AND 17 THEN '15-17'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 18 AND 23 THEN '18-23'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 24 AND 29 THEN '24-29'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 30 AND 59 THEN '30-59'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) BETWEEN 60 AND 69 THEN '60-69'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) >= 70 THEN '70+'
                            ELSE 'unknown'
                        END
                END as age_range,
                COUNT(*) as count
            FROM $jemaatTable
            WHERE status = 1 AND STATUS_AKTIF = 1
            GROUP BY age_range
            ORDER BY age_range
        ");

        $umur = collect($umurData)->pluck('count', 'age_range')->filter(function($value, $key) {
            return $key !== 'unknown';
        });

        // Get classification data using raw SQL
        $klasifikasiData = DB::select("
            SELECT
                CASE
                    WHEN TGL_LAHIR IS NULL OR TGL_LAHIR = '' OR TGL_LAHIR = '0000-00-00' THEN 'unknown'
                    ELSE
                        CASE
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) <= 12 THEN '0-12'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) <= 15 THEN '13-15'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) <= 17 THEN '16-17'
                            WHEN TIMESTAMPDIFF(YEAR, TGL_LAHIR, CURDATE()) > 17 AND (STATUS_PERKAWINAN = 'Belum Kawin' OR STATUS_PERKAWINAN IS NULL) THEN '18+'
                            ELSE 'exclude'
                        END
                END as classification,
                COUNT(*) as count
            FROM $jemaatTable
            WHERE status = 1 AND STATUS_AKTIF = 1
            GROUP BY classification
            HAVING classification NOT IN ('unknown', 'exclude')
            ORDER BY classification
        ");

        $klasifikasi = collect($klasifikasiData)->pluck('count', 'classification');

        $dataKaumBapak = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("JK","L")
            ->where("STATUS_PERKAWINAN","Kawin")
            ->count();

        $dataKaumIbu = DB::table($jemaatTable)->where("status","1")->where("STATUS_AKTIF","1")->where("JK","P")
            ->where("STATUS_PERKAWINAN","Kawin")
            ->count();

        return [
            "dataKK" => $dataKK,
            "dataPasangan" => $dataPasangan,
            "dataAnak" => $dataAnak,
            "dataSaudara" => $dataSaudara,
            "dataOrangTua" => $dataOrangTua,
            "dataMenantu" => $dataMenantu,
            "dataCucu" => $dataCucu,
            "dataLainnya" => $dataLainnya,
            "dataAnakL" => $dataAnakL,
            "dataAnakP" => $dataAnakP,
            "dataJemaat" => $dataJemaat,
            "dataDisabilitas" => $dataDisabilitas,
            "dataTitipan" => $dataTitipan,
            "dataJemaatL" => $dataJemaatL,
            "dataJemaatP" => $dataJemaatP,
            "dataKeluarga" => $dataKeluarga,
            "dataSektor" => $dataSektor,
            "dataWilayah" => $dataWilayah,
            "dataUltah" => $dataUltah,
            "dataJumlahSektor" => $dataJumlahSektor,
            "dataJumlahWilayah" => $dataJumlahWilayah,
            "dataJumlahKeluargaSektor" => $dataJumlahKeluargaSektor,
            "dataJumlahKeluargaWilayah" => $dataJumlahKeluargaWilayah,
            "dataJumlahSektorKosong" => $dataJumlahSektorKosong,
            "dataJumlahWilayahKosong" => $dataJumlahWilayahKosong,
            "dataJumlahKeluargaSektorKosong" => $dataJumlahKeluargaSektorKosong,
            "dataJumlahKeluargaWilayahKosong" => $dataJumlahKeluargaWilayahKosong,
            "dataUmur" => $umur,
            "dataLahirTahunIni" => $dataLahirTahunIni,
            "dataMeninggalTahunIni" => $dataMeninggalTahunIni,
            "dataDuda" => $dataDuda,
            "dataJanda" => $dataJanda,
            "dataSendiri" => $dataSendiri,
            "dataKlasifikasi" => $klasifikasi,
            "dataSidi" => $totalSidi,
            "dataBaptisDewasa" => $dataBaptisDewasa,
            "dataPindahanKeKristen" => $dataPindahanKeKristen,
            "dataPindahanDariKristen" => $dataPindahanDariKristen,
            'dataAttestasiMasuk' => $dataAttestasiMasuk,
            'dataAttestasiKeluar' => $dataAttestasiKeluar,
            "dataKeluargaMasuk" => $dataKeluargaMasuk,
            "dataKeluargaKeluar" => $dataKeluargaKeluar,
            "dataKaumBapak" => $dataKaumBapak,
            "dataKaumIbu" => $dataKaumIbu,
            "terisi" => $terisi,
            "kosong" => $kosong,
        ];
    }

    /**
     * Calculate year-over-year comparisons for all data fields
     */
    private function calculateComparisons($currentData, $previousData)
    {
        $comparisons = [];

        foreach ($currentData as $key => $currentValue) {
            if (!isset($previousData[$key])) {
                continue;
            }

            $previousValue = $previousData[$key];

            // Handle arrays of objects (like dataJumlahSektor, dataJumlahWilayah)
            if (is_array($currentValue) && !empty($currentValue)) {
                $firstItem = $currentValue[0] ?? null;
                if (is_object($firstItem)) {
                    // Handle arrays of stdClass objects - compare by count
                    $currentCount = count($currentValue);
                    $previousCount = is_array($previousValue) ? count($previousValue) : 0;

                    $difference = $currentCount - $previousCount;
                    $percentage = $previousCount > 0 ? round(($difference / $previousCount) * 100, 1) : 0;

                    $comparisons[$key] = [
                        'current' => $currentCount,
                        'previous' => $previousCount,
                        'difference' => $difference,
                        'percentage' => $percentage,
                        'trend' => $difference > 0 ? 'up' : ($difference < 0 ? 'down' : 'same'),
                        'color' => $difference > 0 ? 'success' : ($difference < 0 ? 'danger' : 'secondary')
                    ];
                    continue;
                }
            }

            // Handle associative arrays (like dataUmur, dataKlasifikasi)
            if (is_array($currentValue) && is_array($previousValue)) {
                $currentSubValue = 0;
                $previousSubValue = 0;

                // Sum numeric values from associative arrays
                foreach ($currentValue as $val) {
                    if (is_numeric($val)) {
                        $currentSubValue += $val;
                    }
                }

                foreach ($previousValue as $val) {
                    if (is_numeric($val)) {
                        $previousSubValue += $val;
                    }
                }

                $difference = $currentSubValue - $previousSubValue;
                $percentage = $previousSubValue > 0 ? round(($difference / $previousSubValue) * 100, 1) : 0;

                $comparisons[$key] = [
                    'current' => $currentSubValue,
                    'previous' => $previousSubValue,
                    'difference' => $difference,
                    'percentage' => $percentage,
                    'trend' => $difference > 0 ? 'up' : ($difference < 0 ? 'down' : 'same'),
                    'color' => $difference > 0 ? 'success' : ($difference < 0 ? 'danger' : 'secondary')
                ];
                continue;
            }

            // Handle numeric values
            if (is_numeric($currentValue) && is_numeric($previousValue)) {
                $difference = $currentValue - $previousValue;
                $percentage = $previousValue > 0 ? round(($difference / $previousValue) * 100, 1) : 0;

                $comparisons[$key] = [
                    'current' => $currentValue,
                    'previous' => $previousValue,
                    'difference' => $difference,
                    'percentage' => $percentage,
                    'trend' => $difference > 0 ? 'up' : ($difference < 0 ? 'down' : 'same'),
                    'color' => $difference > 0 ? 'success' : ($difference < 0 ? 'danger' : 'secondary')
                ];
            }
        }

        return $comparisons;
    }

    /**
     * Get available years for comparison
     */
    public function availableYears()
    {
        $currentYear = Carbon::now()->year;
        $years = [];

        // Generate years from current year down to 5 years ago
        for ($i = 0; $i <= 5; $i++) {
            $years[] = $currentYear - $i;
        }

        return response()->json([
            'status' => true,
            'years' => $years
        ]);
    }

    /**
     * Export Data Warga as PDF
     */
    public function exportDataWarga(Request $request)
    {
        $service = new DataWargaService();
        $data = $service->getDataWarga();

        $pdf = Pdf::loadView('pdf.data-warga', $data)
            ->setPaper('a4', 'landscape')
            ->setOption('defaultFont', 'sans-serif')
            ->setOption('isRemoteEnabled', true)
            ->setOption('isHtml5ParserEnabled', true);

        $filename = 'Data_Warga_JKK_' . $data['bulan'] . '_' . $data['tahun'] . '.pdf';

        return $pdf->stream($filename);
    }
}
