import type { Pool } from 'pg';

import { pgAll, pgGet } from '@/shared/db';
import { notFound } from '@/shared/http/errors';

import type { Attachment, AttachmentDraft } from './types';

interface Row {
  id: string;
  message_id: string;
  device_id: string;
  external_id: string;
  filename: string | null;
  transfer_name: string | null;
  mime_type: string | null;
  size: string | number | null;
  storage_kind: Attachment['storageKind'];
  data: Buffer | null;
  storage_bucket: string | null;
  storage_key: string | null;
  created_at: string;
  updated_at: string;
}

const hydrate = (r: Row): Attachment => ({
  id: r.id,
  messageId: r.message_id,
  deviceId: r.device_id,
  externalId: r.external_id,
  filename: r.filename,
  transferName: r.transfer_name,
  mimeType: r.mime_type,
  size: r.size !== null ? Number(r.size) : null,
  storageKind: r.storage_kind,
  data: r.data,
  storageBucket: r.storage_bucket,
  storageKey: r.storage_key,
  createdAt: r.created_at,
  updatedAt: r.updated_at,
});

/** Maximum decoded blob size stored inline as BYTEA. Oversize blobs are stored
 *  metadata-only with storage_kind='missing' until object-storage wiring lands. */
const MAX_INLINE_BYTES = 5 * 1024 * 1024;

export async function upsertAttachment(pool: Pool, draft: AttachmentDraft): Promise<Attachment> {
  try {
    const raw = draft.data ? Buffer.from(draft.data, 'base64') : null;
    const fitsInline = raw !== null && raw.byteLength <= MAX_INLINE_BYTES;
    const storageKind: Attachment['storageKind'] = raw === null
      ? 'missing'
      : fitsInline
        ? 'inline_base64'
        : 'missing';

    const rows = await pgAll<{ id: string }>(
      pool,
      `INSERT INTO macsync.attachments
       (id, message_id, device_id, external_id, filename, transfer_name, mime_type, size,
        storage_kind, data)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       ON CONFLICT(device_id, external_id) DO UPDATE SET
         message_id   = excluded.message_id,
         filename     = COALESCE(excluded.filename, attachments.filename),
         transfer_name= COALESCE(excluded.transfer_name, attachments.transfer_name),
         mime_type    = COALESCE(excluded.mime_type, attachments.mime_type),
         size         = COALESCE(excluded.size, attachments.size),
         storage_kind = excluded.storage_kind,
         data         = COALESCE(excluded.data, attachments.data),
         updated_at   = now()
       RETURNING id`,
      [
        draft.id,
        draft.messageId,
        draft.deviceId,
        draft.externalId,
        draft.filename ?? null,
        draft.transferName ?? null,
        draft.mimeType ?? null,
        draft.size ?? null,
        storageKind,
        fitsInline ? raw : null,
      ],
    );
    const actualId = rows[0]?.id ?? draft.id;
    return await getAttachment(pool, actualId);
  } catch (err) {
    throw new Error(`upsertAttachment(${draft.id}) failed: ${err instanceof Error ? err.message : String(err)}`, { cause: err });
  }
}

export async function getAttachment(pool: Pool, id: string): Promise<Attachment> {
  try {
    const row = await pgGet<Row>(pool, 'SELECT * FROM macsync.attachments WHERE id = $1', [id]);
    if (!row) throw notFound('attachment_not_found', `attachment ${id} not found`);
    return hydrate(row);
  } catch (err) {
    if (err instanceof Error && err.message.includes('attachment_not_found')) throw err;
    throw new Error(`getAttachment(${id}) failed: ${err instanceof Error ? err.message : String(err)}`, { cause: err });
  }
}

/** Maximum decoded blob size stored inline as BYTEA via the dedicated blob upload endpoint. */
const MAX_BLOB_INLINE_BYTES = 25 * 1024 * 1024; // 25MB — covers most photos; large videos stay 'missing'

/**
 * Store a raw binary blob for an existing attachment row.
 * Returns the updated storage_kind: 'inline_base64' if stored, 'missing' if oversized.
 */
export async function uploadAttachmentBlob(
  pool: Pool,
  deviceId: string,
  externalId: string,
  data: Buffer,
): Promise<{ storageKind: Attachment['storageKind']; id: string } | null> {
  try {
    const storageKind: Attachment['storageKind'] = data.byteLength <= MAX_BLOB_INLINE_BYTES
      ? 'inline_base64'
      : 'missing';

    const rows = await pgAll<{ id: string }>(
      pool,
      `UPDATE macsync.attachments
       SET data = $1, storage_kind = $2, updated_at = now()
       WHERE device_id = $3 AND external_id = $4
       RETURNING id`,
      [storageKind === 'inline_base64' ? data : null, storageKind, deviceId, externalId],
    );
    if (!rows[0]) return null;
    return { storageKind, id: rows[0].id };
  } catch (err) {
    throw new Error(`uploadAttachmentBlob(${externalId}) failed: ${err instanceof Error ? err.message : String(err)}`, { cause: err });
  }
}

interface MissingAttachmentRow {
  id: string;
  external_id: string;
  filename: string | null;
  transfer_name: string | null;
  mime_type: string | null;
  size: string | number | null;
}

export interface MissingAttachment {
  id: string;
  externalId: string;
  filename: string | null;
  transferName: string | null;
  mimeType: string | null;
  size: number | null;
}

/** List attachment records that have no blob data yet, for this device. */
export async function listMissingAttachments(
  pool: Pool,
  deviceId: string,
  limit = 200,
): Promise<readonly MissingAttachment[]> {
  try {
    const rows = await pgAll<MissingAttachmentRow>(
      pool,
      `SELECT id, external_id, filename, transfer_name, mime_type, size
       FROM macsync.attachments
       WHERE device_id = $1 AND storage_kind = 'missing'
       ORDER BY created_at ASC
       LIMIT $2`,
      [deviceId, limit],
    );
    return rows.map((r) => ({
      id: r.id,
      externalId: r.external_id,
      filename: r.filename,
      transferName: r.transfer_name,
      mimeType: r.mime_type,
      size: r.size !== null ? Number(r.size) : null,
    }));
  } catch (err) {
    throw new Error(`listMissingAttachments failed: ${err instanceof Error ? err.message : String(err)}`, { cause: err });
  }
}

export async function listAttachmentsByMessage(pool: Pool, messageId: string): Promise<readonly Attachment[]> {
  try {
    const rows = await pgAll<Row>(
      pool,
      'SELECT * FROM macsync.attachments WHERE message_id = $1 ORDER BY created_at ASC',
      [messageId],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listAttachmentsByMessage(${messageId}) failed: ${err instanceof Error ? err.message : String(err)}`, { cause: err });
  }
}
