/**
 * build-analyzer-fixture-T20260424.0045.ts
 *
 * Produces the canonical regression fixture for the AI Ticket Analyzer pipeline,
 * built from LIVE Autotask data (the DB sync is incomplete for this ticket — see
 * dev/analyzer-fixture/T20260424.0045.diff.json).
 *
 * Why live, not DB: only 2 of 9 ticket notes synced — including the spec-required
 * "I'll take it from here" note from the requestor. We need a regression fixture
 * with the full picture, otherwise the analyzer will appear to work while
 * silently missing the most important finding.
 *
 * Outputs:
 *   - lib/services/analyzer/fixtures/T20260424.0045.input.json
 *   - lib/services/analyzer/fixtures/T20260424.0045.expected.json
 *
 * Usage:
 *   npx tsx scripts/build-analyzer-fixture-T20260424.0045.ts
 */

import { config } from 'dotenv';
import { resolve } from 'path';
import { mkdirSync, readFileSync, writeFileSync } from 'fs';
import { Client } from 'pg';

config({ path: resolve(__dirname, '../.env.local') });

const TICKET_ID = 680282;
const LIVE_DUMP = resolve(__dirname, '../dev/analyzer-fixture/T20260424.0045.live.json');
const OUT_DIR = resolve(__dirname, '../lib/services/analyzer/fixtures');

interface ATTicket {
  id: number;
  ticketNumber: string;
  title: string;
  description?: string;
  status: number;
  priority: number;
  queueID: number | null;
  companyID: number;
  contactID: number | null;
  assignedResourceID: number | null;
  createDate: string;
  lastActivityDate: string;
  resolvedDateTime: string | null;
}

interface ATTicketNote {
  id: number;
  ticketID: number;
  title?: string;
  description?: string;
  noteType?: number;
  publish?: number;
  creatorResourceID?: number | null;
  creatorType?: number | null;
  contactID?: number | null;
  createDateTime?: string;
}

interface ATTimeEntry {
  id: number;
  ticketID: number;
  resourceID: number;
  hoursWorked: number;
  summaryNotes?: string;
  internalNotes?: string;
  dateWorked?: string;
  startDateTime?: string;
  endDateTime?: string;
  type?: number;
}

interface LiveDump {
  ticket: ATTicket;
  notes: ATTicketNote[];
  time_entries: ATTimeEntry[];
}

async function getDb(): Promise<Client> {
  const c = new Client({
    host: process.env.POSTGRES_HOST === 'postgres' ? 'localhost' : process.env.POSTGRES_HOST || 'localhost',
    port: parseInt(process.env.POSTGRES_PORT || '5432'),
    database: process.env.POSTGRES_DB!,
    user: process.env.POSTGRES_USER!,
    password: process.env.POSTGRES_PASSWORD!,
  });
  await c.connect();
  return c;
}

async function main() {
  mkdirSync(OUT_DIR, { recursive: true });

  const live = JSON.parse(readFileSync(LIVE_DUMP, 'utf8')) as LiveDump;
  if (!live.ticket || live.ticket.id !== TICKET_ID) {
    throw new Error(`Live dump missing or wrong ticket. Re-run scripts/diff-ticket-680282.ts first.`);
  }

  // Resolve labels for the ticket header and actor names for notes/time entries.
  const db = await getDb();
  try {
    const labels = await db.query(
      `SELECT
         (SELECT label FROM statuses   WHERE value=$1) AS status_label,
         (SELECT label FROM priorities WHERE value=$2) AS priority_label,
         (SELECT label FROM queues     WHERE value=$3) AS queue_label,
         (SELECT company_name FROM companies WHERE id=$4) AS company_name,
         (SELECT first_name||' '||last_name FROM contacts WHERE id=$5) AS contact_name,
         (SELECT email_address FROM contacts WHERE id=$5) AS contact_email,
         (SELECT first_name||' '||last_name FROM resources WHERE id=$6) AS assignee_name,
         (SELECT email FROM resources WHERE id=$6) AS assignee_email`,
      [
        live.ticket.status,
        live.ticket.priority,
        live.ticket.queueID,
        live.ticket.companyID,
        live.ticket.contactID,
        live.ticket.assignedResourceID,
      ]
    );
    const lbl = labels.rows[0];

    const resourceIds = Array.from(
      new Set(
        [
          ...live.notes.map((n) => n.creatorResourceID),
          ...live.time_entries.map((e) => e.resourceID),
        ].filter((x): x is number => typeof x === 'number')
      )
    );
    const resources = await db.query(
      `SELECT id, first_name, last_name, email FROM resources WHERE id = ANY($1::bigint[])`,
      [resourceIds]
    );
    const resourceMap = new Map<number, { name: string; email: string | null }>(
      resources.rows.map((r) => [
        Number(r.id),
        { name: `${r.first_name ?? ''} ${r.last_name ?? ''}`.trim(), email: r.email ?? null },
      ])
    );

    // ── INPUT FIXTURE — shape matches what the data-access layer produces ────
    const input = {
      ticket: {
        id: live.ticket.id,
        ticket_number: live.ticket.ticketNumber,
        title: live.ticket.title,
        description: live.ticket.description ?? null,
        status: live.ticket.status,
        status_label: lbl.status_label ?? null,
        priority: live.ticket.priority,
        priority_label: lbl.priority_label ?? null,
        queue_id: live.ticket.queueID,
        queue_label: lbl.queue_label ?? null,
        company_id: live.ticket.companyID,
        company_name: lbl.company_name ?? null,
        contact_id: live.ticket.contactID,
        contact_name: lbl.contact_name ?? null,
        contact_email: lbl.contact_email ?? null,
        assigned_resource_id: live.ticket.assignedResourceID,
        assignee_name: lbl.assignee_name ?? null,
        assignee_email: lbl.assignee_email ?? null,
        create_date: live.ticket.createDate,
        last_activity_date: live.ticket.lastActivityDate,
        resolved_date_time: live.ticket.resolvedDateTime,
      },
      notes: live.notes
        .slice()
        .sort((a, b) => (a.createDateTime ?? '').localeCompare(b.createDateTime ?? ''))
        .map((n) => {
          const r = n.creatorResourceID ? resourceMap.get(n.creatorResourceID) : undefined;
          return {
            id: n.id,
            title: n.title ?? null,
            description: n.description ?? '',
            note_type: n.noteType ?? null,
            publish: n.publish ?? null,
            creator_resource_id: n.creatorResourceID ?? null,
            creator_name: r?.name ?? null,
            creator_email: r?.email ?? null,
            creator_type: n.creatorType ?? null,
            create_date_time: n.createDateTime ?? null,
          };
        }),
      time_entries: live.time_entries
        .slice()
        .sort((a, b) => (a.dateWorked ?? '').localeCompare(b.dateWorked ?? ''))
        .map((e) => {
          const r = resourceMap.get(e.resourceID);
          return {
            id: e.id,
            resource_id: e.resourceID,
            resource_name: r?.name ?? null,
            resource_email: r?.email ?? null,
            hours_worked: Number(e.hoursWorked),
            notes: e.summaryNotes ?? null, // Summary Notes — customer-visible
            internal_notes: e.internalNotes ?? null,
            entry_date: e.dateWorked ?? null,
            start_date_time: e.startDateTime ?? null,
            end_date_time: e.endDateTime ?? null,
            type: e.type ?? null,
          };
        }),
      provenance: {
        source: 'live_autotask_rest',
        captured_at: new Date().toISOString(),
        note:
          'DB sync was incomplete for this ticket (2/9 notes); fixture built from live Autotask to capture the spec-required "I\'ll take it from here" note (id=33738796) which was missing from ticket_notes.',
      },
    };

    writeFileSync(`${OUT_DIR}/T20260424.0045.input.json`, JSON.stringify(input, null, 2));

    // ── EXPECTED FIXTURE — pre-processor + pipeline assertions ────────────────
    // Note IDs after Stage 0 filtering:
    //   filter as workflow_noise: 33738631, 33738632, 33738633, 33738634
    //     (creator_resource_id=4 "Autotask Administrator", title starts "Workflow Rule")
    //   filter as email_notification: 33738776, 33738797, plus the two already-in-DB
    //     Service Desk Notification entries (titles == "Service Desk Notification")
    //   keep: 33738796 (Lorentz's "I'll take it from here" note)
    //   plus: all 5 time entries (which contain the bulk of the real story)

    const expected = {
      preprocessor: {
        filtered_workflow_noise_ids: [33738631, 33738632, 33738633, 33738634],
        filtered_email_notification_ids_in_input: live.notes
          .filter((n) => n.title === 'Service Desk Notification')
          .map((n) => n.id),
        retained_note_ids: live.notes
          .filter(
            (n) =>
              n.creatorResourceID !== 4 &&
              n.title !== 'Service Desk Notification'
          )
          .map((n) => n.id),
        retained_time_entry_ids: live.time_entries.map((e) => e.id),
        // total_events expected = retained notes + time entries (mixed/internal/customer)
      },
      // Required findings the pipeline output MUST contain — each grounded in
      // real evidence in the input fixture. evidence_ids reference notes
      // (ticket_notes.id) and entries (time_entries.id) by primary key.
      required_findings: [
        {
          id: 'F1_original_ask_narrower',
          severity: 'low',
          description:
            'The original requestor email asked for a credential location for an existing integration (loss run pro / claims department), narrower than the broader vendor-integration scope the ticket pivoted to.',
          evidence: [
            { kind: 'time_entry_internal_notes', id: 465933 },
          ],
          grounded: true,
        },
        {
          id: 'F2_customer_said_stop',
          severity: 'high',
          description:
            'Requestor (Lorentz Hinrichsen) posted a ticket note on 2026-04-24 indicating he could proceed independently and that no further outreach to Vertafore was needed.',
          evidence: [
            { kind: 'ticket_note', id: 33738796 },
          ],
          grounded: true,
          notes_for_review:
            'This note is in Autotask but was NOT in the local ticket_notes table at fixture-build time. The analyzer must source notes either live or via a fixed sync.',
        },
        {
          id: 'F3_work_continued_after_stop',
          severity: 'high',
          description:
            'On 2026-04-27 (next business day after the requestor said "I\'ll take it from here"), the assigned tech took a call from Vertafore and logged ~20 minutes of additional work (entries on 04/27).',
          evidence: [
            { kind: 'ticket_note',  id: 33738796 },              // F2 — the stop signal
            { kind: 'time_entry',   id: 466134  },               // 04/27 call from Richard at Vertafore (0.17 hr)
            { kind: 'time_entry',   id: 466183  },               // 04/27 follow-up email captured (0.17 hr)
          ],
          grounded: true,
        },
        {
          id: 'F4_status_does_not_match_reality',
          severity: 'medium',
          description:
            'Ticket status remains "Waiting Customer" though the requestor effectively closed the loop on 04/24. resolved_date_time is null three days later.',
          evidence: [
            { kind: 'ticket_field', field: 'status_label', value: 'Waiting Customer' },
            { kind: 'ticket_field', field: 'resolved_date_time', value: null },
            { kind: 'ticket_note',  id: 33738796 },
          ],
          grounded: true,
        },
      ],
      expected_next_step_keywords: [
        // The Sonnet-tier next_step should reference at least one of these.
        'confirm with requestor',
        'Vertafore',
        'close',
      ],
      sync_gap_observed: {
        live_note_count: live.notes.length,
        synced_note_count: 2,
        missing_note_ids: live.notes.map((n) => n.id).filter((id) => ![33741514, 33741844].includes(id)),
      },
    };

    writeFileSync(`${OUT_DIR}/T20260424.0045.expected.json`, JSON.stringify(expected, null, 2));

    console.log(`Wrote:\n  ${OUT_DIR}/T20260424.0045.input.json\n  ${OUT_DIR}/T20260424.0045.expected.json`);
    console.log(
      `\nFixture summary:\n  ticket: ${input.ticket.ticket_number} (${input.ticket.status_label})\n  notes: ${input.notes.length}  time_entries: ${input.time_entries.length}\n  retained_notes: ${expected.preprocessor.retained_note_ids.length}  filtered_workflow: ${expected.preprocessor.filtered_workflow_noise_ids.length}  filtered_email: ${expected.preprocessor.filtered_email_notification_ids_in_input.length}`
    );
  } finally {
    await db.end();
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});