import type { RowDataPacket } from "mysql2/promise";
import { createId } from "@/db/ids";
import { connectMysql, mysqlPool, sqlExecute, sqlQuery } from "./pool";
import { DEFAULT_COMMERCE, type CommerceSettings } from "@/services/commerce";

async function ensure() {
  try {
    mysqlPool();
  } catch {
    await connectMysql();
  }
}

export async function getAppSettingMysql<T = unknown>(key: string): Promise<T | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT value FROM app_settings WHERE \`key\` = ? LIMIT 1`,
    [key],
  );
  if (!rows[0]) return null;
  const raw = rows[0].value;
  if (typeof raw === "string") {
    try {
      return JSON.parse(raw) as T;
    } catch {
      return raw as T;
    }
  }
  return raw as T;
}

export async function upsertAppSettingMysql(key: string, value: unknown) {
  await ensure();
  const now = new Date();
  const existing = await sqlQuery<RowDataPacket[]>(
    `SELECT id FROM app_settings WHERE \`key\` = ? LIMIT 1`,
    [key],
  );
  const json = JSON.stringify(value);
  if (existing[0]) {
    await sqlExecute(
      `UPDATE app_settings SET value = CAST(? AS JSON), updated_at = ? WHERE \`key\` = ?`,
      [json, now, key],
    );
  } else {
    await sqlExecute(
      `INSERT INTO app_settings (id, \`key\`, value, created_at, updated_at)
       VALUES (?, ?, CAST(? AS JSON), ?, ?)`,
      [createId(), key, json, now, now],
    );
  }
  const { dualWriteAppSetting } = await import("@/db/dualWrite");
  await dualWriteAppSetting(key, value);
}

export async function getCommerceSettingsMysql(): Promise<CommerceSettings> {
  const value = await getAppSettingMysql<Partial<CommerceSettings>>("commerce");
  if (!value || typeof value !== "object") {
    await upsertAppSettingMysql("commerce", { ...DEFAULT_COMMERCE });
    return { ...DEFAULT_COMMERCE };
  }
  return { ...DEFAULT_COMMERCE, ...value };
}

export async function setCommerceSettingsMysql(
  next: CommerceSettings,
): Promise<CommerceSettings> {
  await upsertAppSettingMysql("commerce", next);
  return next;
}