import type { Pool } from 'pg';

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

import type { ReminderDraft, ReminderFilter, ReminderRecord } from './types';

interface Row {
  id: string;
  device_id: string;
  external_id: string;
  calendar_id: string;
  title: string | null;
  notes: string | null;
  due_date: string | null;
  priority: number | null;
  is_completed: boolean;
  created_at: string;
  modified_at: string | null;
  synced_at: string;
}

const hydrate = (r: Row): ReminderRecord => ({
  id: r.id,
  deviceId: r.device_id,
  externalId: r.external_id,
  calendarId: r.calendar_id,
  title: r.title,
  notes: r.notes,
  dueDate: r.due_date,
  priority: r.priority,
  isCompleted: r.is_completed,
  createdAt: r.created_at,
  modifiedAt: r.modified_at,
  syncedAt: r.synced_at,
});

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

export async function upsertReminder(pool: Pool, draft: ReminderDraft): Promise<void> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.reminders
         (id, device_id, external_id, calendar_id, title, notes,
          due_date, priority, is_completed, modified_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       ON CONFLICT(device_id, external_id) DO UPDATE SET
         calendar_id = excluded.calendar_id,
         title = excluded.title,
         notes = excluded.notes,
         due_date = excluded.due_date,
         priority = excluded.priority,
         is_completed = excluded.is_completed,
         modified_at = excluded.modified_at,
         synced_at = now()`,
      [
        draft.id,
        draft.deviceId,
        draft.externalId,
        draft.calendarId,
        draft.title,
        draft.notes,
        draft.dueDate,
        draft.priority,
        draft.isCompleted,
        draft.modifiedAt,
      ],
    );
  } catch (err) {
    throw wrap(err, 'upsertReminder');
  }
}

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

    if (filter.deviceId) {
      clauses.push(`device_id = $${params.length + 1}`);
      params.push(filter.deviceId);
    }
    if (filter.calendarId) {
      clauses.push(`calendar_id = $${params.length + 1}`);
      params.push(filter.calendarId);
    }
    if (filter.isCompleted !== undefined) {
      clauses.push(`is_completed = $${params.length + 1}`);
      params.push(filter.isCompleted);
    }

    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.reminders ${where}
       ORDER BY COALESCE(due_date, modified_at, created_at) DESC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      [...params, limit, offset],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw wrap(err, 'listReminders');
  }
}

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

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