<?php
// /edenrms/pages/monthly_due_new.php
declare(strict_types=1);

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

register_shutdown_function(function () {
    $e = error_get_last();
    if ($e !== null && in_array($e['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR], true)) {
        echo '<pre style="background:#111;color:#fff;padding:20px;white-space:pre-wrap">';
        print_r($e);
        echo '</pre>';
    }
});

if (session_status() !== PHP_SESSION_ACTIVE) {
    session_start();
}

require_once __DIR__ . '/../includes/auth.php';

$db = ec_db();
if (!$db instanceof mysqli) {
    http_response_code(500);
    exit('DB error');
}

$db->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_OFF);

$EXPORT = isset($_GET['export']) && $_GET['export'] === 'csv';
$DEBUG  = isset($_GET['debug']) && $_GET['debug'] === '1';

$year  = isset($_GET['y']) ? max(2000, (int)$_GET['y']) : (int)date('Y');
$month = isset($_GET['m']) ? max(1, min(12, (int)$_GET['m'])) : (int)date('n');

$monthKey   = sprintf('%04d-%02d', $year, $month);
$monthStart = $monthKey . '-01';
$monthEnd   = date('Y-m-t', strtotime($monthStart));
$monthLabel = date('F Y', strtotime($monthStart));

function mdn_h($v): string {
    return htmlspecialchars((string)$v, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function mdn_table_exists(mysqli $db, string $table): bool {
    static $cache = [];
    if (isset($cache[$table])) return $cache[$table];

    $t = $db->real_escape_string($table);
    $rs = $db->query("SHOW TABLES LIKE '{$t}'");
    return $cache[$table] = ($rs && $rs->num_rows > 0);
}

function mdn_col_exists(mysqli $db, string $table, string $col): bool {
    static $cache = [];
    $key = $table . '.' . $col;
    if (isset($cache[$key])) return $cache[$key];

    $t = $db->real_escape_string($table);
    $c = $db->real_escape_string($col);
    $rs = $db->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return $cache[$key] = ($rs && $rs->num_rows > 0);
}

function mdn_first_col(mysqli $db, string $table, array $cols, ?string $fallback = null): ?string {
    foreach ($cols as $c) {
        if (mdn_col_exists($db, $table, $c)) return $c;
    }
    return $fallback;
}

function mdn_money($n): float {
    return round((float)$n, 2);
}

function mdn_bind(mysqli_stmt $st, string $types, array $params): void {
    if ($types === '' || !$params) return;
    $args = [$types];
    foreach ($params as $k => $v) {
        $args[$k + 1] = &$params[$k];
    }
    call_user_func_array([$st, 'bind_param'], $args);
}

function mdn_fetch_all(mysqli_stmt $st): array {
    $res = $st->get_result();
    return $res ? $res->fetch_all(MYSQLI_ASSOC) : [];
}

function mdn_student_info(mysqli $db, int $studentId): array {
    $nameCols  = ['full_name', 'student_name', 'name', 'full_name_en'];
    $phoneCols = ['mobile', 'phone', 'contact_no', 'tel'];

    $select = ['id'];
    foreach (array_merge($nameCols, $phoneCols) as $c) {
        if (mdn_col_exists($db, 'studentss', $c)) {
            $select[] = "`$c`";
        }
    }

    $sql = "SELECT " . implode(',', array_unique($select)) . " FROM studentss WHERE id=? LIMIT 1";
    $st = $db->prepare($sql);
    if (!$st) return ['name' => 'Student #' . $studentId, 'phone' => ''];

    $st->bind_param('i', $studentId);
    $st->execute();
    $row = mdn_fetch_all($st)[0] ?? [];
    $st->close();

    $name = '';
    foreach ($nameCols as $c) {
        if (!empty($row[$c])) {
            $name = trim((string)$row[$c]);
            break;
        }
    }

    $phone = '';
    foreach ($phoneCols as $c) {
        if (!empty($row[$c])) {
            $phone = trim((string)$row[$c]);
            break;
        }
    }

    return [
        'name'  => $name !== '' ? $name : 'Student #' . $studentId,
        'phone' => $phone,
    ];
}

function mdn_course_name(mysqli $db, int $courseId): string {
    if ($courseId <= 0 || !mdn_table_exists($db, 'courses')) return '-';

    $col = mdn_first_col($db, 'courses', ['course_name', 'name', 'title'], null);
    if (!$col) return '-';

    $st = $db->prepare("SELECT `$col` AS course_name FROM courses WHERE id=? LIMIT 1");
    if (!$st) return '-';

    $st->bind_param('i', $courseId);
    $st->execute();
    $row = mdn_fetch_all($st)[0] ?? [];
    $st->close();

    return trim((string)($row['course_name'] ?? '')) ?: '-';
}

function mdn_paid_total(mysqli $db, int $studentId, int $planId, int $courseId): float {
    if (!mdn_table_exists($db, 'payments')) return 0.0;

    $amountCol = mdn_first_col($db, 'payments', ['amount', 'paid_amount', 'amount_lkr', 'value'], null);
    if (!$amountCol) return 0.0;

    $where = ['student_id = ?'];
    $types = 'i';
    $params = [$studentId];

    $matchParts = [];

    if (mdn_col_exists($db, 'payments', 'plan_id')) {
        $matchParts[] = 'plan_id = ?';
        $types .= 'i';
        $params[] = $planId;
    }

    if ($courseId > 0 && mdn_col_exists($db, 'payments', 'course_id')) {
        $matchParts[] = 'course_id = ?';
        $types .= 'i';
        $params[] = $courseId;
    }

    if ($matchParts) {
        $where[] = '(' . implode(' OR ', $matchParts) . ')';
    }

    $dateCol = mdn_first_col($db, 'payments', ['date_paid', 'payment_date', 'paid_at', 'date', 'created_at'], null);
    if ($dateCol) {
        $where[] = "DATE(`$dateCol`) <= CURDATE()";
    }

    $statusCol = mdn_first_col($db, 'payments', ['status', 'state'], null);
    if ($statusCol) {
        $where[] = "(LOWER(COALESCE(`$statusCol`,'')) IN ('paid','success','completed') OR `$statusCol` IS NULL OR `$statusCol`='')";
    }

    $sql = "
        SELECT COALESCE(SUM(COALESCE(`$amountCol`,0)),0) AS paid
        FROM payments
        WHERE " . implode(' AND ', $where);

    $st = $db->prepare($sql);
    if (!$st) return 0.0;

    mdn_bind($st, $types, $params);
    $st->execute();
    $row = mdn_fetch_all($st)[0] ?? [];
    $st->close();

    return mdn_money($row['paid'] ?? 0);
}

/* =========================
   BUILD ROWS
   ========================= */

$rows = [];
$totalDue = $totalPaid = $totalOutstanding = 0.0;
$debugMessage = '';

if (
    mdn_table_exists($db, 'studentss') &&
    mdn_table_exists($db, 'student_fee_plans') &&
    mdn_table_exists($db, 'payment_schedule')
) {
    $extraCol = mdn_col_exists($db, 'student_fee_plans', 'extra_fee_lkr')
        ? 'extra_fee_lkr'
        : (mdn_col_exists($db, 'student_fee_plans', 'extra_fees_lkr') ? 'extra_fees_lkr' : null);

    $plansSql = "
        SELECT sfp.*, LOWER(COALESCE(s.lms_status,'active')) AS lms_status
        FROM student_fee_plans sfp
        JOIN studentss s ON s.id = sfp.student_id
        WHERE LOWER(COALESCE(s.lms_status,'active')) NOT IN ('inactive','suspended')
        ORDER BY sfp.id ASC
    ";

    $plansRes = $db->query($plansSql);

    if (!$plansRes) {
        $debugMessage = $db->error;
    } else {
        while ($plan = $plansRes->fetch_assoc()) {
            $planId    = (int)($plan['id'] ?? 0);
            $studentId = (int)($plan['student_id'] ?? 0);
            $courseId  = (int)($plan['course_id'] ?? 0);

            if ($planId <= 0 || $studentId <= 0) continue;

            $courseFee = (float)($plan['course_fee_lkr'] ?? 0);
            $regFee    = (float)($plan['registration_fee_lkr'] ?? 0);
            $late      = (float)($plan['late_charges'] ?? 0);
            $extra     = $extraCol ? (float)($plan[$extraCol] ?? 0) : 0.0;

            $discount = (float)($plan['plan_discount_lkr'] ?? 0)
                      + (float)($plan['manual_discount_lkr'] ?? 0)
                      + (float)($plan['method_discount_lkr'] ?? 0);

            $realPayable = mdn_money(max(0, ($courseFee + $regFee + $late + $extra) - max(0, $discount)));
            if ($realPayable <= 0.009) continue;

            $paidTotal = mdn_paid_total($db, $studentId, $planId, $courseId);

            if (($realPayable - $paidTotal) <= 0.009) {
                continue;
            }

            if (mdn_col_exists($db, 'payment_schedule', 'plan_id')) {
                $st = $db->prepare("
                    SELECT *
                    FROM payment_schedule
                    WHERE plan_id = ?
                    ORDER BY due_date ASC, installment_no ASC, id ASC
                ");
                if (!$st) continue;
                $st->bind_param('i', $planId);
            } else {
                $st = $db->prepare("
                    SELECT *
                    FROM payment_schedule
                    WHERE student_id = ?
                    ORDER BY due_date ASC, installment_no ASC, id ASC
                ");
                if (!$st) continue;
                $st->bind_param('i', $studentId);
            }

            $st->execute();
            $scheduleRows = mdn_fetch_all($st);
            $st->close();

            if (!$scheduleRows) continue;

            $rawTotal = 0.0;
            foreach ($scheduleRows as $sr) {
                $rawTotal += (float)($sr['course_fee_lkr'] ?? 0)
                           + (float)($sr['registration_fee_lkr'] ?? 0);
            }

            if ($rawTotal <= 0.009) continue;

            $scale = $realPayable / $rawTotal;
            $allocatedPaid = 0.0;
            $studentInfo = null;
            $courseName = null;

            foreach ($scheduleRows as $sr) {
                $dueDate = trim((string)($sr['due_date'] ?? ''));
                if ($dueDate === '') continue;

                $rawDue = (float)($sr['course_fee_lkr'] ?? 0)
                        + (float)($sr['registration_fee_lkr'] ?? 0);

                $scaledDue = mdn_money($rawDue * $scale);

                if ($dueDate < $monthStart) {
                    $paidForOld = min($scaledDue, max(0, $paidTotal - $allocatedPaid));
                    $allocatedPaid += $paidForOld;
                    continue;
                }

                if ($dueDate > $monthEnd) {
                    continue;
                }

                $paidForLine = min($scaledDue, max(0, $paidTotal - $allocatedPaid));
                $outstanding = mdn_money(max(0, $scaledDue - $paidForLine));
                $allocatedPaid += $paidForLine;

                if ($outstanding <= 0.009) {
                    continue;
                }

                if ($studentInfo === null) {
                    $studentInfo = mdn_student_info($db, $studentId);
                }

                if ($courseName === null) {
                    $courseName = mdn_course_name($db, $courseId);
                }

                $rows[] = [
                    'student_id'  => $studentId,
                    'student'     => $studentInfo['name'],
                    'phone'       => $studentInfo['phone'],
                    'course'      => $courseName,
                    'inst'        => $sr['installment_no'] ?? '',
                    'due_date'    => $dueDate,
                    'due'         => $scaledDue,
                    'paid'        => mdn_money($paidForLine),
                    'outstanding' => $outstanding,
                ];
            }
        }
    }
} else {
    $debugMessage = 'Required tables missing: studentss, student_fee_plans, or payment_schedule.';
}

usort($rows, function($a, $b) {
    return strcmp((string)$a['due_date'], (string)$b['due_date'])
        ?: strcmp((string)$a['student'], (string)$b['student']);
});

foreach ($rows as $r) {
    $totalDue += (float)$r['due'];
    $totalPaid += (float)$r['paid'];
    $totalOutstanding += (float)$r['outstanding'];
}

$totalDue = mdn_money($totalDue);
$totalPaid = mdn_money($totalPaid);
$totalOutstanding = mdn_money($totalOutstanding);

if ($EXPORT) {
    $filename = 'monthly_due_new_' . $monthKey . '.csv';

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="'.$filename.'"');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Student ID', 'Student', 'Phone', 'Course', 'Inst#', 'Due Date', 'Due', 'Paid', 'Outstanding']);

    foreach ($rows as $r) {
        fputcsv($out, [
            $r['student_id'],
            $r['student'],
            $r['phone'],
            $r['course'],
            $r['inst'],
            $r['due_date'],
            $r['due'],
            $r['paid'],
            $r['outstanding'],
        ]);
    }

    fputcsv($out, ['', 'TOTALS', '', '', '', '', $totalDue, $totalPaid, $totalOutstanding]);
    fclose($out);
    exit;
}

$ACTIVE = 'montly_due';
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Monthly Due — <?= mdn_h($monthLabel) ?></title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css" rel="stylesheet">

    <style>
        body{background:#f4f7fb;}
        .due-page{padding:24px;}
        .due-hero{
            background:linear-gradient(135deg,#102a56 0%,#1d4ed8 58%,#38bdf8 100%);
            color:#fff;border-radius:24px;padding:26px;
            box-shadow:0 18px 45px rgba(15,23,42,.18);
            margin-bottom:18px;
        }
        .due-hero h4{font-weight:900;margin:0;letter-spacing:-.03em;}
        .due-hero p{margin:6px 0 0;opacity:.86;font-size:.9rem;}
        .filter-card,.summary-card,.table-card{
            border:0;border-radius:22px;
            box-shadow:0 10px 30px rgba(15,23,42,.07);
        }
        .summary-card{padding:18px;background:#fff;}
        .summary-label{
            color:#64748b;font-size:.78rem;font-weight:800;
            text-transform:uppercase;letter-spacing:.06em;
        }
        .summary-value{font-size:1.35rem;font-weight:900;color:#0f172a;margin-top:4px;}
        .summary-icon{
            width:42px;height:42px;border-radius:14px;
            display:inline-flex;align-items:center;justify-content:center;
            background:#eff6ff;color:#2563eb;font-size:1.15rem;
        }
        .table-card{overflow:hidden;background:#fff;}
        .due-table{margin-bottom:0;}
        .due-table thead th{
            background:#f8fafc;color:#334155;font-size:.76rem;
            text-transform:uppercase;letter-spacing:.07em;font-weight:900;
            padding:14px;border-bottom:1px solid #e5e7eb;white-space:nowrap;
        }
        .due-table tbody td{
            padding:14px;vertical-align:middle;border-color:#edf2f7;font-size:.92rem;
        }
        .due-table tbody tr:hover{background:#fbfdff;}
        .student-name{font-weight:850;color:#0f172a;line-height:1.2;}
        .student-id{color:#64748b;font-size:.8rem;margin-top:2px;}
        .course-pill{
            display:inline-block;max-width:280px;padding:6px 10px;border-radius:999px;
            background:#eef4ff;color:#1d4ed8;font-size:.8rem;font-weight:800;
            white-space:nowrap;overflow:hidden;text-overflow:ellipsis;
        }
        .inst-badge{
            min-width:38px;display:inline-flex;justify-content:center;
            padding:5px 9px;border-radius:10px;background:#f1f5f9;
            color:#334155;font-weight:800;
        }
        .amount-due{font-weight:850;color:#0f172a;}
        .amount-paid{font-weight:850;color:#15803d;}
        .amount-out{font-weight:900;color:#dc2626;}
        .empty-state{padding:52px 20px;text-align:center;color:#64748b;}
        .empty-state i{font-size:2.4rem;color:#22c55e;margin-bottom:10px;}
        .btn-soft{border-radius:14px;font-weight:800;padding:9px 14px;}
        .form-control,.btn{border-radius:14px;}
        @media(max-width:768px){
            .due-page{padding:12px;}
            .due-hero{padding:20px;border-radius:18px;}
        }
    </style>
</head>

<body>
<?php
include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>

<main class="ec-main">
<div class="container-fluid due-page">

    <div class="due-hero">
        <div class="d-flex flex-wrap justify-content-between align-items-center gap-3">
            <div>
                <h4>Monthly Due — <?= mdn_h($monthLabel) ?></h4>
                <p>Selected month dues only. Full paid students are automatically removed.</p>
            </div>

            <div class="d-flex gap-2">
                <a class="btn btn-light btn-sm btn-soft"
                   href="?y=<?= (int)$year ?>&m=<?= (int)$month ?>&export=csv">
                    <i class="bi bi-download me-1"></i> Export CSV
                </a>

                <a class="btn btn-outline-light btn-sm btn-soft" href="javascript:history.back()">
                    <i class="bi bi-arrow-left me-1"></i> Back
                </a>
            </div>
        </div>
    </div>

    <form method="get" class="card filter-card mb-3">
        <div class="card-body">
            <div class="row g-2 align-items-end">
                <div class="col-md-2 col-sm-4">
                    <label class="form-label small fw-bold text-muted">Year</label>
                    <input type="number" name="y" value="<?= (int)$year ?>" min="2000" max="2100" class="form-control">
                </div>

                <div class="col-md-2 col-sm-4">
                    <label class="form-label small fw-bold text-muted">Month</label>
                    <input type="number" name="m" value="<?= (int)$month ?>" min="1" max="12" class="form-control">
                </div>

                <div class="col-md-auto col-sm-4">
                    <button class="btn btn-primary px-4">
                        <i class="bi bi-funnel me-1"></i> Apply
                    </button>
                </div>

                <div class="col-md-auto col-sm-4">
                    <a href="monthly_due_new.php" class="btn btn-outline-secondary px-4">Clear</a>
                </div>
            </div>
        </div>
    </form>

    <div class="row g-3 mb-3">
        <div class="col-md-3 col-sm-6">
            <div class="summary-card">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <div class="summary-label">Rows</div>
                        <div class="summary-value"><?= count($rows) ?></div>
                    </div>
                    <div class="summary-icon"><i class="bi bi-list-check"></i></div>
                </div>
            </div>
        </div>

        <div class="col-md-3 col-sm-6">
            <div class="summary-card">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <div class="summary-label">Total Due</div>
                        <div class="summary-value"><?= number_format($totalDue, 2) ?></div>
                    </div>
                    <div class="summary-icon"><i class="bi bi-cash-stack"></i></div>
                </div>
            </div>
        </div>

        <div class="col-md-3 col-sm-6">
            <div class="summary-card">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <div class="summary-label">Paid</div>
                        <div class="summary-value"><?= number_format($totalPaid, 2) ?></div>
                    </div>
                    <div class="summary-icon"><i class="bi bi-check2-circle"></i></div>
                </div>
            </div>
        </div>

        <div class="col-md-3 col-sm-6">
            <div class="summary-card">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <div class="summary-label">Outstanding</div>
                        <div class="summary-value"><?= number_format($totalOutstanding, 2) ?></div>
                    </div>
                    <div class="summary-icon"><i class="bi bi-exclamation-circle"></i></div>
                </div>
            </div>
        </div>
    </div>

    <div class="table-card">
        <div class="table-responsive">
            <table class="table due-table align-middle">
                <thead>
                    <tr>
                        <th>Student</th>
                        <th>Phone</th>
                        <th>Course</th>
                        <th>Inst#</th>
                        <th>Due Date</th>
                        <th class="text-end">Due</th>
                        <th class="text-end">Paid</th>
                        <th class="text-end">Outstanding</th>
                    </tr>
                </thead>

                <tbody>
                <?php if (!$rows): ?>
                    <tr>
                        <td colspan="8">
                            <div class="empty-state">
                                <i class="bi bi-check-circle-fill d-block"></i>
                                <strong>No due records for <?= mdn_h($monthLabel) ?>.</strong>
                                <div class="small mt-1">All selected month dues are clear.</div>
                            </div>
                        </td>
                    </tr>
                <?php else: foreach ($rows as $r): ?>
                    <tr>
                        <td>
                            <div class="student-name"><?= mdn_h($r['student']) ?></div>
                            <div class="student-id">#<?= (int)$r['student_id'] ?></div>
                        </td>
                        <td><?= mdn_h($r['phone']) ?></td>
                        <td><span class="course-pill"><?= mdn_h($r['course']) ?></span></td>
                        <td><span class="inst-badge"><?= mdn_h($r['inst']) ?></span></td>
                        <td><?= mdn_h($r['due_date']) ?></td>
                        <td class="text-end amount-due"><?= number_format((float)$r['due'], 2) ?></td>
                        <td class="text-end amount-paid"><?= number_format((float)$r['paid'], 2) ?></td>
                        <td class="text-end amount-out"><?= number_format((float)$r['outstanding'], 2) ?></td>
                    </tr>
                <?php endforeach; endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <?php if ($DEBUG || $debugMessage !== ''): ?>
        <div class="alert alert-warning mt-3 small">
            <strong>Debug:</strong>
            <?= mdn_h($debugMessage ?: 'Page uses real payable from student_fee_plans and allocates payments across schedule lines.') ?>
        </div>
    <?php endif; ?>

</div>
</main>
</body>
</html>