create_tables.sql

-- Baasid v1.1 — PostgreSQL DDL (create tables + constraints)
--
-- Execute as a superuser or database owner, e.g.:
--   psql "postgres://user:pass@localhost:5432/baasid" -f scripts/create_tables.sql
--
-- UUID primary keys use PostgreSQL's built-in gen_random_uuid() (PG 13+).

-- Clean slate for idempotent local dev (comment out in production migrations).
DROP TABLE IF EXISTS goods CASCADE;
DROP TABLE IF EXISTS system_user CASCADE;

-- system_user: 登入帳號表
CREATE TABLE system_user (
    "_id"       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account     varchar(128) NOT NULL,
    password    varchar(128) NOT NULL,
    name        varchar(128),
    CONSTRAINT uq_system_user_account UNIQUE (account)
);

COMMENT ON TABLE system_user IS '系統使用者（登入帳號）';
COMMENT ON COLUMN system_user."_id" IS '主鍵 UUID';
COMMENT ON COLUMN system_user.account IS '登入帳號（唯一）';
COMMENT ON COLUMN system_user.password IS '密碼（建議存 bcrypt 雜湊）';
COMMENT ON COLUMN system_user.name IS '使用者顯示名稱';

-- goods: 商品表
CREATE TABLE goods (
    "_id"         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name          varchar(128) NOT NULL,
    cr_user       uuid NOT NULL REFERENCES system_user ("_id"),
    cr_datetime   timestamptz NOT NULL DEFAULT now(),
    up_user       uuid NOT NULL REFERENCES system_user ("_id"),
    up_datetime   timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE goods IS '商品主檔';
COMMENT ON COLUMN goods."_id" IS '商品代號 UUID';
COMMENT ON COLUMN goods.name IS '商品名稱';
COMMENT ON COLUMN goods.cr_user IS '建立者 → system_user._id';
COMMENT ON COLUMN goods.up_user IS '最後更新者 → system_user._id';

CREATE INDEX idx_goods_cr_datetime ON goods (cr_datetime);