import { sql } from 'drizzle-orm';
import type { Querier } from '@platform-modules/db';
import type { DedupStore, IntentStore } from '@platform-modules/billing';

export const CREATE_CHARGE_INTENTS_TABLE_SQL = `CREATE TABLE IF NOT EXISTS mod_storefront_charge_intents (
  charge_key TEXT PRIMARY KEY,
  amount BIGINT NOT NULL,
  currency TEXT NOT NULL,
  provider_ref TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)`;

export const CREATE_WEBHOOK_EVENTS_TABLE_SQL = `CREATE TABLE IF NOT EXISTS mod_storefront_webhook_events (
  event_id TEXT PRIMARY KEY,
  claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  processed_at TIMESTAMPTZ
)`;

function normalizeRows(result: unknown): unknown[] {
  if (Array.isArray(result)) return result;
  const rows = (result as { rows?: unknown[] } | null)?.rows;
  return rows ?? [];
}

export function createDbIntentStore(db: Querier): IntentStore {
  return {
    async claimIntent(chargeKey, intent) {
      const inserted = normalizeRows(
        await db.execute(sql`
          INSERT INTO mod_storefront_charge_intents (charge_key, amount, currency, status, claimed_at)
          VALUES (${chargeKey}, ${intent.amount}, ${intent.currency}, 'pending', NOW())
          ON CONFLICT (charge_key) DO NOTHING
          RETURNING charge_key
        `),
      );
      if (inserted.length > 0) return 'won';
      const rows = normalizeRows(
        await db.execute(
          sql`SELECT status FROM mod_storefront_charge_intents WHERE charge_key = ${chargeKey} LIMIT 1`,
        ),
      );
      const row = rows[0] as { status?: string } | undefined;
      return row?.status === 'settled' ? 'settled' : 'pending';
    },

    async recordProviderRef(chargeKey, providerRef) {
      await db.execute(sql`
        UPDATE mod_storefront_charge_intents
        SET provider_ref = ${providerRef}, updated_at = NOW()
        WHERE charge_key = ${chargeKey}
      `);
    },

    async markIntentSettled(chargeKey) {
      await db.execute(sql`
        UPDATE mod_storefront_charge_intents
        SET status = 'settled', updated_at = NOW()
        WHERE charge_key = ${chargeKey}
      `);
    },

    async listUnsettledIntents(olderThanMs) {
      const rows = normalizeRows(
        await db.execute(sql`
          SELECT charge_key, provider_ref, amount, currency
          FROM mod_storefront_charge_intents
          WHERE status = 'pending'
            AND claimed_at < NOW() - (${olderThanMs ?? 0}::float8 * interval '1 millisecond')
        `),
      );
      return rows.map((r) => {
        const row = r as { charge_key: unknown; provider_ref: unknown; amount: unknown; currency: unknown };
        return {
          chargeKey: String(row.charge_key),
          providerRef: row.provider_ref != null ? String(row.provider_ref) : undefined,
          amount: Number(row.amount),
          currency: String(row.currency),
        };
      });
    },
  };
}

export function createDbDedupStore(db: Querier): DedupStore {
  return {
    async claim(eventId) {
      const rows = normalizeRows(
        await db.execute(sql`
          INSERT INTO mod_storefront_webhook_events (event_id, claimed_at)
          VALUES (${eventId}, NOW())
          ON CONFLICT (event_id) DO UPDATE
            SET claimed_at = NOW()
            WHERE mod_storefront_webhook_events.processed_at IS NULL
              AND (
                mod_storefront_webhook_events.claimed_at IS NULL
                OR mod_storefront_webhook_events.claimed_at < NOW() - INTERVAL '5 minutes'
              )
          RETURNING event_id
        `),
      );
      return rows.length > 0 ? 'won' : 'lost';
    },

    async markProcessed(eventId) {
      await db.execute(sql`
        UPDATE mod_storefront_webhook_events
        SET processed_at = NOW()
        WHERE event_id = ${eventId}
      `);
    },
  };
}