-- Nandail Seba Database Schema
-- Created for MySQL 8.0+

-- ====================================
-- 1. SERVICE_POSTS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS service_posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id VARCHAR(100) UNIQUE NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    user_email VARCHAR(255),
    user_phone VARCHAR(20),
    
    -- Category Info
    category_id VARCHAR(100) NOT NULL,
    category_name VARCHAR(255) NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    
    -- Provider Info
    provider_name VARCHAR(255) NOT NULL,
    business_name VARCHAR(255),
    contact_person VARCHAR(255),
    
    -- Contact
    mobile VARCHAR(20) NOT NULL,
    whatsapp VARCHAR(20),
    email VARCHAR(255),
    
    -- Location
    district VARCHAR(100) NOT NULL,
    upazila VARCHAR(100) NOT NULL,
    service_area JSON,
    full_address TEXT,
    
    -- Description
    description TEXT NOT NULL,
    price_range VARCHAR(100),
    minimum_charge DECIMAL(10,2) DEFAULT 0,
    negotiable BOOLEAN DEFAULT TRUE,
    
    -- Media
    photos JSON,
    video_url VARCHAR(500),
    
    -- Dynamic Fields
    custom_fields JSON,
    
    -- Rating
    rating DECIMAL(3,2) DEFAULT 0,
    total_reviews INT DEFAULT 0,
    completed_jobs INT DEFAULT 0,
    
    -- Status
    verified BOOLEAN DEFAULT FALSE,
    active BOOLEAN DEFAULT TRUE,
    premium BOOLEAN DEFAULT FALSE,
    
    -- Stats
    view_count INT DEFAULT 0,
    contact_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_active TIMESTAMP NULL,
    
    INDEX idx_user (user_id),
    INDEX idx_category (category_id),
    INDEX idx_service_type (service_type),
    INDEX idx_location (district, upazila),
    INDEX idx_active (active),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 2. PRODUCTS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id VARCHAR(100) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_price VARCHAR(50) NOT NULL,
    product_des TEXT NOT NULL,
    product_rating FLOAT DEFAULT 0,
    product_discount VARCHAR(50),
    product_brand VARCHAR(255),
    product_image VARCHAR(500),
    product_category VARCHAR(100) NOT NULL,
    
    -- Location
    latitude DOUBLE DEFAULT 0,
    longitude DOUBLE DEFAULT 0,
    location_address TEXT,
    
    -- Seller
    seller_id VARCHAR(100) NOT NULL,
    product_phone VARCHAR(20),
    shop_id VARCHAR(100),
    
    -- Type
    is_vendor_product BOOLEAN DEFAULT FALSE,
    
    -- Featured
    show_on_home BOOLEAN DEFAULT FALSE,
    home_display_order INT DEFAULT 0,
    is_featured_active BOOLEAN DEFAULT TRUE,
    featured_section_title VARCHAR(255),
    section_position INT DEFAULT 2,
    
    -- Variants
    variant_type VARCHAR(100),
    variant_options JSON,
    default_variant VARCHAR(100),
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_seller (seller_id),
    INDEX idx_shop (shop_id),
    INDEX idx_category (product_category),
    INDEX idx_featured (show_on_home, home_display_order),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 3. VENDORS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS vendors (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vendor_id VARCHAR(100) UNIQUE NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    
    -- Shop Info
    shop_name VARCHAR(255) NOT NULL,
    shop_name_bn VARCHAR(255),
    shop_description TEXT NOT NULL,
    shop_address TEXT NOT NULL,
    shop_phone VARCHAR(20) NOT NULL,
    
    -- Owner Info
    owner_name VARCHAR(255) NOT NULL,
    owner_email VARCHAR(255),
    
    -- Images
    cover_image_url VARCHAR(500),
    logo_image_url VARCHAR(500),
    
    -- Stats
    total_products BIGINT DEFAULT 0,
    total_orders BIGINT DEFAULT 0,
    total_revenue BIGINT DEFAULT 0,
    rating DOUBLE DEFAULT 0,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    show_on_home BOOLEAN DEFAULT FALSE,
    home_display_order INT DEFAULT 0,
    
    -- Location
    latitude DOUBLE DEFAULT 0,
    longitude DOUBLE DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_active (is_active),
    INDEX idx_featured (show_on_home, home_display_order),
    UNIQUE KEY unique_user_vendor (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 4. REVIEWS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS reviews (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    review_id VARCHAR(100) UNIQUE NOT NULL,
    
    -- User Info
    user_id VARCHAR(100) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    
    -- Target
    target_id VARCHAR(100) NOT NULL,
    target_type VARCHAR(50) NOT NULL, -- 'product' or 'service'
    
    -- Review Data
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT NOT NULL,
    
    -- Engagement
    likes INT DEFAULT 0,
    liked_by JSON,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_target (target_id, target_type),
    INDEX idx_user (user_id),
    INDEX idx_rating (rating),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 5. CONVERSATIONS TABLE (Optional - can keep in Firestore)
-- ====================================
CREATE TABLE IF NOT EXISTS conversations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    conversation_id VARCHAR(100) UNIQUE NOT NULL,
    
    -- Participants
    participant1_id VARCHAR(100) NOT NULL,
    participant2_id VARCHAR(100) NOT NULL,
    
    -- Last Message
    last_message TEXT,
    last_message_time TIMESTAMP,
    
    -- Unread Counts
    unread_count_p1 INT DEFAULT 0,
    unread_count_p2 INT DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_participants (participant1_id, participant2_id),
    UNIQUE KEY unique_conversation (participant1_id, participant2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 6. MESSAGES TABLE (Optional - can keep in Firestore)
-- ====================================
CREATE TABLE IF NOT EXISTS messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message_id VARCHAR(100) UNIQUE NOT NULL,
    conversation_id VARCHAR(100) NOT NULL,
    
    -- Sender/Receiver
    sender_id VARCHAR(100) NOT NULL,
    receiver_id VARCHAR(100) NOT NULL,
    
    -- Message Content
    message_text TEXT NOT NULL,
    message_type VARCHAR(50) DEFAULT 'text',
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_conversation (conversation_id, created_at),
    INDEX idx_sender (sender_id),
    INDEX idx_receiver (receiver_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 7. LOCATIONS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS locations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    location_id VARCHAR(100) UNIQUE NOT NULL,
    
    -- Location Data
    district VARCHAR(100) NOT NULL,
    upazila VARCHAR(100) NOT NULL,
    unions JSON,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_location (district, upazila),
    UNIQUE KEY unique_location (district, upazila)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 8. SERVICE_CATEGORIES TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS service_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id VARCHAR(100) UNIQUE NOT NULL,
    
    -- Names
    name VARCHAR(255) NOT NULL,
    name_bn VARCHAR(255),
    name_en VARCHAR(255),
    
    -- Hierarchy
    parent_id VARCHAR(100),
    icon_url VARCHAR(500),
    
    -- Dynamic Form Schema
    form_schema JSON,
    detail_ui_type VARCHAR(100),
    
    -- Order & Status
    order_index INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_parent (parent_id),
    INDEX idx_active (is_active),
    INDEX idx_order (order_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- Sample Data for Locations (Nandail)
-- ====================================
INSERT INTO locations (location_id, district, upazila, unions) VALUES
('nandail_001', 'Mymensingh', 'Nandail', JSON_ARRAY(
    'Nandail Sadar',
    'Char Khidirpur',
    'Muktapur',
    'Sherpur',
    'Achintapur',
    'Rajgati',
    'Singheshwar',
    'Kanihari',
    'Gangail'
))
ON DUPLICATE KEY UPDATE unions = VALUES(unions);

-- ====================================
-- Schema Created Successfully
-- ====================================
SELECT 'Database schema created successfully!' AS status;