CREATE TABLE admins (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) UNIQUE, password_hash VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE domains (id INT AUTO_INCREMENT PRIMARY KEY, hostname VARCHAR(255) UNIQUE NOT NULL, domain_role ENUM('short','creative','both') DEFAULT 'both', verification_token VARCHAR(64) NOT NULL, verified_at DATETIME NULL, status ENUM('pending','active','disabled') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE destination_pools (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE destinations (id INT AUTO_INCREMENT PRIMARY KEY, pool_id INT NOT NULL, label VARCHAR(150) DEFAULT '', url TEXT NOT NULL, weight INT DEFAULT 1, active TINYINT DEFAULT 1, FOREIGN KEY(pool_id) REFERENCES destination_pools(id) ON DELETE CASCADE);
CREATE TABLE creative_pools (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE creatives (id INT AUTO_INCREMENT PRIMARY KEY, pool_id INT NOT NULL, media_url TEXT NOT NULL, media_type ENUM('image','video') DEFAULT 'image', avatar_url TEXT NULL, display_name VARCHAR(120) DEFAULT 'Trending', username VARCHAR(120) DEFAULT '@trending', caption VARCHAR(255) DEFAULT 'Trending now', likes VARCHAR(30) DEFAULT '82.9K', comments VARCHAR(30) DEFAULT '1.5K', shares VARCHAR(30) DEFAULT '949', weight INT DEFAULT 1, active TINYINT DEFAULT 1, FOREIGN KEY(pool_id) REFERENCES creative_pools(id) ON DELETE CASCADE);
CREATE TABLE campaigns (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, short_domain_id INT NOT NULL, short_slug VARCHAR(100) NOT NULL, creative_domain_id INT NOT NULL, creative_slug VARCHAR(100) NOT NULL, creative_pool_id INT NOT NULL, destination_pool_id INT NOT NULL, desktop_fallback_url TEXT NOT NULL, redirect_delay INT DEFAULT 3, og_title VARCHAR(255), og_description VARCHAR(500), og_image TEXT, active TINYINT DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_short(short_domain_id,short_slug), UNIQUE KEY uq_creative(creative_domain_id,creative_slug), FOREIGN KEY(short_domain_id) REFERENCES domains(id), FOREIGN KEY(creative_domain_id) REFERENCES domains(id), FOREIGN KEY(creative_pool_id) REFERENCES creative_pools(id), FOREIGN KEY(destination_pool_id) REFERENCES destination_pools(id));
CREATE TABLE events (id BIGINT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NOT NULL, creative_id INT NULL, destination_id INT NULL, event_type ENUM('layer1','impression','tap','auto','desktop') NOT NULL, user_agent TEXT, referrer TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(campaign_id), INDEX(created_at));