<?php
session_start();
require_once 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$user_id = $_SESSION['user_id'];

// Handle AJAX actions
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    header('Content-Type: application/json');
    
    $action = $_POST['action'];
    
    try {
        switch($action) {
            case 'send_reminder':
                $order_id = (int)$_POST['order_id'];
                $reminder_type = $conn->real_escape_string($_POST['reminder_type']);
                
                // Update reminder history
                $sql = "INSERT INTO payment_reminders (order_id, reminder_type, sent_by, sent_at, status) 
                        VALUES (?, ?, ?, NOW(), 'SENT')";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("isi", $order_id, $reminder_type, $user_id);
                $stmt->execute();
                
                // Log activity
                $sql_log = "INSERT INTO activity_log (user_id, action, module, record_id, new_values, ip_address, user_agent, logged_at) 
                           VALUES (?, 'OTHER', 'PAYMENT_REMINDERS', ?, ?, ?, ?, NOW())";
                $stmt_log = $conn->prepare($sql_log);
                $log_data = json_encode(['reminder_type' => $reminder_type]);
                $stmt_log->bind_param("isss", $user_id, $order_id, $log_data, $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_USER_AGENT']);
                $stmt_log->execute();
                
                echo json_encode(['success' => true, 'message' => 'Payment reminder sent successfully']);
                break;
                
            case 'apply_discount':
                $order_id = (int)$_POST['order_id'];
                $discount_amount = floatval($_POST['discount_amount']);
                $discount_reason = $conn->real_escape_string($_POST['discount_reason']);
                
                // Get current order details
                $sql = "SELECT total_amount, paid_amount FROM orders WHERE order_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $order_id);
                $stmt->execute();
                $order = $stmt->get_result()->fetch_assoc();
                
                if (!$order) {
                    throw new Exception('Order not found');
                }
                
                $new_total = $order['total_amount'] - $discount_amount;
                $new_balance = $new_total - $order['paid_amount'];
                
                // Update order total
                $sql = "UPDATE orders SET total_amount = ?, balance_amount = ?, discount_amount = discount_amount + ? WHERE order_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("dddi", $new_total, $new_balance, $discount_amount, $order_id);
                $stmt->execute();
                
                // Record discount
                $sql = "INSERT INTO order_discounts (order_id, discount_amount, discount_reason, applied_by, applied_at) 
                        VALUES (?, ?, ?, ?, NOW())";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("idsi", $order_id, $discount_amount, $discount_reason, $user_id);
                $stmt->execute();
                
                // Log activity
                $sql_log = "INSERT INTO activity_log (user_id, action, module, record_id, new_values, ip_address, user_agent, logged_at) 
                           VALUES (?, 'UPDATE', 'ORDERS', ?, ?, ?, ?, NOW())";
                $stmt_log = $conn->prepare($sql_log);
                $log_data = json_encode(['discount_amount' => $discount_amount, 'reason' => $discount_reason]);
                $stmt_log->bind_param("isss", $user_id, $order_id, $log_data, $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_USER_AGENT']);
                $stmt_log->execute();
                
                echo json_encode(['success' => true, 'message' => 'Discount applied successfully']);
                break;
                
            case 'write_off':
                $order_id = (int)$_POST['order_id'];
                $writeoff_amount = floatval($_POST['writeoff_amount']);
                $writeoff_reason = $conn->real_escape_string($_POST['writeoff_reason']);
                
                // Get current order details
                $sql = "SELECT balance_amount FROM orders WHERE order_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $order_id);
                $stmt->execute();
                $order = $stmt->get_result()->fetch_assoc();
                
                if (!$order) {
                    throw new Exception('Order not found');
                }
                
                $new_balance = $order['balance_amount'] - $writeoff_amount;
                
                // Update order balance
                $sql = "UPDATE orders SET balance_amount = ? WHERE order_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("di", $new_balance, $order_id);
                $stmt->execute();
                
                // Record write-off
                $sql = "INSERT INTO debt_writeoffs (order_id, writeoff_amount, writeoff_reason, approved_by, approved_at) 
                        VALUES (?, ?, ?, ?, NOW())";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("idsi", $order_id, $writeoff_amount, $writeoff_reason, $user_id);
                $stmt->execute();
                
                // Update payment status if fully written off
                if ($new_balance <= 0) {
                    $sql = "UPDATE orders SET payment_status = 'PAID' WHERE order_id = ?";
                    $stmt = $conn->prepare($sql);
                    $stmt->bind_param("i", $order_id);
                    $stmt->execute();
                }
                
                // Log activity
                $sql_log = "INSERT INTO activity_log (user_id, action, module, record_id, new_values, ip_address, user_agent, logged_at) 
                           VALUES (?, 'UPDATE', 'ORDERS', ?, ?, ?, ?, NOW())";
                $stmt_log = $conn->prepare($sql_log);
                $log_data = json_encode(['writeoff_amount' => $writeoff_amount, 'reason' => $writeoff_reason]);
                $stmt_log->bind_param("isss", $user_id, $order_id, $log_data, $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_USER_AGENT']);
                $stmt_log->execute();
                
                echo json_encode(['success' => true, 'message' => 'Debt written off successfully']);
                break;
                
            default:
                throw new Exception('Invalid action');
        }
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
    exit();
}

// Get filter parameters
$search = isset($_GET['search']) ? $_GET['search'] : '';
$aging_filter = isset($_GET['aging']) ? $_GET['aging'] : '';
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';
$customer_type_filter = isset($_GET['customer_type']) ? $_GET['customer_type'] : '';

// Check if required tables exist, create them if not
$required_tables = ['payment_reminders', 'order_discounts', 'debt_writeoffs', 'payment_refunds'];
foreach ($required_tables as $table) {
    $check_table = $conn->query("SHOW TABLES LIKE '$table'");
    if ($check_table->num_rows == 0) {
        // Create the missing table based on which one it is
        $create_sql = "";
        switch ($table) {
            case 'payment_reminders':
                $create_sql = "CREATE TABLE `payment_reminders` (
                    `reminder_id` int NOT NULL AUTO_INCREMENT,
                    `order_id` int NOT NULL,
                    `reminder_type` enum('GENTLE','FIRM','FINAL','CUSTOM') NOT NULL,
                    `reminder_message` text,
                    `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                    `sent_by` int NOT NULL,
                    `status` enum('SENT','DELIVERED','FAILED') DEFAULT 'SENT',
                    PRIMARY KEY (`reminder_id`),
                    KEY `fk_reminder_order` (`order_id`),
                    KEY `fk_reminder_sent_by` (`sent_by`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
                break;
            case 'order_discounts':
                $create_sql = "CREATE TABLE `order_discounts` (
                    `discount_id` int NOT NULL AUTO_INCREMENT,
                    `order_id` int NOT NULL,
                    `discount_amount` decimal(8,2) NOT NULL,
                    `discount_reason` varchar(255) NOT NULL,
                    `applied_by` int NOT NULL,
                    `applied_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`discount_id`),
                    KEY `fk_discount_order` (`order_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
                break;
            case 'debt_writeoffs':
                $create_sql = "CREATE TABLE `debt_writeoffs` (
                    `writeoff_id` int NOT NULL AUTO_INCREMENT,
                    `order_id` int NOT NULL,
                    `writeoff_amount` decimal(8,2) NOT NULL,
                    `writeoff_reason` varchar(255) NOT NULL,
                    `approved_by` int NOT NULL,
                    `approved_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`writeoff_id`),
                    KEY `fk_writeoff_order` (`order_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
                break;
            case 'payment_refunds':
                $create_sql = "CREATE TABLE `payment_refunds` (
                    `refund_id` int NOT NULL AUTO_INCREMENT,
                    `payment_id` int NOT NULL,
                    `refund_amount` decimal(10,2) NOT NULL,
                    `refund_reason` varchar(255) NOT NULL,
                    `refund_notes` text,
                    `processed_by` int NOT NULL,
                    `processed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                    `status` enum('PENDING','COMPLETED','FAILED','CANCELLED') DEFAULT 'COMPLETED',
                    PRIMARY KEY (`refund_id`),
                    KEY `fk_refund_payment` (`payment_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
                break;
        }
        if ($create_sql) {
            $conn->query($create_sql);
        }
    }
}

// Build base query for outstanding bills
$where_conditions = ["o.balance_amount > 0"];
$params = [];
$param_types = "";

if (!empty($search)) {
    $where_conditions[] = "(o.order_number LIKE ? OR CONCAT(c.first_name, ' ', c.last_name) LIKE ?)";
    $search_param = "%$search%";
    $params[] = $search_param;
    $params[] = $search_param;
    $param_types .= "ss";
}

if (!empty($customer_type_filter)) {
    $where_conditions[] = "c.customer_type = ?";
    $params[] = $customer_type_filter;
    $param_types .= "s";
}

// Get outstanding bills with customer details
$sql_bills = "SELECT 
    o.order_id,
    o.order_number,
    o.order_date,
    o.total_amount,
    o.paid_amount,
    o.balance_amount,
    o.payment_status,
    o.status,
    c.customer_id,
    c.first_name,
    c.last_name,
    c.customer_code,
    c.phone,
    c.email,
    c.customer_type,
    DATEDIFF(CURDATE(), o.order_date) as days_outstanding,
    COALESCE((SELECT COUNT(*) FROM payment_reminders pr WHERE pr.order_id = o.order_id), 0) as reminder_count,
    (SELECT MAX(pr.sent_at) FROM payment_reminders pr WHERE pr.order_id = o.order_id) as last_reminder_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE " . implode(" AND ", $where_conditions);

// Add aging filter
if (!empty($aging_filter)) {
    switch($aging_filter) {
        case '0-30':
            $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) BETWEEN 0 AND 30";
            break;
        case '31-60':
            $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) BETWEEN 31 AND 60";
            break;
        case '61-90':
            $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) BETWEEN 61 AND 90";
            break;
        case '90+':
            $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) > 90";
            break;
    }
}

// Add status filter
if (!empty($status_filter)) {
    if ($status_filter === 'overdue') {
        $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) > 30";
    } elseif ($status_filter === 'current') {
        $sql_bills .= " AND DATEDIFF(CURDATE(), o.order_date) <= 30";
    }
}

$sql_bills .= " ORDER BY days_outstanding DESC, o.balance_amount DESC";

$stmt_bills = $conn->prepare($sql_bills);
if (!empty($params)) {
    $stmt_bills->bind_param($param_types, ...$params);
}
$stmt_bills->execute();
$result_bills = $stmt_bills->get_result();

// Get summary statistics
$sql_summary = "SELECT 
    COUNT(*) as total_outstanding_bills,
    SUM(balance_amount) as total_outstanding_amount,
    SUM(CASE WHEN DATEDIFF(CURDATE(), order_date) > 30 THEN balance_amount ELSE 0 END) as overdue_amount,
    COUNT(CASE WHEN DATEDIFF(CURDATE(), order_date) > 30 THEN 1 END) as overdue_count,
    AVG(DATEDIFF(CURDATE(), order_date)) as avg_days_outstanding
FROM orders 
WHERE balance_amount > 0";

$result_summary = $conn->query($sql_summary);
$summary = $result_summary->fetch_assoc();

// Get aging breakdown
$sql_aging = "SELECT 
    SUM(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 0 AND 30 THEN balance_amount ELSE 0 END) as amount_0_30,
    COUNT(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 0 AND 30 THEN 1 END) as count_0_30,
    SUM(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 31 AND 60 THEN balance_amount ELSE 0 END) as amount_31_60,
    COUNT(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 31 AND 60 THEN 1 END) as count_31_60,
    SUM(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 61 AND 90 THEN balance_amount ELSE 0 END) as amount_61_90,
    COUNT(CASE WHEN DATEDIFF(CURDATE(), order_date) BETWEEN 61 AND 90 THEN 1 END) as count_61_90,
    SUM(CASE WHEN DATEDIFF(CURDATE(), order_date) > 90 THEN balance_amount ELSE 0 END) as amount_90_plus,
    COUNT(CASE WHEN DATEDIFF(CURDATE(), order_date) > 90 THEN 1 END) as count_90_plus
FROM orders 
WHERE balance_amount > 0";

$result_aging = $conn->query($sql_aging);
$aging = $result_aging->fetch_assoc();

// Calculate collection rate (paid vs total billed this month)
$sql_collection = "SELECT 
    SUM(total_amount) as total_billed,
    SUM(paid_amount) as total_collected
FROM orders 
WHERE MONTH(order_date) = MONTH(CURDATE()) AND YEAR(order_date) = YEAR(CURDATE())";

$result_collection = $conn->query($sql_collection);
$collection = $result_collection->fetch_assoc();
$collection_rate = $collection['total_billed'] > 0 ? ($collection['total_collected'] / $collection['total_billed']) * 100 : 0;
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Outstanding Bills - Laundry Management')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <style>
        .page-content {
            background-color: #f8fafc;
        }
        
        .stats-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 1.5rem;
        }
        
        .stats-value {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .stats-label {
            color: #64748b;
            font-size: 0.875rem;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .aging-card {
            background: white;
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 1.5rem;
        }
        
        .aging-header {
            background: #f8fafc;
            padding: 1rem 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            font-weight: 600;
        }
        
        .aging-item {
            padding: 1rem 1.5rem;
            border-bottom: 1px solid #f1f5f9;
            display: flex;
            justify-content: between;
            align-items: center;
        }
        
        .aging-item:last-child {
            border-bottom: none;
        }
        
        .aging-period {
            font-weight: 600;
            color: #1e293b;
        }
        
        .aging-amount {
            font-weight: 700;
            color: #059669;
        }
        
        .aging-count {
            background: #f1f5f9;
            color: #64748b;
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
        }
        
        .bills-table {
            background: white;
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            border: 1px solid #e2e8f0;
        }
        
        .table-header {
            background: #f8fafc;
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .table-title {
            font-size: 1.25rem;
            font-weight: 600;
            margin: 0;
            color: #1e293b;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            color: #64748b;
            font-size: 0.875rem;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .data-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
            font-size: 0.875rem;
        }
        
        .data-table tbody tr:hover {
            background: #f8fafc;
        }
        
        .status-badge {
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
        }
        
        .status-current { background: #dcfce7; color: #166534; }
        .status-overdue { background: #fee2e2; color: #991b1b; }
        .status-critical { background: #fef3c7; color: #92400e; }
        
        .customer-avatar {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            background: #059669;
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 0.875rem;
        }
        
        .action-buttons {
            display: flex;
            gap: 0.5rem;
        }
        
        .btn-action {
            padding: 0.375rem 0.75rem;
            border: 1px solid #e2e8f0;
            background: white;
            border-radius: 6px;
            font-size: 0.75rem;
            font-weight: 500;
            cursor: pointer;
            transition: all 0.2s;
        }
        
        .btn-action:hover {
            background: #f8fafc;
            border-color: #cbd5e1;
        }
        
        .btn-reminder { color: #3b82f6; }
        .btn-discount { color: #059669; }
        .btn-writeoff { color: #dc2626; }
        .btn-payment { color: #7c3aed; }
        
        .filter-section {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 1.5rem;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            border: 1px solid #e2e8f0;
        }
        
        .form-control, .form-select {
            border: 1px solid #d1d5db;
            border-radius: 8px;
            padding: 0.5rem 0.75rem;
            font-size: 0.875rem;
        }
        
        .form-control:focus, .form-select:focus {
            border-color: #059669;
            box-shadow: 0 0 0 3px rgba(5, 150, 105, 0.1);
            outline: none;
        }
        
        .btn-primary {
            background: #059669;
            border-color: #059669;
            padding: 0.5rem 1rem;
            border-radius: 8px;
            font-weight: 500;
        }
        
        .btn-primary:hover {
            background: #047857;
            border-color: #047857;
        }
        
        .modal-content {
            border-radius: 12px;
            border: none;
            box-shadow: 0 10px 25px rgba(0,0,0,0.15);
        }
        
        .modal-header {
            border-bottom: 1px solid #e2e8f0;
            padding: 1.5rem;
        }
        
        .modal-body {
            padding: 1.5rem;
        }
        
        .modal-footer {
            border-top: 1px solid #e2e8f0;
            padding: 1rem 1.5rem;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Outstanding Bills Management</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                        <li class="breadcrumb-item active">Outstanding Bills</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Statistics -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="stats-card">
                                <div class="stats-value text-primary">KES <?php echo number_format($summary['total_outstanding_amount']); ?></div>
                                <div class="stats-label">Total Outstanding</div>
                                <small class="text-muted"><?php echo $summary['total_outstanding_bills']; ?> bills</small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="stats-card">
                                <div class="stats-value text-danger">KES <?php echo number_format($summary['overdue_amount']); ?></div>
                                <div class="stats-label">Overdue Amount</div>
                                <small class="text-muted"><?php echo $summary['overdue_count']; ?> overdue bills</small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="stats-card">
                                <div class="stats-value text-warning"><?php echo round($summary['avg_days_outstanding']); ?> days</div>
                                <div class="stats-label">Average Days Outstanding</div>
                                <small class="text-muted">Across all bills</small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="stats-card">
                                <div class="stats-value text-success"><?php echo number_format($collection_rate, 1); ?>%</div>
                                <div class="stats-label">Collection Rate</div>
                                <small class="text-muted">This month</small>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <!-- Aging Analysis -->
                        <div class="col-xl-4">
                            <div class="aging-card">
                                <div class="aging-header">
                                    Aging Analysis
                                </div>
                                <div class="aging-item">
                                    <div>
                                        <div class="aging-period">0-30 Days</div>
                                        <div class="aging-amount">KES <?php echo number_format($aging['amount_0_30']); ?></div>
                                    </div>
                                    <div class="aging-count"><?php echo $aging['count_0_30']; ?> bills</div>
                                </div>
                                <div class="aging-item">
                                    <div>
                                        <div class="aging-period">31-60 Days</div>
                                        <div class="aging-amount">KES <?php echo number_format($aging['amount_31_60']); ?></div>
                                    </div>
                                    <div class="aging-count"><?php echo $aging['count_31_60']; ?> bills</div>
                                </div>
                                <div class="aging-item">
                                    <div>
                                        <div class="aging-period">61-90 Days</div>
                                        <div class="aging-amount">KES <?php echo number_format($aging['amount_61_90']); ?></div>
                                    </div>
                                    <div class="aging-count"><?php echo $aging['count_61_90']; ?> bills</div>
                                </div>
                                <div class="aging-item">
                                    <div>
                                        <div class="aging-period">90+ Days</div>
                                        <div class="aging-amount text-danger">KES <?php echo number_format($aging['amount_90_plus']); ?></div>
                                    </div>
                                    <div class="aging-count"><?php echo $aging['count_90_plus']; ?> bills</div>
                                </div>
                            </div>
                        </div>

                        <!-- Filters -->
                        <div class="col-xl-8">
                            <div class="filter-section">
                                <form method="GET" class="row g-3">
                                    <div class="col-md-3">
                                        <input type="text" class="form-control" name="search" placeholder="Search orders or customers" value="<?php echo htmlspecialchars($search); ?>">
                                    </div>
                                    <div class="col-md-2">
                                        <select class="form-select" name="aging">
                                            <option value="">All Ages</option>
                                            <option value="0-30" <?php echo $aging_filter === '0-30' ? 'selected' : ''; ?>>0-30 Days</option>
                                            <option value="31-60" <?php echo $aging_filter === '31-60' ? 'selected' : ''; ?>>31-60 Days</option>
                                            <option value="61-90" <?php echo $aging_filter === '61-90' ? 'selected' : ''; ?>>61-90 Days</option>
                                            <option value="90+" <?php echo $aging_filter === '90+' ? 'selected' : ''; ?>>90+ Days</option>
                                        </select>
                                    </div>
                                    <div class="col-md-2">
                                        <select class="form-select" name="status">
                                            <option value="">All Status</option>
                                            <option value="current" <?php echo $status_filter === 'current' ? 'selected' : ''; ?>>Current</option>
                                            <option value="overdue" <?php echo $status_filter === 'overdue' ? 'selected' : ''; ?>>Overdue</option>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <select class="form-select" name="customer_type">
                                            <option value="">All Customers</option>
                                            <option value="Regular" <?php echo $customer_type_filter === 'Regular' ? 'selected' : ''; ?>>Regular</option>
                                            <option value="VIP" <?php echo $customer_type_filter === 'VIP' ? 'selected' : ''; ?>>VIP</option>
                                            <option value="Corporate" <?php echo $customer_type_filter === 'Corporate' ? 'selected' : ''; ?>>Corporate</option>
                                        </select>
                                    </div>
                                    <div class="col-md-2">
                                        <button type="submit" class="btn btn-primary w-100">Filter</button>
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>

                    <!-- Outstanding Bills Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="bills-table">
                                <div class="table-header">
                                    <h5 class="table-title">Outstanding Bills</h5>
                                </div>
                                <div class="table-responsive">
                                    <table class="data-table">
                                        <thead>
                                            <tr>
                                                <th>Customer</th>
                                                <th>Order</th>
                                                <th>Order Date</th>
                                                <th>Total Amount</th>
                                                <th>Paid Amount</th>
                                                <th>Balance</th>
                                                <th>Days Outstanding</th>
                                                <th>Status</th>
                                                <th>Reminders</th>
                                                <th>Actions</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($bill = $result_bills->fetch_assoc()): ?>
                                            <tr>
                                                <td>
                                                    <div class="d-flex align-items-center">
                                                        <div class="customer-avatar me-3">
                                                            <?php echo strtoupper(substr($bill['first_name'], 0, 1) . substr($bill['last_name'], 0, 1)); ?>
                                                        </div>
                                                        <div>
                                                            <div class="fw-semibold"><?php echo $bill['first_name'] . ' ' . $bill['last_name']; ?></div>
                                                            <div class="text-muted small"><?php echo $bill['customer_code']; ?></div>
                                                            <div class="text-muted small"><?php echo $bill['phone']; ?></div>
                                                        </div>
                                                    </div>
                                                </td>
                                                <td>
                                                    <a href="view_order.php?id=<?php echo $bill['order_id']; ?>" class="text-primary fw-semibold text-decoration-none">
                                                        <?php echo $bill['order_number']; ?>
                                                    </a>
                                                    <div class="text-muted small"><?php echo ucfirst($bill['status']); ?></div>
                                                </td>
                                                <td>
                                                    <div><?php echo date('M j, Y', strtotime($bill['order_date'])); ?></div>
                                                    <div class="text-muted small"><?php echo date('g:i A', strtotime($bill['order_date'])); ?></div>
                                                </td>
                                                <td class="fw-semibold">KES <?php echo number_format($bill['total_amount'], 2); ?></td>
                                                <td class="fw-semibold text-success">KES <?php echo number_format($bill['paid_amount'], 2); ?></td>
                                                <td class="fw-bold text-danger">KES <?php echo number_format($bill['balance_amount'], 2); ?></td>
                                                <td>
                                                    <span class="fw-semibold"><?php echo $bill['days_outstanding']; ?> days</span>
                                                </td>
                                                <td>
                                                    <?php
                                                    $status_class = 'status-current';
                                                    $status_text = 'Current';
                                                    
                                                    if ($bill['days_outstanding'] > 90) {
                                                        $status_class = 'status-critical';
                                                        $status_text = 'Critical';
                                                    } elseif ($bill['days_outstanding'] > 30) {
                                                        $status_class = 'status-overdue';
                                                        $status_text = 'Overdue';
                                                    }
                                                    ?>
                                                    <span class="status-badge <?php echo $status_class; ?>"><?php echo $status_text; ?></span>
                                                </td>
                                                <td>
                                                    <div class="fw-semibold"><?php echo $bill['reminder_count']; ?> sent</div>
                                                    <?php if ($bill['last_reminder_date']): ?>
                                                    <div class="text-muted small">Last: <?php echo date('M j', strtotime($bill['last_reminder_date'])); ?></div>
                                                    <?php endif; ?>
                                                </td>
                                                <td>
                                                    <div class="action-buttons">
                                                        <button class="btn-action btn-reminder" onclick="sendReminder(<?php echo $bill['order_id']; ?>)" title="Send Reminder">
                                                            📧
                                                        </button>
                                                        <button class="btn-action btn-discount" onclick="applyDiscount(<?php echo $bill['order_id']; ?>, <?php echo $bill['balance_amount']; ?>)" title="Apply Discount">
                                                            💰
                                                        </button>
                                                        <button class="btn-action btn-writeoff" onclick="writeOffDebt(<?php echo $bill['order_id']; ?>, <?php echo $bill['balance_amount']; ?>)" title="Write Off">
                                                            ❌
                                                        </button>
                                                        <a href="process_payment.php?order_id=<?php echo $bill['order_id']; ?>" class="btn-action btn-payment" title="Record Payment">
                                                            💳
                                                        </a>
                                                    </div>
                                                </td>
                                            </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <!-- Send Reminder Modal -->
    <div class="modal fade" id="reminderModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Send Payment Reminder</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body">
                    <form id="reminderForm">
                        <input type="hidden" id="reminder_order_id">
                        <div class="mb-3">
                            <label class="form-label">Reminder Type</label>
                            <select class="form-select" id="reminder_type" required>
                                <option value="">Select reminder type</option>
                                <option value="GENTLE">Gentle Reminder</option>
                                <option value="FIRM">Firm Reminder</option>
                                <option value="FINAL">Final Notice</option>
                                <option value="CUSTOM">Custom Message</option>
                            </select>
                        </div>
                        <div class="mb-3" id="customMessageDiv" style="display: none;">
                            <label class="form-label">Custom Message</label>
                            <textarea class="form-control" id="custom_message" rows="4" placeholder="Enter your custom reminder message"></textarea>
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="button" class="btn btn-primary" onclick="submitReminder()">Send Reminder</button>
                </div>
            </div>
        </div>
    </div>

    <!-- Apply Discount Modal -->
    <div class="modal fade" id="discountModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Apply Discount</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body">
                    <form id="discountForm">
                        <input type="hidden" id="discount_order_id">
                        <div class="mb-3">
                            <label class="form-label">Outstanding Balance</label>
                            <input type="text" class="form-control" id="current_balance" readonly>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Discount Amount (KES)</label>
                            <input type="number" class="form-control" id="discount_amount" step="0.01" min="0" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Discount Reason</label>
                            <select class="form-select" id="discount_reason" required>
                                <option value="">Select reason</option>
                                <option value="CUSTOMER_LOYALTY">Customer Loyalty</option>
                                <option value="SERVICE_ISSUE">Service Issue Compensation</option>
                                <option value="GOODWILL">Goodwill Gesture</option>
                                <option value="SETTLEMENT">Settlement Discount</option>
                                <option value="PROMOTIONAL">Promotional Discount</option>
                                <option value="OTHER">Other</option>
                            </select>
                        </div>
                        <div class="mb-3" id="otherReasonDiv" style="display: none;">
                            <label class="form-label">Other Reason</label>
                            <input type="text" class="form-control" id="other_reason" placeholder="Specify reason">
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="button" class="btn btn-success" onclick="submitDiscount()">Apply Discount</button>
                </div>
            </div>
        </div>
    </div>

    <!-- Write Off Modal -->
    <div class="modal fade" id="writeoffModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Write Off Debt</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body">
                    <div class="alert alert-warning">
                        <strong>Warning:</strong> This action will write off the debt and cannot be easily reversed. Please ensure proper authorization.
                    </div>
                    <form id="writeoffForm">
                        <input type="hidden" id="writeoff_order_id">
                        <div class="mb-3">
                            <label class="form-label">Outstanding Balance</label>
                            <input type="text" class="form-control" id="writeoff_current_balance" readonly>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Write-off Amount (KES)</label>
                            <input type="number" class="form-control" id="writeoff_amount" step="0.01" min="0" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Write-off Reason</label>
                            <select class="form-select" id="writeoff_reason" required>
                                <option value="">Select reason</option>
                                <option value="UNCOLLECTIBLE">Uncollectible Debt</option>
                                <option value="CUSTOMER_BANKRUPTCY">Customer Bankruptcy</option>
                                <option value="STATUTE_LIMITATIONS">Statute of Limitations</option>
                                <option value="COST_RECOVERY">Cost of Recovery Too High</option>
                                <option value="GOODWILL">Goodwill Write-off</option>
                                <option value="OTHER">Other</option>
                            </select>
                        </div>
                        <div class="mb-3" id="writeoffOtherReasonDiv" style="display: none;">
                            <label class="form-label">Other Reason</label>
                            <textarea class="form-control" id="writeoff_other_reason" rows="3" placeholder="Specify detailed reason for write-off"></textarea>
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="button" class="btn btn-danger" onclick="submitWriteoff()">Write Off Debt</button>
                </div>
            </div>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <script>
        // Send Reminder Function
        function sendReminder(orderId) {
            document.getElementById('reminder_order_id').value = orderId;
            new bootstrap.Modal(document.getElementById('reminderModal')).show();
        }

        // Toggle custom message field
        document.getElementById('reminder_type').addEventListener('change', function() {
            const customDiv = document.getElementById('customMessageDiv');
            if (this.value === 'CUSTOM') {
                customDiv.style.display = 'block';
                document.getElementById('custom_message').required = true;
            } else {
                customDiv.style.display = 'none';
                document.getElementById('custom_message').required = false;
            }
        });

        function submitReminder() {
            const orderId = document.getElementById('reminder_order_id').value;
            const reminderType = document.getElementById('reminder_type').value;
            
            if (!reminderType) {
                alert('Please select a reminder type');
                return;
            }

            fetch('outstanding_bills.php', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/x-www-form-urlencoded',
                },
                body: new URLSearchParams({
                    action: 'send_reminder',
                    order_id: orderId,
                    reminder_type: reminderType
                })
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    alert('Payment reminder sent successfully');
                    bootstrap.Modal.getInstance(document.getElementById('reminderModal')).hide();
                    location.reload();
                } else {
                    alert('Error: ' + data.message);
                }
            })
            .catch(error => {
                console.error('Error:', error);
                alert('An error occurred while sending the reminder');
            });
        }

        // Apply Discount Function
        function applyDiscount(orderId, balance) {
            document.getElementById('discount_order_id').value = orderId;
            document.getElementById('current_balance').value = 'KES ' + balance.toLocaleString();
            document.getElementById('discount_amount').max = balance;
            new bootstrap.Modal(document.getElementById('discountModal')).show();
        }

        // Toggle other reason field for discount
        document.getElementById('discount_reason').addEventListener('change', function() {
            const otherDiv = document.getElementById('otherReasonDiv');
            if (this.value === 'OTHER') {
                otherDiv.style.display = 'block';
                document.getElementById('other_reason').required = true;
            } else {
                otherDiv.style.display = 'none';
                document.getElementById('other_reason').required = false;
            }
        });

        function submitDiscount() {
            const orderId = document.getElementById('discount_order_id').value;
            const discountAmount = document.getElementById('discount_amount').value;
            const discountReason = document.getElementById('discount_reason').value;
            const otherReason = document.getElementById('other_reason').value;

            if (!discountAmount || !discountReason) {
                alert('Please fill in all required fields');
                return;
            }

            if (discountReason === 'OTHER' && !otherReason) {
                alert('Please specify the other reason');
                return;
            }

            const finalReason = discountReason === 'OTHER' ? otherReason : discountReason;

            fetch('outstanding_bills.php', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/x-www-form-urlencoded',
                },
                body: new URLSearchParams({
                    action: 'apply_discount',
                    order_id: orderId,
                    discount_amount: discountAmount,
                    discount_reason: finalReason
                })
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    alert('Discount applied successfully');
                    bootstrap.Modal.getInstance(document.getElementById('discountModal')).hide();
                    location.reload();
                } else {
                    alert('Error: ' + data.message);
                }
            })
            .catch(error => {
                console.error('Error:', error);
                alert('An error occurred while applying the discount');
            });
        }

        // Write Off Function
        function writeOffDebt(orderId, balance) {
            document.getElementById('writeoff_order_id').value = orderId;
            document.getElementById('writeoff_current_balance').value = 'KES ' + balance.toLocaleString();
            document.getElementById('writeoff_amount').max = balance;
            document.getElementById('writeoff_amount').value = balance;
            new bootstrap.Modal(document.getElementById('writeoffModal')).show();
        }

        // Toggle other reason field for writeoff
        document.getElementById('writeoff_reason').addEventListener('change', function() {
            const otherDiv = document.getElementById('writeoffOtherReasonDiv');
            if (this.value === 'OTHER') {
                otherDiv.style.display = 'block';
                document.getElementById('writeoff_other_reason').required = true;
            } else {
                otherDiv.style.display = 'none';
                document.getElementById('writeoff_other_reason').required = false;
            }
        });

        function submitWriteoff() {
            const orderId = document.getElementById('writeoff_order_id').value;
            const writeoffAmount = document.getElementById('writeoff_amount').value;
            const writeoffReason = document.getElementById('writeoff_reason').value;
            const otherReason = document.getElementById('writeoff_other_reason').value;

            if (!writeoffAmount || !writeoffReason) {
                alert('Please fill in all required fields');
                return;
            }

            if (writeoffReason === 'OTHER' && !otherReason) {
                alert('Please specify the detailed reason for write-off');
                return;
            }

            const finalReason = writeoffReason === 'OTHER' ? otherReason : writeoffReason;

            if (!confirm('Are you sure you want to write off this debt? This action cannot be easily reversed.')) {
                return;
            }

            fetch('outstanding_bills.php', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/x-www-form-urlencoded',
                },
                body: new URLSearchParams({
                    action: 'write_off',
                    order_id: orderId,
                    writeoff_amount: writeoffAmount,
                    writeoff_reason: finalReason
                })
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    alert('Debt written off successfully');
                    bootstrap.Modal.getInstance(document.getElementById('writeoffModal')).hide();
                    location.reload();
                } else {
                    alert('Error: ' + data.message);
                }
            })
            .catch(error => {
                console.error('Error:', error);
                alert('An error occurred while writing off the debt');
            });
        }
    </script>
</body>
</html>