<?php

require_once BASE_PATH . '/config/database.php';

class Product
{
    private PDO $db;

    public function __construct()
    {
        $database = new Database();

        $this->db = $database->connect();
    }


    /**
     * Biểu thức lấy giá bán thực tế.
     *
     * Nếu sale_price hợp lệ và nhỏ hơn price
     * => dùng sale_price.
     *
     * Ngược lại => dùng price.
     */
    private function priceExpression(): string
    {
        return "
            CASE
                WHEN p.sale_price IS NOT NULL
                     AND p.sale_price < p.price
                THEN p.sale_price
                ELSE p.price
            END
        ";
    }


    /**
     * Tạo điều kiện lọc chung.
     */
    private function buildFilters(
        array $filters,
        array &$params
    ): string {

        $conditions = [
    "p.status IN ('active', 'out_of_stock')"
];


        // SEARCH
        if (!empty($filters['search'])) {

            $conditions[] = "
                (
                    p.name LIKE :search_name
                    OR p.brand LIKE :search_brand
                    OR p.sku LIKE :search_sku
                )
            ";

            $searchValue = '%' . $filters['search'] . '%';

            $params['search_name'] = $searchValue;
            $params['search_brand'] = $searchValue;
            $params['search_sku'] = $searchValue;
        }


        // CATEGORY
        if (!empty($filters['category_id'])) {

            $conditions[] =
                "p.category_id = :category_id";

            $params['category_id'] =
                (int) $filters['category_id'];
        }


        // DOG / CAT
        if (!empty($filters['pet'])) {

            if ($filters['pet'] === 'dog') {

                $conditions[] =
                    "p.pet_target IN ('dog', 'all')";

            } elseif ($filters['pet'] === 'cat') {

                $conditions[] =
                    "p.pet_target IN ('cat', 'all')";
            }
        }


        $priceExpression =
            $this->priceExpression();


        // MIN PRICE
        if (
            isset($filters['min_price'])
            && $filters['min_price'] !== ''
        ) {

            $conditions[] =
                "{$priceExpression} >= :min_price";

            $params['min_price'] =
                (float) $filters['min_price'];
        }


        // MAX PRICE
        if (
            isset($filters['max_price'])
            && $filters['max_price'] !== ''
        ) {

            $conditions[] =
                "{$priceExpression} <= :max_price";

            $params['max_price'] =
                (float) $filters['max_price'];
        }


        return implode(
            ' AND ',
            $conditions
        );
    }


    /**
     * Lấy danh sách sản phẩm.
     */
    public function getAll(
        array $filters = [],
        int $limit = 12,
        int $offset = 0
    ): array {

        $params = [];

        $where =
            $this->buildFilters(
                $filters,
                $params
            );


        $sort =
            $filters['sort']
            ?? 'newest';


        $orderBy = match ($sort) {

            'price_asc' =>
                $this->priceExpression()
                . ' ASC',

            'price_desc' =>
                $this->priceExpression()
                . ' DESC',

            'best_selling' =>
                'p.sold_quantity DESC',

            'name_asc' =>
                'p.name ASC',

            default =>
                'p.created_at DESC'
        };


        $limit = max(
            1,
            (int) $limit
        );

        $offset = max(
            0,
            (int) $offset
        );


        $sql = "
            SELECT
                p.*,

                pc.name AS category_name,
                pc.slug AS category_slug,

                {$this->priceExpression()}
                    AS current_price

            FROM products p

            INNER JOIN product_categories pc
                ON pc.id = p.category_id

            WHERE {$where}

            ORDER BY {$orderBy}

            LIMIT {$limit}
            OFFSET {$offset}
        ";


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

        $stmt->execute($params);

        return $stmt->fetchAll();
    }


    /**
     * Đếm số sản phẩm sau khi lọc.
     */
    public function countAll(
        array $filters = []
    ): int {

        $params = [];

        $where =
            $this->buildFilters(
                $filters,
                $params
            );


        $sql = "
            SELECT COUNT(*)
            FROM products p
            WHERE {$where}
        ";


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

        $stmt->execute($params);


        return (int) $stmt->fetchColumn();
    }


    /**
     * Chi tiết sản phẩm theo slug.
     */
    public function findBySlug(
        string $slug
    ): ?array {

        $sql = "
            SELECT
                p.*,

                pc.name AS category_name,
                pc.slug AS category_slug,

                {$this->priceExpression()}
                    AS current_price

            FROM products p

            INNER JOIN product_categories pc
                ON pc.id = p.category_id

            WHERE p.slug = :slug
              AND p.status IN ('active', 'out_of_stock')

            LIMIT 1
        ";


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


        $stmt->execute([
            'slug' => $slug
        ]);


        $product =
            $stmt->fetch();


        return $product ?: null;
    }


    /**
     * Lấy toàn bộ ảnh phụ sản phẩm.
     */
    public function getImages(
        int $productId
    ): array {

        $sql = "
            SELECT *
            FROM product_images

            WHERE product_id = :product_id

            ORDER BY sort_order ASC, id ASC
        ";


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


        $stmt->execute([
            'product_id' => $productId
        ]);


        return $stmt->fetchAll();
    }


    /**
     * Sản phẩm liên quan.
     */
    public function getRelatedProducts(
        int $categoryId,
        int $excludeProductId,
        int $limit = 4
    ): array {

        $limit = max(
            1,
            (int) $limit
        );


        $sql = "
            SELECT
                p.*,

                {$this->priceExpression()}
                    AS current_price

            FROM products p

            WHERE p.category_id = :category_id

              AND p.id != :product_id

              AND p.status = 'active'

            ORDER BY
                p.sold_quantity DESC,
                p.created_at DESC

            LIMIT {$limit}
        ";


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


        $stmt->execute([
            'category_id' => $categoryId,
            'product_id'  => $excludeProductId
        ]);


        return $stmt->fetchAll();
    }


    /**
     * Sản phẩm nổi bật cho trang chủ.
     */
    public function getFeatured(
        int $limit = 8
    ): array {

        $limit = max(
            1,
            (int) $limit
        );


        $sql = "
            SELECT
                p.*,

                {$this->priceExpression()}
                    AS current_price

            FROM products p

            WHERE p.status = 'active'
              AND p.is_featured = 1

            ORDER BY
                p.sold_quantity DESC,
                p.created_at DESC

            LIMIT {$limit}
        ";


        return $this->db
            ->query($sql)
            ->fetchAll();
    }


    /**
     * Sản phẩm đang giảm giá.
     */
    public function getSaleProducts(
        int $limit = 8
    ): array {

        $limit = max(
            1,
            (int) $limit
        );


        $sql = "
            SELECT
                p.*,

                p.sale_price
                    AS current_price

            FROM products p

            WHERE p.status = 'active'

              AND p.sale_price IS NOT NULL

              AND p.sale_price < p.price

            ORDER BY
                p.created_at DESC

            LIMIT {$limit}
        ";


        return $this->db
            ->query($sql)
            ->fetchAll();
    }
    /**
 * Tìm sản phẩm theo ID.
 */
public function findById(
    int $id
): ?array {

    $sql = "
        SELECT
            p.*,

            pc.name AS category_name,
            pc.slug AS category_slug,

            {$this->priceExpression()}
                AS current_price

        FROM products p

        INNER JOIN product_categories pc
            ON pc.id = p.category_id

        WHERE p.id = :id

        LIMIT 1
    ";

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

    $stmt->execute([
        'id' => $id
    ]);

    $product =
        $stmt->fetch();

    return $product ?: null;
}
public function countForOwner(
    string $search = '',
    string $status = ''
): int {

    $params = [];

    $where = '1 = 1';


    if ($search !== '') {

        $where .= "
            AND (
                p.name LIKE :search_name
                OR p.sku LIKE :search_sku
                OR p.brand LIKE :search_brand
            )
        ";

        $searchValue = '%' . $search . '%';

        $params['search_name'] = $searchValue;
        $params['search_sku'] = $searchValue;
        $params['search_brand'] = $searchValue;
    }


    if ($status !== '') {

        $where .= "
            AND p.status = :status
        ";

        $params['status'] =
            $status;
    }


    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM products p

            WHERE {$where}
        ");


    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn();
}


public function getForOwner(
    string $search = '',
    string $status = '',
    int $limit = 15,
    int $offset = 0
): array {

    $params = [];

    $where = '1 = 1';


    if ($search !== '') {

        $where .= "
            AND (
                p.name LIKE :search_name
                OR p.sku LIKE :search_sku
                OR p.brand LIKE :search_brand
            )
        ";

        $searchValue = '%' . $search . '%';

        $params['search_name'] = $searchValue;
        $params['search_sku'] = $searchValue;
        $params['search_brand'] = $searchValue;
    }


    if ($status !== '') {

        $where .= "
            AND p.status = :status
        ";

        $params['status'] =
            $status;
    }


    $limit =
        max(1, $limit);

    $offset =
        max(0, $offset);


    $sql = "
        SELECT
            p.*,
            pc.name AS category_name

        FROM products p

        INNER JOIN product_categories pc
            ON pc.id = p.category_id

        WHERE {$where}

        ORDER BY p.created_at DESC

        LIMIT {$limit}
        OFFSET {$offset}
    ";


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

    $stmt->execute($params);

    return $stmt->fetchAll();
}


public function findAnyById(
    int $id
): ?array {

    $stmt =
        $this->db->prepare("
            SELECT
                p.*,
                pc.name AS category_name

            FROM products p

            INNER JOIN product_categories pc
                ON pc.id = p.category_id

            WHERE p.id = :id

            LIMIT 1
        ");

    $stmt->execute([
        'id' => $id
    ]);

    $product =
        $stmt->fetch();

    return $product ?: null;
}


public function slugExists(
    string $slug,
    ?int $excludeId = null
): bool {

    $sql = "
        SELECT COUNT(*)

        FROM products

        WHERE slug = :slug
    ";

    $params = [
        'slug' => $slug
    ];


    if ($excludeId !== null) {

        $sql .= "
            AND id != :exclude_id
        ";

        $params['exclude_id'] =
            $excludeId;
    }


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

    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn() > 0;
}


public function skuExists(
    string $sku,
    ?int $excludeId = null
): bool {

    if ($sku === '') {
        return false;
    }


    $sql = "
        SELECT COUNT(*)

        FROM products

        WHERE sku = :sku
    ";

    $params = [
        'sku' => $sku
    ];


    if ($excludeId !== null) {

        $sql .= "
            AND id != :exclude_id
        ";

        $params['exclude_id'] =
            $excludeId;
    }


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

    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn() > 0;
}


public function create(
    array $data
): int {

    $stmt =
        $this->db->prepare("
            INSERT INTO products (
                category_id,
                sku,
                name,
                slug,
                short_description,
                description,
                brand,
                pet_target,
                price,
                sale_price,
                stock_quantity,
                sold_quantity,
                featured_image,
                status,
                is_featured
            )
            VALUES (
                :category_id,
                :sku,
                :name,
                :slug,
                :short_description,
                :description,
                :brand,
                :pet_target,
                :price,
                :sale_price,
                :stock_quantity,
                0,
                NULL,
                :status,
                :is_featured
            )
        ");


    $stmt->execute([
        'category_id' =>
            $data['category_id'],

        'sku' =>
            $data['sku']
            ?: null,

        'name' =>
            $data['name'],

        'slug' =>
            $data['slug'],

        'short_description' =>
            $data['short_description']
            ?: null,

        'description' =>
            $data['description']
            ?: null,

        'brand' =>
            $data['brand']
            ?: null,

        'pet_target' =>
            $data['pet_target'],

        'price' =>
            $data['price'],

        'sale_price' =>
            $data['sale_price'],

        'stock_quantity' =>
            $data['stock_quantity'],

        'status' =>
            $data['status'],

        'is_featured' =>
            $data['is_featured']
    ]);


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


public function updateProduct(
    int $id,
    array $data
): void {

    $stmt =
        $this->db->prepare("
            UPDATE products

            SET
                category_id = :category_id,
                sku = :sku,
                name = :name,
                slug = :slug,
                short_description = :short_description,
                description = :description,
                brand = :brand,
                pet_target = :pet_target,
                price = :price,
                sale_price = :sale_price,
                stock_quantity = :stock_quantity,
                status = :status,
                is_featured = :is_featured

            WHERE id = :id
        ");


    $stmt->execute([
        'category_id' =>
            $data['category_id'],

        'sku' =>
            $data['sku']
            ?: null,

        'name' =>
            $data['name'],

        'slug' =>
            $data['slug'],

        'short_description' =>
            $data['short_description']
            ?: null,

        'description' =>
            $data['description']
            ?: null,

        'brand' =>
            $data['brand']
            ?: null,

        'pet_target' =>
            $data['pet_target'],

        'price' =>
            $data['price'],

        'sale_price' =>
            $data['sale_price'],

        'stock_quantity' =>
            $data['stock_quantity'],

        'status' =>
            $data['status'],

        'is_featured' =>
            $data['is_featured'],

        'id' =>
            $id
    ]);
}


public function updateFeaturedImage(
    int $productId,
    string $path
): void {

    $stmt =
        $this->db->prepare("
            UPDATE products

            SET featured_image = :image

            WHERE id = :id
        ");

    $stmt->execute([
        'image' => $path,
        'id' => $productId
    ]);
}


public function addImage(
    int $productId,
    string $path,
    int $sortOrder = 0
): int {

    $stmt =
        $this->db->prepare("
            INSERT INTO product_images (
                product_id,
                image_path,
                sort_order
            )
            VALUES (
                :product_id,
                :image_path,
                :sort_order
            )
        ");

    $stmt->execute([
        'product_id' =>
            $productId,

        'image_path' =>
            $path,

        'sort_order' =>
            $sortOrder
    ]);


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


public function findImageById(
    int $imageId,
    int $productId
): ?array {

    $stmt =
        $this->db->prepare("
            SELECT *

            FROM product_images

            WHERE id = :id
              AND product_id = :product_id

            LIMIT 1
        ");

    $stmt->execute([
        'id' => $imageId,
        'product_id' => $productId
    ]);

    $image =
        $stmt->fetch();

    return $image ?: null;
}


public function deleteImage(
    int $imageId,
    int $productId
): void {

    $stmt =
        $this->db->prepare("
            DELETE FROM product_images

            WHERE id = :id
              AND product_id = :product_id
        ");

    $stmt->execute([
        'id' => $imageId,
        'product_id' => $productId
    ]);
}


public function updateStatus(
    int $id,
    string $status
): void {

    $stmt =
        $this->db->prepare("
            UPDATE products

            SET status = :status

            WHERE id = :id
        ");

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


public function countLowStock(
    int $threshold = 5
): int {

    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM products

            WHERE stock_quantity <= :threshold
              AND status != 'hidden'
        ");

    $stmt->execute([
        'threshold' => $threshold
    ]);

    return (int)
        $stmt->fetchColumn();
}
public function getFirstImage(
    int $productId,
    ?int $excludeImageId = null
): ?array {

    $sql = "
        SELECT *
        FROM product_images

        WHERE product_id = :product_id
    ";

    $params = [
        'product_id' => $productId
    ];


    if ($excludeImageId !== null) {

        $sql .= "
            AND id != :exclude_id
        ";

        $params['exclude_id'] =
            $excludeImageId;
    }


    $sql .= "
        ORDER BY sort_order ASC, id ASC
        LIMIT 1
    ";


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

    $stmt->execute($params);


    $image =
        $stmt->fetch();


    return $image ?: null;
}


public function clearFeaturedImage(
    int $productId
): void {

    $stmt =
        $this->db->prepare("
            UPDATE products

            SET featured_image = NULL

            WHERE id = :id
        ");


    $stmt->execute([
        'id' => $productId
    ]);
}


public function setFeaturedImage(
    int $productId,
    string $imagePath
): void {

    $this->updateFeaturedImage(
        $productId,
        $imagePath
    );
}


public function updateModerationStatus(
    int $id,
    string $status
): void {

    $stmt =
        $this->db->prepare("\n            UPDATE products\n\n            SET status = :status\n\n            WHERE id = :id\n        ");

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