<?php
// madr_net_settlement.php

error_reporting(E_ALL);
ini_set('display_errors', 1);

include "includes/db_connect.php";
include "includes/auth.php";
checkAdmin();

// ----------------------
// Read Inputs
// ----------------------
$country  = isset($_GET['to_country']) ? mysqli_real_escape_string($conn, $_GET['to_country']) : '';
$fromDate = isset($_GET['from_date']) ? mysqli_real_escape_string($conn, $_GET['from_date']) : '';
$toDate   = isset($_GET['to_date']) ? mysqli_real_escape_string($conn, $_GET['to_date']) : '';
$cutOffTime = '23:59:59'; // default daily cut-off

// ----------------------
// Show Form if Missing
// ----------------------
if ($country === '' || $fromDate === '' || $toDate === '') {
?>
<!DOCTYPE html>
<html>
<head>
    <title>Generate MADR Net Settlement</title>
    <style>
        body { font-family: Arial; background:#f5f7fa; padding:20px; }
        .form-card { background:white; padding:20px; border-radius:10px; box-shadow:0 4px 10px rgba(0,0,0,0.05); max-width:400px; margin:auto; }
        input, button { padding:8px; margin-top:5px; border-radius:6px; border:1px solid #ccc; width:100%; }
        button { background:#6366f1; color:white; border:none; cursor:pointer; margin-top:15px; }
    </style>
</head>
<body>

<div class="form-card">
    <h2>Generate MADR Net Settlement</h2>
    <form method="GET">
        <label>Country:</label>
        <input type="text" name="to_country" placeholder="e.g. NG" required>

        <label>From Date:</label>
        <input type="date" name="from_date" required>

        <label>To Date:</label>
        <input type="date" name="to_date" required>

        <button type="submit">Generate Settlement</button>
    </form>
</div>

</body>
</html>
<?php
    exit;
}

// ----------------------
// Generate Unique Batch ID
// ----------------------
$batchID = 'BATCH-' . strtoupper($country) . '-' . date('Ymd-His');

// ----------------------
// Settlement Calculation for SETTLED transactions NOT in any batch
// ----------------------
$sql = "
SELECT 
    SUM(send_amount) as total_send,
    SUM(receive_amount) as total_receive,
    SUM(CASE WHEN status='FAILED' THEN send_amount ELSE 0 END) as total_failed,
    SUM(CASE WHEN status='QUEUED' THEN send_amount ELSE 0 END) as total_queued
FROM transactions
WHERE to_country='$country'
AND status='SETTLED'
AND DATE(created_at) BETWEEN '$fromDate' AND '$toDate'
AND TIME(created_at) <= '$cutOffTime'
AND batch_id IS NULL
";

$result = mysqli_query($conn, $sql);
$data = mysqli_fetch_assoc($result);

$totalSend = $data['total_send'] ?? 0;
$totalReceive = $data['total_receive'] ?? 0;
$totalFailed = $data['total_failed'] ?? 0;
$totalQueued = $data['total_queued'] ?? 0;
$netPosition = $totalSend - $totalReceive;

// ----------------------
// Insert batch record
// ----------------------
$insertBatch = "
INSERT INTO settlement_batches 
(batch_id, to_country, from_date, to_date, cut_off_time, total_send, total_receive, total_failed, total_queued, net_position)
VALUES
('$batchID','$country','$fromDate','$toDate','$cutOffTime',$totalSend,$totalReceive,$totalFailed,$totalQueued,$netPosition)
";

mysqli_query($conn, $insertBatch);

// ----------------------
// Lock transactions into batch
// ----------------------
$lockTx = "
UPDATE transactions
SET batch_id='$batchID'
WHERE to_country='$country'
AND status='SETTLED'
AND DATE(created_at) BETWEEN '$fromDate' AND '$toDate'
AND TIME(created_at) <= '$cutOffTime'
AND batch_id IS NULL
";

mysqli_query($conn, $lockTx);

// ----------------------
// CSV Export
// ----------------------
if (isset($_GET['export']) && $_GET['export'] == 'csv') {

    $exportFileName = "MADR_NET_SETTLEMENT_{$batchID}.csv";
    header('Content-Type: text/csv');
    header("Content-Disposition: attachment; filename=\"$exportFileName\"");

    $output = fopen("php://output", "w");

    fputcsv($output, ['MADR NET SETTLEMENT REPORT']);
    fputcsv($output, ['Batch ID', $batchID]);
    fputcsv($output, ['Country', $country]);
    fputcsv($output, ['Period', "$fromDate to $toDate"]);
    fputcsv($output, ['Cut-Off Time', $cutOffTime]);
    fputcsv($output, []);
    fputcsv($output, ['Total SETTLED Send', number_format($totalSend,2)]);
    fputcsv($output, ['Total SETTLED Receive', number_format($totalReceive,2)]);
    fputcsv($output, ['Total FAILED', number_format($totalFailed,2)]);
    fputcsv($output, ['Total QUEUED', number_format($totalQueued,2)]);
    fputcsv($output, []);
    fputcsv($output, ['NET POSITION', number_format($netPosition,2)]);
    fputcsv($output, ['Batch Status', 'OPEN']);

    fclose($output);
    exit;
}

?>

<!DOCTYPE html>
<html>
<head>
<title>MADR Net Settlement</title>
<style>
body { font-family: Arial; background:#f5f7fa; padding:20px; }
.card {
    background:white;
    padding:20px;
    border-radius:10px;
    box-shadow:0 4px 10px rgba(0,0,0,0.05);
    max-width:700px;
}
h2 { margin-top:0; }
.metric { margin:10px 0; font-size:16px; }
.net { font-weight:bold; font-size:18px; }
.positive { color:green; }
.negative { color:red; }
a { text-decoration:none; color:#6366f1; }
a:hover { text-decoration:underline; }
button { background:#6366f1; color:white; border:none; padding:6px 12px; border-radius:6px; cursor:pointer; margin-top:10px; }
</style>
</head>
<body>

<?php include "includes/mobex_sidebar.php"; ?>

<div class="card">
<h2>MADR Net Settlement</h2>
<?php if (isset($_GET['msg'])): ?>
    <div style="background:#d1fae5; padding:10px; border-radius:6px; margin-bottom:10px; color:#065f46;">
        <?= htmlspecialchars($_GET['msg']) ?>
    </div>
<?php endif; ?>

<p><strong>Country:</strong> <?= htmlspecialchars($country) ?></p>
<p><strong>Period:</strong> <?= htmlspecialchars($fromDate) ?> → <?= htmlspecialchars($toDate) ?></p>
<p><strong>Batch ID:</strong> <?= $batchID ?></p>
<p><strong>Cut-Off Time:</strong> <?= $cutOffTime ?></p>

<div class="metric">Total SETTLED Send: <?= number_format($totalSend,2) ?></div>
<div class="metric">Total SETTLED Receive: <?= number_format($totalReceive,2) ?></div>
<div class="metric">Total FAILED: <?= number_format($totalFailed,2) ?></div>
<div class="metric">Total QUEUED: <?= number_format($totalQueued,2) ?></div>

<div class="metric net <?= $netPosition >= 0 ? 'positive' : 'negative' ?>">
NET POSITION: <?= number_format($netPosition,2) ?>
</div>

<br>

<a href="?to_country=<?= urlencode($country) ?>&from_date=<?= urlencode($fromDate) ?>&to_date=<?= urlencode($toDate) ?>&export=csv">
    ⬇ Export Settlement CSV
</a>

<form method="POST" action="update_batch_status.php">
    <input type="hidden" name="batch_id" value="<?= $batchID ?>">
    <label>Update Batch Status:</label>
    <select name="status">
        <option value="OPEN" selected>OPEN</option>
        <option value="CLOSED">CLOSED</option>
        <option value="PAID">PAID</option>
    </select>
    <button type="submit">Update Status</button>
</form>

</div>

</body>
</html>
