-- 0001_init.sql — SD-0001 §6.3 data model. Forward-only and fail-stop (INV-7):
-- do NOT edit this file once it has merged/applied; add a new numbered migration.

-- account — a person's identity on the platform. Email is the canonical key (INV-2);
-- the address is stored already-normalized (lowercased) by the service layer, so a
-- plain unique index enforces one-account-per-email. Data minimization (OHM): email
-- and nothing else (§6.3).
CREATE TABLE account (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX account_email_key ON account (email);  -- INV-2

-- auth_code — one-time codes, handled like secrets (INV-3): only the hash is stored;
-- keyed by email (the code is issued before the account may exist — sign-up and
-- log-in converge, PUC-3).
CREATE TABLE auth_code (
    id           BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        TEXT         NOT NULL,
    code_hash    TEXT         NOT NULL,
    expires_at   TIMESTAMPTZ  NOT NULL,
    attempts     INTEGER      NOT NULL DEFAULT 0,
    consumed_at  TIMESTAMPTZ,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);
CREATE INDEX auth_code_email_idx ON auth_code (email);

-- storefront — a merchant's business presence. Name is always populated (a blank
-- entry gets a generated default at the service layer, §6.3); "unnamed" is never NULL.
CREATE TABLE storefront (
    id          BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT         NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- storefront_membership — account <-> storefront ownership. INV-4: deliberately a
-- many-to-many shape (composite PK, no UNIQUE(account_id)) so many-storefronts-per-
-- account stays open; the one-storefront rule is a deletable service-layer guard, not
-- a schema law. INV-5: every storefront-scoped row carries its storefront_id. `role`
-- is a single-value enum today; it is the seam the staff/permissions model attaches to.
CREATE TABLE storefront_membership (
    account_id     BIGINT       NOT NULL REFERENCES account (id),
    storefront_id  BIGINT       NOT NULL REFERENCES storefront (id),
    role           TEXT         NOT NULL DEFAULT 'owner' CHECK (role IN ('owner')),
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT now(),
    PRIMARY KEY (account_id, storefront_id)
);