/**
 * Player Database Operations
 *
 * Functions for loading and saving player data to MySQL.
 * Matches Python server's database/db_operations.py functionality.
 */

import { spawnSync } from 'node:child_process';
import { dirname, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';
import { getPool, query, queryOne, execute, getConnection } from './pool.js';
import { Player } from '../models/Player.js';
import type { RowDataPacket } from 'mysql2/promise';
import { ensureEducationSetup } from '../services/education/education_manager.js';
import { ensureEventInstancesTable } from './eventInstances.js';

const __dirname = dirname(fileURLToPath(import.meta.url));
const LEGACY_SAVE_EXPORT_SCRIPT = resolve(__dirname, '../../scripts/export-legacy-save.py');

// ============================================================================
// Types
// ============================================================================

interface PlayerRow extends RowDataPacket {
  id: string;
  data: string;
  updated_at: Date;
  connection_status: string;
}

interface PlayerIdRow extends RowDataPacket {
  id: string;
}

interface LegacySaveRow extends RowDataPacket {
  id: string;
  json: string | null;
  pickle_data: Buffer | null;
  firstname: string | null;
  lastname: string | null;
  ageYears: number | null;
  ageDays: number | null;
  lastUpdated: Date | null;
}

// ============================================================================
// Save Operations
// ============================================================================

/**
 * Save a player to the database.
 * Uses REPLACE INTO for upsert behavior.
 *
 * NOTE: Conversations are stored in dedicated `conversations` table only,
 * not in the JSON blob. This prevents dual-storage sync issues.
 */
export async function savePlayer(player: Player): Promise<void> {
  const connection = await getConnection();
  try {
    // Get player data but exclude conversations - they're stored in dedicated table
    const playerJson = player.toJSON();
    delete playerJson.conversations;
    const playerData = JSON.stringify(playerJson);

    await connection.execute(
      `REPLACE INTO players (id, data, updated_at, connection_status)
       VALUES (?, ?, NOW(), ?)`,
      [player.userId, playerData, player.connection ?? 'disconnected']
    );

    console.log(`Saved player ${player.userId}`);
  } catch (error) {
    console.error(`Error saving player ${player.userId}:`, error);
    throw error;
  } finally {
    connection.release();
  }
}

/**
 * Async version matching Python's saveGameAsync
 */
export async function saveGameAsync(player: Player): Promise<void> {
  return savePlayer(player);
}

// ============================================================================
// Load Operations
// ============================================================================

/**
 * Load a player from the database by ID.
 *
 * NOTE: This does NOT load conversations - call loadPlayerConversations()
 * separately to load conversations from the dedicated table.
 */
export async function loadPlayer(playerId: string): Promise<Player | null> {
  try {
    const row = await queryOne<PlayerRow>(
      'SELECT id, data, updated_at, connection_status FROM players WHERE id = ?',
      [playerId]
    );

    if (!row) {
      return loadLegacyPlayer(playerId);
    }

    const playerData = JSON.parse(row.data);
    const player = new Player(playerData);

    // Set offline stats based on last update time.
    //
    // CRITICAL: preserve any welcome-back `digest` that was persisted in the
    // JSON blob (produced by LoopManager.processOfflineTime). Previously this
    // assignment replaced offlineStats wholesale, silently dropping a freshly
    // produced digest before reconnect could surface it — the root cause of the
    // digest never reaching a returning player. We recompute minutesOffline /
    // lastOnline from the authoritative DB updated_at, but carry the digest
    // through so sendOfflineDigest can deliver (and then clear) it once.
    if (row.updated_at) {
      const lastOnline = new Date(row.updated_at);
      const now = new Date();
      const minutesOffline = Math.floor((now.getTime() - lastOnline.getTime()) / 60000);
      const preservedDigest = player.offlineStats?.digest;

      player.offlineStats = {
        minutesOffline,
        lastOnline,
        ...(preservedDigest ? { digest: preservedDigest } : {}),
      };
    }

    // Migration: Ensure school-age characters have proper education set up
    if (player.c && player.status === 'playing') {
      const educationMigrated = ensureEducationSetup(player, player.c);
      if (educationMigrated) {
        player._needsSave = true;
      }
    }

    // If data migration occurred (e.g., avatar URLs fixed, education fixed), save immediately
    if (player._needsSave) {
      console.log(`Player ${playerId} needs data migration save`);
      await savePlayer(player);
      player._needsSave = false;
    }

    console.log(`Loaded player ${playerId}`);
    return player;
  } catch (error) {
    console.error(`Error loading player ${playerId}:`, error);
    return null;
  }
}

async function loadLegacyPlayer(playerId: string): Promise<Player | null> {
  const row = await queryOne<LegacySaveRow>(
    `SELECT id, json, pickle_data, firstname, lastname, ageYears, ageDays, lastUpdated
     FROM lifesim_savegames
     WHERE id = ?`,
    [playerId]
  );

  if (!row) {
    return null;
  }

  const legacyData = parseLegacyJson(row.json) ?? exportLegacyPickle(row.pickle_data);
  const playerData = normalizeLegacyPlayerData(row, legacyData);
  const player = new Player(playerData);

  if (row.lastUpdated) {
    const lastOnline = new Date(row.lastUpdated);
    const now = new Date();
    player.offlineStats = {
      minutesOffline: Math.floor((now.getTime() - lastOnline.getTime()) / 60000),
      lastOnline,
    };
  }

  console.log(`Migrating legacy Python save for player ${playerId}`);
  await savePlayer(player);
  player._needsSave = false;
  return player;
}

function parseLegacyJson(jsonText: string | null): Record<string, unknown> | null {
  if (!jsonText || jsonText.trim() === '' || jsonText.trim() === '{}') {
    return null;
  }

  try {
    const parsed = JSON.parse(jsonText) as unknown;
    return isRecord(parsed) ? parsed : null;
  } catch {
    return null;
  }
}

function exportLegacyPickle(pickleData: Buffer | null): Record<string, unknown> | null {
  if (!pickleData || pickleData.length === 0) {
    return null;
  }

  const result = spawnSync('python3', [LEGACY_SAVE_EXPORT_SCRIPT], {
    input: pickleData,
    maxBuffer: 50 * 1024 * 1024,
  });

  if (result.status !== 0) {
    console.error(
      `Failed to export legacy pickle: ${result.stderr?.toString('utf8').trim() || 'unknown error'}`
    );
    return null;
  }

  try {
    const parsed = JSON.parse(result.stdout.toString('utf8')) as unknown;
    return isRecord(parsed) ? parsed : null;
  } catch (error) {
    console.error(`Failed to parse exported legacy save JSON: ${error}`);
    return null;
  }
}

function normalizeLegacyPlayerData(row: LegacySaveRow, legacyData: Record<string, unknown> | null): ConstructorParameters<typeof Player>[0] {
  const source = legacyData ?? {};
  const character = normalizeLegacyPerson(
    (source.character ?? source.c) as Record<string, unknown> | undefined,
    {
      id: `char-${row.id}`,
      firstname: row.firstname ?? '',
      lastname: row.lastname ?? '',
      ageYears: row.ageYears ?? 0,
      ageDays: row.ageDays ?? 0,
      status: 'alive',
      sex: 'Male',
    }
  );

  const normalized = {
    ...source,
    userId: stringValue(source.userId) ?? stringValue(source.id) ?? row.id,
    character,
    status: stringValue(source.status) ?? 'playing',
    r: arrayValue(source.r).map((person, index) => normalizeLegacyPerson(person, {
      id: `legacy-person-${index}`,
      firstname: '',
      lastname: '',
      status: 'alive',
      sex: 'Male',
    })),
    relData: arrayValue(source.relData),
    messagingModifiers: arrayValue(source.messagingModifiers),
    conversations: arrayValue(source.conversations),
    events: setLikeArray(source.events),
    askedQuestions: setLikeArray(source.askedQuestions),
  };

  return normalized as unknown as ConstructorParameters<typeof Player>[0];
}

function normalizeLegacyPerson(value: unknown, fallback: Record<string, unknown>): Record<string, unknown> {
  const source = isRecord(value) ? value : {};
  return {
    ...fallback,
    ...source,
    id: stringValue(source.id) ?? stringValue(fallback.id) ?? '',
    firstname: stringValue(source.firstname) ?? stringValue(fallback.firstname) ?? '',
    lastname: stringValue(source.lastname) ?? stringValue(fallback.lastname) ?? '',
    sex: stringValue(source.sex) ?? stringValue(fallback.sex) ?? 'Male',
    status: stringValue(source.status) ?? stringValue(fallback.status) ?? 'alive',
  };
}

function setLikeArray(value: unknown): string[] {
  if (Array.isArray(value)) {
    return value.filter((item): item is string => typeof item === 'string');
  }

  if (isRecord(value) && Array.isArray(value.values)) {
    return value.values.filter((item): item is string => typeof item === 'string');
  }

  return [];
}

function arrayValue(value: unknown): Record<string, unknown>[] {
  return Array.isArray(value)
    ? value.filter(isRecord)
    : [];
}

function stringValue(value: unknown): string | null {
  return typeof value === 'string' && value.length > 0 ? value : null;
}

function isRecord(value: unknown): value is Record<string, unknown> {
  return !!value && typeof value === 'object' && !Array.isArray(value);
}

/**
 * Async version matching Python's loadGameAsync
 */
export async function loadGameAsync(playerId: string): Promise<Player | null> {
  return loadPlayer(playerId);
}

// ============================================================================
// Batch Operations (for offline iteration)
// ============================================================================

/**
 * Load all player IDs from the database.
 * Used by iterateGames to process offline players.
 */
export async function loadAllPlayerIds(): Promise<Array<{ playerId: string }>> {
  try {
    // Only iterate players disconnected within the last 24 hours
    // to avoid endlessly processing stale/abandoned games
    const rows = await query<PlayerIdRow[]>(
      'SELECT id FROM players WHERE connection_status = ? AND updated_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)',
      ['disconnected']
    );

    return rows.map(row => ({ playerId: row.id }));
  } catch (error) {
    console.error('Error loading player IDs:', error);
    return [];
  }
}

/**
 * Load all players (for offline iteration)
 * Matches Python's loadGames()
 */
export async function loadGames(): Promise<Array<{ playerId: string }>> {
  return loadAllPlayerIds();
}

/**
 * Update only the connection_status column for a player.
 * Used to immediately mark a player as connected when they establish a WebSocket session,
 * preventing the background offline-iteration job from picking them up.
 */
export async function updateConnectionStatus(playerId: string, status: string): Promise<void> {
  try {
    await execute(
      'UPDATE players SET connection_status = ?, updated_at = NOW() WHERE id = ?',
      [status, playerId]
    );
  } catch (error) {
    console.error(`Error updating connection status for ${playerId}:`, error);
  }
}

// ============================================================================
// Deletion
// ============================================================================

/**
 * Permanently delete a single player and all of its associated data.
 *
 * SCOPE SAFETY: every statement is keyed by the SINGLE `playerId` argument
 * (player row by `id`, all associated child rows by `player_id`). There is no
 * code path here that can delete more than the one account passed in. Callers
 * (the purge background job) are responsible for the past-due / opted-in guard;
 * this function is the narrow, single-id delete primitive.
 *
 * Deletes, in dependency-safe order:
 *  - conversation_messages (player_id)
 *  - conversations (player_id)
 *  - event_instances (player_id)
 *  - players (id)
 *
 * Idempotent: deleting an already-deleted (or never-existed) player is a no-op
 * that succeeds. Best-effort on child tables that may not exist in all envs.
 */
export async function deletePlayer(playerId: string): Promise<void> {
  if (!playerId) {
    throw new Error('deletePlayer requires a non-empty playerId');
  }

  const connection = await getConnection();
  try {
    // Child rows first. Each is scoped to this one player_id. event_instances /
    // conversation_messages may not exist in every environment, so tolerate a
    // missing-table error rather than aborting the whole deletion.
    const childDeletes: Array<{ sql: string }> = [
      { sql: 'DELETE FROM conversation_messages WHERE player_id = ?' },
      { sql: 'DELETE FROM conversations WHERE player_id = ?' },
      { sql: 'DELETE FROM event_instances WHERE player_id = ?' },
    ];

    for (const { sql } of childDeletes) {
      try {
        await connection.execute(sql, [playerId]);
      } catch (childError) {
        console.warn(
          `[deletePlayer] Non-fatal child cleanup failure for ${playerId} (${sql}):`,
          childError instanceof Error ? childError.message : childError
        );
      }
    }

    // Finally the player row itself (single id).
    await connection.execute('DELETE FROM players WHERE id = ?', [playerId]);

    console.log(`Deleted player ${playerId} and associated data`);
  } catch (error) {
    console.error(`Error deleting player ${playerId}:`, error);
    throw error;
  } finally {
    connection.release();
  }
}

// ============================================================================
// Conversation Persistence
// ============================================================================

interface ConversationRow extends RowDataPacket {
  id: string;
  player_id: string;
  character_id: string;
  messages: string;
  created_at: Date;
  updated_at: Date;
  unread: boolean;
}

/**
 * Save a conversation message to the database.
 */
export async function saveConversationMessage(
  playerId: string,
  characterId: string,
  conversationId: string,
  message: string,
  sender: 'player' | 'character'
): Promise<void> {
  const connection = await getConnection();
  try {
    await connection.execute(
      `INSERT INTO conversation_messages
       (conversation_id, player_id, character_id, message, sender, created_at)
       VALUES (?, ?, ?, ?, ?, NOW())`,
      [conversationId, playerId, characterId, message, sender]
    );
  } catch (error) {
    console.error('Error saving conversation message:', error);
    // Don't throw - conversation persistence is non-critical
  } finally {
    connection.release();
  }
}

/**
 * Save entire conversation to database.
 */
export async function saveConversation(
  playerId: string,
  characterId: string,
  conversationId: string,
  messages: unknown[],
  unread: boolean
): Promise<void> {
  const connection = await getConnection();
  try {
    await connection.execute(
      `REPLACE INTO conversations
       (id, player_id, character_id, messages, unread, updated_at)
       VALUES (?, ?, ?, ?, ?, NOW())`,
      [conversationId, playerId, characterId, JSON.stringify(messages), unread]
    );
  } catch (error) {
    console.error('Error saving conversation:', error);
  } finally {
    connection.release();
  }
}

/**
 * Load conversations for a player (raw rows from table).
 */
export async function loadConversations(playerId: string): Promise<ConversationRow[]> {
  try {
    const rows = await query<ConversationRow[]>(
      'SELECT * FROM conversations WHERE player_id = ? ORDER BY updated_at DESC',
      [playerId]
    );
    return rows;
  } catch (error) {
    console.error('Error loading conversations:', error);
    return [];
  }
}

/**
 * Load player conversations from the dedicated conversations table.
 *
 * This is the single source of truth for conversations. It also handles migration
 * from the JSON blob for any conversations that exist there but not in the table.
 *
 * @returns Array of conversation objects ready to assign to player.conversations
 */
export async function loadPlayerConversations(playerId: string): Promise<Array<{
  id: string;
  character: string;
  cType: string;
  conversation: unknown[];
  question: number;
  unread: boolean;
  summary?: string;
  summary_message_count?: number;
}>> {
  try {
    // Load from the dedicated conversations table
    const rows = await loadConversations(playerId);

    // Convert table rows to conversation objects
    const tableConversations: Array<{
      id: string;
      character: string;
      cType: string;
      conversation: unknown[];
      question: number;
      unread: boolean;
      summary?: string;
      summary_message_count?: number;
    }> = rows.map(row => {
      let messages: unknown[] = [];
      try {
        messages = JSON.parse(row.messages || '[]');
      } catch {
        console.warn(`Failed to parse messages for conversation ${row.id}`);
      }
      return {
        id: row.id,
        character: row.character_id,
        cType: 'casual', // Default type
        conversation: messages,
        question: 0,
        unread: row.unread ?? false,
      };
    });

    // Create a Set of conversation IDs and character IDs we already have from the table
    const tableConvoIds = new Set(tableConversations.map(c => c.id));
    const tableCharacterIds = new Set(tableConversations.map(c => c.character));

    // Check JSON blob for any conversations not in the table (migration)
    const playerRow = await queryOne<PlayerRow>(
      'SELECT data FROM players WHERE id = ?',
      [playerId]
    );

    if (!playerRow) {
      console.log(`Loaded ${tableConversations.length} conversations from table for player ${playerId}`);
      return tableConversations;
    }

    let playerData;
    try {
      playerData = JSON.parse(playerRow.data);
    } catch {
      console.warn(`Failed to parse player data for migration: ${playerId}`);
      return tableConversations;
    }

    const blobConversations = playerData.conversations || [];
    if (blobConversations.length === 0) {
      console.log(`Loaded ${tableConversations.length} conversations from table for player ${playerId}`);
      return tableConversations;
    }

    // Find conversations in blob that are NOT in the table (by ID or character)
    const conversationsToMigrate = blobConversations.filter((conv: any) => {
      const characterId = typeof conv.character === 'string'
        ? conv.character
        : conv.character?.id;

      // Skip if this conversation ID already exists in table
      if (tableConvoIds.has(conv.id)) {
        return false;
      }

      // Skip if we already have a conversation with this character in table
      // (table version is newer/more authoritative)
      if (characterId && tableCharacterIds.has(characterId)) {
        return false;
      }

      return true;
    });

    // Migrate missing conversations from JSON blob to table
    if (conversationsToMigrate.length > 0) {
      console.log(`Migrating ${conversationsToMigrate.length} conversations from JSON blob to table for player ${playerId}`);

      for (const conv of conversationsToMigrate) {
        const characterId = typeof conv.character === 'string'
          ? conv.character
          : conv.character?.id;

        if (!characterId) {
          console.warn(`Skipping conversation with missing character: ${conv.id}`);
          continue;
        }

        await saveConversation(
          playerId,
          characterId,
          conv.id,
          conv.conversation || [],
          conv.unread ?? false
        );

        // Add to our result set
        tableConversations.push({
          id: conv.id,
          character: characterId,
          cType: conv.cType || 'casual',
          conversation: conv.conversation || [],
          question: conv.question || 0,
          unread: conv.unread ?? false,
          summary: conv.summary,
          summary_message_count: conv.summary_message_count,
        });
      }

      console.log(`Migration complete for player ${playerId}`);
    }

    console.log(`Loaded ${tableConversations.length} total conversations for player ${playerId}`);
    return tableConversations;
  } catch (error) {
    console.error(`Error loading conversations for player ${playerId}:`, error);
    return [];
  }
}

/**
 * Mark a conversation as read.
 */
export async function markConversationAsRead(
  conversationId: string,
  playerId: string
): Promise<void> {
  const connection = await getConnection();
  try {
    await connection.execute(
      'UPDATE conversations SET unread = false WHERE id = ? AND player_id = ?',
      [conversationId, playerId]
    );
  } catch (error) {
    console.error('Error marking conversation as read:', error);
  } finally {
    connection.release();
  }
}

// ============================================================================
// Database Schema Check/Init
// ============================================================================

/**
 * Ensure required tables exist.
 * Call on server startup.
 */
export async function ensureTables(): Promise<void> {
  const connection = await getConnection();
  try {
    // Players table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS players (
        id VARCHAR(255) PRIMARY KEY,
        data LONGTEXT NOT NULL,
        updated_at DATETIME NOT NULL,
        connection_status VARCHAR(50) DEFAULT 'disconnected',
        INDEX idx_connection (connection_status),
        INDEX idx_updated (updated_at)
      )
    `);

    // Conversations table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS conversations (
        id VARCHAR(255) PRIMARY KEY,
        player_id VARCHAR(255) NOT NULL,
        character_id VARCHAR(255) NOT NULL,
        messages LONGTEXT,
        unread BOOLEAN DEFAULT true,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_player (player_id),
        INDEX idx_character (character_id)
      )
    `);

    // Conversation messages table (for individual message tracking)
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS conversation_messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        conversation_id VARCHAR(255) NOT NULL,
        player_id VARCHAR(255) NOT NULL,
        character_id VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        sender ENUM('player', 'character') NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_conversation (conversation_id),
        INDEX idx_player (player_id)
      )
    `);

    await ensureEventInstancesTable(connection);

    console.log('Database tables verified');
  } catch (error) {
    console.error('Error ensuring tables:', error);
    throw error;
  } finally {
    connection.release();
  }
}