/**
 * db.mjs — PostgreSQL接続モジュール（IMP-30 強化版）
 *
 * DATABASE_URL 環境変数が設定されている場合はPostgreSQLを使用。
 * 未設定の場合は null を返し、server.mjs側でJSONファイルにフォールバック。
 *
 * 使用方法:
 *   import { pool, dbSelect, dbSelectOne, dbCount, dbInsert, dbUpdate, dbDelete } from "./db.mjs";
 *   const rows = await dbSelect("cremation_permit.cremation_permit");
 *   const rows = await dbSelect("table", { status: "active" }, { limit: 50, offset: 0 });
 *   const total = await dbCount("table", { status: "active" });
 */

// pg はオプション依存。DATABASE_URL未設定時はimportしない。
let pg = null;
export let pool = null;

if (process.env.DATABASE_URL) {
  try {
    // Dynamic import so the server still starts without pg installed.
    // 変数経由のspecifierにすることで、esbuild等のバンドラが "pg" を解決しようとして
    // 失敗するのを防ぐ（DATABASE_URL未設定時はこの行に到達しない）。
    const pgSpecifier = "pg";
    pg = await import(pgSpecifier);
    const { Pool } = pg.default ?? pg;
    pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      ssl: process.env.DATABASE_SSL === "true" ? { rejectUnauthorized: false } : false,
      max: 10,
      idleTimeoutMillis: 30000,
    });
    // テスト接続
    await pool.query("SELECT 1");
    console.log("[db] PostgreSQL connected:", process.env.DATABASE_URL.replace(/:\/\/[^@]+@/, "://***@"));
  } catch (err) {
    console.warn("[db] PostgreSQL connection failed, falling back to JSON:", err.message);
    pool = null;
  }
}

/**
 * 全行取得 (SELECT * ORDER BY created_at DESC)
 * @param {string} table - スキーマ付きテーブル名 e.g. "cremation_permit.cremation_permit"
 * @param {object} filter - WHERE条件 e.g. { status: "applied" }
 * @param {{ limit?: number, offset?: number, orderBy?: string }} opts - ページネーション・ソート
 * @returns {object[]|null} null = JSON fallback を使うべき
 */
export async function dbSelect(table, filter = {}, { limit, offset, orderBy = "created_at DESC" } = {}) {
  if (!pool) return null;
  const keys = Object.keys(filter);
  const vals = [];
  const where = keys.length
    ? "WHERE " + keys.map((k) => { vals.push(filter[k]); return `${toSnake(k)} = $${vals.length}`; }).join(" AND ")
    : "";
  let sql = `SELECT * FROM ${table} ${where} ORDER BY ${orderBy}`;
  if (limit != null) { vals.push(limit); sql += ` LIMIT $${vals.length}`; }
  if (offset != null) { vals.push(offset); sql += ` OFFSET $${vals.length}`; }
  const { rows } = await pool.query(sql, vals);
  return rows.map(camelify);
}

/**
 * 1行取得
 * @param {string} table
 * @param {string} id
 * @returns {object|null}
 */
export async function dbSelectOne(table, id) {
  if (!pool) return null;
  const { rows } = await pool.query(`SELECT * FROM ${table} WHERE id = $1`, [id]);
  return rows[0] ? camelify(rows[0]) : null;
}

/**
 * 件数取得 (COUNT)
 * @param {string} table
 * @param {object} filter - WHERE条件
 * @returns {number|null} null = JSON fallback を使うべき
 */
export async function dbCount(table, filter = {}) {
  if (!pool) return null;
  const keys = Object.keys(filter);
  const vals = [];
  const where = keys.length
    ? "WHERE " + keys.map((k) => { vals.push(filter[k]); return `${toSnake(k)} = $${vals.length}`; }).join(" AND ")
    : "";
  const { rows } = await pool.query(`SELECT COUNT(*) FROM ${table} ${where}`, vals);
  return Number(rows[0].count);
}

/**
 * INSERT (RETURNING *)
 * @param {string} table
 * @param {object} record - camelCase keys
 * @returns {object|null} null = JSON fallback
 */
export async function dbInsert(table, record) {
  if (!pool) return null;
  const entries = Object.entries(record).filter(([, v]) => v !== undefined);
  const cols = entries.map(([k]) => toSnake(k));
  const vals = entries.map(([, v]) => v instanceof Date ? v.toISOString() : v);
  const placeholders = vals.map((_, i) => `$${i + 1}`).join(", ");
  const { rows } = await pool.query(
    `INSERT INTO ${table} (${cols.join(", ")}) VALUES (${placeholders}) RETURNING *`,
    vals,
  );
  return camelify(rows[0]);
}

/**
 * UPDATE (RETURNING *) — id/createdAt/createdBy は上書き不可
 * @param {string} table
 * @param {string} id
 * @param {object} updates - camelCase keys
 * @returns {object|null} null = JSON fallback
 */
export async function dbUpdate(table, id, updates) {
  if (!pool) return null;
  // IMP-30: immutable fields are never written back to DB
  const IMMUTABLE = new Set(["id", "createdAt", "created_at", "createdBy", "created_by"]);
  const entries = Object.entries(updates).filter(([k, v]) => v !== undefined && !IMMUTABLE.has(k));
  if (!entries.length) return dbSelectOne(table, id);
  const set = entries.map(([k], i) => `${toSnake(k)} = $${i + 1}`).join(", ");
  const { rows } = await pool.query(
    `UPDATE ${table} SET ${set} WHERE id = $${entries.length + 1} RETURNING *`,
    [...entries.map(([, v]) => v instanceof Date ? v.toISOString() : v), id],
  );
  return rows[0] ? camelify(rows[0]) : null;
}

/**
 * IMP-53: JSONB payload 全文検索 (PostgreSQL ILIKE)
 * @param {string} table - スキーマ付きテーブル名
 * @param {object} filter - WHERE条件 e.g. { type: "report" }
 * @param {string} searchText - 検索キーワード（部分一致）
 * @param {{ limit?: number, offset?: number, orderBy?: string }} opts
 * @returns {object[]|null} null = JSON fallback を使うべき
 */
export async function dbSearchJson(table, filter = {}, searchText = "", { limit, offset, orderBy = "created_at DESC" } = {}) {
  if (!pool) return null;
  if (!searchText) return dbSelect(table, filter, { limit, offset, orderBy });
  const keys = Object.keys(filter);
  const vals = [];
  const conditions = keys.map((k) => { vals.push(filter[k]); return `${toSnake(k)} = $${vals.length}`; });
  vals.push(`%${searchText.toLowerCase()}%`);
  conditions.push(`lower(payload::text) LIKE $${vals.length}`);
  const where = "WHERE " + conditions.join(" AND ");
  let sql = `SELECT * FROM ${table} ${where} ORDER BY ${orderBy}`;
  if (limit != null) { vals.push(limit); sql += ` LIMIT $${vals.length}`; }
  if (offset != null) { vals.push(offset); sql += ` OFFSET $${vals.length}`; }
  const { rows } = await pool.query(sql, vals);
  return rows.map(camelify);
}

/**
 * Q-02: ページネーション + 件数を 1 クエリで取得 (COUNT(*) OVER())
 * @param {string} table
 * @param {object} filter
 * @param {{ limit?: number, offset?: number, orderBy?: string }} opts
 * @returns {{ data: object[], total: number }|null} null = JSON fallback
 */
export async function dbSelectPaged(table, filter = {}, { limit, offset, orderBy = "created_at DESC" } = {}) {
  if (!pool) return null;
  const keys = Object.keys(filter);
  const vals = [];
  const where = keys.length
    ? "WHERE " + keys.map((k) => { vals.push(filter[k]); return `${toSnake(k)} = $${vals.length}`; }).join(" AND ")
    : "";
  let sql = `SELECT *, COUNT(*) OVER() AS __total FROM ${table} ${where} ORDER BY ${orderBy}`;
  if (limit != null) { vals.push(limit); sql += ` LIMIT $${vals.length}`; }
  if (offset != null) { vals.push(offset); sql += ` OFFSET $${vals.length}`; }
  const { rows } = await pool.query(sql, vals);
  const total = rows.length ? Number(rows[0].__total) : (await dbCount(table, filter)) ?? 0;
  const data = rows.map(({ __total, ...rest }) => camelify(rest));
  return { data, total };
}

/**
 * Q-02: JSONB payload 全文検索 + 件数を 1 クエリで取得
 */
export async function dbSearchJsonPaged(table, filter = {}, searchText = "", { limit, offset, orderBy = "created_at DESC" } = {}) {
  if (!pool) return null;
  if (!searchText) return dbSelectPaged(table, filter, { limit, offset, orderBy });
  const keys = Object.keys(filter);
  const vals = [];
  const conditions = keys.map((k) => { vals.push(filter[k]); return `${toSnake(k)} = $${vals.length}`; });
  vals.push(`%${searchText.toLowerCase()}%`);
  conditions.push(`lower(payload::text) LIKE $${vals.length}`);
  const where = "WHERE " + conditions.join(" AND ");
  let sql = `SELECT *, COUNT(*) OVER() AS __total FROM ${table} ${where} ORDER BY ${orderBy}`;
  if (limit != null) { vals.push(limit); sql += ` LIMIT $${vals.length}`; }
  if (offset != null) { vals.push(offset); sql += ` OFFSET $${vals.length}`; }
  const { rows } = await pool.query(sql, vals);
  const total = rows.length ? Number(rows[0].__total) : 0;
  const data = rows.map(({ __total, ...rest }) => camelify(rest));
  return { data, total };
}

/**
 * DELETE (by id)
 * @param {string} table
 * @param {string} id
 * @returns {boolean|null} true = deleted, null = JSON fallback
 */
export async function dbDelete(table, id) {
  if (!pool) return null;
  await pool.query(`DELETE FROM ${table} WHERE id = $1`, [id]);
  return true;
}

// ── 内部ユーティリティ ───────────────────────────────────────────────────

/** camelCase → snake_case */
function toSnake(s) {
  return s.replace(/([A-Z])/g, "_$1").toLowerCase();
}

/** PostgreSQL row (snake_case) → JS object (camelCase) */
function camelify(row) {
  const out = {};
  for (const [k, v] of Object.entries(row)) {
    const key = k.replace(/_([a-z])/g, (_, c) => c.toUpperCase());
    // Date → ISO string
    out[key] = v instanceof Date ? v.toISOString() : v;
  }
  return out;
}
