-- SecurityERP v2 Phase 6: HR, Recruitment & Compliance
CREATE TABLE IF NOT EXISTS recruitment_candidates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 candidate_no VARCHAR(80) NOT NULL,
 name VARCHAR(180) NOT NULL,
 phone VARCHAR(60) NULL,
 email VARCHAR(190) NULL,
 position VARCHAR(120) DEFAULT 'Security Guard',
 source VARCHAR(100) NULL,
 applied_date DATE NULL,
 status ENUM('new','screening','shortlisted','rejected','hired') DEFAULT 'new',
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_candidate(company_id,candidate_no),
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vetting_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NULL,
 candidate_id BIGINT UNSIGNED NULL,
 case_no VARCHAR(80) NOT NULL,
 status ENUM('open','in_progress','passed','failed','expired') DEFAULT 'open',
 opened_date DATE NOT NULL,
 completed_date DATE NULL,
 reviewer VARCHAR(180) NULL,
 notes TEXT NULL,
 UNIQUE KEY uq_vetting(company_id,case_no),
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL,
 FOREIGN KEY(candidate_id) REFERENCES recruitment_candidates(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vetting_checks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vetting_case_id BIGINT UNSIGNED NOT NULL,
 check_type VARCHAR(120) NOT NULL,
 status ENUM('pending','passed','failed','not_required') DEFAULT 'pending',
 checked_date DATE NULL,
 reference_no VARCHAR(120) NULL,
 notes TEXT NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(vetting_case_id) REFERENCES vetting_cases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NOT NULL,
 document_type VARCHAR(120) NOT NULL,
 document_no VARCHAR(120) NULL,
 issue_date DATE NULL,
 expiry_date DATE NULL,
 file_path VARCHAR(500) NULL,
 status ENUM('pending','valid','expired','rejected') DEFAULT 'pending',
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE,
 INDEX idx_doc_expiry(company_id,expiry_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS training_courses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 code VARCHAR(60) NOT NULL,
 name VARCHAR(180) NOT NULL,
 validity_days INT NULL,
 mandatory TINYINT(1) DEFAULT 0,
 status ENUM('active','inactive') DEFAULT 'active',
 UNIQUE KEY uq_course(company_id,code),
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS training_enrolments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NOT NULL,
 course_id BIGINT UNSIGNED NOT NULL,
 completed_date DATE NULL,
 expiry_date DATE NULL,
 status ENUM('planned','completed','expired','failed') DEFAULT 'planned',
 score DECIMAL(5,2) NULL,
 certificate_no VARCHAR(120) NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE,
 FOREIGN KEY(course_id) REFERENCES training_courses(id) ON DELETE CASCADE,
 INDEX idx_training_expiry(company_id,expiry_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS employee_availability (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NOT NULL,
 availability_date DATE NOT NULL,
 status ENUM('available','unavailable','leave','sick','off') DEFAULT 'available',
 start_time TIME NULL,
 end_time TIME NULL,
 notes TEXT NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 UNIQUE KEY uq_availability(company_id,employee_id,availability_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_requirements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 site_id BIGINT UNSIGNED NULL,
 post_id BIGINT UNSIGNED NULL,
 requirement_type ENUM('document','training','vetting','licence') NOT NULL,
 requirement_name VARCHAR(180) NOT NULL,
 mandatory TINYINT(1) DEFAULT 1,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE,
 FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_alerts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NULL,
 alert_type VARCHAR(100) NOT NULL,
 subject VARCHAR(220) NOT NULL,
 due_date DATE NULL,
 severity ENUM('info','warning','critical') DEFAULT 'warning',
 status ENUM('open','acknowledged','resolved') DEFAULT 'open',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL,
 INDEX idx_alerts(company_id,status,due_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS deployment_eligibility_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 guard_id BIGINT UNSIGNED NOT NULL,
 shift_id BIGINT UNSIGNED NULL,
 eligible TINYINT(1) NOT NULL,
 reasons TEXT NULL,
 checked_by BIGINT UNSIGNED NULL,
 checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(guard_id) REFERENCES guards(id) ON DELETE CASCADE,
 FOREIGN KEY(shift_id) REFERENCES shifts(id) ON DELETE SET NULL
) ENGINE=InnoDB;