BEGIN;

ALTER TABLE IF EXISTS hr_payroll.recruitment_jobs
    ADD COLUMN IF NOT EXISTS application_form_id BIGINT NULL;

ALTER TABLE IF EXISTS hr_payroll.recruitment_candidates
    ADD COLUMN IF NOT EXISTS application_form_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS application_responses JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS application_submitted_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS application_source VARCHAR(40) NOT NULL DEFAULT 'manual';

CREATE INDEX IF NOT EXISTS idx_recruitment_jobs_application_form
    ON hr_payroll.recruitment_jobs (tenant_id, application_form_id)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_recruitment_candidates_application_form
    ON hr_payroll.recruitment_candidates (tenant_id, application_form_id)
    WHERE is_deleted = FALSE;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'recruitment_jobs_application_form_id_fkey'
    ) THEN
        ALTER TABLE hr_payroll.recruitment_jobs
            ADD CONSTRAINT recruitment_jobs_application_form_id_fkey
            FOREIGN KEY (application_form_id)
            REFERENCES hr_payroll.recruitment_application_forms(id)
            ON DELETE SET NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'recruitment_candidates_application_form_id_fkey'
    ) THEN
        ALTER TABLE hr_payroll.recruitment_candidates
            ADD CONSTRAINT recruitment_candidates_application_form_id_fkey
            FOREIGN KEY (application_form_id)
            REFERENCES hr_payroll.recruitment_application_forms(id)
            ON DELETE SET NULL;
    END IF;
END $$;

COMMIT;