-- =============================================================================
-- MIGRATION: backfill customer_store_memberships for pre-existing customers
--
-- 027 added customer.customer_store_memberships, but AuthService only
-- creates a row when someone actually logs in/registers/verifies after that
-- migration shipped. Every customer who already existed (and is still
-- signed in with a still-valid JWT) would otherwise land on the
-- sp-account.forgestack.in "which store" picker with zero memberships and
-- no way back to the login form (they're technically authenticated) — a
-- dead end. This is the one-time catch-up: give every already-verified
-- customer a membership at every store they actually have order/cart
-- history at, falling back to the default store only if they have none.
--
-- Run: idempotent — safe to re-execute (ON CONFLICT DO NOTHING on the same
-- unique constraint 027 created).
-- =============================================================================

BEGIN;

-- Backfill from real history: one membership per store a customer has
-- actually placed an order or opened a cart at.
INSERT INTO customer.customer_store_memberships
  (id, "storeId", "userId", role, "joinedAt", "isActive", "sortOrder", "createdBy", "createdOn", "updatedBy", "updatedOn")
SELECT
  gen_random_uuid()::text, history."storeId", u.id, 'customer', NOW(), TRUE, 0,
  'system:backfill-028', NOW(), 'system:backfill-028', NOW()
FROM admin.users u
JOIN LATERAL (
  SELECT "storeId" FROM sales.orders WHERE "customerId" = u.id
  UNION
  SELECT "storeId" FROM cart.carts WHERE "customerId" = u.id
) AS history ON TRUE
WHERE u.role = 'customer' AND u."emailVerifiedAt" IS NOT NULL
ON CONFLICT ("storeId", "userId") DO NOTHING;

-- Anyone still left with zero memberships (verified, but no order/cart
-- history at all) gets the default store, so the picker never dead-ends.
INSERT INTO customer.customer_store_memberships
  (id, "storeId", "userId", role, "joinedAt", "isActive", "sortOrder", "createdBy", "createdOn", "updatedBy", "updatedOn")
SELECT
  gen_random_uuid()::text, 'store_demo', u.id, 'customer', NOW(), TRUE, 0,
  'system:backfill-028-fallback', NOW(), 'system:backfill-028-fallback', NOW()
FROM admin.users u
WHERE u.role = 'customer' AND u."emailVerifiedAt" IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM customer.customer_store_memberships m WHERE m."userId" = u.id
  )
ON CONFLICT ("storeId", "userId") DO NOTHING;

COMMIT;

-- Verify
SELECT u.email, COUNT(m.id) AS memberships
FROM admin.users u
LEFT JOIN customer.customer_store_memberships m ON m."userId" = u.id
WHERE u.role = 'customer' AND u."emailVerifiedAt" IS NOT NULL
GROUP BY u.email
ORDER BY memberships, u.email;