<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class OrderModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    // ── List ──────────────────────────────────────────────────────────────────

    public function all(int $page = 1, int $perPage = ITEMS_PER_PAGE, array $filters = []): array
    {
        $offset = ($page - 1) * $perPage;
        $where  = ['1=1'];
        $params = [];

        if (!empty($filters['status'])) {
            $where[]  = 'o.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['search'])) {
            $where[]  = '(o.order_number LIKE ? OR COALESCE(u.name,o.guest_name) LIKE ? OR COALESCE(u.phone,o.guest_phone) LIKE ?)';
            $params[] = '%'.$filters['search'].'%';
            $params[] = '%'.$filters['search'].'%';
            $params[] = '%'.$filters['search'].'%';
        }
        if (!empty($filters['date_from'])) {
            $where[]  = 'DATE(o.placed_at) >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[]  = 'DATE(o.placed_at) <= ?';
            $params[] = $filters['date_to'];
        }

        $params[] = $perPage;
        $params[] = $offset;

        return $this->db->fetchAll(
            "SELECT o.*,
                    COALESCE(u.name,  o.guest_name)  AS customer_name,
                    COALESCE(u.phone, o.guest_phone) AS customer_phone,
                    u.email AS customer_email,
                    r.name AS rider_name,
                    c.id   AS customer_row_id,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL)
                        AS cust_total_orders,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL
                        AND ox.status = 'delivered')
                        AS cust_delivered_orders
               FROM orders o
               LEFT JOIN customers c ON c.id = o.customer_id
               LEFT JOIN users u     ON u.id = c.user_id
               LEFT JOIN users r     ON r.id = o.rider_id
              WHERE " . implode(' AND ', $where) . "
              ORDER BY o.placed_at DESC
              LIMIT ? OFFSET ?",
            $params
        );
    }

    public function count(array $filters = []): int
    {
        $where  = ['1=1'];
        $params = [];

        if (!empty($filters['status'])) {
            $where[]  = 'o.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['search'])) {
            $where[]  = '(o.order_number LIKE ? OR COALESCE(u.name,o.guest_name) LIKE ? OR COALESCE(u.phone,o.guest_phone) LIKE ?)';
            $params[] = '%'.$filters['search'].'%';
            $params[] = '%'.$filters['search'].'%';
            $params[] = '%'.$filters['search'].'%';
        }
        if (!empty($filters['date_from'])) {
            $where[]  = 'DATE(o.placed_at) >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[]  = 'DATE(o.placed_at) <= ?';
            $params[] = $filters['date_to'];
        }

        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM orders o
               LEFT JOIN customers c ON c.id = o.customer_id
               LEFT JOIN users u     ON u.id = c.user_id
              WHERE " . implode(' AND ', $where),
            $params
        );
    }

    // ── Single order ──────────────────────────────────────────────────────────

    public function find(int $id): ?array
    {
        $row = $this->db->fetchOne(
            "SELECT o.*,
                    COALESCE(u.name,  o.guest_name)  AS customer_name,
                    COALESCE(u.phone, o.guest_phone) AS customer_phone,
                    COALESCE(u.email, o.guest_email) AS customer_email,
                    r.name AS rider_name,
                    v.code AS voucher_code,
                    c.id   AS customer_row_id,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL)
                        AS cust_total_orders,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL
                        AND ox.status = 'delivered')
                        AS cust_delivered_orders,
                    (SELECT COALESCE(SUM(ox.total_amount),0) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL)
                        AS cust_total_spent
               FROM orders o
               LEFT JOIN customers c  ON c.id = o.customer_id
               LEFT JOIN users u      ON u.id = c.user_id
               LEFT JOIN users r      ON r.id = o.rider_id
               LEFT JOIN vouchers v   ON v.id = o.voucher_id
              WHERE o.id = ?",
            [$id]
        );
        if ($row) {
            // Normalise column aliases for views
            $row['special_instructions'] = $row['special_notes'] ?? null;
            $row['order_type']           = $row['order_type'] ?? 'delivery';
            $row['payment_method']       = $row['payment_method'] ?? 'cash';
            $row['payment_status']       = $row['payment_status'] ?? 'unpaid';
            $row['discount_amount']      = $row['discount_amount'] ?? 0;
            $row['delivery_fee']         = $row['delivery_fee'] ?? 0;
            $row['tax_amount']           = $row['tax_amount'] ?? 0;
        }
        return $row;
    }

    public function findByNumber(string $number): ?array
    {
        return $this->db->fetchOne(
            "SELECT * FROM orders WHERE order_number = ?", [$number]
        );
    }

    public function getItems(int $orderId): array
    {
        return $this->db->fetchAll(
            "SELECT oi.*,
                    oi.line_total AS subtotal,
                    oi.choices_summary AS choices_detail
               FROM order_items oi
              WHERE oi.order_id = ?
              ORDER BY oi.id ASC",
            [$orderId]
        );
    }

    public function getStatusLog(int $orderId): array
    {
        return $this->db->fetchAll(
            "SELECT sl.*, u.name AS changed_by_name
               FROM order_status_log sl
               LEFT JOIN users u ON u.id = sl.changed_by
              WHERE sl.order_id = ?
              ORDER BY sl.changed_at ASC",
            [$orderId]
        );
    }

    // ── Status management ─────────────────────────────────────────────────────

    public function updateStatus(int $id, string $status, ?int $userId = null, ?string $note = null): void
    {
        $this->db->update('orders', ['status' => $status, 'updated_at' => date('Y-m-d H:i:s')], ['id' => $id]);

        $this->db->insert('order_status_log', [
            'order_id'   => $id,
            'status'     => $status,
            'note'       => $note,
            'changed_by' => $userId,
        ]);
    }

    public function cancel(int $id, string $reason, ?int $userId = null): void
    {
        $this->db->update('orders', [
            'status'           => 'cancelled',
            'cancelled_reason' => $reason,
        ], ['id' => $id]);

        $this->db->insert('order_status_log', [
            'order_id'   => $id,
            'status'     => 'cancelled',
            'note'       => $reason,
            'changed_by' => $userId,
        ]);
    }

    // ── Invoice ───────────────────────────────────────────────────────────────

    public function getInvoice(int $orderId): ?array
    {
        return $this->db->fetchOne(
            "SELECT * FROM invoices WHERE order_id = ?", [$orderId]
        );
    }

    public function createInvoice(int $orderId): string
    {
        $existing = $this->getInvoice($orderId);
        if ($existing) return $existing['invoice_number'];

        $number = 'INV-' . date('Ymd') . '-' . str_pad((string)$orderId, 4, '0', STR_PAD_LEFT);
        $this->db->insert('invoices', [
            'order_id'       => $orderId,
            'invoice_number' => $number,
        ]);
        return $number;
    }

    // ── Live / dashboard counts ───────────────────────────────────────────────

    public function pendingCount(): int
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM orders WHERE status = 'pending'"
        );
    }

    public function activeOrders(): array
    {
        return $this->db->fetchAll(
            "SELECT o.*,
                    COALESCE(u.name,  o.guest_name)  AS customer_name,
                    COALESCE(u.phone, o.guest_phone) AS customer_phone,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL)
                        AS cust_total_orders,
                    (SELECT COUNT(*) FROM orders ox
                      WHERE ox.customer_id = o.customer_id AND ox.customer_id IS NOT NULL
                        AND ox.status = 'delivered')
                        AS cust_delivered_orders
               FROM orders o
               LEFT JOIN customers c ON c.id = o.customer_id
               LEFT JOIN users u     ON u.id = c.user_id
              WHERE o.status NOT IN ('delivered','cancelled')
              ORDER BY FIELD(o.status,'pending','confirmed','cooking','ready','picked_up','on_the_way'),
                       o.placed_at ASC"
        );
    }

    public function generateOrderNumber(): string
    {
        $date  = date('Ymd');
        $count = (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM orders WHERE DATE(placed_at) = CURDATE()"
        );
        return 'ORD-' . $date . '-' . str_pad((string)($count + 1), 4, '0', STR_PAD_LEFT);
    }

    public function riders(): array
    {
        return $this->db->fetchAll(
            "SELECT u.id, u.name, u.phone
               FROM users u
               JOIN roles r ON r.id = u.role_id
              WHERE r.name = 'rider' AND u.is_active = 1
              ORDER BY u.name ASC"
        );
    }
}
