import { desc, eq, lt, sql } from 'drizzle-orm';
import { bigint, integer, pgTable, text, timestamp } from 'drizzle-orm/pg-core';
import type { Querier, TransactionalDatabase } from '@platform-modules/db';

export class MediaValidationError extends Error {
  readonly name = 'MediaValidationError';
  constructor(readonly field: string, readonly detail: string) {
    super(`media validation failed: ${field} — ${detail}`);
  }
}

export class QuotaExceededError extends Error {
  readonly name = 'QuotaExceededError';
  constructor(message = 'media quota exceeded') {
    super(message);
  }
}

/** Idempotent DDL for media_assets — authoritative over media-schema.sql fixture. */
export const mediaTableSql = (): string =>
  `
CREATE TABLE IF NOT EXISTS media_assets (
  key text PRIMARY KEY,
  url text NOT NULL,
  content_type text NOT NULL,
  size bigint NOT NULL,
  width integer,
  height integer,
  uploader_id text NOT NULL,
  created_at timestamptz(3) NOT NULL DEFAULT now()
);
`.trim();

export const mediaAssets = pgTable('media_assets', {
  key: text('key').primaryKey(),
  url: text('url').notNull(),
  contentType: text('content_type').notNull(),
  size: bigint('size', { mode: 'number' }).notNull(),
  width: integer('width'),
  height: integer('height'),
  uploaderId: text('uploader_id').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
});

export const mediaSchema = { mediaAssets };
export type MediaSchema = typeof mediaSchema;

export interface MediaRow {
  key: string;
  url: string;
  contentType: string;
  size: number;
  width: number | null;
  height: number | null;
  uploaderId: string;
  createdAt: Date;
}

export interface NewMedia {
  key: string;
  url: string;
  contentType: string;
  size: number;
  width?: number;
  height?: number;
  uploaderId: string;
}

/** Sum of all stored bytes — the quota denominator, derived from rows (no drift). */
export async function usedBytes(db: Querier<MediaSchema>): Promise<number> {
  const [row] = await db
    .select({ total: sql<string>`coalesce(sum(${mediaAssets.size}), 0)` })
    .from(mediaAssets);
  return Number(row?.total ?? 0);
}

export async function insertMedia(db: Querier<MediaSchema>, m: NewMedia): Promise<MediaRow> {
  const [row] = await db
    .insert(mediaAssets)
    .values({
      key: m.key,
      url: m.url,
      contentType: m.contentType,
      size: m.size,
      width: m.width ?? null,
      height: m.height ?? null,
      uploaderId: m.uploaderId,
    })
    .returning();
  return row as MediaRow;
}

/**
 * Transactional quota reserve + insert. Locks media rows, re-sums usage, inserts if within quota.
 * Caller must run storage.put BEFORE this (object outside tx); on QuotaExceededError, delete the object.
 */
export async function reserveAndInsertMedia(
  db: TransactionalDatabase<MediaSchema>,
  m: NewMedia,
  quota: number,
): Promise<MediaRow> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SELECT key FROM media_assets FOR UPDATE`);
    const [sumRow] = await tx
      .select({ total: sql<string>`coalesce(sum(${mediaAssets.size}), 0)` })
      .from(mediaAssets);
    const used = Number(sumRow?.total ?? 0);
    if (used + m.size > quota) {
      throw new QuotaExceededError();
    }
    const [row] = await tx
      .insert(mediaAssets)
      .values({
        key: m.key,
        url: m.url,
        contentType: m.contentType,
        size: m.size,
        width: m.width ?? null,
        height: m.height ?? null,
        uploaderId: m.uploaderId,
      })
      .returning();
    return row as MediaRow;
  });
}

/**
 * Keyset page newest-first by `createdAt`. `cursor` is the last seen `createdAt` as an ISO string.
 * Tie-break is `createdAt` alone (no composite key) — rows sharing an identical millisecond at a page
 * boundary may be skipped; acceptable for the single-admin, low-volume mod-cms surface (precision: 3 ms).
 */
export async function listMedia(
  db: Querier<MediaSchema>,
  opts?: { limit?: number; cursor?: string },
): Promise<{ rows: MediaRow[]; cursor?: string }> {
  const limit = Math.min(Math.max(opts?.limit ?? 24, 1), 100);
  const where = opts?.cursor ? lt(mediaAssets.createdAt, new Date(opts.cursor)) : undefined;
  const rows = (await db
    .select()
    .from(mediaAssets)
    .where(where)
    .orderBy(desc(mediaAssets.createdAt))
    .limit(limit + 1)) as MediaRow[];
  const hasMore = rows.length > limit;
  const page = hasMore ? rows.slice(0, limit) : rows;
  const next = hasMore ? page[page.length - 1]!.createdAt.toISOString() : undefined;
  return { rows: page, cursor: next };
}

export async function getMedia(db: Querier<MediaSchema>, key: string): Promise<MediaRow | null> {
  const [row] = await db.select().from(mediaAssets).where(eq(mediaAssets.key, key)).limit(1);
  return (row as MediaRow) ?? null;
}

/**
 * Delete a media row by key. Returns the deleted row, or null if absent (idempotent).
 * Media is a shared editorial library — tier gate only (editors upload/browse; admins delete).
 * No per-row uploaderId ACL: `uploader_id` is attribution/audit, never gated on.
 */
export async function deleteMedia(db: Querier<MediaSchema>, key: string): Promise<MediaRow | null> {
  const [row] = await db.delete(mediaAssets).where(eq(mediaAssets.key, key)).returning();
  return (row as MediaRow) ?? null;
}