
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) UNIQUE,
  password VARCHAR(255),
  role VARCHAR(20) DEFAULT 'editor',
  name VARCHAR(120),
  created_at DATETIME,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  k VARCHAR(100) UNIQUE,
  v TEXT
);

CREATE TABLE IF NOT EXISTS casinos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190),
  slug VARCHAR(190) UNIQUE,
  logo_url VARCHAR(255),
  rating DECIMAL(3,1) DEFAULT 0,
  bonus_title VARCHAR(255),
  bonus_detail TEXT,
  pros JSON NULL,
  cons JSON NULL,
  terms_url VARCHAR(255),
  website_url VARCHAR(255),
  affiliate_url VARCHAR(255),
  tags JSON NULL,
  created_at DATETIME,
  updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS referral_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(100) UNIQUE,
  user_id INT,
  casino_id INT NULL,
  created_at DATETIME,
  CONSTRAINT fk_ref_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ref_casino FOREIGN KEY (casino_id) REFERENCES casinos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clicks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referral_code_id INT,
  ip VARCHAR(64),
  user_agent TEXT,
  utm_source VARCHAR(120),
  utm_medium VARCHAR(120),
  utm_campaign VARCHAR(120),
  created_at DATETIME,
  CONSTRAINT fk_click_ref FOREIGN KEY (referral_code_id) REFERENCES referral_codes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kind ENUM('news','analysis') DEFAULT 'news',
  slug VARCHAR(190) UNIQUE,
  title VARCHAR(255),
  excerpt VARCHAR(255),
  content_md LONGTEXT,
  cover_url VARCHAR(255),
  tags JSON NULL,
  published TINYINT(1) DEFAULT 0,
  published_at DATETIME NULL,
  author_id INT NULL,
  created_at DATETIME,
  updated_at DATETIME,
  CONSTRAINT fk_post_user FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ext_id VARCHAR(190) UNIQUE NULL,
  sport VARCHAR(64),
  league VARCHAR(120) NULL,
  home_team VARCHAR(120),
  away_team VARCHAR(120),
  start_time DATETIME,
  status VARCHAR(32) DEFAULT 'scheduled',
  score_home INT NULL,
  score_away INT NULL,
  odds_json JSON NULL,
  created_at DATETIME,
  updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS queue_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('news','analysis','tips') NOT NULL,
  sport VARCHAR(64) NULL,
  league VARCHAR(120) NULL,
  title VARCHAR(255),
  excerpt VARCHAR(255) NULL,
  content_md LONGTEXT,
  source_url VARCHAR(255) NULL,
  probability DECIMAL(5,2) NULL,
  tags JSON NULL,
  created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

