-- Create database
CREATE DATABASE IF NOT EXISTS infogini_infogini;
USE infogini_infogini;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    country VARCHAR(50) DEFAULT 'India',
    region VARCHAR(50),
    city VARCHAR(50),
    role ENUM('user', 'admin') DEFAULT 'user',
    isActive BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_userId (userId),
    INDEX idx_email (email)
);

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subcategories JSON,
    isActive BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(100) NOT NULL,
    subcategory VARCHAR(100),
    images JSON,
    videoUrl VARCHAR(500),
    country VARCHAR(50) DEFAULT 'India',
    region VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    seller VARCHAR(50) NOT NULL,
    sellerName VARCHAR(100),
    sellerEmail VARCHAR(100),
    sellerPhone VARCHAR(20),
    views INT DEFAULT 0,
    isActive BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (seller) REFERENCES users(userId) ON DELETE CASCADE,
    INDEX idx_category (category),
    INDEX idx_seller (seller),
    INDEX idx_isActive (isActive)
);

-- Reviews table
CREATE TABLE IF NOT EXISTS reviews (
    id VARCHAR(50) PRIMARY KEY,
    productId VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    title VARCHAR(255),
    review TEXT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (productId) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_productId (productId)
);

-- Insert default categories
INSERT IGNORE INTO categories (id, name, subcategories, isActive) VALUES
(1, 'Education', '["School", "College", "Teacher", "Student"]', TRUE),
(2, 'Mobile', '[]', TRUE),
(3, 'Laptop/Desktop', '[]', TRUE),
(4, 'Electronics', '[]', TRUE),
(5, 'Fashion', '[]', TRUE),
(6, 'Health', '[]', TRUE),
(7, 'Acting, Dance & Music', '[]', TRUE),
(8, 'Astrology', '[]', TRUE),
(9, 'Hotel & Restaurant', '[]', TRUE),
(10, 'Insurance', '[]', TRUE),
(11, 'Lodge & Halls', '[]', TRUE),
(12, 'Tour & Travel', '[]', TRUE);