CREATE DATABASE IF NOT EXISTS link_rotator CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE link_rotator;
CREATE TABLE IF NOT EXISTS admins(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(100) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS links(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,url TEXT NOT NULL,
 weight INT UNSIGNED NOT NULL DEFAULT 1,clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,fail_count INT UNSIGNED NOT NULL DEFAULT 0,
 last_status INT NULL,last_checked_at DATETIME NULL,last_error TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS link_checks(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,link_id INT UNSIGNED NOT NULL,status_code INT NULL,
 response_ms INT NULL,ok TINYINT(1) NOT NULL DEFAULT 0,error_message VARCHAR(500) NULL,
 checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(link_id),INDEX(checked_at),
 CONSTRAINT fk_checks_link FOREIGN KEY(link_id) REFERENCES links(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value TEXT NOT NULL);
INSERT INTO settings(setting_key,setting_value) VALUES
('monitor_fail_threshold','3'),('monitor_timeout','10'),('monitor_interval','5')
ON DUPLICATE KEY UPDATE setting_key=setting_key;