import { DatabaseError } from "@/lib/error";
import { logError } from "@/lib/logger";
import { sql } from "kysely";
import { BaseRepository, type RepositoryContext } from "../datastore/repository";
import type { DashboardManifest } from "./dashboard-bundle.types";

export type DashboardAuditEntry = {
  dashboardId: string | null;
  action: string;
  actorId?: string | null;
  actorEmail?: string | null;
  metadata?: Record<string, unknown> | null;
};

export const DashboardRepository = (ctx: RepositoryContext) => {
  const { datastore } = new BaseRepository(ctx);

  // jsonb columns are fed a JSON string and cast by Postgres, matching how
  // admin_activity_log.metadata is written elsewhere in this codebase.
  const toJson = (value: unknown) =>
    value === null || value === undefined
      ? null
      : (JSON.stringify(value) as unknown as never);

  // -------------------------------------------------------------------------
  // dashboards
  // -------------------------------------------------------------------------

  const CreateDashboard = async (entry: {
    name: string;
    slug: string;
    description: string | null;
    createdBy: string;
  }) => {
    try {
      return await datastore
        .insertInto("dashboards")
        .values({
          name: entry.name,
          slug: entry.slug,
          description: entry.description,
          status: "draft",
          created_by: entry.createdBy,
        })
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed creating dashboard" });
    }
  };

  const FindById = async (id: string) => {
    try {
      return await datastore
        .selectFrom("dashboards")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed fetching dashboard" });
    }
  };

  const FindBySlug = async (slug: string) => {
    try {
      return await datastore
        .selectFrom("dashboards")
        .selectAll()
        .where("slug", "=", slug)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed fetching dashboard by slug" });
    }
  };

  /**
   * @param dashboardIds null = unrestricted (caller already established the
   *        user is a dashboard admin). An empty array means "scoped to nothing"
   *        and short-circuits to [] — never to "everything".
   */
  const ListDashboards = async (dashboardIds: string[] | null) => {
    if (dashboardIds !== null && dashboardIds.length === 0) return [];
    try {
      let query = datastore
        .selectFrom("dashboards as d")
        .leftJoin("dashboard_versions as v", "v.id", "d.current_version_id")
        .select((eb) => [
          // Correlated EXISTS, evaluated by Postgres as part of THIS query —
          // one round trip for the whole list, not one per dashboard. Counts
          // only links that a viewer could actually use right now.
          eb
            .exists(
              eb
                .selectFrom("dashboard_public_links as pl")
                .select("pl.id")
                .whereRef("pl.dashboard_id", "=", "d.id")
                .where("pl.is_revoked", "=", false)
                .where((w) =>
                  w.or([
                    w("pl.expires_at", "is", null),
                    w("pl.expires_at", ">", sql<Date>`now()`),
                  ]),
                ),
            )
            .as("has_public_link"),
          "d.id",
          "d.name",
          "d.slug",
          "d.description",
          "d.status",
          "d.cover_image_path",
          "d.current_version_id",
          "d.created_by",
          "d.created_at",
          "d.updated_at",
          "v.version_number as current_version_number",
          "v.entry_point as current_entry_point",
          "v.size_bytes as current_size_bytes",
          "v.file_count as current_file_count",
          "v.created_at as current_version_created_at",
        ])
        .orderBy("d.updated_at", "desc");

      if (dashboardIds !== null) {
        query = query.where("d.id", "in", dashboardIds);
      }
      return await query.execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing dashboards" });
    }
  };

  const UpdateMeta = async (
    id: string,
    patch: {
      name?: string;
      description?: string | null;
      status?: string;
      cover_image_path?: string | null;
    },
  ) => {
    try {
      return await datastore
        .updateTable("dashboards")
        .set({ ...patch, updated_at: sql`now()` })
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed updating dashboard" });
    }
  };

  const SetCurrentVersion = async (
    dashboardId: string,
    versionId: string,
    status?: string,
  ) => {
    try {
      return await datastore
        .updateTable("dashboards")
        .set({
          current_version_id: versionId,
          ...(status ? { status } : {}),
          updated_at: sql`now()`,
        })
        .where("id", "=", dashboardId)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed setting current version" });
    }
  };

  const DeleteDashboard = async (id: string) => {
    try {
      await datastore.deleteFrom("dashboards").where("id", "=", id).execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed deleting dashboard" });
    }
  };

  // -------------------------------------------------------------------------
  // dashboard_versions
  // -------------------------------------------------------------------------

  const GetMaxVersionNumber = async (dashboardId: string): Promise<number> => {
    try {
      const row = await datastore
        .selectFrom("dashboard_versions")
        .select(({ fn }) => [fn.max<number>("version_number").as("max")])
        .where("dashboard_id", "=", dashboardId)
        .executeTakeFirst();
      return Number(row?.max ?? 0);
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed reading version number" });
    }
  };

  /**
   * Allocate the version number and insert the immutable version row in ONE
   * locked transaction.
   *
   * The FOR UPDATE on the parent dashboard row is what makes numbering correct:
   * it now spans the INSERT, so a second uploader blocks here, and when it
   * proceeds it reads the first uploader's COMMITTED row and allocates the next
   * number. (An earlier design took this lock in a separate transaction that
   * committed before the insert — which serialized the read but not the write,
   * so both racers read the same MAX and got the same number.)
   *
   * The transaction is entered only AFTER the GCS upload has finished, so no
   * connection is ever pinned across network I/O.
   *
   * `makeCurrent` is decided in here because it depends on the number this
   * transaction allocates: the first version always goes live, later ones only
   * when the caller asked to publish.
   */
  const InsertVersion = async (entry: {
    dashboardId: string;
    /** UUID minted before upload; already owns the storage prefix. */
    versionId: string;
    storagePrefix: string;
    entryPoint: string;
    manifest: DashboardManifest | null;
    sizeBytes: number;
    fileCount: number;
    uploadedBy: string;
    publish: boolean;
  }) => {
    try {
      return await datastore.transaction().execute(async (trx) => {
        await trx
          .selectFrom("dashboards")
          .select("id")
          .where("id", "=", entry.dashboardId)
          .forUpdate()
          .executeTakeFirst();

        const maxRow = await trx
          .selectFrom("dashboard_versions")
          .select(({ fn }) => [fn.max<number>("version_number").as("max")])
          .where("dashboard_id", "=", entry.dashboardId)
          .executeTakeFirst();
        const versionNumber = Number(maxRow?.max ?? 0) + 1;

        const makeCurrent = entry.publish || versionNumber === 1;

        const version = await trx
          .insertInto("dashboard_versions")
          .values({
            id: entry.versionId,
            dashboard_id: entry.dashboardId,
            version_number: versionNumber,
            storage_prefix: entry.storagePrefix,
            entry_point: entry.entryPoint,
            manifest: toJson(entry.manifest),
            size_bytes: entry.sizeBytes,
            file_count: entry.fileCount,
            uploaded_by: entry.uploadedBy,
          })
          .returningAll()
          .executeTakeFirstOrThrow();

        if (makeCurrent) {
          await trx
            .updateTable("dashboards")
            .set({
              current_version_id: version.id,
              ...(entry.publish ? { status: "published" } : {}),
              updated_at: sql`now()`,
            })
            .where("id", "=", entry.dashboardId)
            .execute();
        }
        return { version, madeCurrent: makeCurrent };
      });
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed recording dashboard version" });
    }
  };

  /**
   * The ONLY mutation permitted on a dashboard_versions row, and the service
   * gates it to dashboards that have never been published. Once a dashboard
   * goes live its versions are frozen.
   */
  const SetVersionEntryPoint = async (versionId: string, entryPoint: string) => {
    try {
      return await datastore
        .updateTable("dashboard_versions")
        .set({ entry_point: entryPoint })
        .where("id", "=", versionId)
        .returningAll()
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed setting entry point" });
    }
  };

  const ListVersionsByDashboard = async (dashboardId: string) => {
    try {
      return await datastore
        .selectFrom("dashboard_versions")
        .selectAll()
        .where("dashboard_id", "=", dashboardId)
        .orderBy("version_number", "desc")
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing versions" });
    }
  };

  const FindVersionById = async (versionId: string) => {
    try {
      return await datastore
        .selectFrom("dashboard_versions")
        .selectAll()
        .where("id", "=", versionId)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed fetching version" });
    }
  };

  // -------------------------------------------------------------------------
  // console_user_dashboard_access
  // -------------------------------------------------------------------------

  /** The fail-closed membership test. No row = no access. */
  const HasAccessGrant = async (userId: string, dashboardId: string) => {
    try {
      const row = await datastore
        .selectFrom("console_user_dashboard_access")
        .select("id")
        .where("console_user_id", "=", userId)
        .where("dashboard_id", "=", dashboardId)
        .executeTakeFirst();
      return row !== undefined;
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed checking dashboard access" });
    }
  };

  const ListDashboardIdsForUser = async (userId: string) => {
    try {
      const rows = await datastore
        .selectFrom("console_user_dashboard_access")
        .select("dashboard_id")
        .where("console_user_id", "=", userId)
        .execute();
      return rows.map((r) => r.dashboard_id);
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing user dashboard access" });
    }
  };

  const ListUsersForDashboard = async (dashboardId: string) => {
    try {
      return await datastore
        .selectFrom("console_user_dashboard_access as a")
        .innerJoin("console_users as u", "u.id", "a.console_user_id")
        .select([
          "a.id as grant_id",
          "a.console_user_id",
          "a.granted_by",
          "a.created_at",
          "u.email",
          "u.first_name",
          "u.last_name",
          "u.is_active",
        ])
        .where("a.dashboard_id", "=", dashboardId)
        .orderBy("u.email", "asc")
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing dashboard access" });
    }
  };

  const GrantAccess = async (
    userId: string,
    dashboardId: string,
    grantedBy: string,
  ) => {
    try {
      await datastore
        .insertInto("console_user_dashboard_access")
        .values({
          console_user_id: userId,
          dashboard_id: dashboardId,
          granted_by: grantedBy,
        })
        .onConflict((oc) =>
          oc.columns(["console_user_id", "dashboard_id"]).doNothing(),
        )
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed granting dashboard access" });
    }
  };

  const RevokeAccess = async (userId: string, dashboardId: string) => {
    try {
      await datastore
        .deleteFrom("console_user_dashboard_access")
        .where("console_user_id", "=", userId)
        .where("dashboard_id", "=", dashboardId)
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed revoking dashboard access" });
    }
  };

  /** Wizard "set access list" step — the whole list swapped atomically. */
  const ReplaceGrants = async (
    dashboardId: string,
    userIds: string[],
    grantedBy: string,
  ) => {
    try {
      await datastore.transaction().execute(async (trx) => {
        await trx
          .deleteFrom("console_user_dashboard_access")
          .where("dashboard_id", "=", dashboardId)
          .execute();
        if (userIds.length > 0) {
          await trx
            .insertInto("console_user_dashboard_access")
            .values(
              userIds.map((userId) => ({
                console_user_id: userId,
                dashboard_id: dashboardId,
                granted_by: grantedBy,
              })),
            )
            .onConflict((oc) =>
              oc.columns(["console_user_id", "dashboard_id"]).doNothing(),
            )
            .execute();
        }
      });
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed saving dashboard access list" });
    }
  };

  // -------------------------------------------------------------------------
  // dashboard_public_links
  //
  // token_hash holds sha256(rawToken); the raw token is never persisted. Only
  // the resolution path below ever selects token_hash/password_hash, and
  // neither value leaves the service layer.
  // -------------------------------------------------------------------------

  const InsertPublicLink = async (entry: {
    dashboardId: string;
    tokenHash: string;
    passwordHash: string | null;
    expiresAt: Date | null;
    createdBy: string;
  }) => {
    try {
      return await datastore
        .insertInto("dashboard_public_links")
        .values({
          dashboard_id: entry.dashboardId,
          token_hash: entry.tokenHash,
          password_hash: entry.passwordHash,
          expires_at: entry.expiresAt,
          created_by: entry.createdBy,
        })
        .returning([
          "id",
          "dashboard_id",
          "expires_at",
          "is_revoked",
          "view_count",
          "created_by",
          "created_at",
        ]) // deliberately NOT returning token_hash / password_hash
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed creating public link" });
    }
  };

  /**
   * Resolution path for a public request. Selects the password hash because the
   * service needs to know whether a password is required; the value is never
   * returned to a caller outside the service.
   */
  const FindPublicLinkByTokenHash = async (tokenHash: string) => {
    try {
      return await datastore
        .selectFrom("dashboard_public_links")
        .select([
          "id",
          "dashboard_id",
          "password_hash",
          "expires_at",
          "is_revoked",
          "view_count",
        ])
        .where("token_hash", "=", tokenHash)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed resolving public link" });
    }
  };

  const FindPublicLinkById = async (linkId: string) => {
    try {
      return await datastore
        .selectFrom("dashboard_public_links")
        .select([
          "id",
          "dashboard_id",
          "expires_at",
          "is_revoked",
          "view_count",
          "created_at",
        ])
        .where("id", "=", linkId)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed fetching public link" });
    }
  };

  /**
   * Revoke, never hard-delete: the view history references this row, and a
   * deleted row would free the token hash for reuse.
   */
  const RevokePublicLink = async (dashboardId: string, linkId: string) => {
    try {
      return await datastore
        .updateTable("dashboard_public_links")
        .set({ is_revoked: true })
        .where("id", "=", linkId)
        .where("dashboard_id", "=", dashboardId)
        .returning(["id", "dashboard_id", "is_revoked"])
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed revoking public link" });
    }
  };

  /** Atomic in SQL — never read-modify-write in app code. */
  const IncrementPublicLinkViewCount = async (linkId: string) => {
    try {
      await datastore
        .updateTable("dashboard_public_links")
        .set((eb) => ({ view_count: eb("view_count", "+", 1) }))
        .where("id", "=", linkId)
        .execute();
    } catch (err) {
      // Bookkeeping must never break serving.
      logError(err, `Failed incrementing view_count for public link ${linkId}`);
    }
  };

  const ListPublicLinksForDashboard = async (dashboardId: string) => {
    try {
      return await datastore
        .selectFrom("dashboard_public_links")
        .select((eb) => [
          "id",
          "dashboard_id",
          "expires_at",
          "is_revoked",
          "view_count",
          "created_by",
          "created_at",
          // Booleanised at the DB so the hash itself never leaves Postgres.
          eb("password_hash", "is not", null).as("has_password"),
        ])
        .where("dashboard_id", "=", dashboardId)
        .orderBy("created_at", "desc")
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing public links" });
    }
  };

  // -------------------------------------------------------------------------
  // dashboard_view_log
  // -------------------------------------------------------------------------

  const InsertView = async (entry: {
    dashboardId: string;
    versionId: string | null;
    consoleUserId: string | null;
    publicLinkId: string | null;
    ipAddress: string | null;
  }) => {
    try {
      await datastore
        .insertInto("dashboard_view_log")
        .values({
          dashboard_id: entry.dashboardId,
          version_id: entry.versionId,
          console_user_id: entry.consoleUserId,
          public_link_id: entry.publicLinkId,
          ip_address: entry.ipAddress,
        })
        .execute();
    } catch (err) {
      // A view log failure must never break serving the dashboard.
      logError(err, "Failed recording dashboard view");
    }
  };

  // -------------------------------------------------------------------------
  // dashboard_audit_log
  // -------------------------------------------------------------------------

  const InsertAudit = async (entry: DashboardAuditEntry) => {
    try {
      await datastore
        .insertInto("dashboard_audit_log")
        .values({
          dashboard_id: entry.dashboardId,
          action: entry.action,
          actor_console_user_id: entry.actorId ?? null,
          actor_email: entry.actorEmail ?? null,
          metadata: toJson(entry.metadata ?? null),
        })
        .execute();
    } catch (err) {
      // Audit is important but must not roll back the mutation it describes.
      logError(err, `Failed writing dashboard audit row (${entry.action})`);
    }
  };

  const ListAuditForDashboard = async (dashboardId: string, limit = 200) => {
    try {
      return await datastore
        .selectFrom("dashboard_audit_log")
        .selectAll()
        .where("dashboard_id", "=", dashboardId)
        .orderBy("created_at", "desc")
        .limit(limit)
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({ error: err, message: "Failed listing dashboard audit log" });
    }
  };

  return {
    CreateDashboard,
    FindById,
    FindBySlug,
    ListDashboards,
    UpdateMeta,
    SetCurrentVersion,
    DeleteDashboard,
    GetMaxVersionNumber,
    InsertVersion,
    SetVersionEntryPoint,
    ListVersionsByDashboard,
    FindVersionById,
    HasAccessGrant,
    ListDashboardIdsForUser,
    ListUsersForDashboard,
    GrantAccess,
    RevokeAccess,
    ReplaceGrants,
    ListPublicLinksForDashboard,
    InsertPublicLink,
    FindPublicLinkByTokenHash,
    FindPublicLinkById,
    RevokePublicLink,
    IncrementPublicLinkViewCount,
    InsertView,
    InsertAudit,
    ListAuditForDashboard,
  };
};

export type TDashboardRepository = ReturnType<typeof DashboardRepository>;