USE db_abos_v0.1;

-- Fixes two Java/SQL stored-procedure signature mismatches found in the follow-up stored-
-- procedure audit (sp_process_payroll, sp_log_compliance_event). Both procedures were called by
-- SimpleJdbcCall with a parameter list that no longer matches the procedure actually deployed
-- (full_schema_snapshot.sql) — wrong order, params the procedure doesn't accept, and OUT
-- parameters the procedure never declared. Safe to re-run — every CREATE PROCEDURE is preceded
-- by DROP PROCEDURE IF EXISTS.
--
-- sp_renew_employee_contract and sp_create_vat_record were also audited (registered in
-- AbosProcedureProperties but with zero call sites). Investigation found ContractService and
-- VatService already implement the equivalent create/renew flows directly via JPA, without ever
-- calling these procedures — they are confirmed-obsolete, not half-built, so no SQL change is
-- made for them here. See the Java fix's commit/plan notes for details.

-- =============================================================================================
-- 1. sp_process_payroll — Java (HrmProcedureRepository) declared
--    (tenant_id, employee_id, company_wallet_id, pay_month, base_salary, allowances, deductions,
--    tax, processed_by) + OUT payroll_id, but the deployed procedure is
--    (p_tenant_id, p_employee_id, p_month, p_base_salary, p_allowances, p_deductions, p_tax,
--    p_net_pay, p_corp_wallet_id, p_created_by) with NO out parameter, and expects the caller to
--    have already computed p_net_pay. Also hardens the original bug where a missing employee
--    wallet left the corp-wallet debit with no matching employee-side credit (v_employee_wallet_id
--    would be NULL, the transfer/ledger rows would silently reference a NULL wallet, and the
--    balance UPDATE would touch zero rows) — instead of failing the whole payroll run, this
--    auto-creates the employee's wallet first, mirroring the lazy-create pattern already used by
--    InvoiceService.findOrCreateCorpWallet (Java) / sp_onboard_employee_full (SQL) for the
--    equivalent "wallet doesn't exist yet" case, rather than a hard SIGNAL failure.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_process_payroll;

DELIMITER $$

CREATE PROCEDURE sp_process_payroll(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_month VARCHAR(20),
    IN p_base_salary DECIMAL(15,2),
    IN p_allowances DECIMAL(15,2),
    IN p_deductions DECIMAL(15,2),
    IN p_tax DECIMAL(15,2),
    IN p_net_pay DECIMAL(15,2),
    IN p_corp_wallet_id INT,
    IN p_created_by INT
)
BEGIN
    DECLARE v_employee_wallet_id INT;
    DECLARE v_first_name VARCHAR(100);
    DECLARE v_last_name VARCHAR(100);
    DECLARE v_currency VARCHAR(10);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT id INTO v_employee_wallet_id FROM dim_wallets
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id LIMIT 1;

    -- Employee wallets are normally created at onboarding (sp_onboard_employee_full), but for
    -- legacy/edge-case employees that predate that logic, auto-create one now instead of paying
    -- into a black hole.
    IF v_employee_wallet_id IS NULL THEN
        SELECT first_name, last_name, currency INTO v_first_name, v_last_name, v_currency
        FROM dim_employees
        WHERE id = p_employee_id AND tenant_id = p_tenant_id;

        INSERT INTO dim_wallets (tenant_id, wallet_name, owner_type, employee_id, currency, created_by)
        VALUES (
            p_tenant_id, CONCAT(COALESCE(v_first_name, 'Employee'), ' ', COALESCE(v_last_name, p_employee_id), ' Wallet'),
            'Employee', p_employee_id, COALESCE(v_currency, 'USD'), p_created_by
        );
        SET v_employee_wallet_id = LAST_INSERT_ID();
    END IF;

    INSERT INTO fact_payroll (tenant_id, employee_id, month, base_salary, allowances, deductions, tax, net_pay, status, pay_date, created_by)
    VALUES (p_tenant_id, p_employee_id, p_month, p_base_salary, p_allowances, p_deductions, p_tax, p_net_pay, 'Paid', CURRENT_DATE, p_created_by);

    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, reference_entity, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, v_employee_wallet_id, p_net_pay, 'Payroll', 'Transfer', 'Completed', p_created_by);

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, 'Debit', p_net_pay, 'Payroll', p_created_by);

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, created_by)
    VALUES (p_tenant_id, v_employee_wallet_id, 'Credit', p_net_pay, 'Payroll', p_created_by);

    UPDATE dim_wallets SET balance = balance - p_net_pay WHERE id = p_corp_wallet_id;
    UPDATE dim_wallets SET balance = balance + p_net_pay WHERE id = v_employee_wallet_id;

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- Not included here — no SQL change needed (Java-only fixes):
--   sp_log_compliance_event — the deployed procedure signature is already correct
--   (p_tenant_id, p_legal_asset_id, p_log_type, p_details, p_new_expiry_date, p_created_by) and
--   has no OUT parameter. The mismatch was entirely on the Java side
--   (ComplianceProcedureRepository omitted p_tenant_id, sent an unknown p_log_date the procedure
--   never declared, and declared a nonexistent OUT log_id) — fixed in
--   ComplianceProcedureRepository.java, no SQL change required.
--
-- Not included here — confirmed obsolete, not wired up (see investigation notes above):
--   sp_renew_employee_contract (ContractService only exposes CREATE/UPDATE/SOFT_DELETE_CONTRACT
--   via plain JPA saves — there is no RENEW_CONTRACT event in ContractEventRequest, so wiring the
--   procedure would mean inventing a new feature, not fixing a broken one) and sp_create_vat_record
--   (VatService.createVat already inserts fact_vat rows directly via JPA; the procedure's
--   duplicate-period SIGNAL guard has no JPA-side equivalent, which is a minor gap but a separate
--   concern from "dead code", not addressed here).
-- =============================================================================================