Constraints — DB last line of defense, không trust app validation

-- 1. NOT NULL: column khong duoc NULL
email TEXT NOT NULL

-- 2. UNIQUE: khong duplicate value trong column
email TEXT UNIQUE NOT NULL
-- Multi-column UNIQUE: cap (user_id, project_id) phai duy nhat
UNIQUE (user_id, project_id)

-- 3. PRIMARY KEY: NOT NULL + UNIQUE + tao B-tree index mac dinh
id BIGSERIAL PRIMARY KEY
-- Composite PK cho junction table M:N
PRIMARY KEY (user_id, project_id)

-- 4. FOREIGN KEY: tham chieu den PK bang khac
project_id BIGINT NOT NULL REFERENCES projects(id)
-- Voi cascading (giai thich o Section 5)
project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE

-- 5. CHECK: dieu kien bat buoc per-row
status TEXT NOT NULL CHECK (status IN ('todo', 'doing', 'done'))
salary NUMERIC NOT NULL CHECK (salary >= 0)

-- Can extension btree_gist de dung GIST index voi kieu du lieu scalar
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id      BIGSERIAL PRIMARY KEY,
  room_id INT NOT NULL,
  period  TSTZRANGE NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
-- room_id WITH =  : cung room_id...
-- period WITH &&  : ...khong cho phep period overlap (&&: range overlap operator)

INSERT INTO bookings(room_id, period)
VALUES (1, '[2026-05-04 14:00, 2026-05-04 16:00)');
-- OK

INSERT INTO bookings(room_id, period)
VALUES (1, '[2026-05-04 15:00, 2026-05-04 17:00)');
-- ERROR: conflicting key value violates exclusion constraint "bookings_room_id_period_excl"

CREATE TABLE companies (
  id     BIGSERIAL PRIMARY KEY,
  ceo_id BIGINT REFERENCES employees(id) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE employees (
  id         BIGSERIAL PRIMARY KEY,
  company_id BIGINT NOT NULL REFERENCES companies(id) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
  -- Insert company truoc, ceo_id tro den employee chua ton tai
  -- OK vi constraint deferred den COMMIT
  INSERT INTO companies(id, ceo_id) VALUES (1, 1);

  -- Insert employee, company_id tro den company vua insert
  INSERT INTO employees(id, company_id) VALUES (1, 1);

  -- Ca hai check tai COMMIT -- luc nay ca hai row da co
COMMIT;

-- 4 option ON DELETE
ON DELETE NO ACTION   -- mac dinh: block delete neu co row con reference
ON DELETE RESTRICT    -- giong NO ACTION nhung check ngay, khong defer duoc
ON DELETE CASCADE     -- xoa row con theo khi xoa row cha
ON DELETE SET NULL    -- set column FK thanh NULL (column phai nullable)
ON DELETE SET DEFAULT -- set column FK ve gia tri DEFAULT

-- TaskFlow: comments la weak entity, ton tai phu thuoc task
CREATE TABLE comments (
  id      BIGSERIAL PRIMARY KEY,
  task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id BIGINT       REFERENCES users(id)    ON DELETE SET NULL,
  body    TEXT NOT NULL
);
-- Xoa task -> tu dong xoa comment (weak entity, khong co y nghia doc lap)
-- Xoa user -> set comment.user_id NULL (preserve comment, lose author link)

// SAI: race condition giua check va insert
const existing = await db.query(
  'SELECT id FROM users WHERE email = $1',
  [email]
);
if (existing.rows.length > 0) {
  throw new Error('Email already exists');
}
// Khoang trong o day: 2 request cung pass check, ca 2 tien den INSERT
await db.query('INSERT INTO users(email) VALUES ($1)', [email]);

try {
  await db.query('INSERT INTO users(email) VALUES ($1)', [email]);
} catch (e) {
  if (e.code === '23505') {  // PG unique_violation error code
    throw new Error('Email already exists');
  }
  throw e;  // re-throw loi khac
}

-- OK: check don gian, immutable, per-row
CHECK (age >= 0 AND age <= 150)
CHECK (status IN ('todo', 'doing', 'done'))
CHECK (start_at < end_at)

-- KHONG OK: check cross-row (phai dung trigger hoac app logic)
-- "User chi tao toi da 5 project" -- khong the dung CHECK
-- CHECK chi nhin thay row hien tai, khong nhin thay row khac

-- NGUY HIEM: check goi function khong immutable
CHECK (verified_at <= now())
-- now() thay doi theo thoi gian -- PostgreSQL se reject CHECK nay
-- hoac neu accept, se fail random tuy thoi diem query

-- users.email TEXT UNIQUE NOT NULL
--   App validate unique? Race-prone. DB UNIQUE enforce: safe.
--   Catch error code 23505 de tra user-facing message.

-- project_members.role TEXT NOT NULL CHECK (role IN ('owner','member','viewer'))
--   Enum-like validation bang CHECK. Alternative: CREATE TYPE role_enum AS ENUM(...)
--   Tradeoff ENUM: type-safe + doc ro rang, nhung ALTER TYPE ADD VALUE can migration
--   CHECK linh hoat hon cho value set co the thay doi.

-- tasks.project_id BIGINT NOT NULL REFERENCES projects(id)
--   Mandatory parent: task khong the ton tai khong co project.
--   Khong ON DELETE CASCADE -- xoa project KHONG tu xoa task.
--   Default RESTRICT: phai cleanup task truoc khi xoa project.
--   Design choice: preserve task history, require explicit cleanup.

-- tasks.assignee_id BIGINT REFERENCES users(id)
--   Nullable: task chua assign la hop le.
--   Khong ON DELETE CASCADE: xoa user KHONG xoa task.
--   Nen them ON DELETE SET NULL? Design choice -- task con gia tri
--   doc lap voi assignee, set NULL preserve task, lose assignment.

-- comments.task_id BIGINT NOT NULL REFERENCES tasks(id)
--   Hien tai: khong co ON DELETE CASCADE trong schema goc.
--   Nen them? Comment la weak entity -- xoa task nen xoa comment.
--   Nhung neu can preserve comment history (audit)? Thi RESTRICT + soft delete.
--   Module 11 cua khoa nay (production migration) se chi cach them
--   constraint nay an toan cho bang co du lieu.