-- Add Shop Categories, Market Categories, and Sliders tables

-- ====================================
-- 1. SHOP_CATEGORIES TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS shop_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_bn VARCHAR(255),
    icon_url VARCHAR(500),
    parent_id VARCHAR(100),
    level INT DEFAULT 0,
    order_index INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 2. MARKET_CATEGORIES TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS market_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_bn VARCHAR(255),
    icon_url VARCHAR(500),
    parent_id VARCHAR(100),
    level INT DEFAULT 0,
    order_index INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 3. SLIDERS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS sliders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    slider_id VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(255),
    description TEXT,
    image_url VARCHAR(500) NOT NULL,
    action_type VARCHAR(50),
    action_url VARCHAR(500),
    target_id VARCHAR(100),
    position VARCHAR(50) DEFAULT 'home', -- 'home', 'shop', 'market'
    order_index INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    start_date DATETIME,
    end_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_position (position),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- Sample Data (Optional but helpful for testing)
-- ====================================
INSERT IGNORE INTO shop_categories (category_id, name, name_bn, level, is_active) VALUES
('cat_shop_001', 'Electronics', 'ইলেকট্রনিক্স', 0, 1),
('cat_shop_002', 'Fashion', 'ফ্যাশন', 0, 1),
('cat_shop_003', 'Groceries', 'মুদি বাজার', 0, 1);

INSERT IGNORE INTO market_categories (category_id, name, name_bn, level, is_active) VALUES
('cat_mkt_001', 'Daily Market', 'কাঁচা বাজার', 0, 1),
('cat_mkt_002', 'Fish & Meat', 'মাছ ও মাংস', 0, 1);