<?php
/**
 * Store Model
 * Maps to `stores` table
 */

require_once __DIR__ . '/../Core/Model.php';

class Store extends Model {
    protected string $table = 'stores';

    public function getActiveStores(int $limit = 8): array {
        $sql = "SELECT s.*, u.username as owner_username,
                (SELECT COUNT(*) FROM `products` p WHERE p.store_id = s.id AND p.status = 'active' AND p.deleted_at IS NULL) as total_products
                FROM `stores` s
                JOIN `users` u ON s.user_id = u.id
                WHERE s.status = 'active' AND s.deleted_at IS NULL AND u.status = 'active'
                ORDER BY s.verified_at DESC, s.created_at DESC
                LIMIT :limit";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function findBySlug(string $slug): ?array {
        $sql = "SELECT s.*, u.username as owner_username, u.first_name, u.last_name,
                (SELECT COUNT(*) FROM `products` p WHERE p.store_id = s.id AND p.status = 'active' AND p.deleted_at IS NULL) as total_products
                FROM `stores` s
                JOIN `users` u ON s.user_id = u.id
                WHERE s.store_slug = :slug AND s.status = 'active' AND s.deleted_at IS NULL
                LIMIT 1";
        return $this->fetchOne($sql, [':slug' => $slug]);
    }

    public function findByUserId(int $userId): ?array {
        $sql = "SELECT * FROM `stores` WHERE `user_id` = :user_id AND `deleted_at` IS NULL LIMIT 1";
        return $this->fetchOne($sql, [':user_id' => $userId]);
    }

    public function slugExists(string $slug, ?int $excludeStoreId = null): bool {
        $sql = "SELECT id FROM `stores` WHERE store_slug = :slug AND deleted_at IS NULL";
        $params = [':slug' => $slug];

        if ($excludeStoreId) {
            $sql .= " AND id != :exclude_id";
            $params[':exclude_id'] = $excludeStoreId;
        }

        $sql .= " LIMIT 1";
        return (bool)$this->fetchOne($sql, $params);
    }

    public function getStoreStats(int $storeId): array {
        $sql = "SELECT 
                COUNT(*) as total_products,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_products,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_products,
                SUM(CASE WHEN stock <= 0 THEN 1 ELSE 0 END) as out_of_stock_products,
                SUM(CASE WHEN stock > 0 AND stock <= 5 THEN 1 ELSE 0 END) as low_stock_products
                FROM `products`
                WHERE store_id = :store_id AND deleted_at IS NULL";
        $productStats = $this->fetchOne($sql, [':store_id' => $storeId]);

        $sqlOrders = "SELECT 
                      COUNT(*) as total_orders,
                      SUM(CASE WHEN order_status = 'pending_payment' THEN 1 ELSE 0 END) as pending_orders,
                      SUM(CASE WHEN order_status = 'preparing' THEN 1 ELSE 0 END) as preparing_orders,
                      SUM(CASE WHEN order_status = 'shipping' THEN 1 ELSE 0 END) as shipping_orders,
                      SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_sales
                      FROM `orders`
                      WHERE store_id = :store_id AND deleted_at IS NULL";
        $orderStats = $this->fetchOne($sqlOrders, [':store_id' => $storeId]);

        return array_merge($productStats ?: [], $orderStats ?: []);
    }

    // =========================================================
    // Admin Management Methods
    // =========================================================

    public function getAdminStores(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminStoreConditions($filters);

        $sql = "SELECT s.*, u.username as owner_username, u.email as owner_email, u.first_name, u.last_name,
                (SELECT COUNT(*) FROM `products` p WHERE p.store_id = s.id AND p.deleted_at IS NULL) as total_products,
                (SELECT COUNT(*) FROM `orders` o WHERE o.store_id = s.id AND o.deleted_at IS NULL) as total_orders
                FROM `stores` s
                JOIN `users` u ON s.user_id = u.id
                WHERE {$whereSql}
                ORDER BY s.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countAdminStores(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminStoreConditions($filters);
        $sql = "SELECT COUNT(*) FROM `stores` s JOIN `users` u ON s.user_id = u.id WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildAdminStoreConditions(array $filters): array {
        $conditions = ['s.deleted_at IS NULL'];
        $params = [];

        if (!empty($filters['q'])) {
            $conditions[] = '(s.store_name LIKE :q OR s.store_slug LIKE :q OR u.username LIKE :q OR u.email LIKE :q)';
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = 's.status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['store_type']) && $filters['store_type'] !== 'all') {
            if ($filters['store_type'] === 'official') {
                $conditions[] = 's.verified_at IS NOT NULL';
            } elseif ($filters['store_type'] === 'standard') {
                $conditions[] = 's.verified_at IS NULL';
            }
        }

        return [implode(' AND ', $conditions), $params];
    }

    public function findAdminStore(int $storeId): ?array {
        $sql = "SELECT s.*, u.username as owner_username, u.email as owner_email,
                u.first_name, u.last_name, u.phone as owner_phone
                FROM `stores` s JOIN `users` u ON s.user_id = u.id
                WHERE s.id = :id AND s.deleted_at IS NULL LIMIT 1";
        return $this->fetchOne($sql, [':id' => $storeId]);
    }

    public function suspendStore(int $storeId): bool {
        $stmt = $this->query(
            "UPDATE `stores` SET `status` = 'suspended', `updated_at` = NOW() WHERE `id` = :id AND `deleted_at` IS NULL",
            [':id' => $storeId]
        );
        return $stmt->rowCount() > 0;
    }

    public function reactivateStore(int $storeId): bool {
        $stmt = $this->query(
            "UPDATE `stores` SET `status` = 'active', `updated_at` = NOW() WHERE `id` = :id",
            [':id' => $storeId]
        );
        return $stmt->rowCount() > 0;
    }

    public function setOfficialStatus(int $storeId, bool $official): bool {
        $sql = $official
            ? "UPDATE `stores` SET `verified_at` = NOW(), `updated_at` = NOW() WHERE `id` = :id"
            : "UPDATE `stores` SET `verified_at` = NULL, `updated_at` = NOW() WHERE `id` = :id";
        $stmt = $this->query($sql, [':id' => $storeId]);
        return $stmt->rowCount() > 0;
    }

    public function getPlatformStoreStats(): array {
        $sql = "SELECT
                COUNT(*) as total_stores,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_stores,
                SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_stores,
                SUM(CASE WHEN verified_at IS NOT NULL THEN 1 ELSE 0 END) as official_stores,
                SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as new_today
                FROM `stores` WHERE deleted_at IS NULL";
        return $this->fetchOne($sql) ?: [];
    }
}