-- Module RH (optionnel) — MySQL additive
CREATE TABLE IF NOT EXISTS employees (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  branch_id VARCHAR(255) NULL,
  user_id VARCHAR(255) NULL,
  matricule VARCHAR(64) NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  cin VARCHAR(64) NULL,
  birth_date VARCHAR(32) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  marital_status VARCHAR(64) NULL,
  dependents INT DEFAULT 0,
  hire_date VARCHAR(32) NULL,
  exit_date VARCHAR(32) NULL,
  job_title VARCHAR(190) NULL,
  department VARCHAR(190) NULL,
  contract_type VARCHAR(32) DEFAULT 'CDI',
  base_salary DOUBLE DEFAULT 0,
  rib VARCHAR(64) NULL,
  bank_name VARCHAR(120) NULL,
  cnss_number VARCHAR(64) NULL,
  status VARCHAR(32) DEFAULT 'ACTIF',
  notes TEXT NULL,
  photo_path TEXT NULL,
  archived INT DEFAULT 0,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employment_contracts (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  contract_type VARCHAR(32) DEFAULT 'CDI',
  start_date VARCHAR(32) NULL,
  end_date VARCHAR(32) NULL,
  trial_end_date VARCHAR(32) NULL,
  job_title VARCHAR(190) NULL,
  base_salary DOUBLE DEFAULT 0,
  work_schedule TEXT NULL,
  document_path TEXT NULL,
  status VARCHAR(32) DEFAULT 'ACTIF',
  notes TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_emp_contract (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leave_types (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  paid INT DEFAULT 1,
  active INT DEFAULT 1,
  UNIQUE KEY uq_leave_type (company_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leave_requests (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  leave_type_id VARCHAR(255) NULL,
  start_date VARCHAR(32) NOT NULL,
  end_date VARCHAR(32) NOT NULL,
  days DOUBLE DEFAULT 0,
  reason TEXT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  decided_by VARCHAR(255) NULL,
  decided_at VARCHAR(64) NULL,
  decision_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance_records (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  work_date VARCHAR(32) NOT NULL,
  status VARCHAR(32) DEFAULT 'PRESENT',
  hours DOUBLE DEFAULT 8,
  overtime_hours DOUBLE DEFAULT 0,
  note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_attendance (company_id, employee_id, work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_employees_co ON employees(company_id);
CREATE INDEX idx_emp_contracts_co ON employment_contracts(company_id);
CREATE INDEX idx_leave_req_co ON leave_requests(company_id);