import * as drizzle from "drizzle-orm";
import type { SQL, SQLWrapper } from "drizzle-orm";
import * as db from "@platform-modules/db";
import type { Querier, TransactionIdentity } from "@platform-modules/db";
import {
  contentEntries,
  type ContentSchema,
  type ContentTransaction,
  type FlatContentEntry,
} from "./schema.js";
import {
  replaceEntryTerms,
  termsForEntry,
  validateTermIdsExist,
} from "./taxonomy.js";
import {
  ContentSanitizationError,
  ContentValidationError,
  UUID_RE,
  normalizeContentInput,
  type ContentEntry,
  type ContentInput,
  type ContentStatus,
  type ContentVisibility,
  type Sanitize,
} from "./model.js";
import { assertCanModify, assertCanPublish, type Actor } from "./authz.js";

export type EntityRef = { id: string; slug: string; type: string };

export type ContentAdapterKind = "d1" | "postgres";

/**
 * The final read seam used while the corpus is still in its flat table. Both
 * adapters expose the callback transaction's normalized `execute()` result;
 * no dialect-specific query builder or registry is allowed through this seam.
 */
export interface ContentFlatCorpusReader {
  readonly adapter: ContentAdapterKind;
  execute<Row extends Record<string, unknown> = Record<string, unknown>>(
    query: SQLWrapper
  ): Promise<readonly Row[]>;
}

export type ContentStoreErrorCode =
  | "invalid-flat-row"
  | "unsupported-adapter"
  | "transaction-capability";

/** Typed structural error for the final-store boundary. */
export class ContentStoreContractError extends Error {
  override readonly name = "ContentStoreContractError";

  constructor(
    readonly code: ContentStoreErrorCode,
    readonly detail: string,
    readonly field?: string
  ) {
    super(`content store ${code}: ${detail}`);
  }
}

export function isContentStoreContractError(
  error: unknown
): error is ContentStoreContractError {
  if (typeof error !== "object" || error === null) return false;
  const candidate = error as { code?: unknown; detail?: unknown };
  return (
    (candidate.code === "invalid-flat-row" ||
      candidate.code === "unsupported-adapter" ||
      candidate.code === "transaction-capability") &&
    typeof candidate.detail === "string"
  );
}

function trustedFlatRow(row: unknown): Record<string, unknown> {
  if (
    typeof row !== "object" ||
    row === null ||
    Array.isArray(row) ||
    Object.getPrototypeOf(row) !== Object.prototype ||
    Object.getOwnPropertySymbols(row).length !== 0
  ) {
    throw new ContentStoreContractError(
      "invalid-flat-row",
      "must be a plain data row",
      "row"
    );
  }
  for (const key of Object.getOwnPropertyNames(row)) {
    const descriptor = Object.getOwnPropertyDescriptor(row, key)!;
    if (!descriptor.enumerable || !("value" in descriptor)) {
      throw new ContentStoreContractError(
        "invalid-flat-row",
        "must not contain non-enumerable properties or accessors",
        "row"
      );
    }
  }
  return row as Record<string, unknown>;
}

function requiredString(row: Record<string, unknown>, field: string): string {
  const value = row[field];
  if (typeof value !== "string") {
    throw new ContentStoreContractError(
      "invalid-flat-row",
      "must be a string",
      field
    );
  }
  return value;
}

function requiredDate(
  row: Record<string, unknown>,
  field: string,
  nullable = false
): Date | null {
  const value = row[field];
  if (nullable && value === null) return null;
  const date =
    value instanceof Date
      ? new Date(Date.prototype.getTime.call(value))
      : typeof value === "string"
      ? new Date(value)
      : null;
  if (date === null || Number.isNaN(date.getTime())) {
    throw new ContentStoreContractError(
      "invalid-flat-row",
      "must be an ISO timestamp or Date",
      field
    );
  }
  return date;
}

/**
 * Reads the legacy flat corpus losslessly enough for cross-adapter comparison.
 * It validates only transport shape; arbitrary legacy type/status strings are
 * deliberately preserved until the registry-materialization migration.
 */
export async function readFlatContentEntries(
  reader: ContentFlatCorpusReader
): Promise<readonly FlatContentEntry[]> {
  if (reader.adapter !== "d1" && reader.adapter !== "postgres") {
    throw new ContentStoreContractError(
      "unsupported-adapter",
      `unknown adapter ${String(reader.adapter)}`
    );
  }

  const rows = await reader.execute(drizzle.sql`
    SELECT id, slug, type, title, body, status, visibility,
      published_at AS "publishedAt", author,
      created_at AS "createdAt", updated_at AS "updatedAt"
    FROM content_entries
    ORDER BY id ASC
  `);

  return Object.freeze(
    rows.map((candidate) => {
      const row = trustedFlatRow(candidate);
      return Object.freeze({
        id: requiredString(row, "id"),
        slug: requiredString(row, "slug"),
        type: requiredString(row, "type"),
        title: requiredString(row, "title"),
        body: requiredString(row, "body"),
        status: requiredString(row, "status"),
        visibility: requiredString(row, "visibility"),
        publishedAt: requiredDate(row, "publishedAt", true),
        author: requiredString(row, "author"),
        createdAt: requiredDate(row, "createdAt")!,
        updatedAt: requiredDate(row, "updatedAt")!,
      });
    })
  );
}

/** Require the exact callback-minted identity before a composite caller acts. */
export function assertContentTransactionIdentity(
  tx: ContentTransaction,
  expectedIdentity: TransactionIdentity
): void {
  try {
    db.assertTransactionIdentity(tx, expectedIdentity);
  } catch (error) {
    if (db.isTransactionCapabilityError(error)) {
      throw new ContentStoreContractError(
        "transaction-capability",
        error.reason
      );
    }
    throw error;
  }
}

export type ListQuery = {
  type?: string;
  status?: ContentStatus;
  term?: string;
  includeDescendants?: boolean;
  limit?: number;
  offset?: number;
};

/**
 * Tenancy readiness seam (spec §4.4 / CLAUDE.md §5). NO-OP in the single-install public schema
 * (no scope column). The private SaaS supplies its own schema + a store variant that consumes
 * `scope`. Accepting it here keeps the public contract stable across that swap — no tenant_id leak.
 */
export type StoreOpts = { scope?: string };

export class ContentNotFoundError extends Error {
  override readonly name = "ContentNotFoundError";
  constructor(readonly selector: string) {
    super(`content entry not found: ${selector}`);
  }
}

/**
 * A duplicate (type, slug) hits content_entries_type_slug_uq. Surface it as a TYPED, contextful
 * boundary error (coding-standard §4) instead of leaking the raw Postgres error out of the seam.
 * DB-index-enforced + caught = race-safe (no pre-check TOCTOU).
 */
export class ContentConflictError extends Error {
  override readonly name = "ContentConflictError";
  constructor(readonly type: string, readonly slug: string) {
    super(`content entry already exists: type=${type} slug=${slug}`);
  }
}

const VISIBILITY_LITERALS: ContentVisibility[] = [
  "public",
  "private",
  "members",
];

function assertVisibilityLiteral(visibility: ContentVisibility): void {
  if (!VISIBILITY_LITERALS.includes(visibility)) {
    throw new ContentValidationError(
      "visibility",
      "must be public|private|members"
    );
  }
}

/** SQL read-authz floor — never post-filter in JS (spec §3). Shared by list/getBySlug/search. */
export function visibilityPredicate(viewer?: Actor | null): SQL | undefined {
  if (viewer?.canEditAny) return undefined;
  if (!viewer) {
    return drizzle.and(
      drizzle.eq(contentEntries.status, "published"),
      drizzle.eq(contentEntries.visibility, "public")
    );
  }
  const clauses: SQL[] = [
    drizzle.eq(contentEntries.author, viewer.id),
    drizzle.and(
      drizzle.eq(contentEntries.status, "published"),
      drizzle.eq(contentEntries.visibility, "public")
    )!,
  ];
  if (viewer.canViewMembers) {
    clauses.push(
      drizzle.and(
        drizzle.eq(contentEntries.status, "published"),
        drizzle.eq(contentEntries.visibility, "members")
      )!
    );
  }
  return drizzle.or(...clauses)!;
}

/** Detect a unique-constraint violation without coupling to one driver's error shape. */
function isUniqueViolation(e: unknown): boolean {
  let cur: unknown = e;
  while (cur) {
    const code = (cur as { code?: unknown })?.code;
    const msg = cur instanceof Error ? cur.message : String(cur);
    if (
      code === "23505" ||
      /content_entries_type_slug_uq|duplicate key|unique constraint/i.test(msg)
    )
      return true;
    cur =
      cur instanceof Error
        ? (cur as Error & { cause?: unknown }).cause
        : undefined;
  }
  return false;
}

async function catchConflict<T>(
  type: string,
  slug: string,
  fn: () => Promise<T>
): Promise<T> {
  try {
    return await fn();
  } catch (e) {
    if (isUniqueViolation(e)) throw new ContentConflictError(type, slug);
    throw e;
  }
}

const DEFAULT_LIMIT = 20;
const MAX_LIMIT = 100;

type Row = typeof contentEntries.$inferSelect;

function toEntry(row: Row, terms: ContentEntry["terms"]): ContentEntry {
  return {
    id: row.id,
    slug: row.slug,
    type: row.type,
    title: row.title,
    body: row.body,
    status: row.status,
    visibility: row.visibility,
    publishedAt: row.publishedAt ?? null,
    author: row.author,
    terms,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    parentId: row.parentId ?? null,
    menuOrder: row.menuOrder,
    templateKey: row.templateKey ?? null,
    excerpt: row.excerpt,
    featuredMedia: row.featuredMedia ?? null,
    commentStatus: row.commentStatus,
    pingStatus: row.pingStatus,
    passwordProtected: false,
    sticky: row.sticky,
    format: row.format ?? null,
    deletedAt: row.deletedAt ?? null,
    lastEditedBy: row.lastEditedBy,
    typeDefinitionRevision: row.typeDefinitionRevision,
    statusDefinitionRevision: row.statusDefinitionRevision,
  };
}

async function entryWithTerms(
  db: Querier<ContentSchema>,
  row: Row
): Promise<ContentEntry> {
  return toEntry(row, await termsForEntry(db, row.id));
}

const REF_COLS = {
  id: contentEntries.id,
  slug: contentEntries.slug,
  type: contentEntries.type,
};

async function getRow(db: Querier<ContentSchema>, id: string): Promise<Row> {
  const [row] = await db
    .select()
    .from(contentEntries)
    .where(drizzle.eq(contentEntries.id, id))
    .limit(1);
  if (!row) throw new ContentNotFoundError(`id=${id}`);
  return row;
}

export async function list(
  db: Querier<ContentSchema>,
  query: ListQuery = {},
  viewer?: Actor | null,
  _opts: StoreOpts = {}
): Promise<ContentEntry[]> {
  const limit = Math.min(Math.max(query.limit ?? DEFAULT_LIMIT, 1), MAX_LIMIT);
  const offset = Math.max(query.offset ?? 0, 0);
  const filters: (SQL | undefined)[] = [];
  const vis = visibilityPredicate(viewer);
  if (vis) filters.push(vis);
  if (query.type) filters.push(drizzle.eq(contentEntries.type, query.type));
  if (query.status) filters.push(drizzle.eq(contentEntries.status, query.status));
  else filters.push(drizzle.ne(contentEntries.status, "trashed"));
  if (query.term) {
    if (!UUID_RE.test(query.term)) {
      throw new ContentValidationError(
        "term",
        "must be a well-formed UUID string"
      );
    }
    if (query.includeDescendants) {
      filters.push(drizzle.sql`EXISTS (
        SELECT 1 FROM content_entry_terms cet
        WHERE cet.entry_id = ${contentEntries.id}
        AND cet.term_id IN (
          WITH RECURSIVE subtree AS (
            SELECT id, 1 AS lvl FROM content_terms WHERE id = ${query.term}::uuid
            UNION ALL
            SELECT t.id, s.lvl + 1 FROM content_terms t INNER JOIN subtree s ON t.parent_id = s.id
            WHERE s.lvl < 64
          )
          SELECT id FROM subtree
        )
      )`);
    } else {
      filters.push(drizzle.sql`EXISTS (
        SELECT 1 FROM content_entry_terms cet
        WHERE cet.entry_id = ${contentEntries.id} AND cet.term_id = ${query.term}::uuid
      )`);
    }
  }
  const defined = filters.filter((f): f is SQL => f !== undefined);
  const rows = await db
    .select()
    .from(contentEntries)
    .where(defined.length ? drizzle.and(...defined) : undefined)
    .orderBy(drizzle.desc(contentEntries.publishedAt), drizzle.desc(contentEntries.createdAt))
    .limit(limit)
    .offset(offset);
  return Promise.all(rows.map((row) => entryWithTerms(db, row)));
}

export async function getBySlug(
  db: Querier<ContentSchema>,
  type: string,
  slug: string,
  viewer?: Actor | null,
  _opts: StoreOpts = {}
): Promise<ContentEntry | null> {
  const filters: SQL[] = [
    drizzle.eq(contentEntries.type, type),
    drizzle.eq(contentEntries.slug, slug),
  ];
  const vis = visibilityPredicate(viewer);
  if (vis) filters.push(vis);
  const [row] = await db
    .select()
    .from(contentEntries)
    .where(drizzle.and(...filters))
    .limit(1);
  return row ? await entryWithTerms(db, row) : null;
}

/**
 * Read a single entry by its stable primary-key id (peer of `getBySlug`; admin edit URLs key on id,
 * not the mutable slug). Applies the IDENTICAL viewer visibility predicate in SQL — an admin
 * (`canEditAny`) loads any status/visibility; a non-permitted viewer gets `null` with no
 * existence oracle (indistinguishable from absent). Read-authz floor enforced in-store, never delegated.
 */
export async function getById(
  db: Querier<ContentSchema>,
  id: string,
  viewer?: Actor | null,
  _opts: StoreOpts = {}
): Promise<ContentEntry | null> {
  const filters: SQL[] = [drizzle.eq(contentEntries.id, id)];
  const vis = visibilityPredicate(viewer);
  if (vis) filters.push(vis);
  const [row] = await db
    .select()
    .from(contentEntries)
    .where(drizzle.and(...filters))
    .limit(1);
  return row ? await entryWithTerms(db, row) : null;
}

/**
 * Fail-closed guard for the host-injected sanitizer (mirrors comments). A missing/non-function
 * `sanitize` throws BEFORE any DB access — a forgotten sanitizer never silently stores raw HTML.
 */
export function assertSanitize(
  sanitize: unknown,
  verb: string
): asserts sanitize is Sanitize {
  if (typeof sanitize !== "function") {
    throw new ContentSanitizationError(
      `${verb}: sanitize must be a function, got ${typeof sanitize}`
    );
  }
}

/**
 * Create or update a content entry. The HTML `body` is sanitized on store (XSS hard floor):
 * `sanitize` is REQUIRED and applied to `body` on both the insert and update paths; the SANITIZED
 * value is persisted (no raw HTML retained). `title` is plain text and is NOT sanitized — the host
 * render-escapes it. The sanitizer ENGINE is host-owned (an allowlist sanitizer appropriate to the
 * host runtime — the adopter picks one that actually strips on their runtime); this module owns the
 * contract + enforcement, never the engine.
 */
export async function put(
  db: Querier<ContentSchema>,
  raw: ContentInput,
  actor: Actor,
  sanitize: unknown,
  _opts: StoreOpts = {}
): Promise<ContentEntry> {
  assertSanitize(sanitize, "put");
  const input = normalizeContentInput(raw);
  const safeBody = sanitize(input.body);
  if (input.termIds !== undefined) {
    await validateTermIdsExist(db, input.termIds);
  }
  if (input.id) {
    const id = input.id;
    const existing = await getRow(db, id);
    assertCanModify(actor, "update", existing.author, existing.id);
    const patch: Partial<typeof contentEntries.$inferInsert> = {
      slug: input.slug,
      type: input.type,
      title: input.title,
      body: safeBody,
      updatedAt: new Date(),
      lastEditedBy: actor.id,
    };
    if (input.visibility !== null) patch.visibility = input.visibility;
    const [updated] = await catchConflict(input.type, input.slug, () =>
      db
        .update(contentEntries)
        .set(patch)
        .where(drizzle.eq(contentEntries.id, id))
        .returning()
    );
    if (input.termIds !== undefined) {
      await replaceEntryTerms(db, id, input.termIds);
    }
    return entryWithTerms(db, updated!);
  }
  const [created] = await catchConflict(input.type, input.slug, () =>
    db
      .insert(contentEntries)
      .values({
        slug: input.slug,
        type: input.type,
        title: input.title,
        body: safeBody,
        author: actor.id,
        lastEditedBy: actor.id,
        status: "draft",
        visibility: input.visibility ?? "public",
      })
      .returning()
  );
  if (input.termIds !== undefined) {
    await replaceEntryTerms(db, created!.id, input.termIds);
  }
  return entryWithTerms(db, created!);
}

export async function setVisibility(
  db: Querier<ContentSchema>,
  id: string,
  visibility: ContentVisibility,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  assertVisibilityLiteral(visibility);
  const existing = await getRow(db, id);
  assertCanModify(actor, "update", existing.author, id);
  const now = new Date();
  const [row] = await db
    .update(contentEntries)
    .set({ visibility, updatedAt: now })
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

export async function publish(
  db: Querier<ContentSchema>,
  id: string,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  await getRow(db, id);
  assertCanPublish(actor, "publish", id);
  const now = new Date();
  const [row] = await db
    .update(contentEntries)
    .set({ status: "published", publishedAt: now, updatedAt: now })
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

export async function schedule(
  db: Querier<ContentSchema>,
  id: string,
  at: Date,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  if (!(at instanceof Date) || Number.isNaN(at.getTime())) {
    throw new ContentValidationError("at", "must be a valid Date");
  }
  const now = new Date();
  if (at <= now) {
    throw new ContentValidationError(
      "at",
      "must be in the future; use publish() to go live now"
    );
  }
  await getRow(db, id);
  assertCanPublish(actor, "schedule", id);
  const [row] = await db
    .update(contentEntries)
    .set({ status: "scheduled", publishedAt: at, updatedAt: now })
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

/** System cron runner — promotes due scheduled entries to published. No actor; authz gated at schedule() time. */
export async function promoteScheduled(
  db: Querier<ContentSchema>,
  now: Date = new Date()
): Promise<EntityRef[]> {
  if (!(now instanceof Date) || Number.isNaN(now.getTime())) {
    throw new ContentValidationError("now", "must be a valid Date");
  }
  const rows = await db
    .update(contentEntries)
    .set({ status: "published", updatedAt: now })
    .where(
      drizzle.and(
        drizzle.eq(contentEntries.status, "scheduled"),
        drizzle.lte(contentEntries.publishedAt, now)
      )
    )
    .returning(REF_COLS);
  return rows;
}

export async function unpublish(
  db: Querier<ContentSchema>,
  id: string,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  await getRow(db, id);
  assertCanPublish(actor, "unpublish", id);
  const now = new Date();
  const [row] = await db
    .update(contentEntries)
    .set({ status: "draft", publishedAt: null, updatedAt: now })
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

/**
 * Take-offline floor (U1b): trashing a live (published|scheduled) entry is a publication-status transition —
 * also requires `canPublish`. Non-live (draft|trashed) → `canModify` only.
 */
export async function trash(
  db: Querier<ContentSchema>,
  id: string,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  const existing = await getRow(db, id);
  assertCanModify(actor, "trash", existing.author, id);
  if (existing.status === "published" || existing.status === "scheduled") {
    assertCanPublish(actor, "trash", id);
  }
  const now = new Date();
  const [row] = await db
    .update(contentEntries)
    .set({ status: "trashed", updatedAt: now })
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

/**
 * Restores a TRASHED entry to draft (the inverse of `trash`). Prior status is intentionally not
 * persisted (no column) — restored content re-enters review and must be re-published by the admin.
 *
 * Scoped to `status='trashed'` ONLY: on a non-trashed entry this is a no-op that returns the entry's
 * ref unchanged. This keeps restore from being a publish-state bypass — `restore` must NOT be a path
 * for a `canModify`-but-not-`canPublish` actor to move a LIVE (published/scheduled) entry to draft
 * (that liveness transition is gated by `unpublish`/`assertCanPublish`). Idempotent: a second restore
 * sees `draft`, matches no row, and no-ops.
 */
export async function restore(
  db: Querier<ContentSchema>,
  id: string,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  const existing = await getRow(db, id);
  assertCanModify(actor, "restore", existing.author, id);
  const now = new Date();
  const [row] = await db
    .update(contentEntries)
    .set({ status: "draft", updatedAt: now })
    .where(drizzle.and(drizzle.eq(contentEntries.id, id), drizzle.eq(contentEntries.status, "trashed")))
    .returning(REF_COLS);
  return row ?? { id: existing.id, slug: existing.slug, type: existing.type };
}

/**
 * Take-offline floor (U1b): removing a live (published|scheduled) entry is a publication-status transition —
 * also requires `canPublish`. Non-live (draft|trashed) → `canModify` only.
 */
export async function remove(
  db: Querier<ContentSchema>,
  id: string,
  actor: Actor,
  _opts: StoreOpts = {}
): Promise<EntityRef> {
  const existing = await getRow(db, id);
  assertCanModify(actor, "remove", existing.author, id);
  if (existing.status === "published" || existing.status === "scheduled") {
    assertCanPublish(actor, "remove", id);
  }
  const [row] = await db
    .delete(contentEntries)
    .where(drizzle.eq(contentEntries.id, id))
    .returning(REF_COLS);
  return row!;
}

/** Idempotent base DDL for content_entries — v0.0.1 shape only (visibility/search are separate migrations). */
export const contentEntriesBaseMigrationSql = (table = "content_entries") =>
  `
CREATE TABLE IF NOT EXISTS ${table} (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug         text NOT NULL,
  type         text NOT NULL,
  title        text NOT NULL,
  body         text NOT NULL DEFAULT '',
  status       text NOT NULL DEFAULT 'draft',
  published_at timestamptz(3),
  author       text NOT NULL,
  created_at   timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at   timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS content_entries_type_slug_uq ON ${table} (type, slug);
CREATE INDEX IF NOT EXISTS content_entries_type_status_pub_idx ON ${table} (type, status, published_at DESC);
`.trim();

/** Additive, idempotent-guarded DDL for adopters migrating existing content_entries tables (spec §7). */
export const contentVisibilityMigrationSql = (table = "content_entries") =>
  `
ALTER TABLE ${table} ADD COLUMN IF NOT EXISTS visibility text NOT NULL DEFAULT 'public';
CREATE INDEX IF NOT EXISTS content_entries_type_status_vis_pub_idx ON ${table} (type, status, visibility, published_at DESC);
`.trim();