/**
 * 数据库一键初始化脚本
 * 用法: pnpm db:init
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

const colors = {
  reset: '\x1b[0m',
  green: '\x1b[32m',
  yellow: '\x1b[33m',
  red: '\x1b[31m',
  cyan: '\x1b[36m',
  bold: '\x1b[1m',
};

function log(msg, color = '') {
  console.log(color ? `${color}${msg}${colors.reset}` : msg);
}

function loadEnv() {
  const envPaths = [
    path.resolve(__dirname, '../apps/server/.env.local'),
    path.resolve(__dirname, '../apps/server/.env'),
  ];

  let envFile = null;
  for (const p of envPaths) {
    if (fs.existsSync(p)) { envFile = p; break; }
  }

  if (!envFile) {
    log('错误: 未找到 .env.local 或 .env 文件', colors.red);
    process.exit(1);
  }

  const env = {};
  for (const line of fs.readFileSync(envFile, 'utf-8').split('\n')) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const i = trimmed.indexOf('=');
    if (i === -1) continue;
    env[trimmed.slice(0, i).trim()] = trimmed.slice(i + 1).trim();
  }

  return {
    host: env.DB_HOST || 'localhost',
    port: parseInt(env.DB_PORT || '3306', 10),
    user: env.DB_USERNAME || 'root',
    password: env.DB_PASSWORD || '',
    database: env.DB_DATABASE || 'rent_platform',
  };
}

async function runSqlFile(conn, filePath) {
  let raw = fs.readFileSync(filePath, 'utf-8');

  // 移除注释行
  raw = raw.replace(/--.*$/gm, '');
  // 移除 CREATE DATABASE 和 USE 语句（已在代码中处理）
  raw = raw.replace(/CREATE\s+DATABASE[\s\S]*?;/i, '');
  raw = raw.replace(/USE\s+\S+\s*;/i, '');

  // 按 ; 分割，过滤空语句
  const stmts = raw
    .split(';')
    .map((s) => s.trim())
    .filter((s) => s.length > 0);

  let ok = 0;
  let skip = 0;

  for (const stmt of stmts) {
    try {
      await conn.execute(stmt);
      ok++;
    } catch (err) {
      if (err.code === 'ER_TABLE_EXISTS_ERROR' || err.code === 'ER_DUP_ENTRY') {
        skip++;
      } else {
        log(`  SQL 错误 [${err.code}]: ${err.message.slice(0, 120)}`, colors.red);
      }
    }
  }

  return { ok, skip };
}

async function main() {
  const config = loadEnv();

  log('');
  log('==========================================', colors.cyan);
  log('  数据库一键初始化', colors.bold + colors.cyan);
  log('==========================================', colors.cyan);
  log(`  主机:   ${config.host}:${config.port}`);
  log(`  用户:   ${config.user}`);
  log(`  数据库: ${config.database}`);
  log('==========================================', colors.cyan);
  log('');

  // 1. 连接并创建数据库
  log('[1/3] 创建数据库...', colors.yellow);
  let conn;
  try {
    conn = await mysql.createConnection({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
    });
  } catch (err) {
    log(`  连接失败: ${err.message}`, colors.red);
    log('  请检查 apps/server/.env.local 中的数据库配置', colors.red);
    process.exit(1);
  }

  await conn.execute(
    `CREATE DATABASE IF NOT EXISTS \`${config.database}\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`
  );
  log(`  ✓ 数据库 ${config.database} 已就绪`, colors.green);
  await conn.end();

  // 2. 执行建表迁移
  log('');
  log('[2/3] 执行建表迁移...', colors.yellow);

  const dbConn = await mysql.createConnection({
    host: config.host,
    port: config.port,
    user: config.user,
    password: config.password,
    database: config.database,
  });

  const schemaFile = path.resolve(__dirname, 'migrations/001_init_schema.sql');
  if (!fs.existsSync(schemaFile)) {
    log(`  迁移文件不存在: ${schemaFile}`, colors.red);
    process.exit(1);
  }

  const schemaResult = await runSqlFile(dbConn, schemaFile);
  log(`  ✓ 表结构创建完成 (${schemaResult.ok} 成功, ${schemaResult.skip} 跳过已存在)`, colors.green);

  // 3. 导入种子数据
  log('');
  log('[3/3] 导入种子数据...', colors.yellow);

  const seedFile = path.resolve(__dirname, 'seeds/001_init_data.sql');
  if (fs.existsSync(seedFile)) {
    const seedResult = await runSqlFile(dbConn, seedFile);
    log(`  ✓ 种子数据导入完成 (${seedResult.ok} 成功, ${seedResult.skip} 跳过已存在)`, colors.green);
  } else {
    log('  - 无种子数据文件，跳过', colors.yellow);
  }

  await dbConn.end();

  log('');
  log('==========================================', colors.green);
  log('  ✓ 初始化完成!', colors.bold + colors.green);
  log('==========================================', colors.green);
  log('');
  log('下一步: pnpm dev:server', colors.cyan);
  log('');
}

main().catch((err) => {
  log(`\n初始化失败: ${err.message}`, colors.red);
  process.exit(1);
});