<?php
// ─── Run this once to set up the database ───────────────────────────────────
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'balimeen_steel');

try {
    $pdo = new PDO("mysql:host=" . DB_HOST . ";charset=utf8mb4", DB_USER, DB_PASS,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

    // Create DB first, then select it
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    $pdo->exec("USE `" . DB_NAME . "`");

    // Drop existing tables for fresh reinstall
    $pdo->exec("SET FOREIGN_KEY_CHECKS=0");
    $pdo->exec("DROP TABLE IF EXISTS `inquiries`");
    $pdo->exec("DROP TABLE IF EXISTS `products`");
    $pdo->exec("DROP TABLE IF EXISTS `categories`");
    $pdo->exec("DROP TABLE IF EXISTS `testimonials`");
    $pdo->exec("DROP TABLE IF EXISTS `settings`");
    $pdo->exec("DROP TABLE IF EXISTS `admins`");
    $pdo->exec("SET FOREIGN_KEY_CHECKS=1");

    // ── Tables ──────────────────────────────────────────────────────────────
    $pdo->exec("CREATE TABLE IF NOT EXISTS `categories` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(100) NOT NULL,
        `slug` VARCHAR(120) NOT NULL UNIQUE,
        `description` TEXT,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `products` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `category_id` INT,
        `name` VARCHAR(200) NOT NULL,
        `slug` VARCHAR(220) NOT NULL UNIQUE,
        `short_desc` VARCHAR(400),
        `description` TEXT,
        `price` DECIMAL(10,2) DEFAULT 0,
        `material` VARCHAR(100),
        `finish` VARCHAR(100),
        `dimensions` VARCHAR(200),
        `weight` VARCHAR(50),
        `thickness` VARCHAR(50),
        `image` VARCHAR(300),
        `gallery` TEXT,
        `is_featured` TINYINT(1) DEFAULT 0,
        `status` ENUM('active','inactive') DEFAULT 'active',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `inquiries` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(150) NOT NULL,
        `email` VARCHAR(200),
        `phone` VARCHAR(20) NOT NULL,
        `company` VARCHAR(200),
        `product_id` INT,
        `message` TEXT,
        `status` ENUM('new','read','replied') DEFAULT 'new',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `testimonials` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(100) NOT NULL,
        `company` VARCHAR(150),
        `city` VARCHAR(80),
        `rating` TINYINT DEFAULT 5,
        `message` TEXT NOT NULL,
        `status` ENUM('active','inactive') DEFAULT 'active',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `settings` (
        `skey` VARCHAR(100) PRIMARY KEY,
        `svalue` TEXT
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `admins` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `username` VARCHAR(80) NOT NULL UNIQUE,
        `password` VARCHAR(255) NOT NULL,
        `name` VARCHAR(150),
        `email` VARCHAR(200),
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");

    // ── Seed Data ────────────────────────────────────────────────────────────
    // Admin
    $hash = password_hash('admin@123', PASSWORD_BCRYPT);
    $pdo->exec("INSERT IGNORE INTO `admins` (username,password,name,email)
        VALUES ('admin','$hash','Administrator','admin@balimeen.com')");

    // Categories
    $pdo->exec("INSERT IGNORE INTO `categories` (name,slug,description) VALUES
        ('Single Bowl Sinks','single-bowl','Spacious single basin kitchen sinks for everyday use.'),
        ('Double Bowl Sinks','double-bowl','Dual basin sinks for multitasking in the kitchen.'),
        ('Undermount Sinks','undermount','Sleek undermount sinks for a clean countertop look.'),
        ('Farmhouse Sinks','farmhouse','Classic apron-front farmhouse style sinks.')");

    // Products
    $pdo->exec("INSERT IGNORE INTO `products`
        (category_id,name,slug,short_desc,description,price,material,finish,dimensions,weight,thickness,image,is_featured,status)
        VALUES
        (1,'Classic Pro Single Bowl','classic-pro-single','Heavy-gauge SS 304 single bowl with anti-noise coating',
         'The Classic Pro Single Bowl is crafted from premium 18/8 stainless steel (SS 304) with a deep 220mm basin. Features anti-noise sound deadening pads and rustproof overflow system.',
         4500,'SS 304 (18/8)','Matt Finish','760×455×220 mm','4.2 kg','1.2 mm','sink_single_bowl.png',1,'active'),

        (2,'Grande Double Bowl','grande-double-bowl','Dual basin SS 304 sink with centre divider',
         'The Grande Double Bowl features two equal basins with a sturdy centre partition, perfect for separating washing and rinsing. Premium brushed finish resists fingerprints.',
         6800,'SS 304 (18/8)','Brushed Finish','900×500×220 mm','6.8 kg','1.2 mm','sink_double_bowl.png',1,'active'),

        (3,'SlimEdge Undermount','slimedge-undermount','Premium undermount for seamless countertop integration',
         'The SlimEdge Undermount fits perfectly beneath granite and quartz countertops, creating a seamless look. Deep 230mm bowl accommodates large pots and pans.',
         7200,'SS 316 (Marine Grade)','Satin Finish','510×455×230 mm','5.1 kg','1.5 mm','sink_undermount.png',1,'active'),

        (4,'Heritage Farmhouse','heritage-farmhouse','Classic apron-front farmhouse sink with wide basin',
         'The Heritage Farmhouse brings timeless elegance to your kitchen. The wide apron front is easy to clean and the extra-deep 250mm basin handles large cookware with ease.',
         9500,'SS 304 (18/8)','Matte Finish','760×510×250 mm','8.5 kg','1.5 mm','sink_farmhouse.png',1,'active'),

        (1,'Compact Single Bowl','compact-single','Space-saving single bowl ideal for small kitchens',
         'Perfect for compact kitchens and studio apartments. Made from durable SS 202 steel with a polished finish.',
         2800,'SS 202','Mirror Polish','610×455×200 mm','3.2 kg','1.0 mm','sink_single_bowl.png',0,'active'),

        (2,'Pro Dual Basin','pro-dual-basin','Professional grade dual basin for heavy use',
         'Designed for heavy-duty kitchen environments. Extra thick 1.5mm steel construction with drain board.',
         8200,'SS 304 (18/8)','Brushed Finish','1000×500×220 mm','8.0 kg','1.5 mm','sink_double_bowl.png',0,'active')");

    // Testimonials
    $pdo->exec("INSERT IGNORE INTO `testimonials` (name,company,city,rating,message) VALUES
        ('Rajesh Kumar','Rajesh Kitchen World','Patna',5,'Excellent quality sinks at very competitive prices. Our customers love them. Will definitely reorder!'),
        ('Sunita Devi','Home Decor Hub','Gaya',5,'The double bowl sink we bought for our new house is absolutely stunning. Great finish and solid build.'),
        ('Mohammed Iqbal','Iqbal Traders','Aurangabad',4,'Good quality products and prompt delivery. The single bowl sink fits perfectly in our modular kitchen.'),
        ('Priya Singh','Interior Studio','Ranchi',5,'Outstanding product quality. The undermount sink changed the look of our kitchen completely. Highly recommended!'),
        ('Arun Prasad','Prasad Home Solutions','Varanasi',5,'Best steel sink manufacturer in Bihar. Premium quality at factory price. Very satisfied customer.')");

    // Settings
    $pdo->exec("INSERT IGNORE INTO `settings` (skey,svalue) VALUES
        ('hero_title','Premium Stainless Steel Kitchen Sinks'),
        ('hero_subtitle','Crafted with Precision. Built to Last.'),
        ('about_short','Balimeen Private Limited is a leading manufacturer of premium stainless steel kitchen sinks based in Aurangabad, Bihar. With years of manufacturing expertise, we deliver quality products at factory-direct prices.'),
        ('meta_title','Balimeen Private Limited – Stainless Steel Kitchen Sinks'),
        ('meta_desc','Balimeen Pvt Ltd manufactures premium SS 304 stainless steel kitchen sinks. Factory-direct pricing. Based in Aurangabad Bihar.')");

    echo "<h2 style='color:green;font-family:sans-serif;'>✅ Database installed successfully!</h2>";
    echo "<p style='font-family:sans-serif;'>You can now <a href='index.php'>visit the website</a> or <a href='admin/login.php'>log in to admin</a>.</p>";
    echo "<p style='font-family:sans-serif;color:red;'><strong>⚠ Delete this file (install.php) after installation!</strong></p>";

} catch (PDOException $e) {
    echo "<h2 style='color:red;'>Error: " . htmlspecialchars($e->getMessage()) . "</h2>";
}
