import sqlite3

def create_database():
    conn = sqlite3.connect('buff_prices.db')
    cursor = conn.cursor()
    
    # 商品主表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            buff_id INTEGER NOT NULL UNIQUE,
            name VARCHAR(500) NOT NULL,
            market_hash_name VARCHAR(255) NOT NULL UNIQUE,
            type VARCHAR(100),
            rarity VARCHAR(50),
            icon_url TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 平台价格表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS platform_prices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL,
            platform_code VARCHAR(20) NOT NULL,
            min_price DECIMAL(10,2),
            recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
            UNIQUE(product_id, platform_code)
        )
    ''')
    
    # 创建索引
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_products_market_hash ON products(market_hash_name)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_platform_prices_product ON platform_prices(product_id)')
    
    conn.commit()
    conn.close()
    print("数据库创建成功")

if __name__ == "__main__":
    create_database()