<?php
// Enable error reporting for debugging
/*error_reporting(E_ALL ^ (E_NOTICE | E_WARNING));
ini_set('display_errors', 1);*/

// Database connection
require_once 'koneksi.php';

// Helper function for kolektibilitas calculation with 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';
}

// Function to save kolektibilitas data with error handling
function saveKolektibilitas($koneksi, $rekening, $tgl,$plafond,$bakidebet,$jaminan,$t_margin,$barang,$kolek) {
    try {
        // Start transaction
        mysqli_begin_transaction($koneksi);

        // First, delete any existing record for this rekening and date
        $delete_query = "DELETE FROM kolek WHERE kode = ? ";
        $delete_stmt = mysqli_prepare($koneksi, $delete_query);
        mysqli_stmt_bind_param($delete_stmt, "s", $kode);
        mysqli_stmt_execute($delete_stmt);
        mysqli_stmt_close($delete_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,$kolek);
        $result = mysqli_stmt_execute($insert_stmt);
        mysqli_stmt_close($insert_stmt);
        
        // Commit transaction
        mysqli_commit($koneksi);
        
        return $result;
    } catch (Exception $e) {
        // Rollback transaction in case of error
        mysqli_rollback($koneksi);
        error_log("Error saving kolektibilitas: " . $e->getMessage());
        return false;
    }
}

// Initialize session if not already started
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

// Default variables
$kode = $_SESSION['kode'] ?? '';
$par2 = $_POST['par2'] ?? '';
$where = '';
$summary = [
    'plafond' => 0,
    'bakidebet' => 0,
    'margin' => 0,
    'pokok' => 0,
    'total' => 0
];

// Prepare WHERE clause if date is provided
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;
}
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <title>Laporan Kolektibilitas</title>
    <link rel="stylesheet" type="text/css" href="./assets/jquery.dataTables.css">
    <script src="./assets/jquery.js"></script>
    <script src="./assets/jquery.dataTables.js"></script>
</head>
<body>
<div class="container-fluid">
    <div class="card">
        <div class="card-header bg-info">
            <h3 class="card-title">Laporan Kolektibilitas</h3>
        </div>
        <div class="card-body">
            <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">Tanggal</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
                    // Prepare and execute query
                    $query = "
                        SELECT 
                            a.plafond,
                            a.margin,
                            a.jangkawaktu,
                            c.nama,
                            a.rekening,
                            a.tgl,
                            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
                    ";
                    $_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);

                    $no = 0;

                    $save_success = true;
                    $kolek_data = []; // To store kolektibilitas data for batch processing
                    
                    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($par2))->days;
                        
                        // Calculate tunggakan
                        $kol_period = floor($daysPastDue / 15);
                        $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
                        $kolek = getKolektibilitas($daysPastDue, $flat_t_margin);
                        
                        // Store kolektibilitas data for batch saving
                        $kolek_data[] = [
                            'rekening' => $row['rekening'],
                            'tgl' => date('Y-m-d',strtotime($par2)),
                            'plafond' => $row['plafond'],
                            'bakidebet' => $row['bakidebet'],
                            'jaminan' => $row['jaminan'],
                            'flat_t_margin' => $flat_t_margin,
                            'barang'=> $row['barang'],
                            'kolek' => $kolek
                        ];
                        ?>
                        <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"><?= $kolek ?></td>
                        </tr>
                    <?php } 
                    mysqli_stmt_close($stmt);
                    
                    // Batch save kolektibilitas data
                    if (!empty($kolek_data)) {
                        $batch_save_success = true;
                        foreach ($kolek_data as $data) {
                            if (!saveKolektibilitas($koneksi, $data['rekening'], $data['tgl'],$data['plafond'],$data['bakidebet'],$data['jaminan'],$data['flat_t_margin'],$data['barang'], $data['kolek'])) {
                                $batch_save_success = false;
                                break;
                            }
                        }
                        
                        // Display save status message
                        if ($batch_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; ?>
        </div>
    </div>
</div>

<script>
$(document).ready(function() {
    $('#sortTable').DataTable();
});
</script>
</body>
</html>