-- 1. TABLA: categories
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(120) NOT NULL UNIQUE
);

-- 2. TABLA: cities
CREATE TABLE IF NOT EXISTS cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    state_province VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE
);

-- 3. TABLA: merchants
CREATE TABLE IF NOT EXISTS merchants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(150) NOT NULL,
    description TEXT,
    phone_numbers TEXT,
    website_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. TABLA: admin_users (Corregido para coincidir con adminAuthController.js)
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    secret_token VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. TABLA: ads
CREATE TABLE IF NOT EXISTS ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    category_id INT NOT NULL,
    city_id INT NOT NULL,
    ad_name VARCHAR(150) NOT NULL UNIQUE,
    meta_description VARCHAR(255) NOT NULL,
    video_embed_url TEXT,
    location_data TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'activo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Restricciones de Integridad Referencial (FOREIGN KEYS)
    CONSTRAINT fk_ads_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    CONSTRAINT fk_ads_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ads_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE RESTRICT,
    CONSTRAINT chk_status CHECK (status IN ('activo', 'inactivo'))
);

-- 6. TABLA: ad_photos
CREATE TABLE IF NOT EXISTS ad_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    orientation VARCHAR(20) NOT NULL,

    CONSTRAINT fk_photos_ad FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE,
    CONSTRAINT chk_orientation CHECK (orientation IN ('landscape', 'portrait'))
);

-- =================================================================
-- LÓGICA DE NEGOCIO Y PERFORMANCE: ÍNDICES DE BÚSQUEDA
-- =================================================================

CREATE INDEX idx_ads_city_id ON ads(city_id);
CREATE INDEX idx_ads_category_id ON ads(category_id);
CREATE INDEX idx_ads_status ON ads(status);
CREATE INDEX idx_ads_city_category_status ON ads(city_id, category_id, status);
CREATE INDEX idx_ad_photos_ad_id ON ad_photos(ad_id);