CREATE DATABASE IF NOT EXISTS xsular_trottinette;
USE xsular_trottinette;

-- Admins Table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories Table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    image VARCHAR(255),
    path VARCHAR(100),
    link_text VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products Table (Scooters, Accessories, Parts)
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50) NOT NULL, -- 'scooter', 'accessory', 'part'
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    image VARCHAR(255),
    rating DECIMAL(3, 1) DEFAULT 0.0,
    tag VARCHAR(50), -- e.g., 'New', 'Pro'
    specs_speed VARCHAR(50),
    specs_range VARCHAR(50),
    specs_charge VARCHAR(50),
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50),
    customer_address TEXT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order Items Table
CREATE TABLE IF NOT EXISTS 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) ON DELETE CASCADE
);

-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    message TEXT NOT NULL,
    status ENUM('Unread', 'Read') DEFAULT 'Unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed Products (Scooters)
INSERT INTO products (category, name, description, price, image, rating, tag, specs_speed, specs_range, specs_charge, stock) VALUES 
('scooter', 'Xsular City Lite', 'Perfect for daily urban commuting. This lightweight, foldable scooter features a powerful 350W motor, intuitive controls, and a sleek design that fits your modern lifestyle.', 599.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460834009880858810/Trottinette-electrique-Ecoxtrem-G2-removebg-preview.png?ex=69685b30&is=696709b0&hm=7abeac432d4f9fd07419984a80c2b0a2a75036cc80bf76bd0db5b52294010a7c&', 4.8, 'Best Seller', '25 km/h', '40 km', '3.5h', 15),
('scooter', 'Xsular Pro GT', 'Enhanced power and comfort for longer rides. The Pro GT features dual suspension, a 500W motor, and an extended battery for those who demand more from their daily ride.', 899.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460834009880858810/Trottinette-electrique-Ecoxtrem-G2-removebg-preview.png?ex=69685b30&is=696709b0&hm=7abeac432d4f9fd07419984a80c2b0a2a75036cc80bf76bd0db5b52294010a7c&', 4.9, 'New Arrival', '45 km/h', '65 km', '5h', 10),
('scooter', 'Xsular Beast X', 'Dual motor beast for off-road and extreme performance. With 2000W of combined power, hydraulic brakes, and full suspension, the Beast X conquers any terrain.', 1499.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460834451281019011/DyWIYAAAAAZJREFUAwApZIL3Dup1UAAAAABJRU5ErkJggg.png?ex=69685b9a&is=69670a1a&hm=838c72ffe7b980ca932aec9c04ebf0cd05d00599faec4e701dc8accaabab69de&', 5.0, 'High Performance', '85 km/h', '120 km', '8h', 5),
('scooter', 'Xsular Urban S', 'Entry-level scooter for short city trips. The Urban S is perfect for beginners or those needing a reliable, affordable option for quick errands around town.', 449.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460834720580370443/lRo2MAAAABklEQVQDAPYJEGAdzmqTAAAAAElFTkSuQmCC.png?ex=69685bda&is=69670a5a&hm=56a7fff5b1d70bbe256263fddf8023a219df1d0adc3810d384217f9444f9b8a8&', 4.6, 'Budget Friendly', '20 km/h', '30 km', '3h', 20),
('scooter', 'Xsular Cruiser', 'The perfect balance of speed and range for daily riders. The Cruiser offers a comfortable riding position, smooth acceleration, and enough power for longer commutes.', 1199.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460834985375301848/wAAAAASUVORK5CYII.png?ex=69685c19&is=69670a99&hm=04801c4ed1d13860257a1b651bbc9583d03bc310a6c2c59899106c4cbec2060f&', 4.9, 'Popular', '60 km/h', '90 km', '6h', 8),
('scooter', 'Xsular Max Pro', 'Our most powerful scooter with premium features. The Max Pro represents the pinnacle of electric scooter technology with dual 1500W motors and unmatched range.', 1899.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460835285435678905/UduAAAAABJRU5ErkJggg.png?ex=69685c61&is=69670ae1&hm=acd5385edc208598ebfdbbfdc09cc4c41b4a1aed401f5b7e9fa7a1ec14d658f0&', 5.0, 'Flagship', '100 km/h', '150 km', '10h', 3),
('scooter', 'Xsular Compact', 'Ultra-compact design for easy storage and transport. Perfect for last-mile commuting with quick-fold mechanism and lightweight frame.', 399.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460835524729110570/4EwAAAABJRU5ErkJggg.png?ex=69685c9a&is=69670b1a&hm=91a88f2b48fd4966ebdd1fbaa471c09e7c34655e7990f867589d53a3bd2d8a44&', 4.5, 'Portable', '18 km/h', '25 km', '2.5h', 12),
('scooter', 'Xsular Sport', 'Designed for performance enthusiasts. Features sport-tuned suspension, aggressive styling, and enhanced acceleration for thrilling rides.', 999.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460835700311326855/r8YTsAAAAGSURBVAMA7JIwBuJ5arYAAAAASUVORK5CYII.png?ex=69685cc3&is=69670b43&hm=511766e2bb1da3db741e1bb761d5cf17e83555e188f1823142535e189d7254b8&', 4.8, 'Athletic', '55 km/h', '75 km', '5.5h', 7);

-- Seed Accessories
INSERT INTO products (category, name, description, price, image, rating, tag) VALUES 
('accessory', 'Pro Safety Helmet', 'Lightweight, ventilated helmet with LED rear light for maximum visibility. Features adjustable straps, removable liner, and meets all safety certifications.', 89.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460836309592571925/3do4zgAAAAGSURBVAMA0B2L30rTbBEAAAAASUVORK5CYII.png?ex=69685d55&is=69670bd5&hm=5e7a46409439e70bc9742ab37745646bbb877a19fa2c16cab7283c5f28478d87&', 4.9, 'Essential'),
('accessory', 'Heavy-Duty U-Lock', 'Hardened steel construction with anti-theft guarantee. This premium lock features a 16mm shackle, dust cover, and includes mounting bracket.', 45.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460836507198689460/zpl5CwAAAAZJREFUAwDYw8Mxd2JWtgAAAABJRU5ErkJggg.png?ex=69685d84&is=69670c04&hm=2240cbf92b043386569b3c2a8443a9f7cb2eca407c4341144771abfde6994544&', 4.7, 'Security'),
('accessory', 'Phone Mount Pro', '360° rotation with shock absorption for all phone sizes. Universal fit with secure grip and one-hand operation.', 29.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460836798292037734/4sNabgAAAAZJREFUAwC8P5aaTBvJwAAAABJRU5ErkJggg.png?ex=69685dc9&is=69670c49&hm=eaee2878af8c3909a100fe23c3c32e224f33d8b7d64de3e529324269994c1d40&', 4.8, 'Utility'),
('accessory', 'LED Light Kit', 'Front and rear LED lights with multiple modes and USB charging. Ultra-bright 1000 lumen front light with 3 modes.', 35.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460837013707165800/zO5lnYAAAAGSURBVAMAb9z99qR4iLYAAAAASUVORK5CYII.png?ex=69685dfd&is=69670c7d&hm=0689a851a7189003585b31b1afe233b2c880373007269214b59da835f1e5a797&', 4.6, 'Safety');

-- Seed Parts
INSERT INTO products (category, name, description, price, image, rating, tag) VALUES 
('part', 'Hub Motor 800W', 'High-torque brushless motor for powerful acceleration and hill climbing. Compatible with most 10-inch wheel scooters.', 189.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460837592743415961/8B74gEAAAABklEQVQDAMkLH4pxRUGjAAAAAElFTkSuQmCC.png?ex=69685e87&is=69670d07&hm=4e15fe90d9cfc01b12aeb0a8b1b63f22b42aa67e857ca1c11018af423803bc4e&', 4.9, 'Engine'),
('part', 'Battery 48V 20Ah', 'Long-lasting battery pack with smart BMS for optimal performance. High-density lithium cells provide 960Wh capacity.', 299.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460837836457644083/81bSkAAAAGSURBVAMACFCqTUPvBH8AAAAASUVORK5CYII.png?ex=69685ec1&is=69670d41&hm=aa7437e90a9ed6affd9e422c266ff461e51c9318416a691a488c1084674f6bf0&', 4.8, 'Power'),
('part', 'Disc Brake Rotor', 'Heat-resistant stainless steel rotor for reliable braking. 140mm diameter with optimized ventilation slots.', 25.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838020692447383/SNnS9wAAAAZJREFUAwCMlpLZMBq0gAAAABJRU5ErkJggg.png?ex=69685eed&is=69670d6d&hm=6737a7f55a3163d7a8e04cccaa0e00357f5eff023d5d079e60f96523be83efd5&', 4.7, 'Brakes'),
('part', 'Suspension Fork', 'Adjustable front suspension for a smoother ride on rough terrain. Features 100mm travel and preload adjustment.', 129.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838187827204157/33cRGkAAAAGSURBVAMAaLEAaiELqOMAAAAASUVORK5CYII.png?ex=69685f15&is=69670d95&hm=13d4bfdab376c72ecf619e936fd9e0b5e219f29ab730ba4c97b93ca856fdd9aa&', 4.6, 'Chassis'),
('part', 'Off-Road Tire Set', 'Puncture-resistant tires with deep treads for all surfaces. Reinforced sidewalls and knobby pattern.', 79.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838386926358786/0it2oWAAAAABJRU5ErkJggg.png?ex=69685f44&is=69670dc4&hm=15c993d991cdb83a0d9dff147c863201181d4b7c32ed4bb26fc2ba86a304b7c5&', 4.8, 'Wheels'),
('part', 'Controller Unit', '48V sine wave controller for smooth motor control. Features regenerative braking and multiple speed modes.', 85.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838514899030121/1kJVLsAAAAGSURBVAMAD3eQj7P5EUYAAAAASUVORK5CYII.png?ex=69685f63&is=69670de3&hm=96b7d85aeea9eea2859a5521b5e5059df7745781a9c5a16ecf19f142b0fa3b49&', 4.5, 'Electronics'),
('part', 'LED Display Panel', 'Waterproof LCD display with speed, battery, and trip data. Backlit for night visibility.', 45.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838689696649379/kknU5gAAAAZJREFUAwBwr5mNPPMfYQAAAABJRU5ErkJggg.png?ex=69685f8c&is=69670e0c&hm=60e98e3e2cffb732ea5bf149b2cdd0314e62ebbd555be4b43d099cfe87f5d8f0&', 4.6, 'Electronics'),
('part', 'Brake Caliper Set', 'Hydraulic brake calipers for powerful stopping. Dual-piston design with adjustable reach.', 55.00, 'https://cdn.discordapp.com/attachments/1361844807336460330/1460838952700350464/6O0x6kAAAAGSURBVAMA17Id5eQ3utsAAAAASUVORK5CYII.png?ex=69685fcb&is=69670e4b&hm=784ca3767fa947faa89ac5cb5974a59770911eb9c2253cb596ebf15ef49ef6a6&', 4.7, 'Brakes');
