BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.succession_plans (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    plan_no VARCHAR(50) NOT NULL DEFAULT ('SCP-' || TO_CHAR(CURRENT_DATE, 'YYYY') || '-' || UPPER(SUBSTR(MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT), 1, 8))),
    designation_id BIGINT NOT NULL REFERENCES org.designations(id),
    criticality VARCHAR(30) NOT NULL DEFAULT 'critical' CHECK (criticality IN ('mission_critical', 'critical', 'important')),
    vacancy_risk VARCHAR(20) NOT NULL DEFAULT 'high' CHECK (vacancy_risk IN ('critical', 'high', 'medium', 'low')),
    status VARCHAR(30) NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'under_review', 'approved', 'closed')),
    target_review_date DATE,
    requirements JSONB NOT NULL DEFAULT '[]'::JSONB,
    notes TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_by UUID,
    CONSTRAINT uq_succession_plan_no UNIQUE (tenant_id, plan_no),
    CONSTRAINT uq_succession_plan_position UNIQUE (tenant_id, designation_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.succession_candidates (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    succession_plan_id BIGINT NOT NULL REFERENCES hr_payroll.succession_plans(id),
    employee_id BIGINT NOT NULL REFERENCES hr_payroll.employees(id),
    readiness VARCHAR(30) NOT NULL DEFAULT 'ready_1_year' CHECK (readiness IN ('ready_now', 'ready_1_year', 'ready_2_3_years', 'emergency_cover', 'not_ready')),
    nomination_reason TEXT NOT NULL,
    development_gaps JSONB NOT NULL DEFAULT '[]'::JSONB,
    development_plan TEXT,
    target_ready_date DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'nominated' CHECK (status IN ('nominated', 'assessed', 'approved', 'withdrawn')),
    performance_score NUMERIC(7,2),
    potential_score NUMERIC(7,2),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_by UUID,
    CONSTRAINT uq_succession_candidate UNIQUE (tenant_id, succession_plan_id, employee_id)
);

CREATE INDEX IF NOT EXISTS idx_succession_plans_tenant_status ON hr_payroll.succession_plans (tenant_id, status, is_deleted);
CREATE INDEX IF NOT EXISTS idx_succession_candidates_plan ON hr_payroll.succession_candidates (tenant_id, succession_plan_id, status, is_deleted);

DROP TRIGGER IF EXISTS trg_audit_succession_plans ON hr_payroll.succession_plans;
CREATE TRIGGER trg_audit_succession_plans
AFTER INSERT OR UPDATE OR DELETE ON hr_payroll.succession_plans
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

DROP TRIGGER IF EXISTS trg_audit_succession_candidates ON hr_payroll.succession_candidates;
CREATE TRIGGER trg_audit_succession_candidates
AFTER INSERT OR UPDATE OR DELETE ON hr_payroll.succession_candidates
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

COMMIT;