<?php

namespace classes;

require_once WD . '/classes/selfserve_schema_bootstrap.php';

use Exception;

class department_wash_count_service
{
    /**
     * @throws Exception
     */
    public function countInDateRange(string $date_start, string $date_end, int $department_id): int
    {
        $rows = $this->countByHourForDepartments($date_start, $date_end, [$department_id]);
        $total = 0;

        foreach ($rows as $row) {
            $total += (int)($row['wash_count'] ?? 0);
        }

        return $total;
    }

    /**
     * @param array<int|string> $department_ids
     * @return array<int,array{department_id:int,hour_bucket:string,wash_count:int}>
     * @throws Exception
     */
    public function countByHourForDepartments(string $date_start, string $date_end, array $department_ids): array
    {
        global $db;

        $this->validateDateRange($date_start, $date_end);
        $normalized_department_ids = $this->normalizeIds($department_ids);
        if ($normalized_department_ids === []) {
            return [];
        }

        selfserve_schema_bootstrap::ensureTables();

        $department_ids_sql = implode(',', $normalized_department_ids);
        $escaped_start = $db->escape_string($date_start);
        $escaped_end = $db->escape_string($date_end);
        $candidate_sql = $this->candidateUnionSql($department_ids_sql, $escaped_start, $escaped_end);

        $sql = "SELECT deduped.department_id,
                       DATE_FORMAT(deduped.counted_at, '%Y-%m-%d %H:00:00') AS hour_bucket,
                       COUNT(*) AS wash_count
                FROM (
                    SELECT dedupe_key,
                           department_id,
                           MIN(counted_at) AS counted_at
                    FROM ($candidate_sql) candidates
                    GROUP BY dedupe_key, department_id
                ) deduped
                GROUP BY deduped.department_id, DATE_FORMAT(deduped.counted_at, '%Y-%m-%d %H:00:00')
                ORDER BY deduped.department_id ASC, hour_bucket ASC";

        $result = $db->query($sql);
        if (!is_object($result) || $result->num_rows === 0) {
            return [];
        }

        $rows = [];
        while ($row = $result->fetch_assoc()) {
            $rows[] = [
                'department_id' => (int)($row['department_id'] ?? 0),
                'hour_bucket' => (string)($row['hour_bucket'] ?? ''),
                'wash_count' => (int)($row['wash_count'] ?? 0),
            ];
        }

        return $rows;
    }

    /**
     * @param array<int|string> $department_ids
     * @return array{quantity:int,products:int,earnings:int,washes:int}
     * @throws Exception
     */
    public function transactionSummary(string $date_start, string $date_end, array $department_ids): array
    {
        global $db;

        $this->validateDateRange($date_start, $date_end);
        $normalized_department_ids = $this->normalizeIds($department_ids);
        if ($normalized_department_ids === []) {
            return [
                'quantity' => 0,
                'products' => 0,
                'earnings' => 0,
                'washes' => 0,
            ];
        }

        $department_ids_sql = implode(',', $normalized_department_ids);
        $escaped_start = $db->escape_string($date_start);
        $escaped_end = $db->escape_string($date_end);

        $sql = "SELECT COUNT(DISTINCT o.id) AS quantity,
                       COALESCE(SUM(oi.quantity), 0) AS products,
                       COALESCE(SUM(oi.price * oi.quantity), 0) AS earnings
                FROM orders o
                JOIN order_items oi ON oi.order_id = o.id
                WHERE o.department_id IN ($department_ids_sql)
                  AND o.created_at BETWEEN '$escaped_start' AND '$escaped_end'
                  AND o.deleted_at IS NULL
                  AND oi.deleted_at IS NULL";

        $result = $db->query($sql);
        $row = is_object($result) ? $result->fetch_assoc() : null;

        return [
            'quantity' => (int)($row['quantity'] ?? 0),
            'products' => (int)($row['products'] ?? 0),
            'earnings' => (int)round((float)($row['earnings'] ?? 0)),
            'washes' => $this->countRows($date_start, $date_end, $normalized_department_ids),
        ];
    }

    /**
     * @param array<int|string> $department_ids
     * @return array<int,array{id:int,department_id:int,created_at:string}>
     * @throws Exception
     */
    public function listTransactions(string $date_start, string $date_end, array $department_ids): array
    {
        global $db;

        $this->validateDateRange($date_start, $date_end);
        $normalized_department_ids = $this->normalizeIds($department_ids);
        if ($normalized_department_ids === []) {
            return [];
        }

        selfserve_schema_bootstrap::ensureTables();

        $department_ids_sql = implode(',', $normalized_department_ids);
        $escaped_start = $db->escape_string($date_start);
        $escaped_end = $db->escape_string($date_end);
        $candidate_sql = $this->candidateUnionSql($department_ids_sql, $escaped_start, $escaped_end);

        $sql = "SELECT CAST(SUBSTRING_INDEX(GROUP_CONCAT(entity_id ORDER BY source_priority ASC, entity_id ASC), ',', 1) AS UNSIGNED) AS id,
                       department_id,
                       MIN(counted_at) AS created_at
                FROM ($candidate_sql) candidates
                GROUP BY dedupe_key, department_id
                ORDER BY created_at ASC";

        $result = $db->query($sql);
        if (!is_object($result) || $result->num_rows === 0) {
            return [];
        }

        $rows = [];
        while ($row = $result->fetch_assoc()) {
            $rows[] = [
                'id' => (int)($row['id'] ?? 0),
                'department_id' => (int)($row['department_id'] ?? 0),
                'created_at' => (string)($row['created_at'] ?? ''),
            ];
        }

        return $rows;
    }

    /**
     * @param array<int> $department_ids
     * @throws Exception
     */
    private function countRows(string $date_start, string $date_end, array $department_ids): int
    {
        $rows = $this->countByHourForDepartments($date_start, $date_end, $department_ids);
        $total = 0;

        foreach ($rows as $row) {
            $total += (int)($row['wash_count'] ?? 0);
        }

        return $total;
    }

    private function candidateUnionSql(string $department_ids_sql, string $escaped_start, string $escaped_end): string
    {
        return "SELECT CONCAT('order:', o.id) AS dedupe_key,
                       o.id AS entity_id,
                       o.department_id,
                       o.created_at AS counted_at,
                       0 AS source_priority
                FROM orders o
                JOIN order_items oi ON oi.order_id = o.id
                JOIN products p ON p.id = oi.product_id
                WHERE o.department_id IN ($department_ids_sql)
                  AND o.created_at BETWEEN '$escaped_start' AND '$escaped_end'
                  AND o.deleted_at IS NULL
                  AND oi.deleted_at IS NULL
                  AND p.is_wash = 1
                UNION ALL
                SELECT CASE
                           WHEN linked_o.id IS NOT NULL THEN CONCAT('order:', linked_o.id)
                           ELSE CONCAT('selfserve:', s.id)
                       END AS dedupe_key,
                       CASE
                           WHEN linked_o.id IS NOT NULL THEN linked_o.id
                           ELSE s.id
                       END AS entity_id,
                       COALESCE(linked_o.department_id, s.department_id) AS department_id,
                       COALESCE(linked_o.created_at, s.completed_at) AS counted_at,
                       1 AS source_priority
                FROM selfserve_wash_sessions s
                LEFT JOIN orders linked_o
                  ON linked_o.id = s.order_id
                 AND linked_o.deleted_at IS NULL
                WHERE COALESCE(linked_o.department_id, s.department_id) IN ($department_ids_sql)
                  AND COALESCE(linked_o.created_at, s.completed_at) BETWEEN '$escaped_start' AND '$escaped_end'
                  AND s.deleted_at IS NULL
                  AND s.completed_at IS NOT NULL
                  AND UPPER(TRIM(s.status)) = 'COMPLETED'";
    }

    /**
     * @param array<int|string> $ids
     * @return array<int>
     */
    private function normalizeIds(array $ids): array
    {
        $normalized = [];
        foreach ($ids as $id) {
            $value = (int)$id;
            if ($value > 0) {
                $normalized[$value] = $value;
            }
        }

        return array_values($normalized);
    }

    /**
     * @throws Exception
     */
    private function validateDateRange(string $date_start, string $date_end): void
    {
        if (strtotime($date_start) === false || strtotime($date_end) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($date_start) > strtotime($date_end)) {
            throw new Exception('The start date cannot be after the end date');
        }
    }
}
