-- migrate:up

ALTER TABLE ledger_transactions
  ADD CONSTRAINT chk_ledger_transactions_posted_time CHECK (
    (status = 'pending' AND posted_at IS NULL) OR
    (status IN ('posted', 'reversed') AND posted_at IS NOT NULL)
  );

CREATE OR REPLACE VIEW wallet_account_balances AS
SELECT
  le.account_id,
  SUM(CASE WHEN le.entry_side = 'credit'
    THEN le.amount_minor ELSE -CAST(le.amount_minor AS SIGNED) END) AS balance_minor,
  MAX(le.id) AS last_ledger_entry_id
FROM ledger_entries le
JOIN ledger_transactions lt
  ON lt.id = le.transaction_id AND lt.status IN ('posted', 'reversed')
GROUP BY le.account_id;

CREATE OR REPLACE VIEW wallet_user_balances AS
SELECT
  wa.user_id,
  wa.currency_id,
  COALESCE(SUM(CASE WHEN lt.id IS NOT NULL AND wa.account_type = 'available'
    THEN CASE WHEN le.entry_side = 'credit'
      THEN le.amount_minor ELSE -CAST(le.amount_minor AS SIGNED) END
    ELSE 0 END), 0) AS available_minor,
  COALESCE(SUM(CASE WHEN lt.id IS NOT NULL AND wa.account_type = 'held'
    THEN CASE WHEN le.entry_side = 'credit'
      THEN le.amount_minor ELSE -CAST(le.amount_minor AS SIGNED) END
    ELSE 0 END), 0) AS held_minor,
  COALESCE(SUM(CASE
    WHEN wa.account_type = 'available' AND (
      lt.transaction_type IN ('task_reward', 'referral_reward', 'mystery_reward') OR
      (lt.transaction_type = 'reversal' AND original.transaction_type IN (
        'task_reward', 'referral_reward', 'mystery_reward'
      ))
    ) THEN CASE WHEN le.entry_side = 'credit'
      THEN le.amount_minor ELSE -CAST(le.amount_minor AS SIGNED) END
    ELSE 0 END), 0) AS lifetime_earned_minor,
  MAX(CASE WHEN lt.id IS NOT NULL THEN le.id ELSE NULL END) AS last_ledger_entry_id
FROM wallet_accounts wa
LEFT JOIN ledger_entries le ON le.account_id = wa.id
LEFT JOIN ledger_transactions lt
  ON lt.id = le.transaction_id AND lt.status IN ('posted', 'reversed')
LEFT JOIN ledger_transactions original ON original.id = lt.reversal_of_transaction_id
WHERE wa.owner_type = 'user'
GROUP BY wa.user_id, wa.currency_id;

CREATE TRIGGER trg_ledger_entries_no_update
BEFORE UPDATE ON ledger_entries
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ledger entries are immutable';

CREATE TRIGGER trg_ledger_entries_no_delete
BEFORE DELETE ON ledger_entries
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ledger entries are append-only';

CREATE TRIGGER trg_ledger_transactions_no_delete
BEFORE DELETE ON ledger_transactions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ledger transactions cannot be deleted';

-- migrate:down

DROP TRIGGER IF EXISTS trg_ledger_transactions_no_delete;
DROP TRIGGER IF EXISTS trg_ledger_entries_no_delete;
DROP TRIGGER IF EXISTS trg_ledger_entries_no_update;
DROP VIEW IF EXISTS wallet_user_balances;

CREATE OR REPLACE VIEW wallet_account_balances AS
SELECT
  account_id,
  SUM(CASE WHEN entry_side = 'credit'
    THEN amount_minor ELSE -CAST(amount_minor AS SIGNED) END) AS balance_minor,
  MAX(id) AS last_ledger_entry_id
FROM ledger_entries
GROUP BY account_id;

ALTER TABLE ledger_transactions
  DROP CONSTRAINT chk_ledger_transactions_posted_time;