-- 用户表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    google_id VARCHAR(255) UNIQUE,
    firebase_uid VARCHAR(255) UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 交易日志表
CREATE TABLE trade_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    trade_time TIMESTAMPTZ NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    direction trade_direction NOT NULL,
    position_size VARCHAR(100) NOT NULL,
    entry_reason TEXT,
    exit_reason TEXT,
    trade_result TEXT NOT NULL,
    mindset_state TEXT NOT NULL,
    lessons_learned TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订阅表
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    plan_id plan_id NOT NULL,
    status subscription_status DEFAULT 'inactive',
    start_date TIMESTAMPTZ NOT NULL,
    end_date TIMESTAMPTZ NOT NULL,
    payment_provider payment_provider NOT NULL,
    payment_id VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- AI分析报告表
CREATE TABLE daily_analyses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    summary TEXT NOT NULL,
    strengths TEXT NOT NULL,
    weaknesses TEXT NOT NULL,
    emotional_impact TEXT NOT NULL,
    improvement_suggestions TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, date)
);

-- 交易方向
CREATE TYPE trade_direction AS ENUM ('Buy', 'Sell', 'Long', 'Short', 'Close');

-- 订阅状态
CREATE TYPE subscription_status AS ENUM ('active', 'inactive', 'cancelled', 'trialing');

-- 订阅计划
CREATE TYPE plan_id AS ENUM ('monthly', 'quarterly', 'semi_annually', 'annually');

-- 支付提供商
CREATE TYPE payment_provider AS ENUM ('wechat_pay', 'alipay', 'stripe');

-- 性能优化索引
CREATE INDEX idx_trade_logs_user_id ON trade_logs(user_id);
CREATE INDEX idx_trade_logs_trade_time ON trade_logs(trade_time);
CREATE INDEX idx_trade_logs_symbol ON trade_logs(symbol);
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_daily_analyses_user_id ON daily_analyses(user_id);
CREATE INDEX idx_daily_analyses_date ON daily_analyses(date);

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 创建数据库
CREATE DATABASE trade_insight_ai;

-- 设置时区
SET timezone = 'UTC';