-- ResellBD Step 20: finance, append-only double-entry ledger and payout workflow.
-- Apply ONLY to the NEW ResellBD database after migrations 001-011.
SET NAMES utf8mb4;

ALTER TABLE commissions
  ADD COLUMN reseller_customer_order_id BIGINT UNSIGNED NULL AFTER order_item_id,
  ADD COLUMN eligible_after DATETIME NULL,
  ADD COLUMN payout_id BIGINT UNSIGNED NULL,
  ADD COLUMN reversed_at DATETIME NULL,
  ADD KEY idx_commissions_reseller_order (reseller_customer_order_id),
  ADD KEY idx_commissions_eligibility (status,eligible_after);

ALTER TABLE payouts
  ADD COLUMN transaction_key CHAR(36) NULL,
  ADD COLUMN paid_reference VARCHAR(190) NULL;

ALTER TABLE ledger_entries
  ADD UNIQUE KEY uq_ledger_tx_account (transaction_key, account_code);

-- Idempotency and transaction-level audit record. One key represents one balanced journal.
CREATE TABLE IF NOT EXISTS finance_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_key CHAR(36) NOT NULL,
  idempotency_key VARCHAR(190) NOT NULL,
  event_type ENUM('commission_accrual','commission_reversal','payout_paid','manual_adjustment') NOT NULL,
  reference_type VARCHAR(64) NOT NULL,
  reference_id BIGINT UNSIGNED NOT NULL,
  amount_paisa BIGINT UNSIGNED NOT NULL,
  description VARCHAR(500) NOT NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_finance_transaction_key(transaction_key),
  UNIQUE KEY uq_finance_idempotency(idempotency_key),
  KEY idx_finance_reference(reference_type,reference_id), KEY idx_finance_event_date(event_type,created_at),
  CONSTRAINT fk_finance_tx_actor FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT chk_finance_tx_amount CHECK(amount_paisa > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Ledger is append-only. Corrections must be posted as reversing entries, never UPDATE/DELETE.
DROP TRIGGER IF EXISTS ledger_entries_no_update;
DROP TRIGGER IF EXISTS ledger_entries_no_delete;
DELIMITER $$
CREATE TRIGGER ledger_entries_no_update BEFORE UPDATE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Ledger entries are immutable; post a reversal instead'; END$$
CREATE TRIGGER ledger_entries_no_delete BEFORE DELETE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Ledger entries are immutable; post a reversal instead'; END$$
DELIMITER ;