import { sql, type Kysely } from "kysely";

/**
 * Subito AI Guru (concierge-support agent) tables. See docs/subito-ai-guru.md.
 *
 * guru_tickets          local index of cases (ClickUp is the system of record)
 * guru_pending_actions  writes proposed by the agent; executed only after an
 *                       allowlisted console user clicks Confirm
 * guru_edit_allowlist   emails allowed to confirm writes (super-admin managed)
 * guru_audit_log        every write attempt + result (spec §3)
 *
 * Statuses are plain text, not PG enums, by house convention.
 * PII note: member_ref holds a member *reference* (account number / member id),
 * never raw contact data. Free text is scrubbed by redactSensitive on the way in.
 */
export async function up(db: Kysely<any>): Promise<void> {
  // Human-friendly ticket references: GRU-1000, GRU-1001, ...
  await sql`CREATE SEQUENCE IF NOT EXISTS guru_ticket_reference_seq START 1000`.execute(
    db,
  );

  await db.schema
    .createTable("guru_tickets")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("reference", "varchar(20)", (col) =>
      col
        .notNull()
        .unique()
        .defaultTo(sql`'GRU-' || nextval('guru_ticket_reference_seq')`),
    )
    .addColumn("console_user_id", "uuid", (col) =>
      col.notNull().references("console_users.id").onDelete("cascade"),
    )
    .addColumn("concierge_email", "varchar(190)", (col) => col.notNull())
    .addColumn("member_ref", "varchar(190)")
    // login | bookings | points | entitlements | fees | other — free-ish text
    .addColumn("category", "varchar(40)")
    // OPEN | RESOLVED | ESCALATED (reopen sets it back to OPEN)
    .addColumn("status", "varchar(20)", (col) =>
      col.notNull().defaultTo("OPEN"),
    )
    // AUTO_RESOLVED | ESCALATED — set when the case closes, for analytics
    .addColumn("resolution", "varchar(20)")
    .addColumn("summary", "text")
    .addColumn("clickup_task_id", "varchar(64)")
    .addColumn("clickup_task_url", "text")
    // ElevenLabs conversation ids, oldest first (reopens append)
    .addColumn("conversation_ids", sql`text[]`, (col) =>
      col.notNull().defaultTo(sql`'{}'::text[]`),
    )
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`),
    )
    .addColumn("updated_at", "timestamptz")
    .addColumn("resolved_at", "timestamptz")
    .execute();

  await sql`
    CREATE INDEX IF NOT EXISTS idx_guru_tickets_owner
    ON guru_tickets (console_user_id, created_at DESC)
  `.execute(db);
  await sql`
    CREATE INDEX IF NOT EXISTS idx_guru_tickets_queue
    ON guru_tickets (status, category, created_at DESC)
  `.execute(db);

  await db.schema
    .createTable("guru_pending_actions")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("ticket_id", "uuid", (col) =>
      col.notNull().references("guru_tickets.id").onDelete("cascade"),
    )
    .addColumn("conversation_id", "varchar(190)", (col) => col.notNull())
    // PASSWORD_RESET | SEND_VERIFICATION_EDM | SYNC_PROFILE
    .addColumn("action_type", "varchar(40)", (col) => col.notNull())
    .addColumn("member_ref", "varchar(190)", (col) => col.notNull())
    .addColumn("params", "jsonb")
    // Human-readable description shown on the confirmation card
    .addColumn("summary", "text", (col) => col.notNull())
    // PENDING | EXECUTED | FAILED | DECLINED | EXPIRED | REFUSED
    .addColumn("status", "varchar(20)", (col) =>
      col.notNull().defaultTo("PENDING"),
    )
    .addColumn("confirmed_by", "varchar(190)")
    .addColumn("result", "jsonb")
    .addColumn("error", "text")
    .addColumn("expires_at", "timestamptz", (col) => col.notNull())
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`),
    )
    .addColumn("executed_at", "timestamptz")
    .execute();

  await sql`
    CREATE INDEX IF NOT EXISTS idx_guru_actions_ticket
    ON guru_pending_actions (ticket_id, created_at DESC)
  `.execute(db);

  await db.schema
    .createTable("guru_edit_allowlist")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    // Stored lowercase; uniqueness is on the normalized value.
    .addColumn("email", "varchar(190)", (col) => col.notNull().unique())
    .addColumn("added_by", "varchar(190)", (col) => col.notNull())
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`),
    )
    .execute();

  await db.schema
    .createTable("guru_audit_log")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("ticket_id", "uuid", (col) =>
      col.references("guru_tickets.id").onDelete("set null"),
    )
    .addColumn("action_id", "uuid", (col) =>
      col.references("guru_pending_actions.id").onDelete("set null"),
    )
    // ticket.created | ticket.reopened | action.proposed | action.confirmed |
    // action.executed | action.failed | action.refused | action.declined |
    // allowlist.added | allowlist.removed | clickup.created | viewpoint.logged ...
    .addColumn("action", "varchar(60)", (col) => col.notNull())
    .addColumn("actor_email", "varchar(190)")
    .addColumn("confirmed_by", "varchar(190)")
    .addColumn("member_ref", "varchar(190)")
    .addColumn("before", "jsonb")
    .addColumn("after", "jsonb")
    .addColumn("clickup_task_id", "varchar(64)")
    .addColumn("success", "boolean")
    .addColumn("metadata", "jsonb")
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`),
    )
    .execute();

  await sql`
    CREATE INDEX IF NOT EXISTS idx_guru_audit_ticket
    ON guru_audit_log (ticket_id, created_at DESC)
  `.execute(db);
  await sql`
    CREATE INDEX IF NOT EXISTS idx_guru_audit_time
    ON guru_audit_log (created_at DESC)
  `.execute(db);
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("guru_audit_log").ifExists().execute();
  await db.schema.dropTable("guru_edit_allowlist").ifExists().execute();
  await db.schema.dropTable("guru_pending_actions").ifExists().execute();
  await db.schema.dropTable("guru_tickets").ifExists().execute();
  await sql`DROP SEQUENCE IF EXISTS guru_ticket_reference_seq`.execute(db);
}
