-- =====================================================
-- ORBIQUANT IA - WALLET TRANSACTIONS TABLE
-- =====================================================
-- Description: Complete transaction history for all wallets
-- Schema: financial
-- =====================================================

CREATE TABLE financial.wallet_transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Wallet relacionada
  wallet_id UUID NOT NULL REFERENCES financial.wallets(id) ON DELETE RESTRICT,

  -- Tipo y estado
  transaction_type financial.transaction_type NOT NULL,
  status financial.transaction_status NOT NULL DEFAULT 'pending',

  -- Montos (precisión de 8 decimales)
  amount DECIMAL(20,8) NOT NULL,
  fee DECIMAL(15,8) DEFAULT 0,
  net_amount DECIMAL(20,8) GENERATED ALWAYS AS (amount - fee) STORED,

  -- Moneda
  currency financial.currency_code NOT NULL,

  -- Balances snapshot (para auditoría)
  balance_before DECIMAL(20,8),
  balance_after DECIMAL(20,8),

  -- Referencias externas
  stripe_payment_intent_id VARCHAR(255),
  stripe_transfer_id VARCHAR(255),
  stripe_charge_id VARCHAR(255),
  reference_id VARCHAR(100), -- ID de referencia interna (ej: trade_id, investment_id)

  -- Para transfers entre wallets
  destination_wallet_id UUID REFERENCES financial.wallets(id) ON DELETE RESTRICT,
  related_transaction_id UUID REFERENCES financial.wallet_transactions(id), -- TX relacionada (para transfers bidireccionales)

  -- Descripción y notas
  description TEXT,
  notes TEXT,
  metadata JSONB DEFAULT '{}',

  -- Procesamiento
  processed_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  failed_at TIMESTAMPTZ,
  failed_reason TEXT,

  -- Idempotency
  idempotency_key VARCHAR(255) UNIQUE,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Constraints
  CONSTRAINT positive_amount CHECK (amount > 0),
  CONSTRAINT positive_fee CHECK (fee >= 0),
  CONSTRAINT fee_lte_amount CHECK (fee <= amount),
  CONSTRAINT destination_for_transfers CHECK (
    (transaction_type IN ('transfer_in', 'transfer_out') AND destination_wallet_id IS NOT NULL) OR
    (transaction_type NOT IN ('transfer_in', 'transfer_out'))
  ),
  CONSTRAINT no_self_transfer CHECK (wallet_id != destination_wallet_id),
  CONSTRAINT completed_has_timestamp CHECK (
    (status = 'completed' AND completed_at IS NOT NULL) OR
    (status != 'completed')
  ),
  CONSTRAINT failed_has_reason CHECK (
    (status = 'failed' AND failed_reason IS NOT NULL AND failed_at IS NOT NULL) OR
    (status != 'failed')
  )
);

-- Indexes para performance y queries comunes
CREATE INDEX idx_wt_wallet_id ON financial.wallet_transactions(wallet_id);
CREATE INDEX idx_wt_transaction_type ON financial.wallet_transactions(transaction_type);
CREATE INDEX idx_wt_status ON financial.wallet_transactions(status);
CREATE INDEX idx_wt_created_at ON financial.wallet_transactions(created_at DESC);
CREATE INDEX idx_wt_reference_id ON financial.wallet_transactions(reference_id) WHERE reference_id IS NOT NULL;
CREATE INDEX idx_wt_stripe_payment ON financial.wallet_transactions(stripe_payment_intent_id) WHERE stripe_payment_intent_id IS NOT NULL;
CREATE INDEX idx_wt_stripe_transfer ON financial.wallet_transactions(stripe_transfer_id) WHERE stripe_transfer_id IS NOT NULL;
CREATE INDEX idx_wt_destination ON financial.wallet_transactions(destination_wallet_id) WHERE destination_wallet_id IS NOT NULL;
CREATE INDEX idx_wt_wallet_status_created ON financial.wallet_transactions(wallet_id, status, created_at DESC);
CREATE INDEX idx_wt_idempotency ON financial.wallet_transactions(idempotency_key) WHERE idempotency_key IS NOT NULL;

-- Composite index para queries de rango por wallet
CREATE INDEX idx_wt_wallet_date_range ON financial.wallet_transactions(wallet_id, created_at DESC)
  WHERE status = 'completed';

-- Comments
COMMENT ON TABLE financial.wallet_transactions IS 'Complete transaction history for all wallet operations';
COMMENT ON COLUMN financial.wallet_transactions.transaction_type IS 'Type of transaction: deposit, withdrawal, transfer, fee, etc.';
COMMENT ON COLUMN financial.wallet_transactions.net_amount IS 'Amount after fees (computed column)';
COMMENT ON COLUMN financial.wallet_transactions.reference_id IS 'Internal reference to related entity (trade, investment, etc.)';
COMMENT ON COLUMN financial.wallet_transactions.destination_wallet_id IS 'Target wallet for transfer operations';
COMMENT ON COLUMN financial.wallet_transactions.related_transaction_id IS 'Related transaction for bidirectional transfers';
COMMENT ON COLUMN financial.wallet_transactions.idempotency_key IS 'Unique key to prevent duplicate transactions';
COMMENT ON COLUMN financial.wallet_transactions.metadata IS 'Extensible JSON field for transaction-specific data';