-- Market data
CREATE TABLE market_symbols (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(255),
    exchange VARCHAR(20),
    sector VARCHAR(100),
    industry VARCHAR(100),
    market_cap BIGINT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE market_quotes (
    id SERIAL PRIMARY KEY,
    symbol_id INTEGER REFERENCES market_symbols(id),
    price DECIMAL(18,4),
    change_amount DECIMAL(18,4),
    change_percent DECIMAL(8,4),
    volume BIGINT,
    high_52w DECIMAL(18,4),
    low_52w DECIMAL(18,4),
    pe_ratio DECIMAL(10,2),
    dividend_yield DECIMAL(6,4),
    timestamp TIMESTAMP DEFAULT NOW()
);

-- User portfolios
CREATE TABLE portfolios (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    name VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE portfolio_holdings (
    id SERIAL PRIMARY KEY,
    portfolio_id INTEGER REFERENCES portfolios(id),
    symbol_id INTEGER REFERENCES market_symbols(id),
    shares DECIMAL(18,6),
    average_cost DECIMAL(18,4),
    added_at TIMESTAMP DEFAULT NOW()
);

-- Analysis
CREATE TABLE market_analysis (
    id SERIAL PRIMARY KEY,
    symbol_id INTEGER REFERENCES market_symbols(id),
    analysis_type VARCHAR(50), -- 'technical', 'fundamental', 'sentiment'
    data JSONB,
    score INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Alerts
CREATE TABLE price_alerts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    symbol_id INTEGER REFERENCES market_symbols(id),
    alert_type VARCHAR(20), -- 'above', 'below', 'change_percent'
    target_price DECIMAL(18,4),
    triggered BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

version: '3.8'
services:
  app:
    build: .
    container_name: market-app
    ports:
      - "127.0.0.1:12770:12770"
    environment:
      - DATABASE_URL=postgresql://market:market123@db:5432/market
      - REDIS_URL=redis://redis:6379/0
      - SESSION_SECRET=${SESSION_SECRET}
      - COOKIE_SECRET=${COOKIE_SECRET}
      - ALPHA_VANTAGE_API_KEY=${ALPHA_VANTAGE_API_KEY}
      - FINNHUB_API_KEY=${FINNHUB_API_KEY}
    depends_on:
      - db
      - redis

  db:
    image: postgres:15
    container_name: market-db
    environment:
      - POSTGRES_USER=market
      - POSTGRES_PASSWORD=market123
      - POSTGRES_DB=market
    volumes:
      - postgres_data:/var/lib/postgresql/data

  redis:
    image: redis:7
    container_name: market-redis

volumes:
  postgres_data: