Data types — vì sao TIMESTAMPTZ thay TIMESTAMP cứu deploy multi-region

-- Int variants - chon dung de tranh overflow + tiet kiem storage
smallint        -- 2 byte, range -32768 den 32767 (counter nho, flag)
int / int4      -- 4 byte, range khoang -2.1B den 2.1B (default)
bigint / int8   -- 8 byte, range khoang -9.2 quintillion (PK lon, counter global)

-- SERIAL / BIGSERIAL = shorthand cho int/bigint + sequence + default
id BIGSERIAL PRIMARY KEY  -- tuong duong bigint + DEFAULT nextval(...)

-- Float KHONG exact - tranh cho money
real / float4        -- 4 byte float, KHONG exact
double precision / float8  -- 8 byte float, van KHONG exact

-- Minh hoa: float cong don
SELECT 0.1::real + 0.2::real;
-- Ket qua: 0.3 (may man round dung)
-- Nhung: SELECT 0.1::real + 0.2::real = 0.3 -> false (IEEE 754 comparison fail)

-- NUMERIC(p, s) - arbitrary precision, exact - dung cho tien
-- p = tong so chu so, s = so chu so sau dau thap phan
balance NUMERIC(15, 2)  -- max 9999999999999.99 (15 digit, 2 decimal)

-- So sanh: float vs numeric
SELECT 0.1::float8 + 0.2::float8;    -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric;  -- 0.3 (exact)

-- Pattern alternative: store cents as bigint (tranh float hoan toan)
balance_cents BIGINT NOT NULL DEFAULT 0
-- Store $10.50 -> 1050 cents
-- App code: display = balance_cents / 100.0
-- Trao doi: app phai convert, nhung zero float bug

-- 3 cach store string trong PG
text         -- variable length, no limit, recommend
varchar(n)   -- variable length voi cap n char
char(n)      -- FIXED length, padded space (legacy, KHONG dung)

-- Demo: TEXT va VARCHAR(n) performance giong nhau trong PG
-- (MySQL co the khac - la ly do duy nhat phai note khi migrate)

-- Pitfall: varchar(n) cap qua som
title VARCHAR(50)  -- spec ban dau OK

-- 6 thang sau marketing muon title 200 char
ALTER TABLE tasks ALTER COLUMN title TYPE VARCHAR(200);
-- PG 9.2+: khong rewrite neu tang len, nhung van phai hold ACCESS EXCLUSIVE lock
-- Tren bang lon: lock table trong thoi gian ALTER -> downtime

-- Better: TEXT + CHECK constraint khi can rang buoc business rule
title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200)
-- ALTER CHECK de hon ALTER TYPE: chi thay doi constraint, khong hold lock lau

-- 2 type chinh:
timestamp                   -- KHONG store TZ, "wall clock time" thuan tuy
timestamp with time zone    -- = timestamptz, normalize ve UTC, display per session TZ

-- Demo bug: TIMESTAMP no-TZ
SET TIME ZONE 'Asia/Ho_Chi_Minh';  -- UTC+7
INSERT INTO events(event_at) VALUES ('2026-05-04 14:00');
-- TIMESTAMP store: 2026-05-04 14:00 (con so tuong trung, khong co TZ context)

SET TIME ZONE 'America/New_York';  -- UTC-4 (EDT)
SELECT event_at FROM events;
-- TIMESTAMP tra ve: 2026-05-04 14:00 (KHONG convert, van giu nguyen con so)
-- -> User New York hieu la 14:00 EDT, sai hoan toan!

-- Fix: TIMESTAMPTZ
SET TIME ZONE 'Asia/Ho_Chi_Minh';
INSERT INTO events(event_at) VALUES ('2026-05-04 14:00');
-- TIMESTAMPTZ store: 2026-05-04 07:00 UTC (convert ve UTC khi insert)

SET TIME ZONE 'America/New_York';
SELECT event_at FROM events;
-- TIMESTAMPTZ tra ve: 2026-05-04 03:00 EDT (convert tu UTC sang session TZ)
-- -> Dung! Cung mot moment in time, khac nhau cach hien thi per timezone

-- Luu y: now() va current_timestamp tra ve TIMESTAMPTZ - an toan
SELECT now();              -- timestamptz, UTC
SELECT current_timestamp;  -- timestamptz, UTC

-- TRANH: localtimestamp tra ve TIMESTAMP (no TZ) - anti-pattern
SELECT localtimestamp;     -- timestamp, KHONG co TZ context

-- UUID: 16 byte, format 8-4-4-4-12 hex
-- PG 13+: gen_random_uuid() built-in (UUID v4, random)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
INSERT INTO users DEFAULT VALUES;
-- id: a1b2c3d4-e5f6-7890-abcd-ef1234567890

-- UUID v4 random: khong sequential -> B-tree index fragmentation
-- UUID v7 (timestamp-prefix): sequential -> tot hon cho index
-- Module 4 bai 5 cua khoa nay deep dive UUID v7 + ULID

-- ARRAY type
tags TEXT[]  -- vd: ARRAY['urgent', 'bug', 'backend']

-- Anti-pattern thuong: violate 1NF
-- Kho query: WHERE 'urgent' = ANY(tags) phai scan mang
-- Module 4 bai 3 cua khoa nay: normalize tags ra bang rieng

-- RANGE type (PG-specific, rat huu ich)
CREATE TABLE bookings (
  room_id INT,
  period TSTZRANGE
);
INSERT INTO bookings VALUES (1, '[2026-05-04 14:00+07, 2026-05-04 16:00+07)');
-- '[' = inclusive, ')' = exclusive (nhu interval toan hoc)

-- Query overlap: tim booking conflict
SELECT * FROM bookings
WHERE room_id = 1
  AND period && '[2026-05-04 15:00+07, 2026-05-04 17:00+07)'::tstzrange;
-- && = overlap operator, rat nhanh voi GiST index

-- BUG 1: TIMESTAMP (no TZ) -> multi-region conflict
-- Da demo o Section 5 - chi ghi lai de nho
due_at TIMESTAMP  -- KHONG dung
due_at TIMESTAMPTZ  -- DUNG

-- BUG 2: float cho money -> accumulated rounding error
balance REAL NOT NULL DEFAULT 0
-- Test: update 10 lan + 0.1
UPDATE accounts SET balance = balance + 0.1 WHERE id = 1;
-- Lap 10 lan
SELECT balance FROM accounts WHERE id = 1;
-- Ket qua: 0.9999999... (khong phai 1.0)
-- Fix: NUMERIC(15,2) hoac balance_cents BIGINT

-- BUG 3: varchar(n) cap qua nho - phat hien muon
title VARCHAR(50)      -- OK khi ra mat
-- 6 thang sau: user dang nhap title 120 char -> ERROR: value too long
-- ALTER TYPE tren bang lon -> lock, downtime
-- Fix: TEXT + CHECK, hoac VARCHAR(500) buffer lon tu dau

-- BUG 4: store numeric data as text
age TEXT              -- sai! khong the SUM/AVG, sort theo string ('9' > '10')
phone TEXT            -- OK - so dien thoai khong can so hoc
user_count TEXT       -- sai! '100' < '99' khi sort string

-- Explicit cast: an toan, ro rang
SELECT '123'::int;           -- 123
SELECT '2026-05-04'::date;   -- 2026-05-04
SELECT 'abc'::int;           -- ERROR: invalid input syntax for type integer

-- Implicit cast: PG tu dong khi type compatible
INSERT INTO tasks(due_at) VALUES ('2026-05-04');  -- text -> timestamptz auto
INSERT INTO tasks(due_at) VALUES ('not-a-date');  -- ERROR: invalid input

-- CHECK constraint - rang buoc linh hoat hon type-level limit
title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200)
-- Co the ALTER CHECK de dang:
ALTER TABLE tasks DROP CONSTRAINT tasks_title_check;
ALTER TABLE tasks ADD CONSTRAINT tasks_title_check
  CHECK (length(title) BETWEEN 1 AND 500);  -- tang len khong can lock lau

-- users.id BIGSERIAL PRIMARY KEY
--   BIGSERIAL = bigint + auto-increment sequence
--   Chon bigint thay int: tranh overflow khi user base > 2 ti (scale safety)
--   BIGSERIAL thay UUID: sequential -> B-tree index tot hon (M04.5 deep)

-- users.email TEXT UNIQUE NOT NULL
--   TEXT thay VARCHAR(n): email dai nhat thuc te ~254 char, TEXT khong can cap
--   UNIQUE constraint dam bao no duplicate, khong can rang buoc length

-- users.created_at TIMESTAMPTZ NOT NULL DEFAULT now()
--   TIMESTAMPTZ: multi-region safe, normalize UTC, display per session TZ
--   DEFAULT now(): server-side default, app khong can set

-- tasks.due_at TIMESTAMPTZ
--   Nullable: khong phai task nao cung co deadline (khac created_at)
--   TIMESTAMPTZ: dam bao user EU va user US thay cung moment in time

-- tasks.status TEXT CHECK (status IN ('todo', 'doing', 'done', 'archived'))
--   TEXT + CHECK thay vi CREATE TYPE ... AS ENUM:
--   Pro: de ALTER (them value chi can ADD CONSTRAINT moi)
--   Con: khong co type-safety o app layer, string compare (khong co enum ORM mapping)
--   Module 4 bai 2 cua khoa nay: ENUM type deep dive, khi nao chon ENUM vs TEXT+CHECK

-- tasks.title TEXT NOT NULL
--   TEXT: marketing co the doi spec length bat ky luc nao
--   Neu can rang buoc: them CHECK (length(title) <= 200) de alter sau