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

USE tytanng_ads;

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

CREATE TABLE ads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,

    image_url VARCHAR(1000) NOT NULL,

    target_url VARCHAR(1000) NOT NULL,

    ad_type ENUM('banner','interstitial')
        NOT NULL DEFAULT 'banner',

    is_active TINYINT(1)
        NOT NULL DEFAULT 1,

    priority INT
        NOT NULL DEFAULT 0,

    start_date DATETIME NULL,

    end_date DATETIME NULL,

    max_impressions_per_device INT UNSIGNED
        NOT NULL DEFAULT 0,

    cooldown_seconds INT UNSIGNED
        NOT NULL DEFAULT 0,

    impressions INT UNSIGNED
        NOT NULL DEFAULT 0,

    clicks INT UNSIGNED
        NOT NULL DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE ad_impressions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ad_id INT UNSIGNED NOT NULL,

    device_id VARCHAR(255) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX(ad_id),
    INDEX(device_id),
    INDEX(created_at),

    CONSTRAINT fk_impression_ad
        FOREIGN KEY (ad_id)
        REFERENCES ads(id)
        ON DELETE CASCADE
);

CREATE TABLE ad_clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ad_id INT UNSIGNED NOT NULL,

    device_id VARCHAR(255) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX(ad_id),
    INDEX(device_id),
    INDEX(created_at),

    CONSTRAINT fk_click_ad
        FOREIGN KEY (ad_id)
        REFERENCES ads(id)
        ON DELETE CASCADE
);