BEGIN;

CREATE SCHEMA IF NOT EXISTS hr_payroll;

CREATE TABLE IF NOT EXISTS hr_payroll.contract_renewals (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    renewal_no varchar(100),
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    contract_type varchar(80),
    current_contract_end_date date,
    proposed_start_date date,
    proposed_end_date date,
    current_salary numeric(18,2) DEFAULT 0,
    proposed_salary numeric(18,2) DEFAULT 0,
    currency_code varchar(10),
    approval_status varchar(50) DEFAULT 'draft',
    status varchar(50) DEFAULT 'pending',
    notes text,
    decision_reason text,
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_contract_renewals_no UNIQUE (tenant_id, renewal_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.contract_expirations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    expiration_no varchar(100),
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    contract_type varchar(80),
    contract_end_date date,
    notice_date date,
    risk_level varchar(50) DEFAULT 'medium',
    action_required varchar(120),
    renewal_status varchar(50) DEFAULT 'not_started',
    status varchar(50) DEFAULT 'open',
    notes text,
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_contract_expirations_no UNIQUE (tenant_id, expiration_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.exit_interviews (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    interview_no varchar(100),
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    separation_type varchar(80),
    scheduled_at timestamp,
    interviewer_user_id uuid,
    reason_for_leaving text,
    feedback text,
    rehire_eligible boolean DEFAULT false,
    status varchar(50) DEFAULT 'scheduled',
    notes text,
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_exit_interviews_no UNIQUE (tenant_id, interview_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.clearance_cases (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    clearance_no varchar(100),
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    exit_date date,
    department_clearance_status varchar(50) DEFAULT 'pending',
    it_clearance_status varchar(50) DEFAULT 'pending',
    assets_clearance_status varchar(50) DEFAULT 'pending',
    finance_clearance_status varchar(50) DEFAULT 'pending',
    hr_clearance_status varchar(50) DEFAULT 'pending',
    overall_status varchar(50) DEFAULT 'pending',
    final_pay_hold boolean DEFAULT true,
    notes text,
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_clearance_cases_no UNIQUE (tenant_id, clearance_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.retirement_plans (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    retirement_no varchar(100),
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    expected_retirement_date date,
    notice_sent_at timestamp,
    benefits_review_status varchar(50) DEFAULT 'pending',
    succession_plan_status varchar(50) DEFAULT 'pending',
    handover_status varchar(50) DEFAULT 'pending',
    status varchar(50) DEFAULT 'planning',
    notes text,
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_retirement_plans_no UNIQUE (tenant_id, retirement_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_exceptions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    exception_no varchar(100),
    payroll_run_id bigint REFERENCES hr_payroll.payroll_runs(id) ON DELETE SET NULL,
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    exception_type varchar(100),
    severity varchar(50) DEFAULT 'medium',
    amount numeric(18,2) DEFAULT 0,
    currency_code varchar(10),
    description text,
    resolution_status varchar(50) DEFAULT 'open',
    resolution_notes text,
    status varchar(50) DEFAULT 'open',
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_payroll_exceptions_no UNIQUE (tenant_id, exception_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_validation_issues (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    issue_no varchar(100),
    payroll_run_id bigint REFERENCES hr_payroll.payroll_runs(id) ON DELETE SET NULL,
    employee_id bigint REFERENCES hr_payroll.employees(id) ON DELETE SET NULL,
    issue_type varchar(100),
    severity varchar(50) DEFAULT 'medium',
    blocker boolean DEFAULT false,
    amount numeric(18,2) DEFAULT 0,
    currency_code varchar(10),
    description text,
    resolution_status varchar(50) DEFAULT 'open',
    status varchar(50) DEFAULT 'open',
    is_active boolean DEFAULT true,
    is_deleted boolean DEFAULT false,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT uq_payroll_validation_issues_no UNIQUE (tenant_id, issue_no)
);

CREATE INDEX IF NOT EXISTS idx_contract_renewals_tenant_status ON hr_payroll.contract_renewals (tenant_id, status) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_contract_expirations_tenant_date ON hr_payroll.contract_expirations (tenant_id, contract_end_date) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_exit_interviews_tenant_status ON hr_payroll.exit_interviews (tenant_id, status) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_clearance_cases_tenant_status ON hr_payroll.clearance_cases (tenant_id, overall_status) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_retirement_plans_tenant_date ON hr_payroll.retirement_plans (tenant_id, expected_retirement_date) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_payroll_exceptions_tenant_status ON hr_payroll.payroll_exceptions (tenant_id, status, severity) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_payroll_validation_issues_tenant_status ON hr_payroll.payroll_validation_issues (tenant_id, status, blocker) WHERE is_deleted = false;

COMMIT;