import type { Pool } from 'pg';
import { pgGet, pgAll, pgRun } from '@/shared/db';
import { notFound } from '@/shared/http/errors';

import type {
  Contact,
  ContactDraft,
  ContactMatchField,
  ContactMatchStrength,
  ContactSearchFilter,
  ContactSearchHit,
  ContactSummaryData,
} from './types';

interface Row {
  id: string;
  device_id: string;
  handle: string;
  display_name: string;
  phone_numbers: string[];
  emails: string[];
  raw_data: Record<string, unknown>;
  synced_at: string;
  updated_at: string;
  summary_data: ContactSummaryData | null;
  summary_generated_at: string | null;
}

const hydrate = (r: Row): Contact => ({
  id: r.id,
  deviceId: r.device_id,
  handle: r.handle,
  displayName: r.display_name,
  phoneNumbers: r.phone_numbers,
  emails: r.emails,
  rawData: JSON.stringify(r.raw_data),
  syncedAt: r.synced_at,
  updatedAt: r.updated_at,
  summaryData: r.summary_data ?? null,
  summaryGeneratedAt: r.summary_generated_at ?? null,
});

export async function upsertContact(pool: Pool, draft: ContactDraft): Promise<Contact> {
  await pgAll(
    pool,
    `INSERT INTO macsync.contacts (id, device_id, handle, display_name, phone_numbers, emails, raw_data)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     ON CONFLICT(device_id, handle) DO UPDATE SET
       display_name = excluded.display_name,
       phone_numbers = excluded.phone_numbers,
       emails = excluded.emails,
       raw_data = excluded.raw_data,
       updated_at = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.handle,
      draft.displayName,
      JSON.stringify(draft.phoneNumbers),
      JSON.stringify(draft.emails),
      draft.rawData ?? '{}',
    ],
  );
  // Fetch by natural key — ON CONFLICT preserves the original row's id.
  const row = await pgGet<Row>(pool, 'SELECT * FROM macsync.contacts WHERE device_id = $1 AND handle = $2', [draft.deviceId, draft.handle]);
  if (!row) throw notFound('contact_not_found', `contact ${draft.handle} not found after upsert`);
  return hydrate(row);
}

export async function getContact(pool: Pool, id: string): Promise<Contact> {
  const row = await pgGet<Row>(pool, 'SELECT * FROM macsync.contacts WHERE id = $1', [id]);
  if (!row) throw notFound('contact_not_found', `contact ${id} not found`);
  return hydrate(row);
}

export async function listContactsByDevice(pool: Pool, deviceId: string): Promise<readonly Contact[]> {
  const rows = await pgAll<Row>(pool, 'SELECT * FROM macsync.contacts WHERE device_id = $1 ORDER BY display_name ASC', [deviceId]);
  return rows.map(hydrate);
}

export async function listAllContacts(pool: Pool): Promise<readonly Contact[]> {
  const rows = await pgAll<Row>(pool, 'SELECT * FROM macsync.contacts ORDER BY display_name ASC');
  return rows.map(hydrate);
}

export async function countContactsByDevice(pool: Pool, deviceId: string): Promise<number> {
  const row = await pgGet<{ n: number }>(
    pool,
    'SELECT COUNT(*) as n FROM macsync.contacts WHERE device_id = $1',
    [deviceId],
  );
  return row?.n ?? 0;
}

interface ContactSearchRow extends Row {
  match_field: ContactMatchField;
  match_strength: ContactMatchStrength;
  rank: number;
}

const CONTACT_SEARCH_LIMIT_DEFAULT = 30;
const CONTACT_SEARCH_LIMIT_MAX = 200;

/**
 * Substring-search contacts by display_name, handle, phone, or email.
 *
 * Ranks exact match > prefix match > substring match, with display_name
 * weighted higher than handle/phone/email. The numeric rank is in [0, 1] —
 * meant to be combined with message/mail/calendar surfaces by the unified
 * search service, which gives the contact arm a higher weight so contact
 * hits outrank body-mention hits at equivalent ranks.
 *
 * Phone matching is digit-normalized so "415-555-1212", "(415) 555 1212",
 * and "+14155551212" all match the same query.
 */
export async function searchContacts(
  pool: Pool,
  filter: ContactSearchFilter,
): Promise<readonly ContactSearchHit[]> {
  const queryRaw = filter.query.trim();
  if (queryRaw.length === 0) return [];

  const limit = Math.min(filter.limit ?? CONTACT_SEARCH_LIMIT_DEFAULT, CONTACT_SEARCH_LIMIT_MAX);
  const queryDigits = queryRaw.replace(/\D+/g, '');

  // Params: $1 query, $2 lower-prefix, $3 lower-substring, $4 digits-substring, [$5 deviceId,] $N limit
  const params: unknown[] = [
    queryRaw,
    `${queryRaw.toLowerCase()}%`,
    `%${queryRaw.toLowerCase()}%`,
    queryDigits.length > 0 ? `%${queryDigits}%` : null,
  ];

  const deviceClause = filter.deviceId
    ? (() => {
        params.push(filter.deviceId);
        return `AND device_id = $${params.length}`;
      })()
    : '';

  params.push(limit);
  const limitParam = `$${params.length}`;

  // The CASE branches are evaluated in order — the first matching one wins
  // (display_name exact > display_name prefix > display_name substring >
  //  handle exact > phone digits > email substring > handle/email substring).
  const sql = `
    WITH scored AS (
      SELECT c.*,
        CASE
          WHEN lower(c.display_name) = lower($1)                       THEN 1.00
          WHEN lower(c.display_name) LIKE $2                           THEN 0.85
          WHEN lower(c.display_name) LIKE $3                           THEN 0.70
          WHEN lower(c.handle)       = lower($1)                       THEN 0.65
          WHEN $4 IS NOT NULL AND c.phone_numbers::text LIKE $4        THEN 0.55
          WHEN c.emails::text         ILIKE $3                         THEN 0.50
          WHEN lower(c.handle)       LIKE $3                           THEN 0.40
          ELSE 0
        END AS rank,
        CASE
          WHEN lower(c.display_name) = lower($1)                       THEN 'display_name'
          WHEN lower(c.display_name) LIKE $2                           THEN 'display_name'
          WHEN lower(c.display_name) LIKE $3                           THEN 'display_name'
          WHEN lower(c.handle)       = lower($1)                       THEN 'handle'
          WHEN $4 IS NOT NULL AND c.phone_numbers::text LIKE $4        THEN 'phone'
          WHEN c.emails::text         ILIKE $3                         THEN 'email'
          WHEN lower(c.handle)       LIKE $3                           THEN 'handle'
          ELSE 'handle'
        END AS match_field,
        CASE
          WHEN lower(c.display_name) = lower($1) OR lower(c.handle) = lower($1) THEN 'exact'
          WHEN lower(c.display_name) LIKE $2                                    THEN 'prefix'
          ELSE 'substring'
        END AS match_strength
      FROM macsync.contacts c
      WHERE TRUE ${deviceClause}
    )
    SELECT * FROM scored
    WHERE rank > 0
    ORDER BY rank DESC, lower(display_name) ASC
    LIMIT ${limitParam}
  `;

  const rows = await pgAll<ContactSearchRow>(pool, sql, params);
  return rows.map((r) => ({
    contact: hydrate(r),
    matchField: r.match_field,
    matchStrength: r.match_strength,
    rank: Number(r.rank),
  }));
}

export async function updateContactSummary(
  pool: Pool,
  id: string,
  summaryData: ContactSummaryData,
): Promise<void> {
  await pgRun(
    pool,
    `UPDATE macsync.contacts
     SET summary_data = $1, summary_generated_at = now()
     WHERE id = $2`,
    [JSON.stringify(summaryData), id],
  );
}
