CREATE TABLE IF NOT EXISTS ad_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    radio_id INT NOT NULL,
    brand_name VARCHAR(160) NOT NULL,
    campaign_name VARCHAR(180) NOT NULL,
    total_broadcasts INT NOT NULL DEFAULT 1,
    broadcasts_done INT NOT NULL DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    interval_minutes INT NOT NULL DEFAULT 60,
    status ENUM('draft','active','paused','finished') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_ad_campaigns_user_radio (user_id, radio_id),
    KEY idx_ad_campaigns_status (radio_id, status),
    KEY idx_ad_campaigns_dates (radio_id, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS ad_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    relative_path VARCHAR(255) NOT NULL,
    public_url VARCHAR(255) NULL,
    duration_seconds INT NOT NULL DEFAULT 0,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    mime_type VARCHAR(120) NULL,
    status ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    KEY idx_ad_files_campaign (campaign_id),
    KEY idx_ad_files_status (campaign_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS ad_jingles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    radio_id INT NULL,
    campaign_id INT NULL,
    type ENUM('intro','outro') NOT NULL,
    name VARCHAR(160) NOT NULL,
    relative_path VARCHAR(255) NOT NULL,
    public_url VARCHAR(255) NULL,
    duration_seconds INT NOT NULL DEFAULT 0,
    status ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_ad_jingles_radio (user_id, radio_id, type, status),
    KEY idx_ad_jingles_campaign (campaign_id, type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS ad_breaks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    radio_id INT NOT NULL,
    scheduled_at DATETIME NOT NULL,
    status ENUM('planned','played','skipped','failed') NOT NULL DEFAULT 'planned',
    total_duration_seconds INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_ad_breaks_radio_schedule (radio_id, scheduled_at),
    KEY idx_ad_breaks_status (radio_id, status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS ad_break_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad_break_id INT NOT NULL,
    item_type ENUM('intro','ad','outro') NOT NULL DEFAULT 'ad',
    campaign_id INT NULL,
    file_id INT NULL,
    jingle_id INT NULL,
    position INT NOT NULL DEFAULT 0,
    duration_seconds INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    KEY idx_ad_break_items_break (ad_break_id, position),
    KEY idx_ad_break_items_campaign (campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS ad_broadcast_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NULL,
    ad_break_id INT NULL,
    file_id INT NULL,
    radio_id INT NOT NULL,
    station VARCHAR(160) NOT NULL,
    played_at DATETIME NOT NULL,
    duration_seconds INT NOT NULL DEFAULT 0,
    status ENUM('played','skipped','failed') NOT NULL DEFAULT 'played',
    created_at DATETIME NOT NULL,
    KEY idx_ad_logs_radio_date (radio_id, played_at),
    KEY idx_ad_logs_campaign (campaign_id, played_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
