schema.sql

-- =============================================================
-- Section 17 — Functions, Stored Procedures, and Triggers
-- =============================================================

DROP SCHEMA IF EXISTS adv_funcs CASCADE;
CREATE SCHEMA adv_funcs;
SET search_path TO adv_funcs;

CREATE TABLE account (
    id         SERIAL PRIMARY KEY,
    holder     VARCHAR(100) NOT NULL,
    balance    INTEGER      NOT NULL CHECK (balance >= 0)
);

CREATE TABLE product (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price_cents INTEGER      NOT NULL,
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Audit log populated by a trigger in the exercises
CREATE TABLE product_audit (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER      NOT NULL,
    changed_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    action          CHAR(1)      NOT NULL,  -- I / U / D
    old_price_cents INTEGER,
    new_price_cents INTEGER
);