BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.candidate_ai_scoring_settings (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    setting_no VARCHAR(50) NOT NULL,
    setting_name VARCHAR(120) NOT NULL DEFAULT 'Default Candidate AI Scoring',
    skills_weight NUMERIC(8, 2) NOT NULL DEFAULT 28.00,
    experience_weight NUMERIC(8, 2) NOT NULL DEFAULT 24.00,
    qualification_weight NUMERIC(8, 2) NOT NULL DEFAULT 18.00,
    interview_weight NUMERIC(8, 2) NOT NULL DEFAULT 22.00,
    readiness_bonus NUMERIC(8, 2) NOT NULL DEFAULT 8.00,
    offer_penalty NUMERIC(8, 2) NOT NULL DEFAULT 5.00,
    minimum_profile_completeness NUMERIC(8, 2) NOT NULL DEFAULT 60.00,
    minimum_confidence_for_recommendation NUMERIC(8, 2) NOT NULL DEFAULT 70.00,
    status VARCHAR(40) NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    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_candidate_ai_scoring_settings_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_candidate_ai_scoring_settings_no UNIQUE (tenant_id, setting_no)
);

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

INSERT INTO hr_payroll.candidate_ai_scoring_settings (
    tenant_id,
    setting_no,
    setting_name,
    skills_weight,
    experience_weight,
    qualification_weight,
    interview_weight,
    readiness_bonus,
    offer_penalty,
    minimum_profile_completeness,
    minimum_confidence_for_recommendation,
    status,
    is_active
)
SELECT
    t.id,
    'CAI-' || to_char(CURRENT_DATE, 'YYYY') || '-0001',
    'Default Candidate AI Scoring',
    28.00,
    24.00,
    18.00,
    22.00,
    8.00,
    5.00,
    60.00,
    70.00,
    'active',
    TRUE
FROM platform.tenants t
WHERE NOT EXISTS (
    SELECT 1
    FROM hr_payroll.candidate_ai_scoring_settings s
    WHERE s.tenant_id = t.id
      AND COALESCE(s.is_deleted, FALSE) = FALSE
);

COMMIT;