import type { Pool } from 'pg';

import { pgAll, pgGet, pgRun } from '@/shared/db';

import type { NoteDraft, NoteFilter, NoteRecord } from './types';

interface Row {
  id: string;
  device_id: string;
  external_id: string;
  folder: string | null;
  name: string | null;
  body: string | null;
  modified_at: string | null;
  synced_at: string;
}

const hydrate = (r: Row): NoteRecord => ({
  id: r.id,
  deviceId: r.device_id,
  externalId: r.external_id,
  folder: r.folder,
  name: r.name,
  body: r.body,
  modifiedAt: r.modified_at,
  syncedAt: r.synced_at,
});

function wrap(err: unknown, op: string): Error {
  return new Error(`note/repo.${op} failed: ${err instanceof Error ? err.message : String(err)}`, {
    cause: err,
  });
}

export async function upsertNote(pool: Pool, draft: NoteDraft): Promise<void> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.notes
         (id, device_id, external_id, folder, name, body, modified_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       ON CONFLICT(device_id, external_id) DO UPDATE SET
         folder = excluded.folder,
         name = excluded.name,
         body = excluded.body,
         modified_at = excluded.modified_at,
         synced_at = now()`,
      [draft.id, draft.deviceId, draft.externalId, draft.folder, draft.name, draft.body, draft.modifiedAt],
    );
  } catch (err) {
    throw wrap(err, 'upsertNote');
  }
}

export async function listNotes(
  pool: Pool,
  filter: NoteFilter = {},
): Promise<readonly NoteRecord[]> {
  try {
    const clauses: string[] = [];
    const params: unknown[] = [];

    if (filter.deviceId) {
      clauses.push(`device_id = $${params.length + 1}`);
      params.push(filter.deviceId);
    }
    if (filter.folder) {
      clauses.push(`folder = $${params.length + 1}`);
      params.push(filter.folder);
    }

    const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
    const limit = filter.limit ?? 500;
    const offset = filter.offset ?? 0;

    const rows = await pgAll<Row>(
      pool,
      `SELECT * FROM macsync.notes ${where}
       ORDER BY COALESCE(modified_at, synced_at) DESC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      [...params, limit, offset],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw wrap(err, 'listNotes');
  }
}

export async function countNotes(pool: Pool, deviceId?: string): Promise<number> {
  try {
    if (deviceId) {
      const row = await pgGet<{ n: string }>(
        pool,
        'SELECT COUNT(*)::text as n FROM macsync.notes WHERE device_id = $1',
        [deviceId],
      );
      return Number(row?.n ?? 0);
    }
    const row = await pgGet<{ n: string }>(pool, 'SELECT COUNT(*)::text as n FROM macsync.notes');
    return Number(row?.n ?? 0);
  } catch (err) {
    throw wrap(err, 'countNotes');
  }
}

export async function getLastNoteSyncAt(pool: Pool, deviceId: string): Promise<string | null> {
  try {
    const row = await pgGet<{ last: string | null }>(
      pool,
      'SELECT MAX(synced_at) as last FROM macsync.notes WHERE device_id = $1',
      [deviceId],
    );
    return row?.last ?? null;
  } catch (err) {
    throw wrap(err, 'getLastNoteSyncAt');
  }
}
