UPSERT — INSERT ... ON CONFLICT idempotent

-- Setup: them external_id de track source cua comment
ALTER TABLE comments ADD COLUMN external_id TEXT;
ALTER TABLE comments ADD CONSTRAINT uq_comment_external
  UNIQUE (task_id, external_id);

-- Webhook delivery lan 1:
INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001');
-- OK, comment_id = 100

-- Webhook delivery lan 2 (network timeout, client retry):
INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001');
-- ERROR: duplicate key value violates unique constraint "uq_comment_external"
-- Detail: Key (task_id, external_id)=(42, webhook_001) already exists.

INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT (task_id, external_id) DO NOTHING;
-- Lan 1: insert thanh cong, 1 row affected
-- Lan 2: conflict phat hien, skip, 0 row affected -- khong error

INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Updated body', 'webhook_001')
ON CONFLICT (task_id, external_id) DO UPDATE
SET body       = EXCLUDED.body,
    updated_at = now()
WHERE comments.body IS DISTINCT FROM EXCLUDED.body;
-- Chi update khi body thuc su thay doi (tranh write khong can thiet)

-- Single column UNIQUE hoac PRIMARY KEY
INSERT INTO tasks (id, title, status)
VALUES (1, 'New title', 'todo')
ON CONFLICT (id) DO UPDATE
SET title  = EXCLUDED.title,
    status = EXCLUDED.status,
    updated_at = now();

-- Composite UNIQUE (task_id, external_id)
INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT (task_id, external_id) DO UPDATE
SET body = EXCLUDED.body;

-- By constraint name -- ro rang hon, chong sai ten column
INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT ON CONSTRAINT uq_comment_external DO UPDATE
SET body = EXCLUDED.body;

-- Partial index -- phai match chinh xac WHERE clause cua partial index
-- Vi du: CREATE UNIQUE INDEX uq_email_active ON users(email) WHERE deleted_at IS NULL;
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE
SET name = EXCLUDED.name;

-- Full UPSERT + RETURNING
INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT (task_id, external_id) DO UPDATE
SET body = EXCLUDED.body, updated_at = now()
RETURNING id, (xmax = 0) AS was_inserted;
-- xmax = 0  -> row moi (INSERT): tuple chua tung bi update
-- xmax != 0 -> row da ton tai (UPDATE): xmax duoc set boi transaction UPDATE

-- Neu co created_at va updated_at:
INSERT INTO comments (task_id, user_id, body, external_id, created_at, updated_at)
VALUES (42, 5, 'Hello', 'webhook_001', now(), now())
ON CONFLICT (task_id, external_id) DO UPDATE
SET body = EXCLUDED.body, updated_at = now()
RETURNING id, (created_at = updated_at) AS was_inserted;
-- created_at = updated_at -> INSERT (vua tao, chua update)
-- created_at != updated_at -> UPDATE

INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT (task_id, external_id) DO NOTHING
RETURNING id;
-- Lan 1 (insert): RETURNING tra ve {id: 100}
-- Lan 2 (skip):   RETURNING tra ve 0 row (empty result set)

-- App phai handle: neu RETURNING empty -> SELECT lai theo unique key
SELECT id FROM comments
WHERE task_id = 42 AND external_id = 'webhook_001';

INSERT INTO comments (task_id, user_id, body, external_id)
VALUES (42, 5, 'Hello', 'webhook_001')
ON CONFLICT (task_id, external_id) DO UPDATE
SET external_id = EXCLUDED.external_id  -- no-op: ghi lai gia tri cu
RETURNING id;
-- Luon co RETURNING, du insert hay "skip" (thuc chat la update no-op)

-- 1) Idempotent webhook handler
--    "Neu chua xu ly -> insert, return id de trigger downstream"
--    "Neu da xu ly  -> skip, RETURNING empty -> app skip downstream"
INSERT INTO events (external_id, payload, received_at)
VALUES ($1, $2::jsonb, now())
ON CONFLICT (external_id) DO NOTHING
RETURNING id;
-- Neu RETURNING co row -> xu ly lan dau -> trigger downstream logic
-- Neu RETURNING trong  -> da xu ly truoc -> no-op, safe to retry


-- 2) Counter increment atomic (khong can transaction rieng)
CREATE TABLE page_views (
  page       TEXT PRIMARY KEY,
  view_count BIGINT NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO page_views (page, view_count, updated_at)
VALUES ('/dashboard', 1, now())
ON CONFLICT (page) DO UPDATE
SET view_count = page_views.view_count + 1,
    updated_at = now();
-- Atomic per row: doc va ghi trong 1 statement
-- Khong co race giua 2 concurrent request
-- Khong can SELECT-then-UPDATE


-- 3) Sync external API data -- chi update khi data thay doi
INSERT INTO products (sku, name, price, last_synced)
SELECT sku, name, price, now()
FROM external_products          -- source tu ETL hoac COPY
ON CONFLICT (sku) DO UPDATE
SET name        = EXCLUDED.name,
    price       = EXCLUDED.price,
    last_synced = now()
WHERE products.price IS DISTINCT FROM EXCLUDED.price
   OR products.name  IS DISTINCT FROM EXCLUDED.name;
-- Chi ghi khi price hoac name thuc su thay doi
-- Giam write amplification + audit log gon hon
-- last_synced luon cap nhat (nen khong nam trong WHERE condition)