<?php
error_reporting(E_ALL^(E_NOTICE|E_WARNING));
ini_set('display_errors', 1);

// Helper function for kolektibilitas calculation - updated logic
function getKolektibilitas($daysPastDue, $flat_t_margin) {
    // If tunggakan ujroh is 0 or negative, return Lancar (L)
    if ($flat_t_margin <= 0) {
        return 'L';
    }

    // Otherwise check days past due
    if ($daysPastDue <= 15) return 'L';
    if ($daysPastDue <= 30) return 'D';
    if ($daysPastDue <= 45) return 'D';
    return 'M';
}

// Initialize summary variables
$summary = [
    'plafond' => 0,
    'bakidebet' => 0,
    'margin' => 0,
    'pokok' => 0,
    'total' => 0
];

// Function to save kolektibilitas data
function saveKolektibilitas($koneksi, $rekening, $tgl,$plafond,$bakidebet,$jaminan,$t_margin,$barang, $kolektibilitas) {
    // First, delete any existing record for this rekening and date
    $delete_query = "DELETE FROM kolek WHERE rekening = ? AND tgl = ?";
    $stmt = mysqli_prepare($koneksi, $delete_query);
    mysqli_stmt_bind_param($stmt, "ss", $rekening, $tgl);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);


    
    
    // Then insert the new record
        $insert_query = "INSERT INTO kolek (rekening, tgl,plafond,bakidebet,jaminan,t_margin,barang, kolek) VALUES (?, ?,?,?,?,?,?, ?)";
        $insert_stmt = mysqli_prepare($koneksi, $insert_query);
        mysqli_stmt_bind_param($insert_stmt, "ssssssss", $rekening, $tgl, $plafond,$bakidebet,$jaminan,$t_margin,$barang,$kolektibilitas);
        $result = mysqli_stmt_execute($insert_stmt);
        mysqli_stmt_close($insert_stmt);
    
    return $result;
}
?>

<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-1">
            <div class="col-sm-12"></div>
        </div>
    </div>
</div>

<section class="content">
<div class="container-fluid">
<div class="row">
<div class="col-15">
<div class="card">
<div class="card-header bg-info">
    <h3 class="card-title">Laporan Kolektibilitas</h3>
    <div class="card-tools">
        <button type="button" class="btn btn-tool" data-card-widget="collapse">
            <i class="fas fa-minus"></i>
        </button>
        <button type="button" class="btn btn-tool" data-card-widget="remove">
            <i class="fas fa-times"></i>
        </button>
    </div>
</div>

<div class="card-body">
<?php
$kode = $_SESSION['kode'] ?? '';
$par2 = $_POST['par2'] ?? '';
$where = '';

if ($par2) {
    $par2 = date('Y-m-d', strtotime($par2));
    $where = "WHERE b.tgl <= ? AND a.kode = ? AND bakidebet <> 0";
    $_SESSION['tuggakan']['periode2'] = date('d-m-Y', strtotime($par2));
    $_SESSION['tuggakan']['kode']['kode']=$kode;
}
?>

<form role="form" action="home.php?home=<?= md5('kolektibilitas') ?>" method="post">
<div class="row">
    <div class="form-group row">
        <label for="nama" class="col-2 m-2">Tgl</label>
        <input type="date" name="par2" class="form-control col-7" value="<?= $par2 ?>">
        <button type="submit" class="btn btn-info"><i class="fas fa-search"></i></button>
    </div>
    
    <?php if ($par2): ?>
        <div class="form-group col-2">
            <a href="pdf/nunggak_angsuranpdf.php" target="_blank" class="btn btn-success float-right mt-2">
                <i class="fa fa-print"></i> PDF
            </a>
        </div>
    <?php endif; ?>
</div>
</form>

<hr>

<?php if ($par2): ?>

<table id="sortTable" class="table table-bordered table-hover">
    <thead>
        <tr align="center">
            <th>No</th>
            <th>Tanggal</th>
            <th>Rekening</th>
            <th>Nama</th>
            <th>Plafond</th>
            <th>Ujr</th>
            <th>Bakidebet</th>
            <th>Barang</th>
            <th>Jaminan</th>
            <th>T Pokok</th>
            <th>T Ujroh</th>
            <th>Jml</th>
            <th>Kol</th>
        </tr>
    </thead>
    <tbody>
        <?php
        require_once 'koneksi.php';
        
        // Optimized query using JOIN and aggregation
        $query = "
            SELECT 
                a.plafond,
                a.margin,
                a.jangkawaktu,
                c.nama,
                a.rekening,
                a.tgl,
                a.jtempo,
                a.barang,
                a.jaminan,
                COALESCE(SUM(b.pokok), 0) as tpokok,
                COALESCE(SUM(b.margin), 0) as tmargin,
                (a.plafond - COALESCE(SUM(b.pokok), 0)) as bakidebet
            FROM pinjaman a 
            LEFT JOIN angsuran b ON a.rekening = b.rekening 
            LEFT JOIN registrasi c ON a.register = c.id 
            $where
            GROUP BY a.rekening
            HAVING bakidebet > 0 ORDER BY a.jaminan ASC
        ";
        $_SESSION['tuggakan']['kode']['sqlt'] = $query;
        $stmt = mysqli_prepare($koneksi, $query);
        if ($where) {
            mysqli_stmt_bind_param($stmt, "ss", $par2, $kode);
        }
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);

        //hapus data dlu seblum simpan
        mysqli_query($koneksi, "DELETE FROM kolek where kode='$kode' ");
        
        $no = 0;
        $save_success = true; // Track if all saves are successful
        
        while ($row = mysqli_fetch_assoc($result)) {
            $no++;
            
            // Calculate installment details
            $angsuran_margin = $row['plafond'] * $row['margin'] / 100;
            $angsuran_pokok = $row['plafond'] / $row['jangkawaktu'];
            
            // Calculate days past due
            $daysPastDue = (new DateTime($row['tgl']))->diff(new DateTime($row['jtempo']))->days;
            
            // Calculate tunggakan
            $kol_period = floor($daysPastDue / 15);

            $flat_t_margin = ($angsuran_margin * 3) - $row['tmargin'];
            //$flat_t_margin = ($angsuran_margin * $kol_period) - $row['tmargin'];
            $flat_t_pokok = ($angsuran_pokok * $kol_period) - $row['tpokok'];
            $tot_tunggakan = $flat_t_margin + $flat_t_pokok;
            
            // Update summary
            $summary['plafond'] += $row['plafond'];
            $summary['bakidebet'] += $row['bakidebet'];
            $summary['margin'] += $flat_t_margin;
            $summary['pokok'] += $flat_t_pokok;
            $summary['total'] += $tot_tunggakan;
            
            // Get kolektibilitas
            $kolektibilitas = getKolektibilitas($daysPastDue, $flat_t_margin);
            
            // Save to kolek table
            if (!saveKolektibilitas($koneksi, $row['rekening'], $par2,$row['plafond'],$row['bakidebet'],$row['jaminan'],$flat_t_margin,$row['barang'],$kolektibilitas)) {
                $save_success = false;
            }
            ?>
            <tr>
                <td align="center"><?= $no ?></td>
                <td><?= date('d-m-Y', strtotime($row['tgl'])) ?></td>
                <td><?= htmlspecialchars($row['rekening']) ?></td>
                <td><?= htmlspecialchars($row['nama']) ?></td>
                <td align="right"><?= number_format($row['plafond']) ?></td>
                <td><?= $row['margin'] ?></td>
                <td align="right"><?= number_format($row['bakidebet']) ?></td>
                <td><?= htmlspecialchars($row['barang']) ?></td>
                <td><?= htmlspecialchars($row['jaminan']) ?></td>
                <td align="right"><?= number_format($flat_t_pokok) ?></td>
                <td align="right"><?= number_format($flat_t_margin) ?></td>
                <td align="right"><?= number_format($tot_tunggakan) ?></td>
                <td align="center"><?= $kolektibilitas ?></td>
            </tr>
        <?php } 
        mysqli_stmt_close($stmt);
        
        // Display save status message
        if ($no > 0) {
            if ($save_success) {
                echo '<div class="alert alert-success">Data kolektibilitas berhasil diproses</div>';
            } else {
                echo '<div class="alert alert-danger">Terjadi kesalahan saat proses kolektibilitas</div>';
            }
        }
        ?>
    </tbody>
    <tfoot>
        <tr>
            <td colspan="4"><b>Total</b></td>
            <td align="right"><b><?= number_format($summary['plafond']) ?></b></td>
            <td></td>
            <td align="right"><b><?= number_format($summary['bakidebet']) ?></b></td>
            <td colspan="2"></td>
            <td align="right"><b><?= number_format($summary['pokok']) ?></b></td>
            <td align="right"><b><?= number_format($summary['margin']) ?></b></td>
            <td align="right"><b><?= number_format($summary['total']) ?></b></td>
            <td></td>
        </tr>
    </tfoot>
</table>
<?php endif; ?>

<script type="text/javascript" charset="utf8" src="./assets/jquery.dataTables.js"></script>
<link rel="stylesheet" type="text/css" href="./assets/jquery.dataTables.css">
<script>
$('#sortTable').DataTable();
</script>
</div>
</div>
</div>
</div>
</div>
</section>