deals24togo_be/migrations/003_payments.sql

-- Migration 003: Payments, Subscriptions, Purchases
-- Run in Supabase SQL editor

-- Extend listings status to include 'sold'
ALTER TABLE listings
  DROP CONSTRAINT IF EXISTS listings_status_check;
ALTER TABLE listings
  ADD CONSTRAINT listings_status_check
    CHECK (status IN ('pending', 'approved', 'rejected', 'sold'));

-- payments: one row per CinetPay transaction
CREATE TABLE payments (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  transaction_id  TEXT UNIQUE NOT NULL,
  type            TEXT NOT NULL
                      CHECK (type IN ('subscription', 'purchase')),
  payer_id        UUID REFERENCES users(id) ON DELETE SET NULL,
  amount          NUMERIC(12,2) NOT NULL,
  currency        TEXT NOT NULL DEFAULT 'XOF',
  status          TEXT NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'completed', 'failed', 'cancelled')),
  payment_method  TEXT,
  operator_id     TEXT,
  metadata        JSONB DEFAULT '{}'::JSONB,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  paid_at         TIMESTAMPTZ
);

-- subscriptions: active plan per agency
CREATE TABLE subscriptions (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  agency_id   UUID NOT NULL REFERENCES agencies(id) ON DELETE CASCADE,
  plan        TEXT NOT NULL CHECK (plan IN ('monthly', 'yearly')),
  status      TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'expired')),
  starts_at   TIMESTAMPTZ NOT NULL,
  ends_at     TIMESTAMPTZ NOT NULL,
  payment_id  UUID REFERENCES payments(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- purchases: visitor buys a listing
CREATE TABLE purchases (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  listing_id  UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  buyer_id    UUID REFERENCES users(id) ON DELETE SET NULL,
  amount      NUMERIC(12,2) NOT NULL,
  payment_id  UUID REFERENCES payments(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_transaction_id ON payments(transaction_id);
CREATE INDEX idx_payments_payer_id ON payments(payer_id);
CREATE INDEX idx_subscriptions_agency_id ON subscriptions(agency_id);
CREATE INDEX idx_subscriptions_ends_at ON subscriptions(ends_at);
CREATE INDEX idx_purchases_listing_id ON purchases(listing_id);