import { createHash, createHmac, randomUUID, timingSafeEqual } from 'node:crypto';
import {
  lstat,
  mkdir,
  open,
  readFile,
  rename,
  rm,
  stat,
  unlink,
} from 'node:fs/promises';
import path from 'node:path';

import type { Pool, PoolClient } from 'pg';

import { db } from '../db.js';

type JsonObject = Record<string, unknown>;

export type StorageStatusCode =
  | 400
  | 401
  | 403
  | 404
  | 409
  | 413
  | 415
  | 416
  | 500
  | 503;

export class StorageError extends Error {
  readonly code: string;
  readonly statusCode: StorageStatusCode;

  constructor(message: string, code: string, statusCode: StorageStatusCode) {
    super(message);
    this.name = 'StorageError';
    this.code = code;
    this.statusCode = statusCode;
  }
}

export type StorageResult<T> =
  | { data: T; error: null }
  | { data: null; error: StorageError };

export interface StorageBucket {
  id: string;
  name: string;
  owner: string | null;
  owner_id: string | null;
  public: boolean;
  file_size_limit: number | null;
  allowed_mime_types: string[] | null;
  created_at: string;
  updated_at: string;
}

export interface BucketOptions {
  public?: boolean;
  fileSizeLimit?: number | null;
  allowedMimeTypes?: string[] | null;
}

export interface UploadOptions {
  cacheControl?: string;
  contentType?: string;
  upsert?: boolean;
  metadata?: JsonObject;
}

export interface SignedUrlOptions {
  download?: boolean | string;
}

export interface UploadedObject {
  path: string;
  fullPath: string;
}

export interface RemovedObject {
  id: string;
  bucket_id: string;
  name: string;
}

export interface StoredObject {
  id: string;
  bucketId: string;
  name: string;
  bytes: Buffer;
  size: number;
  contentType: string;
  etag: string | null;
  checksum: string | null;
  cacheControl: string | null;
  createdAt: string;
  updatedAt: string;
  isPublic: boolean;
}

export interface SignedAccess {
  download: boolean | string;
  expiresAt: number;
}

export interface LocalStorageOptions {
  root?: string;
  publicBaseUrl?: string;
  routePrefix?: string;
  signingSecret?: string;
}

interface BucketRow {
  id: string;
  name: string;
  owner: string | null;
  owner_id: string | null;
  public: boolean;
  file_size_limit: string | number | null;
  allowed_mime_types: string[] | null;
  created_at: Date | string;
  updated_at: Date | string;
}

interface ObjectRow {
  id: string;
  bucket_id: string;
  name: string;
  metadata: JsonObject | null;
  user_metadata: JsonObject | null;
  local_path: string | null;
  size_bytes: string | number | null;
  content_type: string | null;
  etag: string | null;
  content_checksum: string | null;
  created_at: Date | string;
  updated_at: Date | string;
  public?: boolean;
}

interface SignedPayload {
  bucket: string;
  path: string;
  exp: number;
  download: boolean | string;
}

interface MovedFile {
  originalPath: string;
  backupPath: string;
}

const DEFAULT_CONTENT_TYPE = 'application/octet-stream';
const MAX_PATH_LENGTH = 4_096;
const MAX_SIGNED_URL_SECONDS = 7 * 24 * 60 * 60;

function asIsoString(value: Date | string): string {
  return value instanceof Date ? value.toISOString() : new Date(value).toISOString();
}

function safeInteger(value: string | number | null, label: string): number | null {
  if (value === null) {
    return null;
  }

  const parsed = typeof value === 'number' ? value : Number(value);
  if (!Number.isSafeInteger(parsed) || parsed < 0) {
    throw new StorageError(`Invalid ${label} in storage metadata.`, 'invalid_metadata', 500);
  }
  return parsed;
}

function errorCode(error: unknown): string | undefined {
  if (!error || typeof error !== 'object') {
    return undefined;
  }
  return (error as { code?: string }).code;
}

function asStorageError(error: unknown): StorageError {
  if (error instanceof StorageError) {
    return error;
  }

  if (errorCode(error) === '23505') {
    return new StorageError('The storage resource already exists.', 'already_exists', 409);
  }
  if (errorCode(error) === 'ENOENT') {
    return new StorageError('The stored file was not found on disk.', 'file_not_found', 404);
  }

  return new StorageError(
    error instanceof Error ? error.message : 'Unexpected local storage failure.',
    'storage_error',
    500,
  );
}

async function resultOf<T>(operation: () => Promise<T>): Promise<StorageResult<T>> {
  try {
    return { data: await operation(), error: null };
  } catch (error) {
    return { data: null, error: asStorageError(error) };
  }
}

function validateSegment(segment: string, label: string): void {
  if (
    !segment ||
    segment === '.' ||
    segment === '..' ||
    segment.includes('\\') ||
    /[<>:"|?*\u0000-\u001f]/.test(segment) ||
    /[. ]$/.test(segment) ||
    /^(con|prn|aux|nul|com[1-9]|lpt[1-9])(?:\..*)?$/i.test(segment)
  ) {
    throw new StorageError(`${label} contains an unsafe path segment.`, 'invalid_path', 400);
  }
}

function pathSegments(value: string, label: string): string[] {
  if (
    typeof value !== 'string' ||
    value.length === 0 ||
    value.length > MAX_PATH_LENGTH ||
    value.includes('\0') ||
    path.isAbsolute(value) ||
    path.win32.isAbsolute(value)
  ) {
    throw new StorageError(`${label} is not a safe relative path.`, 'invalid_path', 400);
  }

  const segments = value.split('/');
  for (const segment of segments) {
    validateSegment(segment, label);
  }
  return segments;
}

function bucketSegment(value: string): string {
  const segments = pathSegments(value, 'Bucket name');
  if (segments.length !== 1 || value.length > 255) {
    throw new StorageError('Bucket name must be one safe path segment.', 'invalid_bucket', 400);
  }
  return segments[0]!;
}

function normalizedMimeTypes(value: string[] | null | undefined): string[] | null | undefined {
  if (value === undefined || value === null) {
    return value;
  }
  if (!Array.isArray(value) || value.length > 100) {
    throw new StorageError('allowedMimeTypes must be an array.', 'invalid_bucket_options', 400);
  }

  const result = [...new Set(value.map((item) => item.trim().toLowerCase()))];
  if (result.some((item) => !/^[\w.+-]+\/(?:[\w.+-]+|\*)$/.test(item))) {
    throw new StorageError('allowedMimeTypes contains an invalid MIME type.', 'invalid_bucket_options', 400);
  }
  return result;
}

function normalizedFileLimit(value: number | null | undefined): number | null | undefined {
  if (value === undefined || value === null) {
    return value;
  }
  if (!Number.isSafeInteger(value) || value < 0) {
    throw new StorageError('fileSizeLimit must be a non-negative integer.', 'invalid_bucket_options', 400);
  }
  return value;
}

function normalizeContentType(value: string | undefined): string {
  const contentType = value?.trim().toLowerCase() || DEFAULT_CONTENT_TYPE;
  if (contentType.length > 255 || /[\r\n]/.test(contentType)) {
    throw new StorageError('Invalid upload content type.', 'invalid_content_type', 400);
  }
  return contentType;
}

function normalizeCacheControl(value: string | undefined): string {
  const cacheControl = value?.trim() || '3600';
  if (!/^\d{1,10}$/.test(cacheControl)) {
    throw new StorageError('cacheControl must contain seconds.', 'invalid_cache_control', 400);
  }
  return cacheControl;
}

function mimeTypeAllowed(contentType: string, allowed: string[] | null): boolean {
  if (!allowed || allowed.length === 0) {
    return true;
  }
  const normalized = contentType.toLowerCase();
  return allowed.some((candidate) => {
    const value = candidate.toLowerCase();
    return value === normalized || (value.endsWith('/*') && normalized.startsWith(value.slice(0, -1)));
  });
}

async function uploadBuffer(value: unknown): Promise<Buffer> {
  if (typeof value === 'string') {
    return Buffer.from(value);
  }
  if (value instanceof Blob) {
    return Buffer.from(await value.arrayBuffer());
  }
  if (value instanceof ArrayBuffer) {
    return Buffer.from(value);
  }
  if (ArrayBuffer.isView(value)) {
    return Buffer.from(new Uint8Array(value.buffer, value.byteOffset, value.byteLength));
  }
  throw new StorageError('Unsupported upload body.', 'invalid_upload_body', 400);
}

function encodeStoragePath(value: string): string {
  return pathSegments(value, 'Object path').map(encodeURIComponent).join('/');
}

function mapBucket(row: BucketRow): StorageBucket {
  return {
    id: row.id,
    name: row.name,
    owner: row.owner,
    owner_id: row.owner_id,
    public: row.public,
    file_size_limit: safeInteger(row.file_size_limit, 'bucket file size limit'),
    allowed_mime_types: row.allowed_mime_types,
    created_at: asIsoString(row.created_at),
    updated_at: asIsoString(row.updated_at),
  };
}

function metadataString(metadata: JsonObject | null, ...keys: string[]): string | null {
  for (const key of keys) {
    const value = metadata?.[key];
    if (typeof value === 'string' && value.length > 0) {
      return value;
    }
  }
  return null;
}

function metadataSize(metadata: JsonObject | null): number | null {
  const value = metadata?.size ?? metadata?.contentLength;
  if (value === undefined || value === null) {
    return null;
  }
  const parsed = typeof value === 'number' ? value : Number(value);
  return Number.isSafeInteger(parsed) && parsed >= 0 ? parsed : null;
}

function normalizeRoutePrefix(value: string): string {
  const trimmed = value.trim().replace(/\/+$/, '');
  if (!trimmed.startsWith('/') || trimmed.includes('?') || trimmed.includes('#')) {
    throw new StorageError('Invalid storage route prefix.', 'invalid_storage_configuration', 500);
  }
  return trimmed;
}

function defaultPublicBaseUrl(): string {
  const explicit = process.env.BACKEND_PUBLIC_URL?.trim();
  if (explicit) {
    return explicit.replace(/\/+$/, '');
  }
  const configuredHost = process.env.BACKEND_HOST?.trim() || '127.0.0.1';
  const host = configuredHost === '0.0.0.0' || configuredHost === '::' ? '127.0.0.1' : configuredHost;
  const port = process.env.BACKEND_PORT?.trim() || '3001';
  return `http://${host}:${port}`;
}

export class LocalStorageService {
  readonly storageRoot: string;
  readonly publicBaseUrl: string;
  readonly routePrefix: string;

  private readonly signingSecret: string | null;

  constructor(
    private readonly pool: Pool = db,
    options: LocalStorageOptions = {},
  ) {
    const configuredRoot = options.root ?? process.env.STORAGE_ROOT?.trim() ?? './data/storage';
    this.storageRoot = path.resolve(configuredRoot);
    this.publicBaseUrl = (options.publicBaseUrl ?? defaultPublicBaseUrl()).replace(/\/+$/, '');
    this.routePrefix = normalizeRoutePrefix(options.routePrefix ?? '/storage/v1');
    this.signingSecret =
      options.signingSecret ??
      process.env.STORAGE_SIGNING_SECRET?.trim() ??
      process.env.AUTH_ACCESS_TOKEN_SECRET?.trim() ??
      null;
  }

  from(bucketId: string): LocalStorageBucketClient {
    return new LocalStorageBucketClient(this, bucketId);
  }

  listBuckets(): Promise<StorageResult<StorageBucket[]>> {
    return resultOf(async () => {
      const response = await this.pool.query<BucketRow>(
        `SELECT id, name, owner, owner_id, public, file_size_limit,
                allowed_mime_types, created_at, updated_at
         FROM local_storage.buckets
         ORDER BY name`,
      );
      return response.rows.map(mapBucket);
    });
  }

  createBucket(bucketId: string, options: BucketOptions = {}): Promise<StorageResult<{ name: string }>> {
    return resultOf(async () => {
      const id = bucketSegment(bucketId);
      const fileSizeLimit = normalizedFileLimit(options.fileSizeLimit);
      const allowedMimeTypes = normalizedMimeTypes(options.allowedMimeTypes);
      const client = await this.pool.connect();
      let directoryCreated = false;

      try {
        await client.query('BEGIN');
        const collision = await client.query<{ id: string }>(
          `SELECT id FROM local_storage.buckets WHERE lower(id) = lower($1) FOR UPDATE`,
          [id],
        );
        if (collision.rows[0]) {
          throw new StorageError('The bucket already exists.', 'bucket_exists', 409);
        }

        await this.ensureRoot();
        const bucketPath = this.resolveRelative(id);
        try {
          await lstat(bucketPath);
        } catch (error) {
          if (errorCode(error) !== 'ENOENT') {
            throw error;
          }
          await mkdir(bucketPath, { mode: 0o700 });
          directoryCreated = true;
        }
        await this.assertSafeFileSystemPath(bucketPath, true);

        await client.query(
          `INSERT INTO local_storage.buckets
             (id, name, public, file_size_limit, allowed_mime_types, source_record)
           VALUES ($1, $1, $2, $3, $4, $5::jsonb)`,
          [
            id,
            options.public ?? false,
            fileSizeLimit ?? null,
            allowedMimeTypes ?? null,
            JSON.stringify({ provider: 'local' }),
          ],
        );
        await client.query('COMMIT');
        return { name: id };
      } catch (error) {
        await client.query('ROLLBACK').catch(() => undefined);
        if (directoryCreated) {
          await rm(this.resolveRelative(id), { recursive: false, force: true }).catch(() => undefined);
        }
        throw error;
      } finally {
        client.release();
      }
    });
  }

  updateBucket(bucketId: string, options: BucketOptions): Promise<StorageResult<{ message: string }>> {
    return resultOf(async () => {
      const id = bucketSegment(bucketId);
      const fileSizeLimit = normalizedFileLimit(options.fileSizeLimit);
      const allowedMimeTypes = normalizedMimeTypes(options.allowedMimeTypes);
      const response = await this.pool.query(
        `UPDATE local_storage.buckets
         SET public = COALESCE($2, public),
             file_size_limit = CASE WHEN $3 THEN $4 ELSE file_size_limit END,
             allowed_mime_types = CASE WHEN $5 THEN $6 ELSE allowed_mime_types END
         WHERE id = $1`,
        [
          id,
          options.public ?? null,
          options.fileSizeLimit !== undefined,
          fileSizeLimit ?? null,
          options.allowedMimeTypes !== undefined,
          allowedMimeTypes ?? null,
        ],
      );
      if (response.rowCount === 0) {
        throw new StorageError('Bucket not found.', 'bucket_not_found', 404);
      }
      return { message: 'Successfully updated' };
    });
  }

  async bucketIsPublic(bucketId: string): Promise<boolean> {
    const id = bucketSegment(bucketId);
    const response = await this.pool.query<{ public: boolean }>(
      `SELECT public FROM local_storage.buckets WHERE id = $1`,
      [id],
    );
    if (!response.rows[0]) {
      throw new StorageError('Bucket not found.', 'bucket_not_found', 404);
    }
    return response.rows[0].public;
  }

  publicUrl(bucketId: string, objectPath: string): string {
    const bucket = encodeURIComponent(bucketSegment(bucketId));
    const object = encodeStoragePath(objectPath);
    return `${this.publicBaseUrl}${this.routePrefix}/object/public/${bucket}/${object}`;
  }

  async signedUrl(
    bucketId: string,
    objectPath: string,
    expiresIn: number,
    options: SignedUrlOptions = {},
  ): Promise<{ signedUrl: string }> {
    const bucket = bucketSegment(bucketId);
    pathSegments(objectPath, 'Object path');
    if (!Number.isSafeInteger(expiresIn) || expiresIn < 1 || expiresIn > MAX_SIGNED_URL_SECONDS) {
      throw new StorageError(
        `Signed URL lifetime must be between 1 and ${MAX_SIGNED_URL_SECONDS} seconds.`,
        'invalid_expiry',
        400,
      );
    }
    if (!this.signingSecret || this.signingSecret.length < 32) {
      throw new StorageError(
        'Storage signing secret is not configured.',
        'storage_signing_unavailable',
        503,
      );
    }
    if (
      typeof options.download === 'string' &&
      (!options.download.trim() || options.download.length > 255 || /[\r\n]/.test(options.download))
    ) {
      throw new StorageError('Invalid download filename.', 'invalid_download_name', 400);
    }

    await this.objectRow(bucket, objectPath);
    const payload: SignedPayload = {
      bucket,
      path: objectPath,
      exp: Math.floor(Date.now() / 1_000) + expiresIn,
      download: options.download ?? false,
    };
    const encodedPayload = Buffer.from(JSON.stringify(payload)).toString('base64url');
    const signature = createHmac('sha256', this.signingSecret)
      .update(encodedPayload)
      .digest('base64url');
    const token = `${encodedPayload}.${signature}`;
    const encodedBucket = encodeURIComponent(bucket);
    const encodedObject = encodeStoragePath(objectPath);
    return {
      signedUrl:
        `${this.publicBaseUrl}${this.routePrefix}/object/sign/` +
        `${encodedBucket}/${encodedObject}?token=${encodeURIComponent(token)}`,
    };
  }

  verifySignedAccess(token: string, bucketId: string, objectPath: string): SignedAccess {
    const bucket = bucketSegment(bucketId);
    pathSegments(objectPath, 'Object path');
    if (!this.signingSecret || this.signingSecret.length < 32) {
      throw new StorageError('Storage signing is unavailable.', 'storage_signing_unavailable', 503);
    }
    if (!token || token.length > 8_192) {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }

    const [encodedPayload, encodedSignature, extra] = token.split('.');
    if (!encodedPayload || !encodedSignature || extra !== undefined) {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }
    const expected = createHmac('sha256', this.signingSecret)
      .update(encodedPayload)
      .digest();
    let supplied: Buffer;
    try {
      supplied = Buffer.from(encodedSignature, 'base64url');
    } catch {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }
    if (supplied.length !== expected.length || !timingSafeEqual(supplied, expected)) {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }

    let rawPayload: unknown;
    try {
      rawPayload = JSON.parse(Buffer.from(encodedPayload, 'base64url').toString('utf8'));
    } catch {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }
    if (!rawPayload || typeof rawPayload !== 'object' || Array.isArray(rawPayload)) {
      throw new StorageError('Invalid signed URL.', 'invalid_signature', 403);
    }
    const payload = rawPayload as Partial<SignedPayload>;
    if (
      payload.bucket !== bucket ||
      payload.path !== objectPath ||
      !Number.isSafeInteger(payload.exp) ||
      typeof payload.exp !== 'number' ||
      payload.exp < Math.floor(Date.now() / 1_000) ||
      (typeof payload.download !== 'boolean' && typeof payload.download !== 'string')
    ) {
      throw new StorageError('Signed URL is invalid or expired.', 'invalid_signature', 403);
    }
    return { download: payload.download, expiresAt: payload.exp };
  }

  upload(
    bucketId: string,
    objectPath: string,
    body: unknown,
    options: UploadOptions = {},
  ): Promise<StorageResult<UploadedObject>> {
    return resultOf(() => this.uploadOrThrow(bucketId, objectPath, body, options));
  }

  download(bucketId: string, objectPath: string): Promise<StorageResult<Blob>> {
    return resultOf(async () => {
      const object = await this.readObject(bucketId, objectPath);
      return new Blob([object.bytes], { type: object.contentType });
    });
  }

  remove(bucketId: string, objectPaths: string[]): Promise<StorageResult<RemovedObject[]>> {
    return resultOf(() => this.removeOrThrow(bucketId, objectPaths));
  }

  async readObject(bucketId: string, objectPath: string): Promise<StoredObject> {
    const row = await this.objectRow(bucketId, objectPath);
    const relativePath = this.rowLocalPath(row);
    const diskPath = this.resolveRelative(relativePath);
    await this.assertSafeFileSystemPath(diskPath, false);
    const details = await stat(diskPath);
    if (!details.isFile()) {
      throw new StorageError('Stored object is not a regular file.', 'unsafe_storage_path', 500);
    }
    if (!Number.isSafeInteger(details.size)) {
      throw new StorageError('Stored object is too large for this runtime.', 'invalid_metadata', 500);
    }

    const expectedSize = safeInteger(row.size_bytes, 'object size') ?? metadataSize(row.metadata);
    if (expectedSize !== null && details.size !== expectedSize) {
      throw new StorageError('Stored object size does not match its metadata.', 'integrity_error', 500);
    }
    const bytes = await readFile(diskPath);
    if (row.content_checksum) {
      const checksum = createHash('sha256').update(bytes).digest('hex');
      if (checksum !== row.content_checksum.toLowerCase()) {
        throw new StorageError('Stored object checksum does not match.', 'integrity_error', 500);
      }
    }

    await this.pool.query(
      `UPDATE local_storage.objects SET last_accessed_at = transaction_timestamp() WHERE id = $1`,
      [row.id],
    );
    return {
      id: row.id,
      bucketId: row.bucket_id,
      name: row.name,
      bytes,
      size: bytes.byteLength,
      contentType:
        row.content_type ??
        metadataString(row.metadata, 'mimetype', 'contentType') ??
        DEFAULT_CONTENT_TYPE,
      etag: row.etag ?? metadataString(row.metadata, 'eTag', 'etag'),
      checksum: row.content_checksum,
      cacheControl: metadataString(row.metadata, 'cacheControl'),
      createdAt: asIsoString(row.created_at),
      updatedAt: asIsoString(row.updated_at),
      isPublic: row.public ?? false,
    };
  }

  private async uploadOrThrow(
    bucketId: string,
    objectPath: string,
    body: unknown,
    options: UploadOptions,
  ): Promise<UploadedObject> {
    const bucket = bucketSegment(bucketId);
    const segments = pathSegments(objectPath, 'Object path');
    const bytes = await uploadBuffer(body);
    const contentType = normalizeContentType(options.contentType);
    const cacheControl = normalizeCacheControl(options.cacheControl);
    const checksum = createHash('sha256').update(bytes).digest('hex');
    const client = await this.pool.connect();
    let temporaryPath: string | null = null;
    let targetPath: string | null = null;
    let backupPath: string | null = null;
    let replacementPlaced = false;

    try {
      await client.query('BEGIN');
      const bucketResponse = await client.query<BucketRow>(
        `SELECT id, name, owner, owner_id, public, file_size_limit,
                allowed_mime_types, created_at, updated_at
         FROM local_storage.buckets WHERE id = $1 FOR UPDATE`,
        [bucket],
      );
      const bucketRow = bucketResponse.rows[0];
      if (!bucketRow) {
        throw new StorageError('Bucket not found.', 'bucket_not_found', 404);
      }
      const limit = safeInteger(bucketRow.file_size_limit, 'bucket file size limit');
      if (limit !== null && bytes.byteLength > limit) {
        throw new StorageError('The object exceeds the bucket file size limit.', 'file_too_large', 413);
      }
      if (!mimeTypeAllowed(contentType, bucketRow.allowed_mime_types)) {
        throw new StorageError('The object MIME type is not allowed in this bucket.', 'mime_type_not_allowed', 415);
      }

      const existingResponse = await client.query<ObjectRow>(
        `SELECT * FROM local_storage.objects
         WHERE bucket_id = $1 AND name = $2 FOR UPDATE`,
        [bucket, objectPath],
      );
      const existing = existingResponse.rows[0];
      if (existing && !options.upsert) {
        throw new StorageError('The object already exists.', 'object_exists', 409);
      }
      const caseCollision = await client.query<{ id: string; name: string }>(
        `SELECT id, name FROM local_storage.objects
         WHERE bucket_id = $1 AND lower(name) = lower($2)
           AND ($3::uuid IS NULL OR id <> $3::uuid)
         LIMIT 1 FOR UPDATE`,
        [bucket, objectPath, existing?.id ?? null],
      );
      if (caseCollision.rows[0]) {
        throw new StorageError('An object with a colliding path already exists.', 'object_exists', 409);
      }

      const relativePath = existing ? this.rowLocalPath(existing) : `${bucket}/${objectPath}`;
      targetPath = this.resolveRelative(relativePath);
      await this.ensureSafeParent(targetPath);
      const diskExists = await this.regularFileExists(targetPath);
      if (diskExists && !existing) {
        throw new StorageError('A file already occupies the object path.', 'object_exists', 409);
      }

      temporaryPath = path.join(path.dirname(targetPath), `.upload-${randomUUID()}.tmp`);
      const handle = await open(temporaryPath, 'wx', 0o600);
      try {
        await handle.writeFile(bytes);
        await handle.sync();
      } finally {
        await handle.close();
      }
      await this.assertSafeFileSystemPath(temporaryPath, false);
      await this.assertSafeFileSystemPath(targetPath, true);

      if (diskExists) {
        backupPath = path.join(path.dirname(targetPath), `.replace-${randomUUID()}.bak`);
        await rename(targetPath, backupPath);
      }
      await rename(temporaryPath, targetPath);
      temporaryPath = null;
      replacementPlaced = true;

      const metadata = {
        ...(existing?.metadata ?? {}),
        ...(options.metadata ?? {}),
        cacheControl,
        mimetype: contentType,
        size: bytes.byteLength,
        contentLength: bytes.byteLength,
        eTag: checksum,
        lastModified: new Date().toISOString(),
      };
      const version = randomUUID();
      if (existing) {
        await client.query(
          `UPDATE local_storage.objects
           SET metadata = $3::jsonb,
               user_metadata = $4::jsonb,
               path_tokens = $5,
               version = $6,
               local_path = $7,
               size_bytes = $8,
               content_type = $9,
               etag = $10,
               content_checksum = $10
           WHERE id = $1 AND bucket_id = $2`,
          [
            existing.id,
            bucket,
            JSON.stringify(metadata),
            JSON.stringify(options.metadata ?? existing.user_metadata ?? {}),
            segments,
            version,
            relativePath,
            bytes.byteLength,
            contentType,
            checksum,
          ],
        );
      } else {
        await client.query(
          `INSERT INTO local_storage.objects
             (bucket_id, name, metadata, user_metadata, path_tokens, version,
              local_path, size_bytes, content_type, etag, content_checksum, source_record)
           VALUES ($1, $2, $3::jsonb, $4::jsonb, $5, $6, $7, $8, $9, $10, $10, $11::jsonb)`,
          [
            bucket,
            objectPath,
            JSON.stringify(metadata),
            JSON.stringify(options.metadata ?? {}),
            segments,
            version,
            relativePath,
            bytes.byteLength,
            contentType,
            checksum,
            JSON.stringify({ provider: 'local' }),
          ],
        );
      }
      await client.query('COMMIT');
      if (backupPath) {
        await unlink(backupPath).catch(() => undefined);
      }
      return { path: objectPath, fullPath: `${bucket}/${objectPath}` };
    } catch (error) {
      await client.query('ROLLBACK').catch(() => undefined);
      if (replacementPlaced && targetPath) {
        await unlink(targetPath).catch(() => undefined);
      }
      if (backupPath && targetPath) {
        await rename(backupPath, targetPath).catch(() => undefined);
      }
      if (temporaryPath) {
        await unlink(temporaryPath).catch(() => undefined);
      }
      throw error;
    } finally {
      client.release();
    }
  }

  private async removeOrThrow(bucketId: string, objectPaths: string[]): Promise<RemovedObject[]> {
    const bucket = bucketSegment(bucketId);
    if (!Array.isArray(objectPaths) || objectPaths.length > 1_000) {
      throw new StorageError('Object paths must be an array.', 'invalid_path_list', 400);
    }
    const paths = [...new Set(objectPaths)];
    for (const objectPath of paths) {
      pathSegments(objectPath, 'Object path');
    }
    if (paths.length === 0) {
      return [];
    }

    const client = await this.pool.connect();
    const moved: MovedFile[] = [];
    const rows: ObjectRow[] = [];
    try {
      await client.query('BEGIN');
      const bucketResponse = await client.query<{ id: string }>(
        `SELECT id FROM local_storage.buckets WHERE id = $1 FOR UPDATE`,
        [bucket],
      );
      if (!bucketResponse.rows[0]) {
        throw new StorageError('Bucket not found.', 'bucket_not_found', 404);
      }

      for (const objectPath of paths) {
        const response = await client.query<ObjectRow>(
          `SELECT * FROM local_storage.objects
           WHERE bucket_id = $1 AND name = $2 FOR UPDATE`,
          [bucket, objectPath],
        );
        const row = response.rows[0];
        if (!row) {
          continue;
        }
        rows.push(row);
        const diskPath = this.resolveRelative(this.rowLocalPath(row));
        if (await this.regularFileExists(diskPath)) {
          await this.assertSafeFileSystemPath(diskPath, false);
          const backupPath = path.join(path.dirname(diskPath), `.remove-${randomUUID()}.bak`);
          await rename(diskPath, backupPath);
          moved.push({ originalPath: diskPath, backupPath });
        }
      }

      if (rows.length > 0) {
        await client.query(
          `DELETE FROM local_storage.objects WHERE id = ANY($1::uuid[])`,
          [rows.map((row) => row.id)],
        );
      }
      await client.query('COMMIT');
      await Promise.all(moved.map((entry) => unlink(entry.backupPath).catch(() => undefined)));
      return rows.map((row) => ({ id: row.id, bucket_id: row.bucket_id, name: row.name }));
    } catch (error) {
      await client.query('ROLLBACK').catch(() => undefined);
      for (const entry of moved.reverse()) {
        await rename(entry.backupPath, entry.originalPath).catch(() => undefined);
      }
      throw error;
    } finally {
      client.release();
    }
  }

  private async objectRow(bucketId: string, objectPath: string): Promise<ObjectRow> {
    const bucket = bucketSegment(bucketId);
    pathSegments(objectPath, 'Object path');
    const response = await this.pool.query<ObjectRow>(
      `SELECT o.*, b.public
       FROM local_storage.objects o
       JOIN local_storage.buckets b ON b.id = o.bucket_id
       WHERE o.bucket_id = $1 AND o.name = $2`,
      [bucket, objectPath],
    );
    const row = response.rows[0];
    if (!row) {
      throw new StorageError('Object not found.', 'object_not_found', 404);
    }
    return row;
  }

  private rowLocalPath(row: ObjectRow): string {
    const relativePath = row.local_path || `${row.bucket_id}/${row.name}`;
    pathSegments(relativePath, 'Stored local path');
    return relativePath;
  }

  private resolveRelative(relativePath: string): string {
    const segments = pathSegments(relativePath, 'Storage path');
    const resolved = path.resolve(this.storageRoot, ...segments);
    if (!resolved.startsWith(`${this.storageRoot}${path.sep}`)) {
      throw new StorageError('Storage path escapes STORAGE_ROOT.', 'invalid_path', 400);
    }
    return resolved;
  }

  private async ensureRoot(): Promise<void> {
    await mkdir(this.storageRoot, { recursive: true, mode: 0o700 });
    const details = await lstat(this.storageRoot);
    if (!details.isDirectory() || details.isSymbolicLink()) {
      throw new StorageError('STORAGE_ROOT must be a real directory.', 'unsafe_storage_root', 500);
    }
  }

  private async ensureSafeParent(filePath: string): Promise<void> {
    await this.ensureRoot();
    const relative = path.relative(this.storageRoot, path.dirname(filePath));
    if (!relative || relative.startsWith('..') || path.isAbsolute(relative)) {
      throw new StorageError('Object parent escapes STORAGE_ROOT.', 'invalid_path', 400);
    }

    let current = this.storageRoot;
    for (const segment of relative.split(path.sep)) {
      current = path.join(current, segment);
      try {
        const details = await lstat(current);
        if (!details.isDirectory() || details.isSymbolicLink()) {
          throw new StorageError('Symbolic links are not allowed in storage paths.', 'unsafe_storage_path', 400);
        }
      } catch (error) {
        if (errorCode(error) !== 'ENOENT') {
          throw error;
        }
        await mkdir(current, { mode: 0o700 });
        const details = await lstat(current);
        if (!details.isDirectory() || details.isSymbolicLink()) {
          throw new StorageError('Unsafe storage directory.', 'unsafe_storage_path', 400);
        }
      }
    }
  }

  private async assertSafeFileSystemPath(filePath: string, allowMissingFile: boolean): Promise<void> {
    await this.ensureRoot();
    const relative = path.relative(this.storageRoot, filePath);
    if (!relative || relative.startsWith('..') || path.isAbsolute(relative)) {
      throw new StorageError('Path escapes STORAGE_ROOT.', 'invalid_path', 400);
    }

    let current = this.storageRoot;
    const segments = relative.split(path.sep);
    for (const [index, segment] of segments.entries()) {
      current = path.join(current, segment);
      try {
        const details = await lstat(current);
        if (details.isSymbolicLink()) {
          throw new StorageError('Symbolic links are not allowed in storage.', 'unsafe_storage_path', 400);
        }
        const isLast = index === segments.length - 1;
        if ((!isLast && !details.isDirectory()) || (isLast && !details.isFile() && !details.isDirectory())) {
          throw new StorageError('Storage path has an invalid filesystem entry.', 'unsafe_storage_path', 400);
        }
      } catch (error) {
        if (allowMissingFile && index === segments.length - 1 && errorCode(error) === 'ENOENT') {
          return;
        }
        throw error;
      }
    }
  }

  private async regularFileExists(filePath: string): Promise<boolean> {
    try {
      await this.assertSafeFileSystemPath(filePath, false);
      const details = await lstat(filePath);
      if (!details.isFile() || details.isSymbolicLink()) {
        throw new StorageError('Object path is not a regular file.', 'unsafe_storage_path', 400);
      }
      return true;
    } catch (error) {
      if (errorCode(error) === 'ENOENT') {
        return false;
      }
      throw error;
    }
  }
}

export class LocalStorageBucketClient {
  constructor(
    private readonly service: LocalStorageService,
    private readonly bucketId: string,
  ) {}

  upload(path: string, body: unknown, options: UploadOptions = {}): Promise<StorageResult<UploadedObject>> {
    return this.service.upload(this.bucketId, path, body, options);
  }

  download(path: string): Promise<StorageResult<Blob>> {
    return this.service.download(this.bucketId, path);
  }

  remove(paths: string[]): Promise<StorageResult<RemovedObject[]>> {
    return this.service.remove(this.bucketId, paths);
  }

  getPublicUrl(path: string): { data: { publicUrl: string } } {
    return { data: { publicUrl: this.service.publicUrl(this.bucketId, path) } };
  }

  createSignedUrl(
    path: string,
    expiresIn: number,
    options: SignedUrlOptions = {},
  ): Promise<StorageResult<{ signedUrl: string }>> {
    return resultOf(() => this.service.signedUrl(this.bucketId, path, expiresIn, options));
  }
}

export const storageService = new LocalStorageService();