SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_otp_codes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_user_id BIGINT UNSIGNED NOT NULL,
  code_hash CHAR(64) NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_otp_admin_created (admin_user_id, created_at),
  CONSTRAINT fk_otp_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS applications (
  id CHAR(36) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  city VARCHAR(100) NOT NULL,
  experience_years VARCHAR(40) NOT NULL,
  experience_area VARCHAR(80) NOT NULL,
  availability VARCHAR(100) NOT NULL,
  motivation TEXT NOT NULL,
  source VARCHAR(100) NOT NULL DEFAULT 'direct',
  session_id VARCHAR(128) NOT NULL DEFAULT '',
  ref VARCHAR(100) NOT NULL DEFAULT '',
  utm_source VARCHAR(100) NOT NULL DEFAULT '',
  utm_medium VARCHAR(100) NOT NULL DEFAULT '',
  utm_campaign VARCHAR(100) NOT NULL DEFAULT '',
  utm_content VARCHAR(100) NOT NULL DEFAULT '',
  status ENUM('new','reviewing','interview','rejected','hired') NOT NULL DEFAULT 'new',
  priority ENUM('high','normal','low') NOT NULL DEFAULT 'normal',
  admin_notes TEXT NOT NULL,
  cv_path VARCHAR(255) NOT NULL,
  cv_filename VARCHAR(190) NOT NULL,
  cv_content_type VARCHAR(120) NOT NULL,
  malware_scan_status ENUM('clean','unavailable','failed') NOT NULL DEFAULT 'clean',
  consent_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_applications_status_created (status, created_at),
  KEY idx_applications_priority (priority),
  KEY idx_applications_source (source),
  KEY idx_applications_phone_created (phone, created_at),
  KEY idx_applications_session_created (session_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_views (
  id CHAR(36) NOT NULL,
  event_id CHAR(36) NOT NULL,
  session_id VARCHAR(128) NOT NULL,
  ref VARCHAR(100) NOT NULL DEFAULT '',
  utm_source VARCHAR(100) NOT NULL DEFAULT '',
  utm_medium VARCHAR(100) NOT NULL DEFAULT '',
  utm_campaign VARCHAR(100) NOT NULL DEFAULT '',
  utm_content VARCHAR(100) NOT NULL DEFAULT '',
  landing_path VARCHAR(190) NOT NULL DEFAULT '/',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_page_views_event_id (event_id),
  KEY idx_page_views_created_at (created_at),
  KEY idx_page_views_session_id (session_id),
  KEY idx_page_views_ref (ref),
  KEY idx_page_views_utm_campaign (utm_campaign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS security_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action VARCHAR(60) NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_security_action_fingerprint_created (action, fingerprint, created_at),
  KEY idx_security_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;