CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS merchants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  api_key_hash CHAR(64) NOT NULL UNIQUE,
  webhook_url TEXT,
  webhook_secret_ciphertext TEXT,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS checkout_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  merchant_id UUID NOT NULL REFERENCES merchants(id),
  idempotency_key TEXT NOT NULL,
  request_hash CHAR(64) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  token_ciphertext TEXT NOT NULL,
  amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
  currency CHAR(3) NOT NULL,
  description TEXT NOT NULL,
  order_reference TEXT NOT NULL,
  customer_email TEXT,
  customer_name TEXT,
  success_url TEXT NOT NULL,
  failure_url TEXT NOT NULL,
  cancel_url TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('created','pending','paid','failed','cancelled','expired','refunded')),
  expires_at TIMESTAMPTZ NOT NULL,
  provider_session_id TEXT,
  provider_payment_id TEXT,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (merchant_id, idempotency_key)
);
CREATE INDEX IF NOT EXISTS checkout_sessions_merchant_created_idx ON checkout_sessions(merchant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS checkout_sessions_expiry_idx ON checkout_sessions(status, expires_at);

CREATE TABLE IF NOT EXISTS payment_attempts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES checkout_sessions(id),
  provider TEXT NOT NULL,
  provider_reference TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL CHECK (status IN ('initiated','pending','paid','failed','cancelled','refunded')),
  provider_session_id TEXT,
  provider_payment_id TEXT,
  raw_result JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS gateway_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  merchant_id UUID NOT NULL REFERENCES merchants(id),
  session_id UUID NOT NULL REFERENCES checkout_sessions(id),
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS gateway_events_session_idx ON gateway_events(session_id, created_at);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES gateway_events(id),
  merchant_id UUID NOT NULL REFERENCES merchants(id),
  target_url TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','delivering','delivered','failed')),
  attempts INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_http_status INTEGER,
  last_error TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  delivered_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS webhook_deliveries_queue_idx ON webhook_deliveries(status, next_attempt_at);