-- 简化版 cPanel 数据库设置脚本
-- 移除了所有需要特殊权限的查询，适用于共享主机环境

-- 直接使用现有数据库 yeahmy_pc
-- 不需要创建数据库或用户，这些在cPanel中已经设置好了

-- 创建所有必要的表
-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 产品表
CREATE TABLE IF NOT EXISTS 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 DEFAULT 0,
    category VARCHAR(50),
    image_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    session_id VARCHAR(100),
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'canceled') DEFAULT 'pending',
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    shipping_address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 订单项目表
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NULL,
    product_name VARCHAR(200),
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- 网站设置表
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(100) NOT NULL DEFAULT 'PC Builder Store',
    language VARCHAR(10) NOT NULL DEFAULT 'en',
    currency VARCHAR(10) DEFAULT 'RM',
    admin_email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PC Builder 专用表
-- 组件分类表
CREATE TABLE IF NOT EXISTS component_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    name_en VARCHAR(50) NOT NULL,
    name_ms VARCHAR(50),
    name_zh VARCHAR(50),
    display_order INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PC组件表
CREATE TABLE IF NOT EXISTS 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_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES component_categories(id) ON DELETE RESTRICT
);

-- PC配置模板表
CREATE TABLE IF NOT EXISTS pc_build_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ms VARCHAR(100),
    name_zh VARCHAR(100),
    description TEXT,
    description_en TEXT,
    description_ms TEXT,
    description_zh TEXT,
    target_use VARCHAR(100) NOT NULL,
    performance_rating INT NOT NULL DEFAULT 5 CHECK (performance_rating >= 1 AND performance_rating <= 10),
    base_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    default_components JSON,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 客户配置表
CREATE TABLE IF NOT EXISTS 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,
    selected_components JSON NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    build_status ENUM('draft', 'saved', 'shared', 'ordered') DEFAULT 'draft',
    share_token VARCHAR(50) NULL UNIQUE,
    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,
    FOREIGN KEY (template_id) REFERENCES pc_build_templates(id) ON DELETE SET NULL
);

-- 兼容性规则表
CREATE TABLE IF NOT EXISTS build_compatibility_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    rule_description TEXT,
    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)
);

-- 购物车表 (session based)
CREATE TABLE IF NOT EXISTS shopping_cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL,
    user_id INT NULL,
    item_type ENUM('product', 'pc_build') NOT NULL,
    item_id INT NULL, -- product_id or build_id
    item_data JSON, -- for pc_build configurations
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_user (user_id)
);

-- 插入基础设置数据
INSERT IGNORE INTO settings (site_name, language, currency, admin_email) VALUES 
('PC Builder Store', 'en', 'RM', 'admin@pcbuilder.com');

-- 插入组件分类 (多语言支持)
INSERT IGNORE INTO component_categories (id, name, name_en, name_ms, name_zh, display_order) VALUES 
(1, 'CPU', 'CPU', 'CPU', 'CPU', 1),
(2, 'Motherboard', 'Motherboard', 'Papan Induk', '主板', 2),
(3, 'RAM', 'RAM', 'RAM', '内存', 3),
(4, 'GPU', 'Graphics Card', 'Kad Grafik', '显卡', 4),
(5, 'Storage', 'Storage', 'Penyimpanan', '存储', 5),
(6, 'PSU', 'Power Supply', 'Unit Bekalan Kuasa', '电源', 6),
(7, 'Case', 'Computer Case', 'Kotak Komputer', '机箱', 7),
(8, 'Cooling', 'Cooling System', 'Sistem Penyejukan', '散热系统', 8);

-- 插入PC配置模板 (多语言支持)
INSERT IGNORE INTO pc_build_templates (name, name_en, name_ms, name_zh, description_en, description_ms, description_zh, target_use, performance_rating, base_price) VALUES 
('Budget Office', 'Budget Office', 'Pejabat Bajet', '预算办公', 'Perfect for basic office work, web browsing, and document editing', 'Sempurna untuk kerja pejabat asas, pelayaran web dan penyuntingan dokumen', '适合基础办公工作、网页浏览和文档编辑', 'Office Work', 4, 1500.00),
('Gaming Starter', 'Gaming Starter', 'Permulaan Gaming', '游戏入门', 'Entry-level gaming build for 1080p gaming at medium settings', 'Binaan gaming peringkat permulaan untuk gaming 1080p pada tetapan sederhana', '入门级游戏配置，支持1080p中等设置游戏', 'Gaming', 6, 3500.00),
('Gaming Pro', 'Gaming Pro', 'Gaming Pro', '游戏专业', 'High-performance gaming build for 1440p gaming at high settings', 'Binaan gaming prestasi tinggi untuk gaming 1440p pada tetapan tinggi', '高性能游戏配置，支持1440p高设置游戏', 'Gaming', 8, 6500.00),
('Gaming Ultimate', 'Gaming Ultimate', 'Gaming Ultimate', '游戏顶级', 'Top-tier gaming build for 4K gaming and VR experiences', 'Binaan gaming peringkat tertinggi untuk gaming 4K dan pengalaman VR', '顶级游戏配置，支持4K游戏和VR体验', 'Gaming', 10, 12000.00),
('Content Creator', 'Content Creator', 'Pencipta Kandungan', '内容创作者', 'Optimized for video editing, 3D rendering, and content creation', 'Dioptimumkan untuk penyuntingan video, rendering 3D dan penciptaan kandungan', '为视频编辑、3D渲染和内容创作优化', 'Content Creation', 9, 8500.00),
('Workstation Pro', 'Workstation Pro', 'Workstation Pro', '工作站专业', 'Professional workstation for CAD, engineering, and scientific computing', 'Workstation profesional untuk CAD, kejuruteraan dan pengkomputeran saintifik', 'CAD、工程和科学计算专业工作站', 'Professional Work', 9, 10000.00),
('Home Entertainment', 'Home Entertainment', 'Hiburan Rumah', '家庭娱乐', 'Perfect HTPC for streaming, light gaming, and media consumption', 'HTPC sempurna untuk streaming, gaming ringan dan penggunaan media', '完美的HTPC，用于流媒体、轻度游戏和媒体消费', 'Entertainment', 5, 2500.00),
('Compact Mini-ITX', 'Compact Mini-ITX', 'Kompak Mini-ITX', '紧凑型 Mini-ITX', 'Small form factor build without compromising performance', 'Binaan faktor bentuk kecil tanpa menjejaskan prestasi', '小体积配置，性能不妥协', 'Compact Gaming', 7, 4500.00),
('Streaming Setup', 'Streaming Setup', 'Setup Streaming', '直播配置', 'Dual-purpose build for gaming and live streaming', 'Binaan dwi-guna untuk gaming dan streaming langsung', '游戏和直播双用途配置', 'Gaming/Streaming', 8, 7500.00),
('AI/ML Workstation', 'AI/ML Workstation', 'Workstation AI/ML', 'AI/ML 工作站', 'Specialized build for machine learning and AI development', 'Binaan khusus untuk pembelajaran mesin dan pembangunan AI', '专门用于机器学习和AI开发的配置', 'AI/Development', 10, 15000.00);

-- 插入样本CPU组件 (马币定价)
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(1, 'Intel Core i3-12100F', 'Intel', 'i3-12100F', 599.00, '{"cores": 4, "threads": 8, "base_clock": "3.3 GHz", "boost_clock": "4.3 GHz", "socket": "LGA1700", "tdp": "58W", "integrated_graphics": false}', '{"socket": "LGA1700", "chipset": ["B660", "H610", "H670", "Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 30, false),
(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", "integrated_graphics": false}', '{"socket": "LGA1700", "chipset": ["B660", "H610", "H670", "Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 25, true),
(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", "integrated_graphics": true}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 15, true),
(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", "integrated_graphics": true}', '{"socket": "AM5", "chipset": ["B650", "B650E", "X670", "X670E"], "memory_support": ["DDR5"]}', 20, true),
(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", "integrated_graphics": true}', '{"socket": "AM5", "chipset": ["B650", "B650E", "X670", "X670E"], "memory_support": ["DDR5"]}', 12, true),
(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", "integrated_graphics": true}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 8, true);

-- 插入样本显卡组件
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(4, 'NVIDIA GeForce GTX 1660 SUPER', 'NVIDIA', 'GTX 1660 SUPER', 1199.00, '{"memory": "6GB GDDR6", "memory_bus": "192-bit", "base_clock": "1530 MHz", "boost_clock": "1785 MHz", "power": "125W", "performance_tier": "1080p Medium-High"}', '{"pcie": "3.0 x16", "power_connectors": "1x 8-pin", "length": "267mm", "recommended_psu": "450W"}', 20, false),
(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", "performance_tier": "1080p High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin", "length": "244mm", "recommended_psu": "550W"}', 30, true),
(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", "performance_tier": "1440p High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin", "length": "267mm", "recommended_psu": "650W"}', 18, true),
(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", "performance_tier": "4K Medium-High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin", "length": "304mm", "recommended_psu": "750W"}', 10, true),
(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", "performance_tier": "1080p High", "ray_tracing": true}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin", "length": "263mm", "recommended_psu": "550W"}', 25, true),
(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", "performance_tier": "1440p High", "ray_tracing": true}', '{"pcie": "4.0 x16", "power_connectors": "2x 8-pin", "length": "287mm", "recommended_psu": "700W"}', 15, true);

-- 插入样本内存组件
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(3, 'Corsair Vengeance LPX 16GB DDR4', 'Corsair', 'CMK16GX4M2B3200C16', 399.00, '{"capacity": "16GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x8GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3200, "compatible_platforms": ["Intel", "AMD"]}', 50, true),
(3, 'G.Skill Trident Z5 32GB DDR5', 'G.Skill', 'F5-6000J3038F16GX2-TZ5K', 899.00, '{"capacity": "32GB", "type": "DDR5", "speed": "6000MHz", "modules": "2x16GB", "cas_latency": "30", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR5", "max_speed": 6000, "compatible_platforms": ["Intel 12th+", "AMD Ryzen 7000+"]}', 20, true),
(3, 'Kingston Fury Beast 32GB DDR4', 'Kingston', 'KF432C16BBK2/32', 699.00, '{"capacity": "32GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x16GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3200, "compatible_platforms": ["Intel", "AMD"]}', 35, true),
(3, 'Corsair Vengeance LPX 8GB DDR4', 'Corsair', 'CMK8GX4M1D3000C16', 199.00, '{"capacity": "8GB", "type": "DDR4", "speed": "3000MHz", "modules": "1x8GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3000, "compatible_platforms": ["Intel", "AMD"]}', 40, false);

-- 成功消息
SELECT '数据库设置完成！所有表格已创建并插入样本数据。' as '状态';

-- 如果数据库不存在，请在 cPanel > MySQL Databases 中创建:
-- 数据库名: yeahmy_pc (如果还没有)
-- 用户名: yeahmy_adminld
-- 确保用户已添加到数据库并给予所有权限

-- 创建所有必要的表
-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 产品表
CREATE TABLE IF NOT EXISTS 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 DEFAULT 0,
    category VARCHAR(50),
    image_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    session_id VARCHAR(100),
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'canceled') DEFAULT 'pending',
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    shipping_address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 订单项目表
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NULL,
    product_name VARCHAR(200),
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- 网站设置表
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(100) NOT NULL DEFAULT 'PC Builder Store',
    language VARCHAR(10) NOT NULL DEFAULT 'en',
    currency VARCHAR(10) DEFAULT 'RM',
    admin_email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PC Builder 专用表
-- 组件分类表
CREATE TABLE IF NOT EXISTS component_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    name_en VARCHAR(50) NOT NULL,
    name_ms VARCHAR(50),
    name_zh VARCHAR(50),
    display_order INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PC组件表
CREATE TABLE IF NOT EXISTS 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_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES component_categories(id) ON DELETE RESTRICT
);

-- PC配置模板表
CREATE TABLE IF NOT EXISTS pc_build_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ms VARCHAR(100),
    name_zh VARCHAR(100),
    description TEXT,
    description_en TEXT,
    description_ms TEXT,
    description_zh TEXT,
    target_use VARCHAR(100) NOT NULL,
    performance_rating INT NOT NULL DEFAULT 5 CHECK (performance_rating >= 1 AND performance_rating <= 10),
    base_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    default_components JSON,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 客户配置表
CREATE TABLE IF NOT EXISTS 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,
    selected_components JSON NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    build_status ENUM('draft', 'saved', 'shared', 'ordered') DEFAULT 'draft',
    share_token VARCHAR(50) NULL UNIQUE,
    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,
    FOREIGN KEY (template_id) REFERENCES pc_build_templates(id) ON DELETE SET NULL
);

-- 兼容性规则表
CREATE TABLE IF NOT EXISTS build_compatibility_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    rule_description TEXT,
    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)
);

-- 购物车表 (session based)
CREATE TABLE IF NOT EXISTS shopping_cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL,
    user_id INT NULL,
    item_type ENUM('product', 'pc_build') NOT NULL,
    item_id INT NULL, -- product_id or build_id
    item_data JSON, -- for pc_build configurations
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_user (user_id)
);

-- 插入基础设置数据
INSERT IGNORE INTO settings (site_name, language, currency, admin_email) VALUES 
('PC Builder Store', 'en', 'RM', 'admin@pcbuilder.com');

-- 插入组件分类 (多语言支持)
INSERT IGNORE INTO component_categories (id, name, name_en, name_ms, name_zh, display_order) VALUES 
(1, 'CPU', 'CPU', 'CPU', 'CPU', 1),
(2, 'Motherboard', 'Motherboard', 'Papan Induk', '主板', 2),
(3, 'RAM', 'RAM', 'RAM', '内存', 3),
(4, 'GPU', 'Graphics Card', 'Kad Grafik', '显卡', 4),
(5, 'Storage', 'Storage', 'Penyimpanan', '存储', 5),
(6, 'PSU', 'Power Supply', 'Unit Bekalan Kuasa', '电源', 6),
(7, 'Case', 'Computer Case', 'Kotak Komputer', '机箱', 7),
(8, 'Cooling', 'Cooling System', 'Sistem Penyejukan', '散热系统', 8);

-- 插入PC配置模板 (多语言支持)
INSERT IGNORE INTO pc_build_templates (name, name_en, name_ms, name_zh, description_en, description_ms, description_zh, target_use, performance_rating, base_price) VALUES 
('Budget Office', 'Budget Office', 'Pejabat Bajet', '预算办公', 'Perfect for basic office work, web browsing, and document editing', 'Sempurna untuk kerja pejabat asas, pelayaran web dan penyuntingan dokumen', '适合基础办公工作、网页浏览和文档编辑', 'Office Work', 4, 1500.00),
('Gaming Starter', 'Gaming Starter', 'Permulaan Gaming', '游戏入门', 'Entry-level gaming build for 1080p gaming at medium settings', 'Binaan gaming peringkat permulaan untuk gaming 1080p pada tetapan sederhana', '入门级游戏配置，支持1080p中等设置游戏', 'Gaming', 6, 3500.00),
('Gaming Pro', 'Gaming Pro', 'Gaming Pro', '游戏专业', 'High-performance gaming build for 1440p gaming at high settings', 'Binaan gaming prestasi tinggi untuk gaming 1440p pada tetapan tinggi', '高性能游戏配置，支持1440p高设置游戏', 'Gaming', 8, 6500.00),
('Gaming Ultimate', 'Gaming Ultimate', 'Gaming Ultimate', '游戏顶级', 'Top-tier gaming build for 4K gaming and VR experiences', 'Binaan gaming peringkat tertinggi untuk gaming 4K dan pengalaman VR', '顶级游戏配置，支持4K游戏和VR体验', 'Gaming', 10, 12000.00),
('Content Creator', 'Content Creator', 'Pencipta Kandungan', '内容创作者', 'Optimized for video editing, 3D rendering, and content creation', 'Dioptimumkan untuk penyuntingan video, rendering 3D dan penciptaan kandungan', '为视频编辑、3D渲染和内容创作优化', 'Content Creation', 9, 8500.00),
('Workstation Pro', 'Workstation Pro', 'Workstation Pro', '工作站专业', 'Professional workstation for CAD, engineering, and scientific computing', 'Workstation profesional untuk CAD, kejuruteraan dan pengkomputeran saintifik', 'CAD、工程和科学计算专业工作站', 'Professional Work', 9, 10000.00),
('Home Entertainment', 'Home Entertainment', 'Hiburan Rumah', '家庭娱乐', 'Perfect HTPC for streaming, light gaming, and media consumption', 'HTPC sempurna untuk streaming, gaming ringan dan penggunaan media', '完美的HTPC，用于流媒体、轻度游戏和媒体消费', 'Entertainment', 5, 2500.00),
('Compact Mini-ITX', 'Compact Mini-ITX', 'Kompak Mini-ITX', '紧凑型 Mini-ITX', 'Small form factor build without compromising performance', 'Binaan faktor bentuk kecil tanpa menjejaskan prestasi', '小体积配置，性能不妥协', 'Compact Gaming', 7, 4500.00),
('Streaming Setup', 'Streaming Setup', 'Setup Streaming', '直播配置', 'Dual-purpose build for gaming and live streaming', 'Binaan dwi-guna untuk gaming dan streaming langsung', '游戏和直播双用途配置', 'Gaming/Streaming', 8, 7500.00),
('AI/ML Workstation', 'AI/ML Workstation', 'Workstation AI/ML', 'AI/ML 工作站', 'Specialized build for machine learning and AI development', 'Binaan khusus untuk pembelajaran mesin dan pembangunan AI', '专门用于机器学习和AI开发的配置', 'AI/Development', 10, 15000.00);

-- 插入样本CPU组件 (马币定价)
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(1, 'Intel Core i3-12100F', 'Intel', 'i3-12100F', 599.00, '{"cores": 4, "threads": 8, "base_clock": "3.3 GHz", "boost_clock": "4.3 GHz", "socket": "LGA1700", "tdp": "58W", "integrated_graphics": false}', '{"socket": "LGA1700", "chipset": ["B660", "H610", "H670", "Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 30, false),
(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", "integrated_graphics": false}', '{"socket": "LGA1700", "chipset": ["B660", "H610", "H670", "Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 25, true),
(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", "integrated_graphics": true}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 15, true),
(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", "integrated_graphics": true}', '{"socket": "AM5", "chipset": ["B650", "B650E", "X670", "X670E"], "memory_support": ["DDR5"]}', 20, true),
(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", "integrated_graphics": true}', '{"socket": "AM5", "chipset": ["B650", "B650E", "X670", "X670E"], "memory_support": ["DDR5"]}', 12, true),
(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", "integrated_graphics": true}', '{"socket": "LGA1700", "chipset": ["Z690", "Z790"], "memory_support": ["DDR4", "DDR5"]}', 8, true);

-- 插入样本显卡组件
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(4, 'NVIDIA GeForce GTX 1660 SUPER', 'NVIDIA', 'GTX 1660 SUPER', 1199.00, '{"memory": "6GB GDDR6", "memory_bus": "192-bit", "base_clock": "1530 MHz", "boost_clock": "1785 MHz", "power": "125W", "performance_tier": "1080p Medium-High"}', '{"pcie": "3.0 x16", "power_connectors": "1x 8-pin", "length": "267mm", "recommended_psu": "450W"}', 20, false),
(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", "performance_tier": "1080p High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin", "length": "244mm", "recommended_psu": "550W"}', 30, true),
(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", "performance_tier": "1440p High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin", "length": "267mm", "recommended_psu": "650W"}', 18, true),
(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", "performance_tier": "4K Medium-High", "ray_tracing": true, "dlss": "3.0"}', '{"pcie": "4.0 x16", "power_connectors": "1x 12-pin", "length": "304mm", "recommended_psu": "750W"}', 10, true),
(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", "performance_tier": "1080p High", "ray_tracing": true}', '{"pcie": "4.0 x16", "power_connectors": "1x 8-pin", "length": "263mm", "recommended_psu": "550W"}', 25, true),
(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", "performance_tier": "1440p High", "ray_tracing": true}', '{"pcie": "4.0 x16", "power_connectors": "2x 8-pin", "length": "287mm", "recommended_psu": "700W"}', 15, true);

-- 插入样本内存组件
INSERT IGNORE INTO pc_components (category_id, name, brand, model, price, specifications, compatibility_data, stock_quantity, is_featured) VALUES 
(3, 'Corsair Vengeance LPX 16GB DDR4', 'Corsair', 'CMK16GX4M2B3200C16', 399.00, '{"capacity": "16GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x8GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3200, "compatible_platforms": ["Intel", "AMD"]}', 50, true),
(3, 'G.Skill Trident Z5 32GB DDR5', 'G.Skill', 'F5-6000J3038F16GX2-TZ5K', 899.00, '{"capacity": "32GB", "type": "DDR5", "speed": "6000MHz", "modules": "2x16GB", "cas_latency": "30", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR5", "max_speed": 6000, "compatible_platforms": ["Intel 12th+", "AMD Ryzen 7000+"]}', 20, true),
(3, 'Kingston Fury Beast 32GB DDR4', 'Kingston', 'KF432C16BBK2/32', 699.00, '{"capacity": "32GB", "type": "DDR4", "speed": "3200MHz", "modules": "2x16GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3200, "compatible_platforms": ["Intel", "AMD"]}', 35, true),
(3, 'Corsair Vengeance LPX 8GB DDR4', 'Corsair', 'CMK8GX4M1D3000C16', 199.00, '{"capacity": "8GB", "type": "DDR4", "speed": "3000MHz", "modules": "1x8GB", "cas_latency": "16", "voltage": "1.35V", "form_factor": "DIMM"}', '{"memory_type": "DDR4", "max_speed": 3000, "compatible_platforms": ["Intel", "AMD"]}', 40, false);

-- 显示表格创建结果
SHOW TABLES;

-- 验证数据插入
SELECT COUNT(*) as component_categories_count FROM component_categories;
SELECT COUNT(*) as pc_components_count FROM pc_components;
SELECT COUNT(*) as templates_count FROM pc_build_templates;

SELECT 'Database setup completed successfully! ✅' as Status;
