-- =====================================================
-- INVESTMENT SCHEMA - ACCOUNTS TABLE
-- =====================================================
-- Description: Individual investor PAMM accounts
-- Schema: investment
-- Author: Database Agent
-- Date: 2025-12-06
-- =====================================================

CREATE TABLE investment.accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Propietario
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Producto PAMM
  product_id UUID NOT NULL REFERENCES investment.products(id) ON DELETE RESTRICT,

  -- Identificación
  account_number VARCHAR(20) NOT NULL UNIQUE, -- INV-202512-00001

  -- Balance
  initial_balance DECIMAL(15,2) NOT NULL,
  current_balance DECIMAL(15,2) NOT NULL,
  total_deposits DECIMAL(15,2) DEFAULT 0.00,
  total_withdrawals DECIMAL(15,2) DEFAULT 0.00,
  total_distributions DECIMAL(15,2) DEFAULT 0.00,

  -- Rentabilidad
  total_return_percent DECIMAL(10,4) DEFAULT 0.00,
  total_return_amount DECIMAL(15,2) DEFAULT 0.00,

  -- Perfil de riesgo del usuario
  user_risk_profile investment.risk_profile NOT NULL,
  questionnaire_id UUID REFERENCES investment.risk_questionnaire(id),

  -- Estado
  status investment.account_status DEFAULT 'pending_kyc',

  -- KYC/Compliance
  kyc_verified BOOLEAN DEFAULT false,
  kyc_verified_at TIMESTAMPTZ,
  kyc_verified_by VARCHAR(100),

  -- Fechas importantes
  opened_at TIMESTAMPTZ,
  closed_at TIMESTAMPTZ,
  last_distribution_at TIMESTAMPTZ,

  -- Metadata
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Índices
CREATE INDEX idx_accounts_user ON investment.accounts(user_id);
CREATE INDEX idx_accounts_product ON investment.accounts(product_id);
CREATE INDEX idx_accounts_status ON investment.accounts(status);
CREATE INDEX idx_accounts_active ON investment.accounts(status) WHERE status = 'active';
CREATE INDEX idx_accounts_number ON investment.accounts(account_number);

-- Comentarios
COMMENT ON TABLE investment.accounts IS 'Individual investor PAMM accounts';
COMMENT ON COLUMN investment.accounts.account_number IS 'Unique account identifier (INV-YYYYMM-NNNNN)';
COMMENT ON COLUMN investment.accounts.current_balance IS 'Current account balance including all deposits, withdrawals, and distributions';
COMMENT ON COLUMN investment.accounts.total_return_percent IS 'Cumulative return percentage since account opening';
COMMENT ON COLUMN investment.accounts.user_risk_profile IS 'Risk profile from questionnaire, must match product recommendation';