CustomerID | Name    | Orders
-----------|---------|-------------------
1          | John    | (ORD-1, ORD-2, ORD-3)

CustomerID | Name    | OrderID
-----------|---------|--------
1          | John    | ORD-1
1          | John    | ORD-2
1          | John    | ORD-3

OrderID | CustomerID | CustomerName | OrderDate | ProductID | ProductName | Quantity
--------|------------|--------------|-----------|-----------|-------------|----------
1       | 100        | John         | 2024-01-01| P-1       | AAPL        | 100
1       | 100        | John         | 2024-01-01| P-2       | GOOGL       | 50

Order Table:
OrderID | CustomerID | OrderDate
--------|------------|----------
1       | 100        | 2024-01-01

Customer Table:
CustomerID | CustomerName
-----------|--------------
100        | John

OrderItem Table:
OrderID | ProductID | Quantity
--------|-----------|----------
1       | P-1       | 100
1       | P-2       | 50

Product Table:
ProductID | ProductName
----------|-------------
P-1       | AAPL
P-2       | GOOGL

AccountID | CustomerID | CustomerName | Balance
----------|------------|--------------|--------
A-1       | 100        | John         | 5000

Account Table:
AccountID | CustomerID | Balance
----------|------------|--------
A-1       | 100        | 5000

Customer Table:
CustomerID | CustomerName
-----------|--------------
100        | John

StockPrice:
Symbol | Date       | Price | Exchange | ExchangeAddress
-------|------------|-------|----------|----------------
AAPL   | 2024-01-01 | 150   | NASDAQ   | NYC
AAPL   | 2024-01-02 | 152   | NASDAQ   | NYC

StockPrice:
Symbol | Date       | Price | Exchange
-------|------------|-------|----------
AAPL   | 2024-01-01 | 150   | NASDAQ

Exchange:
Exchange | ExchangeAddress
---------|-----------------
NASDAQ   | NYC

-- Customer table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    tax_id VARCHAR(50),  -- Encrypted SSN/TIN
    phone VARCHAR(20),
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50) DEFAULT 'US',
    kyc_status VARCHAR(20) DEFAULT 'PENDING',
    kyc_verified_date TIMESTAMP,
    risk_score INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

-- Account table
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    account_number VARCHAR(50) UNIQUE NOT NULL,
    account_type VARCHAR(20) CHECK (account_type IN ('CHECKING', 'SAVINGS', 'TRADING', 'MARGIN')),
    currency VARCHAR(3) DEFAULT 'USD',
    balance DECIMAL(18,2) DEFAULT 0.00,
    available_balance DECIMAL(18,2) DEFAULT 0.00,
    held_balance DECIMAL(18,2) DEFAULT 0.00,
    margin_used DECIMAL(18,2) DEFAULT 0.00,
    margin_limit DECIMAL(18,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP,
    last_activity TIMESTAMP
);

-- Account holder relationship (for joint accounts)
CREATE TABLE account_holders (
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    relationship_type VARCHAR(20) DEFAULT 'PRIMARY',
    ownership_percentage DECIMAL(5,2) DEFAULT 100.00,
    PRIMARY KEY (account_id, customer_id)
);

-- Trading instruments (securities)
CREATE TABLE instruments (
    instrument_id SERIAL PRIMARY KEY,
    symbol VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    instrument_type VARCHAR(20) CHECK (instrument_type IN ('STOCK', 'ETF', 'BOND', 'FUTURE', 'OPTION', 'FOREX', 'CRYPTO')),
    sector VARCHAR(50),
    industry VARCHAR(50),
    country VARCHAR(50),
    currency VARCHAR(3) DEFAULT 'USD',
    exchange VARCHAR(20),
    isin VARCHAR(12),  -- International Securities Identification Number
    cusip VARCHAR(9),  -- CUSIP number
    market_cap DECIMAL(18,2),
    shares_outstanding BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    instrument_id INTEGER NOT NULL REFERENCES instruments(instrument_id),
    side VARCHAR(4) CHECK (side IN ('BUY', 'SELL')),
    quantity INTEGER NOT NULL,
    filled_quantity INTEGER DEFAULT 0,
    price DECIMAL(18,4),
    stop_price DECIMAL(18,4),
    order_type VARCHAR(20) CHECK (order_type IN ('MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT', 'TRAILING_STOP')),
    time_in_force VARCHAR(20) CHECK (time_in_force IN ('DAY', 'GTC', 'FOK', 'IOC', 'GTD')),
    status VARCHAR(20) DEFAULT 'PENDING',
    rejection_reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP,
    executed_at TIMESTAMP,
    cancelled_at TIMESTAMP,
    expire_date TIMESTAMP,
    parent_order_id INTEGER REFERENCES orders(order_id),
    client_order_id VARCHAR(50)
);

-- Trade executions
CREATE TABLE executions (
    execution_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    execution_quantity INTEGER NOT NULL,
    execution_price DECIMAL(18,4) NOT NULL,
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    venue VARCHAR(50),
    execution_id_external VARCHAR(50),
    settlement_date DATE
);

-- Position table
CREATE TABLE positions (
    position_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    instrument_id INTEGER NOT NULL REFERENCES instruments(instrument_id),
    quantity INTEGER DEFAULT 0,
    average_cost DECIMAL(18,4) DEFAULT 0.00,
    current_price DECIMAL(18,4),
    unrealized_pnl DECIMAL(18,4),
    realized_pnl DECIMAL(18,4),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(account_id, instrument_id)
);

-- Transaction log
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    transaction_type VARCHAR(30) CHECK (transaction_type IN (
        'DEPOSIT', 'WITHDRAWAL', 'TRANSFER_IN', 'TRANSFER_OUT',
        'TRADE_BUY', 'TRADE_SELL', 'FEE', 'INTEREST', 'DIVIDEND',
        'REFUND', 'CHARGEBACK', 'ADJUSTMENT'
    )),
    amount DECIMAL(18,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    description VARCHAR(255),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    settlement_date DATE,
    reference_id VARCHAR(50),
    status VARCHAR(20) DEFAULT 'PENDING',
    related_transaction_id INTEGER REFERENCES transactions(transaction_id)
);

-- Payment methods
CREATE TABLE payment_methods (
    payment_method_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    method_type VARCHAR(20) CHECK (method_type IN ('CREDIT_CARD', 'DEBIT_CARD', 'BANK_ACCOUNT', 'CRYPTO_WALLET', 'DIGITAL_WALLET')),
    provider VARCHAR(50),
    tokenized_data VARCHAR(255),  -- Tokenized payment data
    last_four VARCHAR(4),
    expiry_month INTEGER,
    expiry_year INTEGER,
    is_default BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Price history (optimized for time-series)
CREATE TABLE price_history (
    price_id SERIAL PRIMARY KEY,
    instrument_id INTEGER NOT NULL REFERENCES instruments(instrument_id),
    price DECIMAL(18,4) NOT NULL,
    volume INTEGER,
    bid DECIMAL(18,4),
    ask DECIMAL(18,4),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Partition for performance
CREATE TABLE price_history_partitioned (
    instrument_id INTEGER NOT NULL,
    price DECIMAL(18,4) NOT NULL,
    volume INTEGER,
    bid DECIMAL(18,4),
    ask DECIMAL(18,4),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (timestamp);

-- Create monthly partitions
CREATE TABLE price_history_2024_01 PARTITION OF price_history_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
    
CREATE TABLE price_history_2024_02 PARTITION OF price_history_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Daily summary table
CREATE TABLE daily_summary (
    summary_id SERIAL PRIMARY KEY,
    instrument_id INTEGER NOT NULL REFERENCES instruments(instrument_id),
    open DECIMAL(18,4),
    high DECIMAL(18,4),
    low DECIMAL(18,4),
    close DECIMAL(18,4),
    volume INTEGER,
    turnover DECIMAL(18,2),
    trade_date DATE NOT NULL,
    UNIQUE(instrument_id, trade_date)
);

-- Risk limits
CREATE TABLE risk_limits (
    risk_limit_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    limit_type VARCHAR(30) CHECK (limit_type IN (
        'DAILY_LOSS', 'POSITION_SIZE', 'CONCENTRATION', 'LEVERAGE',
        'DRAWDOWN', 'VAR_LIMIT', 'NOTIONAL_LIMIT'
    )),
    limit_value DECIMAL(18,2) NOT NULL,
    current_value DECIMAL(18,2) DEFAULT 0.00,
    breach_count INTEGER DEFAULT 0,
    last_breach TIMESTAMP,
    is_enforced BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Risk events log
CREATE TABLE risk_events (
    risk_event_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    event_type VARCHAR(30) CHECK (event_type IN (
        'LIMIT_BREACH', 'MARGIN_CALL', 'LIQUIDATION', 'STOP_LOSS',
        'VAR_VIOLATION', 'CONCENTRATION_VIOLATION'
    )),
    severity VARCHAR(20) CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    description TEXT,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP,
    resolution_notes TEXT
);

-- Audit log (tamper-proof)
CREATE TABLE audit_log (
    audit_id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(20) CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values JSONB,
    new_values JSONB,
    user_id INTEGER REFERENCES customers(customer_id),
    ip_address INET,
    user_agent TEXT,
    hmac_signature VARCHAR(64),  -- For tamper-proof verification
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Compliance records
CREATE TABLE compliance_records (
    compliance_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    record_type VARCHAR(30) CHECK (record_type IN (
        'KYC', 'AML_CHECK', 'PEP_SCREENING', 'SANCTIONS_CHECK',
        'FATCA', 'CRS', 'REGULATORY_REPORT'
    )),
    status VARCHAR(20) CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'FLAGGED')),
    result JSONB,
    reviewer_id INTEGER REFERENCES customers(customer_id),
    reviewed_at TIMESTAMP,
    expiration_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Data dictionary query (PostgreSQL)
SELECT 
    t.table_name,
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.column_default,
    pgd.description
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_name = c.table_name
LEFT JOIN pg_catalog.pg_statio_user_tables st ON st.relname = t.table_name
LEFT JOIN pg_catalog.pg_description pgd ON pgd.objoid = st.relid
WHERE t.table_schema = 'public'
ORDER BY t.table_name, c.ordinal_position;

## Table: orders

**Purpose:** Stores all trading orders placed by customers.

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| order_id | SERIAL | NO | | Primary key |
| account_id | INTEGER | NO | | Foreign key to accounts |
| instrument_id | INTEGER | NO | | Foreign key to instruments |
| side | VARCHAR(4) | NO | | BUY or SELL |
| quantity | INTEGER | NO | | Number of shares/contracts |
| filled_quantity | INTEGER | NO | 0 | Quantity executed |
| price | DECIMAL(18,4) | YES | | Limit price |
| order_type | VARCHAR(20) | NO | | MARKET, LIMIT, STOP |
| status | VARCHAR(20) | NO | PENDING | Order lifecycle status |
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | When order was created |

**Indexes:**
- idx_orders_account_id (account_id)
- idx_orders_status (status)
- idx_orders_symbol (instrument_id)

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import os

def create_database():
    """Create the financial database."""
    conn = psycopg2.connect(
        host=os.environ.get('DB_HOST', 'localhost'),
        user=os.environ.get('DB_USER', 'postgres'),
        password=os.environ.get('DB_PASSWORD', 'postgres')
    )
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cursor = conn.cursor()
    
    # Create database
    cursor.execute("CREATE DATABASE fintech_db")
    print("Database created successfully")
    
    cursor.close()
    conn.close()

def initialize_tables():
    """Initialize all tables."""
    conn = psycopg2.connect(
        host=os.environ.get('DB_HOST', 'localhost'),
        database='fintech_db',
        user=os.environ.get('DB_USER', 'postgres'),
        password=os.environ.get('DB_PASSWORD', 'postgres')
    )
    cursor = conn.cursor()
    
    # Read and execute schema.sql
    with open('schema.sql', 'r') as f:
        cursor.execute(f.read())
    
    conn.commit()
    cursor.close()
    conn.close()
    print("Tables initialized successfully")

# Usage
# create_database()
# initialize_tables()

from psycopg2 import pool
import os

class DatabasePool:
    """Connection pool for database connections."""
    
    _instance = None
    _pool = None
    
    def __new__(cls):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance
    
    def initialize(self, min_conn=5, max_conn=20):
        """Initialize the connection pool."""
        self._pool = psycopg2.pool.SimpleConnectionPool(
            min_conn,
            max_conn,
            host=os.environ.get('DB_HOST', 'localhost'),
            database=os.environ.get('DB_NAME', 'fintech_db'),
            user=os.environ.get('DB_USER', 'postgres'),
            password=os.environ.get('DB_PASSWORD', 'postgres')
        )
    
    def get_connection(self):
        """Get a connection from the pool."""
        return self._pool.getconn()
    
    def return_connection(self, conn):
        """Return a connection to the pool."""
        self._pool.putconn(conn)
    
    def close_all(self):
        """Close all connections in the pool."""
        self._pool.closeall()

# Usage
db_pool = DatabasePool()
db_pool.initialize()

class DatabaseMigration:
    """Manages database schema migrations."""
    
    def __init__(self, connection):
        self.conn = connection
        self.cursor = connection.cursor()
        self._init_migration_table()
    
    def _init_migration_table(self):
        """Create the migration history table."""
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS migrations (
                migration_id SERIAL PRIMARY KEY,
                version VARCHAR(50) NOT NULL UNIQUE,
                description TEXT,
                applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        self.conn.commit()
    
    def apply_migration(self, version, description, up_sql, down_sql=None):
        """Apply a migration."""
        self.cursor.execute("SELECT version FROM migrations WHERE version = %s", (version,))
        if self.cursor.fetchone():
            print(f"Migration {version} already applied")
            return
        
        try:
            self.cursor.execute(up_sql)
            self.cursor.execute(
                "INSERT INTO migrations (version, description) VALUES (%s, %s)",
                (version, description)
            )
            self.conn.commit()
            print(f"Migration {version} applied successfully")
        except Exception as e:
            self.conn.rollback()
            print(f"Migration {version} failed: {e}")
            raise
    
    def rollback_migration(self, version):
        """Rollback a migration."""
        self.cursor.execute("SELECT description FROM migrations WHERE version = %s", (version,))
        result = self.cursor.fetchone()
        if not result:
            print(f"Migration {version} not found")
            return
        
        # Implementation would store down_sql for rollback
        print(f"Rolling back migration {version}")
        # self.cursor.execute(down_sql)
        self.cursor.execute("DELETE FROM migrations WHERE version = %s", (version,))
        self.conn.commit()
    
    def get_current_version(self):
        """Get the current migration version."""
        self.cursor.execute("SELECT version FROM migrations ORDER BY applied_at DESC LIMIT 1")
        result = self.cursor.fetchone()
        return result[0] if result else None