CREATE TABLE IF NOT EXISTS ad_zones (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    position INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_active_pos (is_active, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_spots (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT '',
    ad_type ENUM('code','image','vast') NOT NULL DEFAULT 'code',
    ad_code TEXT DEFAULT NULL,
    image_url VARCHAR(500) DEFAULT NULL,
    click_url VARCHAR(500) DEFAULT NULL,
    vast_url VARCHAR(500) DEFAULT NULL,
    device ENUM('all','desktop','mobile') NOT NULL DEFAULT 'all',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    impressions INT UNSIGNED NOT NULL DEFAULT 0,
    clicks INT UNSIGNED NOT NULL DEFAULT 0,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    position INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_active (zone_id, is_active, position),
    FOREIGN KEY (zone_id) REFERENCES ad_zones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO ad_zones (slug, name, description, position) VALUES
('header_banner', 'Header Banner', 'Top of page, below navigation', 1),
('before_player', 'Before Player', 'Above the video player on watch page', 2),
('player_overlay', 'Player Overlay', 'Overlay inside the video player area', 3),
('after_player', 'After Player', 'Below the video player on watch page', 4),
('sidebar', 'Sidebar', 'Right sidebar on watch page', 5),
('listing_between', 'Between Videos', 'Inserted between video thumbnails in listings', 6),
('listing_bottom', 'Below Listings', 'Bottom of video listing pages', 7),
('footer_banner', 'Footer Banner', 'Above the site footer on all pages', 8),
('popup', 'Popup / Interstitial', 'Full-screen popup or interstitial ad', 9),
('mobile_sticky', 'Mobile Sticky', 'Sticky banner at bottom on mobile devices', 10)
ON DUPLICATE KEY UPDATE slug = slug;
