-- MySQL 8.x
CREATE DATABASE IF NOT EXISTS shortlink CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shortlink;

CREATE TABLE domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  host VARCHAR(255) NOT NULL UNIQUE,            -- contoh: short.link, sl.xyz
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE links (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  alias VARCHAR(64) NOT NULL UNIQUE,            -- custom short path
  target TEXT NOT NULL,
  domain_id INT NULL,
  campaign_id INT NULL,
  is_active TINYINT(1) DEFAULT 1,
  expire_at DATETIME NULL,
  max_clicks INT NULL,
  total_clicks INT DEFAULT 0,
  bot_filter TINYINT(1) DEFAULT 1,
  cloaking TINYINT(1) DEFAULT 0,                -- (opsional utk masa depan)
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
);

CREATE TABLE clicks (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  link_id BIGINT NOT NULL,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ip VARBINARY(16) NULL,                        -- simpan packed
  ua TEXT NULL,
  referrer TEXT NULL,
  country VARCHAR(64) NULL,
  device VARCHAR(16) NULL,                      -- mobile/desktop/tablet/bot
  is_bot TINYINT(1) DEFAULT 0,
  FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE,
  INDEX (link_id, ts)
);

CREATE TABLE settings (
  skey VARCHAR(64) PRIMARY KEY,
  svalue TEXT NOT NULL
);

-- Backup URL per link
CREATE TABLE IF NOT EXISTS link_backups (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  link_id BIGINT NOT NULL,
  backup_url TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE,
  INDEX(link_id)
);

INSERT INTO settings (skey, svalue) VALUES
('brand_name','Shortlink'),
('accent','teal'),           -- teal | gold
('dark_mode','1');

