import type { Pool } from 'pg';
import { z } from 'zod';

import { pgAll, pgRun, pgRunReturning } from '@/shared/db';

import type {
  EnqueueInput,
  PendingItem,
  SendQueueRow,
  SendResultInput,
  SendQueueStatus,
} from './types';

/**
 * Factory that returns a typed repository over a `<module>_send_queue` table.
 *
 * The repo encapsulates the four operations every module needs:
 *  1. `enqueue` — admin/web call that queues an action for the Mac
 *  2. `listPending` — Mac client polls this on each drain tick
 *  3. `markResult` — Mac client acks success/failure
 *  4. `count` — surface stats for the dashboard
 *
 * Per-module tables (calendar_send_queue, reminder_send_queue, note_send_queue)
 * all share the same shape:
 *   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 *   device_id UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
 *   action TEXT NOT NULL,
 *   payload JSONB NOT NULL,
 *   status TEXT NOT NULL DEFAULT 'queued',
 *   sent_at TIMESTAMPTZ,
 *   failure_reason TEXT,
 *   created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
 *   updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
 *
 * `payloadSchema` validates payloads at the boundary so type drift between
 * the Mac client and the web tab is caught in the queue layer.
 */
export interface SendQueueRepo<TPayload> {
  enqueue(db: Pool, input: EnqueueInput<TPayload>): Promise<{ id: string }>;
  listPending(db: Pool, deviceId: string, limit?: number): Promise<PendingItem<TPayload>[]>;
  markResult(db: Pool, input: SendResultInput): Promise<void>;
  count(db: Pool, deviceId: string, status: SendQueueStatus): Promise<number>;
}

export interface CreateSendQueueRepoOptions<TSchema extends z.ZodTypeAny> {
  /** Fully-qualified table name, e.g. `macsync.calendar_send_queue`. */
  tableName: string;
  /** Zod schema validating the `payload` JSONB column. */
  payloadSchema: TSchema;
  /** Optional allow-list of action strings; rejects unknowns at enqueue time. */
  allowedActions?: readonly string[];
}

const QUALIFIED_TABLE_RE = /^[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*$/i;

export function createSendQueueRepo<TSchema extends z.ZodTypeAny>(
  opts: CreateSendQueueRepoOptions<TSchema>,
): SendQueueRepo<z.infer<TSchema>> {
  // Defence-in-depth: the table name is concatenated into SQL because pg's
  // parameter binding cannot template identifiers. We restrict to a strict
  // identifier pattern so a misconfigured caller cannot cause injection.
  if (!QUALIFIED_TABLE_RE.test(opts.tableName)) {
    throw new Error(`createSendQueueRepo: invalid tableName ${opts.tableName}`);
  }
  const allowed = opts.allowedActions ? new Set(opts.allowedActions) : null;

  type Payload = z.infer<TSchema>;

  return {
    async enqueue(db, input) {
      if (allowed && !allowed.has(input.action)) {
        throw new Error(`createSendQueueRepo[${opts.tableName}]: unknown action '${input.action}'`);
      }
      // Parse asserts the payload matches the module's schema before it goes
      // into JSONB. Errors here are 4xx, not 5xx — caller routes already wrap
      // ZodError in a 400 response.
      const parsed = opts.payloadSchema.parse(input.payload) as Payload;
      const row = await pgRunReturning<{ id: string }>(
        db,
        `INSERT INTO ${opts.tableName} (device_id, action, payload, status)
         VALUES ($1, $2, $3::jsonb, 'queued')
         RETURNING id`,
        [input.deviceId, input.action, JSON.stringify(parsed)],
      );
      return { id: row.id };
    },

    async listPending(db, deviceId, limit = 50) {
      const rows = await pgAll<SendQueueRow<Payload>>(
        db,
        `SELECT id, device_id, action, payload, status,
                sent_at, failure_reason, created_at, updated_at
         FROM ${opts.tableName}
         WHERE device_id = $1 AND status = 'queued'
         ORDER BY created_at ASC
         LIMIT $2`,
        [deviceId, limit],
      );
      return rows.map((r) => ({
        id: r.id,
        action: r.action,
        payload: r.payload,
        createdAt: r.created_at,
      }));
    },

    async markResult(db, input) {
      if (input.status === 'sent') {
        await pgRun(
          db,
          `UPDATE ${opts.tableName}
              SET status = 'sent', sent_at = now(), updated_at = now()
            WHERE id = $1`,
          [input.id],
        );
      } else {
        await pgRun(
          db,
          `UPDATE ${opts.tableName}
              SET status = 'failed',
                  failure_reason = $2,
                  updated_at = now()
            WHERE id = $1`,
          [input.id, input.error ?? null],
        );
      }
    },

    async count(db, deviceId, status) {
      const rows = await pgAll<{ n: string }>(
        db,
        `SELECT COUNT(*)::text AS n FROM ${opts.tableName}
          WHERE device_id = $1 AND status = $2`,
        [deviceId, status],
      );
      return Number(rows[0]?.n ?? 0);
    },
  };
}

/**
 * Schema builder for `<module>_send_queue` migrations so every module's
 * table is structurally identical. Pass the desired table name; this returns
 * the SQL that creates the table + supporting indexes.
 */
export function sendQueueTableSql(tableName: string): string {
  if (!QUALIFIED_TABLE_RE.test(tableName)) {
    throw new Error(`sendQueueTableSql: invalid tableName ${tableName}`);
  }
  const indexBase = tableName.replace('.', '_');
  return `
    CREATE TABLE IF NOT EXISTS ${tableName} (
      id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      device_id       UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
      action          TEXT NOT NULL,
      payload         JSONB NOT NULL,
      status          TEXT NOT NULL DEFAULT 'queued',
      sent_at         TIMESTAMPTZ,
      failure_reason  TEXT,
      created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    CREATE INDEX IF NOT EXISTS idx_${indexBase}_device_status
      ON ${tableName}(device_id, status);
    CREATE INDEX IF NOT EXISTS idx_${indexBase}_created
      ON ${tableName}(created_at DESC);
  `.trim();
}
