Stripe Deployment Guide

-- ═══════════════════════════════════════════════════════════════
-- Agent Builder × Stripe — SQL Migration
-- Run in: Supabase Dashboard → SQL Editor → New Query → Run
-- Safe to re-run (uses IF NOT EXISTS everywhere)
-- ═══════════════════════════════════════════════════════════════


-- ──────────────────────────────────────────────────────────────
-- 1. stripe_payments table
--    Stores webhook events from Stripe (payment_intent.succeeded,
--    payment_intent.payment_failed, account.updated, etc.)
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.stripe_payments (
  id              text        PRIMARY KEY,          -- pi_xxx or evt_xxx
  event_type      text        NOT NULL,             -- payment_intent.succeeded etc.
  status          text,                             -- succeeded | failed | canceled | pending
  amount          integer,                          -- cents (e.g. 1000 = $10.00)
  currency        text,                             -- usd, eur, gbp …
  description     text,
  customer_email  text,
  metadata        jsonb       DEFAULT '{}'::jsonb,
  account_id      text,                             -- connected account (acct_xxx) if any
  raw_event       jsonb,                            -- full Stripe event payload
  created_at      timestamptz DEFAULT now()
);

-- Index for common queries
CREATE INDEX IF NOT EXISTS stripe_payments_status_idx
  ON public.stripe_payments (status);

CREATE INDEX IF NOT EXISTS stripe_payments_created_idx
  ON public.stripe_payments (created_at DESC);

CREATE INDEX IF NOT EXISTS stripe_payments_account_idx
  ON public.stripe_payments (account_id);

-- Row Level Security (allow service role full access)
ALTER TABLE public.stripe_payments ENABLE ROW LEVEL SECURITY;

-- Policy: service role can do everything
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stripe_payments'
    AND policyname  = 'service_role_all'
  ) THEN
    EXECUTE $policy$
      CREATE POLICY service_role_all ON public.stripe_payments
        FOR ALL TO service_role USING (true) WITH CHECK (true)
    $policy$;
  END IF;
END
$$;

-- Policy: authenticated users can read (for admin dashboard)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stripe_payments'
    AND policyname  = 'authenticated_read'
  ) THEN
    EXECUTE $policy$
      CREATE POLICY authenticated_read ON public.stripe_payments
        FOR SELECT TO authenticated USING (true)
    $policy$;
  END IF;
END
$$;


-- ──────────────────────────────────────────────────────────────
-- 2. Add Stripe columns to platform_settings
--    (safe — uses DO block with IF NOT EXISTS check)
-- ──────────────────────────────────────────────────────────────
DO $$
BEGIN
  -- Stripe Publishable Key  (pk_live_... or pk_test_...)
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
    AND   table_name   = 'platform_settings'
    AND   column_name  = 'stripe_publishable_key'
  ) THEN
    ALTER TABLE public.platform_settings
      ADD COLUMN stripe_publishable_key text DEFAULT '';
  END IF;

  -- Stripe Secret Key  (sk_live_... or sk_test_...)
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
    AND   table_name   = 'platform_settings'
    AND   column_name  = 'stripe_secret_key'
  ) THEN
    ALTER TABLE public.platform_settings
      ADD COLUMN stripe_secret_key text DEFAULT '';
  END IF;

  -- Stripe Webhook Signing Secret  (whsec_...)
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
    AND   table_name   = 'platform_settings'
    AND   column_name  = 'stripe_webhook_secret'
  ) THEN
    ALTER TABLE public.platform_settings
      ADD COLUMN stripe_webhook_secret text DEFAULT '';
  END IF;

  -- Platform fee percentage  (e.g. 0.10 = 10%)
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
    AND   table_name   = 'platform_settings'
    AND   column_name  = 'stripe_platform_fee_pct'
  ) THEN
    ALTER TABLE public.platform_settings
      ADD COLUMN stripe_platform_fee_pct numeric(5,4) DEFAULT 0;
  END IF;
END
$$;


-- ──────────────────────────────────────────────────────────────
-- 3. Ensure the global settings row exists
--    (safe — INSERT ... ON CONFLICT DO NOTHING)
-- ──────────────────────────────────────────────────────────────
INSERT INTO public.platform_settings (id)
VALUES ('global')
ON CONFLICT (id) DO NOTHING;


-- ──────────────────────────────────────────────────────────────
-- 4. stripe_connect_accounts table  (NEW — company-level Connect)
--    One row per Agent Builder company (id = sb_accounts.id).
--    Populated by stripe-connect-onboard when a company onboards,
--    kept in sync by stripe-webhook on account.updated events.
--    stripe-create-payment reads this table to auto-resolve the
--    company's acct_xxx — no manual entry needed in the builder.
-- ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.stripe_connect_accounts (
  id                 text        PRIMARY KEY,           -- = Agent Builder account_id (sb_accounts.id)
  stripe_account_id  text        NOT NULL,               -- Stripe Express account (acct_xxx)
  email              text,
  status             text        DEFAULT 'pending',      -- pending | pending_review | active
  charges_enabled    boolean     DEFAULT false,
  payouts_enabled    boolean     DEFAULT false,
  details_submitted  boolean     DEFAULT false,
  updated_at         timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS stripe_connect_accounts_stripe_acct_idx
  ON public.stripe_connect_accounts (stripe_account_id);

ALTER TABLE public.stripe_connect_accounts ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stripe_connect_accounts'
    AND policyname  = 'service_role_all'
  ) THEN
    EXECUTE $policy$
      CREATE POLICY service_role_all ON public.stripe_connect_accounts
        FOR ALL TO service_role USING (true) WITH CHECK (true)
    $policy$;
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stripe_connect_accounts'
    AND policyname  = 'authenticated_read'
  ) THEN
    EXECUTE $policy$
      CREATE POLICY authenticated_read ON public.stripe_connect_accounts
        FOR SELECT TO authenticated USING (true)
    $policy$;
  END IF;
END
$$;


-- ──────────────────────────────────────────────────────────────
-- Done!  Expected output: "Success. No rows returned"
-- ──────────────────────────────────────────────────────────────

/*
  stripe-create-payment  —  Supabase Edge Function
  ─────────────────────────────────────────────────
  Handles two actions:

  POST { action: "get_publishable_key" }
    → returns { publishable_key: "pk_live_..." }

  POST { action: "create_intent", amount, currency, description, metadata, account_id, company_account_id }
    → returns { client_secret: "pi_xxx_secret_xxx", intent_id: "pi_xxx" }

  account_id vs company_account_id:
    account_id         — an explicit Stripe connected account (acct_xxx). Takes priority if set.
    company_account_id — YOUR internal Agent Builder account_id (sb_accounts.id). If account_id is
                         NOT provided, the function looks up stripe_connect_accounts for this
                         company and auto-uses its acct_xxx (only if charges_enabled = true).
                         This lets a Payment node just reference "this company's account" without
                         anyone hand-typing an acct_xxx in the builder.

  Keys are read from platform_settings WHERE id='global':
    stripe_secret_key        — sk_live_... or sk_test_...
    stripe_publishable_key   — pk_live_... or pk_test_...
    stripe_platform_fee_pct  — e.g. 0.10 for 10%  (optional)

  CORS: allows all origins (chatbot embed use-case).
  No import needed — uses Deno.serve() built-in.
*/

const CORS: Record<string, string> = {
  "Access-Control-Allow-Origin" : "*",
  "Access-Control-Allow-Methods": "POST, GET, OPTIONS",
  "Access-Control-Allow-Headers": "Content-Type, Authorization, apikey"
};

function json(data: unknown, status = 200): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { ...CORS, "Content-Type": "application/json" }
  });
}

/* ── Fetch Stripe keys from platform_settings table ── */
let _cachedSettings: Record<string, string> | null = null;

async function getStripeSettings(): Promise<Record<string, string>> {
  if (_cachedSettings) return _cachedSettings;

  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";

  if (!sbUrl || !serviceKey) {
    throw new Error("SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY env vars missing");
  }

  const url = `${sbUrl}/rest/v1/platform_settings?id=eq.global&select=stripe_secret_key,stripe_publishable_key,stripe_platform_fee_pct&limit=1`;
  const r   = await fetch(url, {
    headers: {
      "apikey":        serviceKey,
      "Authorization": "Bearer " + serviceKey
    }
  });

  if (!r.ok) {
    throw new Error(`platform_settings fetch failed (HTTP ${r.status}): ${await r.text()}`);
  }

  const rows = await r.json() as Record<string, string>[];
  const row  = rows[0] ?? {};

  if (!row.stripe_secret_key) {
    throw new Error(
      "stripe_secret_key is empty in platform_settings. " +
      "Go to Platform Settings → Stripe and add your Stripe Secret Key."
    );
  }

  _cachedSettings = {
    secret_key     : row.stripe_secret_key,
    publishable_key: row.stripe_publishable_key ?? "",
    fee_pct        : row.stripe_platform_fee_pct ?? "0"
  };

  return _cachedSettings;
}

/* ── Resolve a company's connected Stripe account (if any) ──
   Looks up stripe_connect_accounts WHERE id = company_account_id.
   Only returns the acct_xxx if charges_enabled = true — otherwise
   the company hasn't finished onboarding yet, so we fall back to
   the platform account (accountId = null). ── */
async function resolveCompanyConnectAccount(companyAccountId: string): Promise<string | null> {
  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";
  if (!sbUrl || !serviceKey) return null;

  try {
    const url = `${sbUrl}/rest/v1/stripe_connect_accounts?id=eq.${encodeURIComponent(companyAccountId)}&select=stripe_account_id,charges_enabled&limit=1`;
    const r   = await fetch(url, {
      headers: { "apikey": serviceKey, "Authorization": "Bearer " + serviceKey }
    });
    if (!r.ok) return null;
    const rows = await r.json() as { stripe_account_id?: string; charges_enabled?: boolean }[];
    const row  = rows[0];
    if (row && row.charges_enabled && row.stripe_account_id) {
      return row.stripe_account_id;
    }
    return null;
  } catch (_) {
    return null;
  }
}

/* ── Main handler ── */
Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method === "OPTIONS") {
    return new Response(null, { status: 204, headers: CORS });
  }

  if (req.method !== "POST") {
    return json({ error: "Only POST is supported" }, 405);
  }

  let body: Record<string, unknown> = {};
  try {
    body = await req.json();
  } catch (_) {
    return json({ error: "Invalid JSON body" }, 400);
  }

  const action = body.action as string | undefined;

  /* ── Action: get_publishable_key ── */
  if (action === "get_publishable_key") {
    try {
      const settings = await getStripeSettings();
      if (!settings.publishable_key) {
        return json({ error: "stripe_publishable_key not set in platform_settings" }, 500);
      }
      return json({ publishable_key: settings.publishable_key });
    } catch (e) {
      return json({ error: (e as Error).message }, 500);
    }
  }

  /* ── Action: create_intent ── */
  if (action === "create_intent") {
    const amount            = Number(body.amount);
    const currency          = String(body.currency    || "usd").toLowerCase();
    const description       = String(body.description || "");
    const metadata          = (body.metadata as Record<string, string>) || {};
    const companyAccountId  = body.company_account_id ? String(body.company_account_id) : null;

    /* Explicit account_id wins; otherwise auto-resolve from company_account_id */
    let accountId = body.account_id ? String(body.account_id) : null;
    if (!accountId && companyAccountId) {
      accountId = await resolveCompanyConnectAccount(companyAccountId);
    }

    if (!amount || amount < 50) {
      return json({ error: "amount must be at least 50 (cents)" }, 400);
    }

    try {
      const settings  = await getStripeSettings();
      const secretKey = settings.secret_key;
      const feePct    = parseFloat(settings.fee_pct || "0");

      /* Build Stripe API request */
      const params: string[][] = [
        ["amount",   String(amount)  ],
        ["currency", currency        ],
        ["description", description  ],
        ["automatic_payment_methods[enabled]", "true"]
      ];

      /* Attach metadata */
      Object.entries(metadata).forEach(([k, v]) => {
        params.push([`metadata[${k}]`, String(v)]);
      });

      /* Platform fee (only if routing to a connected account) */
      if (accountId && feePct > 0) {
        const fee = Math.round(amount * feePct);
        params.push(["application_fee_amount", String(fee)]);
      }

      const headers: Record<string, string> = {
        "Authorization": "Bearer " + secretKey,
        "Content-Type" : "application/x-www-form-urlencoded"
      };
      if (accountId) {
        headers["Stripe-Account"] = accountId;
      }

      const stripeResp = await fetch("https://api.stripe.com/v1/payment_intents", {
        method : "POST",
        headers,
        body   : new URLSearchParams(params).toString()
      });

      const pi = await stripeResp.json() as Record<string, string>;

      if (!stripeResp.ok) {
        const errMsg = (pi as unknown as { error?: { message?: string } }).error?.message
          ?? "Stripe API error";
        return json({ error: errMsg }, 502);
      }

      return json({
        client_secret : pi.client_secret,
        intent_id     : pi.id,
        amount,
        currency,
        status        : pi.status
      });

    } catch (e) {
      return json({ error: (e as Error).message }, 500);
    }
  }

  return json({ error: `Unknown action: ${action}` }, 400);
});

/*
  stripe-connect-onboard  —  Supabase Edge Function
  ────────────────────────────────────────────────────
  Creates a Stripe Express connected account and returns
  an onboarding link (account_links URL) that redirects
  the user to Stripe-hosted onboarding.

  POST body:
  {
    account_id?         : string   // existing acct_xxx to resume onboarding
    email?              : string   // pre-fill email
    return_url          : string   // where Stripe redirects after onboarding
    refresh_url         : string   // where Stripe redirects if link expires
    metadata?           : object   // stored on the Stripe account
    company_account_id? : string   // YOUR internal Agent Builder account_id (sb_accounts.id).
                                    // When provided, the resulting acct_xxx is persisted
                                    // into the stripe_connect_accounts table so the company's
                                    // Payment nodes can auto-resolve their connected account.
  }

  Returns:
  {
    url          : string   // Stripe onboarding URL (redirect user here)
    account_id   : string   // acct_xxx — also saved to stripe_connect_accounts if
                             // company_account_id was supplied
  }

  Stripe key read from platform_settings.stripe_secret_key
*/

const CORS: Record<string, string> = {
  "Access-Control-Allow-Origin" : "*",
  "Access-Control-Allow-Methods": "POST, OPTIONS",
  "Access-Control-Allow-Headers": "Content-Type, Authorization, apikey"
};

function json(data: unknown, status = 200): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { ...CORS, "Content-Type": "application/json" }
  });
}

let _cachedKey: string | null = null;

async function getStripeSecretKey(): Promise<string> {
  if (_cachedKey) return _cachedKey;
  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";
  if (!sbUrl || !serviceKey) throw new Error("Missing SUPABASE env vars");

  const r = await fetch(
    `${sbUrl}/rest/v1/platform_settings?id=eq.global&select=stripe_secret_key&limit=1`,
    { headers: { "apikey": serviceKey, "Authorization": "Bearer " + serviceKey } }
  );
  if (!r.ok) throw new Error(`platform_settings fetch failed (${r.status})`);

  const rows = await r.json() as { stripe_secret_key?: string }[];
  const key  = rows[0]?.stripe_secret_key ?? "";
  if (!key) throw new Error("stripe_secret_key not set in platform_settings");
  _cachedKey = key;
  return key;
}

/* ── Stripe helper: POST form-encoded to Stripe API ── */
async function stripePost(
  secretKey: string,
  endpoint: string,
  params: string[][]
): Promise<Record<string, unknown>> {
  const r = await fetch(`https://api.stripe.com/v1/${endpoint}`, {
    method : "POST",
    headers: {
      "Authorization": "Bearer " + secretKey,
      "Content-Type" : "application/x-www-form-urlencoded"
    },
    body: new URLSearchParams(params).toString()
  });
  const data = await r.json() as Record<string, unknown>;
  if (!r.ok) {
    const errMsg = (data.error as { message?: string } | undefined)?.message ?? "Stripe error";
    throw new Error(errMsg);
  }
  return data;
}

/* ── Persist / upsert the connected account against a company ──
   One row per company_account_id (our internal sb_accounts.id).
   Uses PostgREST upsert via ?on_conflict=id + Prefer: resolution=merge-duplicates. ── */
async function saveConnectAccount(
  companyAccountId: string,
  stripeAccountId: string,
  email: string | undefined
): Promise<void> {
  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";
  if (!sbUrl || !serviceKey) return; // best-effort — don't fail onboarding if this errors

  try {
    const r = await fetch(`${sbUrl}/rest/v1/stripe_connect_accounts?on_conflict=id`, {
      method : "POST",
      headers: {
        "apikey"       : serviceKey,
        "Authorization": "Bearer " + serviceKey,
        "Content-Type" : "application/json",
        "Prefer"       : "resolution=merge-duplicates"
      },
      body: JSON.stringify({
        id                : companyAccountId,
        stripe_account_id : stripeAccountId,
        email             : email ?? null,
        status            : "pending",
        charges_enabled   : false,
        details_submitted: false,
        updated_at        : new Date().toISOString()
      })
    });
    if (!r.ok) {
      console.error("saveConnectAccount failed:", r.status, await r.text());
    }
  } catch (e) {
    console.error("saveConnectAccount error:", (e as Error).message);
  }
}

Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method === "OPTIONS") return new Response(null, { status: 204, headers: CORS });
  if (req.method !== "POST")   return json({ error: "POST only" }, 405);

  let body: Record<string, string | Record<string, string> | undefined> = {};
  try { body = await req.json(); } catch (_) { return json({ error: "Invalid JSON" }, 400); }

  const returnUrl        = body.return_url         as string | undefined;
  const refreshUrl       = body.refresh_url        as string | undefined;
  const companyAccountId = body.company_account_id as string | undefined;
  if (!returnUrl || !refreshUrl) {
    return json({ error: "return_url and refresh_url are required" }, 400);
  }

  try {
    const key = await getStripeSecretKey();

    /* 1. Create or reuse a connected account */
    let accountId = body.account_id as string | undefined;
    if (!accountId) {
      const params: string[][] = [
        ["type", "express"],
        ["capabilities[card_payments][requested]",  "true"],
        ["capabilities[transfers][requested]",       "true"]
      ];
      if (body.email) params.push(["email", body.email as string]);

      /* Attach metadata */
      const meta = body.metadata as Record<string, string> | undefined;
      if (meta) {
        Object.entries(meta).forEach(([k, v]) => params.push([`metadata[${k}]`, v]));
      }

      const acct  = await stripePost(key, "accounts", params);
      accountId   = acct.id as string;
    }

    /* 2. Create account link (onboarding URL) */
    const linkParams: string[][] = [
      ["account",     accountId],
      ["type",        "account_onboarding"],
      ["return_url",  returnUrl],
      ["refresh_url", refreshUrl]
    ];
    const link = await stripePost(key, "account_links", linkParams);

    /* 3. Persist to stripe_connect_accounts so the company's Payment nodes
          can auto-resolve their connected account without manual entry */
    if (companyAccountId) {
      await saveConnectAccount(companyAccountId, accountId, body.email as string | undefined);
    }

    return json({
      url        : link.url,
      account_id : accountId,
      expires_at : link.expires_at
    });

  } catch (e) {
    return json({ error: (e as Error).message }, 500);
  }
});

/*
  stripe-payment-status  —  Supabase Edge Function
  ─────────────────────────────────────────────────
  GET /functions/v1/stripe-payment-status?intent_id=pi_xxx

  Returns:
  {
    status      : "succeeded" | "requires_payment_method" | "processing" | ...
    amount      : number   (cents)
    currency    : string
    description : string
    metadata    : object
    created     : number   (unix timestamp)
  }

  Reads stripe_secret_key from platform_settings.
*/

const CORS: Record<string, string> = {
  "Access-Control-Allow-Origin" : "*",
  "Access-Control-Allow-Methods": "GET, OPTIONS",
  "Access-Control-Allow-Headers": "Content-Type, Authorization, apikey"
};

function json(data: unknown, status = 200): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { ...CORS, "Content-Type": "application/json" }
  });
}

let _cachedKey: string | null = null;

async function getStripeSecretKey(): Promise<string> {
  if (_cachedKey) return _cachedKey;
  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";
  if (!sbUrl || !serviceKey) throw new Error("Missing SUPABASE env vars");
  const r = await fetch(
    `${sbUrl}/rest/v1/platform_settings?id=eq.global&select=stripe_secret_key&limit=1`,
    { headers: { "apikey": serviceKey, "Authorization": "Bearer " + serviceKey } }
  );
  if (!r.ok) throw new Error(`platform_settings fetch failed (${r.status})`);
  const rows = await r.json() as { stripe_secret_key?: string }[];
  const key  = rows[0]?.stripe_secret_key ?? "";
  if (!key) throw new Error("stripe_secret_key not set in platform_settings");
  _cachedKey = key;
  return key;
}

Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method === "OPTIONS") return new Response(null, { status: 204, headers: CORS });

  const url      = new URL(req.url);
  const intentId = url.searchParams.get("intent_id") ?? "";

  if (!intentId) return json({ error: "intent_id query param required" }, 400);

  try {
    const key = await getStripeSecretKey();
    const r   = await fetch(`https://api.stripe.com/v1/payment_intents/${encodeURIComponent(intentId)}`, {
      headers: { "Authorization": "Bearer " + key }
    });
    const pi = await r.json() as Record<string, unknown>;
    if (!r.ok) {
      const errMsg = (pi.error as { message?: string } | undefined)?.message ?? "Stripe error";
      return json({ error: errMsg }, r.status);
    }
    return json({
      status      : pi.status,
      amount      : pi.amount,
      currency    : pi.currency,
      description : pi.description,
      metadata    : pi.metadata,
      created     : pi.created,
      intent_id   : pi.id
    });
  } catch (e) {
    return json({ error: (e as Error).message }, 500);
  }
});

/*
  stripe-webhook  —  Supabase Edge Function
  ──────────────────────────────────────────
  Receives Stripe webhook events and persists them to
  the stripe_payments table in Supabase.

  Setup steps:
  1. In Stripe Dashboard → Webhooks → Add endpoint:
     https://<project>.supabase.co/functions/v1/stripe-webhook
  2. Select events:
     payment_intent.succeeded
     payment_intent.payment_failed
     payment_intent.canceled
     account.updated
  3. Copy the Webhook Signing Secret (whsec_...) into
     platform_settings WHERE id='global', column: stripe_webhook_secret

  On each event, this function verifies the Stripe-Signature header
  using HMAC-SHA256, then upserts a row into the `stripe_payments` table.
*/

const CORS: Record<string, string> = {
  "Access-Control-Allow-Origin" : "*",
  "Access-Control-Allow-Methods": "POST, OPTIONS",
  "Access-Control-Allow-Headers": "Content-Type, Stripe-Signature"
};

function json(data: unknown, status = 200): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { ...CORS, "Content-Type": "application/json" }
  });
}

/* ── Read settings from platform_settings ── */
let _settings: { secret: string; webhookSecret: string; sbUrl: string; serviceKey: string } | null = null;

async function loadSettings(): Promise<{ secret: string; webhookSecret: string; sbUrl: string; serviceKey: string }> {
  if (_settings) return _settings;
  const sbUrl      = Deno.env.get("SUPABASE_URL")             ?? "";
  const serviceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "";
  if (!sbUrl || !serviceKey) throw new Error("Missing SUPABASE env vars");

  const r = await fetch(
    `${sbUrl}/rest/v1/platform_settings?id=eq.global&select=stripe_secret_key,stripe_webhook_secret&limit=1`,
    { headers: { "apikey": serviceKey, "Authorization": "Bearer " + serviceKey } }
  );
  if (!r.ok) throw new Error(`platform_settings fetch failed (${r.status})`);
  const rows = await r.json() as { stripe_secret_key?: string; stripe_webhook_secret?: string }[];
  const row  = rows[0] ?? {};
  _settings = {
    secret       : row.stripe_secret_key      ?? "",
    webhookSecret: row.stripe_webhook_secret   ?? "",
    sbUrl,
    serviceKey
  };
  return _settings;
}

/* ── Stripe webhook signature verification ── */
async function verifyStripeSignature(
  payload   : string,
  sigHeader : string,
  secret    : string
): Promise<boolean> {
  /* Stripe-Signature format: t=timestamp,v1=hash1,v1=hash2 */
  const parts     = sigHeader.split(",");
  const tPart     = parts.find(p => p.startsWith("t="));
  const v1Part    = parts.find(p => p.startsWith("v1="));
  if (!tPart || !v1Part) return false;

  const timestamp     = tPart.slice(2);
  const expectedSig   = v1Part.slice(3);
  const signedPayload = `${timestamp}.${payload}`;

  const enc     = new TextEncoder();
  const keyData = enc.encode(secret);
  const msgData = enc.encode(signedPayload);

  const cryptoKey = await crypto.subtle.importKey(
    "raw", keyData, { name: "HMAC", hash: "SHA-256" }, false, ["sign"]
  );
  const sigBuffer = await crypto.subtle.sign("HMAC", cryptoKey, msgData);
  const sigHex    = Array.from(new Uint8Array(sigBuffer))
    .map(b => b.toString(16).padStart(2, "0"))
    .join("");

  return sigHex === expectedSig;
}

/* ── Upsert payment row ── */
async function upsertPayment(
  sbUrl      : string,
  serviceKey : string,
  row        : Record<string, unknown>
): Promise<void> {
  const r = await fetch(`${sbUrl}/rest/v1/stripe_payments`, {
    method : "POST",
    headers: {
      "apikey"        : serviceKey,
      "Authorization" : "Bearer " + serviceKey,
      "Content-Type"  : "application/json",
      "Prefer"        : "resolution=merge-duplicates,return=minimal"
    },
    body: JSON.stringify(row)
  });
  if (!r.ok && r.status !== 201) {
    console.error("[stripe-webhook] upsert failed:", r.status, await r.text());
  }
}

/* ── Main handler ── */
Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method === "OPTIONS") return new Response(null, { status: 204, headers: CORS });
  if (req.method !== "POST")   return json({ error: "POST only" }, 405);

  /* Read raw body for signature verification */
  const rawBody   = await req.text();
  const sigHeader = req.headers.get("Stripe-Signature") ?? "";

  let cfg: { secret: string; webhookSecret: string; sbUrl: string; serviceKey: string };
  try { cfg = await loadSettings(); }
  catch (e) { return json({ error: (e as Error).message }, 500); }

  /* Verify signature (skip if no webhook secret configured — dev mode) */
  if (cfg.webhookSecret) {
    const valid = await verifyStripeSignature(rawBody, sigHeader, cfg.webhookSecret);
    if (!valid) {
      console.error("[stripe-webhook] Signature verification failed");
      return json({ error: "Invalid signature" }, 400);
    }
  } else {
    console.warn("[stripe-webhook] No webhook secret configured — skipping signature check");
  }

  let event: Record<string, unknown>;
  try { event = JSON.parse(rawBody); }
  catch (_) { return json({ error: "Invalid JSON" }, 400); }

  const eventType = event.type as string ?? "";
  const eventId   = event.id   as string ?? "";
  const dataObj   = (event.data as { object?: Record<string, unknown> })?.object ?? {};

  console.log(`[stripe-webhook] Event: ${eventType} (${eventId})`);

  /* ── Handle payment_intent events ── */
  if (
    eventType === "payment_intent.succeeded" ||
    eventType === "payment_intent.payment_failed" ||
    eventType === "payment_intent.canceled"
  ) {
    const pi = dataObj as {
      id?: string; amount?: number; currency?: string;
      status?: string; description?: string; metadata?: Record<string, string>;
    };

    /* event.account is present for Connect events (connected account acct_xxx) */
    const connectedAcct = (event.account as string | undefined) ?? null;

    await upsertPayment(cfg.sbUrl, cfg.serviceKey, {
      id         : eventId,
      intent_id  : pi.id         ?? "",
      amount     : pi.amount     ?? 0,
      currency   : pi.currency   ?? "usd",
      status     : pi.status     ?? eventType,
      description: pi.description ?? "",
      metadata   : pi.metadata   ?? {},
      account_id : connectedAcct,
      updated_at : new Date().toISOString()
    });
  }

  /* ── Handle account.updated ──
     Fired when a connected account's onboarding status changes
     (e.g. after they complete Stripe's hosted onboarding flow).
     Persists charges_enabled / details_submitted / status into
     stripe_connect_accounts so the company's Payment nodes can
     check whether the connected account is ready to accept charges. ── */
  if (eventType === "account.updated") {
    const acct = dataObj as {
      id?: string; charges_enabled?: boolean; payouts_enabled?: boolean;
      details_submitted?: boolean; email?: string;
    };
    console.log(`[stripe-webhook] Account ${acct.id} updated — charges: ${acct.charges_enabled}, payouts: ${acct.payouts_enabled}`);

    if (acct.id) {
      try {
        const status = acct.charges_enabled ? "active"
                     : acct.details_submitted ? "pending_review"
                     : "pending";

        const r = await fetch(
          `${cfg.sbUrl}/rest/v1/stripe_connect_accounts?stripe_account_id=eq.${encodeURIComponent(acct.id)}`,
          {
            method : "PATCH",
            headers: {
              "apikey"       : cfg.serviceKey,
              "Authorization": "Bearer " + cfg.serviceKey,
              "Content-Type" : "application/json",
              "Prefer"       : "return=minimal"
            },
            body: JSON.stringify({
              charges_enabled  : !!acct.charges_enabled,
              payouts_enabled  : !!acct.payouts_enabled,
              details_submitted: !!acct.details_submitted,
              status,
              updated_at: new Date().toISOString()
            })
          }
        );
        if (!r.ok) {
          console.error("[stripe-webhook] stripe_connect_accounts update failed:", r.status, await r.text());
        }
      } catch (e) {
        console.error("[stripe-webhook] account.updated handler error:", (e as Error).message);
      }
    }
  }

  return json({ received: true, event_type: eventType });
});