import crypto from "crypto";
import type { Database } from "sqlite";

type JsonValue = Record<string, unknown> | unknown[] | string | number | boolean | null;

export interface TowerCatalogResponse {
  domains: unknown[];
  sectors: unknown[];
  floors: unknown[];
  rooms: unknown[];
  encounters: unknown[];
  guardians: unknown[];
  astrophonists: unknown[];
  rewards: unknown[];
  recipes: unknown[];
  research: unknown[];
  anomalies: unknown[];
  counts: Record<string, number>;
  hasContent: boolean;
}

export interface TowerSimulationStartInput {
  gameMode: string;
  domainId: number;
  sectorId: number;
  floorId: number;
  roomId: number;
  seed?: string;
}

export interface TowerSimulationRecord {
  simulationId: string;
  playerId: string;
  gameMode: string;
  domainId: number | null;
  sectorId: number | null;
  floorId: number | null;
  roomId: number | null;
  status: string;
  seed: string | null;
  context: Record<string, unknown>;
  encounter: Record<string, unknown> | null;
  battleResult: Record<string, unknown> | null;
  rewardPackage: Record<string, unknown> | null;
  result: Record<string, unknown> | null;
  lastError: string | null;
  abandonedReason: string | null;
  startTime: string | null;
  endTime: string | null;
  createdAt: string | null;
  updatedAt: string | null;
}

export interface TowerProgressResponse {
  progress: Record<string, unknown> | null;
  activeSimulation: TowerSimulationRecord | null;
  recentSimulations: TowerSimulationRecord[];
}

const parseJson = <T>(value: unknown, fallback: T): T => {
  if (value == null || value === "") return fallback;
  if (typeof value === "object") return value as T;
  if (typeof value !== "string") return fallback;

  try {
    return JSON.parse(value) as T;
  } catch {
    return fallback;
  }
};

const serializeJson = (value: JsonValue) => JSON.stringify(value ?? null);

const asPositiveInt = (value: unknown): number | null => {
  const numeric = typeof value === "number" ? value : Number(value);
  if (!Number.isInteger(numeric) || numeric <= 0) return null;
  return numeric;
};

const toNumberOrNull = (value: unknown): number | null => {
  const numeric = typeof value === "number" ? value : Number(value);
  return Number.isFinite(numeric) ? numeric : null;
};

const mapSimulationRow = (row: any): TowerSimulationRecord | null => {
  if (!row) return null;

  return {
    simulationId: String(row.simulation_id),
    playerId: String(row.player_id),
    gameMode: String(row.game_mode),
    domainId: toNumberOrNull(row.domain_id),
    sectorId: toNumberOrNull(row.sector_id),
    floorId: toNumberOrNull(row.floor_id),
    roomId: toNumberOrNull(row.room_id),
    status: String(row.status),
    seed: row.seed ?? null,
    context: parseJson<Record<string, unknown>>(row.context_json, {}),
    encounter: parseJson<Record<string, unknown> | null>(row.encounter_json, null),
    battleResult: parseJson<Record<string, unknown> | null>(row.battle_result_json, null),
    rewardPackage: parseJson<Record<string, unknown> | null>(row.reward_package_json, null),
    result: parseJson<Record<string, unknown> | null>(row.result_json, null),
    lastError: row.last_error ?? null,
    abandonedReason: row.abandoned_reason ?? null,
    startTime: row.start_time ?? null,
    endTime: row.end_time ?? null,
    createdAt: row.created_at ?? null,
    updatedAt: row.updated_at ?? null,
  };
};

const countRows = async (db: Database, table: string): Promise<number> => {
  const row = await db.get(`SELECT COUNT(*) as count FROM ${table}`);
  return Number(row?.count ?? 0);
};

const loadTowerRows = async (db: Database, table: string) => db.all(`SELECT * FROM ${table} ORDER BY id ASC`);

export async function getTowerCatalog(db: Database): Promise<TowerCatalogResponse> {
  const [
    domains,
    sectors,
    floors,
    rooms,
    encounters,
    guardians,
    astrophonists,
    rewards,
    recipes,
    research,
    anomalies,
    domainCount,
    sectorCount,
    floorCount,
    roomCount,
    encounterCount,
    guardianCount,
    astrophonistCount,
    rewardCount,
    recipeCount,
    researchCount,
    anomalyCount,
  ] = await Promise.all([
    loadTowerRows(db, "tower_domains"),
    loadTowerRows(db, "tower_sectors"),
    loadTowerRows(db, "tower_floors"),
    loadTowerRows(db, "tower_rooms"),
    loadTowerRows(db, "tower_encounters"),
    loadTowerRows(db, "tower_guardians"),
    loadTowerRows(db, "tower_astrophonists"),
    loadTowerRows(db, "tower_rewards"),
    loadTowerRows(db, "tower_recipes"),
    loadTowerRows(db, "tower_research"),
    loadTowerRows(db, "tower_anomalies"),
    countRows(db, "tower_domains"),
    countRows(db, "tower_sectors"),
    countRows(db, "tower_floors"),
    countRows(db, "tower_rooms"),
    countRows(db, "tower_encounters"),
    countRows(db, "tower_guardians"),
    countRows(db, "tower_astrophonists"),
    countRows(db, "tower_rewards"),
    countRows(db, "tower_recipes"),
    countRows(db, "tower_research"),
    countRows(db, "tower_anomalies"),
  ]);

  return {
    domains,
    sectors,
    floors,
    rooms,
    encounters,
    guardians,
    astrophonists,
    rewards,
    recipes,
    research,
    anomalies,
    counts: {
      domains: domainCount,
      sectors: sectorCount,
      floors: floorCount,
      rooms: roomCount,
      encounters: encounterCount,
      guardians: guardianCount,
      astrophonists: astrophonistCount,
      rewards: rewardCount,
      recipes: recipeCount,
      research: researchCount,
      anomalies: anomalyCount,
    },
    hasContent: [
      domainCount,
      sectorCount,
      floorCount,
      roomCount,
      encounterCount,
      guardianCount,
      astrophonistCount,
      rewardCount,
      recipeCount,
      researchCount,
      anomalyCount,
    ].some((count) => count > 0),
  };
}

async function loadGuardiansForScope(db: Database, domainId: number, sectorId: number, floorId: number) {
  const floorGuardian = await db.get(
    `SELECT g.*, s.nombre as sonus_name, s.rareza as sonus_rarity, s.elemento as sonus_element, s.clase as sonus_class
     FROM tower_guardians g
     JOIN sonus_master s ON s.id_sonus = g.sonus_master_id
     WHERE g.floor_id = ?
     ORDER BY g.id ASC
     LIMIT 1`,
    [floorId]
  );
  if (floorGuardian) return floorGuardian;

  const sectorGuardian = await db.get(
    `SELECT g.*, s.nombre as sonus_name, s.rareza as sonus_rarity, s.elemento as sonus_element, s.clase as sonus_class
     FROM tower_guardians g
     JOIN sonus_master s ON s.id_sonus = g.sonus_master_id
     WHERE g.sector_id = ?
     ORDER BY g.id ASC
     LIMIT 1`,
    [sectorId]
  );
  if (sectorGuardian) return sectorGuardian;

  const domainGuardian = await db.get(
    `SELECT g.*, s.nombre as sonus_name, s.rareza as sonus_rarity, s.elemento as sonus_element, s.clase as sonus_class
     FROM tower_guardians g
     JOIN sonus_master s ON s.id_sonus = g.sonus_master_id
     WHERE g.domain_id = ?
     ORDER BY g.id ASC
     LIMIT 1`,
    [domainId]
  );

  return domainGuardian ?? null;
}

async function loadRewardsForProfile(db: Database, rewardProfile: string | null | undefined) {
  if (!rewardProfile) return [];
  return db.all(
    `SELECT * FROM tower_rewards WHERE reward_profile = ? ORDER BY rarity_weight DESC, id ASC`,
    [rewardProfile]
  );
}

async function loadEncounterTemplate(db: Database, roomId: number) {
  const room = await db.get(
    `SELECT r.*, f.sector_id, f.difficulty as floor_difficulty, f.reward_profile as floor_reward_profile, f.encounter_profile as floor_encounter_profile,
            s.domain_id, s.theme as sector_theme, s.difficulty as sector_difficulty, s.recommended_level, s.guardian_sonus_master_id, s.law_astral,
            d.name as domain_name, d.description as domain_description, d.order_index as domain_order_index
     FROM tower_rooms r
     JOIN tower_floors f ON f.id = r.floor_id
     JOIN tower_sectors s ON s.id = f.sector_id
     JOIN tower_domains d ON d.id = s.domain_id
     WHERE r.id = ?`,
    [roomId]
  );

  if (!room) {
    return { room: null, encounter: null, guardian: null, rewards: [] };
  }

  const encounter = await db.get(
    `SELECT * FROM tower_encounters WHERE room_id = ? ORDER BY id ASC LIMIT 1`,
    [roomId]
  );

  const guardian = await loadGuardiansForScope(db, room.domain_id, room.sector_id, room.floor_id);
  const rewardProfile = String(room.floor_reward_profile || room.floor_encounter_profile || room.encounter_profile || room.room_type || "");
  const rewards = await loadRewardsForProfile(db, rewardProfile);

  return { room, encounter, guardian, rewards };
}

async function loadEnemyPool(db: Database, enemyPoolJson: unknown) {
  const enemyIds = parseJson<unknown[]>(enemyPoolJson, [])
    .map((value) => asPositiveInt(value))
    .filter((value): value is number => value !== null);

  if (!enemyIds.length) return [];

  const placeholders = enemyIds.map(() => "?").join(",");
  const rows = await db.all(
    `SELECT id_sonus, nombre, rareza, elemento, clase, instrumento, base_hp, base_atk, base_def, base_spd
     FROM sonus_master
     WHERE id_sonus IN (${placeholders})
     ORDER BY id_sonus ASC`,
    enemyIds
  );

  return rows;
}

export async function createTowerSimulation(
  db: Database,
  playerId: string,
  input: TowerSimulationStartInput
) {
  const domainId = asPositiveInt(input.domainId);
  const sectorId = asPositiveInt(input.sectorId);
  const floorId = asPositiveInt(input.floorId);
  const roomId = asPositiveInt(input.roomId);

  if (!domainId || !sectorId || !floorId || !roomId) {
    return {
      ok: false as const,
      code: "INVALID_TOWER_CONTEXT",
      message: "domainId, sectorId, floorId y roomId deben ser valores positivos.",
    };
  }

  const domain = await db.get("SELECT * FROM tower_domains WHERE id = ?", [domainId]);
  if (!domain) {
    return {
      ok: false as const,
      code: "DOMAIN_NOT_FOUND",
      message: "El dominio solicitado no existe.",
    };
  }

  const sector = await db.get("SELECT * FROM tower_sectors WHERE id = ? AND domain_id = ?", [sectorId, domainId]);
  if (!sector) {
    return {
      ok: false as const,
      code: "SECTOR_NOT_FOUND",
      message: "El sector solicitado no pertenece al dominio indicado.",
    };
  }

  const floor = await db.get("SELECT * FROM tower_floors WHERE id = ? AND sector_id = ?", [floorId, sectorId]);
  if (!floor) {
    return {
      ok: false as const,
      code: "FLOOR_NOT_FOUND",
      message: "El piso solicitado no pertenece al sector indicado.",
    };
  }

  const room = await db.get("SELECT * FROM tower_rooms WHERE id = ? AND floor_id = ?", [roomId, floorId]);
  if (!room) {
    return {
      ok: false as const,
      code: "ROOM_NOT_FOUND",
      message: "La sala solicitada no pertenece al piso indicado.",
    };
  }

  const template = await loadEncounterTemplate(db, roomId);
  const enemyPool = parseJson<unknown[]>(template.encounter?.enemy_pool_json, []);
  const enemyPoolRows = template.encounter?.enemy_pool_json ? await loadEnemyPool(db, template.encounter.enemy_pool_json) : [];
  const seed = input.seed?.trim() || crypto.randomUUID();

  const context = {
    playerId,
    gameMode: input.gameMode,
    seed,
    domainId,
    sectorId,
    floorId,
    roomId,
    domainName: domain.name,
    sectorNumber: sector.sector_number,
    floorNumber: floor.floor_number,
    roomNumber: room.room_number,
  };

  const encounter = {
    room: {
      id: room.id,
      roomNumber: room.room_number,
      roomType: room.room_type,
      encounterProfile: room.encounter_profile,
    },
    domain: {
      id: domain.id,
      name: domain.name,
      orderIndex: domain.order_index,
    },
    sector: {
      id: sector.id,
      sectorNumber: sector.sector_number,
      theme: sector.theme,
      difficulty: sector.difficulty,
      recommendedLevel: sector.recommended_level,
      lawAstral: sector.law_astral,
    },
    floor: {
      id: floor.id,
      floorNumber: floor.floor_number,
      difficulty: floor.difficulty,
      rewardProfile: floor.reward_profile,
      encounterProfile: floor.encounter_profile,
    },
    encounterTemplate: template.encounter ? {
      id: template.encounter.id,
      encounterProfile: template.encounter.encounter_profile,
      seedRule: template.encounter.seed_rule,
      modifierRule: template.encounter.modifier_rule,
    } : null,
    guardian: template.guardian ? {
      id: template.guardian.id,
      sonusMasterId: template.guardian.sonus_master_id,
      sonusName: template.guardian.sonus_name,
      sonusRarity: template.guardian.sonus_rarity,
      sonusElement: template.guardian.sonus_element,
      sonusClass: template.guardian.sonus_class,
      guardianType: template.guardian.guardian_type,
      unlockRule: template.guardian.unlock_rule,
    } : null,
    rewards: template.rewards,
    enemyPool,
    enemyPoolRecords: enemyPoolRows,
  };

  const simulationId = crypto.randomUUID();
  const nowIso = new Date().toISOString();

  await db.run(
    `INSERT INTO tower_simulations (
      simulation_id, player_id, game_mode, domain_id, sector_id, floor_id, room_id,
      status, seed, context_json, encounter_json, start_time, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, 'READY', ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
    [
      simulationId,
      playerId,
      input.gameMode,
      domainId,
      sectorId,
      floorId,
      roomId,
      seed,
      serializeJson(context),
      serializeJson(encounter),
    ]
  );

  return {
    ok: true as const,
    simulation: {
      simulationId,
      playerId,
      gameMode: input.gameMode,
      domainId,
      sectorId,
      floorId,
      roomId,
      status: "READY",
      seed,
      context,
      encounter,
      startTime: nowIso,
    },
  };
}

export async function getTowerSimulation(db: Database, playerId: string, simulationId: string) {
  const row = await db.get(
    `SELECT * FROM tower_simulations WHERE simulation_id = ? AND player_id = ?`,
    [simulationId, playerId]
  );

  if (!row) return null;
  return mapSimulationRow(row);
}

export async function listTowerSimulations(db: Database, playerId: string, limit = 20) {
  const safeLimit = Math.min(Math.max(Number(limit) || 20, 1), 100);
  const rows = await db.all(
    `SELECT * FROM tower_simulations WHERE player_id = ? ORDER BY start_time DESC LIMIT ?`,
    [playerId, safeLimit]
  );

  return rows.map(mapSimulationRow).filter((row): row is TowerSimulationRecord => row !== null);
}

export async function getTowerProgress(db: Database, playerId: string): Promise<TowerProgressResponse> {
  const progress = await db.get(
    `SELECT * FROM tower_player_progress WHERE player_id = ?`,
    [playerId]
  );

  const activeSimulationRow = await db.get(
    `SELECT * FROM tower_simulations WHERE player_id = ? AND status IN ('CREATED', 'READY', 'RUNNING') ORDER BY start_time DESC LIMIT 1`,
    [playerId]
  );

  const recentRows = await db.all(
    `SELECT * FROM tower_simulations WHERE player_id = ? ORDER BY start_time DESC LIMIT 10`,
    [playerId]
  );

  return {
    progress: progress ? {
      playerId: progress.player_id,
      highestDomainId: toNumberOrNull(progress.highest_domain_id),
      highestSectorId: toNumberOrNull(progress.highest_sector_id),
      highestFloorId: toNumberOrNull(progress.highest_floor_id),
      lastAttemptAt: progress.last_attempt_at ?? null,
      firstClearAt: progress.first_clear_at ?? null,
      statistics: parseJson<Record<string, unknown>>(progress.statistics_json, {}),
      updatedAt: progress.updated_at ?? null,
    } : null,
    activeSimulation: mapSimulationRow(activeSimulationRow),
    recentSimulations: recentRows.map(mapSimulationRow).filter((row): row is TowerSimulationRecord => row !== null),
  };
}

export async function abandonTowerSimulation(
  db: Database,
  playerId: string,
  simulationId: string,
  reason = "abandoned"
) {
  const existing = await db.get(
    `SELECT * FROM tower_simulations WHERE simulation_id = ? AND player_id = ?`,
    [simulationId, playerId]
  );
  if (!existing) {
    return {
      ok: false as const,
      code: "SIMULATION_NOT_FOUND",
      message: "La simulacion no existe o no pertenece al jugador.",
    };
  }

  if (existing.status === "COMPLETED" || existing.status === "ABANDONED" || existing.status === "FAILED") {
    return {
      ok: false as const,
      code: "SIMULATION_ALREADY_CLOSED",
      message: "La simulacion ya fue cerrada.",
    };
  }

  await db.run(
    `UPDATE tower_simulations
     SET status = 'ABANDONED',
         abandoned_reason = ?,
         end_time = CURRENT_TIMESTAMP,
         updated_at = CURRENT_TIMESTAMP
     WHERE simulation_id = ? AND player_id = ?`,
    [reason, simulationId, playerId]
  );

  return {
    ok: true as const,
    simulation: await getTowerSimulation(db, playerId, simulationId),
  };
}