import type { Pool } from 'pg';
import { pgAll, pgRun } from '@/shared/db';
import type { EventDraft, EventFilter, EventRecord, EventSearchHit } from './types';

interface Row {
  id: string;
  device_id: string;
  calendar_id: string | null;
  external_id: string;
  title: string;
  notes: string | null;
  location: string | null;
  url: string | null;
  is_all_day: boolean;
  start_date: string;
  end_date: string;
  status: string;
  availability: string;
  is_detached: boolean;
  has_recurrence: boolean;
  recurrence_rule: string | null;
  organizer_email: string | null;
  organizer_name: string | null;
  attendee_count: number;
  created_at: string | null;
  modified_at: string | null;
  synced_at: string;
}

const hydrate = (r: Row): EventRecord => ({
  id: r.id,
  deviceId: r.device_id,
  calendarId: r.calendar_id,
  externalId: r.external_id,
  title: r.title,
  notes: r.notes,
  location: r.location,
  url: r.url,
  isAllDay: r.is_all_day,
  startDate: r.start_date,
  endDate: r.end_date,
  status: r.status,
  availability: r.availability,
  isDetached: r.is_detached,
  hasRecurrence: r.has_recurrence,
  recurrenceRule: r.recurrence_rule,
  organizerEmail: r.organizer_email,
  organizerName: r.organizer_name,
  attendeeCount: r.attendee_count,
  createdAt: r.created_at,
  modifiedAt: r.modified_at,
  syncedAt: r.synced_at,
});

export async function upsertEvent(pool: Pool, draft: EventDraft): Promise<void> {
  await pgRun(
    pool,
    `INSERT INTO macsync.events
    (id, device_id, calendar_id, external_id, title, notes, location, url,
     is_all_day, start_date, end_date, status, availability,
     is_detached, has_recurrence, recurrence_rule,
     organizer_email, organizer_name, attendee_count, created_at, modified_at)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
    ON CONFLICT(device_id, external_id) DO UPDATE SET
      title = excluded.title,
      notes = excluded.notes,
      location = excluded.location,
      url = excluded.url,
      is_all_day = excluded.is_all_day,
      start_date = excluded.start_date,
      end_date = excluded.end_date,
      status = excluded.status,
      availability = excluded.availability,
      is_detached = excluded.is_detached,
      has_recurrence = excluded.has_recurrence,
      recurrence_rule = excluded.recurrence_rule,
      organizer_email = excluded.organizer_email,
      organizer_name = excluded.organizer_name,
      attendee_count = excluded.attendee_count,
      modified_at = excluded.modified_at,
      synced_at = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.calendarId,
      draft.externalId,
      draft.title,
      draft.notes,
      draft.location,
      draft.url,
      draft.isAllDay,
      draft.startDate,
      draft.endDate,
      draft.status,
      draft.availability,
      draft.isDetached,
      draft.hasRecurrence,
      draft.recurrenceRule,
      draft.organizerEmail,
      draft.organizerName,
      draft.attendeeCount,
      draft.createdAt,
      draft.modifiedAt,
    ],
  );
}

export async function listEvents(pool: Pool, filter: EventFilter = {}): Promise<readonly EventRecord[]> {
  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.startAfter) {
    clauses.push(`start_date >= $${params.length + 1}`);
    params.push(filter.startAfter);
  }
  if (filter.startBefore) {
    clauses.push(`start_date <= $${params.length + 1}`);
    params.push(filter.startBefore);
  }

  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.events ${where} ORDER BY start_date ASC LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
    [...params, limit, offset],
  );
  return rows.map(hydrate);
}

export async function countEvents(pool: Pool): Promise<number> {
  const row = await pgAll<{ n: number }>(pool, 'SELECT COUNT(*) as n FROM macsync.events');
  return row[0]?.n ?? 0;
}

const SEARCH_LIMIT_DEFAULT = 50;
const SEARCH_LIMIT_MAX = 200;

export async function searchEvents(
  pool: Pool,
  query: string,
  opts: { deviceId?: string; limit?: number } = {},
): Promise<readonly EventSearchHit[]> {
  const limit = Math.min(opts.limit ?? SEARCH_LIMIT_DEFAULT, SEARCH_LIMIT_MAX);
  const conditions: string[] = ['e.search_tsv @@ q'];
  const params: unknown[] = [query];

  if (opts.deviceId) {
    conditions.push(`e.device_id = $${params.length + 1}`);
    params.push(opts.deviceId);
  }

  const where = `WHERE ${conditions.join(' AND ')}`;

  const rows = await pgAll<Row & { rank: number; snippet: string }>(
    pool,
    `WITH q AS (
       SELECT CASE
         WHEN array_length(string_to_array(trim($1), ' '), 1) = 1
         THEN to_tsquery('simple', trim($1) || ':*')
         ELSE websearch_to_tsquery('english', $1)
       END AS q
     )
     SELECT e.*,
       ts_rank(e.search_tsv, q.q) AS rank,
       ts_headline('english',
         coalesce(e.title, '') || ' ' || coalesce(e.notes, '') || ' ' || coalesce(e.location, ''),
         q.q,
         'StartSel=«,StopSel=»,MaxFragments=2,MaxWords=12,MinWords=3'
       ) AS snippet
     FROM macsync.events e CROSS JOIN q
     ${where}
     ORDER BY rank DESC, e.start_date ASC
     LIMIT $${params.length + 1}`,
    [...params, limit],
  );

  return rows.map((r) => ({
    event: hydrate(r),
    rank: Number(r.rank),
    snippet: r.snippet,
  }));
}
