-- ============================================================
-- Sistema de Currículos e Vagas - Schema MySQL 8+
-- UTF-8, datas em UTC onde aplicável
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- users
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('candidate','recruiter','admin') NOT NULL,
  email_verified_at DATETIME NULL,
  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 uk_users_email (email),
  INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- companies
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS companies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  cnpj VARCHAR(18) NULL,
  website VARCHAR(255) NULL,
  logo_path VARCHAR(500) NULL,
  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),
  INDEX idx_companies_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- company_users (recrutadores vinculados à empresa)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS company_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('owner','recruiter','viewer') NOT NULL DEFAULT 'recruiter',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_company_user (company_id, user_id),
  CONSTRAINT fk_company_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_company_users_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- candidates (dados extras do candidato)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS candidates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  city VARCHAR(100) NULL,
  state CHAR(2) NULL,
  phone VARCHAR(20) NULL,
  linkedin_url VARCHAR(500) NULL,
  github_url VARCHAR(500) NULL,
  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 uk_candidates_user (user_id),
  CONSTRAINT fk_candidates_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- skills (normalizado para matching)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS skills (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  name_normalized VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_skills_normalized (name_normalized),
  INDEX idx_skills_name (name_normalized)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- resumes (currículo do candidato)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resumes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  candidate_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(150) NULL COMMENT 'Objetivo/cargo desejado',
  summary TEXT NULL,
  visibility ENUM('public','private') NOT NULL DEFAULT 'private',
  version INT UNSIGNED 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),
  INDEX idx_resumes_candidate (candidate_id),
  INDEX idx_resumes_visibility (visibility),
  CONSTRAINT fk_resumes_candidate FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- resume_experiences
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resume_experiences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resume_id BIGINT UNSIGNED NOT NULL,
  job_title VARCHAR(150) NOT NULL,
  company_name VARCHAR(200) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL COMMENT 'NULL = até o momento',
  description TEXT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_resume_exp_resume (resume_id),
  CONSTRAINT fk_resume_exp_resume FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- resume_education
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resume_education (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resume_id BIGINT UNSIGNED NOT NULL,
  course VARCHAR(200) NOT NULL,
  institution VARCHAR(200) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_resume_edu_resume (resume_id),
  CONSTRAINT fk_resume_edu_resume FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- resume_skills (habilidades + nível)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resume_skills (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resume_id BIGINT UNSIGNED NOT NULL,
  skill_id BIGINT UNSIGNED NOT NULL,
  level ENUM('basic','intermediate','advanced','expert') NULL DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_resume_skill (resume_id, skill_id),
  CONSTRAINT fk_resume_skills_resume FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE,
  CONSTRAINT fk_resume_skills_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- jobs (com período de divulgação)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NOT NULL,
  responsibilities TEXT NULL,
  location_city VARCHAR(100) NULL,
  location_state CHAR(2) NULL,
  work_model ENUM('remote','hybrid','onsite') NOT NULL DEFAULT 'onsite',
  job_type ENUM('internship','clt','pj','freelance') NOT NULL DEFAULT 'clt',
  seniority VARCHAR(50) NULL,
  area VARCHAR(100) NULL COMMENT 'Área profissional (ex: TI, Administrativo)',
  salary_min DECIMAL(12,2) NULL,
  salary_max DECIMAL(12,2) NULL,
  status ENUM('draft','published','paused','closed','archived') NOT NULL DEFAULT 'draft',
  publish_start_at DATETIME NOT NULL,
  publish_end_at DATETIME NOT NULL,
  published_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_jobs_public (status, publish_start_at, publish_end_at),
  INDEX idx_jobs_company (company_id, status),
  CONSTRAINT fk_jobs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Triggers: validar publish_end_at > publish_start_at
DELIMITER $$
DROP TRIGGER IF EXISTS trg_jobs_validate_dates_before_insert$$
CREATE TRIGGER trg_jobs_validate_dates_before_insert
BEFORE INSERT ON jobs
FOR EACH ROW
BEGIN
  IF NEW.publish_end_at <= NEW.publish_start_at THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'publish_end_at must be greater than publish_start_at';
  END IF;
END$$

DROP TRIGGER IF EXISTS trg_jobs_validate_dates_before_update$$
CREATE TRIGGER trg_jobs_validate_dates_before_update
BEFORE UPDATE ON jobs
FOR EACH ROW
BEGIN
  IF NEW.publish_end_at <= NEW.publish_start_at THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'publish_end_at must be greater than publish_start_at';
  END IF;
END$$
DELIMITER ;

-- ------------------------------------------------------------
-- job_requirements (skills obrigatórias/desejáveis + peso)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_requirements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  skill_id BIGINT UNSIGNED NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=obrigatória, 0=desejável',
  weight TINYINT UNSIGNED NULL COMMENT 'peso 0-100 naquele grupo',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_job_req_job (job_id),
  CONSTRAINT fk_job_req_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_req_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- job_questions (perguntas de triagem)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_questions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  question TEXT NOT NULL,
  type ENUM('yes_no','text') NOT NULL DEFAULT 'text',
  required TINYINT(1) NOT NULL DEFAULT 1,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_job_questions_job (job_id),
  CONSTRAINT fk_job_questions_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- applications (candidatura)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS applications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  candidate_id BIGINT UNSIGNED NOT NULL,
  resume_id BIGINT UNSIGNED NOT NULL,
  status ENUM('submitted','screening','interview','offer','approved','rejected') NOT NULL DEFAULT 'submitted',
  cover_letter TEXT NULL,
  ip_address VARCHAR(45) NULL,
  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 uk_application_job_candidate (job_id, candidate_id),
  INDEX idx_applications_job (job_id),
  INDEX idx_applications_candidate (candidate_id),
  INDEX idx_applications_status (status),
  CONSTRAINT fk_applications_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_applications_candidate FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  CONSTRAINT fk_applications_resume FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- application_answers (respostas às perguntas de triagem)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS application_answers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  answer_text TEXT NULL,
  answer_yes_no TINYINT(1) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_app_ans_app_question (application_id, question_id),
  CONSTRAINT fk_app_ans_application FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_ans_question FOREIGN KEY (question_id) REFERENCES job_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- application_scores (score automático 0-100 + breakdown)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS application_scores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_id BIGINT UNSIGNED NOT NULL,
  score_total TINYINT UNSIGNED NOT NULL COMMENT '0-100',
  breakdown_json JSON NULL COMMENT 'ex: {"skills":40,"experience":25,"education":15,"location":10}',
  calculated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_app_score_application (application_id),
  CONSTRAINT fk_app_score_application FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- application_reviews (nota manual, tags, comentário)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS application_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_id BIGINT UNSIGNED NOT NULL,
  reviewer_id BIGINT UNSIGNED NOT NULL,
  rating TINYINT UNSIGNED NULL COMMENT '0-5',
  tags VARCHAR(255) NULL COMMENT 'ex: bom,talvez,sem fit',
  comment TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_app_review_application (application_id),
  CONSTRAINT fk_app_review_application FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_review_reviewer FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- files (PDF currículo, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  owner_type VARCHAR(30) NOT NULL COMMENT 'candidate, company',
  owner_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(50) NOT NULL COMMENT 'resume_pdf, logo',
  path VARCHAR(500) NOT NULL,
  original_name VARCHAR(255) NULL,
  mime_type VARCHAR(100) NULL,
  size_bytes INT UNSIGNED NULL,
  file_hash VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_files_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Event: encerrar vagas expiradas (MySQL Event Scheduler)
-- Ativar com: SET GLOBAL event_scheduler = ON;
-- ============================================================
-- CREATE EVENT ev_close_expired_jobs
-- ON SCHEDULE EVERY 15 MINUTE
-- DO
--   UPDATE jobs
--   SET status = 'closed',
--       closed_at = UTC_TIMESTAMP()
--   WHERE status = 'published'
--     AND publish_end_at < UTC_TIMESTAMP();