<?php
declare(strict_types=1);

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

require_permission($conn, 'loyalty');

function loyalty_transaction_type_label(string $type): string
{
    if ($type === 'earn') {
        return ui_text('ได้รับแต้ม', 'Earn');
    }

    if ($type === 'redeem') {
        return ui_text('ใช้แต้ม', 'Redeem');
    }

    if ($type === 'adjust') {
        return ui_text('ปรับแต้ม', 'Adjust');
    }

    return $type;
}

$customers = array();
$customerTypes = '';
$customerParams = array();
$customerOrderJoin = "LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'";
$branchCondition = auth_branch_condition($conn, 'o', $customerTypes, $customerParams);
if ($branchCondition !== '') {
    $customerOrderJoin .= " AND {$branchCondition}";
}
$customerWhereSql = $branchCondition !== '' ? 'WHERE o.order_id IS NOT NULL' : '';

$customersStmt = db_prepare($conn, "
    SELECT
        c.customer_id,
        c.name,
        c.phone,
        c.loyalty_points,
        COUNT(DISTINCT o.order_id) AS visit_count,
        COALESCE(SUM(o.total_price), 0) AS lifetime_value,
        MAX(o.order_date) AS latest_visit
    FROM customers c
    {$customerOrderJoin}
    {$customerWhereSql}
    GROUP BY c.customer_id, c.name, c.phone, c.loyalty_points
    ORDER BY c.loyalty_points DESC, latest_visit DESC
    LIMIT 50
");
if ($customerTypes !== '') {
    $customersStmt->bind_param($customerTypes, ...$customerParams);
}
$customersStmt->execute();
$customersResult = $customersStmt->get_result();

if ($customersResult instanceof mysqli_result) {
    while ($row = $customersResult->fetch_assoc()) {
        $customers[] = $row;
    }
}
$customersStmt->close();

$transactions = array();
$transactionTypes = '';
$transactionParams = array();
$transactionWhere = array();
$branchCondition = auth_branch_condition($conn, 'o', $transactionTypes, $transactionParams);
if ($branchCondition !== '') {
    $transactionWhere[] = $branchCondition;
}
$transactionWhereSql = empty($transactionWhere) ? '' : 'WHERE ' . implode(' AND ', $transactionWhere);

$transactionsStmt = db_prepare($conn, "
    SELECT
        lt.points,
        lt.transaction_type,
        lt.description,
        lt.created_at,
        c.name AS customer_name,
        lt.order_id
    FROM loyalty_transactions lt
    INNER JOIN customers c ON c.customer_id = lt.customer_id
    LEFT JOIN orders o ON o.order_id = lt.order_id
    {$transactionWhereSql}
    ORDER BY lt.created_at DESC, lt.transaction_id DESC
    LIMIT 20
");
if ($transactionTypes !== '') {
    $transactionsStmt->bind_param($transactionTypes, ...$transactionParams);
}
$transactionsStmt->execute();
$transactionsResult = $transactionsStmt->get_result();

if ($transactionsResult instanceof mysqli_result) {
    while ($row = $transactionsResult->fetch_assoc()) {
        $transactions[] = $row;
    }
}
$transactionsStmt->close();

$pageTitle = ui_text('สะสมแต้ม', 'Loyalty');
$pageHeading = ui_text('ภาพรวมระบบสะสมแต้ม', 'Loyalty overview');
$pageDescription = ui_text(
    'ลูกค้าจะได้รับแต้มอัตโนมัติเมื่อปิดงานสำเร็จ กติกาปัจจุบันคือ 1 แต้มต่อยอดใช้จ่ายทุก 100 บาท',
    'Customers earn points automatically when a job is completed. Current rule: 1 point per 100 THB.'
);
$newBookingLabel = ui_text('เปิดบิลใหม่', 'New booking');
$membersLabel = ui_text('สมาชิก', 'Members');
$membersHelperLabel = ui_text('ลูกค้าอันดับสูงสุดตามยอดแต้มสะสม', 'Top customers by point balance.');
$noCustomersLabel = ui_text('ยังไม่มีข้อมูลลูกค้า', 'No customers yet.');
$customerLabel = ui_text('ลูกค้า', 'Customer');
$levelLabel = ui_text('ระดับสมาชิก', 'Level');
$pointsLabel = ui_text('แต้ม', 'Points');
$visitsLabel = ui_text('จำนวนครั้ง', 'Visits');
$lifetimeValueLabel = ui_text('มูลค่ารวมสะสม', 'Lifetime value');
$latestVisitLabel = ui_text('ใช้บริการล่าสุด', 'Latest visit');
$recentPointActivityLabel = ui_text('ความเคลื่อนไหวแต้มล่าสุด', 'Recent point activity');
$recentPointActivityHelperLabel = ui_text('รายการได้รับแต้ม ใช้แต้ม หรือปรับแต้มล่าสุด', 'Latest earn/redeem/adjust events.');
$noPointActivityLabel = ui_text('ยังไม่มีความเคลื่อนไหวของแต้ม', 'No point activity yet.');
$dateLabel = ui_text('วันที่', 'Date');
$typeLabel = ui_text('ประเภท', 'Type');

render_page_start($pageTitle, 'loyalty', array('translation_buffer' => false));
?>

<section class="page-hero">
    <div>
        <h1><?= e($pageHeading) ?></h1>
        <p><?= e($pageDescription) ?></p>
    </div>
    <div class="hero-actions">
        <a class="button button-secondary" href="booking.php"><?= e($newBookingLabel) ?></a>
    </div>
</section>

<div class="content-grid">
    <section class="table-card">
        <div class="section-head" style="padding: 18px 18px 0;">
            <div>
                <h2 class="section-title"><?= e($membersLabel) ?></h2>
                <p class="helper"><?= e($membersHelperLabel) ?></p>
            </div>
        </div>

        <?php if (empty($customers)): ?>
            <div class="empty-state" style="margin: 18px;"><?= e($noCustomersLabel) ?></div>
        <?php else: ?>
            <table>
                <thead>
                <tr>
                    <th><?= e($customerLabel) ?></th>
                    <th><?= e($levelLabel) ?></th>
                    <th><?= e($pointsLabel) ?></th>
                    <th><?= e($visitsLabel) ?></th>
                    <th><?= e($lifetimeValueLabel) ?></th>
                    <th><?= e($latestVisitLabel) ?></th>
                </tr>
                </thead>
                <tbody>
                <?php foreach ($customers as $customer): ?>
                    <tr>
                        <td>
                            <div class="stack">
                                <strong><?= e((string) $customer['name']) ?></strong>
                                <span class="muted"><?= e((string) ($customer['phone'] ?? '-')) ?></span>
                            </div>
                        </td>
                        <td><?= e(member_level((int) $customer['loyalty_points'])) ?></td>
                        <td><?= (int) $customer['loyalty_points'] ?></td>
                        <td><?= (int) $customer['visit_count'] ?></td>
                        <td><?= format_money($customer['lifetime_value']) ?></td>
                        <td><?= e(format_datetime($customer['latest_visit'] ? (string) $customer['latest_visit'] : null)) ?></td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </section>

    <aside class="table-card">
        <div class="section-head" style="padding: 18px 18px 0;">
            <div>
                <h2 class="section-title"><?= e($recentPointActivityLabel) ?></h2>
                <p class="helper"><?= e($recentPointActivityHelperLabel) ?></p>
            </div>
        </div>

        <?php if (empty($transactions)): ?>
            <div class="empty-state" style="margin: 18px;"><?= e($noPointActivityLabel) ?></div>
        <?php else: ?>
            <table style="min-width: 520px;">
                <thead>
                <tr>
                    <th><?= e($dateLabel) ?></th>
                    <th><?= e($customerLabel) ?></th>
                    <th><?= e($typeLabel) ?></th>
                    <th><?= e($pointsLabel) ?></th>
                </tr>
                </thead>
                <tbody>
                <?php foreach ($transactions as $transaction): ?>
                    <tr>
                        <td><?= e(format_datetime((string) $transaction['created_at'])) ?></td>
                        <td><?= e((string) $transaction['customer_name']) ?></td>
                        <td><?= e(loyalty_transaction_type_label((string) $transaction['transaction_type'])) ?></td>
                        <td><?= (int) $transaction['points'] ?></td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </aside>
</div>

<?php render_page_end(); ?>
