<?php
/**
 * Database Setup Script for Rorschach Co.
 * Run this file once to create the database and tables
 */

// Database Configuration
$host = "localhost";
$username = "nicciiu14_rorschach.co";
$password = "rorschach.c026";
$database = "nicciiu14_rorschach.co";

try {
    // Connect without database first to create it
    $pdo = new PDO("mysql:host=$dbHost", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if not exists
    $pdo->exec("CREATE DATABASE IF NOT EXISTS rorschach_co CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "Database 'rorschach_co' created successfully!<br>";
    
    // Use the database
    $pdo->exec("USE rorschach_co");
    
    // Create users table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            email VARCHAR(255) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            full_name VARCHAR(255) NOT NULL,
            phone VARCHAR(50),
            address TEXT,
            role ENUM('customer', 'admin') DEFAULT 'customer',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "Table 'users' created successfully!<br>";
    
    // Create products table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            price DECIMAL(10,2) NOT NULL,
            category VARCHAR(100),
            image_url VARCHAR(500),
            stock_quantity INT DEFAULT 0,
            sizes JSON,
            colors JSON,
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "Table 'products' created successfully!<br>";
    
    // Create orders table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            order_number VARCHAR(50) UNIQUE NOT NULL,
            total_amount DECIMAL(10,2) NOT NULL,
            status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
            shipping_address TEXT,
            payment_method VARCHAR(50),
            payment_status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
            notes TEXT,
            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 SET NULL
        )
    ");
    echo "Table 'orders' created successfully!<br>";
    
    // Create order_items table
    $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(255) NOT NULL,
            product_price DECIMAL(10,2) NOT NULL,
            quantity INT NOT NULL,
            size VARCHAR(20),
            color VARCHAR(50),
            subtotal DECIMAL(10,2) NOT NULL,
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
        )
    ");
    echo "Table 'order_items' created successfully!<br>";
    
    // Insert default admin user (password: admin123)
    $adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
    $stmt = $pdo->prepare("INSERT IGNORE INTO users (email, password_hash, full_name, role) VALUES (?, ?, ?, ?)");
    $stmt->execute(['admin@rorschachco.com', $adminPassword, 'Administrator', 'admin']);
    echo "Default admin user created! (email: admin@rorschachco.com, password: admin123)<br>";
    
    // Insert sample products
    $sampleProducts = [
        [
            'name' => 'Minimalist Cotton Tee',
            'description' => 'Premium organic cotton t-shirt with a relaxed fit. Perfect for everyday wear.',
            'price' => 45.00,
            'category' => 'Tops',
            'image_url' => 'https://images.unsplash.com/photo-1521572163474-6864f9cf17ab?w=600',
            'stock_quantity' => 100,
            'sizes' => json_encode(['XS', 'S', 'M', 'L', 'XL']),
            'colors' => json_encode(['White', 'Black', 'Gray'])
        ],
        [
            'name' => 'Structured Blazer',
            'description' => 'Tailored wool blend blazer with modern silhouette. Sharp and sophisticated.',
            'price' => 285.00,
            'category' => 'Outerwear',
            'image_url' => 'https://images.unsplash.com/photo-1594938298603-c8148c4dae35?w=600',
            'stock_quantity' => 50,
            'sizes' => json_encode(['S', 'M', 'L', 'XL']),
            'colors' => json_encode(['Black', 'Navy', 'Charcoal'])
        ],
        [
            'name' => 'Slim Fit Chinos',
            'description' => 'Clean-lined chino pants in stretch cotton. Versatile for any occasion.',
            'price' => 95.00,
            'category' => 'Bottoms',
            'image_url' => 'https://images.unsplash.com/photo-1473966968600-fa801b869a1a?w=600',
            'stock_quantity' => 75,
            'sizes' => json_encode(['28', '30', '32', '34', '36']),
            'colors' => json_encode(['Black', 'Khaki', 'Navy'])
        ],
        [
            'name' => 'Silk Blend Sweater',
            'description' => 'Luxurious silk-cashmere blend sweater. Soft, warm, and elegant.',
            'price' => 165.00,
            'category' => 'Tops',
            'image_url' => 'https://images.unsplash.com/photo-1434389677669-e08b4cac3105?w=600',
            'stock_quantity' => 40,
            'sizes' => json_encode(['S', 'M', 'L', 'XL']),
            'colors' => json_encode(['Black', 'Ivory', 'Burgundy'])
        ],
        [
            'name' => 'Leather Belt',
            'description' => 'Full-grain leather belt with brushed silver buckle. Timeless accessory.',
            'price' => 65.00,
            'category' => 'Accessories',
            'image_url' => 'https://images.unsplash.com/photo-1553062407-98eeb64c6a62?w=600',
            'stock_quantity' => 60,
            'sizes' => json_encode(['S', 'M', 'L', 'XL']),
            'colors' => json_encode(['Black', 'Brown'])
        ],
        [
            'name' => 'Classic White Shirt',
            'description' => 'Crisp cotton oxford shirt with mother-of-pearl buttons. Wardrobe essential.',
            'price' => 85.00,
            'category' => 'Tops',
            'image_url' => 'https://images.unsplash.com/photo-1596755094514-f87e34085b2c?w=600',
            'stock_quantity' => 80,
            'sizes' => json_encode(['XS', 'S', 'M', 'L', 'XL', 'XXL']),
            'colors' => json_encode(['White', 'Light Blue'])
        ],
        [
            'name' => 'Wool Overcoat',
            'description' => 'Double-breasted wool coat with satin lining. Ultimate winter elegance.',
            'price' => 395.00,
            'category' => 'Outerwear',
            'image_url' => 'https://images.unsplash.com/photo-1539533018447-63fcce2678e3?w=600',
            'stock_quantity' => 25,
            'sizes' => json_encode(['S', 'M', 'L', 'XL']),
            'colors' => json_encode(['Black', 'Camel', 'Charcoal'])
        ],
        [
            'name' => 'Canvas Tote Bag',
            'description' => 'Durable canvas tote with leather handles. Spacious and stylish.',
            'price' => 55.00,
            'category' => 'Accessories',
            'image_url' => 'https://images.unsplash.com/photo-1544816155-12df9643f363?w=600',
            'stock_quantity' => 45,
            'sizes' => json_encode(['One Size']),
            'colors' => json_encode(['Black', 'Natural', 'Gray'])
        ]
    ];
    
    $stmt = $pdo->prepare("INSERT IGNORE INTO products (name, description, price, category, image_url, stock_quantity, sizes, colors) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    foreach ($sampleProducts as $product) {
        $stmt->execute([
            $product['name'],
            $product['description'],
            $product['price'],
            $product['category'],
            $product['image_url'],
            $product['stock_quantity'],
            $product['sizes'],
            $product['colors']
        ]);
    }
    echo "Sample products inserted successfully!<br>";
    
    echo "<br><strong>Database setup complete!</strong><br>";
    echo "You can now use the application.<br>";
    echo "<a href='index.php'>Go to Homepage</a>";
    
} catch (PDOException $e) {
    die("Database setup failed: " . $e->getMessage());
}
