<?php
// Run this to add customer/order tables to the existing DB
define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PASS',''); define('DB_NAME','balimeen_steel');
try {
    $pdo = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=utf8mb4", DB_USER, DB_PASS,
        [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
    $pdo->exec("SET FOREIGN_KEY_CHECKS=0");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `customers` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(150) NOT NULL,
        `email` VARCHAR(200) NOT NULL UNIQUE,
        `phone` VARCHAR(20),
        `password` VARCHAR(255) NOT NULL,
        `address` TEXT,
        `city` VARCHAR(100),
        `state` VARCHAR(100),
        `pincode` VARCHAR(10),
        `status` ENUM('active','blocked') DEFAULT 'active',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `orders` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `order_no` VARCHAR(30) NOT NULL UNIQUE,
        `customer_id` INT NOT NULL,
        `name` VARCHAR(150) NOT NULL,
        `phone` VARCHAR(20) NOT NULL,
        `email` VARCHAR(200),
        `address` TEXT NOT NULL,
        `city` VARCHAR(100) NOT NULL,
        `state` VARCHAR(100) NOT NULL,
        `pincode` VARCHAR(10) NOT NULL,
        `subtotal` DECIMAL(12,2) DEFAULT 0,
        `shipping` DECIMAL(10,2) DEFAULT 0,
        `total` DECIMAL(12,2) DEFAULT 0,
        `payment_method` ENUM('cod','bank_transfer','upi') DEFAULT 'cod',
        `payment_status` ENUM('pending','paid','failed') DEFAULT 'pending',
        `order_status` ENUM('pending','confirmed','processing','shipped','delivered','cancelled') DEFAULT 'pending',
        `notes` TEXT,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `order_items` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `order_id` INT NOT NULL,
        `product_id` INT,
        `product_name` VARCHAR(200) NOT NULL,
        `product_image` VARCHAR(300),
        `price` DECIMAL(10,2) NOT NULL,
        `qty` INT NOT NULL DEFAULT 1,
        `subtotal` DECIMAL(12,2) NOT NULL,
        FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
    ) ENGINE=InnoDB");

    $pdo->exec("SET FOREIGN_KEY_CHECKS=1");
    echo "<h2 style='color:green;font-family:sans-serif'>✅ Customer & Order tables created!</h2>";
    echo "<p style='font-family:sans-serif'>Tables added: customers, orders, order_items<br>";
    echo "<a href='index.php'>← Back to website</a> | <a href='pages/login.php'>Customer Login</a> | <a href='pages/register.php'>Register</a></p>";
    echo "<p style='color:red;font-family:sans-serif'><strong>⚠ Delete this file after running!</strong></p>";
} catch(PDOException $e) {
    echo "<h2 style='color:red'>Error: ".htmlspecialchars($e->getMessage())."</h2>";
}
