CREATE DATABASE IF NOT EXISTS lozypop_ads
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE lozypop_ads;

CREATE TABLE ads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_id BIGINT UNSIGNED NOT NULL,
    type ENUM('banner','rewarded') NOT NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    asset_url VARCHAR(500) NOT NULL,
    target_url VARCHAR(1000) NOT NULL,

    width INT UNSIGNED NULL,
    height INT UNSIGNED NULL,

    reward INT UNSIGNED NOT NULL DEFAULT 0,
    watch_seconds INT UNSIGNED NOT NULL DEFAULT 10,

    priority INT UNSIGNED NOT NULL DEFAULT 1,
    weight INT UNSIGNED NOT NULL DEFAULT 1,

    active TINYINT(1) NOT NULL DEFAULT 1,

    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (app_id)
        REFERENCES apps(id)
        ON DELETE CASCADE,

    INDEX idx_ads_select
        (app_id, type, active, priority)
);

CREATE TABLE ad_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ad_id BIGINT UNSIGNED NOT NULL,
    app_id BIGINT UNSIGNED NOT NULL,

    event_type ENUM(
        'impression',
        'click',
        'start',
        'complete'
    ) NOT NULL,

    client_hash CHAR(64) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (ad_id)
        REFERENCES ads(id)
        ON DELETE CASCADE,

    FOREIGN KEY (app_id)
        REFERENCES apps(id)
        ON DELETE CASCADE,

    INDEX idx_events_app_event_time
        (app_id, event_type, created_at)
);