-- migrate:up

ALTER TABLE task_completions
  MODIFY ledger_transaction_id BIGINT UNSIGNED NOT NULL,
  ADD CONSTRAINT fk_task_completions_ledger FOREIGN KEY (ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT;

ALTER TABLE referral_rewards
  ADD CONSTRAINT fk_referral_rewards_ledger FOREIGN KEY (ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT;

CREATE INDEX idx_users_active_last_seen ON users (status, last_seen_at, id);
CREATE INDEX idx_task_attempts_expiry ON task_attempts (status, expires_at, id);
CREATE INDEX idx_ledger_transactions_currency_posted ON ledger_transactions (currency_id, status, posted_at, id);
CREATE INDEX idx_ledger_entries_transaction ON ledger_entries (transaction_id, id);
CREATE INDEX idx_withdrawal_requests_method_status ON withdrawal_requests (withdrawal_method_id, status, requested_at, id);
CREATE INDEX idx_notifications_user_unread ON notifications (user_id, read_at, created_at, id);
CREATE INDEX idx_telegram_updates_processing ON telegram_updates (processing_status, received_at, id);
CREATE INDEX idx_security_events_actor ON security_events (actor_type, actor_reference, occurred_at, id);
CREATE INDEX idx_fraud_flags_entity ON fraud_flags (entity_type, entity_reference, status, created_at);
CREATE INDEX idx_job_runs_name_started ON job_runs (job_name, started_at, id);

CREATE OR REPLACE VIEW ledger_transaction_balances AS
SELECT
  transaction_id,
  SUM(CASE WHEN entry_side = 'debit' THEN amount_minor ELSE 0 END) AS debit_minor,
  SUM(CASE WHEN entry_side = 'credit' THEN amount_minor ELSE 0 END) AS credit_minor,
  SUM(CASE WHEN entry_side = 'debit' THEN amount_minor ELSE -CAST(amount_minor AS SIGNED) END) AS difference_minor,
  COUNT(*) AS entry_count,
  SUM(CASE WHEN entry_side = 'debit' THEN amount_minor ELSE -CAST(amount_minor AS SIGNED) END) = 0 AS is_balanced
FROM ledger_entries
GROUP BY transaction_id;

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;

-- migrate:down

DROP VIEW IF EXISTS wallet_account_balances;
DROP VIEW IF EXISTS ledger_transaction_balances;
DROP INDEX idx_job_runs_name_started ON job_runs;
DROP INDEX idx_fraud_flags_entity ON fraud_flags;
DROP INDEX idx_security_events_actor ON security_events;
DROP INDEX idx_telegram_updates_processing ON telegram_updates;
DROP INDEX idx_notifications_user_unread ON notifications;
DROP INDEX idx_withdrawal_requests_method_status ON withdrawal_requests;
DROP INDEX idx_ledger_entries_transaction ON ledger_entries;
DROP INDEX idx_ledger_transactions_currency_posted ON ledger_transactions;
DROP INDEX idx_task_attempts_expiry ON task_attempts;
DROP INDEX idx_users_active_last_seen ON users;
ALTER TABLE referral_rewards DROP FOREIGN KEY fk_referral_rewards_ledger;
ALTER TABLE task_completions
  DROP FOREIGN KEY fk_task_completions_ledger,
  MODIFY ledger_transaction_id BIGINT UNSIGNED NULL;