import type { Kysely } from "kysely";
import { sql } from "kysely";
import { DatabaseError } from "@/lib/error";
import { logError } from "@/lib/logger";
import type { DB } from "../datastore/db";

/**
 * Metadata access for the local EDM template store.
 *
 * Takes a Kysely handle directly rather than a Hono context (matching
 * HotDealsService.getDeal(db, …)) because EDMService is a process-wide
 * singleton, not a per-request injected service.
 *
 * Template BYTES are not here — they live in GCS, see lib/storage/edm-storage.ts.
 * This module exists so the console can list hundreds of templates without
 * paging object storage.
 */

export type EDMTemplateRow = {
  id: string;
  folder_id: string | null;
  name: string;
  generation: string;
  source: string;
  active_version_id: string | null;
  created_at: Date;
  updated_at: Date;
};

export type EDMVersionRow = {
  id: string;
  template_id: string;
  version_number: number;
  name: string;
  subject: string;
  storage_path: string;
  html_sha256: string | null;
  generate_plain_content: boolean;
  active: number;
  created_at: Date;
};

export type EDMAssetManifestEntry = {
  original: string;
  path: string;
  url: string;
  sha256: string;
};

export const EDMRepository = {
  // ── templates ─────────────────────────────────────────────────────────────

  async FindTemplateById(db: Kysely<DB>, id: string) {
    try {
      return await db
        .selectFrom("edm_templates")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed finding EDM template");
      throw new DatabaseError({ error: err, message: "Failed finding EDM template" });
    }
  },

  /**
   * Every template with its versions, in one round trip.
   *
   * Versions are aggregated into a jsonb array rather than fetched per template:
   * the console renders the full list client-side (search and filter are
   * in-memory), so an N+1 over ~1000 templates would be the page's whole cost.
   * html_content is deliberately absent — the list never reads it, and pulling
   * a GCS object per template here would make the page unusable.
   */
  async ListTemplatesWithVersions(db: Kysely<DB>, opts?: { folderId?: string | null }) {
    try {
      let query = db
        .selectFrom("edm_templates as t")
        .select([
          "t.id",
          "t.name",
          "t.generation",
          "t.source",
          "t.folder_id",
          "t.active_version_id",
          "t.created_at",
          "t.updated_at",
          sql<EDMVersionRow[]>`
            coalesce(
              (
                select jsonb_agg(
                  jsonb_build_object(
                    'id', v.id,
                    'template_id', v.template_id,
                    'version_number', v.version_number,
                    'name', v.name,
                    'subject', v.subject,
                    'storage_path', v.storage_path,
                    'html_sha256', v.html_sha256,
                    'generate_plain_content', v.generate_plain_content,
                    'active', v.active,
                    'created_at', v.created_at
                  )
                  order by v.version_number desc
                )
                from edm_template_versions v
                where v.template_id = t.id
              ),
              '[]'::jsonb
            )
          `.as("versions"),
        ])
        .orderBy("t.updated_at", "desc");

      if (opts?.folderId !== undefined) {
        query =
          opts.folderId === null
            ? query.where("t.folder_id", "is", null)
            : query.where("t.folder_id", "=", opts.folderId);
      }

      return await query.execute();
    } catch (err) {
      logError(err, "Failed listing EDM templates");
      throw new DatabaseError({ error: err, message: "Failed listing EDM templates" });
    }
  },

  async CreateTemplate(
    db: Kysely<DB>,
    entry: {
      id: string;
      name: string;
      folderId?: string | null;
      source?: string;
      createdBy?: string | null;
    },
  ) {
    try {
      return await db
        .insertInto("edm_templates")
        .values({
          id: entry.id,
          name: entry.name,
          folder_id: entry.folderId ?? null,
          generation: "dynamic",
          source: entry.source ?? "local",
          created_by: entry.createdBy ?? null,
        })
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err, "Failed creating EDM template");
      throw new DatabaseError({ error: err, message: "Failed creating EDM template" });
    }
  },

  async UpdateTemplate(
    db: Kysely<DB>,
    id: string,
    patch: { name?: string; folderId?: string | null; source?: string },
  ) {
    try {
      const values: Record<string, unknown> = { updated_at: new Date() };
      if (patch.name !== undefined) values.name = patch.name;
      if (patch.folderId !== undefined) values.folder_id = patch.folderId;
      if (patch.source !== undefined) values.source = patch.source;

      return await db
        .updateTable("edm_templates")
        .set(values as never)
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed updating EDM template");
      throw new DatabaseError({ error: err, message: "Failed updating EDM template" });
    }
  },

  async DeleteTemplate(db: Kysely<DB>, id: string) {
    try {
      // active_version_id FKs into the versions we are about to cascade away;
      // clearing it first keeps the delete order from tripping the constraint.
      await db
        .updateTable("edm_templates")
        .set({ active_version_id: null })
        .where("id", "=", id)
        .execute();

      await db.deleteFrom("edm_templates").where("id", "=", id).execute();
    } catch (err) {
      logError(err, "Failed deleting EDM template");
      throw new DatabaseError({ error: err, message: "Failed deleting EDM template" });
    }
  },

  // ── versions ──────────────────────────────────────────────────────────────

  async ListVersions(db: Kysely<DB>, templateId: string) {
    try {
      return await db
        .selectFrom("edm_template_versions")
        .selectAll()
        .where("template_id", "=", templateId)
        .orderBy("version_number", "desc")
        .execute();
    } catch (err) {
      logError(err, "Failed listing EDM template versions");
      throw new DatabaseError({
        error: err,
        message: "Failed listing EDM template versions",
      });
    }
  },

  async FindVersionById(db: Kysely<DB>, versionId: string) {
    try {
      return await db
        .selectFrom("edm_template_versions")
        .selectAll()
        .where("id", "=", versionId)
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed finding EDM template version");
      throw new DatabaseError({
        error: err,
        message: "Failed finding EDM template version",
      });
    }
  },

  /**
   * Allocate the next version number for a template.
   *
   * Runs inside the caller's transaction and takes a row lock on the parent
   * template, so two concurrent saves serialise instead of both computing the
   * same number and colliding on the (template_id, version_number) unique
   * constraint.
   */
  async NextVersionNumber(db: Kysely<DB>, templateId: string): Promise<number> {
    const locked = await sql<{ max: number | null }>`
      select max(v.version_number) as max
      from edm_template_versions v
      where v.template_id = (
        select t.id from edm_templates t where t.id = ${templateId} for update
      )
    `.execute(db);
    return (locked.rows[0]?.max ?? 0) + 1;
  },

  async CreateVersion(
    db: Kysely<DB>,
    entry: {
      templateId: string;
      versionNumber: number;
      name: string;
      subject: string;
      storagePath: string;
      htmlSha256?: string | null;
      generatePlainContent?: boolean;
      active?: number;
      assetManifest?: EDMAssetManifestEntry[] | null;
      createdBy?: string | null;
    },
  ) {
    try {
      return await db
        .insertInto("edm_template_versions")
        .values({
          template_id: entry.templateId,
          version_number: entry.versionNumber,
          name: entry.name,
          subject: entry.subject,
          storage_path: entry.storagePath,
          html_sha256: entry.htmlSha256 ?? null,
          generate_plain_content: entry.generatePlainContent ?? true,
          active: entry.active ?? 1,
          asset_manifest: (entry.assetManifest
            ? JSON.stringify(entry.assetManifest)
            : null) as never,
          created_by: entry.createdBy ?? null,
        })
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err, "Failed creating EDM template version");
      throw new DatabaseError({
        error: err,
        message: "Failed creating EDM template version",
      });
    }
  },

  /**
   * Point the template at a version and collapse every other version's active
   * flag, so edm_templates.active_version_id and the 0|1 flags can never
   * disagree about which version is live.
   */
  async SetActiveVersion(db: Kysely<DB>, templateId: string, versionId: string) {
    try {
      await db
        .updateTable("edm_template_versions")
        .set({ active: 0 })
        .where("template_id", "=", templateId)
        .where("id", "!=", versionId)
        .execute();

      await db
        .updateTable("edm_template_versions")
        .set({ active: 1 })
        .where("id", "=", versionId)
        .execute();

      await db
        .updateTable("edm_templates")
        .set({ active_version_id: versionId, updated_at: new Date() })
        .where("id", "=", templateId)
        .execute();
    } catch (err) {
      logError(err, "Failed setting active EDM version");
      throw new DatabaseError({
        error: err,
        message: "Failed setting active EDM version",
      });
    }
  },

  // ── folders ───────────────────────────────────────────────────────────────

  async ListFolders(db: Kysely<DB>) {
    try {
      return await db
        .selectFrom("edm_folders")
        .selectAll()
        .orderBy("sort_order", "asc")
        .orderBy("name", "asc")
        .execute();
    } catch (err) {
      logError(err, "Failed listing EDM folders");
      throw new DatabaseError({ error: err, message: "Failed listing EDM folders" });
    }
  },

  async CreateFolder(
    db: Kysely<DB>,
    entry: { name: string; parentId?: string | null; createdBy?: string | null },
  ) {
    try {
      return await db
        .insertInto("edm_folders")
        .values({
          name: entry.name,
          parent_id: entry.parentId ?? null,
          created_by: entry.createdBy ?? null,
        })
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err, "Failed creating EDM folder");
      throw new DatabaseError({ error: err, message: "Failed creating EDM folder" });
    }
  },

  // ── browse ────────────────────────────────────────────────────────────────

  async ListChildFolders(db: Kysely<DB>, parentId: string | null) {
    try {
      let query = db.selectFrom("edm_folders").selectAll();
      query =
        parentId === null
          ? query.where("parent_id", "is", null)
          : query.where("parent_id", "=", parentId);

      return await query.orderBy("sort_order", "asc").orderBy("name", "asc").execute();
    } catch (err) {
      logError(err, "Failed listing child EDM folders");
      throw new DatabaseError({ error: err, message: "Failed listing child folders" });
    }
  },

  /**
   * Templates filed directly in one folder — not the whole subtree.
   *
   * The browser shows a folder's own contents the way a file manager does;
   * descendants are reached by opening the child folder, not by flattening.
   */
  async ListTemplatesInFolder(db: Kysely<DB>, folderId: string | null) {
    try {
      let query = db
        .selectFrom("edm_templates as t")
        .leftJoin("edm_template_versions as v", "v.id", "t.active_version_id")
        .select([
          "t.id",
          "t.name",
          "t.source",
          "t.folder_id",
          "t.updated_at",
          "t.active_version_id",
          "v.subject as subject",
          "v.version_number as version_number",
        ]);

      query =
        folderId === null
          ? query.where("t.folder_id", "is", null)
          : query.where("t.folder_id", "=", folderId);

      return await query.orderBy("t.name", "asc").execute();
    } catch (err) {
      logError(err, "Failed listing templates in folder");
      throw new DatabaseError({ error: err, message: "Failed listing templates in folder" });
    }
  },

  /**
   * Images referenced by the templates in this folder.
   *
   * Assets have no folder of their own — they are content-addressed objects in
   * one flat bucket prefix, deliberately, so that the same logo used by 500
   * templates is stored once and a URL already sitting in someone's inbox never
   * breaks. So "images in this folder" is derived: the union of the asset
   * manifests of the folder's templates, deduped by storage path.
   */
  async ListFolderAssets(db: Kysely<DB>, folderId: string | null) {
    try {
      let query = db
        .selectFrom("edm_template_versions as v")
        .innerJoin("edm_templates as t", "t.active_version_id", "v.id")
        .select(["v.asset_manifest", "t.id as template_id", "t.name as template_name"]);

      query =
        folderId === null
          ? query.where("t.folder_id", "is", null)
          : query.where("t.folder_id", "=", folderId);

      const rows = await query.execute();

      const byPath = new Map<
        string,
        { original: string; path: string; url: string; sha256: string; usedBy: string[] }
      >();

      for (const row of rows) {
        const manifest = (row.asset_manifest ?? []) as unknown as EDMAssetManifestEntry[];
        if (!Array.isArray(manifest)) continue;

        for (const entry of manifest) {
          if (!entry?.path) continue;
          const existing = byPath.get(entry.path);
          if (existing) {
            if (!existing.usedBy.includes(row.template_name)) {
              existing.usedBy.push(row.template_name);
            }
          } else {
            byPath.set(entry.path, { ...entry, usedBy: [row.template_name] });
          }
        }
      }

      return [...byPath.values()];
    } catch (err) {
      logError(err, "Failed listing folder assets");
      throw new DatabaseError({ error: err, message: "Failed listing folder assets" });
    }
  },

  async FindFolderById(db: Kysely<DB>, id: string) {
    try {
      return await db
        .selectFrom("edm_folders")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed finding EDM folder");
      throw new DatabaseError({ error: err, message: "Failed finding EDM folder" });
    }
  },

  async RenameFolder(db: Kysely<DB>, id: string, name: string) {
    try {
      return await db
        .updateTable("edm_folders")
        .set({ name, updated_at: new Date() })
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed renaming EDM folder");
      throw new DatabaseError({ error: err, message: "Failed renaming EDM folder" });
    }
  },

  /**
   * Root-to-folder name path, e.g. ["Campaigns", "Welcome"].
   *
   * Used to place uploaded images under a matching prefix in the bucket so a
   * folder's assets can be found and removed as a unit.
   */
  async FolderNamePath(db: Kysely<DB>, folderId: string): Promise<string[]> {
    const rows = await sql<{ name: string; depth: number }>`
      with recursive up as (
        select id, parent_id, name, 0 as depth
        from edm_folders where id = ${folderId}::uuid
        union all
        select f.id, f.parent_id, f.name, up.depth + 1
        from edm_folders f join up on f.id = up.parent_id
      )
      select name, depth from up order by depth desc
    `.execute(db);
    return rows.rows.map((r) => r.name);
  },

  /**
   * Templates whose active version lists this asset in its manifest.
   *
   * Manifest-based, so it is a lower bound: a template edited by hand can
   * reference an image URL without the manifest recording it. Good enough to
   * warn with, not to gate on.
   */
  async FindTemplatesUsingAsset(db: Kysely<DB>, assetPath: string): Promise<string[]> {
    try {
      const rows = await db
        .selectFrom("edm_template_versions as v")
        .innerJoin("edm_templates as t", "t.active_version_id", "v.id")
        .select(["t.name", "v.asset_manifest"])
        .execute();

      return rows
        .filter((r) => {
          const manifest = (r.asset_manifest ?? []) as unknown as EDMAssetManifestEntry[];
          return Array.isArray(manifest) && manifest.some((m) => m?.path === assetPath);
        })
        .map((r) => r.name);
    } catch (err) {
      logError(err, "Failed finding templates using asset");
      return [];
    }
  },

  /** Every folder id in this folder's subtree, inclusive. */
  async CollectSubtreeFolderIds(db: Kysely<DB>, folderId: string): Promise<string[]> {
    const rows = await sql<{ id: string }>`
      with recursive subtree as (
        select id from edm_folders where id = ${folderId}::uuid
        union all
        select f.id from edm_folders f join subtree s on f.parent_id = s.id
      )
      select id from subtree
    `.execute(db);
    return rows.rows.map((r) => r.id);
  },

  /** Template ids filed anywhere inside this folder's subtree. */
  async CollectSubtreeTemplateIds(db: Kysely<DB>, folderId: string): Promise<string[]> {
    const folderIds = await this.CollectSubtreeFolderIds(db, folderId);
    if (folderIds.length === 0) return [];
    const rows = await db
      .selectFrom("edm_templates")
      .select("id")
      .where("folder_id", "in", folderIds)
      .execute();
    return rows.map((r) => r.id);
  },

  /**
   * Re-file a folder's direct contents somewhere else, leaving it empty.
   *
   * Only direct children move; grandchildren travel with their own parent
   * folder, which keeps the structure the author built rather than flattening
   * it into the destination.
   */
  async MoveFolderContents(
    db: Kysely<DB>,
    fromId: string,
    toId: string | null,
  ): Promise<{ templates: number; folders: number }> {
    try {
      const templates = await db
        .updateTable("edm_templates")
        .set({ folder_id: toId, updated_at: new Date() })
        .where("folder_id", "=", fromId)
        .executeTakeFirst();

      const folders = await db
        .updateTable("edm_folders")
        .set({ parent_id: toId, updated_at: new Date() })
        .where("parent_id", "=", fromId)
        .executeTakeFirst();

      return {
        templates: Number(templates.numUpdatedRows ?? 0),
        folders: Number(folders.numUpdatedRows ?? 0),
      };
    } catch (err) {
      logError(err, "Failed moving EDM folder contents");
      throw new DatabaseError({ error: err, message: "Failed moving folder contents" });
    }
  },

  /**
   * Delete a folder.
   *
   * Templates and child folders are NOT deleted here — the schema sets their
   * parent to null, so they resurface at the root. Destroying them is a
   * separate, explicit choice made by the caller, because a template is
   * referenced by live mail.
   */
  async DeleteFolder(db: Kysely<DB>, id: string) {
    try {
      await db.deleteFrom("edm_folders").where("id", "=", id).execute();
    } catch (err) {
      logError(err, "Failed deleting EDM folder");
      throw new DatabaseError({ error: err, message: "Failed deleting EDM folder" });
    }
  },

  /**
   * Reparent a folder, refusing any move that would put it inside its own
   * subtree — that would detach the whole branch from the root and make it
   * unreachable in the console with no way to fix it from the UI.
   */
  async MoveFolder(db: Kysely<DB>, id: string, newParentId: string | null) {
    if (id === newParentId) {
      throw new DatabaseError({
        error: new Error("cycle"),
        message: "A folder cannot be its own parent",
      });
    }

    if (newParentId) {
      const cycle = await sql<{ id: string }>`
        with recursive ancestors as (
          select id, parent_id from edm_folders where id = ${newParentId}::uuid
          union all
          select f.id, f.parent_id
          from edm_folders f
          join ancestors a on f.id = a.parent_id
        )
        select id from ancestors where id = ${id}::uuid
      `.execute(db);

      if (cycle.rows.length > 0) {
        throw new DatabaseError({
          error: new Error("cycle"),
          message: "Cannot move a folder into its own subtree",
        });
      }
    }

    try {
      return await db
        .updateTable("edm_folders")
        .set({ parent_id: newParentId, updated_at: new Date() })
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err, "Failed moving EDM folder");
      throw new DatabaseError({ error: err, message: "Failed moving EDM folder" });
    }
  },
};