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

USE lozypop_ads;

CREATE TABLE admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL
);

CREATE TABLE apps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    package_name VARCHAR(180) NOT NULL UNIQUE,
    api_key_hash CHAR(64) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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)
);

CREATE TABLE reward_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    session_token CHAR(64) NOT NULL UNIQUE,

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

    client_hash CHAR(64) NOT NULL,

    started_at DATETIME NULL,
    completed_at DATETIME NULL,

    required_seconds INT UNSIGNED NOT NULL,
    reward_amount INT UNSIGNED NOT NULL,

    used TINYINT(1) NOT NULL DEFAULT 0,

    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_reward_client
        (client_hash, created_at)
);

INSERT INTO apps (
    name,
    package_name,
    api_key_hash
)
VALUES (
    'Lozy Pop',
    'com.lozypop',
    SHA2('CHANGE_THIS_APP_SECRET', 256)
);