-- =========================================
-- VivaBoard Hire — MySQL Schema
-- Converted from Supabase Postgres migrations
-- Target: MariaDB 10.11 / MySQL 8.0
-- =========================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================
-- USERS & AUTH (replaces Supabase auth.users)
-- =========================================
CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  avatar_url VARCHAR(500),
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE refresh_tokens (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  token_hash VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_rt_user (user_id),
  INDEX idx_rt_hash (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- ORGANIZATIONS
-- =========================================
CREATE TABLE organizations (
  id CHAR(36) PRIMARY KEY,
  name TEXT NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  industry VARCHAR(100),
  size VARCHAR(50),
  website TEXT,
  logo_url TEXT,
  is_demo TINYINT(1) NOT NULL DEFAULT 0,
  onboarding_completed TINYINT(1) NOT NULL DEFAULT 0,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_org_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE organization_members (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  role ENUM('owner','admin','recruiter','hiring_manager','reviewer','interviewer','viewer') NOT NULL DEFAULT 'recruiter',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_org_user (organization_id, user_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_om_user (user_id),
  INDEX idx_om_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE organization_invitations (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  email VARCHAR(255) NOT NULL,
  role ENUM('owner','admin','recruiter','hiring_manager','reviewer','interviewer','viewer') NOT NULL DEFAULT 'recruiter',
  token VARCHAR(255) NOT NULL UNIQUE,
  invited_by CHAR(36),
  accepted_at TIMESTAMP NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_oi_org (organization_id),
  INDEX idx_oi_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- JOBS
-- =========================================
CREATE TABLE jobs (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  title TEXT NOT NULL,
  department VARCHAR(255),
  description TEXT,
  employment_type ENUM('full_time','part_time','contract','internship','temporary'),
  workplace_type ENUM('onsite','remote','hybrid'),
  location VARCHAR(500),
  salary_min DECIMAL(12,2),
  salary_max DECIMAL(12,2),
  currency VARCHAR(10) DEFAULT 'USD',
  openings INT DEFAULT 1,
  deadline DATE,
  status ENUM('draft','published','paused','closed','archived') NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP NULL,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_jobs_slug (organization_id, slug),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_jobs_org_status (organization_id, status),
  INDEX idx_jobs_slug_lookup (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_requirements (
  id CHAR(36) PRIMARY KEY,
  job_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  kind ENUM('mandatory','scored','preferred') NOT NULL,
  label TEXT NOT NULL,
  description TEXT,
  weight INT NOT NULL DEFAULT 1,
  knockout TINYINT(1) NOT NULL DEFAULT 0,
  keywords JSON NOT NULL DEFAULT ('[]'),
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_jr_job (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_competencies (
  id CHAR(36) PRIMARY KEY,
  job_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  weight INT NOT NULL DEFAULT 1,
  interview_focus TEXT,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_jc_job (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE application_form_fields (
  id CHAR(36) PRIMARY KEY,
  job_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  field_key VARCHAR(100) NOT NULL,
  label TEXT NOT NULL,
  helper_text TEXT,
  field_type ENUM('short_text','long_text','email','phone','select','multiselect','checkbox','file','cv','url','consent') NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 0,
  options JSON,
  sort_order INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_aff_key (job_id, field_key),
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- CANDIDATES & APPLICATIONS
-- =========================================
CREATE TABLE candidates (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  full_name TEXT NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50),
  location VARCHAR(500),
  headline TEXT,
  linkedin_url TEXT,
  github_url TEXT,
  portfolio_url TEXT,
  tags JSON NOT NULL DEFAULT ('[]'),
  is_demo TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cand_email (organization_id, email),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_cand_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE applications (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  job_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  stage ENUM('applied','ai_review','recruiter_review','interview_invited','interview_completed','work_sample','final_review','offer','hired','rejected','withdrawn') NOT NULL DEFAULT 'applied',
  assigned_to CHAR(36),
  status_token VARCHAR(64) NOT NULL UNIQUE,
  idempotency_key VARCHAR(255),
  source VARCHAR(50) DEFAULT 'direct',
  consent_ai TINYINT(1) NOT NULL DEFAULT 0,
  consent_privacy TINYINT(1) NOT NULL DEFAULT 0,
  is_demo TINYINT(1) NOT NULL DEFAULT 0,
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_app_job_cand (job_id, candidate_id),
  UNIQUE KEY uq_app_idem_key (job_id, idempotency_key),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_app_org_stage (organization_id, stage),
  INDEX idx_app_job (job_id),
  INDEX idx_app_token (status_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE application_answers (
  id CHAR(36) PRIMARY KEY,
  application_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  field_key VARCHAR(100) NOT NULL,
  value_text TEXT,
  value_json JSON,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_aa_app (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE application_notes (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  application_id CHAR(36) NOT NULL,
  author_id CHAR(36) NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_an_app (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE candidate_files (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  kind VARCHAR(50) NOT NULL,
  storage_path TEXT NOT NULL,
  file_name VARCHAR(500) NOT NULL,
  mime_type VARCHAR(255),
  size_bytes BIGINT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  INDEX idx_cf_cand (candidate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cv_documents (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  file_id CHAR(36),
  status ENUM('uploaded','queued','processing','parsed','needs_review','failed','provider_required') NOT NULL DEFAULT 'uploaded',
  parsed JSON,
  parsed_summary TEXT,
  skills JSON NOT NULL DEFAULT ('[]'),
  years_experience DECIMAL(4,1),
  processing_note TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  FOREIGN KEY (file_id) REFERENCES candidate_files(id) ON DELETE SET NULL,
  INDEX idx_cv_cand (candidate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- SCREENING
-- =========================================
CREATE TABLE screening_runs (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  application_id CHAR(36) NOT NULL,
  engine VARCHAR(100) NOT NULL DEFAULT 'rule_based_v1',
  recommendation ENUM('priority_review','recommended','potential_fit','insufficient_information','does_not_meet') NOT NULL,
  score INT NOT NULL DEFAULT 0,
  max_score INT NOT NULL DEFAULT 0,
  summary TEXT,
  ran_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ran_by CHAR(36),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (ran_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_sr_app (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE screening_requirement_results (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  run_id CHAR(36) NOT NULL,
  requirement_id CHAR(36) NOT NULL,
  met TINYINT(1) NOT NULL,
  confidence DECIMAL(4,3) NOT NULL DEFAULT 0,
  evidence TEXT,
  score INT NOT NULL DEFAULT 0,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (run_id) REFERENCES screening_runs(id) ON DELETE CASCADE,
  FOREIGN KEY (requirement_id) REFERENCES job_requirements(id) ON DELETE CASCADE,
  INDEX idx_srr_run (run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- NOTES, HISTORY, NOTIFICATIONS, AUDIT
-- =========================================
CREATE TABLE candidate_notes (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  author_id CHAR(36),
  body TEXT NOT NULL,
  pinned TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE candidate_stage_history (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  application_id CHAR(36) NOT NULL,
  from_stage VARCHAR(50),
  to_stage VARCHAR(50) NOT NULL,
  actor_id CHAR(36),
  reason TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  user_id CHAR(36),
  kind ENUM('new_application','screening_completed','candidate_needs_review','stage_change','team_invite','system') NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  link TEXT,
  read_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user (user_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  actor_id CHAR(36),
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id CHAR(36),
  metadata JSON,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_org_time (organization_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- DEMO REQUESTS
-- =========================================
CREATE TABLE demo_requests (
  id CHAR(36) PRIMARY KEY,
  full_name TEXT NOT NULL,
  work_email VARCHAR(255) NOT NULL,
  company VARCHAR(255) NOT NULL,
  company_size VARCHAR(50),
  country VARCHAR(100),
  message TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- INTERVIEW TEMPLATES
-- =========================================
CREATE TABLE interview_templates (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  name TEXT NOT NULL,
  duration_min INT DEFAULT 30,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_template_versions (
  id CHAR(36) PRIMARY KEY,
  template_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  version INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP NULL,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_itv (template_id, version),
  FOREIGN KEY (template_id) REFERENCES interview_templates(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_template_questions (
  id CHAR(36) PRIMARY KEY,
  version_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  position INT NOT NULL DEFAULT 0,
  prompt TEXT NOT NULL,
  competency VARCHAR(255),
  expected_signal TEXT,
  time_limit_seconds INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (version_id) REFERENCES interview_template_versions(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- INTERVIEW CAMPAIGNS
-- =========================================
CREATE TABLE interview_campaigns (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  job_id CHAR(36),
  template_version_id CHAR(36),
  name TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  starts_at TIMESTAMP NULL,
  ends_at TIMESTAMP NULL,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL,
  FOREIGN KEY (template_version_id) REFERENCES interview_template_versions(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_campaign_candidates (
  id CHAR(36) PRIMARY KEY,
  campaign_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  invitation_token_hash VARCHAR(255) UNIQUE,
  status VARCHAR(20) NOT NULL DEFAULT 'invited',
  invited_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  submitted_at TIMESTAMP NULL,
  UNIQUE KEY uq_icc (campaign_id, candidate_id),
  FOREIGN KEY (campaign_id) REFERENCES interview_campaigns(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- INTERVIEW RUNTIME
-- =========================================
CREATE TABLE interview_invitations (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  application_id CHAR(36) NOT NULL,
  candidate_id CHAR(36),
  token_hash VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMP NOT NULL,
  opens_at TIMESTAMP NULL,
  used_at TIMESTAMP NULL,
  revoked_at TIMESTAMP NULL,
  attempt_limit INT NOT NULL DEFAULT 1,
  attempts_used INT NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'invited',
  verification_method VARCHAR(20) NOT NULL DEFAULT 'none',
  verification_code_hash VARCHAR(255),
  verification_expires_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE SET NULL,
  INDEX idx_ii_token (token_hash),
  INDEX idx_ii_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_sessions (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  invitation_id CHAR(36) NOT NULL,
  candidate_id CHAR(36),
  job_id CHAR(36),
  attempt_no INT NOT NULL DEFAULT 1,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  started_at TIMESTAMP NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (invitation_id) REFERENCES interview_invitations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE SET NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL,
  INDEX idx_is_invitation (invitation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_session_state (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL UNIQUE,
  state VARCHAR(50) NOT NULL DEFAULT 'created',
  current_question_id CHAR(36),
  fullscreen_exit_count INT NOT NULL DEFAULT 0,
  tab_hidden_count INT NOT NULL DEFAULT 0,
  last_heartbeat_at TIMESTAMP NULL,
  client_snapshot JSON NOT NULL DEFAULT ('{}'),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_questions (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  order_index INT NOT NULL,
  kind VARCHAR(50) NOT NULL,
  competency VARCHAR(255),
  text_en TEXT NOT NULL,
  text_bn TEXT,
  time_limit_sec INT NOT NULL DEFAULT 180,
  generated_by_provider VARCHAR(100),
  prompt_version VARCHAR(50),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_iq (session_id, order_index),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_answers (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  question_id CHAR(36) NOT NULL,
  started_at TIMESTAMP NULL,
  ended_at TIMESTAMP NULL,
  ended_early TINYINT(1) NOT NULL DEFAULT 0,
  media_object_key TEXT,
  transcript_text TEXT,
  transcript_confidence DECIMAL(4,3),
  provider_run_id CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ia (session_id, question_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES interview_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_consents (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  consent_version VARCHAR(50) NOT NULL,
  accepted_sections JSON NOT NULL DEFAULT ('[]'),
  ip_address VARCHAR(50),
  user_agent TEXT,
  accepted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_device_checks (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  browser JSON NOT NULL DEFAULT ('{}'),
  camera JSON NOT NULL DEFAULT ('{}'),
  microphone JSON NOT NULL DEFAULT ('{}'),
  network JSON NOT NULL DEFAULT ('{}'),
  environment JSON NOT NULL DEFAULT ('{}'),
  overall_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_identity_checks (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  snapshot_object_key TEXT,
  candidate_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  provider VARCHAR(100),
  provider_result JSON,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_integrity_events (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  question_id CHAR(36),
  event_type VARCHAR(50) NOT NULL,
  severity VARCHAR(20) NOT NULL DEFAULT 'info',
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at TIMESTAMP NULL,
  duration_ms INT,
  confidence DECIMAL(4,3),
  client_meta JSON NOT NULL DEFAULT ('{}'),
  candidate_explanation TEXT,
  reviewer_status VARCHAR(20) NOT NULL DEFAULT 'new',
  reviewer_note TEXT,
  reviewer_id CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES interview_questions(id) ON DELETE SET NULL,
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_iie_session (session_id, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_candidate_explanations (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  event_id CHAR(36),
  body TEXT NOT NULL,
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES interview_integrity_events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interview_retry_requests (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  session_id CHAR(36) NOT NULL,
  reason TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  reviewer_id CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- SHORTLISTS
-- =========================================
CREATE TABLE shortlists (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  job_id CHAR(36),
  name TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  approved_at TIMESTAMP NULL,
  approved_by CHAR(36),
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shortlist_candidates (
  id CHAR(36) PRIMARY KEY,
  shortlist_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  position INT NOT NULL DEFAULT 0,
  note TEXT,
  added_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sc (shortlist_id, candidate_id),
  FOREIGN KEY (shortlist_id) REFERENCES shortlists(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shortlist_approvals (
  id CHAR(36) PRIMARY KEY,
  shortlist_id CHAR(36) NOT NULL,
  organization_id CHAR(36) NOT NULL,
  approver_id CHAR(36) NOT NULL,
  decision VARCHAR(20) NOT NULL,
  comment TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shortlist_id) REFERENCES shortlists(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (approver_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- WORK SAMPLES
-- =========================================
CREATE TABLE work_sample_templates (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  instructions TEXT,
  time_limit_minutes INT,
  scoring_rubric JSON NOT NULL DEFAULT ('[]'),
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE work_sample_assignments (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  template_id CHAR(36) NOT NULL,
  candidate_id CHAR(36) NOT NULL,
  application_id CHAR(36),
  status VARCHAR(20) NOT NULL DEFAULT 'assigned',
  token_hash VARCHAR(255) UNIQUE,
  due_at TIMESTAMP NULL,
  submitted_at TIMESTAMP NULL,
  score DECIMAL(5,2),
  assigned_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (template_id) REFERENCES work_sample_templates(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE work_sample_submissions (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  assignment_id CHAR(36) NOT NULL,
  content TEXT,
  file_paths JSON NOT NULL DEFAULT ('[]'),
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (assignment_id) REFERENCES work_sample_assignments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE work_sample_reviews (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  submission_id CHAR(36) NOT NULL,
  reviewer_id CHAR(36),
  score DECIMAL(5,2),
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (submission_id) REFERENCES work_sample_submissions(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- AUTOMATIONS, INTEGRATIONS, BILLING
-- =========================================
CREATE TABLE automations (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  name TEXT NOT NULL,
  trigger_event VARCHAR(100) NOT NULL,
  conditions JSON NOT NULL DEFAULT ('[]'),
  actions JSON NOT NULL DEFAULT ('[]'),
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE integrations (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  provider VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'disconnected',
  config JSON NOT NULL DEFAULT ('{}'),
  connected_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_int (organization_id, provider),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webhooks (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  url TEXT NOT NULL,
  events JSON NOT NULL DEFAULT ('[]'),
  secret VARCHAR(255) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE billing_plans (
  id CHAR(36) PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  monthly_price_cents INT NOT NULL DEFAULT 0,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  seat_limit INT,
  job_limit INT,
  features JSON NOT NULL DEFAULT ('[]'),
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO billing_plans (id, code, name, monthly_price_cents, currency, seat_limit, job_limit, features) VALUES
  (UUID(), 'starter','Starter',0,'USD',3,3,'["Up to 3 seats","Up to 3 active jobs","Community support"]'),
  (UUID(), 'growth','Growth',29900,'USD',15,25,'["Up to 15 seats","Up to 25 active jobs","AI screening","Priority support"]'),
  (UUID(), 'enterprise','Enterprise',99900,'USD',NULL,NULL,'["Unlimited seats","Unlimited jobs","SSO / SAML","Dedicated CSM","Custom SLAs"]');

CREATE TABLE organization_subscriptions (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL UNIQUE,
  plan_id CHAR(36) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  current_period_start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  current_period_end TIMESTAMP NULL,
  provider VARCHAR(50),
  provider_ref VARCHAR(255),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES billing_plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- COMPLIANCE & SUPPORT
-- =========================================
CREATE TABLE compliance_requests (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  candidate_id CHAR(36),
  subject_email VARCHAR(255) NOT NULL,
  request_type VARCHAR(20) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  notes TEXT,
  requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  handled_by CHAR(36),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE SET NULL,
  FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_tickets (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  subject TEXT NOT NULL,
  category VARCHAR(20) NOT NULL DEFAULT 'question',
  priority VARCHAR(20) NOT NULL DEFAULT 'normal',
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  created_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_messages (
  id CHAR(36) PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  ticket_id CHAR(36) NOT NULL,
  author_id CHAR(36),
  body TEXT NOT NULL,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;