<?php
require_once 'admin/core/Database.php';

class Product {
    private $db;

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

    public function getConnection() {
        return $this->db->conn;
    }

    // SLUG
    private function generateSlug($title) {
        $slug = strtolower(trim($title));
        $slug = preg_replace('/[^a-z0-9-]+/', '-', $slug);
        return trim($slug, '-');
    }

    // ADD PRODUCT
    public function add($data) {

        $slug = $this->generateSlug($data['pro_title']);

        $stmt = $this->db->conn->prepare("
            INSERT INTO products 
            (pro_title, slug, pro_material, category_id, sub_category_id, price, sale_price, highlight,
            description, benefits, how_to_use, ideal_for, care, special_instruction, short_description, pro_image)

            VALUES 
            (:pro_title, :slug, :pro_material, :category_id, :sub_category_id, :price, :sale_price, :highlight,
            :description, :benefits, :how_to_use, :ideal_for, :care, :special_instruction, :short_description, :pro_image)
        ");

        $stmt->execute([
            ':pro_title' => $data['pro_title'],
            ':slug' => $slug,
            ':pro_material' => $data['pro_material'],
            ':category_id' => $data['category_id'],
            ':sub_category_id' => $data['sub_category_id'],
            ':price' => $data['price'],
            ':sale_price' => $data['sale_price'],
            ':highlight' => $data['highlight'],
            ':description' => $data['description'],
            ':benefits' => $data['benefits'],
            ':how_to_use' => $data['how_to_use'],
            ':ideal_for' => $data['ideal_for'],
            ':care' => $data['care'],
            ':special_instruction' => $data['special_instruction'],
            ':short_description' => $data['short_description'],
            ':pro_image' => $data['pro_image']
        ]);

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

    // UPDATE
    public function update($id, $data) {

        $slug = $this->generateSlug($data['pro_title']);

        $stmt = $this->db->conn->prepare("
            UPDATE products SET
            pro_title=:pro_title,
            slug=:slug,
            pro_material=:pro_material,
            category_id=:category_id,
            sub_category_id=:sub_category_id,
            price=:price,
            sale_price=:sale_price,
            highlight=:highlight,
            description=:description,
            benefits=:benefits,
            how_to_use=:how_to_use,
            ideal_for=:ideal_for,
            care=:care,
            special_instruction=:special_instruction,
            short_description=:short_description,
            pro_image=:pro_image
            WHERE id=:id
        ");

        $stmt->execute([
            ':pro_title'=>$data['pro_title'],
            ':slug'=>$slug,
            ':pro_material'=>$data['pro_material'],
            ':category_id'=>$data['category_id'],
            ':sub_category_id'=>$data['sub_category_id'],
            ':price'=>$data['price'],
            ':sale_price'=>$data['sale_price'],
            ':highlight'=>$data['highlight'],
            ':description'=>$data['description'],
            ':benefits'=>$data['benefits'],
            ':how_to_use'=>$data['how_to_use'],
            ':ideal_for'=>$data['ideal_for'],
            ':care'=>$data['care'],
            ':special_instruction'=>$data['special_instruction'],
            ':short_description'=>$data['short_description'],
            ':pro_image'=>$data['pro_image'],
            ':id'=>$id
        ]);
    }

    // DELETE
    public function delete($id) {
        $stmt = $this->db->conn->prepare("DELETE FROM products WHERE id=:id");
        $stmt->execute([':id'=>$id]);
    }

    // GET ALL
    public function getAll() {
        return $this->db->conn->query("
            SELECT p.*, c.cat_title 
            FROM products p 
            LEFT JOIN category c ON p.category_id=c.id 
            ORDER BY p.id DESC
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function get($id) {
        $stmt=$this->db->conn->prepare("SELECT * FROM products WHERE id=:id");
        $stmt->execute([':id'=>$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // GALLERY
    public function addImages($pid,$images){
        $stmt=$this->db->conn->prepare("INSERT INTO product_images (product_id,image_name) VALUES (:pid,:img)");
        foreach($images as $img){
            $stmt->execute([':pid'=>$pid,':img'=>$img]);
        }
    }

    public function getImages($pid){
        $stmt=$this->db->conn->prepare("SELECT * FROM product_images WHERE product_id=:pid");
        $stmt->execute([':pid'=>$pid]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deleteImage($id){
        $stmt=$this->db->conn->prepare("DELETE FROM product_images WHERE id=:id");
        $stmt->execute([':id'=>$id]);
    }

    public function deleteAllImagesByProduct($pid){
        $stmt=$this->db->conn->prepare("DELETE FROM product_images WHERE product_id=:pid");
        $stmt->execute([':pid'=>$pid]);
    }

    public function getByCategory($categoryId) {
    $stmt = $this->db->conn->prepare("
        SELECT * FROM products 
        WHERE category_id = :id OR sub_category_id = :id
    ");
    $stmt->execute([':id' => $categoryId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}