-- GulfLink MIS — Database Schema
-- Run this file after creating the database, OR use setup.php which runs it automatically.

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS role_categories (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(80)  NOT NULL,
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS candidate_statuses (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label      VARCHAR(60)  NOT NULL,
  color_hex  VARCHAR(7)   DEFAULT '#6B7280',
  sort_order TINYINT      DEFAULT 0,
  is_active  TINYINT(1)   DEFAULT 1,
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','accounts','operations','company','candidate') NOT NULL,
  is_active     TINYINT(1)   DEFAULT 1,
  theme         ENUM('light','dark') DEFAULT 'light',
  last_login    TIMESTAMP    NULL,
  created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS companies (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NULL,
  name            VARCHAR(150) NOT NULL,
  country         VARCHAR(80)  NOT NULL,
  city            VARCHAR(80)  NULL,
  contact_person  VARCHAR(120) NULL,
  contact_email   VARCHAR(150) NULL,
  contact_phone   VARCHAR(30)  NULL,
  address         TEXT         NULL,
  notes           TEXT         NULL,
  is_active       TINYINT(1)   DEFAULT 1,
  created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS vacancies (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id   INT UNSIGNED NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  role_title   VARCHAR(120) NOT NULL,
  total_seats  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  default_fee  DECIMAL(10,2) DEFAULT 0.00,
  deadline     DATE         NULL,
  requirements TEXT         NULL,
  status       ENUM('active','urgent','closed','filled') DEFAULT 'active',
  created_by   INT UNSIGNED NULL,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id)  REFERENCES companies(id)       ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES role_categories(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by)  REFERENCES users(id)           ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS candidates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NULL,
  full_name    VARCHAR(120) NOT NULL,
  phone        VARCHAR(20)  NOT NULL,
  email        VARCHAR(150) NULL,
  city         VARCHAR(80)  NULL,
  state        VARCHAR(80)  NULL,
  category_id  INT UNSIGNED NULL,
  trade_skill  VARCHAR(100) NULL,
  dob          DATE         NULL,
  id_proof_no  VARCHAR(60)  NULL,
  resume_path  VARCHAR(255) NULL,
  reg_source   ENUM('walk_in','advertisement','referral','online_portal','agent') DEFAULT 'walk_in',
  notes        TEXT         NULL,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)     REFERENCES users(id)           ON DELETE SET NULL,
  FOREIGN KEY (category_id) REFERENCES role_categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS candidate_vacancies (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  candidate_id  INT UNSIGNED NOT NULL,
  vacancy_id    INT UNSIGNED NOT NULL,
  status_id     INT UNSIGNED NOT NULL,
  agreed_fee    DECIMAL(10,2) DEFAULT 0.00,
  assigned_date DATE          NULL,
  assigned_by   INT UNSIGNED  NULL,
  notes         TEXT          NULL,
  created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cv (candidate_id, vacancy_id),
  FOREIGN KEY (candidate_id) REFERENCES candidates(id)         ON DELETE CASCADE,
  FOREIGN KEY (vacancy_id)   REFERENCES vacancies(id)          ON DELETE CASCADE,
  FOREIGN KEY (status_id)    REFERENCES candidate_statuses(id) ON DELETE RESTRICT,
  FOREIGN KEY (assigned_by)  REFERENCES users(id)              ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS campaigns (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(150) NOT NULL,
  medium      VARCHAR(80)  NOT NULL,
  city        VARCHAR(80)  NULL,
  run_date    DATE         NULL,
  budget      DECIMAL(10,2) DEFAULT 0.00,
  notes       TEXT          NULL,
  created_by  INT UNSIGNED  NULL,
  created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS interviews (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vacancy_id     INT UNSIGNED NOT NULL,
  campaign_id    INT UNSIGNED NULL,
  interview_date DATE         NOT NULL,
  city           VARCHAR(80)  NOT NULL,
  venue          VARCHAR(200) NULL,
  notes          TEXT         NULL,
  status         ENUM('upcoming','filling','confirmed','completed','cancelled') DEFAULT 'upcoming',
  created_by     INT UNSIGNED NULL,
  created_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (vacancy_id)  REFERENCES vacancies(id)  ON DELETE CASCADE,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id)  ON DELETE SET NULL,
  FOREIGN KEY (created_by)  REFERENCES users(id)      ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS interview_candidates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  interview_id INT UNSIGNED NOT NULL,
  candidate_id INT UNSIGNED NOT NULL,
  outcome      ENUM('pending','passed','failed','absent','deferred') DEFAULT 'pending',
  notes        TEXT         NULL,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ic (interview_id, candidate_id),
  FOREIGN KEY (interview_id) REFERENCES interviews(id) ON DELETE CASCADE,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS checklist_templates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id  INT UNSIGNED NOT NULL,
  item_name    VARCHAR(120) NOT NULL,
  is_mandatory TINYINT(1)   DEFAULT 1,
  sort_order   TINYINT      DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES role_categories(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS candidate_documents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  candidate_id INT UNSIGNED NOT NULL,
  vacancy_id   INT UNSIGNED NULL,
  item_name    VARCHAR(120) NOT NULL,
  status       ENUM('pending','submitted','verified','rejected','waived') DEFAULT 'pending',
  file_path    VARCHAR(255) NULL,
  notes        TEXT         NULL,
  updated_by   INT UNSIGNED NULL,
  updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
  FOREIGN KEY (vacancy_id)   REFERENCES vacancies(id)  ON DELETE SET NULL,
  FOREIGN KEY (updated_by)   REFERENCES users(id)      ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS payments (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  candidate_vacancy_id INT UNSIGNED  NOT NULL,
  receipt_no           VARCHAR(30)   NOT NULL UNIQUE,
  amount               DECIMAL(10,2) NOT NULL,
  payment_date         DATE          NOT NULL,
  mode                 ENUM('cash','bank_transfer','cheque','upi','dd','online') DEFAULT 'cash',
  installment_no       TINYINT       DEFAULT 1,
  cheque_ref           VARCHAR(60)   NULL,
  received_by          INT UNSIGNED  NOT NULL,
  notes                TEXT          NULL,
  created_at           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (candidate_vacancy_id) REFERENCES candidate_vacancies(id) ON DELETE RESTRICT,
  FOREIGN KEY (received_by)          REFERENCES users(id)               ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS transactions (
  id          INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  type        ENUM('income','expense') NOT NULL,
  amount      DECIMAL(12,2) NOT NULL,
  txn_date    DATE          NOT NULL,
  description VARCHAR(255)  NOT NULL,
  category    VARCHAR(80)   NULL,
  ref_type    VARCHAR(40)   NULL,
  ref_id      INT UNSIGNED  NULL,
  entered_by  INT UNSIGNED  NULL,
  notes       TEXT          NULL,
  created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_ref  (ref_type, ref_id),
  INDEX idx_date (txn_date),
  INDEX idx_type (type)
);

SET FOREIGN_KEY_CHECKS = 1;