#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3
import os
import uuid
from datetime import datetime

def init_database():
    """初始化SQLite数据库"""
    db_path = '/Users/moshui/Documents/survey/data/survey.db'
    
    # 如果数据库已存在，先删除
    if os.path.exists(db_path):
        os.remove(db_path)
        print(f"已删除旧数据库: {db_path}")
    
    # 创建数据库连接
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建学员信息表
    cursor.execute('''
        CREATE TABLE students (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            school TEXT NOT NULL,
            grade TEXT NOT NULL,
            phone TEXT NOT NULL,
            selected_tag TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建答题会话表
    cursor.execute('''
        CREATE TABLE quiz_sessions (
            id TEXT PRIMARY KEY,
            student_id TEXT NOT NULL,
            questions_config TEXT NOT NULL,
            status TEXT DEFAULT 'created',
            started_at TIMESTAMP,
            completed_at TIMESTAMP,
            total_score INTEGER DEFAULT 0,
            max_score INTEGER DEFAULT 100,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students (id)
        )
    ''')
    
    # 创建答题记录表 - 修改为JSON格式存储，添加用户信息字段
    cursor.execute('''
        CREATE TABLE quiz_answers (
            id TEXT PRIMARY KEY,
            session_id TEXT NOT NULL UNIQUE,
            student_name TEXT NOT NULL,
            student_school TEXT NOT NULL,
            student_grade TEXT NOT NULL,
            selected_tag TEXT,
            answers_data TEXT NOT NULL,
            total_score INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (session_id) REFERENCES quiz_sessions (id)
        )
    ''')
    
    # 提交更改
    conn.commit()
    
    print("数据库表创建成功:")
    print("- students: 学员信息表")
    print("- quiz_sessions: 答题会话表")
    print("- quiz_answers: 答题记录表 (JSON格式)")
    
    # 创建索引
    cursor.execute('CREATE INDEX idx_students_name ON students(name)')
    cursor.execute('CREATE INDEX idx_sessions_student ON quiz_sessions(student_id)')
    cursor.execute('CREATE INDEX idx_answers_session ON quiz_answers(session_id)')
    
    conn.commit()
    conn.close()
    
    print("索引创建完成")
    print(f"数据库初始化完成: {db_path}")

if __name__ == "__main__":
    init_database()