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


-- =========================================
-- 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;

-- =========================================
-- 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 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_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;

-- =========================================
-- 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_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 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;

