`migrations/007_upgrade_orders.sql`

40 lines
-- EZ Upgrade order intent. Captures "yes I want to pay $499" before Stripe is
-- wired so admin can hand-process payment links until automation lands.

CREATE TABLE IF NOT EXISTS upgrade_orders (
  id              BIGSERIAL PRIMARY KEY,
  app_user_id     BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
  professional_id BIGINT REFERENCES professionals(id) ON DELETE SET NULL,
  organization_id BIGINT REFERENCES organizations(id) ON DELETE SET NULL,

  full_name       TEXT NOT NULL,
  email           TEXT NOT NULL,
  phone           TEXT,
  bar_number      TEXT,
  firm_name       TEXT,
  website         TEXT,

  plan            TEXT NOT NULL DEFAULT 'ez_upgrade_499',
  amount_cents    INTEGER NOT NULL DEFAULT 49900,

  status          TEXT NOT NULL DEFAULT 'pending_payment'
                  CHECK (status IN ('pending_payment','paid','in_production','delivered','refunded','cancelled')),
  payment_link    TEXT,
  paid_at         TIMESTAMPTZ,
  delivered_at    TIMESTAMPTZ,
  notes           TEXT,

  ip              TEXT,
  user_agent      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_upgrade_orders_status  ON upgrade_orders(status);
CREATE INDEX IF NOT EXISTS idx_upgrade_orders_email   ON upgrade_orders(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_upgrade_orders_created ON upgrade_orders(created_at DESC);

CREATE OR REPLACE TRIGGER trg_upgrade_orders_updated
  BEFORE UPDATE ON upgrade_orders
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();