BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_application_forms (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    form_no VARCHAR(40) NOT NULL,
    form_name VARCHAR(180) NOT NULL,
    recruitment_job_id BIGINT NULL,
    vacancy_type VARCHAR(30) NOT NULL DEFAULT 'external',
    form_purpose VARCHAR(60) NOT NULL DEFAULT 'application',
    form_fields JSONB NOT NULL DEFAULT '[]'::jsonb,
    consent_required BOOLEAN NOT NULL DEFAULT TRUE,
    cv_required BOOLEAN NOT NULL DEFAULT TRUE,
    published_at TIMESTAMP NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'draft',
    notes TEXT NULL,
    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,
    CONSTRAINT recruitment_application_forms_tenant_fk FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT recruitment_application_forms_job_fk FOREIGN KEY (recruitment_job_id) REFERENCES hr_payroll.recruitment_jobs(id) ON DELETE SET NULL,
    CONSTRAINT recruitment_application_forms_form_no_unique UNIQUE (tenant_id, form_no),
    CONSTRAINT recruitment_application_forms_vacancy_type_chk CHECK (vacancy_type IN ('internal', 'external', 'both')),
    CONSTRAINT recruitment_application_forms_status_chk CHECK (status IN ('draft', 'published', 'paused', 'archived'))
);

CREATE INDEX IF NOT EXISTS idx_recruitment_application_forms_tenant_status
    ON hr_payroll.recruitment_application_forms (tenant_id, status, is_active)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_recruitment_application_forms_job
    ON hr_payroll.recruitment_application_forms (tenant_id, recruitment_job_id)
    WHERE is_deleted = FALSE;

COMMIT;