CREATE TABLE IF NOT EXISTS short_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(64) NOT NULL UNIQUE,
    target_url TEXT NOT NULL,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    max_clicks INT UNSIGNED NOT NULL DEFAULT 1,
    click_count INT UNSIGNED NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_status_expires (status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS link_click_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_link_id BIGINT UNSIGNED NULL,
    token VARCHAR(64) NOT NULL,
    clicked_at DATETIME NOT NULL,
    ip_address VARCHAR(64) NOT NULL,
    user_agent VARCHAR(512) NOT NULL,
    referer VARCHAR(512) NOT NULL,
    outcome VARCHAR(64) NOT NULL,
    INDEX idx_token_clicked (token, clicked_at),
    INDEX idx_short_link_id (short_link_id),
    CONSTRAINT fk_click_short_link
        FOREIGN KEY (short_link_id) REFERENCES short_links(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;