import { and, eq, sql } from "drizzle-orm";
import type { PgTransaction } from "drizzle-orm/pg-core";
import {
  tenantEntityPages,
  tenantEntityPageAliases,
  tenantEntityPageLinks,
  tenantEntityPageSections,
  tenantEntitySectionSources,
} from "@thinkwork/database-pg/schema";
import { db as defaultDb } from "../db.js";
import {
  canPromote,
  deriveSourceFacetType,
  isKnownSectionSourceKind,
  type FactCitation,
  type FacetType,
} from "./facet-types.js";

export type DbClient = typeof defaultDb | PgTransaction<any, any, any>;

export interface TenantEntityPageRow {
  id: string;
  tenant_id: string;
  type: "entity" | "topic" | "decision";
  entity_subtype: string;
  slug: string;
  title: string;
  summary: string | null;
  body_md: string | null;
  status: "active" | "archived";
  created_at: Date;
  updated_at: Date;
}

export interface FindOrCreateTenantEntityPageInput {
  tenantId: string;
  type?: "entity" | "topic" | "decision";
  subtype: string;
  slug: string;
  title: string;
  summary?: string | null;
  bodyMd?: string | null;
  aliases?: string[];
}

export interface WriteFacetSectionInput {
  tenantId: string;
  pageId: string;
  facetType: FacetType;
  sectionSlug: string;
  heading: string;
  content: string | null | undefined;
  sources: FactCitation[];
  position?: number;
  allowPromotion?: boolean;
}

export interface FindTenantEntityPageBySlugInput {
  tenantId: string;
  type?: "entity" | "topic" | "decision";
  subtype: string;
  slug: string;
}

export interface UpsertTenantEntityPageLinkInput {
  fromPageId: string;
  toPageId: string;
  relationshipSlug: string;
  context?: string | null;
}

export interface TenantEntitySectionForBody {
  heading: string;
  body_md: string | null;
  position: number;
  status?: string | null;
}

export interface AcrossSurfaceSourceHit {
  pageTable: "wiki_pages" | "tenant_entity_pages";
  pageId: string;
  sectionId: string;
  sourceKind: string;
  sourceRef: string;
  title: string;
  slug: string;
  entitySubtype: string | null;
}

export async function findOrCreateTenantEntityPage(
  input: FindOrCreateTenantEntityPageInput,
  db: DbClient = defaultDb,
): Promise<TenantEntityPageRow> {
  const subtype = normalizeTenantSubtype(input.subtype);
  const slug = normalizeSlug(input.slug);
  const title = input.title.trim();
  if (!slug) throw new Error("tenant entity slug is required");
  if (!title) throw new Error("tenant entity title is required");

  const existing = await db
    .select()
    .from(tenantEntityPages)
    .where(
      and(
        eq(tenantEntityPages.tenant_id, input.tenantId),
        eq(tenantEntityPages.type, input.type ?? "entity"),
        eq(tenantEntityPages.entity_subtype, subtype),
        eq(tenantEntityPages.slug, slug),
      ),
    )
    .limit(1);
  if (existing[0]) return existing[0] as TenantEntityPageRow;

  const [page] = await db
    .insert(tenantEntityPages)
    .values({
      tenant_id: input.tenantId,
      type: input.type ?? "entity",
      entity_subtype: subtype,
      slug,
      title,
      summary: input.summary ?? null,
      body_md: input.bodyMd ?? null,
    })
    .returning();

  const aliases = [
    ...new Set([...(input.aliases ?? []), title].map(normalizeAlias)),
  ]
    .filter(Boolean)
    .map((alias) => ({
      page_id: page.id,
      alias,
      source: "brain",
    }));
  if (aliases.length > 0) {
    await db
      .insert(tenantEntityPageAliases)
      .values(aliases)
      .onConflictDoNothing();
  }

  return page as TenantEntityPageRow;
}

export async function findTenantEntityPageBySlug(
  input: FindTenantEntityPageBySlugInput,
  db: DbClient = defaultDb,
): Promise<TenantEntityPageRow | null> {
  const rows = await db
    .select()
    .from(tenantEntityPages)
    .where(
      and(
        eq(tenantEntityPages.tenant_id, input.tenantId),
        eq(tenantEntityPages.type, input.type ?? "entity"),
        eq(
          tenantEntityPages.entity_subtype,
          normalizeTenantSubtype(input.subtype),
        ),
        eq(tenantEntityPages.slug, normalizeSlug(input.slug)),
        eq(tenantEntityPages.status, "active"),
      ),
    )
    .limit(1);
  return (rows[0] as TenantEntityPageRow | undefined) ?? null;
}

export async function upsertTenantEntityPageLink(
  input: UpsertTenantEntityPageLinkInput,
  db: DbClient = defaultDb,
): Promise<boolean> {
  if (input.fromPageId === input.toPageId) return false;
  const relationshipSlug = normalizeTenantSubtype(input.relationshipSlug);
  const returned = await db
    .insert(tenantEntityPageLinks)
    .values({
      from_page_id: input.fromPageId,
      to_page_id: input.toPageId,
      kind: relationshipSlug,
      context: input.context ?? null,
    })
    .onConflictDoNothing()
    .returning({ id: tenantEntityPageLinks.id });
  return returned.length > 0;
}

export async function recordTenantEntitySectionSources(
  args: {
    tenantId: string;
    sectionId: string;
    sources: FactCitation[];
  },
  db: DbClient = defaultDb,
): Promise<void> {
  if (args.sources.length === 0) return;
  for (const source of args.sources) {
    if (!isKnownSectionSourceKind(source.kind)) {
      throw new Error(`unknown Brain source kind: ${source.kind}`);
    }
    if (!source.ref.trim()) {
      throw new Error(`source_ref is required for ${source.kind}`);
    }
  }
  await db
    .insert(tenantEntitySectionSources)
    .values(
      args.sources.map((source) => ({
        tenant_id: args.tenantId,
        section_id: args.sectionId,
        source_kind: source.kind,
        source_ref: source.ref,
      })),
    )
    .onConflictDoNothing();
}

export async function writeFacetSection(
  input: WriteFacetSectionInput,
  db: DbClient = defaultDb,
): Promise<{ sectionId: string; sourceFacetType: FacetType }> {
  if (input.sources.length === 0) {
    throw new Error("brain facet writes require at least one source citation");
  }
  const sourceFacetType = deriveSourceFacetType(input.sources);
  if (!input.allowPromotion && !canPromote(sourceFacetType, input.facetType)) {
    throw new Error(
      `cannot write ${sourceFacetType} sources into ${input.facetType} facet without explicit promotion`,
    );
  }

  const aggregation = {
    facet_type: input.facetType,
    source_facet_type: sourceFacetType,
  };
  const content = normalizeTenantEntitySectionBody(input.content);
  const [section] = await db
    .insert(tenantEntityPageSections)
    .values({
      page_id: input.pageId,
      section_slug: input.sectionSlug,
      heading: input.heading,
      body_md: content,
      position: input.position ?? 0,
      aggregation,
      last_source_at: new Date(),
    })
    .onConflictDoUpdate({
      target: [
        tenantEntityPageSections.page_id,
        tenantEntityPageSections.section_slug,
      ],
      set: {
        heading: input.heading,
        body_md: content,
        position: input.position ?? 0,
        aggregation,
        status: "active",
        last_source_at: new Date(),
        updated_at: new Date(),
      },
    })
    .returning({ id: tenantEntityPageSections.id });

  await recordTenantEntitySectionSources(
    {
      tenantId: input.tenantId,
      sectionId: section.id,
      sources: input.sources,
    },
    db,
  );

  return { sectionId: section.id, sourceFacetType };
}

export async function syncTenantEntityPageBodyFromSections(args: {
  pageId: string;
  db?: DbClient;
}): Promise<{ bodyMd: string; sectionCount: number }> {
  const db = args.db ?? defaultDb;
  const sections = await db
    .select({
      heading: tenantEntityPageSections.heading,
      body_md: tenantEntityPageSections.body_md,
      position: tenantEntityPageSections.position,
      status: tenantEntityPageSections.status,
    })
    .from(tenantEntityPageSections)
    .where(eq(tenantEntityPageSections.page_id, args.pageId))
    .orderBy(tenantEntityPageSections.position);
  const activeSections = sections.filter(
    (section) => section.status === "active",
  );
  const bodyMd = composeTenantEntityPageBody(activeSections);
  await db
    .update(tenantEntityPages)
    .set({
      body_md: bodyMd,
      last_compiled_at: new Date(),
      updated_at: new Date(),
    })
    .where(eq(tenantEntityPages.id, args.pageId));
  return { bodyMd, sectionCount: activeSections.length };
}

export function composeTenantEntityPageBody(
  sections: TenantEntitySectionForBody[],
): string {
  return sections
    .filter((section) => (section.body_md ?? "").trim().length > 0)
    .sort((a, b) => a.position - b.position)
    .map((section) => {
      const heading = section.heading.trim();
      const body = (section.body_md ?? "").trim();
      return heading ? `## ${heading}\n\n${body}` : body;
    })
    .join("\n\n")
    .trim();
}

export function normalizeTenantEntitySectionBody(rawBody: unknown): string {
  return typeof rawBody === "string" ? rawBody : "";
}

export async function findPageSourcesAcrossSurfaces(
  args: {
    tenantId: string;
    sourceKind: string;
    sourceRef: string;
    ownerId?: string | null;
  },
  db: DbClient = defaultDb,
): Promise<AcrossSurfaceSourceHit[]> {
  const ownerPredicate = args.ownerId
    ? sql`AND wp.owner_id = ${args.ownerId}`
    : sql``;
  const result = await db.execute(sql`
		SELECT
			'wiki_pages'::text AS "pageTable",
			wp.id AS "pageId",
			ws.id AS "sectionId",
			wss.source_kind AS "sourceKind",
			wss.source_ref AS "sourceRef",
			wp.title,
			wp.slug,
			wp.entity_subtype AS "entitySubtype"
		FROM wiki.section_sources wss
		INNER JOIN wiki.page_sections ws ON ws.id = wss.section_id
		INNER JOIN wiki.pages wp ON wp.id = ws.page_id
		WHERE wp.tenant_id = ${args.tenantId}
			${ownerPredicate}
			AND wp.status = 'active'
			AND wss.source_kind = ${args.sourceKind}
			AND wss.source_ref = ${args.sourceRef}
		UNION ALL
		SELECT
			'tenant_entity_pages'::text AS "pageTable",
			tep.id AS "pageId",
			teps.id AS "sectionId",
			tess.source_kind AS "sourceKind",
			tess.source_ref AS "sourceRef",
			tep.title,
			tep.slug,
			tep.entity_subtype AS "entitySubtype"
		FROM brain.section_sources tess
		INNER JOIN brain.page_sections teps ON teps.id = tess.section_id
		INNER JOIN brain.pages tep ON tep.id = teps.page_id
		WHERE tess.tenant_id = ${args.tenantId}
			AND tep.status = 'active'
			AND tess.source_kind = ${args.sourceKind}
			AND tess.source_ref = ${args.sourceRef}
	`);
  return (result as unknown as { rows?: AcrossSurfaceSourceHit[] }).rows ?? [];
}

export function normalizeTenantSubtype(subtype: string): string {
  const normalized = subtype.trim().toLowerCase();
  if (!/^[a-z][a-z0-9_]{0,63}$/.test(normalized)) {
    throw new Error(`unsupported tenant entity subtype: ${subtype}`);
  }
  return normalized;
}

function normalizeSlug(value: string): string {
  return value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "");
}

function normalizeAlias(value: string): string {
  return value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, " ")
    .replace(/\s+/g, " ")
    .trim();
}
