ALTER TABLE loan_monthly_transactions
    ADD COLUMN bookkeeping_type ENUM('replacement', 'payment') NOT NULL DEFAULT 'replacement' AFTER direction;

UPDATE loan_monthly_transactions
SET bookkeeping_type = CASE
    WHEN payment_status = 'belum_dibayar' THEN 'payment'
    ELSE 'replacement'
END;

ALTER TABLE loan_monthly_transactions
    ADD INDEX idx_lmt_bookkeeping_type (bookkeeping_type);

ALTER TABLE loan_monthly_closing_items
    ADD COLUMN bookkeeping_type ENUM('replacement', 'payment') NOT NULL DEFAULT 'replacement' AFTER direction;

UPDATE loan_monthly_closing_items
SET bookkeeping_type = CASE
    WHEN payment_status = 'belum_dibayar' THEN 'payment'
    ELSE 'replacement'
END;

ALTER TABLE loan_monthly_closing_items
    ADD INDEX idx_lmci_bookkeeping_type (bookkeeping_type);

ALTER TABLE loan_monthly_logs
    ADD COLUMN bookkeeping_type ENUM('replacement', 'payment') NULL AFTER direction;

UPDATE loan_monthly_logs
SET bookkeeping_type = CASE
    WHEN action IN ('create_transaction', 'update_transaction', 'closing_snapshot', 'carry_over_create')
         AND JSON_UNQUOTE(JSON_EXTRACT(meta_json, '$.payment_status')) = 'belum_dibayar' THEN 'payment'
    WHEN action IN ('create_transaction', 'update_transaction', 'closing_snapshot', 'carry_over_create') THEN 'replacement'
    ELSE bookkeeping_type
END;

ALTER TABLE loan_monthly_logs
    ADD INDEX idx_lml_bookkeeping_type (bookkeeping_type);