BEGIN;

ALTER TABLE hr_payroll.recruitment_candidates
    ADD COLUMN IF NOT EXISTS merged_into_candidate_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS merged_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS merged_by UUID NULL,
    ADD COLUMN IF NOT EXISTS merge_case_id BIGINT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'fk_recruitment_candidates_merged_into'
          AND conrelid = 'hr_payroll.recruitment_candidates'::regclass
    ) THEN
        ALTER TABLE hr_payroll.recruitment_candidates
            ADD CONSTRAINT fk_recruitment_candidates_merged_into
            FOREIGN KEY (merged_into_candidate_id)
            REFERENCES hr_payroll.recruitment_candidates(id)
            ON DELETE RESTRICT;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'fk_recruitment_candidates_merge_case'
          AND conrelid = 'hr_payroll.recruitment_candidates'::regclass
    ) THEN
        ALTER TABLE hr_payroll.recruitment_candidates
            ADD CONSTRAINT fk_recruitment_candidates_merge_case
            FOREIGN KEY (merge_case_id)
            REFERENCES hr_payroll.recruitment_candidate_duplicate_cases(id)
            ON DELETE RESTRICT;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_recruitment_candidates_merged_into
    ON hr_payroll.recruitment_candidates (tenant_id, merged_into_candidate_id)
    WHERE merged_into_candidate_id IS NOT NULL;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT 'hr-payroll', 'hr.recruitment.candidates.merge', 'Merge Duplicate Candidates', 'Authorizes reviewed duplicate candidate records to be consolidated with full audit evidence.'
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions WHERE permission_code = 'hr.recruitment.candidates.merge'
);

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.candidates.merge'
WHERE LOWER(COALESCE(r.code, '')) IN ('hr_approver', 'hr_manager', 'hr-admin', 'hr_admin', 'tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator')
  AND COALESCE(r.is_active, TRUE) = TRUE
  AND NOT EXISTS (
      SELECT 1 FROM iam.role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

COMMIT;