-- =============================================
-- Electro E-Commerce Database Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS electro_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE electro_shop;

-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    description TEXT,
    color VARCHAR(100),
    selling_status ENUM('available','out_of_stock','coming_soon') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Product Images Table (up to 8 images per product)
CREATE TABLE IF NOT EXISTS product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Contact Numbers Table (5 phone numbers for random call)
CREATE TABLE IF NOT EXISTS contact_numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL,
    whatsapp_number VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    label VARCHAR(100)
) ENGINE=InnoDB;

-- Admin Users Table
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Site Settings Table
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =============================================
-- Default Data
-- =============================================

-- Default Admin (username: admin, password: Admin@123)
INSERT INTO admin_users (username, password) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- 5 Contact Numbers
INSERT INTO contact_numbers (phone_number, whatsapp_number, is_active, label) VALUES
('+919876543210', '919876543210', 1, 'Sales 1'),
('+919876543211', '919876543211', 1, 'Sales 2'),
('+919876543212', '919876543212', 1, 'Sales 3'),
('+919876543213', '919876543213', 1, 'Support 1'),
('+919876543214', '919876543214', 1, 'Support 2');

-- Site Settings
INSERT INTO site_settings (setting_key, setting_value) VALUES
('site_name', 'Electro Shop'),
('site_tagline', 'Best Electronics at Best Prices'),
('whatsapp_message_template', 'Hi! I am interested in *{product_name}* priced at *₹{price}*. Product Link: {product_link}');

-- Sample Products
INSERT INTO products (name, price, description, color, selling_status) VALUES
('Smart 4K LED TV 55"', 45999.00, 'Experience stunning 4K Ultra HD resolution with HDR support. Smart TV with built-in WiFi, Netflix, YouTube and all major streaming apps. Voice control enabled with Alexa and Google Assistant.', 'Black', 'available'),
('Wireless Noise-Cancelling Headphones', 8999.00, 'Premium sound quality with active noise cancellation. 30-hour battery life with fast charging. Foldable design for portability. Compatible with all Bluetooth devices.', 'Midnight Blue', 'available'),
('Gaming Laptop 15.6"', 72500.00, 'High-performance gaming laptop with Intel Core i7, 16GB RAM, 512GB SSD, and NVIDIA RTX 3060 graphics. 144Hz display for smooth gaming experience.', 'Space Grey', 'available');
