-- CTV Scale (revivedemo) initial schema
-- Run this in phpMyAdmin (revivedemo) or via mysql CLI.

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','advertiser') NOT NULL DEFAULT 'advertiser',
  advertiser_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username),
  KEY idx_users_role (role),
  KEY idx_users_advertiser_id (advertiser_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS advertisers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  category VARCHAR(100) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_advertisers_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed data (safe-ish): insert only if missing.
INSERT INTO advertisers (name, category)
SELECT 'Demo Advertiser', 'OTT'
WHERE NOT EXISTS (SELECT 1 FROM advertisers WHERE name = 'Demo Advertiser');

-- Admin user: admin / admin123 (CHANGE IN PROD)
INSERT INTO users (username, password_hash, role, advertiser_id, is_active)
SELECT 'admin', '$2y$10$oSJxkzFmMAeVAwpHPQShWeHe.qpBsuCy/C8JgA7Uvksg/lJZKX10a', 'admin', NULL, 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin');

-- Advertiser user: advertiser / advertiser123
INSERT INTO users (username, password_hash, role, advertiser_id, is_active)
SELECT 'advertiser', '$2y$10$qvFEXniENvJYZPOsgzye8.etek90O2ooeZ1xqvMxtzhvEAvQLrUgK', 'advertiser',
       (SELECT id FROM advertisers WHERE name='Demo Advertiser' LIMIT 1),
       1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'advertiser');

