src/services/sqlValidator.ts

import initSqlJs, { Database } from 'sql.js';

interface SqlValidationResult {
  isCorrect: boolean;
  feedback: string;
  suggestions: string;
}

interface TestCase {
  schema?: string;
  seed_data?: string;
  expected_rows?: any[][];
  expected_columns?: string[];
  expected_tables?: Record<string, string[]>;
  validate_schema?: boolean;
  validate_view?: boolean;
  view_name?: string;
}

function normalizeValue(v: any): any {
  if (v === null || v === undefined) return null;
  if (typeof v === 'number') return v;
  return String(v);
}

function rowsMatch(actual: any[][], expected: any[][]): boolean {
  if (actual.length !== expected.length) return false;
  for (let i = 0; i < actual.length; i++) {
    const aRow = actual[i];
    const eRow = expected[i];
    if (!aRow || !eRow || aRow.length !== eRow.length) return false;
    for (let j = 0; j < aRow.length; j++) {
      const a = normalizeValue(aRow[j]);
      const b = normalizeValue(eRow[j]);
      if (a !== b) return false;
    }
  }
  return true;
}

function columnsMatch(actual: string[], expected: string[]): boolean {
  const norm = (s: string) => s.toLowerCase().trim();
  const normActual = actual.map(norm).sort();
  const normExpected = expected.map(norm).sort();
  if (normActual.length !== normExpected.length) return false;
  return normActual.every((v, i) => v === normExpected[i]);
}

function getAllTablesInfo(db: Database): Record<string, string[]> {
  const tables: Record<string, string[]> = {};
  try {
    const res = db.exec("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");
    if (res.length > 0 && res[0]) {
      for (const row of res[0].values) {
        const name = row[0] as string;
        try {
          const cols = db.exec(`PRAGMA table_info("${name}")`);
          if (cols.length > 0 && cols[0]) {
            tables[name] = cols[0].values.map((c: any) => c[1] as string);
          }
        } catch {}
      }
    }
  } catch {}
  return tables;
}

function getViewDef(db: Database, viewName: string): string | null {
  try {
    const res = db.exec(`SELECT sql FROM sqlite_master WHERE type='view' AND name='${viewName}'`);
    const first = res[0];
    if (first && first.values.length > 0) {
      const row = first.values[0];
      return row ? (row[0] as string) : null;
    }
  } catch {}
  return null;
}

function findTableName(userQuery: string): string {
  if (/^INSERT\s+INTO\s+(\w+)/i.test(userQuery)) {
    return userQuery.match(/^INSERT\s+INTO\s+(\w+)/i)?.[1] || '';
  }
  if (/^UPDATE\s+(\w+)/i.test(userQuery)) {
    return userQuery.match(/^UPDATE\s+(\w+)/i)?.[1] || '';
  }
  if (/^DELETE\s+FROM\s+(\w+)/i.test(userQuery)) {
    return userQuery.match(/^DELETE\s+FROM\s+(\w+)/i)?.[1] || '';
  }
  if (/^ALTER\s+TABLE\s+(\w+)/i.test(userQuery)) {
    return userQuery.match(/^ALTER\s+TABLE\s+(\w+)/i)?.[1] || '';
  }
  return '';
}

function execStatements(db: Database, sql: string): void {
  const stmts = sql.split(';').filter(s => s.trim());
  for (const stmt of stmts) {
    const trimmed = stmt.trim();
    if (trimmed) {
      db.run(trimmed);
    }
  }
}

function formatRows(rows: any[][]): string {
  return rows.map(r => `[${r.map(v => v === null ? 'NULL' : JSON.stringify(v)).join(', ')}]`).join('\n');
}

export async function validateSqlChallenge(
  testCases: TestCase,
  userQuery: string
): Promise<SqlValidationResult> {
  if (!userQuery || !userQuery.trim()) {
    return { isCorrect: false, feedback: 'اكتب استعلام SQL أولاً', suggestions: '' };
  }

  try {
    const SQL = await initSqlJs();
    const trimmed = userQuery.trim().toUpperCase();

    // ═══ DDL challenges: CREATE TABLE ═══
    if (testCases.validate_schema && testCases.expected_tables) {
      const db = new SQL.Database();
      try {
        execStatements(db, userQuery);
      } catch (err: any) {
        db.close();
        return { isCorrect: false, feedback: `خطأ في SQL: ${err.message}`, suggestions: 'تأكد من صحة بناء الجملة SQL' };
      }

      const actual = getAllTablesInfo(db);
      db.close();

      for (const [tableName, expectedCols] of Object.entries(testCases.expected_tables)) {
        const actualCols = actual[tableName];
        if (!actualCols) {
          return {
            isCorrect: false,
            feedback: `الجدول "${tableName}" لم يتم إنشاؤه`,
            suggestions: 'تأكد من استخدام CREATE TABLE'
          };
        }
        const norm = (s: string) => s.toLowerCase().replace(/\s+/g, ' ').trim();
        const normActual = actualCols.map(norm).sort();
        const normExpected = expectedCols.map(norm).sort();
        if (normActual.length !== normExpected.length || !normActual.every((c, i) => c === normExpected[i])) {
          return {
            isCorrect: false,
            feedback: `أعمدة الجدول "${tableName}" غير صحيحة. المتوقع: ${expectedCols.join(', ')}`,
            suggestions: `تأكد من تضمين جميع الأعمدة: ${expectedCols.join(', ')}`
          };
        }
      }
      return { isCorrect: true, feedback: 'ممتاز! تم إنشاء الجدول بشكل صحيح', suggestions: '' };
    }

    // ═══ View challenges ═══
    if (testCases.validate_view && testCases.view_name) {
      const db = new SQL.Database();
      try {
        if (testCases.schema) execStatements(db, testCases.schema);
        if (testCases.seed_data) execStatements(db, testCases.seed_data);
        execStatements(db, userQuery);
      } catch (err: any) {
        db.close();
        return { isCorrect: false, feedback: `خطأ في SQL: ${err.message}`, suggestions: 'تأكد من صحة بناء الجملة SQL' };
      }

      const viewDef = getViewDef(db, testCases.view_name);
      if (!viewDef) {
        db.close();
        return {
          isCorrect: false,
          feedback: `لم يتم إنشاء العرض "${testCases.view_name}"`,
          suggestions: 'تأكد من استخدام CREATE VIEW'
        };
      }

      let columns: string[] = [];
      let rows: any[][] = [];
      try {
        const res = db.exec(`SELECT * FROM "${testCases.view_name}"`);
        if (res.length > 0 && res[0]) {
          columns = res[0].columns;
          rows = res[0].values;
        }
      } catch (err: any) {
        db.close();
        return { isCorrect: false, feedback: `خطأ في استعلام العرض: ${err.message}`, suggestions: '' };
      }
      db.close();

      if (testCases.expected_columns && !columnsMatch(columns, testCases.expected_columns)) {
        return {
          isCorrect: false,
          feedback: `الأعمدة غير صحيحة. المتوقع: ${testCases.expected_columns.join(', ')}`,
          suggestions: ''
        };
      }
      if (testCases.expected_rows && !rowsMatch(rows, testCases.expected_rows)) {
        return {
          isCorrect: false,
          feedback: `النتائج غير صحيحة.\nالمتوقع:\n${formatRows(testCases.expected_rows)}\nحصلت:\n${formatRows(rows)}`,
          suggestions: 'تحقق من شروط التصفية والحسابات'
        };
      }
      return { isCorrect: true, feedback: 'ممتاز! تم إنشاء العرض بشكل صحيح', suggestions: '' };
    }

    // ═══ Standard challenges with expected_rows ═══
    if (testCases.expected_rows) {
      const db = new SQL.Database();
      try {
        if (testCases.schema) execStatements(db, testCases.schema);
        if (testCases.seed_data) execStatements(db, testCases.seed_data);
        execStatements(db, userQuery);
      } catch (err: any) {
        db.close();
        return { isCorrect: false, feedback: `خطأ في SQL: ${err.message}`, suggestions: 'تأكد من صحة بناء الجملة SQL' };
      }

      const isDML = /^(INSERT|UPDATE|DELETE|ALTER)\s/.test(trimmed);
      let verifyQuery = userQuery;

      if (isDML) {
        const tableName = findTableName(userQuery);
        if (tableName) {
          verifyQuery = `SELECT * FROM "${tableName}"`;
        }
      }

      let columns: string[] = [];
      let rows: any[][] = [];
      try {
        const res = db.exec(verifyQuery);
        if (res.length > 0 && res[0]) {
          columns = res[0].columns;
          rows = res[0].values;
        }
      } catch (err: any) {
        db.close();
        return { isCorrect: false, feedback: `خطأ في استعلام التحقق: ${err.message}`, suggestions: '' };
      }
      db.close();

      if (testCases.expected_columns && !columnsMatch(columns, testCases.expected_columns)) {
        return {
          isCorrect: false,
          feedback: `الأعمدة غير صحيحة. المتوقع: ${testCases.expected_columns.join(', ')}`,
          suggestions: ''
        };
      }

      if (!rowsMatch(rows, testCases.expected_rows)) {
        return {
          isCorrect: false,
          feedback: `النتائج لا تتطابق مع المطلوب.\nالمتوقع:\n${formatRows(testCases.expected_rows)}\nحصلت:\n${formatRows(rows)}`,
          suggestions: 'تحقق من منطق الاستعلام'
        };
      }

      return { isCorrect: true, feedback: 'صحيح! النتائج مطابقة للمطلوب', suggestions: '' };
    }

    // No test_cases — just check syntax
    const db = new SQL.Database();
    try {
      execStatements(db, userQuery);
      db.close();
      return { isCorrect: true, feedback: 'الاستعلام صحيح', suggestions: '' };
    } catch (err: any) {
      db.close();
      return { isCorrect: false, feedback: `خطأ في SQL: ${err.message}`, suggestions: '' };
    }
  } catch (err: any) {
    return { isCorrect: false, feedback: `خطأ في التحقق: ${err.message}`, suggestions: '' };
  }
}