CREATE DATABASE multilingual_ecommerce;

USE multilingual_ecommerce;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'completed', 'canceled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(100) NOT NULL,
    language VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO settings (site_name, language) VALUES ('My E-commerce Site', 'en');

-- PC Builder Tables
CREATE TABLE component_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    display_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pc_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    specifications JSON,
    compatibility_data JSON,
    image_url VARCHAR(500),
    stock_quantity INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES component_categories(id)
);

CREATE TABLE pc_build_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    target_use VARCHAR(100) NOT NULL,
    performance_rating INT NOT NULL DEFAULT 5,
    base_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    components JSON,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_builds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    session_id VARCHAR(100) NOT NULL,
    build_name VARCHAR(200),
    template_id INT NULL,
    components JSON NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    build_status ENUM('draft', 'saved', 'ordered') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (template_id) REFERENCES pc_build_templates(id)
);

CREATE TABLE build_compatibility_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    component_category1 INT NOT NULL,
    component_category2 INT NOT NULL,
    compatibility_rule JSON NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (component_category1) REFERENCES component_categories(id),
    FOREIGN KEY (component_category2) REFERENCES component_categories(id)
);

-- Insert component categories
INSERT INTO component_categories (name, display_order) VALUES 
('CPU', 1),
('Motherboard', 2),
('RAM', 3),
('GPU', 4),
('Storage', 5),
('PSU', 6),
('Case', 7),
('Cooling', 8);

-- Insert PC build templates
INSERT INTO pc_build_templates (name, description, target_use, performance_rating, base_price, components) VALUES 
('Budget Office', 'Perfect for basic office work, web browsing, and document editing', 'Office Work', 4, 1500.00, '{}'),
('Gaming Starter', 'Entry-level gaming build for 1080p gaming at medium settings', 'Gaming', 6, 3500.00, '{}'),
('Gaming Pro', 'High-performance gaming build for 1440p gaming at high settings', 'Gaming', 8, 6500.00, '{}'),
('Gaming Ultimate', 'Top-tier gaming build for 4K gaming and VR', 'Gaming', 10, 12000.00, '{}'),
('Content Creator', 'Optimized for video editing, 3D rendering, and content creation', 'Content Creation', 9, 8500.00, '{}'),
('Workstation Pro', 'Professional workstation for CAD, engineering, and scientific computing', 'Professional Work', 9, 10000.00, '{}'),
('Home Entertainment', 'Perfect HTPC for streaming, light gaming, and media consumption', 'Entertainment', 5, 2500.00, '{}'),
('Compact Mini-ITX', 'Small form factor build without compromising performance', 'Compact Gaming', 7, 4500.00, '{}'),
('Streaming Setup', 'Dual-purpose build for gaming and live streaming', 'Gaming/Streaming', 8, 7500.00, '{}'),
('AI/ML Workstation', 'Specialized build for machine learning and AI development', 'AI/Development', 10, 15000.00, '{}');

-- Sample CPU components
INSERT INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity) VALUES 
(1, 'Intel Core i5-13400F', 'Intel', 'i5-13400F', 899.00, '{"cores": 10, "threads": 16, "base_clock": "2.5 GHz", "boost_clock": "4.6 GHz", "socket": "LGA1700", "tdp": "148W"}', '{"socket": "LGA1700", "chipset": ["B660", "H610", "Z690", "Z790"]}', 25),
(1, 'Intel Core i7-13700K', 'Intel', 'i7-13700K', 1699.00, '{"cores": 16, "threads": 24, "base_clock": "3.4 GHz", "boost_clock": "5.4 GHz", "socket": "LGA1700", "tdp": "253W"}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"]}', 15),
(1, 'AMD Ryzen 5 7600X', 'AMD', 'Ryzen 5 7600X', 1299.00, '{"cores": 6, "threads": 12, "base_clock": "4.7 GHz", "boost_clock": "5.3 GHz", "socket": "AM5", "tdp": "105W"}', '{"socket": "AM5", "chipset": ["B650", "X670"]}', 20),
(1, 'AMD Ryzen 7 7800X3D', 'AMD', 'Ryzen 7 7800X3D', 2299.00, '{"cores": 8, "threads": 16, "base_clock": "4.2 GHz", "boost_clock": "5.0 GHz", "socket": "AM5", "tdp": "120W", "3d_cache": "96MB"}', '{"socket": "AM5", "chipset": ["B650", "X670"]}', 12),
(1, 'Intel Core i9-13900K', 'Intel', 'i9-13900K', 2699.00, '{"cores": 24, "threads": 32, "base_clock": "3.0 GHz", "boost_clock": "5.8 GHz", "socket": "LGA1700", "tdp": "253W"}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"]}', 8);

-- Sample GPU components
INSERT INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity) VALUES 
(4, 'NVIDIA GeForce RTX 4060', 'NVIDIA', 'RTX 4060', 1599.00, '{"memory": "8GB GDDR6", "memory_bus": "128-bit", "base_clock": "1830 MHz", "boost_clock": "2460 MHz", "power": "115W"}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin"}', 30),
(4, 'NVIDIA GeForce RTX 4070', 'NVIDIA', 'RTX 4070', 2899.00, '{"memory": "12GB GDDR6X", "memory_bus": "192-bit", "base_clock": "1920 MHz", "boost_clock": "2475 MHz", "power": "200W"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin"}', 18),
(4, 'NVIDIA GeForce RTX 4080', 'NVIDIA', 'RTX 4080', 5299.00, '{"memory": "16GB GDDR6X", "memory_bus": "256-bit", "base_clock": "2205 MHz", "boost_clock": "2505 MHz", "power": "320W"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin"}', 10),
(4, 'AMD Radeon RX 7600', 'AMD', 'RX 7600', 1399.00, '{"memory": "8GB GDDR6", "memory_bus": "128-bit", "base_clock": "1720 MHz", "boost_clock": "2655 MHz", "power": "165W"}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin"}', 25),
(4, 'AMD Radeon RX 7800 XT', 'AMD', 'RX 7800 XT', 2699.00, '{"memory": "16GB GDDR6", "memory_bus": "256-bit", "base_clock": "1295 MHz", "boost_clock": "2430 MHz", "power": "263W"}', '{"pcie": "4.0 x16", "power_connectors": "2x 8-pin"}', 15);

-- Sample RAM components
INSERT INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity) VALUES 
(3, 'Corsair Vengeance LPX 16GB', 'Corsair', 'CMK16GX4M2B3200C16', 399.00, '{"capacity": "16GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x8GB", "cas_latency": "16", "voltage": "1.35V"}', '{"memory_type": "DDR4", "max_speed": 3200}', 50),
(3, 'G.Skill Trident Z5 32GB', 'G.Skill', 'F5-6000J3038F16GX2-TZ5K', 899.00, '{"capacity": "32GB", "type": "DDR5", "speed": "6000MHz", "modules": "2x16GB", "cas_latency": "30", "voltage": "1.35V"}', '{"memory_type": "DDR5", "max_speed": 6000}', 20),
(3, 'Kingston Fury Beast 32GB', 'Kingston', 'KF432C16BBK2/32', 699.00, '{"capacity": "32GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x16GB", "cas_latency": "16", "voltage": "1.35V"}', '{"memory_type": "DDR4", "max_speed": 3200}', 35);