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

import type { Conversation, ConversationDraft } from './types';

interface Row {
  id: string;
  device_id: string;
  external_id: string;
  display_name: string;
  is_group: boolean;
  participants: string[];
  last_message_at: string | null;
  message_count: number;
  synced_at: string;
}

const hydrate = (r: Row): Conversation => ({
  id: r.id,
  deviceId: r.device_id,
  externalId: r.external_id,
  displayName: r.display_name,
  isGroup: r.is_group,
  participants: r.participants,
  lastMessageAt: r.last_message_at,
  messageCount: r.message_count,
  syncedAt: r.synced_at,
});

export async function upsertConversation(pool: Pool, draft: ConversationDraft): Promise<Conversation> {
  await pgAll(
    pool,
    `INSERT INTO macsync.conversations (id, device_id, external_id, display_name, is_group, participants, last_message_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     ON CONFLICT(device_id, external_id) DO UPDATE SET
       display_name = excluded.display_name,
       participants = excluded.participants,
       last_message_at = COALESCE(excluded.last_message_at, conversations.last_message_at),
       synced_at = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.externalId,
      draft.displayName,
      draft.isGroup,
      JSON.stringify(draft.participants),
      draft.lastMessageAt ?? null,
    ],
  );
  const row = await pgGet<Row>(
    pool,
    'SELECT * FROM macsync.conversations WHERE device_id = $1 AND external_id = $2',
    [draft.deviceId, draft.externalId],
  );
  if (!row) throw notFound('conversation_not_found', `conversation ${draft.externalId} not found`);
  return hydrate(row);
}

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

export async function listConversations(pool: Pool, deviceId?: string): Promise<readonly Conversation[]> {
  const rows = deviceId
    ? await pgAll<Row>(
        pool,
        'SELECT * FROM macsync.conversations WHERE device_id = $1 ORDER BY last_message_at DESC NULLS LAST',
        [deviceId],
      )
    : await pgAll<Row>(pool, 'SELECT * FROM macsync.conversations ORDER BY last_message_at DESC NULLS LAST');
  return rows.map(hydrate);
}

export async function updateConversationStats(pool: Pool, id: string): Promise<void> {
  await pgRun(
    pool,
    `UPDATE macsync.conversations c
     SET message_count = (SELECT COUNT(*) FROM macsync.messages m WHERE m.conversation_id = c.id),
         last_message_at = (SELECT MAX(sent_at) FROM macsync.messages m WHERE m.conversation_id = c.id)
     WHERE c.id = $1`,
    [id],
  );
}

export interface ActiveConversation {
  readonly conversationId: string;
  readonly handle: string;
  readonly lastInboundAt: string;
  readonly inboundCount: number;
}

export async function listActiveConversations(
  pool: Pool,
  hours: number,
): Promise<readonly ActiveConversation[]> {
  const rows = await pgAll<{
    conversation_id: string;
    handle: string;
    last_inbound_at: string;
    inbound_count: string;
  }>(
    pool,
    `WITH inbound AS (
       SELECT m.conversation_id,
              m.from_handle,
              m.sent_at,
              ROW_NUMBER() OVER (PARTITION BY m.conversation_id ORDER BY m.sent_at DESC) AS rn
       FROM macsync.messages m
       WHERE m.is_from_me = false
         AND m.from_handle <> ''
         AND m.sent_at > now() - ($1 || ' hours')::interval
     )
     SELECT conversation_id,
            MAX(from_handle) FILTER (WHERE rn = 1) AS handle,
            MAX(sent_at)     FILTER (WHERE rn = 1) AS last_inbound_at,
            COUNT(*)::text                         AS inbound_count
     FROM inbound
     GROUP BY conversation_id
     ORDER BY MAX(sent_at) FILTER (WHERE rn = 1) DESC NULLS LAST`,
    [String(hours)],
  );
  return rows.map((r) => ({
    conversationId: r.conversation_id,
    handle: r.handle,
    lastInboundAt: r.last_inbound_at,
    inboundCount: Number(r.inbound_count),
  }));
}
