CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('owner','admin','analyst','viewer') NOT NULL DEFAULT 'admin',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  channel VARCHAR(80) NOT NULL,
  description TEXT NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  alias VARCHAR(90) NOT NULL UNIQUE,
  destination_url TEXT NOT NULL,
  category_id INT NULL,
  campaign_id INT NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  notes TEXT NULL,
  total_clicks INT NOT NULL DEFAULT 0,
  last_clicked_at DATETIME NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_links_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_links_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
  CONSTRAINT fk_links_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE click_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  link_id INT NOT NULL,
  alias VARCHAR(90) NOT NULL,
  visitor_hash CHAR(64) NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  referrer TEXT NULL,
  campaign VARCHAR(160) NULL,
  country VARCHAR(80) NULL,
  region VARCHAR(120) NULL,
  city VARCHAR(120) NULL,
  device_type VARCHAR(40) NULL,
  browser VARCHAR(80) NULL,
  operating_system VARCHAR(80) NULL,
  is_qr TINYINT(1) NOT NULL DEFAULT 0,
  source VARCHAR(40) NOT NULL DEFAULT 'web',
  clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_click_link_time (link_id, clicked_at),
  INDEX idx_click_alias_time (alias, clicked_at),
  CONSTRAINT fk_click_link FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE download_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  link_id INT NULL,
  file_name VARCHAR(255) NOT NULL,
  visitor_hash CHAR(64) NULL,
  referrer TEXT NULL,
  downloaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(80) NOT NULL,
  entity_id BIGINT NULL,
  message TEXT NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (name,email,password_hash,role,status) VALUES
('TradeCPO Founder Office','admin@tradecpo.com','$2y$12$YJtocRnX1pKMX60oUauQm.tHUaO1nkqBdUge/9rCJg/NxWuCFTAtC','owner','active');

INSERT INTO categories (name,description) VALUES
('ALPHA Intelligence','TradeCPO ALPHA publication links'),
('Intelligence Library','Case studies, library pages and publications'),
('Investment','Investor and capital planning materials'),
('Website','Core TradeCPO website links');

INSERT INTO campaigns (name,channel,description) VALUES
('ALPHA Institutional Outreach','ALPHA','Official ALPHA intelligence distribution'),
('Investor Readiness Campaign','Investor','Founder Office investment and capital planning outreach'),
('TradeCPO Intelligence Library Launch','Website','Intelligence Library publication distribution');

INSERT INTO links (title,alias,destination_url,category_id,campaign_id,status,notes) VALUES
('TradeCPO Intelligence Library','library','https://tradecpo.com/intelligence-library/',2,3,'active','Official Intelligence Library'),
('TradeCPO Investment Book','investment','https://tradecpo.com/',3,2,'active','Investor materials placeholder'),
('ALPHA Intelligence Example','alpha28','https://tradecpo.com/alpha-intelligence-xxviii',1,1,'active','Example ALPHA link');