BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_interview_conflicts (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    declaration_no VARCHAR(50) NOT NULL,
    recruitment_panel_assignment_id BIGINT NOT NULL,
    recruitment_interview_id BIGINT NULL,
    recruitment_candidate_id BIGINT NOT NULL,
    recruitment_job_id BIGINT NULL,
    recruitment_panel_id BIGINT NOT NULL,
    panelist_user_id UUID NOT NULL,
    conflict_type VARCHAR(80) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'open',
    declared_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_by UUID NULL,
    reviewed_at TIMESTAMP NULL,
    resolution_action VARCHAR(40) NULL,
    resolution_note TEXT NULL,
    workflow_audit JSONB NOT NULL DEFAULT '[]'::jsonb,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_by UUID NULL,
    updated_by UUID NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    deleted_by UUID NULL,
    CONSTRAINT fk_recruitment_interview_conflicts_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_interview_conflicts_assignment FOREIGN KEY (recruitment_panel_assignment_id) REFERENCES hr_payroll.recruitment_panel_assignments(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_interview_conflicts_interview FOREIGN KEY (recruitment_interview_id) REFERENCES hr_payroll.recruitment_interviews(id) ON DELETE SET NULL,
    CONSTRAINT fk_recruitment_interview_conflicts_candidate FOREIGN KEY (recruitment_candidate_id) REFERENCES hr_payroll.recruitment_candidates(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_interview_conflicts_job FOREIGN KEY (recruitment_job_id) REFERENCES hr_payroll.recruitment_jobs(id) ON DELETE SET NULL,
    CONSTRAINT fk_recruitment_interview_conflicts_panel FOREIGN KEY (recruitment_panel_id) REFERENCES hr_payroll.recruitment_interview_panels(id) ON DELETE CASCADE,
    CONSTRAINT uq_recruitment_interview_conflicts_no UNIQUE (tenant_id, declaration_no)
);

CREATE INDEX IF NOT EXISTS idx_recruitment_interview_conflicts_assignment
    ON hr_payroll.recruitment_interview_conflicts (tenant_id, recruitment_panel_assignment_id)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_recruitment_interview_conflicts_panelist
    ON hr_payroll.recruitment_interview_conflicts (tenant_id, panelist_user_id, status)
    WHERE is_deleted = FALSE;

COMMIT;