import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { createId } from "@/db/ids";
import { dualWriteCitySoftDelete, dualWriteCityUpsert } from "@/db/dualWrite";
import { connectMysql, mysqlPool, sqlExecute, sqlQuery, withMysqlTxn } from "./pool";

export type CityRow = {
  id: string;
  nameAr: string;
  nameEn: string;
  slug: string;
  country: string;
  region: string;
  isTourist: boolean;
  aliases: string[];
  blurbEn: string;
  blurbAr: string;
  imageUrl: string | null;
  deletedAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
};

export type CityWriteInput = {
  nameAr: string;
  nameEn: string;
  slug: string;
  country?: string;
  region?: string;
  isTourist?: boolean;
  aliases?: string[];
  blurbEn?: string;
  blurbAr?: string;
  imageUrl?: string | null;
};

function mapCity(r: RowDataPacket, aliases: string[] = []): CityRow {
  return {
    id: String(r.id),
    nameAr: String(r.name_ar),
    nameEn: String(r.name_en),
    slug: String(r.slug),
    country: String(r.country ?? "Tunisia"),
    region: String(r.region ?? "Tunisia"),
    isTourist: Boolean(r.is_tourist),
    aliases,
    blurbEn: String(r.blurb_en ?? ""),
    blurbAr: String(r.blurb_ar ?? ""),
    imageUrl: r.image_url == null ? null : String(r.image_url),
    deletedAt: r.deleted_at ? new Date(r.deleted_at) : null,
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

/** Ensure pool is up without requiring global ACTIVE_DATABASE=mysql. */
export async function ensureCitiesMysql() {
  try {
    mysqlPool();
  } catch {
    await connectMysql();
  }
}

async function loadAliases(cityIds: string[]): Promise<Map<string, string[]>> {
  const map = new Map<string, string[]>();
  if (cityIds.length === 0) return map;
  const placeholders = cityIds.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT city_id, alias FROM city_aliases WHERE city_id IN (${placeholders})`,
    cityIds,
  );
  for (const r of rows) {
    const id = String(r.city_id);
    const list = map.get(id) || [];
    list.push(String(r.alias));
    map.set(id, list);
  }
  return map;
}

async function replaceAliases(cityId: string, aliases: string[]) {
  const unique = [...new Set(aliases.map((a) => a.trim()).filter(Boolean))];
  await sqlExecute(`DELETE FROM city_aliases WHERE city_id = ?`, [cityId]);
  for (const alias of unique) {
    await sqlExecute(
      `INSERT INTO city_aliases (city_id, alias) VALUES (?, ?)
       ON DUPLICATE KEY UPDATE alias = VALUES(alias)`,
      [cityId, alias],
    );
  }
  return unique;
}

export async function findCitiesByQueryMysql(query: string): Promise<CityRow[]> {
  await ensureCitiesMysql();
  const trimmed = query.trim();
  if (!trimmed) return [];
  const like = `%${trimmed}%`;
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT DISTINCT c.*
     FROM cities c
     LEFT JOIN city_aliases a ON a.city_id = c.id
     WHERE c.deleted_at IS NULL
       AND (
         c.name_en LIKE ? OR c.name_ar LIKE ? OR c.slug LIKE ? OR a.alias LIKE ?
       )
     ORDER BY c.is_tourist DESC, c.name_en ASC`,
    [like, like, like, like],
  );
  const ids = rows.map((r) => String(r.id));
  const aliases = await loadAliases(ids);
  return rows.map((r) => mapCity(r, aliases.get(String(r.id)) || []));
}

export async function listCitiesMysql(opts?: {
  touristOnly?: boolean;
}): Promise<CityRow[]> {
  await ensureCitiesMysql();
  const rows = await sqlQuery<RowDataPacket[]>(
    opts?.touristOnly
      ? `SELECT * FROM cities WHERE deleted_at IS NULL AND is_tourist = 1 ORDER BY is_tourist DESC, name_en ASC`
      : `SELECT * FROM cities WHERE deleted_at IS NULL ORDER BY is_tourist DESC, name_en ASC`,
  );
  const aliases = await loadAliases(rows.map((r) => String(r.id)));
  return rows.map((r) => mapCity(r, aliases.get(String(r.id)) || []));
}

export async function findCitiesByIdsMysql(ids: string[]): Promise<CityRow[]> {
  await ensureCitiesMysql();
  if (ids.length === 0) return [];
  const ph = ids.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM cities WHERE id IN (${ph}) AND deleted_at IS NULL`,
    ids,
  );
  const aliases = await loadAliases(rows.map((r) => String(r.id)));
  return rows.map((r) => mapCity(r, aliases.get(String(r.id)) || []));
}

export async function findCityByIdMysql(id: string): Promise<CityRow | null> {
  await ensureCitiesMysql();
  const rows = await sqlQuery<RowDataPacket[]>(`SELECT * FROM cities WHERE id = ? LIMIT 1`, [id]);
  if (!rows[0]) return null;
  const aliases = await loadAliases([id]);
  return mapCity(rows[0], aliases.get(id) || []);
}

export async function createCityMysql(input: CityWriteInput): Promise<CityRow> {
  await ensureCitiesMysql();
  const id = createId();
  const now = new Date();
  const aliases = input.aliases || [];
  await withMysqlTxn(async (conn) => {
    await conn.execute<ResultSetHeader>(
      `INSERT INTO cities (
        id, name_ar, name_en, slug, country, region, is_tourist, blurb_en, blurb_ar,
        image_url, deleted_at, created_at, updated_at
      ) VALUES (?,?,?,?,?,?,?,?,?,?,NULL,?,?)`,
      [
        id,
        input.nameAr,
        input.nameEn,
        input.slug,
        input.country ?? "Tunisia",
        input.region ?? input.country ?? "Tunisia",
        input.isTourist === false ? 0 : 1,
        input.blurbEn ?? "",
        input.blurbAr ?? "",
        input.imageUrl ?? null,
        now,
        now,
      ] as never[],
    );
    const unique = [...new Set(aliases.map((a) => a.trim()).filter(Boolean))];
    for (const alias of unique) {
      await conn.execute(`INSERT INTO city_aliases (city_id, alias) VALUES (?, ?)`, [
        id,
        alias,
      ] as never[]);
    }
  });
  const created = await findCityByIdMysql(id);
  if (!created) throw new Error("Failed to load created city");
  await dualWriteCityUpsert(created);
  return created;
}

export async function updateCityMysql(
  id: string,
  input: Partial<CityWriteInput>,
): Promise<CityRow | null> {
  await ensureCitiesMysql();
  const existing = await findCityByIdMysql(id);
  if (!existing || existing.deletedAt) return null;

  const sets: string[] = [];
  const params: unknown[] = [];
  const map: Array<[keyof CityWriteInput, string]> = [
    ["nameAr", "name_ar"],
    ["nameEn", "name_en"],
    ["slug", "slug"],
    ["country", "country"],
    ["region", "region"],
    ["blurbEn", "blurb_en"],
    ["blurbAr", "blurb_ar"],
  ];
  for (const [key, col] of map) {
    if (input[key] !== undefined) {
      sets.push(`\`${col}\` = ?`);
      params.push(input[key]);
    }
  }
  if (input.isTourist !== undefined) {
    sets.push(`is_tourist = ?`);
    params.push(input.isTourist ? 1 : 0);
  }
  if (input.imageUrl !== undefined) {
    sets.push(`image_url = ?`);
    params.push(input.imageUrl);
  }
  sets.push(`updated_at = ?`);
  params.push(new Date());
  params.push(id);

  await withMysqlTxn(async (conn) => {
    if (sets.length > 1) {
      await conn.execute(
        `UPDATE cities SET ${sets.join(", ")} WHERE id = ?`,
        params as never[],
      );
    }
    if (input.aliases !== undefined) {
      await conn.execute(`DELETE FROM city_aliases WHERE city_id = ?`, [id] as never[]);
      const unique = [...new Set(input.aliases.map((a) => a.trim()).filter(Boolean))];
      for (const alias of unique) {
        await conn.execute(`INSERT INTO city_aliases (city_id, alias) VALUES (?, ?)`, [
          id,
          alias,
        ] as never[]);
      }
    }
  });

  const updated = await findCityByIdMysql(id);
  if (updated) {
    await dualWriteCityUpsert(updated);
  }
  return updated;
}

export async function softDeleteCityMysql(id: string): Promise<boolean> {
  await ensureCitiesMysql();
  const deletedAt = new Date();
  const res = await sqlExecute(
    `UPDATE cities SET deleted_at = ?, updated_at = ? WHERE id = ? AND deleted_at IS NULL`,
    [deletedAt, deletedAt, id],
  );
  if (res.affectedRows > 0) {
    await dualWriteCitySoftDelete(id, deletedAt);
  }
  return res.affectedRows > 0;
}

/** @deprecated internal helper kept for callers that already have aliases list */
export async function setCityAliasesMysql(cityId: string, aliases: string[]) {
  await ensureCitiesMysql();
  return replaceAliases(cityId, aliases);
}

export function cityRowToApi(c: CityRow) {
  return {
    _id: c.id,
    id: c.id,
    nameAr: c.nameAr,
    nameEn: c.nameEn,
    name: c.nameEn,
    slug: c.slug,
    country: c.country,
    region: c.region,
    isTourist: c.isTourist,
    aliases: c.aliases,
    blurbEn: c.blurbEn,
    blurbAr: c.blurbAr,
    imageUrl: c.imageUrl,
    deletedAt: c.deletedAt,
    createdAt: c.createdAt,
    updatedAt: c.updatedAt,
  };
}