CREATE TABLE IF NOT EXISTS urls (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(32) NOT NULL UNIQUE,
target TEXT NOT NULL,
title VARCHAR(255) NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_ip VARCHAR(45) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS clicks (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
url_id BIGINT UNSIGNED NOT NULL,
ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ip VARCHAR(45) NULL,
user_agent TEXT NULL,
referrer TEXT NULL,
utm_source VARCHAR(100) NULL,
utm_medium VARCHAR(100) NULL,
utm_campaign VARCHAR(100) NULL,
country_code CHAR(2) NULL,
lat DECIMAL(10, 8) NULL,
lon DECIMAL(11, 8) NULL,
INDEX (url_id),
CONSTRAINT fk_clicks_url
FOREIGN KEY (url_id) REFERENCES urls(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE INDEX idx_urls_code ON urls(code);
CREATE INDEX idx_clicks_ts ON clicks(ts);