/**
 * Achievement System
 *
 * Tracks and unlocks achievements based on player actions.
 * Includes 40+ achievements across multiple categories.
 *
 * Ported from Python: ws/retention/achievements.py
 */

import { query, queryOne, execute, getConnection } from '../../database/index.js';
import { awardDiamonds } from '../../monetization/diamondEconomy.js';
import type { RowDataPacket, PoolConnection } from 'mysql2/promise';

// =====================================================
// TYPES AND INTERFACES
// =====================================================

export type AchievementCategory =
  | 'life_milestone'
  | 'career'
  | 'relationship'
  | 'collection'
  | 'secret';

export interface AchievementDefinition {
  key: string;
  name: string;
  desc: string;
  category: AchievementCategory;
  reward: number;
  icon: string;
  hidden: boolean;
}

export interface UnlockedAchievement {
  id: string;
  key: string;
  name: string;
  description: string;
  icon: string;
  reward: number;
  unlockedAt?: Date;
}

export interface PlayerAchievements {
  unlocked: UnlockedAchievement[];
  locked: AchievementDefinition[];
  totalCount: number;
  unlockedCount: number;
  progressPercent: number;
}

export interface PlayerStats {
  lifetimeEarnings?: number;
  lifetimeSpending?: number;
  jobCount?: number;
  childrenCount?: number;
  friendsCount?: number;
  peopleDated?: number;
  timesFired?: number;
  yearsMarried?: number;
  everMarried?: boolean;
  marriageCount?: number;
}

export interface EventData {
  level?: string;
  gpa?: number;
  title?: string;
  age?: number;
  affinity?: number;
  hours?: number;
  performance?: number;
  amount?: number;
  [key: string]: unknown;
}

interface DiamondAwardPlayer {
  userId?: string;
  c?: {
    diamonds?: number;
  };
  character?: {
    diamonds?: number;
  };
}

interface AchievementRow extends RowDataPacket {
  id: number;
  key_name: string;
  display_name: string;
  description: string;
  category: string;
  diamond_reward: number;
  icon_name: string;
  hidden: boolean;
  progress_max: number;
}

interface PlayerAchievementRow extends RowDataPacket {
  player_id: string;
  achievement_id: number;
  progress: number;
  unlocked: boolean;
  unlock_date: Date;
}

interface ColumnNameRow extends RowDataPacket {
  COLUMN_NAME: string;
}

// =====================================================
// ACHIEVEMENT DEFINITIONS (40+ achievements)
// =====================================================

export const ACHIEVEMENT_DEFINITIONS: AchievementDefinition[] = [
  // === LIFE MILESTONES ===
  { key: 'first_steps', name: 'First Steps', desc: 'Complete the onboarding tutorial',
    category: 'life_milestone', reward: 25, icon: 'graduationcap', hidden: false },
  { key: 'first_day_school', name: 'First Day of School', desc: 'Start elementary school',
    category: 'life_milestone', reward: 10, icon: 'book', hidden: false },
  { key: 'graduate_hs', name: 'High School Graduate', desc: 'Graduate from high school',
    category: 'life_milestone', reward: 25, icon: 'graduationcap', hidden: false },
  { key: 'graduate_college', name: 'College Degree', desc: 'Graduate from college',
    category: 'life_milestone', reward: 50, icon: 'scroll', hidden: false },
  { key: 'graduate_masters', name: 'Master Scholar', desc: "Complete a master's degree",
    category: 'life_milestone', reward: 75, icon: 'brain', hidden: false },
  { key: 'graduate_phd', name: 'Doctor of Philosophy', desc: 'Earn a PhD',
    category: 'life_milestone', reward: 100, icon: 'crown', hidden: false },
  { key: 'get_married', name: 'Happily Ever After', desc: 'Get married',
    category: 'life_milestone', reward: 30, icon: 'heart', hidden: false },
  { key: 'have_child', name: 'New Parent', desc: 'Have your first child',
    category: 'life_milestone', reward: 40, icon: 'figure.2', hidden: false },
  { key: 'live_to_50', name: 'Half Century', desc: 'Live to 50 years old',
    category: 'life_milestone', reward: 20, icon: 'star', hidden: false },
  { key: 'live_to_100', name: 'Century Club', desc: 'Live to 100 years old',
    category: 'life_milestone', reward: 200, icon: 'star.fill', hidden: false },

  // === CAREER ===
  { key: 'first_job', name: 'Working Class', desc: 'Get your first job',
    category: 'career', reward: 10, icon: 'briefcase', hidden: false },
  { key: 'promotion', name: 'Climbing the Ladder', desc: 'Get promoted',
    category: 'career', reward: 20, icon: 'arrow.up', hidden: false },
  { key: 'become_manager', name: 'Management Material', desc: 'Become a manager',
    category: 'career', reward: 30, icon: 'person.2', hidden: false },
  { key: 'become_ceo', name: 'Corner Office', desc: 'Become a CEO',
    category: 'career', reward: 75, icon: 'crown', hidden: false },
  { key: 'earn_100k', name: 'Six Figures', desc: 'Earn $100,000 in a year',
    category: 'career', reward: 50, icon: 'dollarsign.circle', hidden: false },
  { key: 'earn_1m_lifetime', name: 'Millionaire', desc: 'Earn $1,000,000 in your lifetime',
    category: 'career', reward: 100, icon: 'banknote', hidden: false },
  { key: 'earn_10m_lifetime', name: 'Multi-Millionaire', desc: 'Earn $10,000,000 in your lifetime',
    category: 'career', reward: 250, icon: 'banknote.fill', hidden: false },
  { key: 'perfect_performance', name: 'Employee of the Year', desc: 'Achieve 100% job performance',
    category: 'career', reward: 25, icon: 'trophy', hidden: false },

  // === RELATIONSHIPS ===
  { key: 'make_first_friend', name: 'Social Butterfly Begins', desc: 'Make your first friend',
    category: 'relationship', reward: 10, icon: 'person', hidden: false },
  { key: 'best_friend', name: 'Best Friends Forever', desc: 'Reach 100 affinity with someone',
    category: 'relationship', reward: 30, icon: 'heart.fill', hidden: false },
  { key: 'date_10_people', name: 'Dating Around', desc: 'Date 10 different people',
    category: 'relationship', reward: 25, icon: 'heart.multiple', hidden: false },
  { key: 'first_kiss', name: 'First Kiss', desc: 'Share your first kiss',
    category: 'relationship', reward: 15, icon: 'heart.circle', hidden: false },
  { key: 'golden_anniversary', name: 'Golden Anniversary', desc: 'Stay married for 50 years',
    category: 'relationship', reward: 100, icon: 'sparkles', hidden: false },
  { key: 'five_children', name: 'Big Family', desc: 'Have 5 children',
    category: 'relationship', reward: 50, icon: 'house.fill', hidden: false },
  { key: 'popular', name: 'Mr/Ms Popular', desc: 'Have 10 friends',
    category: 'relationship', reward: 35, icon: 'person.3', hidden: false },

  // === COLLECTION ===
  { key: 'first_purchase', name: 'First Purchase', desc: 'Buy your first item',
    category: 'collection', reward: 5, icon: 'bag', hidden: false },
  { key: 'own_25_items', name: 'Collector', desc: 'Own 25 items',
    category: 'collection', reward: 20, icon: 'square.grid.3x3', hidden: false },
  { key: 'own_50_items', name: 'Hoarder', desc: 'Own 50 items',
    category: 'collection', reward: 40, icon: 'square.grid.4x3', hidden: false },
  { key: 'own_100_items', name: 'Ultimate Collector', desc: 'Own 100 items',
    category: 'collection', reward: 80, icon: 'square.stack.3d.up', hidden: false },
  { key: 'prestige_100', name: 'Rising Star', desc: 'Reach 100 prestige',
    category: 'collection', reward: 25, icon: 'star', hidden: false },
  { key: 'prestige_500', name: 'Celebrity Status', desc: 'Reach 500 prestige',
    category: 'collection', reward: 75, icon: 'star.circle', hidden: false },
  { key: 'max_prestige', name: 'Maximum Prestige', desc: 'Reach 1000 prestige',
    category: 'collection', reward: 150, icon: 'star.circle.fill', hidden: false },

  // === SECRET ACHIEVEMENTS ===
  { key: 'die_at_69', name: 'Nice', desc: 'Die at exactly 69 years old',
    category: 'secret', reward: 50, icon: 'face.smiling', hidden: true },
  { key: 'fired_3_times', name: 'Professional Quitter', desc: 'Get fired 3 times',
    category: 'secret', reward: 25, icon: 'xmark.circle', hidden: true },
  { key: 'never_marry', name: 'Forever Alone', desc: 'Complete a life without marrying',
    category: 'secret', reward: 30, icon: 'person.fill', hidden: true },
  { key: 'marry_3_times', name: 'Serial Monogamist', desc: 'Get married 3 different times',
    category: 'secret', reward: 40, icon: 'arrow.triangle.2.circlepath', hidden: true },
  { key: 'die_poor', name: 'Broke at Death', desc: 'Die with less than $100',
    category: 'secret', reward: 20, icon: 'banknote.slash', hidden: true },
  { key: 'die_rich', name: 'Died Wealthy', desc: 'Die with over $1,000,000',
    category: 'secret', reward: 100, icon: 'banknote.fill', hidden: true },
  { key: 'perfect_health', name: 'Health Nut', desc: 'Maintain 100% health for a full year',
    category: 'secret', reward: 50, icon: 'heart.text.square.fill', hidden: true },
  { key: 'workaholic', name: 'Workaholic', desc: 'Work 80+ hours in a week',
    category: 'secret', reward: 30, icon: 'deskclock', hidden: true },
  { key: 'early_retirement', name: 'Early Retirement', desc: 'Retire before age 40',
    category: 'secret', reward: 75, icon: 'beach.umbrella', hidden: true },
  { key: 'straight_a', name: 'Straight A Student', desc: 'Graduate with a 4.0 GPA',
    category: 'secret', reward: 40, icon: 'a.circle.fill', hidden: true },
  { key: 'dropout', name: 'School Dropout', desc: 'Drop out of high school',
    category: 'secret', reward: 15, icon: 'book.closed', hidden: true },
  { key: 'no_friends', name: 'Lone Wolf', desc: 'Reach age 30 without making any friends',
    category: 'secret', reward: 25, icon: 'person.fill.questionmark', hidden: true },
];

// In-memory player achievement storage (fallback when database unavailable)
const playerAchievementsCache: Map<string, Set<string>> = new Map();

// =====================================================
// DATABASE OPERATIONS
// =====================================================

async function getTableColumns(connection: PoolConnection, tableName: string): Promise<Set<string>> {
  const [rows] = await connection.execute<ColumnNameRow[]>(
    `SELECT COLUMN_NAME
     FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = ?`,
    [tableName]
  );

  return new Set(rows.map(row => row.COLUMN_NAME.toLowerCase()));
}

/**
 * Ensure legacy achievement schema variants have all columns expected by TS runtime.
 */
async function normalizeAchievementSchema(): Promise<void> {
  const connection = await getConnection();
  try {
    const achievementColumns = await getTableColumns(connection, 'achievements');

    if (!achievementColumns.has('name')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN name VARCHAR(200) NULL');
      if (achievementColumns.has('display_name')) {
        await connection.execute(`
          UPDATE achievements
          SET name = display_name
          WHERE name IS NULL
        `);
      } else {
        await connection.execute(`
          UPDATE achievements
          SET name = key_name
          WHERE name IS NULL
        `);
      }
    }

    if (!achievementColumns.has('display_name')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN display_name VARCHAR(255) NULL');
      if (achievementColumns.has('name')) {
        await connection.execute(`
          UPDATE achievements
          SET display_name = name
          WHERE display_name IS NULL
        `);
      } else {
        await connection.execute(`
          UPDATE achievements
          SET display_name = key_name
          WHERE display_name IS NULL
        `);
      }
    }

    if (!achievementColumns.has('diamond_reward')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN diamond_reward INT DEFAULT 0');
      if (achievementColumns.has('reward_diamonds')) {
        await connection.execute(`
          UPDATE achievements
          SET diamond_reward = reward_diamonds
          WHERE diamond_reward IS NULL OR diamond_reward = 0
        `);
      }
    }

    if (!achievementColumns.has('icon_name')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN icon_name VARCHAR(100) NULL');
      if (achievementColumns.has('icon')) {
        await connection.execute(`
          UPDATE achievements
          SET icon_name = icon
          WHERE icon_name IS NULL
        `);
      }
    }

    if (!achievementColumns.has('hidden')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN hidden BOOLEAN DEFAULT FALSE');
    }

    if (!achievementColumns.has('progress_max')) {
      await connection.execute('ALTER TABLE achievements ADD COLUMN progress_max INT DEFAULT 1');
    }

    if (!achievementColumns.has('updated_at')) {
      await connection.execute(
        'ALTER TABLE achievements ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
      );
    }

    // Tighten display_name to expected NOT NULL semantics after backfill.
    await connection.execute('UPDATE achievements SET display_name = key_name WHERE display_name IS NULL OR display_name = \'\'');
    await connection.execute('ALTER TABLE achievements MODIFY COLUMN display_name VARCHAR(255) NOT NULL');
    // Legacy schema used a restrictive ENUM that does not support current categories.
    await connection.execute('ALTER TABLE achievements MODIFY COLUMN category VARCHAR(50) NOT NULL');

    const playerAchievementColumns = await getTableColumns(connection, 'player_achievements');

    if (!playerAchievementColumns.has('unlock_date')) {
      await connection.execute('ALTER TABLE player_achievements ADD COLUMN unlock_date TIMESTAMP NULL');
      if (playerAchievementColumns.has('unlocked_at')) {
        await connection.execute(`
          UPDATE player_achievements
          SET unlock_date = unlocked_at
          WHERE unlock_date IS NULL
        `);
      }
    }

    if (!playerAchievementColumns.has('progress')) {
      await connection.execute('ALTER TABLE player_achievements ADD COLUMN progress INT DEFAULT 0');
    }

    if (!playerAchievementColumns.has('unlocked')) {
      await connection.execute('ALTER TABLE player_achievements ADD COLUMN unlocked BOOLEAN DEFAULT FALSE');
    }
  } finally {
    connection.release();
  }
}

/**
 * Initialize achievements table and insert definitions
 */
export async function initializeAchievements(): Promise<void> {
  try {
    // Create achievements table if not exists
    await execute(`
      CREATE TABLE IF NOT EXISTS achievements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        key_name VARCHAR(100) UNIQUE NOT NULL,
        display_name VARCHAR(255) NOT NULL,
        description TEXT,
        category VARCHAR(50) NOT NULL,
        diamond_reward INT DEFAULT 0,
        icon_name VARCHAR(100),
        hidden BOOLEAN DEFAULT FALSE,
        progress_max INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // Create player_achievements table if not exists
    await execute(`
      CREATE TABLE IF NOT EXISTS player_achievements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        player_id VARCHAR(255) NOT NULL,
        achievement_id INT NOT NULL,
        progress INT DEFAULT 0,
        unlocked BOOLEAN DEFAULT FALSE,
        unlock_date TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_player_achievement (player_id, achievement_id),
        FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE
      )
    `);

    await normalizeAchievementSchema();

    // Insert/update achievement definitions
    for (const ach of ACHIEVEMENT_DEFINITIONS) {
      await execute(`
        INSERT INTO achievements
          (key_name, name, display_name, description, category, diamond_reward, icon_name, hidden)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          name = VALUES(name),
          display_name = VALUES(display_name),
          description = VALUES(description),
          diamond_reward = VALUES(diamond_reward),
          icon_name = VALUES(icon_name),
          hidden = VALUES(hidden)
      `, [ach.key, ach.name, ach.name, ach.desc, ach.category, ach.reward, ach.icon, ach.hidden]);
    }

    console.log(`Initialized ${ACHIEVEMENT_DEFINITIONS.length} achievements`);
  } catch (error) {
    console.error('Error initializing achievements:', error);
    throw error;
  }
}

/**
 * Get achievement definition by key
 */
export function getAchievementByKey(key: string): AchievementDefinition | undefined {
  return ACHIEVEMENT_DEFINITIONS.find(a => a.key === key);
}

/**
 * Check if player has unlocked an achievement (database + cache)
 */
export async function hasUnlockedAchievementAsync(playerId: string, achievementKey: string): Promise<boolean> {
  try {
    // Check cache first
    const cached = playerAchievementsCache.get(playerId);
    if (cached?.has(achievementKey)) {
      return true;
    }

    // Check database
    const result = await queryOne<PlayerAchievementRow>(`
      SELECT pa.* FROM player_achievements pa
      JOIN achievements a ON pa.achievement_id = a.id
      WHERE pa.player_id = ? AND a.key_name = ? AND pa.unlocked = TRUE
    `, [playerId, achievementKey]);

    if (result) {
      // Update cache
      if (!cached) {
        playerAchievementsCache.set(playerId, new Set([achievementKey]));
      } else {
        cached.add(achievementKey);
      }
      return true;
    }

    return false;
  } catch (error) {
    console.error(`Error checking achievement ${achievementKey} for player ${playerId}:`, error);
    // Fallback to cache-only
    return playerAchievementsCache.get(playerId)?.has(achievementKey) ?? false;
  }
}

/**
 * Synchronous check using cache only
 */
export function hasUnlockedAchievement(playerId: string, achievementKey: string): boolean {
  return playerAchievementsCache.get(playerId)?.has(achievementKey) ?? false;
}

/**
 * Unlock an achievement for a player (with database persistence)
 */
export async function unlockAchievementAsync(
  playerId: string,
  achievementKey: string,
  player?: DiamondAwardPlayer
): Promise<UnlockedAchievement | null> {
  try {
    // Check if already unlocked
    if (await hasUnlockedAchievementAsync(playerId, achievementKey)) {
      return null;
    }

    // Get achievement definition
    const achievement = getAchievementByKey(achievementKey);
    if (!achievement) {
      console.warn(`Achievement not found: ${achievementKey}`);
      return null;
    }

    // Get achievement ID from database
    const achievementRow = await queryOne<AchievementRow>(
      'SELECT * FROM achievements WHERE key_name = ?',
      [achievementKey]
    );

    if (!achievementRow) {
      console.warn(`Achievement not in database: ${achievementKey}`);
      return null;
    }

    // Insert/update player achievement record
    await execute(`
      INSERT INTO player_achievements
        (player_id, achievement_id, progress, unlocked, unlock_date)
      VALUES (?, ?, ?, TRUE, NOW())
      ON DUPLICATE KEY UPDATE
        unlocked = TRUE,
        unlock_date = NOW(),
        progress = VALUES(progress)
    `, [playerId, achievementRow.id, achievementRow.progress_max || 1]);

    // Award diamonds
    awardDiamonds(playerId, `achievement_${achievementKey}`, achievement.reward, player);

    // Update cache
    let unlocked = playerAchievementsCache.get(playerId);
    if (!unlocked) {
      unlocked = new Set();
      playerAchievementsCache.set(playerId, unlocked);
    }
    unlocked.add(achievementKey);

    console.log(`Player ${playerId} unlocked achievement: ${achievementKey}`);

    return {
      id: achievementKey,
      key: achievement.key,
      name: achievement.name,
      description: achievement.desc,
      icon: achievement.icon,
      reward: achievement.reward,
      unlockedAt: new Date(),
    };
  } catch (error) {
    console.error(`Error unlocking achievement ${achievementKey}:`, error);
    return null;
  }
}

/**
 * Synchronous unlock using cache only (for backwards compatibility)
 */
export function unlockAchievement(
  playerId: string,
  achievementKey: string,
  player?: DiamondAwardPlayer
): UnlockedAchievement | null {
  // Check if already unlocked
  if (hasUnlockedAchievement(playerId, achievementKey)) {
    return null;
  }

  // Get achievement definition
  const achievement = getAchievementByKey(achievementKey);
  if (!achievement) {
    console.warn(`Achievement not found: ${achievementKey}`);
    return null;
  }

  // Add to cache
  let unlocked = playerAchievementsCache.get(playerId);
  if (!unlocked) {
    unlocked = new Set();
    playerAchievementsCache.set(playerId, unlocked);
  }
  unlocked.add(achievementKey);

  // Award diamonds
  awardDiamonds(playerId, `achievement_${achievementKey}`, achievement.reward, player);

  console.log(`Player ${playerId} unlocked achievement: ${achievementKey}`);

  return {
    id: achievementKey,
    key: achievement.key,
    name: achievement.name,
    description: achievement.desc,
    icon: achievement.icon,
    reward: achievement.reward,
    unlockedAt: new Date(),
  };
}

/**
 * Check if achievement should be unlocked and unlock it (async version)
 */
export async function checkAndUnlockAsync(
  playerId: string,
  achievementKey: string,
  player?: DiamondAwardPlayer
): Promise<UnlockedAchievement[]> {
  const result = await unlockAchievementAsync(playerId, achievementKey, player);
  return result ? [result] : [];
}

/**
 * Check if achievement should be unlocked and unlock it (sync version)
 */
export function checkAndUnlock(
  playerId: string,
  achievementKey: string,
  player?: DiamondAwardPlayer
): UnlockedAchievement[] {
  const result = unlockAchievement(playerId, achievementKey, player);
  return result ? [result] : [];
}

// =====================================================
// ACHIEVEMENT CHECKING LOGIC
// =====================================================

/**
 * Check achievements based on event type and data
 */
export function checkAchievements(
  playerId: string,
  eventType: string,
  eventData: EventData = {},
  playerData: {
    age?: number;
    money?: number;
    prestige?: number;
    itemCount?: number;
  } = {},
  stats: PlayerStats = {},
  player?: DiamondAwardPlayer
): UnlockedAchievement[] {
  const unlocked: UnlockedAchievement[] = [];
  const unlock = (achievementKey: string): UnlockedAchievement[] =>
    checkAndUnlock(playerId, achievementKey, player);

  // Event-specific checks
  switch (eventType) {
    case 'start_school':
      unlocked.push(...unlock('first_day_school'));
      break;

    case 'graduate': {
      const level = (eventData.level ?? '').toLowerCase();
      if (level.includes('high_school') || level.includes('high school')) {
        unlocked.push(...unlock('graduate_hs'));
        if ((eventData.gpa ?? 0) >= 4.0) {
          unlocked.push(...unlock('straight_a'));
        }
      } else if (level.includes('college') || level.includes('university')) {
        unlocked.push(...unlock('graduate_college'));
      } else if (level.includes('master')) {
        unlocked.push(...unlock('graduate_masters'));
      } else if (level.includes('phd') || level.includes('doctorate')) {
        unlocked.push(...unlock('graduate_phd'));
      }
      break;
    }

    case 'dropout':
      unlocked.push(...unlock('dropout'));
      break;

    case 'get_job':
      if (stats.jobCount === 1) {
        unlocked.push(...unlock('first_job'));
      }
      break;

    case 'promotion': {
      unlocked.push(...unlock('promotion'));
      const title = (eventData.title ?? '').toLowerCase();
      if (title.includes('manager')) {
        unlocked.push(...unlock('become_manager'));
      } else if (title.includes('ceo') || title.includes('chief executive')) {
        unlocked.push(...unlock('become_ceo'));
      }
      break;
    }

    case 'marriage':
      unlocked.push(...unlock('get_married'));
      // Check for multiple marriages
      if ((stats.marriageCount ?? 0) >= 3) {
        unlocked.push(...unlock('marry_3_times'));
      }
      break;

    case 'birth_child':
      if (stats.childrenCount === 1) {
        unlocked.push(...unlock('have_child'));
      }
      if ((stats.childrenCount ?? 0) >= 5) {
        unlocked.push(...unlock('five_children'));
      }
      break;

    case 'first_kiss':
      unlocked.push(...unlock('first_kiss'));
      break;

    case 'birthday': {
      const age = eventData.age ?? playerData.age ?? 0;
      if (age === 50) {
        unlocked.push(...unlock('live_to_50'));
      } else if (age === 100) {
        unlocked.push(...unlock('live_to_100'));
      }
      // Check for lone wolf at age 30
      if (age === 30 && (stats.friendsCount ?? 0) === 0) {
        unlocked.push(...unlock('no_friends'));
      }
      break;
    }

    case 'death': {
      const money = playerData.money ?? 0;
      const age = eventData.age ?? playerData.age ?? 0;

      // Death at specific ages
      if (age === 69) {
        unlocked.push(...unlock('die_at_69'));
      }

      // Wealth-related death achievements
      if (money < 100) {
        unlocked.push(...unlock('die_poor'));
      } else if (money >= 1000000) {
        unlocked.push(...unlock('die_rich'));
      }

      // Marriage-related death achievements
      if (!stats.everMarried) {
        unlocked.push(...unlock('never_marry'));
      }

      // Lifetime earnings achievements
      if ((stats.lifetimeEarnings ?? 0) >= 10000000) {
        unlocked.push(...unlock('earn_10m_lifetime'));
      } else if ((stats.lifetimeEarnings ?? 0) >= 1000000) {
        unlocked.push(...unlock('earn_1m_lifetime'));
      }
      break;
    }

    case 'make_friend':
      if (stats.friendsCount === 1) {
        unlocked.push(...unlock('make_first_friend'));
      } else if ((stats.friendsCount ?? 0) >= 10) {
        unlocked.push(...unlock('popular'));
      }
      break;

    case 'purchase_item': {
      const itemCount = playerData.itemCount ?? 0;
      if (itemCount === 1) {
        unlocked.push(...unlock('first_purchase'));
      }
      // Check thresholds in descending order
      if (itemCount >= 100) {
        unlocked.push(...unlock('own_100_items'));
      }
      if (itemCount >= 50) {
        unlocked.push(...unlock('own_50_items'));
      }
      if (itemCount >= 25) {
        unlocked.push(...unlock('own_25_items'));
      }
      break;
    }

    case 'affinity_milestone':
      if ((eventData.affinity ?? 0) >= 100) {
        unlocked.push(...unlock('best_friend'));
      }
      break;

    case 'fired':
      if ((stats.timesFired ?? 0) >= 3) {
        unlocked.push(...unlock('fired_3_times'));
      }
      break;

    case 'retire':
      if ((playerData.age ?? 0) < 40) {
        unlocked.push(...unlock('early_retirement'));
      }
      break;

    case 'work_hours':
      if ((eventData.hours ?? 0) >= 80) {
        unlocked.push(...unlock('workaholic'));
      }
      break;

    case 'job_performance':
      if ((eventData.performance ?? 0) >= 100) {
        unlocked.push(...unlock('perfect_performance'));
      }
      break;

    case 'annual_earnings':
      if ((eventData.amount ?? 0) >= 100000) {
        unlocked.push(...unlock('earn_100k'));
      }
      break;

    case 'health_maintained':
      unlocked.push(...unlock('perfect_health'));
      break;

    case 'complete_tutorial':
      unlocked.push(...unlock('first_steps'));
      break;
  }

  // Continuous checks (can be triggered by any event)
  const prestige = playerData.prestige ?? 0;
  if (prestige >= 1000) {
    unlocked.push(...unlock('max_prestige'));
  }
  if (prestige >= 500) {
    unlocked.push(...unlock('prestige_500'));
  }
  if (prestige >= 100) {
    unlocked.push(...unlock('prestige_100'));
  }

  // Dating milestone
  if ((stats.peopleDated ?? 0) >= 10) {
    unlocked.push(...unlock('date_10_people'));
  }

  // Marriage duration
  if ((stats.yearsMarried ?? 0) >= 50) {
    unlocked.push(...unlock('golden_anniversary'));
  }

  return unlocked;
}

/**
 * Async version of checkAchievements with database persistence
 */
export async function checkAchievementsAsync(
  playerId: string,
  eventType: string,
  eventData: EventData = {},
  playerData: {
    age?: number;
    money?: number;
    prestige?: number;
    itemCount?: number;
  } = {},
  stats: PlayerStats = {},
  player?: DiamondAwardPlayer
): Promise<UnlockedAchievement[]> {
  // Use sync version for speed, then persist asynchronously
  const unlocked = checkAchievements(playerId, eventType, eventData, playerData, stats, player);

  // Persist any unlocked achievements to database in background
  for (const ach of unlocked) {
    try {
      const achievementRow = await queryOne<AchievementRow>(
        'SELECT * FROM achievements WHERE key_name = ?',
        [ach.key]
      );
      if (achievementRow) {
        await execute(`
          INSERT INTO player_achievements
            (player_id, achievement_id, progress, unlocked, unlock_date)
          VALUES (?, ?, ?, TRUE, NOW())
          ON DUPLICATE KEY UPDATE
            unlocked = TRUE,
            unlock_date = COALESCE(unlock_date, NOW())
        `, [playerId, achievementRow.id, achievementRow.progress_max || 1]);
      }
    } catch (error) {
      console.error(`Error persisting achievement ${ach.key} to database:`, error);
    }
  }

  return unlocked;
}

// =====================================================
// PLAYER ACHIEVEMENT QUERIES
// =====================================================

/**
 * Get all achievements and player's progress (async with database)
 */
export async function getPlayerAchievementsAsync(playerId: string): Promise<PlayerAchievements> {
  try {
    // Get unlocked achievements from database
    const unlockedRows = await query<(AchievementRow & { unlock_date: Date })[]>(`
      SELECT a.*, pa.unlock_date
      FROM achievements a
      JOIN player_achievements pa ON a.id = pa.achievement_id
      WHERE pa.player_id = ? AND pa.unlocked = TRUE
      ORDER BY pa.unlock_date DESC
    `, [playerId]);

    // Get locked (non-hidden) achievements from database
    const lockedRows = await query<AchievementRow[]>(`
      SELECT a.*
      FROM achievements a
      WHERE a.id NOT IN (
        SELECT achievement_id FROM player_achievements
        WHERE player_id = ? AND unlocked = TRUE
      )
      AND a.hidden = FALSE
      ORDER BY a.category, a.diamond_reward
    `, [playerId]);

    const unlocked: UnlockedAchievement[] = unlockedRows.map(row => ({
      id: row.key_name,
      key: row.key_name,
      name: row.display_name,
      description: row.description,
      icon: row.icon_name,
      reward: row.diamond_reward,
      unlockedAt: row.unlock_date,
    }));

    const locked: AchievementDefinition[] = lockedRows.map(row => ({
      key: row.key_name,
      name: row.display_name,
      desc: row.description,
      category: row.category as AchievementCategory,
      reward: row.diamond_reward,
      icon: row.icon_name,
      hidden: row.hidden,
    }));

    // Update cache
    const cachedUnlocked = new Set(unlockedRows.map(r => r.key_name));
    playerAchievementsCache.set(playerId, cachedUnlocked);

    return {
      unlocked,
      locked,
      totalCount: ACHIEVEMENT_DEFINITIONS.length,
      unlockedCount: unlocked.length,
      progressPercent: Math.floor((unlocked.length / ACHIEVEMENT_DEFINITIONS.length) * 100),
    };
  } catch (error) {
    console.error(`Error getting achievements for player ${playerId}:`, error);
    // Fallback to cache-only
    return getPlayerAchievements(playerId);
  }
}

/**
 * Get all achievements and player's progress (sync using cache)
 */
export function getPlayerAchievements(playerId: string): PlayerAchievements {
  const unlockedKeys = playerAchievementsCache.get(playerId) ?? new Set();

  const unlocked: UnlockedAchievement[] = [];
  const locked: AchievementDefinition[] = [];

  for (const achievement of ACHIEVEMENT_DEFINITIONS) {
    if (unlockedKeys.has(achievement.key)) {
      unlocked.push({
        id: achievement.key,
        key: achievement.key,
        name: achievement.name,
        description: achievement.desc,
        icon: achievement.icon,
        reward: achievement.reward,
      });
    } else if (!achievement.hidden) {
      locked.push(achievement);
    }
  }

  return {
    unlocked,
    locked,
    totalCount: ACHIEVEMENT_DEFINITIONS.length,
    unlockedCount: unlocked.length,
    progressPercent: Math.floor((unlocked.length / ACHIEVEMENT_DEFINITIONS.length) * 100),
  };
}

/**
 * Get achievements by category
 */
export function getAchievementsByCategory(category: AchievementCategory): AchievementDefinition[] {
  return ACHIEVEMENT_DEFINITIONS.filter(a => a.category === category);
}

/**
 * Load player achievements from database into cache
 */
export async function loadPlayerAchievements(playerId: string): Promise<void> {
  try {
    const rows = await query<AchievementRow[]>(`
      SELECT a.key_name
      FROM achievements a
      JOIN player_achievements pa ON a.id = pa.achievement_id
      WHERE pa.player_id = ? AND pa.unlocked = TRUE
    `, [playerId]);

    const unlockedSet = new Set(rows.map(r => r.key_name));
    playerAchievementsCache.set(playerId, unlockedSet);
    console.log(`Loaded ${unlockedSet.size} achievements for player ${playerId}`);
  } catch (error) {
    console.error(`Error loading achievements for player ${playerId}:`, error);
  }
}

// =====================================================
// UTILITY FUNCTIONS
// =====================================================

/**
 * Clear player achievements (for testing or new game)
 */
export function clearPlayerAchievements(playerId: string): void {
  playerAchievementsCache.delete(playerId);
}

/**
 * Clear player achievements from database
 */
export async function clearPlayerAchievementsAsync(playerId: string): Promise<void> {
  try {
    await execute('DELETE FROM player_achievements WHERE player_id = ?', [playerId]);
    playerAchievementsCache.delete(playerId);
  } catch (error) {
    console.error(`Error clearing achievements for player ${playerId}:`, error);
  }
}

/**
 * Clear all achievements (for testing)
 */
export function clearAllAchievements(): void {
  playerAchievementsCache.clear();
}

/**
 * Get total diamond reward for a list of achievements
 */
export function calculateTotalReward(achievements: UnlockedAchievement[]): number {
  return achievements.reduce((sum, a) => sum + a.reward, 0);
}

/**
 * A single achievement entry in the collection view. Includes locked AND
 * unlocked achievements so the client can render a complete collection screen
 * with greyed-out / "mystery" entries. Locked entries carry a hint and a
 * progressPercent (0 or 100 for the binary achievements we model today; the
 * field is forward-compatible with graded progress).
 */
export interface CollectionEntry {
  key: string;
  name: string;
  description: string;
  icon: string;
  reward: number;
  category: AchievementCategory;
  unlocked: boolean;
  hidden: boolean;
  /** A hint shown for locked achievements (the description, or a teaser for
   *  hidden/secret ones so they remain a surprise). Empty for unlocked. */
  hint: string;
  /** 0-100. 100 when unlocked, 0 when still locked. */
  progressPercent: number;
}

export interface CategorySummary {
  total: number;
  unlocked: number;
  /** 0-100 completion percentage for this category. */
  progressPercent: number;
  /** Locked + unlocked entries for this category, for the collection screen. */
  entries: CollectionEntry[];
}

export interface AchievementSummary {
  total: number;
  unlocked: number;
  /** 0-100 overall completion percentage. */
  progressPercent: number;
  byCategory: Record<AchievementCategory, CategorySummary>;
}

/**
 * Build a hint for a locked achievement. Hidden/secret achievements get a
 * teaser so the player knows there's something to discover without spoiling it.
 */
function buildHint(def: AchievementDefinition): string {
  if (def.hidden) {
    return 'Secret achievement — keep playing to discover it.';
  }
  return def.desc;
}

/**
 * Get achievement progress summary for a player, including a per-category
 * collection of BOTH locked and unlocked achievements (with hints +
 * progressPercent) and per-category completion percentages. Powers a client
 * collection / trophy-case screen.
 */
export function getAchievementSummary(playerId: string): AchievementSummary {
  const unlockedKeys = playerAchievementsCache.get(playerId) ?? new Set();

  const categories: AchievementCategory[] = [
    'life_milestone',
    'career',
    'relationship',
    'collection',
    'secret',
  ];

  const byCategory = {} as Record<AchievementCategory, CategorySummary>;
  for (const cat of categories) {
    byCategory[cat] = { total: 0, unlocked: 0, progressPercent: 0, entries: [] };
  }

  for (const achievement of ACHIEVEMENT_DEFINITIONS) {
    const cat = byCategory[achievement.category];
    const isUnlocked = unlockedKeys.has(achievement.key);

    cat.total++;
    if (isUnlocked) cat.unlocked++;

    cat.entries.push({
      key: achievement.key,
      name: achievement.name,
      description: achievement.desc,
      icon: achievement.icon,
      reward: achievement.reward,
      category: achievement.category,
      unlocked: isUnlocked,
      hidden: achievement.hidden,
      hint: isUnlocked ? '' : buildHint(achievement),
      progressPercent: isUnlocked ? 100 : 0,
    });
  }

  for (const cat of categories) {
    const c = byCategory[cat];
    c.progressPercent = c.total > 0 ? Math.floor((c.unlocked / c.total) * 100) : 0;
  }

  const total = ACHIEVEMENT_DEFINITIONS.length;
  const unlocked = unlockedKeys.size;

  return {
    total,
    unlocked,
    progressPercent: total > 0 ? Math.floor((unlocked / total) * 100) : 0,
    byCategory,
  };
}