import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { MAX_QUANTITY } from './internal/bigint.js'
import type { MeteringSchema } from './schema.js'

const quantityBound = sql.raw(MAX_QUANTITY.toString())

export class MeteringMigrateError extends Error {
  override readonly name = 'MeteringMigrateError'
  readonly code = 'METERING_MIGRATE' as const

  constructor(readonly detail: string) {
    super(`metering migrate: ${detail}`)
  }
}

export function isMeteringMigrateError(error: unknown): error is MeteringMigrateError {
  return (
    typeof error === 'object' &&
    error !== null &&
    (error as { name?: unknown }).name === 'MeteringMigrateError' &&
    (error as { code?: unknown }).code === 'METERING_MIGRATE'
  )
}

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS metering_counter (
      tenant_id text NOT NULL,
      account text NOT NULL,
      meter text NOT NULL,
      period_id text NOT NULL,
      committed numeric NOT NULL,
      reserved numeric NOT NULL,
      CONSTRAINT metering_counter_pkey PRIMARY KEY (tenant_id, account, meter, period_id),
      CONSTRAINT metering_counter_committed_nonnegative CHECK (committed >= 0),
      CONSTRAINT metering_counter_reserved_nonnegative CHECK (reserved >= 0),
      CONSTRAINT metering_counter_committed_integral CHECK (committed = trunc(committed)),
      CONSTRAINT metering_counter_committed_bounded CHECK (committed < ${quantityBound}),
      CONSTRAINT metering_counter_reserved_integral CHECK (reserved = trunc(reserved)),
      CONSTRAINT metering_counter_reserved_bounded CHECK (reserved < ${quantityBound})
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS metering_reservation (
      id text PRIMARY KEY,
      tenant_id text NOT NULL,
      idempotency_key text NOT NULL,
      fingerprint text NOT NULL,
      account text NOT NULL,
      meter text NOT NULL,
      period_id text NOT NULL,
      period_starts_at timestamptz NOT NULL,
      period_ends_at timestamptz NOT NULL,
      reserved_units numeric NOT NULL,
      committed_units numeric,
      status text NOT NULL,
      applied_included_units numeric NOT NULL,
      applied_hard_limit numeric,
      applied_overage text NOT NULL,
      policy_version text NOT NULL,
      expires_at timestamptz NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT metering_reservation_tenant_idempotency_key_key UNIQUE (tenant_id, idempotency_key),
      CONSTRAINT metering_reservation_counter_fk
        FOREIGN KEY (tenant_id, account, meter, period_id)
        REFERENCES metering_counter (tenant_id, account, meter, period_id),
      CONSTRAINT metering_reservation_reserved_units_nonnegative CHECK (reserved_units >= 0),
      CONSTRAINT metering_reservation_reserved_units_integral CHECK (reserved_units = trunc(reserved_units)),
      CONSTRAINT metering_reservation_reserved_units_bounded CHECK (reserved_units < ${quantityBound}),
      CONSTRAINT metering_reservation_committed_units_nonnegative
        CHECK (committed_units IS NULL OR committed_units >= 0),
      CONSTRAINT metering_reservation_committed_units_integral
        CHECK (committed_units IS NULL OR committed_units = trunc(committed_units)),
      CONSTRAINT metering_reservation_committed_units_bounded
        CHECK (committed_units IS NULL OR committed_units < ${quantityBound}),
      CONSTRAINT metering_reservation_applied_included_units_nonnegative CHECK (applied_included_units >= 0),
      CONSTRAINT metering_reservation_applied_included_units_integral
        CHECK (applied_included_units = trunc(applied_included_units)),
      CONSTRAINT metering_reservation_applied_included_units_bounded
        CHECK (applied_included_units < ${quantityBound}),
      CONSTRAINT metering_reservation_applied_hard_limit
        CHECK (applied_hard_limit IS NULL OR applied_hard_limit >= applied_included_units),
      CONSTRAINT metering_reservation_applied_hard_limit_integral
        CHECK (applied_hard_limit IS NULL OR applied_hard_limit = trunc(applied_hard_limit)),
      CONSTRAINT metering_reservation_applied_hard_limit_bounded
        CHECK (applied_hard_limit IS NULL OR applied_hard_limit < ${quantityBound}),
      CONSTRAINT metering_reservation_status
        CHECK (status IN ('reserved', 'committed', 'released', 'expired')),
      CONSTRAINT metering_reservation_applied_overage CHECK (applied_overage IN ('deny', 'allow'))
    )
  `,
  sql`
    CREATE INDEX IF NOT EXISTS metering_reservation_status_expires_at_idx
      ON metering_reservation (status, expires_at)
  `,
  sql`
    CREATE TABLE IF NOT EXISTS metering_usage_event (
      id text PRIMARY KEY,
      tenant_id text NOT NULL,
      account text NOT NULL,
      meter text NOT NULL,
      period_id text NOT NULL,
      units numeric NOT NULL,
      kind text NOT NULL,
      reservation_id text NOT NULL,
      rule_version text,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT metering_usage_event_reservation_id_key UNIQUE (reservation_id),
      CONSTRAINT metering_usage_event_units_nonnegative CHECK (units >= 0),
      CONSTRAINT metering_usage_event_units_integral CHECK (units = trunc(units)),
      CONSTRAINT metering_usage_event_units_bounded CHECK (units < ${quantityBound}),
      CONSTRAINT metering_usage_event_kind CHECK (kind = 'commit')
    )
  `,
  sql`
    CREATE OR REPLACE FUNCTION metering_usage_event_append_only() RETURNS trigger AS $$
    BEGIN
      RAISE EXCEPTION 'metering_usage_event is append-only: % is not permitted', TG_OP
        USING ERRCODE = 'restrict_violation';
    END;
    $$ LANGUAGE plpgsql
  `,
  sql`
    DROP TRIGGER IF EXISTS metering_usage_event_append_only ON metering_usage_event
  `,
  sql`
    CREATE TRIGGER metering_usage_event_append_only
      BEFORE UPDATE OR DELETE ON metering_usage_event
      FOR EACH ROW EXECUTE FUNCTION metering_usage_event_append_only()
  `,
  sql`
    DROP TRIGGER IF EXISTS metering_usage_event_append_only_truncate ON metering_usage_event
  `,
  sql`
    CREATE TRIGGER metering_usage_event_append_only_truncate
      BEFORE TRUNCATE ON metering_usage_event
      FOR EACH STATEMENT EXECUTE FUNCTION metering_usage_event_append_only()
  `,
]

export async function pushSchema(db: Querier<MeteringSchema>): Promise<void> {
  try {
    for (const statement of MIGRATION_STATEMENTS) {
      await db.execute(statement)
    }
  } catch (error) {
    throw new MeteringMigrateError(error instanceof Error ? error.message : String(error))
  }
}