/**
 * The only SQL in the media module — public gallery reads (PLANNING.md §6d) plus
 * the admin CRUD / reorder / visibility writes (§4b). Rows in, rows out; the
 * service turns them into DTOs. Writes that touch more than one table take an
 * optional executor so the caller can run them inside a transaction.
 */
import type { Knex } from 'knex';
import { db } from '../../lib/db';
import { newId } from '../../lib/ulid';

type Executor = Knex | Knex.Transaction;

export type Page = 'genesis' | 'hive';
export type ImageStatus = 'ready' | 'processing' | 'failed';

interface MediaImagesRow {
  id: string;
  page: Page;
  category_id: number | null;
  alt: string;
  caption: string | null;
  position: number;
  is_visible: number;
  width: number;
  height: number;
  content_hash: string;
  base_path: string;
  original_name: string;
  original_bytes: number;
  status: ImageStatus;
  created_by: string | null;
  created_at: string;
  updated_at: string;
  deleted_at: string | null;
}

interface MediaVariantsRow {
  id: number;
  image_id: string;
  format: 'avif' | 'webp' | 'jpeg';
  width: number;
  height: number;
  bytes: number;
  path: string;
  is_primary: number;
}

interface MediaCategoryRow {
  id: number;
  slug: string;
  label: string;
  position: number;
}

const PUBLIC_IMAGE_COLUMNS = [
  'id',
  'page',
  'category_id',
  'alt',
  'caption',
  'position',
  'width',
  'height',
  'updated_at',
] as const;

const ADMIN_IMAGE_COLUMNS = [
  ...PUBLIC_IMAGE_COLUMNS,
  'is_visible',
  'status',
  'content_hash',
  'base_path',
  'created_at',
] as const;

export type MediaImageRow = Pick<MediaImagesRow, (typeof PUBLIC_IMAGE_COLUMNS)[number]>;
export type AdminMediaImageRow = Pick<MediaImagesRow, (typeof ADMIN_IMAGE_COLUMNS)[number]>;
export type MediaVariantRow = Pick<
  MediaVariantsRow,
  'image_id' | 'format' | 'width' | 'height' | 'path' | 'is_primary'
>;

// ─── public gallery reads (§6d) ──────────────────────────────────────────

export function listReadyImages(page: Page, categorySlug?: string): Promise<MediaImageRow[]> {
  const q = db<MediaImagesRow>('media_images')
    .select(...PUBLIC_IMAGE_COLUMNS)
    .where({ page, is_visible: 1, status: 'ready' })
    .whereNull('deleted_at')
    .orderBy('position', 'asc');

  if (categorySlug) {
    q.whereIn('category_id', db('media_categories').select('id').where({ slug: categorySlug }));
  }
  return q;
}

export function listVariants(imageIds: string[]): Promise<MediaVariantRow[]> {
  if (imageIds.length === 0) return Promise.resolve([]);
  return db<MediaVariantsRow>('media_variants')
    .select('image_id', 'format', 'width', 'height', 'path', 'is_primary')
    .whereIn('image_id', imageIds)
    .orderBy('width', 'asc');
}

// ─── categories ─────────────────────────────────────────────────────────

export function listCategories(): Promise<MediaCategoryRow[]> {
  return db<MediaCategoryRow>('media_categories')
    .select('id', 'slug', 'label', 'position')
    .orderBy('position', 'asc');
}

export function findCategoryBySlug(
  slug: string,
): Promise<Pick<MediaCategoryRow, 'id'> | undefined> {
  return db<MediaCategoryRow>('media_categories').select('id').where({ slug }).first();
}

// ─── admin reads ────────────────────────────────────────────────────────

export function listAdminImages(filter: {
  page: Page;
  categoryId?: number;
  visible?: boolean;
}): Promise<AdminMediaImageRow[]> {
  const q = db<MediaImagesRow>('media_images')
    .select(...ADMIN_IMAGE_COLUMNS)
    .where({ page: filter.page })
    .whereNull('deleted_at')
    .orderBy('position', 'asc');

  if (filter.categoryId !== undefined) q.where({ category_id: filter.categoryId });
  if (filter.visible !== undefined) q.where({ is_visible: filter.visible ? 1 : 0 });
  return q;
}

export function findAdminImageById(id: string): Promise<AdminMediaImageRow | undefined> {
  return db<MediaImagesRow>('media_images')
    .select(...ADMIN_IMAGE_COLUMNS)
    .where({ id })
    .whereNull('deleted_at')
    .first();
}

export async function nextPosition(page: Page, exec: Executor = db): Promise<number> {
  const row = (await exec('media_images')
    .where({ page })
    .whereNull('deleted_at')
    .max({ maxPos: 'position' })
    .first()) as { maxPos: number | null } | undefined;
  return (row?.maxPos ?? -1) + 1;
}

/** Non-deleted image ids for a page, for reorder-set validation (§6c). */
export async function pageImageIds(page: Page, exec: Executor = db): Promise<string[]> {
  const rows = await exec<MediaImagesRow>('media_images')
    .select('id')
    .where({ page })
    .whereNull('deleted_at');
  return rows.map((r) => r.id);
}

// ─── admin writes ───────────────────────────────────────────────────────

export interface NewImageRow {
  page: Page;
  category_id: number | null;
  alt: string;
  caption: string | null;
  position: number;
  width: number;
  height: number;
  content_hash: string;
  base_path: string;
  original_name: string;
  original_bytes: number;
  status: ImageStatus;
  created_by: string | null;
}

export async function insertImage(row: NewImageRow, exec: Executor = db): Promise<string> {
  const id = newId();
  await exec('media_images').insert({ id, ...row });
  return id;
}

export interface NewVariantRow {
  image_id: string;
  format: 'avif' | 'webp' | 'jpeg';
  width: number;
  height: number;
  bytes: number;
  path: string;
  is_primary: number;
}

export async function insertVariants(rows: NewVariantRow[], exec: Executor = db): Promise<void> {
  if (rows.length > 0) await exec('media_variants').insert(rows);
}

export async function replaceVariants(
  imageId: string,
  rows: NewVariantRow[],
  exec: Executor = db,
): Promise<void> {
  await exec('media_variants').where({ image_id: imageId }).del();
  await insertVariants(rows, exec);
}

export async function setImageStatus(
  id: string,
  status: ImageStatus,
  exec: Executor = db,
): Promise<void> {
  await exec('media_images').where({ id }).update({ status });
}

export async function updateImageFields(
  id: string,
  patch: Partial<{ alt: string; caption: string | null; category_id: number | null }>,
): Promise<void> {
  await db('media_images').where({ id }).update(patch);
}

export async function setVisibility(id: string, isVisible: boolean): Promise<void> {
  await db('media_images')
    .where({ id })
    .update({ is_visible: isVisible ? 1 : 0 });
}

export async function softDeleteImage(id: string): Promise<void> {
  await db('media_images').where({ id }).update({ deleted_at: db.fn.now() });
}

export async function setPositions(orderedIds: string[], exec: Executor = db): Promise<void> {
  // One UPDATE per row inside the caller's transaction; the lists here are tens
  // of images, not thousands.
  for (let i = 0; i < orderedIds.length; i += 1) {
    await exec('media_images').where({ id: orderedIds[i] }).update({ position: i });
  }
}

/** The knex instance — exposed for services that need `db.transaction(...)`. */
export { db };