import { desc, sql } from "drizzle-orm";
import {
  bigserial,
  boolean,
  check,
  index,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  type AnyPgColumn,
} from "drizzle-orm/pg-core";
import { getTransactionIdentity, type Transaction } from "@platform-modules/db";
import type { ContentCommentStatus, ContentMediaRef, ContentPingStatus, ContentStatus, ContentVisibility } from "./model.js";

/**
 * The serializable definition payload shared by code, database, import, and
 * immutable-definition records. Registry enforcement lands after the flat
 * corpus migration; this shape deliberately does not interpret a definition.
 */
export type ContentSchemaValue =
  | string
  | number
  | boolean
  | null
  | readonly ContentSchemaValue[]
  | { readonly [key: string]: ContentSchemaValue };

export type ContentDefinitionOrigin = "code" | "db" | "import";
export type ContentDefinitionKind = "type" | "status";

/** Normalized registry identity; keys/origin/revisions are never rewritten. */
export interface NormalizedContentRegistryRecord {
  readonly key: string;
  readonly origin: ContentDefinitionOrigin;
  readonly version: number;
  readonly revision: number;
  readonly active: boolean;
  readonly canonicalHash: string;
  readonly definition: ContentSchemaValue;
  readonly shadowedDbVersion?: number;
}

/** An immutable byte-equivalent definition snapshot used by future values/revisions. */
export interface ContentDefinitionVersion {
  readonly definitionKind: ContentDefinitionKind;
  readonly definitionKey: string;
  readonly revision: number;
  readonly canonicalHash: string;
  readonly definition: ContentSchemaValue;
  readonly createdAt: Date;
  readonly origin: ContentDefinitionOrigin;
}

/**
 * The pre-migration content_entries shape. `type` and `status` intentionally
 * remain arbitrary strings here: materialization/enforcement is a later wave.
 */
export interface FlatContentEntry {
  readonly id: string;
  readonly slug: string;
  readonly type: string;
  readonly title: string;
  readonly body: string;
  readonly status: string;
  readonly visibility: string;
  readonly publishedAt: Date | null;
  readonly author: string;
  readonly createdAt: Date;
  readonly updatedAt: Date;
}


export type ContentMigrationAdapter = "d1" | "postgres";
export type ContentMigrationPhase =
  | "inventory"
  | "definitions"
  | "dual-write"
  | "backfill"
  | "parity"
  | "enforced"
  | "reverse";

/** Transitional final-column projection while legacy reads remain authoritative. */
export interface ContentEntryCompletionRow {
  readonly parentId: string | null;
  readonly menuOrder: number;
  readonly templateKey: string | null;
  readonly excerpt: string;
  readonly featuredMedia: { readonly id: string; readonly kind?: string } | null;
  readonly commentStatus: "open" | "closed";
  readonly pingStatus: "open" | "closed";
  readonly sticky: boolean;
  readonly format: string | null;
  readonly deletedAt: Date | null;
  readonly lastEditedBy: string;
  readonly typeDefinitionRevision: number;
  readonly statusDefinitionRevision: number;
}

const definitionColumns = () => ({
  key: text("key").primaryKey(),
  origin: text("origin").$type<ContentDefinitionOrigin>().notNull(),
  version: integer("version").notNull().default(1),
  active: boolean("active").notNull().default(true),
  currentRevision: integer("current_revision").notNull().default(1),
  canonicalHash: text("canonical_hash").notNull(),
  definition: jsonb("definition").$type<ContentSchemaValue>().notNull(),
  shadowedDbVersion: integer("shadowed_db_version"),
  hostSessionId: text("host_session_id"),
  createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
    .notNull()
    .$defaultFn(() => new Date()),
  updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
    .notNull()
    .$defaultFn(() => new Date()),
});

export const contentTypeDefinitions = pgTable(
  "content_type_definitions",
  definitionColumns(),
  (t) => [
    check("content_type_definitions_version_positive", sql`${t.version} > 0`),
    check("content_type_definitions_revision_positive", sql`${t.currentRevision} > 0`),
    index("content_type_definitions_active_idx").on(t.active, t.key),
  ]
);

export const contentStatusDefinitions = pgTable(
  "content_status_definitions",
  definitionColumns(),
  (t) => [
    check("content_status_definitions_version_positive", sql`${t.version} > 0`),
    check("content_status_definitions_revision_positive", sql`${t.currentRevision} > 0`),
    index("content_status_definitions_active_idx").on(t.active, t.key),
  ]
);

export const contentDefinitionVersions = pgTable(
  "content_definition_versions",
  {
    definitionKind: text("definition_kind").$type<ContentDefinitionKind>().notNull(),
    definitionKey: text("definition_key").notNull(),
    revision: integer("revision").notNull(),
    canonicalHash: text("canonical_hash").notNull(),
    definition: jsonb("definition").$type<ContentSchemaValue>().notNull(),
    origin: text("origin").$type<ContentDefinitionOrigin>().notNull(),
    hostSessionId: text("host_session_id"),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    primaryKey({ columns: [t.definitionKind, t.definitionKey, t.revision] }),
    check("content_definition_versions_revision_positive", sql`${t.revision} > 0`),
    index("content_definition_versions_hash_idx").on(t.canonicalHash),
  ]
);

/** Private credential bytes never join the public entry projection. */
export const contentPasswordCredentials = pgTable(
  "content_password_credentials",
  {
    entryId: uuid("entry_id").primaryKey(),
    credentialVersion: text("credential_version").notNull(),
    credential: text("credential").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [index("content_password_credentials_version_idx").on(t.credentialVersion)]
);

export const contentLifecycleJournal = pgTable(
  "content_lifecycle_journal",
  {
    id: uuid("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    operationId: text("operation_id").notNull(),
    kind: text("kind").notNull(),
    entryId: uuid("entry_id"),
    definitionKind: text("definition_kind").$type<ContentDefinitionKind>(),
    definitionKey: text("definition_key"),
    definitionRevision: integer("definition_revision"),
    state: text("state").notNull(),
    payload: jsonb("payload").$type<ContentSchemaValue>(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex("content_lifecycle_journal_operation_uq").on(t.operationId),
    index("content_lifecycle_journal_entry_idx").on(t.entryId, t.createdAt),
  ]
);

export const contentImportJournal = pgTable(
  "content_import_journal",
  {
    importId: uuid("import_id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    hostSessionId: text("host_session_id").notNull(),
    manifestHash: text("manifest_hash").notNull(),
    planHash: text("plan_hash").notNull(),
    state: text("state").notNull(),
    version: integer("version").notNull().default(0),
    nextSection: text("next_section"),
    nextOffset: integer("next_offset"),
    highWaterMark: text("high_water_mark"),
    payload: jsonb("payload").$type<ContentSchemaValue>(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex("content_import_journal_session_uq").on(t.hostSessionId),
    check("content_import_journal_version_nonnegative", sql`${t.version} >= 0`),
  ]
);

export const contentTombstones = pgTable(
  "content_tombstones",
  {
    id: uuid("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    entryId: uuid("entry_id").notNull(),
    operationId: text("operation_id").notNull(),
    typeKey: text("type_key").notNull(),
    typeDefinitionRevision: integer("type_definition_revision").notNull(),
    statusKey: text("status_key").notNull(),
    statusDefinitionRevision: integer("status_definition_revision").notNull(),
    snapshot: jsonb("snapshot").$type<ContentSchemaValue>().notNull(),
    deletedAt: timestamp("deleted_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex("content_tombstones_operation_entry_uq").on(t.operationId, t.entryId),
    index("content_tombstones_entry_idx").on(t.entryId, t.deletedAt),
  ]
);

export const contentMigrationCheckpoints = pgTable(
  "content_migration_checkpoints",
  {
    migrationId: text("migration_id").notNull(),
    adapter: text("adapter").$type<ContentMigrationAdapter>().notNull(),
    phase: text("phase").$type<ContentMigrationPhase>().notNull(),
    batchOrdinal: integer("batch_ordinal").notNull().default(0),
    version: integer("version").notNull().default(0),
    lastKey: text("last_key"),
    sourceCount: integer("source_count").notNull(),
    targetCount: integer("target_count"),
    sourceHash: text("source_hash").notNull(),
    targetHash: text("target_hash"),
    highWaterMark: text("high_water_mark"),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    primaryKey({ columns: [t.migrationId, t.adapter, t.phase, t.batchOrdinal] }),
    check("content_migration_checkpoint_batch_nonnegative", sql`${t.batchOrdinal} >= 0`),
    check("content_migration_checkpoint_version_nonnegative", sql`${t.version} >= 0`),
  ]
);

export const contentMigrationWriteJournal = pgTable(
  "content_migration_write_journal",
  {
    seq: bigserial("seq", { mode: "number" }).primaryKey(),
    migrationId: text("migration_id").notNull(),
    operation: text("operation").$type<"insert" | "update" | "delete">().notNull(),
    entryId: text("entry_id").notNull(),
    beforeValue: jsonb("before_value").$type<ContentSchemaValue>(),
    afterValue: jsonb("after_value").$type<ContentSchemaValue>(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [index("content_migration_write_journal_migration_seq_idx").on(t.migrationId, t.seq)]
);

// Tenantless public schema — NO tenant_id/scope column (spec §4.4). The private SaaS owns its
// own schema variant; the `scope` store param is a no-op readiness slot here.
export const contentEntries = pgTable(
  "content_entries",
  {
    id: uuid("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    slug: text("slug").notNull(),
    type: text("type").notNull(),
    title: text("title").notNull(),
    body: text("body").notNull().default(""),
    status: text("status").$type<ContentStatus>().notNull().default("draft"),
    visibility: text("visibility")
      .$type<ContentVisibility>()
      .notNull()
      .default("public"),
    publishedAt: timestamp("published_at", {
      withTimezone: true,
      precision: 3,
    }),
    author: text("author").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    updatedAt: timestamp("updated_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
    parentId: uuid("parent_id"),
    menuOrder: integer("menu_order").notNull().default(0),
    templateKey: text("template_key"),
    excerpt: text("excerpt").notNull().default(""),
    featuredMedia: jsonb("featured_media").$type<ContentMediaRef>(),
    commentStatus: text("comment_status").$type<ContentCommentStatus>().notNull().default("open"),
    pingStatus: text("ping_status").$type<ContentPingStatus>().notNull().default("open"),
    sticky: boolean("sticky").notNull().default(false),
    format: text("format"),
    deletedAt: timestamp("deleted_at", { withTimezone: true, precision: 3 }),
    lastEditedBy: text("last_edited_by").notNull(),
    typeDefinitionRevision: integer("type_definition_revision").notNull().default(1),
    statusDefinitionRevision: integer("status_definition_revision").notNull().default(1),
  },
  (t) => [
    uniqueIndex("content_entries_type_slug_uq").on(t.type, t.slug),
    index("content_entries_type_status_pub_idx").on(
      t.type,
      t.status,
      desc(t.publishedAt)
    ),
    index("content_entries_type_status_vis_pub_idx").on(
      t.type,
      t.status,
      t.visibility,
      desc(t.publishedAt)
    ),
    index("content_entries_parent_order_idx").on(t.type, t.parentId, t.menuOrder, t.id),
    index("content_entries_deleted_idx").on(t.deletedAt),
    index("content_entries_type_definition_idx").on(t.type, t.typeDefinitionRevision),
    index("content_entries_status_definition_idx").on(t.status, t.statusDefinitionRevision),
  ]
);

export const contentTerms = pgTable(
  "content_terms",
  {
    id: uuid("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    taxonomy: text("taxonomy").notNull(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    parentId: uuid("parent_id").references((): AnyPgColumn => contentTerms.id, {
      onDelete: "restrict",
    }),
    depth: integer("depth").notNull().default(0),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex("content_terms_sibling_slug_uq").on(
      t.taxonomy,
      sql`COALESCE(${t.parentId}, '00000000-0000-0000-0000-000000000000'::uuid)`,
      t.slug
    ),
    index("content_terms_taxonomy_parent_idx").on(t.taxonomy, t.parentId),
  ]
);

export const contentEntryTerms = pgTable(
  "content_entry_terms",
  {
    entryId: uuid("entry_id")
      .notNull()
      .references(() => contentEntries.id, { onDelete: "cascade" }),
    termId: uuid("term_id")
      .notNull()
      .references(() => contentTerms.id, { onDelete: "cascade" }),
  },
  (t) => [
    primaryKey({ columns: [t.entryId, t.termId] }),
    index("content_entry_terms_term_idx").on(t.termId),
  ]
);

export const contentRevisions = pgTable(
  "content_revisions",
  {
    id: uuid("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
    entryId: uuid("entry_id")
      .notNull()
      .references(() => contentEntries.id, { onDelete: "cascade" }),
    seq: bigserial("seq", { mode: "number" }).notNull(),
    title: text("title").notNull(),
    body: text("body").notNull(),
    slug: text("slug").notNull(),
    type: text("type").notNull(),
    termIds: jsonb("term_ids")
      .$type<string[]>()
      .notNull()
      .$defaultFn(() => []),
    snapshot: jsonb("snapshot").$type<ContentSchemaValue>(),
    editor: text("editor").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true, precision: 3 })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (t) => [index("content_revisions_entry_seq_idx").on(t.entryId, desc(t.seq))]
);

export const contentSchema = {
  contentEntries,
  contentTerms,
  contentEntryTerms,
  contentRevisions,
  contentTypeDefinitions,
  contentStatusDefinitions,
  contentDefinitionVersions,
  contentPasswordCredentials,
  contentLifecycleJournal,
  contentImportJournal,
  contentTombstones,
  contentMigrationCheckpoints,
  contentMigrationWriteJournal,
};
export type ContentSchema = typeof contentSchema;

/** Callback-minted capability; callers never construct this from a querier. */
export type ContentTransaction = Transaction<ContentSchema>;

/** Runtime guard: only an active callback-minted database transaction may enter T4 lifecycle seams. */
export function assertActiveContentTransaction(tx: ContentTransaction): void {
  getTransactionIdentity(tx);
}