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

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

// ── categories ──────────────────────────────────────────
// Subforum / category definitions, owned by Forum DID.
export const categories = pgTable(
  "categories",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    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: bigint("forum_id", { mode: "bigint" }).references(() => forums.id),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).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 = pgTable(
  "boards",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    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: bigint("category_id", { mode: "bigint" }).references(
      () => categories.id
    ),
    categoryUri: text("category_uri").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).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 = pgTable("users", {
  did: text("did").primaryKey(),
  handle: text("handle"),
  indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
});

// ── memberships ─────────────────────────────────────────
// User membership in a forum. Owned by user DID.
// `did` is both the record owner and the member.
export const memberships = pgTable(
  "memberships",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did")
      .notNull()
      .references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    forumId: bigint("forum_id", { mode: "bigint" }).references(
      () => forums.id
    ),
    forumUri: text("forum_uri").notNull(),
    role: text("role"),
    roleUri: text("role_uri"),
    joinedAt: timestamp("joined_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).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 = pgTable(
  "posts",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    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: bigint("board_id", { mode: "bigint" }).references(() => boards.id),
    rootPostId: bigint("root_post_id", { mode: "bigint" }).references(
      (): any => posts.id
    ),
    parentPostId: bigint("parent_post_id", { mode: "bigint" }).references(
      (): any => posts.id
    ),
    rootUri: text("root_uri"),
    parentUri: text("parent_uri"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
    bannedByMod: boolean("banned_by_mod").notNull().default(false),
    deletedByUser: boolean("deleted_by_user").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 = pgTable(
  "mod_actions",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    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: bigint("forum_id", { mode: "bigint" }).references(
      () => forums.id
    ),
    reason: text("reason"),
    createdBy: text("created_by").notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).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 = pgTable("firehose_cursor", {
  service: text("service").primaryKey().default("jetstream"),
  cursor: bigint("cursor", { mode: "bigint" }).notNull(), // time_us value from Jetstream
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
});

// ── roles ───────────────────────────────────────────────
// Role definitions, owned by Forum DID.
export const roles = pgTable(
  "roles",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    priority: integer("priority").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).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 ─────────────────────────────────────────────────────────
// Normalized join table replacing the permissions text[] column.
// Cascade delete ensures permissions are cleaned up when a role is deleted.
export const rolePermissions = pgTable(
  "role_permissions",
  {
    roleId: bigint("role_id", { mode: "bigint" })
      .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 = pgTable("backfill_progress", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  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: timestamp("started_at", { withTimezone: true }).notNull(),
  completedAt: timestamp("completed_at", { withTimezone: true }),
  errorMessage: text("error_message"),
});

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

// ── themes ──────────────────────────────────────────────
// Theme definitions, owned by Forum DID. Multiple themes per forum.
// Key: tid (multiple records per repo).
export const themes = pgTable(
  "themes",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    colorScheme: text("color_scheme").notNull(), // "light" | "dark"
    tokens: jsonb("tokens").notNull(),           // design token key-value map
    cssOverrides: text("css_overrides"),
    fontUrls: text("font_urls").array(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [uniqueIndex("themes_did_rkey_idx").on(table.did, table.rkey)]
);

// ── theme_policies ───────────────────────────────────────
// Singleton theme policy, owned by Forum DID.
// Key: literal:self (rkey is always "self").
export const themePolicies = pgTable(
  "theme_policies",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    defaultLightThemeUri: text("default_light_theme_uri").notNull(),
    defaultDarkThemeUri: text("default_dark_theme_uri").notNull(),
    allowUserChoice: boolean("allow_user_choice").notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [
    uniqueIndex("theme_policies_did_rkey_idx").on(table.did, table.rkey),
  ]
);

// ── theme_policy_available_themes ────────────────────────
// Normalized join table: which themes does the policy expose to users?
// ON DELETE CASCADE: deleting a policy row removes all its available-theme rows.
export const themePolicyAvailableThemes = pgTable(
  "theme_policy_available_themes",
  {
    policyId: bigint("policy_id", { mode: "bigint" })
      .notNull()
      .references(() => themePolicies.id, { onDelete: "cascade" }),
    themeUri: text("theme_uri").notNull(),
    themeCid: text("theme_cid").notNull(),
  },
  (t) => [primaryKey({ columns: [t.policyId, t.themeUri] })]
);