BEGIN;

ALTER TABLE IF EXISTS hr_payroll.recruitment_candidates
    ADD COLUMN IF NOT EXISTS stage_changed_at timestamp,
    ADD COLUMN IF NOT EXISTS stage_changed_by uuid,
    ADD COLUMN IF NOT EXISTS stage_change_notes text;

ALTER TABLE IF EXISTS hr_payroll.recruitment_interviews
    ADD COLUMN IF NOT EXISTS feedback text,
    ADD COLUMN IF NOT EXISTS completed_at timestamp,
    ADD COLUMN IF NOT EXISTS cancelled_at timestamp,
    ADD COLUMN IF NOT EXISTS decided_by uuid,
    ADD COLUMN IF NOT EXISTS decided_at timestamp;

ALTER TABLE IF EXISTS hr_payroll.recruitment_offers
    ADD COLUMN IF NOT EXISTS approved_by uuid,
    ADD COLUMN IF NOT EXISTS approved_at timestamp,
    ADD COLUMN IF NOT EXISTS approval_reason text,
    ADD COLUMN IF NOT EXISTS sent_by uuid,
    ADD COLUMN IF NOT EXISTS accepted_by uuid,
    ADD COLUMN IF NOT EXISTS acceptance_note text,
    ADD COLUMN IF NOT EXISTS declined_reason text,
    ADD COLUMN IF NOT EXISTS converted_employee_id bigint,
    ADD COLUMN IF NOT EXISTS converted_at timestamp,
    ADD COLUMN IF NOT EXISTS converted_by uuid,
    ADD COLUMN IF NOT EXISTS salary_structure_id bigint,
    ADD COLUMN IF NOT EXISTS employment_type varchar(40);

CREATE INDEX IF NOT EXISTS idx_hr_recruitment_candidates_stage
    ON hr_payroll.recruitment_candidates (tenant_id, current_stage, stage_changed_at);

CREATE INDEX IF NOT EXISTS idx_hr_recruitment_offers_status
    ON hr_payroll.recruitment_offers (tenant_id, status, approved_at, converted_at);

WITH permissions_to_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr-payroll', 'hr.recruitment.view', 'View Recruitment', 'View recruitment jobs, candidates, interviews, and offers.'),
        ('hr-payroll', 'hr.recruitment.create', 'Create Recruitment Records', 'Create recruitment jobs, candidates, interviews, and offers.'),
        ('hr-payroll', 'hr.recruitment.update', 'Update Recruitment Records', 'Update recruitment jobs, candidates, interviews, and offers.'),
        ('hr-payroll', 'hr.recruitment.delete', 'Delete Recruitment Records', 'Delete or remove recruitment records.'),
        ('hr-payroll', 'hr.recruitment.stage', 'Move Candidate Stage', 'Move candidates through recruitment stages.'),
        ('hr-payroll', 'hr.recruitment.offer.approve', 'Approve Recruitment Offers', 'Approve or reject candidate offers.'),
        ('hr-payroll', 'hr.recruitment.offer.convert', 'Convert Offer To Employee', 'Convert accepted recruitment offers into employee master records.')
)
INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT p.module_code, p.permission_code, p.permission_name, p.description
FROM permissions_to_seed p
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions existing WHERE existing.permission_code = p.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 IN (
    'hr.recruitment.view',
    'hr.recruitment.create',
    'hr.recruitment.update',
    'hr.recruitment.delete',
    'hr.recruitment.stage',
    'hr.recruitment.offer.approve',
    'hr.recruitment.offer.convert'
)
WHERE LOWER(COALESCE(r.code, '')) IN (
    'tenant_owner',
    'tenant-owner',
    'owner',
    'admin',
    'administrator',
    'hr_manager',
    'hr-admin',
    'hr_admin',
    'payroll_manager'
)
  AND NOT EXISTS (
      SELECT 1
      FROM iam.role_permissions rp
      WHERE rp.role_id = r.id
        AND rp.permission_id = p.id
  );

COMMIT;