-- Created on savesnippets.com · https://savesnippets.com/6dHzTRhymDEcXw
CREATE TABLE users (
    id           BIGSERIAL PRIMARY KEY,
    email        TEXT          NOT NULL UNIQUE,
    name         TEXT          NOT NULL,
    age          INTEGER       CHECK (age IS NULL OR age >= 0),
    status       TEXT          NOT NULL DEFAULT 'active'
                               CHECK (status IN ('active', 'archived', 'banned')),
    created_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT        NOT NULL,
    amount      NUMERIC(10,2) NOT NULL CHECK (amount > 0),
    currency    CHAR(3)       NOT NULL DEFAULT 'USD',
    status      TEXT          NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,      -- delete orders when user is deleted

    -- Composite UNIQUE — at most one "primary" order per user
    CONSTRAINT uq_user_primary UNIQUE (user_id, status)
        DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);