import {
  sqliteTable,
  text,
  integer,
  uniqueIndex,
  index,
  primaryKey,
} from "drizzle-orm/sqlite-core";

// ── forums ──────────────────────────────────────────────
// Singleton forum metadata record, owned by Forum DID.
// Key: literal:self (rkey is always "self").
export const forums = sqliteTable(
  "forums",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [uniqueIndex("forums_did_rkey_idx").on(table.did, table.rkey)]
);

// ── categories ──────────────────────────────────────────
// Subforum / category definitions, owned by Forum DID.
export const categories = sqliteTable(
  "categories",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    slug: text("slug"),
    sortOrder: integer("sort_order"),
    forumId: integer("forum_id").references(() => forums.id),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("categories_did_rkey_idx").on(table.did, table.rkey),
  ]
);

// ── boards ──────────────────────────────────────────────
// Board (subforum) definitions within categories, owned by Forum DID.
export const boards = sqliteTable(
  "boards",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    slug: text("slug"),
    sortOrder: integer("sort_order"),
    categoryId: integer("category_id").references(() => categories.id),
    categoryUri: text("category_uri").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("boards_did_rkey_idx").on(table.did, table.rkey),
    index("boards_category_id_idx").on(table.categoryId),
  ]
);

// ── users ───────────────────────────────────────────────
// Known AT Proto identities. Populated when any record
// from a DID is indexed. DID is the primary key.
export const users = sqliteTable("users", {
  did: text("did").primaryKey(),
  handle: text("handle"),
  indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
});

// ── memberships ─────────────────────────────────────────
// User membership in a forum. Owned by user DID.
// `did` is both the record owner and the member.
export const memberships = sqliteTable(
  "memberships",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did")
      .notNull()
      .references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    forumId: integer("forum_id").references(() => forums.id),
    forumUri: text("forum_uri").notNull(),
    role: text("role"),
    roleUri: text("role_uri"),
    joinedAt: integer("joined_at", { mode: "timestamp" }),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("memberships_did_rkey_idx").on(table.did, table.rkey),
    index("memberships_did_idx").on(table.did),
  ]
);

// ── posts ───────────────────────────────────────────────
// Unified post model. NULL root/parent = thread starter (topic).
// Non-null root/parent = reply. Mirrors app.bsky.feed.post pattern.
// Owned by user DID.
export const posts = sqliteTable(
  "posts",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did")
      .notNull()
      .references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    title: text("title"),
    text: text("text").notNull(),
    forumUri: text("forum_uri"),
    boardUri: text("board_uri"),
    boardId: integer("board_id").references(() => boards.id),
    rootPostId: integer("root_post_id").references((): any => posts.id),
    parentPostId: integer("parent_post_id").references((): any => posts.id),
    rootUri: text("root_uri"),
    parentUri: text("parent_uri"),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
    bannedByMod: integer("banned_by_mod", { mode: "boolean" })
      .notNull()
      .default(false),
    deletedByUser: integer("deleted_by_user", { mode: "boolean" })
      .notNull()
      .default(false),
  },
  (table) => [
    uniqueIndex("posts_did_rkey_idx").on(table.did, table.rkey),
    index("posts_forum_uri_idx").on(table.forumUri),
    index("posts_board_id_idx").on(table.boardId),
    index("posts_board_uri_idx").on(table.boardUri),
    index("posts_root_post_id_idx").on(table.rootPostId),
  ]
);

// ── mod_actions ─────────────────────────────────────────
// Moderation actions, owned by Forum DID. Written by AppView
// on behalf of authorized moderators after role verification.
export const modActions = sqliteTable(
  "mod_actions",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    action: text("action").notNull(),
    subjectDid: text("subject_did"),
    subjectPostUri: text("subject_post_uri"),
    forumId: integer("forum_id").references(() => forums.id),
    reason: text("reason"),
    createdBy: text("created_by").notNull(),
    expiresAt: integer("expires_at", { mode: "timestamp" }),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("mod_actions_did_rkey_idx").on(table.did, table.rkey),
    index("mod_actions_subject_did_idx").on(table.subjectDid),
    index("mod_actions_subject_post_uri_idx").on(table.subjectPostUri),
  ]
);

// ── firehose_cursor ─────────────────────────────────────
// Tracks the last processed event from the Jetstream firehose.
// Singleton table (service is primary key).
export const firehoseCursor = sqliteTable("firehose_cursor", {
  service: text("service").primaryKey().default("jetstream"),
  cursor: integer("cursor").notNull(), // time_us value from Jetstream
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull(),
});

// ── roles ───────────────────────────────────────────────
// Role definitions, owned by Forum DID.
// Note: permissions are stored in the role_permissions join table (not as an array column).
export const roles = sqliteTable(
  "roles",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    priority: integer("priority").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("roles_did_rkey_idx").on(table.did, table.rkey),
    index("roles_did_idx").on(table.did),
    index("roles_did_name_idx").on(table.did, table.name),
  ]
);

// ── role_permissions ────────────────────────────────────
// Many-to-many join table for role permissions.
// Replaces the permissions text[] array column from the Postgres schema.
export const rolePermissions = sqliteTable(
  "role_permissions",
  {
    roleId: integer("role_id")
      .notNull()
      .references(() => roles.id, { onDelete: "cascade" }),
    permission: text("permission").notNull(),
  },
  (t) => [primaryKey({ columns: [t.roleId, t.permission] })]
);

// ── backfill_progress ───────────────────────────────────
// Tracks backfill job state for crash-resilient resume.
export const backfillProgress = sqliteTable("backfill_progress", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  status: text("status").notNull(), // 'in_progress', 'completed', 'failed'
  backfillType: text("backfill_type").notNull(), // 'full_sync', 'catch_up'
  lastProcessedDid: text("last_processed_did"),
  didsTotal: integer("dids_total").notNull().default(0),
  didsProcessed: integer("dids_processed").notNull().default(0),
  recordsIndexed: integer("records_indexed").notNull().default(0),
  startedAt: integer("started_at", { mode: "timestamp" }).notNull(),
  completedAt: integer("completed_at", { mode: "timestamp" }),
  errorMessage: text("error_message"),
});

// ── backfill_errors ─────────────────────────────────────
// Per-DID error log for failed backfill syncs.
export const backfillErrors = sqliteTable(
  "backfill_errors",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    backfillId: integer("backfill_id")
      .notNull()
      .references(() => backfillProgress.id),
    did: text("did").notNull(),
    collection: text("collection").notNull(),
    errorMessage: text("error_message").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [index("backfill_errors_backfill_id_idx").on(table.backfillId)]
);