-- Customer accounts: one balance row per (user, kind, currency) plus an
-- append-only entry ledger. Monetary kinds carry a currency; points do not.
-- The CHECK plus the two partial unique indexes keep those pairs honest and
-- prevent duplicates (a plain UNIQUE would let NULL currencies repeat).

CREATE TYPE customer_account_kind AS ENUM ('available', 'frozen', 'points');

CREATE TABLE customer_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    kind customer_account_kind NOT NULL,
    currency CHAR(3) REFERENCES currencies (code),
    balance_minor BIGINT NOT NULL DEFAULT 0 CHECK (balance_minor >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT customer_accounts_kind_currency CHECK (
        (kind = 'points' AND currency IS NULL)
        OR (kind <> 'points' AND currency IS NOT NULL)
    )
);

CREATE UNIQUE INDEX customer_accounts_monetary_idx
    ON customer_accounts (user_id, kind, currency)
    WHERE currency IS NOT NULL;

CREATE UNIQUE INDEX customer_accounts_points_idx
    ON customer_accounts (user_id, kind)
    WHERE currency IS NULL;

CREATE TABLE customer_account_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL REFERENCES customer_accounts (id) ON DELETE CASCADE,
    delta_minor BIGINT NOT NULL CHECK (delta_minor <> 0),
    balance_minor BIGINT NOT NULL CHECK (balance_minor >= 0),
    reason TEXT NOT NULL,
    reference_type TEXT,
    reference_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX customer_account_entries_account_idx
    ON customer_account_entries (account_id, created_at DESC);

-- Backfill zero rows for users that already exist; new registrations create
-- their own rows in the same transaction as the user insert.
INSERT INTO customer_accounts (user_id, kind)
SELECT id, 'points' FROM users
ON CONFLICT DO NOTHING;

INSERT INTO customer_accounts (user_id, kind, currency)
SELECT u.id, k.kind, c.code
  FROM users u
  CROSS JOIN (VALUES ('available'::customer_account_kind),
                     ('frozen'::customer_account_kind)) AS k (kind)
  CROSS JOIN (SELECT code FROM currencies WHERE is_base LIMIT 1) AS c
ON CONFLICT DO NOTHING;