import os
import sqlite3
from datetime import datetime, timedelta
from passlib.context import CryptContext

# 加密工具
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def init_database():
    """初始化数据库，确保所有必要的表和数据存在"""
    # 连接数据库
    db_path = 'aiplatform.db'
    
    # 如果数据库文件已存在，先删除
    if os.path.exists(db_path):
        print(f"删除现有数据库文件: {db_path}")
        os.remove(db_path)
    
    print(f"创建新数据库: {db_path}")
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建表（如果不存在）
    create_tables(cursor)
    
    # 初始化基础数据
    init_data(cursor)
    
    # 提交更改
    conn.commit()
    conn.close()
    
    print("数据库初始化完成")

def create_tables(cursor):
    """创建所有必要的表"""
    print("创建数据库表...")
    
    # 用户表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        email TEXT UNIQUE,
        hashed_password TEXT,
        is_active INTEGER DEFAULT 1,
        is_admin INTEGER DEFAULT 0,
        balance REAL DEFAULT 0
    )
    ''')
    
    # 应用表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS apps (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        desc TEXT,
        price REAL,
        status TEXT DEFAULT '上架',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # 订单表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        app_id INTEGER,
        type TEXT DEFAULT '应用调用',
        amount REAL,
        description TEXT,
        status TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users (id),
        FOREIGN KEY (app_id) REFERENCES apps (id)
    )
    ''')
    
    # 历史记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        type TEXT,
        amount REAL,
        desc TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users (id)
    )
    ''')
    
    print("数据库表创建完成")

def init_data(cursor):
    """初始化基础数据"""
    print("初始化基础数据...")
    
    # 1. 创建admin用户
    print("创建admin用户...")
    hashed_password = pwd_context.hash("admin123")
    cursor.execute('''
    INSERT INTO users (username, hashed_password, is_admin, is_active, balance)
    VALUES (?, ?, ?, ?, ?)
    ''', ("admin", hashed_password, 1, 1, 100))
    
    # 2. 创建Twitter应用
    print("创建Twitter应用...")
    apps = [
        ("Twitter推文摘要", "输入Twitter用户名，获取最近推文摘要", 12.0, "上架", datetime.utcnow()),
        ("Twitter自动发推", "输入Twitter用户名，获取摘要并发送到Twitter", 15.0, "上架", datetime.utcnow())
    ]
    
    for app in apps:
        cursor.execute('''
        INSERT INTO apps (name, desc, price, status, created_at)
        VALUES (?, ?, ?, ?, ?)
        ''', app)
    
    # 获取新创建的应用ID
    cursor.execute("SELECT id FROM apps WHERE name = 'Twitter推文摘要'")
    summary_app_id = cursor.fetchone()[0]
    
    cursor.execute("SELECT id FROM apps WHERE name = 'Twitter自动发推'")
    post_app_id = cursor.fetchone()[0]
    
    # 3. 创建示例订单
    print("创建示例订单...")
    # 获取admin用户ID
    cursor.execute("SELECT id FROM users WHERE username = 'admin'")
    admin_id = cursor.fetchone()[0]
    
    days_ago_5 = datetime.utcnow() - timedelta(days=5)
    days_ago_2 = datetime.utcnow() - timedelta(days=2)
    
    orders = [
        (admin_id, summary_app_id, "应用调用", 12.0, "使用Twitter推文摘要服务", "已完成", days_ago_5),
        (admin_id, post_app_id, "应用调用", 15.0, "使用Twitter自动发推服务", "已完成", days_ago_2)
    ]
    
    for order in orders:
        cursor.execute('''
        INSERT INTO orders (user_id, app_id, type, amount, description, status, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', order)
    
    # 4. 创建历史记录
    print("创建历史记录...")
    days_ago_10 = datetime.utcnow() - timedelta(days=10)
    
    histories = [
        (admin_id, "recharge", 100.0, "账户充值", days_ago_10),
        (admin_id, "consume", -12.0, "使用Twitter推文摘要服务", days_ago_5),
        (admin_id, "consume", -15.0, "使用Twitter自动发推服务", days_ago_2)
    ]
    
    for history in histories:
        cursor.execute('''
        INSERT INTO history (user_id, type, amount, desc, created_at)
        VALUES (?, ?, ?, ?, ?)
        ''', history)

# 如果想要手动运行初始化
if __name__ == "__main__":
    print("开始初始化数据库...")
    init_database()
    print("数据库初始化完成!")