<?php
/**
 * ========================================
 * EXCEL IMPORT SCRIPT
 * ========================================
 * Import data from RESUME_KEPATUHAN_PEKANAN Excel file
 * to database with proper error handling
 * ========================================
 */

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../vendor/autoload.php'; // PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

/**
 * Configuration
 */
$config = [
    'excel_file' => __DIR__ . '/../../uploads/RESUME_KEPATUHAN_PEKANAN_2026-2027.xlsx',
    'sheet_name' => 'KEPATUHAN PER SEKOLAH TA-2',
    'start_row' => 8, // Data starts from row 8
    'batch_size' => 50, // Insert in batches for better performance
];

/**
 * Database connection
 */
function getConnection() {
    $host = 'localhost';
    $dbname = 'inventory_acf';
    $username = 'root';
    $password = '';
    
    try {
        $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        return $pdo;
    } catch(PDOException $e) {
        die("Connection failed: " . $e->getMessage());
    }
}

/**
 * Clear existing data (optional - use with caution)
 */
function clearExistingData($pdo, $confirm = false) {
    if (!$confirm) {
        return false;
    }
    
    try {
        $pdo->beginTransaction();
        
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
        $pdo->exec("TRUNCATE TABLE pembayaran_bulanan");
        $pdo->exec("TRUNCATE TABLE pembayaran_tahunan");
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
        
        $pdo->commit();
        echo "✓ Existing data cleared\n";
        return true;
    } catch(PDOException $e) {
        $pdo->rollBack();
        echo "✗ Error clearing data: " . $e->getMessage() . "\n";
        return false;
    }
}

/**
 * Get ID by code/name from lookup tables
 */
function getSekolahId($pdo, $namaSekolah) {
    static $cache = [];
    
    if (isset($cache[$namaSekolah])) {
        return $cache[$namaSekolah];
    }
    
    $stmt = $pdo->prepare("SELECT id FROM sekolah WHERE nama_sekolah = ?");
    $stmt->execute([$namaSekolah]);
    $result = $stmt->fetch();
    
    if ($result) {
        $cache[$namaSekolah] = $result['id'];
        return $result['id'];
    }
    
    return null;
}

function getTahunAjaranId($pdo, $tahunAjaran) {
    static $cache = [];
    
    if (isset($cache[$tahunAjaran])) {
        return $cache[$tahunAjaran];
    }
    
    $stmt = $pdo->prepare("SELECT id FROM tahun_ajaran WHERE tahun_ajaran = ?");
    $stmt->execute([$tahunAjaran]);
    $result = $stmt->fetch();
    
    if ($result) {
        $cache[$tahunAjaran] = $result['id'];
        return $result['id'];
    }
    
    return null;
}

function getJenisPembayaranId($pdo, $kodePembayaran) {
    static $cache = [];
    
    if (isset($cache[$kodePembayaran])) {
        return $cache[$kodePembayaran];
    }
    
    $stmt = $pdo->prepare("SELECT id FROM jenis_pembayaran WHERE kode_pembayaran = ?");
    $stmt->execute([$kodePembayaran]);
    $result = $stmt->fetch();
    
    if ($result) {
        $cache[$kodePembayaran] = $result['id'];
        return $result['id'];
    }
    
    return null;
}

function getKategoriSiswaId($pdo, $kodeKategori) {
    static $cache = [];
    
    if (isset($cache[$kodeKategori])) {
        return $cache[$kodeKategori];
    }
    
    $stmt = $pdo->prepare("SELECT id FROM kategori_siswa WHERE kode_kategori = ?");
    $stmt->execute([$kodeKategori]);
    $result = $stmt->fetch();
    
    if ($result) {
        $cache[$kodeKategori] = $result['id'];
        return $result['id'];
    }
    
    return null;
}

/**
 * Column mapping for different TAs in Excel
 */
function getColumnMapping() {
    return [
        '2022-2023' => ['target' => 'D', 'capaian' => 'E', 'piutang' => 'F', 'persen' => 'G', 'kategori' => 'H'],
        '2023-2024' => ['target' => 'L', 'capaian' => 'M', 'piutang' => 'N', 'persen' => 'O', 'kategori' => 'P'],
        '2024-2025' => ['target' => 'T', 'capaian' => 'U', 'piutang' => 'V', 'persen' => 'W', 'kategori' => 'X'],
        '2025-2026' => ['target' => 'AB', 'capaian' => 'AC', 'piutang' => 'AD', 'persen' => 'AE', 'kategori' => 'AF'],
        '2026-2027' => ['target' => 'AJ', 'capaian' => 'AK', 'piutang' => 'AL', 'persen' => 'AM', 'kategori' => 'AN'],
        '2027-2028' => ['target' => 'AR', 'capaian' => 'AS', 'piutang' => 'AT', 'persen' => 'AU', 'kategori' => 'AV'],
        '2019-2020' => ['target' => 'AZ', 'capaian' => 'BA', 'piutang' => 'BB', 'persen' => 'BC', 'kategori' => 'BD'],
        '2020-2021' => ['target' => 'BH', 'capaian' => 'BI', 'piutang' => 'BJ', 'persen' => 'BK', 'kategori' => 'BL'],
        '2021-2022' => ['target' => 'BP', 'capaian' => 'BQ', 'piutang' => 'BR', 'persen' => 'BS', 'kategori' => 'BT'],
        '2019-2025' => ['target' => 'BX', 'capaian' => 'BY', 'piutang' => 'BZ', 'persen' => 'CA', 'kategori' => 'CB'],
    ];
}

/**
 * Import data from Excel to database
 */
function importExcelData($pdo, $excelFile, $sheetName, $startRow) {
    echo "========================================\n";
    echo "STARTING IMPORT PROCESS\n";
    echo "========================================\n";
    
    // Load Excel file
    try {
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($excelFile);
        $worksheet = $spreadsheet->getSheetByName($sheetName);
        
        if (!$worksheet) {
            throw new Exception("Sheet '$sheetName' not found");
        }
        
        echo "✓ Excel file loaded successfully\n";
        echo "✓ Sheet: $sheetName\n\n";
    } catch(Exception $e) {
        die("✗ Error loading Excel: " . $e->getMessage() . "\n");
    }
    
    // Get kategori siswa from row 2, column D
    $kategoriSiswaText = $worksheet->getCell('D2')->getValue();
    $kodeKategori = 'UMUM'; // Default
    
    if (stripos($kategoriSiswaText, 'B&U') !== false) {
        $kodeKategori = 'BU';
    } elseif (stripos($kategoriSiswaText, 'UMUM') !== false) {
        $kodeKategori = 'UMUM';
    }
    
    $kategoriSiswaId = getKategoriSiswaId($pdo, $kodeKategori);
    
    if (!$kategoriSiswaId) {
        die("✗ Kategori siswa '$kodeKategori' not found in database\n");
    }
    
    echo "✓ Kategori Siswa: $kodeKategori (ID: $kategoriSiswaId)\n";
    
    // Get jenis pembayaran (SPP from row 5)
    $jenisPembayaran = 'SPP'; // Default from Excel
    $jenisPembayaranId = getJenisPembayaranId($pdo, $jenisPembayaran);
    
    if (!$jenisPembayaranId) {
        die("✗ Jenis pembayaran '$jenisPembayaran' not found in database\n");
    }
    
    echo "✓ Jenis Pembayaran: $jenisPembayaran (ID: $jenisPembayaranId)\n\n";
    
    // Get column mapping
    $columnMapping = getColumnMapping();
    
    // Process data
    $pdo->beginTransaction();
    
    try {
        $totalProcessed = 0;
        $totalInserted = 0;
        $totalErrors = 0;
        
        $highestRow = $worksheet->getHighestRow();
        
        echo "Processing rows $startRow to $highestRow...\n\n";
        
        for ($row = $startRow; $row <= $highestRow; $row++) {
            // Get school name from column C
            $namaSekolah = trim($worksheet->getCell("C$row")->getValue());
            
            // Skip if empty school name
            if (empty($namaSekolah)) {
                continue;
            }
            
            $totalProcessed++;
            
            $sekolahId = getSekolahId($pdo, $namaSekolah);
            
            if (!$sekolahId) {
                echo "  ⚠ Skipping row $row: School '$namaSekolah' not found\n";
                $totalErrors++;
                continue;
            }
            
            echo "  Processing: $namaSekolah (Row $row)\n";
            
            // Process each TA
            foreach ($columnMapping as $tahunAjaran => $columns) {
                $tahunAjaranId = getTahunAjaranId($pdo, $tahunAjaran);
                
                if (!$tahunAjaranId) {
                    echo "    ⚠ TA '$tahunAjaran' not found, skipping...\n";
                    continue;
                }
                
                // Get values from Excel
                $target = $worksheet->getCell($columns['target'] . $row)->getValue();
                $capaian = $worksheet->getCell($columns['capaian'] . $row)->getValue();
                
                // Skip if no data
                if ($target === null && $capaian === null) {
                    continue;
                }
                
                // Convert to decimal (handle NULL)
                $target = floatval($target ?? 0);
                $capaian = floatval($capaian ?? 0);
                
                // Insert or update pembayaran_tahunan
                $stmt = $pdo->prepare("
                    INSERT INTO pembayaran_tahunan 
                    (sekolah_id, tahun_ajaran_id, jenis_pembayaran_id, kategori_siswa_id, 
                     target_tahunan, capaian_tahunan, bulan_data)
                    VALUES (?, ?, ?, ?, ?, ?, 'JUNI')
                    ON DUPLICATE KEY UPDATE 
                    target_tahunan = VALUES(target_tahunan),
                    capaian_tahunan = VALUES(capaian_tahunan),
                    bulan_data = VALUES(bulan_data),
                    updated_at = CURRENT_TIMESTAMP
                ");
                
                $stmt->execute([
                    $sekolahId,
                    $tahunAjaranId,
                    $jenisPembayaranId,
                    $kategoriSiswaId,
                    $target,
                    $capaian
                ]);
                
                $totalInserted++;
            }
        }
        
        $pdo->commit();
        
        echo "\n========================================\n";
        echo "IMPORT COMPLETED SUCCESSFULLY\n";
        echo "========================================\n";
        echo "Total Rows Processed: $totalProcessed\n";
        echo "Total Records Inserted/Updated: $totalInserted\n";
        echo "Total Errors: $totalErrors\n";
        echo "========================================\n";
        
        return true;
        
    } catch(Exception $e) {
        $pdo->rollBack();
        echo "\n✗ ERROR: " . $e->getMessage() . "\n";
        echo "Transaction rolled back\n";
        return false;
    }
}

/**
 * Main execution
 */
try {
    // Get database connection
    $pdo = getConnection();
    echo "✓ Database connected\n\n";
    
    // Optional: Clear existing data (set to true to enable)
    // clearExistingData($pdo, false);
    
    // Import data
    $success = importExcelData(
        $pdo,
        $config['excel_file'],
        $config['sheet_name'],
        $config['start_row']
    );
    
    if ($success) {
        echo "\n✓ Import process finished successfully!\n";
        
        // Display sample data
        echo "\nSample Data (First 5 records):\n";
        echo "========================================\n";
        
        $stmt = $pdo->query("
            SELECT 
                s.nama_sekolah,
                ta.tahun_ajaran,
                pt.target_tahunan,
                pt.capaian_tahunan,
                pt.persentase_capaian,
                pt.kategori_kepatuhan
            FROM pembayaran_tahunan pt
            JOIN sekolah s ON pt.sekolah_id = s.id
            JOIN tahun_ajaran ta ON pt.tahun_ajaran_id = ta.id
            ORDER BY s.nama_sekolah, ta.tahun_ajaran
            LIMIT 5
        ");
        
        while ($row = $stmt->fetch()) {
            printf(
                "%s | %s | Target: Rp %s | Capaian: Rp %s | %s%% (%s)\n",
                $row['nama_sekolah'],
                $row['tahun_ajaran'],
                number_format($row['target_tahunan'], 0, ',', '.'),
                number_format($row['capaian_tahunan'], 0, ',', '.'),
                number_format($row['persentase_capaian'], 2),
                $row['kategori_kepatuhan']
            );
        }
    }
    
} catch(Exception $e) {
    echo "✗ Fatal error: " . $e->getMessage() . "\n";
    exit(1);
}