import {
  boolean,
  index,
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

/**
 * The four tables better-auth owns.
 *
 * The property keys are what the drizzle adapter looks up (`schema.user`, then
 * `user.emailVerified`), so they must match better-auth's field names exactly.
 * The SQL column names underneath are ours to choose, and stay snake_case like
 * the rest of the schema.
 */
export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").notNull().default(false),
  image: text("image"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

export const session = pgTable(
  "session",
  {
    id: text("id").primaryKey(),
    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
    token: text("token").notNull().unique(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
  },
  (table) => [index("session_user_id_idx").on(table.userId)],
);

export const account = pgTable(
  "account",
  {
    id: text("id").primaryKey(),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    accessTokenExpiresAt: timestamp("access_token_expires_at", { withTimezone: true }),
    refreshTokenExpiresAt: timestamp("refresh_token_expires_at", { withTimezone: true }),
    scope: text("scope"),
    password: text("password"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index("account_user_id_idx").on(table.userId)],
);

export const verification = pgTable(
  "verification",
  {
    id: text("id").primaryKey(),
    identifier: text("identifier").notNull(),
    value: text("value").notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index("verification_identifier_idx").on(table.identifier)],
);

/**
 * Who asked for a report, and from which of our three sites.
 *
 * Separate from `user` because the two answer different questions: `user` is
 * who can sign in, `lead` is who asked and where they came from. A visitor who
 * never signs in again still leaves a row here.
 */
export const lead = pgTable("lead", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull(),
  site: text("site").notNull(),
  userId: text("user_id").references(() => user.id, { onDelete: "set null" }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export type AuditStatus = "queued" | "running" | "done" | "failed";

/**
 * The queue. Postgres is the broker: `SELECT … FOR UPDATE SKIP LOCKED` gives
 * exactly-one-consumer semantics without a second piece of infrastructure to
 * run, back up and monitor for a workload of a few audits an hour.
 */
export const auditJob = pgTable(
  "audit_job",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** What the visitor typed, kept verbatim so a support question can be answered. */
    target: text("target").notNull(),
    /** The normalised origin actually handed to the binary. */
    origin: text("origin").notNull(),
    /** Which of our three sites hosted the form. */
    site: text("site").notNull(),
    status: text("status").$type<AuditStatus>().notNull().default("queued"),
    /**
     * The secret in the visitor's cookie. It is what lets an anonymous run
     * become "my audit" at sign-in, before any account existed to own it.
     */
    claimToken: text("claim_token").notNull(),
    userId: text("user_id").references(() => user.id, { onDelete: "set null" }),
    /** Hashed, not stored: the rate limit needs to count, not to identify. */
    ipHash: text("ip_hash").notNull(),
    error: text("error"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    startedAt: timestamp("started_at", { withTimezone: true }),
    finishedAt: timestamp("finished_at", { withTimezone: true }),
  },
  (table) => [
    // The claim loop's index: queued jobs, oldest first.
    index("audit_job_status_created_idx").on(table.status, table.createdAt),
    // The rate limit counts a single IP's jobs within the hour.
    index("audit_job_ip_hash_created_idx").on(table.ipHash, table.createdAt),
    index("audit_job_claim_token_idx").on(table.claimToken),
    index("audit_job_user_id_created_idx").on(table.userId, table.createdAt),
  ],
);

/**
 * One report per job, keyed by the job itself: a job has a report or it does
 * not, and a second one would mean a job ran twice.
 *
 * The counters are lifted out of the JSON so a list of audits can be rendered
 * without deserialising every document.
 */
export const auditReport = pgTable("audit_report", {
  jobId: uuid("job_id")
    .primaryKey()
    .references(() => auditJob.id, { onDelete: "cascade" }),
  exitCode: integer("exit_code").notNull(),
  blocking: integer("blocking").notNull(),
  warnings: integer("warnings").notNull(),
  checked: integer("checked").notNull(),
  total: integer("total").notNull(),
  report: jsonb("report").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export type AuditJobRow = typeof auditJob.$inferSelect;
export type AuditReportRow = typeof auditReport.$inferSelect;