-- 050_repair_store_owner_staff.sql
--
-- Restores admin access for stores whose owner has no active staff row.
--
-- `MerchantService.createStore()` used to create the creator's StoreStaff row
-- AFTER its transaction committed. Anything that threw in between left a store
-- that existed and was live, with nobody attached to it. The merchant then hit
-- "Admin access is required" on every store screen, because AdminAccessGuard
-- grants access on an active StoreStaff row and there wasn't one.
--
-- The code is fixed (the row is now written inside the transaction, and as an
-- upsert), but that does nothing for stores already in this state — hence this.
--
-- Two shapes are repaired:
--   1. a staff row exists but every one of them is inactive  → reactivate the owner's
--   2. no staff row at all                                   → create one
--
-- Idempotent, and deliberately conservative: it only ever attaches the store's
-- OWN creator, or the owning organisation's merchant user. It never invents a
-- relationship that was not already implied.

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. Reactivate a deactivated owner row
-- ─────────────────────────────────────────────────────────────────────────────

UPDATE store.store_staff st
   SET "isActive"  = TRUE,
       "deletedAt" = NULL,
       "updatedBy" = 'system:repair-050',
       "updatedOn" = now()
  FROM store.stores s
 WHERE st."storeId" = s."id"
   AND s."deletedAt" IS NULL
   AND st."userId" = s."createdBy"
   AND st."isActive" = FALSE
   -- Only when the store has NO active staff at all. A store whose owner was
   -- deliberately deactivated while other admins remain is a real decision,
   -- not damage, and must be left alone.
   AND NOT EXISTS (
     SELECT 1 FROM store.store_staff a
      WHERE a."storeId" = s."id" AND a."isActive" = TRUE
   );

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. Create the missing owner row
-- ─────────────────────────────────────────────────────────────────────────────
--
-- The owner is the store's `createdBy` when that is a real user; otherwise the
-- organisation's merchant/owner user. Seeded stores carry `createdBy='system'`,
-- which is why the fallback exists.

INSERT INTO store.store_staff
  ("id", "storeId", "userId", "organizationId", "role", "isActive", "joinedAt",
   "createdBy", "createdOn", "updatedBy", "updatedOn")
SELECT gen_random_uuid()::TEXT,
       s."id",
       owner."id",
       s."organizationId",
       'merchant',
       TRUE,
       now(),
       'system:repair-050', now(), 'system:repair-050', now()
  FROM store.stores s
  CROSS JOIN LATERAL (
    SELECT u."id"
      FROM admin.users u
     WHERE u."id" = s."createdBy"
     UNION ALL
    SELECT u."id"
      FROM admin.users u
     WHERE u."organizationId" = s."organizationId"
       AND u."role" IN ('merchant', 'store_owner')
     ORDER BY 1
     LIMIT 1
  ) AS owner
 WHERE s."deletedAt" IS NULL
   AND NOT EXISTS (
     SELECT 1 FROM store.store_staff a
      WHERE a."storeId" = s."id" AND a."isActive" = TRUE
   )
ON CONFLICT ("storeId", "userId") DO UPDATE
  SET "isActive"  = TRUE,
      "deletedAt" = NULL,
      "updatedBy" = 'system:repair-050',
      "updatedOn" = now();

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. Report what is still unattached
-- ─────────────────────────────────────────────────────────────────────────────
--
-- A store left here has no candidate owner at all — its creator is gone and its
-- organisation has no merchant user. That needs a human, not a migration.

DO $$
DECLARE remaining INT;
BEGIN
  SELECT count(1) INTO remaining
    FROM store.stores s
   WHERE s."deletedAt" IS NULL
     AND NOT EXISTS (
       SELECT 1 FROM store.store_staff a
        WHERE a."storeId" = s."id" AND a."isActive" = TRUE
     );

  IF remaining > 0 THEN
    RAISE NOTICE 'repair-050: % store(s) still have no active staff and no resolvable owner — assign one by hand', remaining;
  ELSE
    RAISE NOTICE 'repair-050: every live store now has an active staff row';
  END IF;
END $$;

COMMIT;