BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_medical_assessments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    assessment_no varchar(50) NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    consent_id bigint NULL,
    provider_name varchar(180) NULL,
    assessment_date date NULL,
    outcome varchar(40) NOT NULL DEFAULT 'pending',
    fitness_status varchar(60) NOT NULL DEFAULT 'pending',
    restrictions text NULL,
    document_references jsonb NOT NULL DEFAULT '[]'::jsonb,
    notes text NULL,
    status varchar(40) NOT NULL DEFAULT 'pending',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_medical_assessments_no UNIQUE (tenant_id, assessment_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_background_verifications (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    verification_no varchar(50) NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    consent_id bigint NULL,
    verification_type varchar(80) NOT NULL DEFAULT 'background',
    provider_name varchar(180) NULL,
    requested_at timestamp NULL,
    completed_at timestamp NULL,
    result varchar(40) NOT NULL DEFAULT 'pending',
    risk_level varchar(40) NOT NULL DEFAULT 'unknown',
    findings text NULL,
    document_reference varchar(240) NULL,
    status varchar(40) NOT NULL DEFAULT 'pending',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_background_verifications_no UNIQUE (tenant_id, verification_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_reference_checks (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    reference_no varchar(50) NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    consent_id bigint NULL,
    referee_name varchar(180) NOT NULL,
    referee_email varchar(180) NULL,
    referee_phone varchar(60) NULL,
    relationship_to_candidate varchar(120) NULL,
    requested_at timestamp NULL,
    responded_at timestamp NULL,
    rating numeric(6, 2) NULL,
    recommendation varchar(40) NOT NULL DEFAULT 'pending',
    feedback text NULL,
    status varchar(40) NOT NULL DEFAULT 'pending',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_reference_checks_no UNIQUE (tenant_id, reference_no)
);

CREATE INDEX IF NOT EXISTS idx_recruitment_medical_assessments_candidate
    ON hr_payroll.recruitment_medical_assessments (tenant_id, recruitment_candidate_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_background_verifications_candidate
    ON hr_payroll.recruitment_background_verifications (tenant_id, recruitment_candidate_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_reference_checks_candidate
    ON hr_payroll.recruitment_reference_checks (tenant_id, recruitment_candidate_id, status) WHERE is_deleted = FALSE;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT v.module_code, v.permission_code, v.permission_name, v.description
FROM (VALUES
    ('hr-payroll', 'hr.recruitment.checks.manage', 'Manage Recruitment Checks', 'Manage medical assessments, background verification, and reference checks.')
) AS v(module_code, permission_code, permission_name, description)
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions p WHERE p.permission_code = v.permission_code
);

INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM iam.roles r
JOIN iam.permissions p ON p.permission_code = 'hr.recruitment.checks.manage'
WHERE LOWER(COALESCE(r.code, '')) IN ('tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator', 'hr_manager', 'hr-admin', 'hr_admin')
  AND NOT EXISTS (
      SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

COMMIT;