BEGIN;

ALTER TABLE IF EXISTS hr_payroll.employees
    ADD COLUMN IF NOT EXISTS salary_structure_id bigint,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10);

ALTER TABLE IF EXISTS hr_payroll.payroll_run_items
    ADD COLUMN IF NOT EXISTS gross_pay numeric(18, 2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10),
    ADD COLUMN IF NOT EXISTS worked_minutes integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS overtime_minutes integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS hourly_rate numeric(18, 4),
    ADD COLUMN IF NOT EXISTS source_salary_amount numeric(18, 2),
    ADD COLUMN IF NOT EXISTS salary_structure_id bigint,
    ADD COLUMN IF NOT EXISTS salary_structure_code varchar(50),
    ADD COLUMN IF NOT EXISTS salary_structure_name varchar(150),
    ADD COLUMN IF NOT EXISTS payroll_component_summary jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS attendance_summary jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS updated_by uuid,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE INDEX IF NOT EXISTS idx_hr_payroll_run_items_employee_run
    ON hr_payroll.payroll_run_items (tenant_id, payroll_run_id, employee_id);

DO $$
DECLARE
    v_permission_id iam.permissions.id%TYPE;
BEGIN
    IF to_regclass('iam.permissions') IS NULL THEN
        RETURN;
    END IF;

    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT
        'hr-payroll',
        'hr.payroll.generate_items',
        'Generate Payroll Run Items',
        'Generate payroll run employee items from salary structures, payroll components, and attendance.'
    WHERE NOT EXISTS (
        SELECT 1 FROM iam.permissions p WHERE p.permission_code = 'hr.payroll.generate_items'
    );

    SELECT id INTO v_permission_id
    FROM iam.permissions
    WHERE permission_code = 'hr.payroll.generate_items'
    LIMIT 1;

    IF v_permission_id IS NULL OR to_regclass('iam.role_permissions') IS NULL OR to_regclass('iam.roles') IS NULL THEN
        RETURN;
    END IF;

    INSERT INTO iam.role_permissions (role_id, permission_id)
    SELECT r.id, v_permission_id
    FROM iam.roles r
    WHERE LOWER(COALESCE(r.code, '')) IN (
        'tenant_owner',
        'tenant-owner',
        'owner',
        'admin',
        'administrator',
        'hr_manager',
        'payroll_manager',
        'finance_manager',
        'cost_accountant'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM iam.role_permissions rp
        WHERE rp.role_id = r.id
          AND rp.permission_id = v_permission_id
    );
END $$;

COMMIT;