-- ===== Quantaris Finance — PostgreSQL Schema =====
-- Run: psql -U quantaris -d quantaris -f schema.sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============ USERS ============
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'superadmin')),
  
  -- Profile
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  phone VARCHAR(30),
  country VARCHAR(3),
  
  -- KYC
  kyc_status VARCHAR(20) DEFAULT 'none' CHECK (kyc_status IN ('none', 'demo', 'pending', 'verified', 'rejected')),
  kyc_provider VARCHAR(50),
  kyc_reference_id VARCHAR(255),
  kyc_verified_at TIMESTAMPTZ,
  
  -- Plan
  plan_id VARCHAR(50) DEFAULT 'cadet',
  
  -- Security
  two_factor_secret VARCHAR(255),
  two_factor_enabled BOOLEAN DEFAULT FALSE,
  failed_login_attempts INTEGER DEFAULT 0,
  locked_until TIMESTAMPTZ,
  last_login_at TIMESTAMPTZ,
  last_login_ip INET,
  
  -- Status
  is_active BOOLEAN DEFAULT TRUE,
  is_blocked BOOLEAN DEFAULT FALSE,
  blocked_reason TEXT,
  blocked_at TIMESTAMPTZ,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_kyc_status ON users(kyc_status);
CREATE INDEX idx_users_plan ON users(plan_id);
CREATE INDEX idx_users_created ON users(created_at);

-- ============ WALLETS ============
CREATE TABLE wallets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  balance_usd DECIMAL(18, 8) DEFAULT 0,
  balance_btc DECIMAL(18, 8) DEFAULT 0,
  balance_eth DECIMAL(18, 8) DEFAULT 0,
  
  demo_balance_usd DECIMAL(18, 8) DEFAULT 50000,
  is_demo BOOLEAN DEFAULT TRUE,
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_wallets_user ON wallets(user_id);

-- ============ TRANSACTIONS ============
CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tx_id VARCHAR(50) UNIQUE NOT NULL, -- Human-readable ID: TX-XXXXX
  user_id UUID NOT NULL REFERENCES users(id),
  
  type VARCHAR(20) NOT NULL CHECK (type IN ('deposit', 'withdrawal', 'rebalance', 'adjustment', 'fee', 'reward')),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled')),
  
  amount DECIMAL(18, 8),
  currency VARCHAR(10) DEFAULT 'USD',
  
  -- Payment details
  payment_method VARCHAR(50), -- 'crypto', 'stripe', 'wire'
  payment_reference VARCHAR(255),
  wallet_address VARCHAR(255),
  tx_hash VARCHAR(255), -- blockchain tx hash
  network VARCHAR(20), -- 'ethereum', 'tron', 'bitcoin'
  
  -- Admin adjustments
  adjusted_by UUID REFERENCES users(id),
  adjustment_reason TEXT,
  
  -- Metadata
  metadata JSONB DEFAULT '{}',
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tx_user ON transactions(user_id);
CREATE INDEX idx_tx_type ON transactions(type);
CREATE INDEX idx_tx_status ON transactions(status);
CREATE INDEX idx_tx_created ON transactions(created_at);
CREATE INDEX idx_tx_txid ON transactions(tx_id);

-- ============ PLANS ============
CREATE TABLE plans (
  id VARCHAR(50) PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  subtitle VARCHAR(100),
  description TEXT,
  
  min_deposit DECIMAL(18, 2) NOT NULL DEFAULT 100,
  currency VARCHAR(10) DEFAULT 'USD',
  risk_profile VARCHAR(20) CHECK (risk_profile IN ('conservative', 'balanced', 'aggressive')),
  volatility_min DECIMAL(5, 2),
  volatility_max DECIMAL(5, 2),
  rebalance_frequency VARCHAR(20) DEFAULT 'daily',
  
  features JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT TRUE,
  is_highlighted BOOLEAN DEFAULT FALSE,
  sort_order INTEGER DEFAULT 0,
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============ ASSETS / POOLS ============
CREATE TABLE assets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  symbol VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  asset_class VARCHAR(50) NOT NULL, -- 'crypto', 'index', 'bonds', 'commodity', 'defi', 'stablecoin', 'real_estate', 'fund'
  
  target_allocation DECIMAL(5, 2) DEFAULT 0, -- percentage
  risk_level VARCHAR(20) CHECK (risk_level IN ('low', 'medium', 'high')),
  strategy VARCHAR(100), -- 'HODL + DCA', 'Buy & Hold', etc.
  
  color VARCHAR(10), -- hex color for charts
  
  is_active BOOLEAN DEFAULT TRUE,
  metadata JSONB DEFAULT '{}',
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============ PORTFOLIO SNAPSHOTS ============
CREATE TABLE portfolio_snapshots (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id),
  
  total_value_usd DECIMAL(18, 8),
  pnl_usd DECIMAL(18, 8),
  pnl_percent DECIMAL(8, 4),
  
  allocations JSONB NOT NULL, -- { "BTC": 22, "ETH": 15, ... }
  metrics JSONB DEFAULT '{}', -- { sharpeRatio, sortinoRatio, maxDrawdown, winRate }
  
  snapshot_date DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_snapshots_user_date ON portfolio_snapshots(user_id, snapshot_date);

-- ============ OPTIMIZATION DECISIONS ============
CREATE TABLE optimization_decisions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  
  decision_type VARCHAR(50) NOT NULL, -- 'rebalance', 'risk_adjustment', 'emergency'
  trigger_reason TEXT,
  
  old_allocations JSONB,
  new_allocations JSONB,
  affected_users INTEGER DEFAULT 0,
  total_volume_usd DECIMAL(18, 2),
  
  parameters JSONB DEFAULT '{}', -- algorithm parameters used
  execution_time_ms INTEGER,
  
  status VARCHAR(20) DEFAULT 'executed',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_optim_created ON optimization_decisions(created_at);

-- ============ KYC DOCUMENTS ============
CREATE TABLE kyc_documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id),
  
  document_type VARCHAR(30) NOT NULL, -- 'passport', 'id_card', 'driver_license', 'selfie', 'proof_of_address'
  file_path VARCHAR(500) NOT NULL, -- encrypted storage path
  file_hash VARCHAR(64), -- SHA-256 hash for integrity
  
  provider_reference VARCHAR(255),
  verification_status VARCHAR(20) DEFAULT 'pending',
  rejection_reason TEXT,
  
  uploaded_at TIMESTAMPTZ DEFAULT NOW(),
  verified_at TIMESTAMPTZ
);

CREATE INDEX idx_kyc_user ON kyc_documents(user_id);

-- ============ SITE CONTENT (CMS) ============
CREATE TABLE site_content (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  section VARCHAR(50) NOT NULL, -- 'hero', 'how_it_works', 'faq', 'footer', 'disclaimer', 'contacts'
  key VARCHAR(100) NOT NULL,
  value TEXT NOT NULL,
  value_type VARCHAR(20) DEFAULT 'text', -- 'text', 'html', 'json', 'url'
  locale VARCHAR(10) DEFAULT 'ru',
  
  updated_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(section, key, locale)
);

CREATE INDEX idx_content_section ON site_content(section);

-- ============ AUDIT LOGS ============
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  
  user_id UUID REFERENCES users(id),
  user_email VARCHAR(255),
  ip_address INET,
  user_agent TEXT,
  
  action VARCHAR(100) NOT NULL, -- 'LOGIN', 'CONFIG_CHANGE', 'KYC_APPROVE', 'MANUAL_ADJUST', etc.
  resource_type VARCHAR(50), -- 'user', 'plan', 'transaction', 'config'
  resource_id VARCHAR(255),
  
  old_value JSONB,
  new_value JSONB,
  details TEXT,
  
  severity VARCHAR(20) DEFAULT 'info' CHECK (severity IN ('info', 'warning', 'critical')),
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_created ON audit_logs(created_at);
CREATE INDEX idx_audit_severity ON audit_logs(severity);

-- ============ SESSIONS ============
CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  token_hash VARCHAR(255) NOT NULL,
  ip_address INET,
  user_agent TEXT,
  
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT TRUE,
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_token ON sessions(token_hash);

-- ============ PLATFORM SETTINGS ============
CREATE TABLE platform_settings (
  key VARCHAR(100) PRIMARY KEY,
  value JSONB NOT NULL,
  description TEXT,
  updated_by UUID REFERENCES users(id),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============ SEED DATA ============

-- Default plans
INSERT INTO plans (id, title, subtitle, description, min_deposit, risk_profile, volatility_min, volatility_max, rebalance_frequency, is_highlighted, sort_order) VALUES
  ('cadet', 'Cadet', 'Консервативный', 'Стабильный рост с минимальным риском. Идеален для долгосрочного накопления.', 100, 'conservative', 3.00, 8.00, 'weekly', FALSE, 1),
  ('explorer', 'Explorer', 'Сбалансированный', 'Баланс между доходностью и риском. Диверсифицированный портфель.', 1000, 'balanced', 8.00, 18.00, 'daily', TRUE, 2),
  ('commander', 'Commander', 'Агрессивный', 'Максимальная доходность при высоком риске. Для опытных инвесторов.', 10000, 'aggressive', 18.00, 40.00, 'realtime', FALSE, 3);

-- Default assets
INSERT INTO assets (symbol, name, asset_class, target_allocation, risk_level, strategy, color) VALUES
  ('BTC', 'Bitcoin', 'crypto', 22, 'high', 'HODL + DCA', '#FF7A3D'),
  ('ETH', 'Ethereum', 'crypto', 15, 'high', 'Staking + DCA', '#7C3AED'),
  ('SPY', 'S&P 500 ETF', 'index', 18, 'medium', 'Buy & Hold', '#0A4BFF'),
  ('TBOND', 'US Treasury Bonds', 'bonds', 12, 'low', 'Laddering', '#38E6FF'),
  ('GOLD', 'Gold', 'commodity', 8, 'low', 'Strategic Reserve', '#FBBF24'),
  ('DEFI', 'DeFi Index', 'defi', 7, 'high', 'Yield Farming', '#9B66F2'),
  ('USDC', 'USDC Staking', 'stablecoin', 10, 'low', 'Lending', '#34D399'),
  ('REIT', 'Real Estate REIT', 'real_estate', 5, 'medium', 'Passive Income', '#FF9E6D'),
  ('AITECH', 'AI Tech Fund', 'fund', 3, 'medium', 'Growth', '#3D6FFF');

-- Default demo user
INSERT INTO users (email, password_hash, role, kyc_status, plan_id, first_name, last_name) VALUES
  ('user@example.com', '$2a$12$LJ3m9z6MkR7GnY5hP0Lxe.9H8vP1Z5K0Y4W7.QTv8N6R2Dq5Kja', 'user', 'demo', 'explorer', 'Demo', 'User');

-- Default admin (password: changeme1234)
INSERT INTO users (email, password_hash, role, kyc_status, two_factor_enabled) VALUES
  ('admin@quantaris.finance', '$2a$12$LJ3m9z6MkR7GnY5hP0Lxe.9H8vP1Z5K0Y4W7.QTv8N6R2Dq5Kja', 'admin', 'verified', FALSE);

-- Platform settings
INSERT INTO platform_settings (key, value, description) VALUES
  ('maintenance_mode', 'false', 'Enable maintenance mode'),
  ('read_only_mode', 'false', 'Enable read-only mode'),
  ('max_withdrawal_auto', '50000', 'Max auto-approved withdrawal in USD'),
  ('rebalance_threshold', '5', 'Rebalance trigger threshold in %'),
  ('displayed_aum', '"$2.4B"', 'AUM displayed on landing page'),
  ('displayed_users', '"47K+"', 'User count displayed on landing page');

-- Create wallet for demo user
INSERT INTO wallets (user_id, demo_balance_usd, is_demo)
  SELECT id, 50000, TRUE FROM users WHERE email = 'user@example.com';

-- ============ TRIGGERS ============

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_column();
CREATE TRIGGER update_wallets_modtime BEFORE UPDATE ON wallets FOR EACH ROW EXECUTE FUNCTION update_modified_column();
CREATE TRIGGER update_transactions_modtime BEFORE UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION update_modified_column();
CREATE TRIGGER update_plans_modtime BEFORE UPDATE ON plans FOR EACH ROW EXECUTE FUNCTION update_modified_column();
CREATE TRIGGER update_assets_modtime BEFORE UPDATE ON assets FOR EACH ROW EXECUTE FUNCTION update_modified_column();
CREATE TRIGGER update_content_modtime BEFORE UPDATE ON site_content FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- Generate TX ID
CREATE OR REPLACE FUNCTION generate_tx_id()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.tx_id IS NULL THEN
    NEW.tx_id = 'TX-' || LPAD(nextval('tx_id_seq')::TEXT, 6, '0');
  END IF;
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE SEQUENCE IF NOT EXISTS tx_id_seq START WITH 10000;
CREATE TRIGGER set_tx_id BEFORE INSERT ON transactions FOR EACH ROW EXECUTE FUNCTION generate_tx_id();