/* =========================================================
   MySQL Migration Script — Create tables + import JSON data
   ========================================================= */

require("dotenv").config();
const mysql = require("mysql2/promise");
const fs = require("fs");
const path = require("path");

const DATA_DIR = path.join(__dirname, "data");

async function migrate() {
  console.log("🔄 Starting MySQL migration...\n");

  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || "localhost",
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || "root",
    password: process.env.DB_PASSWORD || "",
    database: process.env.DB_NAME || "zatoctlb_za_farm_kh",
    charset: "utf8mb4",
  });

  // ═══════════════════════════════════════════════════════════
  // CREATE TABLES
  // ═══════════════════════════════════════════════════════════

  console.log("📦 Creating tables...");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id VARCHAR(36) PRIMARY KEY,
      username VARCHAR(50) NOT NULL UNIQUE,
      email VARCHAR(254) NOT NULL UNIQUE,
      telegram VARCHAR(100) DEFAULT '',
      password VARCHAR(255) NOT NULL,
      balance DECIMAL(10, 3) DEFAULT 0.000,
      apiKey VARCHAR(64) NOT NULL UNIQUE,
      googleId VARCHAR(100) DEFAULT NULL,
      createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_email (email),
      INDEX idx_username (username),
      INDEX idx_apiKey (apiKey)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ users table");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS orders (
      id VARCHAR(36) PRIMARY KEY,
      userId VARCHAR(36) NOT NULL,
      username VARCHAR(50) NOT NULL,
      service VARCHAR(50) NOT NULL,
      qty INT NOT NULL DEFAULT 1,
      total DECIMAL(10, 3) NOT NULL,
      status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
      createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_userId (userId),
      INDEX idx_status (status),
      INDEX idx_createdAt (createdAt),
      FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ orders table");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS order_items (
      id INT AUTO_INCREMENT PRIMARY KEY,
      orderId VARCHAR(36) NOT NULL,
      mail VARCHAR(255) DEFAULT '',
      upstreamOrderId VARCHAR(100) DEFAULT '',
      otp VARCHAR(50) DEFAULT '',
      INDEX idx_orderId (orderId),
      FOREIGN KEY (orderId) REFERENCES orders(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ order_items table");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS payments (
      id VARCHAR(64) PRIMARY KEY,
      userId VARCHAR(36) NOT NULL,
      username VARCHAR(50) NOT NULL,
      amount DECIMAL(10, 3) NOT NULL,
      status ENUM('pending', 'completed', 'expired') DEFAULT 'pending',
      createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      paidAt DATETIME DEFAULT NULL,
      INDEX idx_userId (userId),
      INDEX idx_status (status),
      FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ payments table");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS services (
      id VARCHAR(50) PRIMARY KEY,
      title VARCHAR(100) NOT NULL,
      sub VARCHAR(255) DEFAULT '',
      stock INT DEFAULT 0,
      price DECIMAL(10, 3) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ services table");

  await connection.execute(`
    CREATE TABLE IF NOT EXISTS licenses (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id VARCHAR(36) DEFAULT NULL,
      username VARCHAR(100) DEFAULT '',
      license_key VARCHAR(100) NOT NULL UNIQUE,
      package_id VARCHAR(30) DEFAULT '',
      package_name VARCHAR(100) DEFAULT '',
      expiry_date DATE NOT NULL,
      is_active TINYINT(1) DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_user_id (user_id),
      INDEX idx_license_key (license_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  console.log("  ✅ licenses table");

  // ═══════════════════════════════════════════════════════════
  // IMPORT EXISTING JSON DATA
  // ═══════════════════════════════════════════════════════════

  console.log("\n📥 Importing existing JSON data...");

  // Import users
  const usersFile = path.join(DATA_DIR, "users.json");
  if (fs.existsSync(usersFile)) {
    const users = JSON.parse(fs.readFileSync(usersFile, "utf-8"));
    if (users.length > 0) {
      for (const u of users) {
        try {
          await connection.execute(
            `INSERT IGNORE INTO users (id, username, email, telegram, password, balance, apiKey, googleId, createdAt)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              u.id,
              u.username,
              u.email,
              u.telegram || "",
              u.password,
              u.balance || 0,
              u.apiKey,
              u.googleId || null,
              u.createdAt ? new Date(u.createdAt) : new Date(),
            ]
          );
        } catch (err) {
          console.log(`  ⚠️  Skip user ${u.email}: ${err.message}`);
        }
      }
      console.log(`  ✅ Imported ${users.length} users`);
    } else {
      console.log("  ℹ️  No users to import");
    }
  }

  // Import services
  const servicesFile = path.join(DATA_DIR, "services.json");
  if (fs.existsSync(servicesFile)) {
    const services = JSON.parse(fs.readFileSync(servicesFile, "utf-8"));
    for (const s of services) {
      await connection.execute(
        `INSERT INTO services (id, title, sub, stock, price) VALUES (?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE title=VALUES(title), sub=VALUES(sub), stock=VALUES(stock), price=VALUES(price)`,
        [s.id, s.title, s.sub || "", s.stock || 0, s.price]
      );
    }
    console.log(`  ✅ Imported ${services.length} services`);
  } else {
    // Insert default service
    await connection.execute(
      `INSERT IGNORE INTO services (id, title, sub, stock, price) VALUES (?, ?, ?, ?, ?)`,
      ["gmail_otp", "Gmail OTP", "Gmail OTP សម្រាប់ Facebook Verification (6 នាទី)", 500, 0.055]
    );
    console.log("  ✅ Inserted default service");
  }

  // Import orders
  const ordersFile = path.join(DATA_DIR, "orders.json");
  if (fs.existsSync(ordersFile)) {
    const orders = JSON.parse(fs.readFileSync(ordersFile, "utf-8"));
    if (orders.length > 0) {
      for (const o of orders) {
        try {
          await connection.execute(
            `INSERT IGNORE INTO orders (id, userId, username, service, qty, total, status, createdAt)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              o.id,
              o.userId,
              o.username || "",
              o.service,
              o.qty || 1,
              o.total,
              o.status || "pending",
              o.createdAt ? new Date(o.createdAt) : new Date(),
            ]
          );
          // Import order items
          if (o.items && o.items.length > 0) {
            for (const item of o.items) {
              await connection.execute(
                `INSERT INTO order_items (orderId, mail, upstreamOrderId, otp) VALUES (?, ?, ?, ?)`,
                [o.id, item.mail || "", item.upstreamOrderId || "", item.otp || ""]
              );
            }
          }
        } catch (err) {
          console.log(`  ⚠️  Skip order ${o.id}: ${err.message}`);
        }
      }
      console.log(`  ✅ Imported ${orders.length} orders`);
    } else {
      console.log("  ℹ️  No orders to import");
    }
  }

  // Import payments
  const paymentsFile = path.join(DATA_DIR, "payments.json");
  if (fs.existsSync(paymentsFile)) {
    const payments = JSON.parse(fs.readFileSync(paymentsFile, "utf-8"));
    if (payments.length > 0) {
      for (const p of payments) {
        try {
          await connection.execute(
            `INSERT IGNORE INTO payments (id, userId, username, amount, status, createdAt, paidAt)
             VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [
              p.id,
              p.userId,
              p.username || "",
              p.amount,
              p.status || "pending",
              p.createdAt ? new Date(p.createdAt) : new Date(),
              p.paidAt ? new Date(p.paidAt) : null,
            ]
          );
        } catch (err) {
          console.log(`  ⚠️  Skip payment ${p.id}: ${err.message}`);
        }
      }
      console.log(`  ✅ Imported ${payments.length} payments`);
    } else {
      console.log("  ℹ️  No payments to import");
    }
  }

  console.log("\n🎉 Migration completed successfully!");
  console.log("   អ្នកអាចលុប server/data/ folder បានហើយ (backup ទុកមុនសិន)\n");

  await connection.end();
  process.exit(0);
}

migrate().catch(err => {
  console.error("\n❌ Migration failed:", err.message);
  process.exit(1);
});
