<?php

declare(strict_types=1);

/* ================= SESSION ================= */
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

/* ================= OUTPUT CLEAN ================= */
if (ob_get_level()) ob_end_clean();
ob_start();

/* ================= ERROR HANDLING ================= */
error_reporting(E_ALL);
ini_set('display_errors', '0');
ini_set('log_errors', '1');
ini_set('error_log', __DIR__ . '/logs/api_error.log');

/* ================= HEADERS ================= */
header('Content-Type: application/json; charset=utf-8');
header('X-Content-Type-Options: nosniff');

/* ================= TIMEZONE ================= */
date_default_timezone_set('Asia/Jakarta');

/* ================= UPLOAD LIMITS ================= */
ini_set('upload_max_filesize', '10M');
ini_set('post_max_size', '10M');
ini_set('max_execution_time', '300');
ini_set('memory_limit', '256M');
define('MAX_UPLOAD_SIZE', 10 * 1024 * 1024);

/* ================= DB CONFIG ================= */
$config_paths = [
    __DIR__ . '/../../config/connection.php',
    __DIR__ . '/../config/connection.php'
];

foreach ($config_paths as $path) {
    if (file_exists($path)) {
        require_once $path;
        break;
    }
}

if (!defined('DB_HOST')) define('DB_HOST', 'localhost');
if (!defined('DB_USER')) define('DB_USER', 'root');
if (!defined('DB_PASS')) define('DB_PASS', '');
if (!defined('DB_NAME')) define('DB_NAME', 'inventory_acf');

/* ================= COMPOSER AUTOLOAD ================= */
$autoloadPaths = [
    __DIR__ . '/../../vendor/autoload.php',
    __DIR__ . '/../vendor/autoload.php',
    'D:/laragon/www/inventoryACF/vendor/autoload.php'
];

foreach ($autoloadPaths as $path) {
    if (file_exists($path)) {
        require_once $path;
        break;
    }
}

/* ================= AUTH HELPERS ================= */
function isAdmin(): bool {
    if (!isset($_SESSION['role'])) return false;
    $role = strtoupper(trim($_SESSION['role']));
    return in_array($role, ['A', 'ADMIN', 'SUPERADMIN'], true);
}

function requireAdmin(): void {
    if (!isAdmin()) {
        jsonResponse(false, null, 'Akses ditolak', 403);
    }
}

/* ================= RESPONSE HELPER ================= */
function jsonResponse(bool $success, $data = null, string $error = null, int $httpCode = 200): void {
    if (ob_get_level()) ob_clean();
    http_response_code($httpCode);
    
    $response = [
        'success' => $success,
        'timestamp' => date('Y-m-d H:i:s')
    ];
    
    if ($data !== null) $response['data'] = $data;
    if ($error !== null) {
        $response['error'] = $error;
        error_log("API ERROR: $error");
    }
    
    echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
    exit;
}

/* ================= UTILITY HELPERS ================= */
function cleanNum($val): float {
    if (is_numeric($val)) return (float)$val;
    $cleaned = preg_replace('/[^\d.-]/', '', str_replace(',', '', (string)$val));
    return $cleaned !== '' ? (float)$cleaned : 0.0;
}

function persen(float $capaian, float $target): float {
    return $target > 0 ? round(($capaian / $target) * 100, 2) : 0.0;
}

function kategori(float $persen): string {
    if ($persen >= 95) return 'EXCELLENT';
    if ($persen >= 85) return 'GOOD';
    if ($persen >= 70) return 'AVERAGE';
    return 'POOR';
}

function sanitizeString(string $input): string {
    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}

/* ================= DATABASE CLASS ================= */
class Database {
    private mysqli $conn;
    
    public function __construct() {
        $this->conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if ($this->conn->connect_error) {
            throw new Exception('Koneksi database gagal: ' . $this->conn->connect_error);
        }
        $this->conn->set_charset('utf8mb4');
    }
    
    public function getConnection(): mysqli { return $this->conn; }
    public function prepare(string $query): mysqli_stmt|false { return $this->conn->prepare($query); }
    public function query(string $query): mysqli_result|bool { return $this->conn->query($query); }
    public function begin(): void { $this->conn->begin_transaction(); }
    public function commit(): void { $this->conn->commit(); }
    public function rollback(): void { if ($this->conn->ping()) $this->conn->rollback(); }
    public function close(): void { $this->conn->close(); }
}

/* ================= EXCEL PARSER ================= */
function parseExcelKepatuhan(string $filepath): array {
    if (!class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
        throw new Exception('PhpSpreadsheet tidak terinstal');
    }

    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
    $sheet = $spreadsheet->getActiveSheet();
    $dataRows = $sheet->toArray(null, true, true, true);

    $rows = [];
    $detectedPeriod = '';
    
    foreach ($dataRows as $row) {
        $no = trim((string)($row['A'] ?? ''));
        $ta = trim((string)($row['B'] ?? ''));
        $period = trim((string)($row['C'] ?? ''));
        $category = trim((string)($row['D'] ?? ''));
        $month = trim((string)($row['E'] ?? ''));
        $target_raw = $row['F'] ?? 0;
        $capaian_raw = $row['G'] ?? 0;
        $piutang_raw = $row['H'] ?? 0;
        $persen_raw = $row['I'] ?? 0;
        $kategori_raw = trim((string)($row['J'] ?? ''));
        
        // Skip header & empty
        if (strtoupper($no) === 'NO' || strtoupper($ta) === 'TA' || empty($ta) || $ta === '-') {
            continue;
        }
        
        // Stop at TOTAL row
        if (strtoupper($ta) === 'TOTAL') break;
        
        // Clean numeric values
        $target = cleanNum($target_raw);
        $capaian = cleanNum($capaian_raw);
        $piutang = cleanNum($piutang_raw);
        
        // Handle percentage (Excel may store as decimal: 0.90 = 90%)
        if (is_numeric($persen_raw) && $persen_raw < 10) {
            $persen_val = round((float)$persen_raw * 100, 2);
        } else {
            $persen_val = cleanNum(str_replace('%', '', (string)$persen_raw));
        }
        
        // Recalculate if needed
        if ($persen_val == 0 && $target > 0) {
            $persen_val = persen($capaian, $target);
        }
        if ($piutang == 0 && ($target > 0 || $capaian > 0)) {
            $piutang = $target - $capaian;
        }
        
        // Detect period from first valid row
        if (empty($detectedPeriod) && !empty($period)) {
            $detectedPeriod = $period;
        }
        
        // Normalize category (CRITICAL FIX)
        $category = strtoupper(trim($category));
        if (empty($category) || stripos($category, 'PIUTANG') !== false || stripos($category, 'SPP') !== false) {
            $category = 'TOTAL';
        }
        
        // Validate kategori
        $kategori_val = strtoupper(trim($kategori_raw));
        if (empty($kategori_val) || !in_array($kategori_val, ['EXCELLENT', 'GOOD', 'AVERAGE', 'POOR'])) {
            $kategori_val = kategori($persen_val);
        }
        
        // CRITICAL: Skip rows with target=0 AND capaian=0
        if ($target == 0 && $capaian == 0) {
            continue;
        }
        
        $rows[] = [
            'ta' => sanitizeString($ta),
            'period' => sanitizeString($period ?: $detectedPeriod),
            'category' => $category,
            'month' => 'TOTAL', // Normalized
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $persen_val,
            'kategori' => $kategori_val
        ];
    }
    
    if (empty($rows)) {
        throw new Exception('Tidak ada data valid dalam file Excel');
    }
    
    return [
        'period' => $detectedPeriod,
        'category' => 'TOTAL',
        'rows' => $rows,
        'total_rows' => count($rows)
    ];
}

/* ================= ACTION: CHECK DEPENDENCIES ================= */
function action_check_deps(): void {
    $deps = [
        'spreadsheet' => class_exists('PhpOffice\PhpSpreadsheet\IOFactory'),
        'pdf_parser' => class_exists('Smalot\PdfParser\Parser'),
        'pdftotext' => false
    ];
    
    $output = [];
    @exec('pdftotext -v 2>&1', $output, $return_var);
    if ($return_var === 0 || $return_var === 1) {
        $deps['pdftotext'] = true;
    }
    
    jsonResponse(true, $deps);
}

/* ================= ACTION: GET PERIODS ================= */
function action_get_periods(Database $db): void {
    $result = $db->query("
        SELECT DISTINCT TRIM(period) period
        FROM kepatuhan_pembayaran
        WHERE UPPER(TRIM(month)) = 'TOTAL'
        ORDER BY period DESC
    ");
    
    $periods = [];
    while ($row = $result->fetch_assoc()) {
        if (!empty($row['period'])) {
            $periods[] = $row['period'];
        }
    }
    
    jsonResponse(true, $periods);
}

/* ================= ACTION: GET DATA (Aggregate ALL Categories) ================= */
function action_get_data(Database $db): void {
    $period = trim($_GET['period'] ?? '');
    if ($period === '') throw new Exception('Period wajib diisi');

    $stmt = $db->prepare("
        SELECT
            MIN(id) id,
            TRIM(ta) ta,
            SUM(target) target,
            SUM(capaian) capaian
        FROM kepatuhan_pembayaran
        WHERE TRIM(period) = ?
          AND UPPER(TRIM(month)) = 'TOTAL'
        GROUP BY TRIM(ta)
        ORDER BY ta
    ");
    $stmt->bind_param("s", $period);
    $stmt->execute();
    $res = $stmt->get_result();

    $rows = [];
    $summary = [
        'total_target' => 0.0,
        'total_capaian' => 0.0,
        'total_piutang' => 0.0,
        'persen' => 0.0,
        'kategori' => ['EXCELLENT' => 0, 'GOOD' => 0, 'AVERAGE' => 0, 'POOR' => 0]
    ];
    
    while ($r = $res->fetch_assoc()) {
        $target = (float)$r['target'];
        $capaian = (float)$r['capaian'];
        $piutang = $target - $capaian;
        $persen_val = persen($capaian, $target);
        $kategori_val = kategori($persen_val);
        
        $rows[] = [
            'id' => (int)$r['id'],
            'ta' => $r['ta'],
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $persen_val,
            'kategori' => $kategori_val
        ];
        
        $summary['total_target'] += $target;
        $summary['total_capaian'] += $capaian;
        $summary['total_piutang'] += $piutang;
        $summary['kategori'][$kategori_val]++;
    }

    if ($summary['total_target'] > 0) {
        $summary['persen'] = persen($summary['total_capaian'], $summary['total_target']);
    }
    $summary['kategori_overall'] = kategori($summary['persen']);

    jsonResponse(true, [
        'rows' => $rows,
        'summary' => $summary
    ]);
}

/* ================= ACTION: GET DETAIL (Per Category) ================= */
function action_get_detail(Database $db): void {
    $period = trim($_GET['period'] ?? '');
    $cat = strtoupper(trim($_GET['category'] ?? ''));
    
    if ($period === '' || $cat === '') {
        throw new Exception('Parameter period dan category wajib diisi');
    }

    $stmt = $db->prepare("
        SELECT
            MIN(id) id,
            TRIM(ta) ta,
            SUM(target) target,
            SUM(capaian) capaian
        FROM kepatuhan_pembayaran
        WHERE TRIM(period) = ?
          AND UPPER(TRIM(category)) = ?
          AND UPPER(TRIM(month)) = 'TOTAL'
        GROUP BY TRIM(ta)
        ORDER BY ta
    ");
    $stmt->bind_param("ss", $period, $cat);
    $stmt->execute();
    $res = $stmt->get_result();

    $rows = [];
    $summary = [
        'total_target' => 0.0,
        'total_capaian' => 0.0,
        'total_piutang' => 0.0,
        'persen' => 0.0,
        'kategori' => ['EXCELLENT' => 0, 'GOOD' => 0, 'AVERAGE' => 0, 'POOR' => 0]
    ];
    
    while ($r = $res->fetch_assoc()) {
        $target = (float)$r['target'];
        $capaian = (float)$r['capaian'];
        $piutang = $target - $capaian;
        $persen_val = persen($capaian, $target);
        $kategori_val = kategori($persen_val);
        
        $rows[] = [
            'id' => (int)$r['id'],
            'ta' => $r['ta'],
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $persen_val,
            'kategori' => $kategori_val
        ];
        
        $summary['total_target'] += $target;
        $summary['total_capaian'] += $capaian;
        $summary['total_piutang'] += $piutang;
        $summary['kategori'][$kategori_val]++;
    }

    if ($summary['total_target'] > 0) {
        $summary['persen'] = persen($summary['total_capaian'], $summary['total_target']);
    }
    $summary['kategori_overall'] = kategori($summary['persen']);

    jsonResponse(true, [
        'rows' => $rows,
        'summary' => $summary
    ]);
}

/* ================= ACTION: EXPORT CSV ================= */
function action_export(Database $db): void {
    $period = trim($_GET['period'] ?? '');
    if ($period === '') throw new Exception('Period wajib diisi');

    if (ob_get_level()) ob_end_clean();
    
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="Kepatuhan_' . $period . '_' . date('Ymd_His') . '.csv"');

    $out = fopen('php://output', 'w');
    fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM
    
    fputcsv($out, ['KEPATUHAN PEMBAYARAN']);
    fputcsv($out, ['Periode: ' . $period]);
    fputcsv($out, []);
    fputcsv($out, ['NO', 'TA', 'CATEGORY', 'TARGET', 'CAPAIAN', 'PIUTANG', 'PERSEN (%)', 'KATEGORI']);

    $stmt = $db->prepare("
        SELECT TRIM(ta) ta, UPPER(TRIM(category)) category,
               SUM(target) target, SUM(capaian) capaian
        FROM kepatuhan_pembayaran
        WHERE TRIM(period) = ?
          AND UPPER(TRIM(month)) = 'TOTAL'
        GROUP BY ta, category
        ORDER BY category, ta
    ");
    $stmt->bind_param("s", $period);
    $stmt->execute();
    $res = $stmt->get_result();

    $no = 1;
    while ($r = $res->fetch_assoc()) {
        $target = (float)$r['target'];
        $capaian = (float)$r['capaian'];
        $piutang = $target - $capaian;
        $persen_val = persen($capaian, $target);
        
        fputcsv($out, [
            $no++,
            $r['ta'],
            $r['category'],
            number_format($target, 2, '.', ''),
            number_format($capaian, 2, '.', ''),
            number_format($piutang, 2, '.', ''),
            number_format($persen_val, 2, '.', ''),
            kategori($persen_val)
        ]);
    }
    
    fclose($out);
    exit;
}

/* ================= ACTION: IMPORT ================= */
function action_import(Database $db): void {
    requireAdmin();
    
    if (!isset($_FILES['file'])) {
        throw new Exception('File tidak ditemukan');
    }
    
    $file = $_FILES['file'];
    
    if ($file['error'] !== UPLOAD_ERR_OK) {
        throw new Exception('Upload error: ' . $file['error']);
    }
    
    if ($file['size'] > MAX_UPLOAD_SIZE) {
        throw new Exception('File terlalu besar (max 10MB)');
    }
    
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, ['xlsx', 'xls'])) {
        throw new Exception('Format tidak didukung. Gunakan XLSX atau XLS');
    }
    
    // Parse Excel
    try {
        $parsed = parseExcelKepatuhan($file['tmp_name']);
    } catch (Exception $e) {
        throw new Exception('Gagal parsing: ' . $e->getMessage());
    }
    
    if (empty($parsed['period'])) {
        throw new Exception('Period tidak ditemukan dalam file');
    }
    
    // Import to database
    $db->begin();
    
    try {
        $checkStmt = $db->prepare("
            SELECT id FROM kepatuhan_pembayaran 
            WHERE TRIM(UPPER(ta)) = TRIM(UPPER(?)) 
              AND TRIM(period) = TRIM(?) 
              AND TRIM(UPPER(category)) = TRIM(UPPER(?))
              AND UPPER(TRIM(month)) = 'TOTAL'
            LIMIT 1
        ");
        
        $insertStmt = $db->prepare("
            INSERT INTO kepatuhan_pembayaran 
            (ta, period, category, month, target, capaian, piutang, persen, kategori) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $updateStmt = $db->prepare("
            UPDATE kepatuhan_pembayaran 
            SET target = ?, capaian = ?, piutang = ?, persen = ?, kategori = ?, updated_at = NOW()
            WHERE TRIM(UPPER(ta)) = TRIM(UPPER(?)) 
              AND TRIM(period) = TRIM(?) 
              AND TRIM(UPPER(category)) = TRIM(UPPER(?))
              AND UPPER(TRIM(month)) = 'TOTAL'
        ");
        
        $imported = 0;
        $updated = 0;
        $skipped = 0;
        
        foreach ($parsed['rows'] as $row) {
            $ta = $row['ta'];
            $period = $row['period'];
            $category = $row['category'];
            $month = $row['month'];
            $target = $row['target'];
            $capaian = $row['capaian'];
            $piutang = $row['piutang'];
            $persen_val = $row['persen'];
            $kategori_val = $row['kategori'];
            
            // Check if exists
            $checkStmt->bind_param("sss", $ta, $period, $category);
            $checkStmt->execute();
            $exists = $checkStmt->get_result()->num_rows > 0;
            
            if ($exists) {
                // Update existing
                $updateStmt->bind_param(
                    "ddddssss",
                    $target, $capaian, $piutang, $persen_val, $kategori_val,
                    $ta, $period, $category
                );
                if ($updateStmt->execute() && $updateStmt->affected_rows > 0) {
                    $updated++;
                } else {
                    $skipped++;
                }
            } else {
                // Insert new
                $insertStmt->bind_param(
                    "ssssdddds",
                    $ta, $period, $category, $month,
                    $target, $capaian, $piutang, $persen_val, $kategori_val
                );
                if ($insertStmt->execute()) {
                    $imported++;
                }
            }
        }
        
        $db->commit();
        
        $message = $imported > 0 
            ? "✅ Import berhasil! $imported data baru ditambahkan untuk periode {$parsed['period']}" 
            : ($updated > 0 
                ? "⚠️ Import selesai: $updated data diperbarui" 
                : "ℹ️ Tidak ada data baru untuk diimport");
        
        jsonResponse(true, [
            'message' => $message,
            'imported' => $imported,
            'updated' => $updated,
            'skipped' => $skipped,
            'period' => $parsed['period'],
            'total_rows' => $parsed['total_rows']
        ]);
        
    } catch (Exception $e) {
        $db->rollback();
        throw $e;
    }
}

/* ================= ACTION: CREATE ================= */
function action_create(Database $db): void {
    requireAdmin();
    
    $input = json_decode(file_get_contents('php://input'), true);
    
    $ta = trim($input['ta'] ?? '');
    $period = trim($input['period'] ?? '');
    $category = strtoupper(trim($input['category'] ?? 'TOTAL'));
    $month = strtoupper(trim($input['month'] ?? 'TOTAL'));
    $target = cleanNum($input['target'] ?? 0);
    $capaian = cleanNum($input['capaian'] ?? 0);
    
    if (empty($ta) || empty($period)) {
        throw new Exception("TA dan Period wajib diisi");
    }
    
    $piutang = $target - $capaian;
    $persen_val = persen($capaian, $target);
    $kategori_val = kategori($persen_val);
    
    $stmt = $db->prepare("
        INSERT INTO kepatuhan_pembayaran 
        (ta, period, category, month, target, capaian, piutang, persen, kategori) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        "ssssdddds", 
        $ta, $period, $category, $month, 
        $target, $capaian, $piutang, $persen_val, $kategori_val
    );
    $stmt->execute();
    
    jsonResponse(true, [
        'message' => 'Data berhasil ditambahkan',
        'id' => $db->getConnection()->insert_id
    ]);
}

/* ================= ACTION: UPDATE ================= */
function action_update(Database $db): void {
    requireAdmin();
    
    $input = json_decode(file_get_contents('php://input'), true);
    
    $id = (int)($input['id'] ?? 0);
    $ta = trim($input['ta'] ?? '');
    $period = trim($input['period'] ?? '');
    $category = strtoupper(trim($input['category'] ?? 'TOTAL'));
    $month = strtoupper(trim($input['month'] ?? 'TOTAL'));
    $target = cleanNum($input['target'] ?? 0);
    $capaian = cleanNum($input['capaian'] ?? 0);
    
    if ($id <= 0) throw new Exception("ID tidak valid");
    if (empty($ta) || empty($period)) throw new Exception("TA dan Period wajib diisi");
    
    $piutang = $target - $capaian;
    $persen_val = persen($capaian, $target);
    $kategori_val = kategori($persen_val);
    
    $stmt = $db->prepare("
        UPDATE kepatuhan_pembayaran 
        SET ta = ?, period = ?, category = ?, month = ?, 
            target = ?, capaian = ?, piutang = ?, persen = ?, kategori = ?,
            updated_at = NOW()
        WHERE id = ?
    ");
    $stmt->bind_param(
        "ssssddddsi", 
        $ta, $period, $category, $month, 
        $target, $capaian, $piutang, $persen_val, $kategori_val, 
        $id
    );
    $stmt->execute();
    
    if ($stmt->affected_rows === 0) {
        throw new Exception("Data tidak ditemukan atau tidak ada perubahan");
    }
    
    jsonResponse(true, ['message' => 'Data berhasil diperbarui']);
}

/* ================= ACTION: DELETE ================= */
function action_delete(Database $db): void {
    requireAdmin();
    
    $input = json_decode(file_get_contents('php://input'), true);
    $id = (int)($input['id'] ?? 0);
    
    if ($id <= 0) throw new Exception("ID tidak valid");
    
    $stmt = $db->prepare("DELETE FROM kepatuhan_pembayaran WHERE id = ?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    
    if ($stmt->affected_rows === 0) {
        throw new Exception("Data tidak ditemukan");
    }
    
    jsonResponse(true, ['message' => 'Data berhasil dihapus']);
}

/* ================= MAIN ROUTER ================= */
try {
    $action = $_GET['action'] ?? $_POST['action'] ?? '';
    
    if (empty($action)) {
        throw new Exception("Parameter 'action' wajib diisi");
    }
    
    // No DB needed
    if ($action === 'check_deps') {
        action_check_deps();
        exit;
    }
    
    // Initialize DB
    $db = new Database();
    
    // Route to actions
    match($action) {
        'get_periods' => action_get_periods($db),
        'get_data' => action_get_data($db),
        'get_detail' => action_get_detail($db),
        'get_chart' => action_get_data($db), // Alias
        'export' => action_export($db),
        'import' => action_import($db),
        'create' => action_create($db),
        'update' => action_update($db),
        'delete' => action_delete($db),
        default => throw new Exception("Action '$action' tidak dikenali")
    };
    
} catch (Throwable $e) {
    if (isset($db)) {
        try { $db->rollback(); } catch (Exception $ex) {}
    }
    
    error_log("API ERROR [{$action}]: " . $e->getMessage() . " | " . $e->getFile() . ":" . $e->getLine());
    jsonResponse(false, null, $e->getMessage(), 400);
    
} finally {
    if (isset($db)) $db->close();
}