import type { Pool } from 'pg';
import { pgGet, pgAll, pgRun } from '@/shared/db';
import type { Prospect, ProspectTier, ProspectHeat } from './types';

export interface ProspectFilter {
  readonly deviceId?: string;
  readonly tier?: ProspectTier;
  readonly heat?: ProspectHeat;
  readonly metro?: string;
  readonly limit?: number;
}

interface Row {
  handle: string;
  device_id: string;
  tier: string;
  heat: string;
  metro: string | null;
  first_contact_at: string | null;
  last_contact_at: string | null;
  last_inbound_at: string | null;
  last_outbound_at: string | null;
  msg_count_in: number;
  msg_count_out: number;
  rate_balk_count: number;
  vip_flag: boolean;
  peer_flag: boolean;
  regular_flag: boolean;
  updated_at: string;
}

const hydrate = (r: Row): Prospect => ({
  handle: r.handle,
  deviceId: r.device_id,
  tier: r.tier as ProspectTier,
  heat: r.heat as ProspectHeat,
  metro: r.metro,
  firstContactAt: r.first_contact_at,
  lastContactAt: r.last_contact_at,
  lastInboundAt: r.last_inbound_at,
  lastOutboundAt: r.last_outbound_at,
  msgCountIn: Number(r.msg_count_in),
  msgCountOut: Number(r.msg_count_out),
  rateBalkCount: Number(r.rate_balk_count),
  vipFlag: r.vip_flag,
  peerFlag: r.peer_flag,
  regularFlag: r.regular_flag,
  updatedAt: r.updated_at,
});

export async function upsertProspect(pool: Pool, p: Prospect): Promise<void> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.prospect
         (handle, device_id, tier, heat, metro,
          first_contact_at, last_contact_at, last_inbound_at, last_outbound_at,
          msg_count_in, msg_count_out, rate_balk_count,
          vip_flag, peer_flag, regular_flag)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
       ON CONFLICT (handle, device_id) DO UPDATE SET
         tier             = excluded.tier,
         heat             = excluded.heat,
         metro            = excluded.metro,
         first_contact_at = excluded.first_contact_at,
         last_contact_at  = excluded.last_contact_at,
         last_inbound_at  = excluded.last_inbound_at,
         last_outbound_at = excluded.last_outbound_at,
         msg_count_in     = excluded.msg_count_in,
         msg_count_out    = excluded.msg_count_out,
         rate_balk_count  = excluded.rate_balk_count,
         vip_flag         = excluded.vip_flag,
         peer_flag        = excluded.peer_flag,
         regular_flag     = excluded.regular_flag,
         updated_at       = now()`,
      [
        p.handle, p.deviceId, p.tier, p.heat, p.metro,
        p.firstContactAt, p.lastContactAt, p.lastInboundAt, p.lastOutboundAt,
        p.msgCountIn, p.msgCountOut, p.rateBalkCount,
        p.vipFlag, p.peerFlag, p.regularFlag,
      ],
    );
  } catch (err) {
    throw new Error(`upsertProspect failed for handle=${p.handle} device=${p.deviceId}: ${String(err)}`, { cause: err });
  }
}

export async function getProspect(pool: Pool, deviceId: string, handle: string): Promise<Prospect | null> {
  try {
    const row = await pgGet<Row>(
      pool,
      'SELECT * FROM macsync.prospect WHERE device_id = $1 AND handle = $2',
      [deviceId, handle],
    );
    return row ? hydrate(row) : null;
  } catch (err) {
    throw new Error(`getProspect failed for handle=${handle} device=${deviceId}: ${String(err)}`, { cause: err });
  }
}

export async function listProspects(pool: Pool, filter: ProspectFilter): Promise<readonly Prospect[]> {
  try {
    const conditions: string[] = [];
    const params: unknown[] = [];
    let n = 1;

    if (filter.deviceId) { conditions.push(`device_id = $${n++}`); params.push(filter.deviceId); }
    if (filter.tier)     { conditions.push(`tier = $${n++}`);      params.push(filter.tier); }
    if (filter.heat)     { conditions.push(`heat = $${n++}`);      params.push(filter.heat); }
    if (filter.metro)    { conditions.push(`metro = $${n++}`);     params.push(filter.metro); }

    const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
    const limit = Math.min(filter.limit ?? 100, 500);
    const rows = await pgAll<Row>(
      pool,
      `SELECT * FROM macsync.prospect ${where} ORDER BY last_contact_at DESC NULLS LAST LIMIT $${n}`,
      [...params, limit],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listProspects failed: ${String(err)}`, { cause: err });
  }
}

export async function listHandlesForDevice(pool: Pool, deviceId: string): Promise<string[]> {
  try {
    const rows = await pgAll<{ handle: string }>(
      pool,
      `SELECT DISTINCT m.from_handle AS handle
       FROM macsync.messages m
       JOIN macsync.conversations c ON c.id = m.conversation_id
       WHERE m.device_id = $1 AND c.is_group = false AND m.from_handle <> ''
       UNION
       SELECT handle FROM macsync.contacts WHERE device_id = $1`,
      [deviceId],
    );
    return rows.map((r) => r.handle);
  } catch (err) {
    throw new Error(`listHandlesForDevice failed for device=${deviceId}: ${String(err)}`, { cause: err });
  }
}
