database/database.js

const sqlite3 = require('sqlite3').verbose();
const { createClient } = require('@libsql/client');
const path = require('path');

// 检查是否使用 Turso（云端数据库）
const USE_TURSO = !!(process.env.TURSO_DATABASE_URL && process.env.TURSO_AUTH_TOKEN);
const DB_PATH = process.env.DB_PATH || './database/fitjourney.db';

let db = null;
let tursoClient = null;

const initDatabase = () => {
  return new Promise((resolve, reject) => {
    if (USE_TURSO) {
      // 使用 Turso 云端数据库
      try {
        tursoClient = createClient({
          url: process.env.TURSO_DATABASE_URL,
          authToken: process.env.TURSO_AUTH_TOKEN,
        });
        console.log('✅ Connected to Turso database');
        
        // 创建表
        createTables()
          .then(() => resolve())
          .catch(reject);
      } catch (error) {
        console.error('❌ Error connecting to Turso:', error.message);
        reject(error);
      }
    } else {
      // 使用本地 SQLite 数据库
      const dbDir = path.dirname(DB_PATH);
      const fs = require('fs');
      if (!fs.existsSync(dbDir)) {
        fs.mkdirSync(dbDir, { recursive: true });
      }

      db = new sqlite3.Database(DB_PATH, (err) => {
        if (err) {
          console.error('❌ Error opening database:', err.message);
          reject(err);
          return;
        }
        console.log('✅ Connected to local SQLite database');
        
        // 创建用户表
        createTables()
          .then(() => resolve())
          .catch(reject);
      });
    }
  });
};

const createTables = () => {
  return new Promise((resolve, reject) => {
    const createUsersTable = `
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        is_verified INTEGER DEFAULT 0,
        verification_code TEXT,
        verification_expires INTEGER,
        reset_token TEXT,
        reset_expires INTEGER,
        is_activated INTEGER DEFAULT 0,
        activation_expires INTEGER,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        updated_at INTEGER DEFAULT (strftime('%s', 'now'))
      )
    `;

    const createTokensTable = `
      CREATE TABLE IF NOT EXISTS user_tokens (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        token_hash TEXT NOT NULL,
        expires_at INTEGER NOT NULL,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
      )
    `;

    const createActivationCodesTable = `
      CREATE TABLE IF NOT EXISTS activation_codes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL,
        days INTEGER NOT NULL DEFAULT 30,
        is_used INTEGER DEFAULT 0,
        used_by INTEGER,
        used_at INTEGER,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        FOREIGN KEY (used_by) REFERENCES users (id) ON DELETE SET NULL
      )
    `;

    // 教练表
    const createCoachesTable = `
      CREATE TABLE IF NOT EXISTS coaches (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        password_plain TEXT,
        name TEXT,
        phone TEXT,
        is_active INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT (datetime('now', 'localtime')),
        updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
      )
    `;

    // 教练邀请码表
    const createInviteCodesTable = `
      CREATE TABLE IF NOT EXISTS invite_codes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        coach_id INTEGER NOT NULL,
        code TEXT UNIQUE NOT NULL,
        is_active INTEGER DEFAULT 1,
        created_at INTEGER DEFAULT (strftime('%s', 'now')),
        FOREIGN KEY (coach_id) REFERENCES coaches (id) ON DELETE CASCADE
      )
    `;

    // 教练-学员关系表
    const createTrainerStudentsTable = `
      CREATE TABLE IF NOT EXISTS trainer_students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        coach_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        student_name TEXT,
        joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (coach_id) REFERENCES coaches (id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        UNIQUE(coach_id, user_id)
      )
    `;

    // 用户数据表（用于教练查看学员数据）
    const createUserDataTable = `
      CREATE TABLE IF NOT EXISTS user_data (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER UNIQUE NOT NULL,
        diet_data TEXT,
        sleep_data TEXT,
        exercise_data TEXT,
        status_data TEXT,
        personal_data TEXT,
        updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
      )
    `;

    // 迁移脚本：为现有的users表添加新列
    const migrateUsersTable = () => {
      return new Promise((migrateResolve, migrateReject) => {
        // 检查是否存在is_activated列
        db.all("PRAGMA table_info(users)", (err, columns) => {
          if (err) {
            console.error('❌ Error checking table info:', err.message);
            migrateReject(err);
            return;
          }

          const hasIsActivated = columns.some(col => col.name === 'is_activated');
          const hasActivationExpires = columns.some(col => col.name === 'activation_expires');

          let migrationCount = 0;
          let totalMigrations = 0;

          const checkMigrationComplete = () => {
            migrationCount++;
            if (migrationCount === totalMigrations) {
              migrateResolve();
            }
          };

          // 添加is_activated列
          if (!hasIsActivated) {
            totalMigrations++;
            db.run("ALTER TABLE users ADD COLUMN is_activated INTEGER DEFAULT 0", (err) => {
              if (err) {
                console.error('❌ Error adding is_activated column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added is_activated column to users table');
              checkMigrationComplete();
            });
          }

          // 添加activation_expires列
          if (!hasActivationExpires) {
            totalMigrations++;
            db.run("ALTER TABLE users ADD COLUMN activation_expires INTEGER", (err) => {
              if (err) {
                console.error('❌ Error adding activation_expires column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added activation_expires column to users table');
              checkMigrationComplete();
            });
          }

          // 如果没有需要迁移的列，直接完成
          if (totalMigrations === 0) {
            console.log('✅ Users table migration not needed');
            migrateResolve();
          }
        });
      });
    };

    // 迁移脚本：为教练表添加password_plain列
    const migrateCoachesTable = () => {
      return new Promise((migrateResolve, migrateReject) => {
        db.all("PRAGMA table_info(coaches)", (err, columns) => {
          if (err) {
            console.error('❌ Error checking coaches table info:', err.message);
            migrateReject(err);
            return;
          }

          const hasPasswordPlain = columns.some(col => col.name === 'password_plain');

          if (!hasPasswordPlain) {
            db.run("ALTER TABLE coaches ADD COLUMN password_plain TEXT", (err) => {
              if (err) {
                console.error('❌ Error adding password_plain column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added password_plain column to coaches table');
              migrateResolve();
            });
          } else {
            console.log('✅ Coaches table migration not needed');
            migrateResolve();
          }
        });
      });
    };

    // 迁移脚本：为trainer_students表添加student_name列
    const migrateTrainerStudentsTable = () => {
      return new Promise((migrateResolve, migrateReject) => {
        db.all("PRAGMA table_info(trainer_students)", (err, columns) => {
          if (err) {
            console.error('❌ Error checking trainer_students table info:', err.message);
            migrateReject(err);
            return;
          }

          const hasStudentName = columns.some(col => col.name === 'student_name');

          if (!hasStudentName) {
            db.run("ALTER TABLE trainer_students ADD COLUMN student_name TEXT", (err) => {
              if (err) {
                console.error('❌ Error adding student_name column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added student_name column to trainer_students table');
              migrateResolve();
            });
          } else {
            console.log('✅ Trainer_students table migration not needed');
            migrateResolve();
          }
        });
      });
    };

    const migrateUserDataTable = () => {
      return new Promise((migrateResolve, migrateReject) => {
        db.all("PRAGMA table_info(user_data)", (err, columns) => {
          if (err) {
            console.error('❌ Error checking user_data table info:', err.message);
            migrateReject(err);
            return;
          }

          const hasStatusData = columns.some(col => col.name === 'status_data');
          const hasPersonalData = columns.some(col => col.name === 'personal_data');
          
          let migrationCount = 0;
          let totalMigrations = 0;
          
          const checkMigrationComplete = () => {
            migrationCount++;
            if (migrationCount === totalMigrations) {
              migrateResolve();
            }
          };
          
          if (!hasStatusData) {
            totalMigrations++;
          }
          if (!hasPersonalData) {
            totalMigrations++;
          }
          
          if (totalMigrations === 0) {
            console.log('✅ User_data table migration not needed');
            migrateResolve();
            return;
          }
          
          if (!hasStatusData) {
            db.run("ALTER TABLE user_data ADD COLUMN status_data TEXT", (err) => {
              if (err) {
                console.error('❌ Error adding status_data column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added status_data column to user_data table');
              checkMigrationComplete();
            });
          }
          
          if (!hasPersonalData) {
            db.run("ALTER TABLE user_data ADD COLUMN personal_data TEXT", (err) => {
              if (err) {
                console.error('❌ Error adding personal_data column:', err.message);
                migrateReject(err);
                return;
              }
              console.log('✅ Added personal_data column to user_data table');
              checkMigrationComplete();
            });
          }
        });
      });
    };

    // 迁移 user_data 表的时间戳从UTC转换为本地时间（东八区）
    const migrateUserDataTimestamp = () => {
      return new Promise((migrateResolve, migrateReject) => {
        // 更新所有现有记录的 updated_at，将UTC时间转换为东八区时间 (+8小时)
        db.run(
          `UPDATE user_data 
           SET updated_at = datetime(updated_at, '+8 hours') 
           WHERE updated_at IS NOT NULL 
           AND updated_at NOT LIKE '%+%'`,
          (err) => {
            if (err) {
              console.error('❌ Error migrating user_data timestamps:', err.message);
              migrateReject(err);
              return;
            }
            console.log('✅ Migrated user_data timestamps to local time (UTC+8)');
            migrateResolve();
          }
        );
      });
    };

    // 修正 coaches 表的时间戳（如果之前加了 12 小时，现在改为只加 8 小时）
    const fixCoachesTimezone = () => {
      return new Promise((migrateResolve, migrateReject) => {
        // 检查是否有 DATETIME 格式的数据（已经被迁移过的）
        db.get(
          `SELECT created_at FROM coaches WHERE created_at > '2025-01-01' LIMIT 1`,
          (err, row) => {
            if (err) {
              console.error('❌ Error checking coaches timestamps:', err.message);
              migrateReject(err);
              return;
            }

            if (row && row.created_at) {
              // 如果时间看起来比正常时间快（即多加了4小时），则修正
              // 这里我们减去4小时，将 UTC+12 改为 UTC+8
              db.run(
                `UPDATE coaches 
                 SET created_at = datetime(created_at, '-4 hours'),
                     updated_at = datetime(updated_at, '-4 hours')
                 WHERE created_at > datetime('now', 'localtime', '+1 hour')`,
                (err) => {
                  if (err) {
                    console.error('❌ Error fixing coaches timezone:', err.message);
                    migrateReject(err);
                    return;
                  }
                  console.log('✅ Fixed coaches timezone (UTC+12 -> UTC+8)');
                  migrateResolve();
                }
              );
            } else {
              console.log('✅ Coaches timezone fix not needed');
              migrateResolve();
            }
          }
        );
      });
    };

    const migrateCoachesTimestamp = () => {
      return new Promise((migrateResolve, migrateReject) => {
        // 检查coaches表的created_at字段类型
        db.all("PRAGMA table_info(coaches)", (err, columns) => {
          if (err) {
            console.error('❌ Error checking coaches table info:', err.message);
            migrateReject(err);
            return;
          }

          const createdAtCol = columns.find(col => col.name === 'created_at');
          
          // 如果created_at是INTEGER类型，需要转换
          if (createdAtCol && createdAtCol.type === 'INTEGER') {
            console.log('🔄 Migrating coaches table timestamp fields from INTEGER to DATETIME...');
            
            // SQLite不支持直接修改列类型，需要重建表
            db.serialize(() => {
              // 1. 创建新表
              db.run(`
                CREATE TABLE coaches_new (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  username TEXT UNIQUE NOT NULL,
                  password_hash TEXT NOT NULL,
                  password_plain TEXT,
                  name TEXT,
                  phone TEXT,
                  is_active INTEGER DEFAULT 1,
                  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
              `, (err) => {
                if (err) {
                  console.error('❌ Error creating new coaches table:', err.message);
                  migrateReject(err);
                  return;
                }

                // 2. 复制数据，将INTEGER时间戳转换为DATETIME（服务器已在东八区，使用localtime即可）
                db.run(`
                  INSERT INTO coaches_new (id, username, password_hash, password_plain, name, phone, is_active, created_at, updated_at)
                  SELECT id, username, password_hash, password_plain, name, phone, is_active, 
                         datetime(created_at, 'unixepoch', 'localtime'),
                         datetime(updated_at, 'unixepoch', 'localtime')
                  FROM coaches
                `, (err) => {
                  if (err) {
                    console.error('❌ Error copying data to new coaches table:', err.message);
                    migrateReject(err);
                    return;
                  }

                  // 3. 删除旧表
                  db.run('DROP TABLE coaches', (err) => {
                    if (err) {
                      console.error('❌ Error dropping old coaches table:', err.message);
                      migrateReject(err);
                      return;
                    }

                    // 4. 重命名新表
                    db.run('ALTER TABLE coaches_new RENAME TO coaches', (err) => {
                      if (err) {
                        console.error('❌ Error renaming new coaches table:', err.message);
                        migrateReject(err);
                        return;
                      }
                      console.log('✅ Coaches table timestamp migration completed');
                      migrateResolve();
                    });
                  });
                });
              });
            });
          } else {
            console.log('✅ Coaches table timestamp migration not needed');
            migrateResolve();
          }
        });
      });
    };

    // 使用统一的方式执行SQL（支持 Turso 和 SQLite）
    const executeSQL = async (sql, errorMsg, successMsg) => {
      try {
        await runQuery(sql);
        console.log(successMsg);
      } catch (err) {
        console.error(errorMsg, err.message);
        throw err;
      }
    };

    // 按顺序创建所有表
    (async () => {
      try {
        await executeSQL(createUsersTable, '❌ Error creating users table:', '✅ Users table ready');
        await executeSQL(createTokensTable, '❌ Error creating tokens table:', '✅ Tokens table ready');
        await executeSQL(createActivationCodesTable, '❌ Error creating activation codes table:', '✅ Activation codes table ready');
        await executeSQL(createCoachesTable, '❌ Error creating coaches table:', '✅ Coaches table ready');
        await executeSQL(createInviteCodesTable, '❌ Error creating invite codes table:', '✅ Invite codes table ready');
        await executeSQL(createTrainerStudentsTable, '❌ Error creating trainer_students table:', '✅ Trainer students table ready');
        await executeSQL(createUserDataTable, '❌ Error creating user_data table:', '✅ User data table ready');

        // 只在本地 SQLite 时执行迁移（Turso 不需要）
        if (!USE_TURSO) {
          await migrateUsersTable();
          await migrateCoachesTable();
          await migrateTrainerStudentsTable();
          await migrateUserDataTable();
          await migrateUserDataTimestamp();
          await migrateCoachesTimestamp();
          await fixCoachesTimezone();
        }

        console.log('✅ Database migration completed');
        resolve();
      } catch (error) {
        reject(error);
      }
    })();
  });
};

const getDatabase = () => {
  if (USE_TURSO) {
    if (!tursoClient) {
      throw new Error('Turso database not initialized. Call initDatabase() first.');
    }
    return tursoClient;
  } else {
    if (!db) {
      throw new Error('Database not initialized. Call initDatabase() first.');
    }
    return db;
  }
};

// 辅助函数：执行SQL查询（支持 Turso 和 SQLite）
const runQuery = async (sql, params = []) => {
  if (USE_TURSO) {
    try {
      const result = await tursoClient.execute({ sql, args: params });
      return { 
        id: result.lastInsertRowid ? Number(result.lastInsertRowid) : null,
        changes: result.rowsAffected 
      };
    } catch (error) {
      throw error;
    }
  } else {
    return new Promise((resolve, reject) => {
      db.run(sql, params, function(err) {
        if (err) {
          reject(err);
        } else {
          resolve({ id: this.lastID, changes: this.changes });
        }
      });
    });
  }
};

const getQuery = async (sql, params = []) => {
  if (USE_TURSO) {
    try {
      const result = await tursoClient.execute({ sql, args: params });
      return result.rows[0] || undefined;
    } catch (error) {
      throw error;
    }
  } else {
    return new Promise((resolve, reject) => {
      db.get(sql, params, (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  }
};

const allQuery = async (sql, params = []) => {
  if (USE_TURSO) {
    try {
      const result = await tursoClient.execute({ sql, args: params });
      return result.rows || [];
    } catch (error) {
      throw error;
    }
  } else {
    return new Promise((resolve, reject) => {
      db.all(sql, params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }
};

module.exports = {
  initDatabase,
  getDatabase,
  runQuery,
  getQuery,
  allQuery
};