-- =============================================================================
-- MIGRATION: customer_store_memberships — the customer-side mirror of
-- store.store_staff
--
-- One global User identity, many stores: a person shopping at 3 stores is
-- one users row + 3 rows here, not 3 separate users rows. Informational
-- only, not an access gate (unlike store_staff, which genuinely restricts
-- merchant staff to their own stores) — a customer doesn't need permission
-- to shop anywhere; this just powers the admin app's "my stores" picker.
-- Rows are created automatically the first time someone logs in at a store
-- they haven't shopped at before (AuthService.ensureMembership), not
-- through a separate invite/join flow — same DDL conventions as
-- store.store_staff (001_platform_rbac_and_merchant.sql).
--
-- Run: idempotent — safe to re-execute.
-- =============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS customer.customer_store_memberships (
    id          VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text,
    "storeId"   VARCHAR(255) NOT NULL,
    "userId"    VARCHAR(255) NOT NULL,
    role        VARCHAR(50)  NOT NULL DEFAULT 'customer',
    "joinedAt"  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    "isActive"  BOOLEAN      NOT NULL DEFAULT TRUE,
    "sortOrder" INT          NOT NULL DEFAULT 0,
    "createdBy" VARCHAR(255) NOT NULL DEFAULT 'system',
    "createdOn" TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    "updatedBy" VARCHAR(255) NOT NULL DEFAULT 'system',
    "updatedOn" TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT pk_customer_store_memberships  PRIMARY KEY (id),
    CONSTRAINT uq_customer_store_membership   UNIQUE ("storeId", "userId"),
    CONSTRAINT fk_csm_store                   FOREIGN KEY ("storeId") REFERENCES store.stores(id) ON DELETE CASCADE,
    CONSTRAINT fk_csm_user                    FOREIGN KEY ("userId") REFERENCES admin.users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_csm_store ON customer.customer_store_memberships ("storeId");
CREATE INDEX IF NOT EXISTS idx_csm_user  ON customer.customer_store_memberships ("userId");

COMMENT ON TABLE customer.customer_store_memberships IS 'Customer-side mirror of store.store_staff — one global User, many stores. Informational only, not an access gate.';

COMMIT;

-- Verify
SELECT table_schema, table_name FROM information_schema.tables
WHERE table_schema = 'customer' AND table_name = 'customer_store_memberships';