<?php
session_start();

// Database connection
$host = 'localhost';
$db = 'zemaraim_interior';
$user = 'zemaraim';
$password = '8wwxk]-X3Kx]CJ]5';

$conn = new mysqli($host, $user, $password, $db);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create orders table
$orders_table = "CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    billing_country VARCHAR(100) NOT NULL,
    billing_address VARCHAR(255) NOT NULL,
    billing_city VARCHAR(100) NOT NULL,
    billing_postcode VARCHAR(20) NOT NULL,
    shipping_country VARCHAR(100),
    shipping_address VARCHAR(255),
    shipping_city VARCHAR(100),
    shipping_postcode VARCHAR(20),
    company VARCHAR(100),
    order_notes TEXT,
    payment_method VARCHAR(50) NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(10, 2) NOT NULL,
    shipping DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    tracking_number VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)";

if ($conn->query($orders_table) === TRUE) {
    echo "✅ Orders table created successfully<br>";
} else {
    echo "❌ Error creating orders table: " . $conn->error . "<br>";
}

// Create order_items table
$order_items_table = "CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
)";

if ($conn->query($order_items_table) === TRUE) {
    echo "✅ Order items table created successfully<br>";
} else {
    echo "❌ Error creating order items table: " . $conn->error . "<br>";
}

// Create coupons table
$coupons_table = "CREATE TABLE IF NOT EXISTS coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255),
    discount_type VARCHAR(20) NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    min_purchase DECIMAL(10, 2),
    max_usage INT,
    current_usage INT DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($coupons_table) === TRUE) {
    echo "✅ Coupons table created successfully<br>";
} else {
    echo "❌ Error creating coupons table: " . $conn->error . "<br>";
}

// Create order_tracking table
$tracking_table = "CREATE TABLE IF NOT EXISTS order_tracking (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
)";

if ($conn->query($tracking_table) === TRUE) {
    echo "✅ Order tracking table created successfully<br>";
} else {
    echo "❌ Error creating order tracking table: " . $conn->error . "<br>";
}

// Add sample coupon
$sample_coupon = "INSERT IGNORE INTO coupons (code, description, discount_type, discount_value, min_purchase, max_usage, is_active, expires_at)
VALUES ('WELCOME10', '10% off on first purchase', 'percentage', 10, 0, 100, 1, DATE_ADD(NOW(), INTERVAL 30 DAY))";

if ($conn->query($sample_coupon) === TRUE) {
    echo "✅ Sample coupon added successfully<br>";
} else {
    echo "❌ Error adding sample coupon: " . $conn->error . "<br>";
}

echo "<br><h3>✅ Database migration completed!</h3>";
echo "<a href='../cart.php'>Go to Cart</a>";

$conn->close();
?>
