Data types — vì sao TIMESTAMP có timezone cứu deploy multi-region

-- Cac kieu so nguyen chuan SQL - chon dung de tranh overflow + tiet kiem storage
SMALLINT     -- thuong 2 byte, range nho (counter nho, flag)
INTEGER      -- thuong 4 byte, range khoang -2.1 ti den 2.1 ti (default pho bien)
BIGINT       -- thuong 8 byte, range rat lon (PK lon, counter global)

-- Float KHONG exact - tranh cho money
REAL                -- float chinh xac thap, KHONG exact
DOUBLE PRECISION    -- float chinh xac cao hon, van KHONG exact

-- Minh hoa: float khong bieu dien chinh xac phan so thap phan
-- 0.1 + 0.2 trong float = 0.30000000000000004 (IEEE 754)
SELECT CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION);
-- Ket qua: 0.30000000000000004 -- so sanh = 0.3 se fail

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

-- So sanh: float vs decimal
SELECT CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION);  -- 0.30000000000000004
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));        -- 0.30 (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

-- Cac kieu chuoi chuan SQL
VARCHAR(n)   -- variable length voi gioi han n ky tu (pho bien nhat)
CHAR(n)      -- FIXED length, padded space (chi dung khi do dai that su co dinh)

-- 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);
-- Tuy engine: co the rewrite bang hoac chi doi metadata
-- Tren bang lon: nhieu engine phai hold lock trong thoi gian ALTER -> downtime

-- Better: cap rong rai hon + CHECK constraint cho business rule
title VARCHAR(500) NOT NULL CHECK (CHAR_LENGTH(title) BETWEEN 1 AND 200)
-- ALTER CHECK de hon ALTER TYPE: chi thay doi constraint, khong rewrite du lieu

-- 2 type thoi diem chinh trong chuan SQL:
TIMESTAMP                      -- "wall clock time" thuan tuy, KHONG luu timezone
TIMESTAMP WITH TIME ZONE       -- gan timezone, cho phep convert dung giua cac mui gio

-- Demo bug: TIMESTAMP khong co TZ
-- Server EU (UTC+2) insert:
INSERT INTO events(event_at) VALUES (TIMESTAMP '2026-05-04 14:00');
-- Store: 2026-05-04 14:00 (con so tuong trung, khong co TZ context)

-- User US (UTC-4) doc cung row:
SELECT event_at FROM events;
-- Tra ve: 2026-05-04 14:00 (KHONG convert, van giu nguyen con so)
-- -> User US hieu la 14:00 gio dia phuong cua ho, lech 6 tieng so voi y dinh!

-- Fix: TIMESTAMP WITH TIME ZONE
-- Server EU (UTC+2) insert:
INSERT INTO events(event_at)
VALUES (TIMESTAMP WITH TIME ZONE '2026-05-04 14:00+02:00');
-- Store chuan hoa ve thoi diem tuyet doi (tuong duong 12:00 UTC)

-- User US (UTC-4) doc voi session timezone cua ho:
SELECT event_at FROM events;
-- Tra ve: 2026-05-04 08:00-04:00 (cung mot thoi diem, hien thi theo mui gio US)
-- -> Dung! Cung mot moment in time, khac nhau cach hien thi per timezone

-- UUID: 128-bit, format 8-4-4-4-12 hex, vd:
-- a1b2c3d4-e5f6-7890-abcd-ef1234567890

-- Cach sinh UUID la DIALECT:
--   Mot so engine: ham gen_random_uuid()
--   Mot so engine: ham UUID()
--   ULID la dinh dang/thu vien ngoai (sortable theo thoi gian), thuong sinh o app layer

-- Kieu luu cung khac nhau: engine co kieu UUID native;
-- engine khac luu nhu CHAR(36) hoac BINARY(16)

-- Khai niem: mot column luu document JSON (cu phap minh hoa, dialect khac nhau)
-- settings JSON
-- Query path trong JSON: moi engine mot cu phap rieng
-- Index tren field JSON: co/khong va cach lam tuy engine

-- ARRAY column (dialect - khong phai moi engine ho tro)
-- tags VARCHAR[]  -- vd: ['urgent', 'bug', 'backend']

-- Anti-pattern thuong: vi pham 1NF
-- Kho query, kho index, kho aggregate
-- Bai 03 cua module nay: normalize tags ra bang rieng

-- BUG 1: TIMESTAMP (no TZ) -> multi-region conflict
-- Da demo o Section 5 - chi ghi lai de nho
due_at TIMESTAMP                  -- KHONG dung cho du lieu cross-region
due_at TIMESTAMP WITH TIME ZONE   -- DUNG

-- BUG 2: float cho money -> accumulated rounding error
balance REAL NOT NULL DEFAULT 0
-- Cong don nhieu lan + 0.1 -> ket qua 0.9999999... (khong phai 1.0)
-- Fix: DECIMAL(15,2) hoac balance_cents BIGINT

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

-- BUG 4: luu so duoi dang text
age VARCHAR(10)       -- sai! khong the SUM/AVG, sort theo string ('9' > '10')
phone VARCHAR(20)     -- OK - so dien thoai khong can so hoc, co the co dau '+'
user_count VARCHAR(10) -- sai! '100' < '99' khi sort string

-- Explicit cast chuan SQL: an toan, ro rang (dung CAST, khong dung cu phap :: rieng engine)
SELECT CAST('123' AS INTEGER);        -- 123
SELECT CAST('2026-05-04' AS DATE);    -- 2026-05-04
SELECT CAST('abc' AS INTEGER);        -- ERROR: invalid input

-- CHECK constraint - rang buoc linh hoat hon type-level limit
title VARCHAR(500) NOT NULL CHECK (CHAR_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 (CHAR_LENGTH(title) BETWEEN 1 AND 500);  -- tang gioi han, khong rewrite du lieu

-- users.id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
--   Chon BIGINT thay INTEGER: tranh overflow khi user base vuot 2 ti (scale safety)
--   IDENTITY thay UUID random: sequential -> index co thu tu tot hon (xem bai 05)

-- users.email VARCHAR(254) UNIQUE NOT NULL
--   254 = do dai email toi da theo RFC; cap dung mot lan, on dinh
--   UNIQUE constraint dam bao khong duplicate

-- users.created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
--   Tour TaskFlow dung TIMESTAMP (keyword pho quat); nen nang len
--   TIMESTAMP WITH TIME ZONE cho he da vung (xem muc 5) -> hien thi dung per session tz
--   DEFAULT CURRENT_TIMESTAMP: server-side default, app khong can set

-- tasks.due_at TIMESTAMP
--   Nullable: khong phai task nao cung co deadline (khac created_at)
--   Da vung -> nang len TIMESTAMP WITH TIME ZONE de user EU va US thay cung moment

-- tasks.status VARCHAR(20) CHECK (status IN ('todo', 'doing', 'done', 'archived'))
--   VARCHAR + CHECK: de ALTER danh sach gia tri (them value chi can ADD CONSTRAINT moi)
--   Alternative la kieu ENUM cua engine (dialect): type-safe hon nhung kho alter
--   Bai 02 cua module nay: rationale CHECK vs domain/enum

-- tasks.title VARCHAR(500) NOT NULL CHECK (CHAR_LENGTH(title) <= 200)
--   Cap rong + CHECK: marketing co the doi gioi han length ma chi alter CHECK