import { getDb } from "./db.ts";
import fs from "fs";
import path from "path";

/**
 * Execute training database migrations
 * Run this once to set up all training tables
 */
export async function setupTrainingDatabase() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    console.log("Starting training database setup...");

    // Read migration SQL file
    const migrationPath = path.join(process.cwd(), "migrations", "training_tables.sql");
    const migrationSQL = fs.readFileSync(migrationPath, "utf-8");

    // Split by semicolon and execute each statement
    const statements = migrationSQL
      .split(";")
      .map((stmt) => stmt.trim())
      .filter((stmt) => stmt.length > 0);

    for (const statement of statements) {
      try {
        console.log(`Executing: ${statement.substring(0, 50)}...`);
        await db.execute(statement);
      } catch (error) {
        // Some statements might fail if tables already exist, which is OK
        if (error instanceof Error && error.message.includes("already exists")) {
          console.log(`Table already exists, skipping...`);
        } else {
          console.error(`Error executing statement: ${error}`);
        }
      }
    }

    console.log("✓ Training database setup completed successfully!");
    return { success: true, message: "Training tables created" };
  } catch (error) {
    console.error("Database setup failed:", error);
    throw error;
  }
}

/**
 * Verify training database tables exist
 */
export async function verifyTrainingTables() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    const requiredTables = [
      "training_videos",
      "quiz_questions",
      "admin_certifications",
      "certification_issues",
      "admin_training_progress",
      "admin_badges",
      "admin_badge_awards",
      "quiz_attempts",
      "training_analytics",
    ];

    const results = [];

    for (const table of requiredTables) {
      try {
        const result = await db.execute(`SELECT 1 FROM ${table} LIMIT 1`);
        results.push({ table, exists: true });
        console.log(`✓ Table ${table} exists`);
      } catch (error) {
        results.push({ table, exists: false });
        console.log(`✗ Table ${table} not found`);
      }
    }

    const allExist = results.every((r) => r.exists);
    return { success: allExist, tables: results };
  } catch (error) {
    console.error("Table verification failed:", error);
    throw error;
  }
}

/**
 * Create sample training data for testing
 */
export async function createSampleTrainingData() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    console.log("Creating sample training data...");

    // Create sample certification
    const certId = `cert-${Date.now()}`;
    await db.execute(
      `INSERT INTO admin_certifications (id, title, description, level, required_modules, passing_score, expiration_days, created_by, is_active)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        certId,
        "CoinKrazy Admin Certification",
        "Complete training for casino administrators",
        "advanced",
        JSON.stringify(["game-management", "support", "fraud-detection", "analytics"]),
        70,
        365,
        "admin",
        true,
      ]
    );

    // Create sample video
    const videoId = `video-${Date.now()}`;
    await db.execute(
      `INSERT INTO training_videos (id, title, description, video_url, module, duration, created_by, is_active)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        videoId,
        "Getting Started with Admin Panel",
        "Introduction to the CoinKrazy admin panel features",
        "https://example.com/videos/intro.mp4",
        "game-management",
        300,
        "admin",
        true,
      ]
    );

    // Create sample questions
    const questionId = `q-${Date.now()}`;
    await db.execute(
      `INSERT INTO quiz_questions (id, text, type, module, options, correct_answer, points, created_by, is_active)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        questionId,
        "What is the primary purpose of the fraud detection module?",
        "multiple-choice",
        "fraud-detection",
        JSON.stringify([
          "To identify suspicious player behavior",
          "To manage game settings",
          "To track revenue",
          "To send notifications",
        ]),
        "To identify suspicious player behavior",
        10,
        "admin",
        true,
      ]
    );

    console.log("✓ Sample training data created successfully!");
    return {
      success: true,
      created: { certification: certId, video: videoId, question: questionId },
    };
  } catch (error) {
    console.error("Sample data creation failed:", error);
    throw error;
  }
}

/**
 * Initialize training system
 * Call this on application startup
 */
export async function initializeTrainingSystem() {
  try {
    console.log("Initializing training system...");

    // Verify tables exist
    const verification = await verifyTrainingTables();

    if (!verification.success) {
      console.log("Training tables not found, running setup...");
      await setupTrainingDatabase();
    }

    console.log("✓ Training system initialized successfully!");
    return { success: true };
  } catch (error) {
    console.error("Training system initialization failed:", error);
    return { success: false, error };
  }
}