<?php
/**
 * ========================================
 * API ENDPOINT: GET KEPATUHAN DATA
 * ========================================
 * Fetch payment compliance data from database
 * Usage: api_get_data.php?ta=2024-2025&kategori=UMUM&jenis=SPP
 * ========================================
 */

header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

require_once __DIR__ . '/config_database.php';

/**
 * Get query parameters
 */
$tahunAjaran = $_GET['ta'] ?? '2024-2025';
$kategoriSiswa = $_GET['kategori'] ?? 'UMUM';
$jenisPembayaran = $_GET['jenis'] ?? 'SPP';
$bulan = $_GET['bulan'] ?? null;

/**
 * Response structure
 */
$response = [
    'success' => false,
    'message' => '',
    'data' => [],
    'filters' => [
        'tahun_ajaran' => $tahunAjaran,
        'kategori_siswa' => $kategoriSiswa,
        'jenis_pembayaran' => $jenisPembayaran,
        'bulan' => $bulan
    ],
    'timestamp' => date('Y-m-d H:i:s')
];

try {
    $pdo = getDB();
    
    /**
     * Query data from view
     */
    $sql = "
        SELECT 
            sekolah_id,
            kode_sekolah,
            nama_sekolah,
            kota,
            tahun_ajaran,
            kode_pembayaran,
            nama_pembayaran,
            kode_kategori,
            nama_kategori,
            target_tahunan,
            capaian_tahunan,
            piutang_tahunan,
            persentase_capaian,
            kategori_kepatuhan,
            bulan_data
        FROM v_kepatuhan_per_sekolah
        WHERE tahun_ajaran = ?
        AND kode_kategori = ?
        AND kode_pembayaran = ?
        ORDER BY nama_sekolah
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$tahunAjaran, $kategoriSiswa, $jenisPembayaran]);
    $data = $stmt->fetchAll();
    
    /**
     * Calculate statistics
     */
    $totalSekolah = count($data);
    $totalTarget = array_sum(array_column($data, 'target_tahunan'));
    $totalCapaian = array_sum(array_column($data, 'capaian_tahunan'));
    $totalPiutang = $totalTarget - $totalCapaian;
    $persentaseTotal = $totalTarget > 0 ? ($totalCapaian / $totalTarget * 100) : 0;
    
    /**
     * Count by category
     */
    $excellent = count(array_filter($data, fn($d) => $d['kategori_kepatuhan'] === 'EXCELLENT'));
    $good = count(array_filter($data, fn($d) => $d['kategori_kepatuhan'] === 'GOOD'));
    $average = count(array_filter($data, fn($d) => $d['kategori_kepatuhan'] === 'AVERAGE'));
    $poor = count(array_filter($data, fn($d) => $d['kategori_kepatuhan'] === 'POOR'));
    
    /**
     * Build response
     */
    $response['success'] = true;
    $response['message'] = 'Data fetched successfully';
    $response['data'] = $data;
    $response['statistics'] = [
        'total_sekolah' => $totalSekolah,
        'total_target' => $totalTarget,
        'total_capaian' => $totalCapaian,
        'total_piutang' => $totalPiutang,
        'persentase_total' => round($persentaseTotal, 2),
        'kategori' => [
            'excellent' => $excellent,
            'good' => $good,
            'average' => $average,
            'poor' => $poor
        ]
    ];
    
    /**
     * Get monthly data if bulan parameter is provided
     */
    if ($bulan) {
        $sqlBulanan = "
            SELECT 
                s.nama_sekolah,
                pb.nama_bulan,
                pb.target_bulanan,
                pb.capaian_bulanan,
                pb.piutang_bulanan,
                pb.persentase_capaian,
                pb.kategori_kepatuhan
            FROM pembayaran_bulanan pb
            JOIN pembayaran_tahunan pt ON pb.pembayaran_tahunan_id = pt.id
            JOIN sekolah s ON pt.sekolah_id = s.id
            JOIN tahun_ajaran ta ON pt.tahun_ajaran_id = ta.id
            JOIN jenis_pembayaran jp ON pt.jenis_pembayaran_id = jp.id
            JOIN kategori_siswa ks ON pt.kategori_siswa_id = ks.id
            WHERE ta.tahun_ajaran = ?
            AND ks.kode_kategori = ?
            AND jp.kode_pembayaran = ?
            AND pb.nama_bulan = ?
            ORDER BY s.nama_sekolah
        ";
        
        $stmt = $pdo->prepare($sqlBulanan);
        $stmt->execute([$tahunAjaran, $kategoriSiswa, $jenisPembayaran, strtoupper($bulan)]);
        $response['data_bulanan'] = $stmt->fetchAll();
    }
    
} catch(PDOException $e) {
    $response['success'] = false;
    $response['message'] = 'Database error: ' . $e->getMessage();
    http_response_code(500);
}

/**
 * Output JSON response
 */
echo json_encode($response, JSON_PRETTY_PRINT);