init.sql

-- ============================================
-- Baasid 資料庫初始化腳本
-- Database: PostgreSQL
-- ============================================

-- 啟用 UUID 擴充（PostgreSQL 13+ 內建 gen_random_uuid()）
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================
-- 建立資料庫（若尚未建立，請先手動執行）
-- CREATE DATABASE baasid;
-- ============================================

-- ============================================
-- DROP TABLES（如需重建）
-- ============================================
DROP TABLE IF EXISTS goods;
DROP TABLE IF EXISTS "system_user";

-- ============================================
-- CREATE TABLE: system_user
-- ============================================
CREATE TABLE "system_user" (
    _id       UUID         NOT NULL DEFAULT gen_random_uuid(),
    account   VARCHAR(128) NOT NULL,
    password  VARCHAR(128),
    name      VARCHAR(128),
    CONSTRAINT pk_system_user PRIMARY KEY (_id),
    CONSTRAINT uk_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 '名稱';

-- ============================================
-- CREATE TABLE: goods
-- ============================================
CREATE TABLE goods (
    _id          UUID          NOT NULL DEFAULT gen_random_uuid(),
    name         VARCHAR(128)  NOT NULL,
    cr_user      UUID          NOT NULL,
    cr_datetime  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    up_user      UUID          NOT NULL,
    up_datetime  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_goods PRIMARY KEY (_id),
    CONSTRAINT fk_goods_cr_user FOREIGN KEY (cr_user) REFERENCES "system_user"(_id),
    CONSTRAINT fk_goods_up_user FOREIGN KEY (up_user) REFERENCES "system_user"(_id)
);

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.cr_datetime IS '創建時間';
COMMENT ON COLUMN goods.up_user     IS '更新者（system_user._id 外鍵）';
COMMENT ON COLUMN goods.up_datetime IS '更新時間';

-- ============================================
-- INSERT: 測試資料
-- 密碼皆為 "password123"（BCrypt 雜湊）
-- 若透過 DataInitializer 啟動則此段可跳過
-- ============================================

-- BCrypt hash of "password123": $2a$10$dXJ3SW6G7P50lGmMQoeGb.U7fN1tzR6FReaSqOMhcOFsNG5Vb5bCi
INSERT INTO "system_user" (_id, account, password, name) VALUES
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'admin',  '$2a$10$dXJ3SW6G7P50lGmMQoeGb.U7fN1tzR6FReaSqOMhcOFsNG5Vb5bCi', '管理員'),
    ('b1ffcd00-0d1c-5ff9-cc7e-7cc0ce491b22', 'user01', '$2a$10$dXJ3SW6G7P50lGmMQoeGb.U7fN1tzR6FReaSqOMhcOFsNG5Vb5bCi', '測試用戶');

INSERT INTO goods (_id, name, cr_user, cr_datetime, up_user, up_datetime) VALUES
    ('c2aade11-1e2d-6af0-ad8f-8dd1df502c33', '蘋果', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', CURRENT_TIMESTAMP, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', CURRENT_TIMESTAMP),
    ('d3bbef22-2f3e-7bf1-ae9a-9ee2ea613d44', '香蕉', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', CURRENT_TIMESTAMP, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', CURRENT_TIMESTAMP),
    ('e4ccfa33-3a4f-8cf2-bfab-aff3fb724e55', '鍵盤', 'b1ffcd00-0d1c-5ff9-cc7e-7cc0ce491b22', CURRENT_TIMESTAMP, 'b1ffcd00-0d1c-5ff9-cc7e-7cc0ce491b22', CURRENT_TIMESTAMP);