<?php
// Koneksi ke database
$host = 'localhost';
$dbname = 'pt_gadai_online';
$username = 'akutans1';
$password = 'kh';

try {
   $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   // Query SQL
   $sql = "WITH TunggakanInfo AS (
       SELECT 
           a.rekening,
           c.nama,
           b.tgl as tgl_realisasi,
           b.barang,
           b.jaminan, 
           b.jenisbarang,
           SUM(a.plafond) - SUM(a.pokok) AS bakidebet,
           DATEDIFF(
               MAX(CASE WHEN a.pokok > 0 THEN a.tgl ELSE NULL END),
               '2025-01-20'
           ) as hari_tunggakan
       FROM 
           angsuran a 
           LEFT JOIN pinjaman b 
               ON a.rekening = b.rekening 
               AND b.kode = '101'
           LEFT JOIN registrasi c 
               ON b.register = c.id 
       WHERE 
           a.kode = '101'
           AND a.tgl <= '2025-01-20'
       GROUP BY
           a.rekening,
           c.nama,
           b.tgl,
           b.barang,
           b.jaminan,
           b.jenisbarang
       HAVING 
           (SUM(a.plafond) - SUM(a.pokok)) > 0
   )
   SELECT * FROM TunggakanInfo";

   $stmt = $conn->prepare($sql);
   $stmt->execute();
   $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

   // Tampilkan dalam bentuk tabel HTML
   ?>
   <!DOCTYPE html>
   <html>
   <head>
       <title>Data Tunggakan</title>
       <style>
           table {
               border-collapse: collapse;
               width: 100%;
           }
           th, td {
               border: 1px solid #ddd;
               padding: 8px;
               text-align: left;
           }
           th {
               background-color: #4CAF50;
               color: white;
           }
           tr:nth-child(even) {
               background-color: #f2f2f2;
           }
           .kolektibilitas-1 { background-color: #90EE90; }
           .kolektibilitas-2 { background-color: #FFD700; }
           .kolektibilitas-3 { background-color: #FFA500; }
           .kolektibilitas-4 { background-color: #FF6347; }
           .kolektibilitas-5 { background-color: #FF0000; color: white; }
       </style>
   </head>
   <body>
       <h2>Data Tunggakan dan Kolektibilitas</h2>
       <table>
           <tr>
               <th>No</th>
               <th>Rekening</th>
               <th>Nama</th>
               <th>Tgl Realisasi</th>
               <th>Barang</th>
               <th>Jaminan</th>
               <th>Jenis Barang</th>
               <th>Baki Debet</th>
               <th>Hari Tunggakan</th>
               <th>Kolektibilitas</th>
               <th>Status</th>
           </tr>
           <?php
           $no = 1;
           foreach($results as $row) {
               // Hitung kolektibilitas
               $kolektibilitas = 1;
               $status = 'Lancar';
               
               if($row['bakidebet'] == 0) {
                   $kolektibilitas = 1;
                   $status = 'Lunas';
               } elseif($row['hari_tunggakan'] >= 0) {
                   $kolektibilitas = 1;
                   $status = 'Lancar';
               } elseif($row['hari_tunggakan'] > -90) {
                   $kolektibilitas = 2;
                   $status = 'Dalam Perhatian Khusus';
               } elseif($row['hari_tunggakan'] > -120) {
                   $kolektibilitas = 3;
                   $status = 'Kurang Lancar';
               } elseif($row['hari_tunggakan'] > -180) {
                   $kolektibilitas = 4;
                   $status = 'Diragukan';
               } else {
                   $kolektibilitas = 5;
                   $status = 'Macet';
               }
               
               echo "<tr class='kolektibilitas-{$kolektibilitas}'>";
               echo "<td>" . $no++ . "</td>";
               echo "<td>" . htmlspecialchars($row['rekening']) . "</td>";
               echo "<td>" . htmlspecialchars($row['nama']) . "</td>";
               echo "<td>" . htmlspecialchars($row['tgl_realisasi']) . "</td>";
               echo "<td>" . htmlspecialchars($row['barang']) . "</td>";
               echo "<td>" . htmlspecialchars($row['jaminan']) . "</td>";
               echo "<td>" . htmlspecialchars($row['jenisbarang']) . "</td>";
               echo "<td align='right'>" . number_format($row['bakidebet'], 2) . "</td>";
               echo "<td align='right'>" . $row['hari_tunggakan'] . "</td>";
               echo "<td align='center'>" . $kolektibilitas . "</td>";
               echo "<td>" . $status . "</td>";
               echo "</tr>";
           }
           ?>
       </table>
   </body>
   </html>
   <?php
} catch(PDOException $e) {
   echo "Error: " . $e->getMessage();
}

$conn = null;
?>