import { query } from '../../db';
import { alerter } from '../../services/alerter';

function extractFieldTypes(obj: any, prefix = ''): Record<string, string> {
  const fields: Record<string, string> = {};
  if (!obj || typeof obj !== 'object') return fields;

  for (const [key, value] of Object.entries(obj)) {
    const fullKey = prefix ? `${prefix}.${key}` : key;
    const type = value === null ? 'null' : Array.isArray(value) ? 'array' : typeof value;
    fields[fullKey] = type;

    if (type === 'object' && value !== null) {
      Object.assign(fields, extractFieldTypes(value, fullKey));
    }
  }
  return fields;
}

function computeFingerprint(sample: any[]): Record<string, string> {
  const merged: Record<string, Set<string>> = {};

  for (const item of sample) {
    const fields = extractFieldTypes(item);
    for (const [key, type] of Object.entries(fields)) {
      if (!merged[key]) merged[key] = new Set();
      merged[key].add(type);
    }
  }

  const result: Record<string, string> = {};
  for (const [key, types] of Object.entries(merged)) {
    result[key] = [...types].sort().join('|');
  }
  return result;
}

async function checkSource(source: string, tableName: string, limit: number = 100): Promise<void> {
  try {
    // Get recent samples from the table
    let sampleQuery: string;
    if (tableName.startsWith('"')) {
      sampleQuery = `SELECT row_to_json(t) as data FROM ${tableName} t ORDER BY "updatedAt" DESC LIMIT $1`;
    } else {
      sampleQuery = `SELECT row_to_json(t) as data FROM ${tableName} t ORDER BY created_at DESC LIMIT $1`;
    }

    const sampleResult = await query(sampleQuery, [limit]);
    if (sampleResult.rows.length === 0) return;

    const samples = sampleResult.rows.map(r => r.data);
    const currentFingerprint = computeFingerprint(samples);

    // Get stored fingerprint
    const storedResult = await query(
      `SELECT * FROM sc_schema_fingerprints WHERE source = $1 AND is_current = TRUE LIMIT 1`,
      [source]
    );

    if (storedResult.rows.length === 0) {
      // First time — store fingerprint
      await query(
        `INSERT INTO sc_schema_fingerprints (source, fingerprint) VALUES ($1, $2)`,
        [source, JSON.stringify(currentFingerprint)]
      );
      await alerter.logEvent(
        'schema_baseline', 'info', 'schema-guard',
        `Baseline schema fingerprint created for ${source}`,
        { field_count: Object.keys(currentFingerprint).length }
      );
      return;
    }

    const storedFingerprint = storedResult.rows[0].fingerprint as Record<string, string>;

    // Compare fingerprints
    const currentKeys = new Set(Object.keys(currentFingerprint));
    const storedKeys = new Set(Object.keys(storedFingerprint));

    // New fields
    const newFields = [...currentKeys].filter(k => !storedKeys.has(k));
    // Missing fields
    const missingFields = [...storedKeys].filter(k => !currentKeys.has(k));
    // Type changes
    const typeChanges: string[] = [];
    for (const key of currentKeys) {
      if (storedKeys.has(key) && currentFingerprint[key] !== storedFingerprint[key]) {
        typeChanges.push(`${key}: ${storedFingerprint[key]} → ${currentFingerprint[key]}`);
      }
    }

    if (newFields.length > 0) {
      await alerter.logEvent(
        'schema_new_fields', 'info', 'schema-guard',
        `New fields detected in ${source}: ${newFields.join(', ')}`,
        { source, new_fields: newFields }
      );
    }

    if (missingFields.length > 0) {
      await alerter.logEvent(
        'schema_missing_fields', 'warn', 'schema-guard',
        `Missing fields in ${source}: ${missingFields.join(', ')}`,
        { source, missing_fields: missingFields }
      );
    }

    if (typeChanges.length > 0) {
      // Separate real type changes from nullable/sampling variations.
      // A "real" type change = both old and new have non-null types with ZERO overlap.
      // e.g. string → number is real. number → null|number is sampling noise.
      // null|number → null is noise (sample didn't include non-null values).
      const realChanges: string[] = [];
      const noiseChanges: string[] = [];

      for (const change of typeChanges) {
        const match = change.match(/^(.+): (.+) → (.+)$/);
        if (match) {
          const oldBase = new Set(match[2].split('|').filter(t => t !== 'null'));
          const newBase = new Set(match[3].split('|').filter(t => t !== 'null'));

          // Only critical if both sides have concrete types AND they share nothing
          const hasOverlap = [...oldBase].some(t => newBase.has(t));
          if (oldBase.size > 0 && newBase.size > 0 && !hasOverlap) {
            realChanges.push(change);
          } else {
            noiseChanges.push(change);
          }
        } else {
          noiseChanges.push(change);
        }
      }

      if (noiseChanges.length > 0) {
        await alerter.logEvent(
          'schema_nullable_change', 'info', 'schema-guard',
          `Nullable variations in ${source} (${noiseChanges.length} fields)`,
          { source, changes: noiseChanges }
        );
      }

      if (realChanges.length > 0) {
        await alerter.logEvent(
          'schema_type_change', 'critical', 'schema-guard',
          `Type changes in ${source}: ${realChanges.join('; ')}`,
          { source, type_changes: realChanges }
        );
      }
    }

    // Update stored fingerprint
    if (newFields.length > 0 || missingFields.length > 0 || typeChanges.length > 0) {
      await query(
        `UPDATE sc_schema_fingerprints SET is_current = FALSE WHERE source = $1 AND is_current = TRUE`,
        [source]
      );
      await query(
        `INSERT INTO sc_schema_fingerprints (source, fingerprint) VALUES ($1, $2)`,
        [source, JSON.stringify(currentFingerprint)]
      );
    } else {
      // Just update last_seen
      await query(
        `UPDATE sc_schema_fingerprints SET last_seen = NOW() WHERE id = $1`,
        [storedResult.rows[0].id]
      );
    }
  } catch (err) {
    console.error(`[schema-guard] Error checking ${source}:`, err);
  }
}

export async function runSchemaGuard(): Promise<void> {
  // Check key data tables
  await checkSource('SportsGame', '"SportsGame"', 50);
  await checkSource('PlayerPropLine', '"PlayerPropLine"', 50);
  await checkSource('PlayerInjury', '"PlayerInjury"', 50);
}