<?php

namespace Core\App\Models;

use PDO;

class OrderModel
{
    protected PDO $db;

    public function __construct()
    {
        $this->db = \db();
    }

    /* =====================================================
       CREATE / READ (UNCHANGED)
    ===================================================== */

    public function addOrder(array $data): int
    {
        $sql = "
            INSERT INTO orders
                (product_id, full_name, phone, address,
                 payment_method, payment_status, order_status,
                 total_amount, extra)
            VALUES
                (:product_id, :full_name, :phone, :address,
                 :payment_method, :payment_status, :order_status,
                 :total_amount, :extra)
        ";

        $stmt = $this->db->prepare($sql);

        $stmt->execute([
            ':product_id'     => $data['product_id'],
            ':full_name'      => $data['full_name'],
            ':phone'          => $data['phone'],
            ':address'        => $data['address'],
            ':payment_method' => $data['payment_method'],
            ':payment_status' => $data['payment_status'],
            ':order_status'   => $data['order_status'],
            ':total_amount'   => $data['total_amount'],
            ':extra'          => json_encode($data['extra'], JSON_UNESCAPED_UNICODE),
        ]);

        return (int) $this->db->lastInsertId();
    }

    /**
     * Get all orders — use only for small datasets (CSV export, etc.)
     * For admin listings, prefer getPaginated() to avoid memory issues.
     */
    public function getAll(): array
    {
        $stmt = $this->db->query("SELECT * FROM orders ORDER BY id DESC");
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($orders as &$order) {
            $order['extra'] = $order['extra']
                ? json_decode($order['extra'], true)
                : [];
        }

        return $orders;
    }

    /**
     * Get paginated orders for admin listing.
     *
     * @param  int    $page    1-based page number
     * @param  int    $perPage Rows per page (default 50)
     * @param  array  $filters Optional: ['status' => 'pending', 'search' => 'phone/name']
     * @return array  ['items' => [...], 'total' => int, 'pages' => int, 'page' => int, 'perPage' => int]
     */
    public function getPaginated(int $page = 1, int $perPage = 50, array $filters = []): array
    {
        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $where  = [];
        $params = [];

        if (!empty($filters['status'])) {
            $where[]  = 'order_status = :status';
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['search'])) {
            $where[]  = '(full_name LIKE :search OR phone LIKE :search)';
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        // Total count
        $countStmt = $this->db->prepare("SELECT COUNT(*) FROM orders {$whereClause}");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        // Paginated rows
        $params[':limit']  = $perPage;
        $params[':offset'] = $offset;
        $stmt = $this->db->prepare(
            "SELECT * FROM orders {$whereClause} ORDER BY id DESC LIMIT :limit OFFSET :offset"
        );
        foreach ($params as $key => $val) {
            if ($key === ':limit' || $key === ':offset') {
                $stmt->bindValue($key, $val, PDO::PARAM_INT);
            } else {
                $stmt->bindValue($key, $val, PDO::PARAM_STR);
            }
        }
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($orders as &$order) {
            $order['extra'] = $order['extra']
                ? json_decode($order['extra'], true)
                : [];
        }

        return [
            'items'   => $orders,
            'total'   => $total,
            'pages'   => (int)ceil($total / $perPage),
            'page'    => $page,
            'perPage' => $perPage,
        ];
    }

    public function getById(int $id): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM orders WHERE id = :id");
        $stmt->execute([':id' => $id]);

        $order = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$order) return null;

        $order['extra'] = $order['extra']
            ? json_decode($order['extra'], true)
            : [];

        return $order;
    }

    public function updateStatus(int $id, string $status): bool
    {
        $stmt = $this->db->prepare(
            "UPDATE orders SET order_status = :status WHERE id = :id"
        );

        return $stmt->execute([
            ':status' => $status,
            ':id'     => $id
        ]);
    }

    public function deleteOrder(int $id): bool
    {
        $stmt = $this->db->prepare("DELETE FROM orders WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }

    /* =====================================================
       SHIPPING – SMART AUTOMATION HELPERS (NEW)
    ===================================================== */

    /**
     * Check if shipping is locked (retry protection)
     */
    public function isShippingLocked(array $extra): bool
    {
        $lockedUntil = $extra['shipping']['locked_until'] ?? null;
        if (!$lockedUntil) {
            return false;
        }

        return strtotime($lockedUntil) > time();
    }

    /**
     * Register a failed shipping attempt
     */
    public function registerShippingFailure(
        int $orderId,
        array $extra,
        string $errorMessage,
        int $lockMinutes = 5,
        int $maxAttempts = 3
    ): void {
        $shipping = $extra['shipping'] ?? [];

        $attempts = (int)($shipping['attempts'] ?? 0) + 1;
        $shipping['attempts'] = $attempts;
        $shipping['last_attempt_at'] = date('Y-m-d H:i:s');
        $shipping['last_error'] = $errorMessage;

        if ($attempts >= $maxAttempts) {
            $shipping['locked_until'] = date(
                'Y-m-d H:i:s',
                time() + ($lockMinutes * 60)
            );
        }

        $extra['shipping'] = $shipping;

        $this->updateExtra($orderId, $extra);
    }

    /**
     * Reset shipping attempts on success
     */
    public function clearShippingFailures(int $orderId, array $extra): void
    {
        if (isset($extra['shipping'])) {
            unset(
                $extra['shipping']['attempts'],
                $extra['shipping']['locked_until'],
                $extra['shipping']['last_error']
            );
        }

        $this->updateExtra($orderId, $extra);
    }

    /**
     * Update extra column safely
     */
    public function updateExtra(int $orderId, array $extra): void

    {
        $stmt = $this->db->prepare(
            "UPDATE orders SET extra = :extra WHERE id = :id"
        );

        $stmt->execute([
            ':extra' => json_encode($extra, JSON_UNESCAPED_UNICODE),
            ':id'    => $orderId
        ]);
    }

    /* =====================================================
       SHIPPING QUERIES (UNCHANGED)
    ===================================================== */

    public function getByTracking(string $tracking): ?array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM orders
            WHERE JSON_UNQUOTE(JSON_EXTRACT(extra, '$.shipping.tracking')) = :tracking
            LIMIT 1
        ");

        $stmt->execute([':tracking' => $tracking]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }

    public function getOrdersWithShippingTracking()
    {
        $sql = "
            SELECT *
            FROM orders
            WHERE 
                JSON_EXTRACT(extra, '$.shipping.tracking') IS NOT NULL
                AND JSON_EXTRACT(extra, '$.shipping.provider') IS NOT NULL
        ";

        $stmt = $this->db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($orders as &$order) {
            $order['extra'] = $order['extra']
                ? json_decode($order['extra'], true)
                : [];
        }

        return $orders;
    }

public function getAllWithEcotrackTracking(): array
{
    $sql = "
        SELECT *
        FROM orders
        WHERE
            JSON_EXTRACT(extra, '$.shipping.tracking') IS NOT NULL
            AND JSON_EXTRACT(extra, '$.shipping.provider') = 'ecotrack'
    ";

    $stmt = $this->db->query($sql);
    $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($orders as &$order) {
        $order['extra'] = $order['extra']
            ? json_decode($order['extra'], true)
            : [];
    }

    return $orders ?: [];
}

    public function getShippedOrders()
    {
        $sql = "
            SELECT *
            FROM orders
            WHERE
                JSON_EXTRACT(extra, '$.shipping.provider') IS NOT NULL
                AND JSON_EXTRACT(extra, '$.shipping.tracking') IS NOT NULL
            ORDER BY id DESC
        ";

        $stmt = $this->db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($orders as &$order) {
            $order['extra'] = $order['extra']
                ? json_decode($order['extra'], true)
                : [];
        }

        return $orders;
    }
	
	public function exportAllToCsv(): array
{
    $stmt = $this->db->query("SELECT * FROM orders ORDER BY id DESC");
    $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($orders as &$order) {
        $order['extra'] = $order['extra']
            ? json_decode($order['extra'], true)
            : [];
    }

    return $orders;
}
}