import postgres, { type Sql } from 'postgres';
import {
  ConflictError,
  PreconditionFailedError,
  toBase64,
  fromBase64,
} from '@shade/core';
import type { BlobStore, BlobSlotRecord, PutBlobResult } from '@shade/inbox-server';
import { ensureBlobServerTables } from './ensure-tables.js';

/**
 * PostgreSQL-backed BlobStore for the V4.9 encrypted-blob primitive.
 *
 * CAS is implemented at SQL level using a single UPDATE-with-WHERE
 * (existing slots) or INSERT (empty slots), wrapped in a transaction so
 * the read-then-write window can't race. ETag is generated server-side
 * via `nextval('shade_blob_seq')` so the value is monotonic across
 * processes — multi-instance deployments share a strict ordering.
 */
export class PostgresBlobStore implements BlobStore {
  private constructor(
    private readonly sql: Sql,
    private readonly ownsConnection: boolean,
  ) {}

  static async create(connectionString: string): Promise<PostgresBlobStore> {
    const sql = postgres(connectionString);
    const store = new PostgresBlobStore(sql, true);
    await ensureBlobServerTables(sql);
    return store;
  }

  static async fromClient(sql: Sql): Promise<PostgresBlobStore> {
    const store = new PostgresBlobStore(sql, false);
    await ensureBlobServerTables(sql);
    return store;
  }

  async close(): Promise<void> {
    if (this.ownsConnection) await this.sql.end();
  }

  async get(slotId: string): Promise<BlobSlotRecord | null> {
    const rows = await this.sql<
      Array<{
        slot_id: string;
        owner_pubkey: string;
        blob: string;
        etag: string;
        updated_at: string;
      }>
    >`
      SELECT slot_id, owner_pubkey, blob, etag::text, updated_at::text
      FROM shade_blob_slots WHERE slot_id = ${slotId}
    `;
    if (rows.length === 0) return null;
    const r = rows[0]!;
    return {
      slotId: r.slot_id,
      ownerPubkey: fromBase64(r.owner_pubkey),
      blob: fromBase64(r.blob),
      etag: parseInt(r.etag, 10),
      updatedAt: parseInt(r.updated_at, 10),
    };
  }

  async put(args: {
    slotId: string;
    blob: Uint8Array;
    ownerPubkey: Uint8Array;
    expectedEtag: number | '*' | undefined;
    now: number;
  }): Promise<PutBlobResult> {
    // Wrap in a serializable txn so the read-current → write window
    // can't race with another writer.
    return this.sql.begin(async (tx) => {
      const existing = await tx<Array<{ etag: string }>>`
        SELECT etag::text FROM shade_blob_slots
        WHERE slot_id = ${args.slotId}
        FOR UPDATE
      `;

      if (existing.length === 0) {
        if (args.expectedEtag !== undefined) {
          throw new PreconditionFailedError(
            `Slot ${args.slotId} does not exist; cannot match ifMatch=${String(args.expectedEtag)}`,
          );
        }
        const inserted = await tx<Array<{ etag: string }>>`
          INSERT INTO shade_blob_slots (slot_id, owner_pubkey, blob, etag, updated_at)
          VALUES (
            ${args.slotId},
            ${toBase64(args.ownerPubkey)},
            ${toBase64(args.blob)},
            nextval('shade_blob_seq'),
            ${args.now}
          )
          RETURNING etag::text
        `;
        return {
          created: true,
          etag: parseInt(inserted[0]!.etag, 10),
          updatedAt: args.now,
        };
      }

      const currentEtag = parseInt(existing[0]!.etag, 10);
      if (args.expectedEtag === undefined) {
        throw new ConflictError(
          `Slot ${args.slotId} already exists; supply ifMatch to update`,
        );
      }
      if (args.expectedEtag !== '*' && args.expectedEtag !== currentEtag) {
        throw new PreconditionFailedError(
          `Slot ${args.slotId} etag mismatch: expected=${args.expectedEtag} actual=${currentEtag}`,
        );
      }
      const updated = await tx<Array<{ etag: string }>>`
        UPDATE shade_blob_slots
        SET blob = ${toBase64(args.blob)},
            etag = nextval('shade_blob_seq'),
            updated_at = ${args.now}
        WHERE slot_id = ${args.slotId}
        RETURNING etag::text
      `;
      return {
        created: false,
        etag: parseInt(updated[0]!.etag, 10),
        updatedAt: args.now,
      };
    }) as Promise<PutBlobResult>;
  }

  async delete(slotId: string): Promise<boolean> {
    const result = await this.sql`
      DELETE FROM shade_blob_slots WHERE slot_id = ${slotId}
    `;
    return result.count > 0;
  }
}
