<?php
declare(strict_types=1);

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

require_permission($conn, 'report_service');

ensure_order_branch_column($conn);
ensure_order_bill_number_column($conn);
ensure_car_profile_columns($conn);

function report_rows(mysqli $conn, string $sql, string $types, array $params): array
{
    $stmt = db_prepare($conn, $sql);

    if ($types !== '') {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}

function report_date_value(string $value, string $default): string
{
    $timestamp = strtotime($value);

    if ($timestamp === false) {
        return $default;
    }

    return date('Y-m-d', $timestamp);
}

function report_month_value(string $value, string $default): string
{
    if (!preg_match('/^\d{4}-\d{2}$/', $value)) {
        return $default;
    }

    $timestamp = strtotime($value . '-01');

    if ($timestamp === false) {
        return $default;
    }

    return date('Y-m', $timestamp);
}

function report_year_value(string $value, string $default): string
{
    if (!preg_match('/^\d{4}$/', $value)) {
        return $default;
    }

    $year = (int) $value;

    if ($year < 2000 || $year > 2100) {
        return $default;
    }

    return (string) $year;
}

function report_service_category_label(string $category): string
{
    return $category === 'History'
        ? ui_text('ประวัติ', 'History')
        : $category;
}

function report_service_branch_label(string $branchName): string
{
    $branchName = trim($branchName);

    if ($branchName === '' || $branchName === 'No branch') {
        return ui_text('ไม่ระบุสาขา', 'No branch');
    }

    return $branchName;
}

$period = (string) ($_GET['period'] ?? 'daily');
$allowedPeriods = array('daily', 'monthly', 'yearly');
if (!in_array($period, $allowedPeriods, true)) {
    $period = 'daily';
}

$today = date('Y-m-d');
$currentMonth = date('Y-m');
$currentYear = date('Y');
$shouldLoadReport = isset($_GET['load']) && (string) $_GET['load'] === '1';
$dateValue = report_date_value((string) ($_GET['date'] ?? $today), $today);
$monthValue = report_month_value((string) ($_GET['month'] ?? $currentMonth), $currentMonth);
$yearValue = report_year_value((string) ($_GET['year'] ?? $currentYear), $currentYear);

if ($period === 'monthly') {
    $startAt = date('Y-m-01 00:00:00', strtotime($monthValue . '-01'));
    $endAt = date('Y-m-d 00:00:00', strtotime($startAt . ' +1 month'));
    $periodLabel = localized_year_month_label($startAt);
} elseif ($period === 'yearly') {
    $startAt = $yearValue . '-01-01 00:00:00';
    $endAt = ((int) $yearValue + 1) . '-01-01 00:00:00';
    $periodLabel = $yearValue;
} else {
    $startAt = $dateValue . ' 00:00:00';
    $endAt = date('Y-m-d 00:00:00', strtotime($startAt . ' +1 day'));
    $periodLabel = date('d/m/Y', strtotime($startAt));
}

$branchNames = authorized_order_branch_options($conn);

$serviceOptions = array();
$servicesResult = $conn->query("
    SELECT service_id, service_name, category
    FROM services
    ORDER BY category ASC, service_name ASC
");

if ($servicesResult instanceof mysqli_result) {
    while ($row = $servicesResult->fetch_assoc()) {
        $serviceId = (int) $row['service_id'];
        $serviceOptions[$serviceId] = array(
            'name' => (string) $row['service_name'],
            'category' => (string) $row['category'],
        );
    }
}

$historyServicesResult = $conn->query("
    SELECT DISTINCT service_id, service_name_snapshot
    FROM order_details
    ORDER BY service_name_snapshot ASC
");

if ($historyServicesResult instanceof mysqli_result) {
    while ($row = $historyServicesResult->fetch_assoc()) {
        $serviceId = (int) $row['service_id'];

        if ($serviceId > 0 && !isset($serviceOptions[$serviceId])) {
            $serviceOptions[$serviceId] = array(
                'name' => (string) $row['service_name_snapshot'],
                'category' => 'History',
            );
        }
    }
}

$branchFilter = trim((string) ($_GET['branch_name'] ?? ''));
if ($branchFilter !== '' && !in_array($branchFilter, $branchNames, true)) {
    $branchFilter = '';
}

$serviceFilter = filter_input(INPUT_GET, 'service_id', FILTER_VALIDATE_INT);
if (!$serviceFilter || !isset($serviceOptions[(int) $serviceFilter])) {
    $serviceFilter = 0;
}

$where = array(
    'o.order_date >= ?',
    'o.order_date < ?',
    "o.status <> 'cancelled'",
);
$types = 'ss';
$params = array($startAt, $endAt);

$branchAccessCondition = auth_branch_condition($conn, 'o', $types, $params);
if ($branchAccessCondition !== '') {
    $where[] = $branchAccessCondition;
}

if ($branchFilter !== '') {
    $where[] = 'o.branch_name = ?';
    $types .= 's';
    $params[] = $branchFilter;
}

if ($serviceFilter > 0) {
    $where[] = 'od.service_id = ?';
    $types .= 'i';
    $params[] = (int) $serviceFilter;
}

$whereSql = implode(' AND ', $where);

$summary = array(
    'booking_count' => 0,
    'service_count' => 0,
    'service_revenue' => 0,
);
$serviceRows = array();
$branchRows = array();
$jobRows = array();

if ($shouldLoadReport) {
    $summaryRows = report_rows($conn, "
        SELECT
            COUNT(DISTINCT o.order_id) AS booking_count,
            COUNT(od.detail_id) AS service_count,
            COALESCE(SUM(od.price), 0) AS service_revenue
        FROM orders o
        INNER JOIN order_details od ON od.order_id = o.order_id
        WHERE {$whereSql}
    ", $types, $params);

    $summary = $summaryRows[0] ?? $summary;

    $serviceRows = report_rows($conn, "
        SELECT
            od.service_id,
            COALESCE(s.service_name, od.service_name_snapshot) AS service_name,
            COALESCE(s.category, 'History') AS category,
            COUNT(od.detail_id) AS usage_count,
            COUNT(DISTINCT o.order_id) AS booking_count,
            COALESCE(SUM(od.price), 0) AS service_revenue
        FROM orders o
        INNER JOIN order_details od ON od.order_id = o.order_id
        LEFT JOIN services s ON s.service_id = od.service_id
        WHERE {$whereSql}
        GROUP BY
            od.service_id,
            s.service_name,
            s.category,
            od.service_name_snapshot
        ORDER BY usage_count DESC, service_revenue DESC, service_name ASC
    ", $types, $params);

    $branchRows = report_rows($conn, "
        SELECT
            COALESCE(NULLIF(o.branch_name, ''), 'No branch') AS branch_name,
            COUNT(DISTINCT o.order_id) AS booking_count,
            COUNT(od.detail_id) AS service_count,
            COALESCE(SUM(od.price), 0) AS service_revenue
        FROM orders o
        INNER JOIN order_details od ON od.order_id = o.order_id
        WHERE {$whereSql}
        GROUP BY COALESCE(NULLIF(o.branch_name, ''), 'No branch')
        ORDER BY service_revenue DESC, service_count DESC, branch_name ASC
    ", $types, $params);

    $jobRows = report_rows($conn, "
        SELECT
            o.order_id,
            o.bill_number,
            o.order_date,
            o.branch_name,
            c.name AS customer_name,
            car.plate_number,
            car.profile_image_path,
            car.profile_image_name,
            GROUP_CONCAT(od.service_name_snapshot ORDER BY od.detail_id SEPARATOR ', ') AS service_names,
            COUNT(od.detail_id) AS service_count,
            COALESCE(SUM(od.price), 0) AS service_revenue
        FROM orders o
        INNER JOIN customers c ON c.customer_id = o.customer_id
        INNER JOIN cars car ON car.car_id = o.car_id
        INNER JOIN order_details od ON od.order_id = o.order_id
        WHERE {$whereSql}
        GROUP BY
            o.order_id,
            o.bill_number,
            o.order_date,
            o.branch_name,
            c.name,
            car.plate_number,
            car.profile_image_path,
            car.profile_image_name
        ORDER BY o.order_date DESC, o.order_id DESC
        LIMIT 100
    ", $types, $params);
}

$pageTitle = ui_text('รายงานการใช้บริการ', 'Service Report');
$pageDescription = ui_text('ดูการใช้บริการรายวัน รายเดือน หรือรายปี พร้อมตัวกรองสาขาและบริการ', 'Service usage by day, month or year with branch and service filters.');
$manageServicesLabel = ui_text('จัดการบริการ', 'Manage services');
$viewByLabel = ui_text('ดูตาม', 'View by');
$dailyLabel = ui_text('รายวัน', 'Daily');
$monthlyLabel = ui_text('รายเดือน', 'Monthly');
$yearlyLabel = ui_text('รายปี', 'Yearly');
$dailyDateLabel = ui_text('วันที่', 'Daily date');
$monthlyPeriodLabel = ui_text('ช่วงเดือน', 'Monthly period');
$yearlyPeriodLabel = ui_text('ช่วงปี', 'Yearly period');
$branchLabel = ui_text('สาขา', 'Branch');
$allBranchesLabel = ui_text('ทุกสาขา', 'All branches');
$serviceLabel = ui_text('บริการ', 'Service');
$allServicesLabel = ui_text('ทุกบริการ', 'All services');
$currentPeriodLabel = ui_text('ช่วงที่ดู', 'Current period');
$viewReportLabel = ui_text('ดูรายงาน', 'View report');
$resetLabel = ui_text('รีเซ็ต', 'Reset');
$loadPromptLabel = ui_text('กดดูรายงานเพื่อโหลดข้อมูลการใช้บริการ', 'Click View report to load service usage data.');
$bookingCountLabel = ui_text('จำนวนบิล', 'Bookings');
$serviceUsesLabel = ui_text('จำนวนครั้งใช้บริการ', 'Service uses');
$serviceRevenueLabel = ui_text('รายได้จากบริการ', 'Service revenue');
$serviceUsageLabel = ui_text('สรุปการใช้บริการ', 'Service usage');
$serviceRowsLabel = ui_text(count($serviceRows) . ' แถวบริการ', count($serviceRows) . ' service row(s)');
$emptyServiceRowsLabel = ui_text('ไม่พบข้อมูลการใช้บริการตามตัวกรองที่เลือก', 'No service usage found for this filter.');
$categoryLabel = ui_text('หมวดหมู่', 'Category');
$usesLabel = ui_text('จำนวนครั้ง', 'Uses');
$revenueLabel = ui_text('รายได้', 'Revenue');
$branchSummaryLabel = ui_text('สรุปตามสาขา', 'Branch summary');
$branchRowsLabel = ui_text(count($branchRows) . ' แถวสาขา', count($branchRows) . ' branch row(s)');
$emptyBranchRowsLabel = ui_text('ไม่พบข้อมูลการใช้งานตามสาขา', 'No branch usage found.');
$recentJobsLabel = ui_text('บิลที่ตรงเงื่อนไขล่าสุด', 'Recent matched jobs');
$recentJobsHelperLabel = ui_text('ล่าสุด ' . count($jobRows) . ' บิล', 'Latest ' . count($jobRows) . ' booking(s)');
$emptyJobsLabel = ui_text('ไม่พบบิลที่ตรงเงื่อนไข', 'No jobs found.');
$billNumberLabel = ui_text('เลขที่บิล', 'Bill no.');
$dateLabel = ui_text('วันที่', 'Date');
$customerPlateLabel = ui_text('ลูกค้า / ทะเบียน', 'Customer / plate');
$servicesLabel = ui_text('บริการ', 'Services');
$selectedServiceName = $serviceFilter > 0 ? ($serviceOptions[(int) $serviceFilter]['name'] ?? $serviceLabel) : $allServicesLabel;
$selectedBranchName = $branchFilter !== '' ? $branchFilter : $allBranchesLabel;

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

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

<section class="form-card sale-report-filters sale-report-toolbar-card">
    <form method="GET" action="report_service.php" class="sale-report-toolbar-form">
        <input type="hidden" name="load" value="1">

        <div class="sale-report-toolbar-head">
            <div>
                <h2 class="section-title"><?= e($pageTitle) ?></h2>
                <p class="helper"><?= e($pageDescription) ?></p>
            </div>
        </div>

        <div class="sale-report-filter-strip">
            <label class="sale-report-filter-pill" for="period">
                <span><?= e($viewByLabel) ?></span>
                <select id="period" name="period">
                    <option value="daily" <?= $period === 'daily' ? 'selected' : '' ?>><?= e($dailyLabel) ?></option>
                    <option value="monthly" <?= $period === 'monthly' ? 'selected' : '' ?>><?= e($monthlyLabel) ?></option>
                    <option value="yearly" <?= $period === 'yearly' ? 'selected' : '' ?>><?= e($yearlyLabel) ?></option>
                </select>
            </label>

            <label class="sale-report-filter-pill <?= $period === 'daily' ? 'is-active' : '' ?>" for="date">
                <span><?= e($dailyDateLabel) ?></span>
                <input id="date" name="date" type="date" value="<?= e($dateValue) ?>">
            </label>

            <label class="sale-report-filter-pill <?= $period === 'monthly' ? 'is-active' : '' ?>" for="month">
                <span><?= e($monthlyPeriodLabel) ?></span>
                <?= render_month_picker('month', 'month', $monthValue, $monthlyPeriodLabel) ?>
            </label>

            <label class="sale-report-filter-pill <?= $period === 'yearly' ? 'is-active' : '' ?>" for="year">
                <span><?= e($yearlyPeriodLabel) ?></span>
                <input id="year" name="year" type="number" min="2000" max="2100" value="<?= e($yearValue) ?>">
            </label>

            <label class="sale-report-filter-pill" for="branch_name">
                <span><?= e($branchLabel) ?></span>
                <select id="branch_name" name="branch_name">
                    <option value=""><?= e($allBranchesLabel) ?></option>
                    <?php foreach ($branchNames as $branchName): ?>
                        <option value="<?= e($branchName) ?>" <?= $branchFilter === $branchName ? 'selected' : '' ?>>
                            <?= e($branchName) ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </label>

            <label class="sale-report-filter-pill" for="service_id">
                <span><?= e($serviceLabel) ?></span>
                <select id="service_id" name="service_id">
                    <option value="0"><?= e($allServicesLabel) ?></option>
                    <?php foreach ($serviceOptions as $serviceId => $service): ?>
                        <option value="<?= (int) $serviceId ?>" <?= (int) $serviceFilter === (int) $serviceId ? 'selected' : '' ?>>
                            <?= e((string) $service['name']) ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </label>
        </div>

        <div class="sale-report-current-scope">
            <span><strong><?= e($currentPeriodLabel) ?>:</strong> <?= e($periodLabel) ?></span>
            <span><strong><?= e($branchLabel) ?>:</strong> <?= e($selectedBranchName) ?></span>
            <span><strong><?= e($serviceLabel) ?>:</strong> <?= e($selectedServiceName) ?></span>
        </div>

        <div class="sale-report-toolbar-actions">
            <a class="button button-secondary app-action-button" href="services.php"><?= app_button_content('wrench', $manageServicesLabel) ?></a>
            <button class="button button-accent app-action-button" type="submit"><?= app_button_content('chart', $viewReportLabel) ?></button>
            <a class="button button-ghost app-action-button" href="report_service.php"><?= app_button_content('rotate-ccw', $resetLabel) ?></a>
        </div>
    </form>
</section>

<?php if (!$shouldLoadReport): ?>
    <section class="empty-state" style="margin-top: 18px;"><?= e($loadPromptLabel) ?></section>
<?php else: ?>
    <section class="grid grid-3" style="margin-top: 18px;">
        <article class="stat-card">
            <p class="stat-label"><?= e($bookingCountLabel) ?></p>
            <p class="stat-value"><?= (int) $summary['booking_count'] ?></p>
        </article>
        <article class="stat-card">
            <p class="stat-label"><?= e($serviceUsesLabel) ?></p>
            <p class="stat-value"><?= (int) $summary['service_count'] ?></p>
        </article>
        <article class="stat-card">
            <p class="stat-label"><?= e($serviceRevenueLabel) ?></p>
            <p class="stat-value"><?= format_money($summary['service_revenue']) ?></p>
        </article>
    </section>

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

        <?php if (empty($serviceRows)): ?>
            <div class="empty-state" style="margin: 18px;"><?= e($emptyServiceRowsLabel) ?></div>
        <?php else: ?>
            <table>
                <thead>
                <tr>
                    <th><?= e($serviceLabel) ?></th>
                    <th><?= e($categoryLabel) ?></th>
                    <th><?= e($usesLabel) ?></th>
                    <th><?= e($bookingCountLabel) ?></th>
                    <th><?= e($revenueLabel) ?></th>
                </tr>
                </thead>
                <tbody>
                <?php foreach ($serviceRows as $row): ?>
                    <tr>
                        <td><strong><?= e((string) $row['service_name']) ?></strong></td>
                        <td><?= e(report_service_category_label((string) $row['category'])) ?></td>
                        <td><?= (int) $row['usage_count'] ?></td>
                        <td><?= (int) $row['booking_count'] ?></td>
                        <td><?= format_money($row['service_revenue']) ?></td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </section>

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

            <?php if (empty($branchRows)): ?>
                <div class="empty-state" style="margin: 18px;"><?= e($emptyBranchRowsLabel) ?></div>
            <?php else: ?>
                <table style="min-width: 620px;">
                    <thead>
                    <tr>
                        <th><?= e($branchLabel) ?></th>
                        <th><?= e($bookingCountLabel) ?></th>
                        <th><?= e($serviceUsesLabel) ?></th>
                        <th><?= e($revenueLabel) ?></th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($branchRows as $row): ?>
                        <tr>
                            <td><strong><?= e(report_service_branch_label((string) $row['branch_name'])) ?></strong></td>
                            <td><?= (int) $row['booking_count'] ?></td>
                            <td><?= (int) $row['service_count'] ?></td>
                            <td><?= format_money($row['service_revenue']) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            <?php endif; ?>
        </section>

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

            <?php if (empty($jobRows)): ?>
                <div class="empty-state" style="margin: 18px;"><?= e($emptyJobsLabel) ?></div>
            <?php else: ?>
                <table style="min-width: 760px;">
                    <thead>
                    <tr>
                        <th><?= e($billNumberLabel) ?></th>
                        <th><?= e($dateLabel) ?></th>
                        <th><?= e($customerPlateLabel) ?></th>
                        <th><?= e($branchLabel) ?></th>
                        <th><?= e($servicesLabel) ?></th>
                        <th><?= e($revenueLabel) ?></th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($jobRows as $row): ?>
                        <tr>
                            <td><a href="job_details.php?id=<?= (int) $row['order_id'] ?>"><?= e((string) ($row['bill_number'] ?: '#' . (int) $row['order_id'])) ?></a></td>
                            <td><?= e(format_datetime((string) $row['order_date'])) ?></td>
                            <td>
                                <div class="vehicle-profile-inline">
                                    <?= car_profile_photo_html($row, 'vehicle-profile-photo vehicle-profile-photo--sm', 'vehicle-profile-photo-placeholder vehicle-profile-photo-placeholder--sm') ?>
                                    <div class="stack">
                                        <strong><?= e((string) $row['customer_name']) ?></strong>
                                        <span class="muted"><?= e((string) $row['plate_number']) ?></span>
                                    </div>
                                </div>
                            </td>
                            <td><?= e(report_service_branch_label((string) ($row['branch_name'] ?? ''))) ?></td>
                            <td><?= e((string) ($row['service_names'] ?? '-')) ?></td>
                            <td><?= format_money($row['service_revenue']) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            <?php endif; ?>
        </section>
    </div>
<?php endif; ?>

<?php render_page_end(); ?>
