import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { paginate } from '../lib/envelope.js';
import { pageQuery } from '../lib/pagination.js';
import { AnyList, camel, num, ok, respond } from '../lib/respond.js';

export const changeRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/changes', { schema: { tags: ['changes'], summary: 'Entity change history (created/updated/trial_added/approval_added/ranking_changed/merged/deprecated)', querystring: z.object({ entityType: z.string().optional(), entityId: z.string().optional(), kind: z.string().optional(), since: z.string().optional().describe('ISO date lower bound'), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {
    const q = req.query;
    const conds = [sql`true`];
    if (q.entityType) conds.push(sql`e.entity_type = ${q.entityType}`);
    if (q.entityId) conds.push(sql`e.entity_id = ${q.entityId}`);
    if (q.kind) conds.push(sql`e.kind = ${q.kind}`);
    if (q.since) conds.push(sql`e.created_at >= ${q.since}::timestamptz`);
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT e.id, e.entity_type, e.entity_id, e.kind, e.summary, e.before, e.after, e.ingest_run_id, e.created_at, r.connector_id, r.source_id,
        CASE e.entity_type WHEN 'cancer' THEN (SELECT canonical_name FROM cancers WHERE id = e.entity_id) WHEN 'gene' THEN (SELECT symbol FROM genes WHERE id = e.entity_id) WHEN 'drug' THEN (SELECT name FROM drugs WHERE id = e.entity_id) WHEN 'trial' THEN (SELECT nct_id FROM clinical_trials WHERE id = e.entity_id) END AS entity_name,
        count(*) OVER() AS total
      FROM change_events e LEFT JOIN ingest_runs r ON r.id = e.ingest_run_id
      WHERE ${sql.join(conds, sql` AND `)} ORDER BY e.created_at DESC, e.id DESC LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const data = rows.map((r) => {
      const { total: _t, source_id: _s, ...rest } = r;
      return camel(rest);
    });
    return respond(
      app,
      data,
      rows.map((r) => r.source_id as string | null),
      paginate(total, q.limit, q.offset),
    );
  });
};