ডেটাবেস ডিজাইন ইন্টারভিউ — schema থেকে scaling

-- URL shortener: schema আর নমুনা ডেটা
CREATE TABLE links (
  link_id    INTEGER PRIMARY KEY,
  code       TEXT NOT NULL UNIQUE CHECK (length(code) BETWEEN 4 AND 16),
  long_url   TEXT NOT NULL CHECK (long_url LIKE 'http%://%'),
  owner_id   INTEGER,                     -- NULL = লগইন ছাড়া বানানো
  created_at TEXT NOT NULL,               -- UTC, ISO-8601
  expires_at TEXT,                        -- NULL = মেয়াদ নেই
  CHECK (expires_at IS NULL OR expires_at > created_at)
);
CREATE TABLE click_daily (                -- প্রতিটি ক্লিক নয়, দিনের মোট
  link_id INTEGER NOT NULL REFERENCES links(link_id) ON DELETE CASCADE,
  day     TEXT NOT NULL,
  clicks  INTEGER NOT NULL DEFAULT 0 CHECK (clicks >= 0),
  PRIMARY KEY (link_id, day)
);
INSERT INTO links VALUES
 (1, 'abcd12',    'https://abcltech.com/dbms-sql/',  7,    '2026-10-01 04:00:00', NULL),
 (2, 'eid-offer', 'https://shop.example.com/eid',    9,    '2026-10-02 10:00:00', '2026-10-20 00:00:00'),
 (3, 'Zx9Q',      'https://example.org/notice.pdf',  NULL, '2026-10-03 08:30:00', NULL);
-- রিডাইরেক্ট (এখন ধরা হলো '2026-10-25 06:00:00' UTC)
SELECT code, long_url FROM links
WHERE code IN ('abcd12', 'eid-offer')
  AND (expires_at IS NULL OR expires_at > '2026-10-25 06:00:00');
EXPLAIN QUERY PLAN SELECT long_url FROM links WHERE code = 'abcd12';

-- id থেকে base62 code: 0-9, a-z, A-Z — মোট ৬২টি অক্ষর
WITH RECURSIVE ids(id) AS (VALUES (61), (62), (1000000), (3521614606207)),
b62(id, n, code) AS (
  SELECT id, id, '' FROM ids
  UNION ALL
  SELECT id, n / 62,
    substr('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', n % 62 + 1, 1) || code
  FROM b62 WHERE n > 0)
SELECT id, code FROM b62 WHERE n = 0 ORDER BY id;
-- প্রতিটি ক্লিকে দিনের গণনা এক বাড়ানো — upsert, দুবার
INSERT INTO click_daily VALUES (1, '2026-10-25', 1)
  ON CONFLICT (link_id, day) DO UPDATE SET clicks = clicks + 1;
INSERT INTO click_daily VALUES (1, '2026-10-25', 1)
  ON CONFLICT (link_id, day) DO UPDATE SET clicks = clicks + 1;
SELECT * FROM click_daily;
-- কেউ নিজের পছন্দের নাম (custom alias) চাইলেন, যা আগেই নেওয়া
INSERT INTO links VALUES (4, 'eid-offer', 'https://other.example.com', 12, '2026-10-25 06:00:00', NULL);

-- ফলাফল সিস্টেম: db-uni-এর ওপর গ্রেড-পয়েন্টের টেবিল আর গ্রেড বদলের হিসাব
CREATE TABLE grade_points (grade TEXT PRIMARY KEY,
  point REAL NOT NULL CHECK (point BETWEEN 0 AND 4));
INSERT INTO grade_points VALUES ('A+', 4.00), ('A', 3.75), ('A-', 3.50), ('B+', 3.25),
 ('B', 3.00), ('B-', 2.75), ('C+', 2.50), ('C', 2.25), ('D', 2.00), ('F', 0.00);
CREATE TABLE grade_audit (
  audit_id   INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL, course_id TEXT NOT NULL, term TEXT NOT NULL,
  old_grade  TEXT, new_grade TEXT,
  changed_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TRIGGER grade_must_exist BEFORE UPDATE OF grade ON enrollments
WHEN NEW.grade IS NOT NULL AND NEW.grade NOT IN (SELECT grade FROM grade_points)
BEGIN SELECT RAISE(ABORT, 'unknown grade'); END;
CREATE TRIGGER grade_log AFTER UPDATE OF grade ON enrollments
WHEN OLD.grade IS NOT NEW.grade
BEGIN
  INSERT INTO grade_audit (student_id, course_id, term, old_grade, new_grade)
  VALUES (OLD.student_id, OLD.course_id, OLD.term, OLD.grade, NEW.grade);
END;
-- CGPA = Σ(ক্রেডিট × পয়েন্ট) ÷ Σ ক্রেডিট; F-এর পয়েন্ট ০, NULL গ্রেড বাদ
SELECT s.student_id, s.name, s.cgpa AS stored_cgpa,
  ROUND(SUM(c.credits * g.point) / SUM(c.credits), 2) AS computed_cgpa,
  SUM(c.credits) AS credits
FROM students s
JOIN enrollments e  ON e.student_id = s.student_id
JOIN courses c      ON c.course_id = e.course_id
JOIN grade_points g ON g.grade = e.grade
GROUP BY s.student_id ORDER BY s.student_id;

-- পুনর্নিরীক্ষায় Bristy Saha-র CSE301 গ্রেড B থেকে B+
UPDATE enrollments SET grade = 'B+'
WHERE student_id = 2102 AND course_id = 'CSE301' AND term = '2023-Fall';
SELECT student_id, course_id, term, old_grade, new_grade FROM grade_audit;
SELECT ROUND(SUM(c.credits * g.point) / SUM(c.credits), 2) AS cgpa_2102
FROM enrollments e JOIN courses c ON c.course_id = e.course_id
JOIN grade_points g ON g.grade = e.grade WHERE e.student_id = 2102;
-- টাইপের ভুল: স্কেলে নেই এমন গ্রেড
UPDATE enrollments SET grade = 'A++'
WHERE student_id = 2101 AND course_id = 'CSE201' AND term = '2022-Spring';

CREATE TABLE accounts (                   -- সব টাকা পয়সায়, INTEGER
  account_id INTEGER PRIMARY KEY, owner TEXT NOT NULL,
  kind       TEXT NOT NULL CHECK (kind IN ('customer', 'merchant', 'system')),
  balance    INTEGER NOT NULL DEFAULT 0,   -- entries-এর যোগফলের কপি
  CHECK (kind = 'system' OR balance >= 0)
);
CREATE TABLE transfers (
  transfer_id     INTEGER PRIMARY KEY,
  idempotency_key TEXT NOT NULL UNIQUE,    -- অ্যাপের পাঠানো অনন্য অনুরোধ-id
  kind TEXT NOT NULL, created_at TEXT NOT NULL
);
CREATE TABLE entries (
  entry_id    INTEGER PRIMARY KEY,
  transfer_id INTEGER NOT NULL REFERENCES transfers(transfer_id),
  account_id  INTEGER NOT NULL REFERENCES accounts(account_id),
  amount      INTEGER NOT NULL CHECK (amount <> 0)   -- + জমা, − খরচ
);
CREATE INDEX entries_by_account ON entries(account_id, entry_id);
CREATE TRIGGER entries_append_only BEFORE UPDATE ON entries
BEGIN SELECT RAISE(ABORT, 'ledger entries are append-only'); END;
INSERT INTO accounts VALUES (1, 'Cash-in pool', 'system', -100000),
 (2, 'Rahim', 'customer', 100000), (3, 'Karima', 'customer', 0), (4, 'Mudi Dokan', 'merchant', 0);
INSERT INTO transfers VALUES (1, 'agent-cashin-0001', 'cash_in', '2026-10-25 03:00:00');
INSERT INTO entries (transfer_id, account_id, amount) VALUES (1, 1, -100000), (1, 2, 100000);
SELECT transfer_id, SUM(amount) AS net FROM entries GROUP BY transfer_id;
SELECT a.account_id, a.owner, a.balance, COALESCE(SUM(e.amount), 0) AS ledger_sum
FROM accounts a LEFT JOIN entries e ON e.account_id = a.account_id
GROUP BY a.account_id;
UPDATE entries SET amount = 500000 WHERE entry_id = 2;

-- Rahim → Karima ২৫০ টাকা; অ্যাপের idempotency key 'app-7f3a-0001'
BEGIN;
INSERT INTO transfers VALUES (2, 'app-7f3a-0001', 'send_money', '2026-10-25 05:10:00');
INSERT INTO entries (transfer_id, account_id, amount) VALUES (2, 2, -25000), (2, 3, 25000);
UPDATE accounts SET balance = balance - 25000 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 25000 WHERE account_id = 3;
COMMIT;
-- নেটওয়ার্ক কেটে যাওয়ায় অ্যাপ একই অনুরোধ আবার পাঠাল
INSERT INTO transfers VALUES (3, 'app-7f3a-0001', 'send_money', '2026-10-25 05:10:07')
  ON CONFLICT (idempotency_key) DO NOTHING;
SELECT transfer_id, created_at FROM transfers WHERE idempotency_key = 'app-7f3a-0001';
SELECT account_id, owner, balance FROM accounts WHERE account_id IN (2, 3);
-- Karima-র আছে ২৫০ টাকা, তিনি ৫০০ টাকা পাঠাতে চান
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 3;

-- ট্রেনের টিকিট: প্রতিটি যাত্রার প্রতিটি আসন একটি সারি (inventory)
CREATE TABLE trips (
  trip_id    INTEGER PRIMARY KEY,
  train_no   TEXT NOT NULL,
  journey_on TEXT NOT NULL,              -- ঢাকার স্থানীয় তারিখ
  UNIQUE (train_no, journey_on)
);
CREATE TABLE trip_seats (
  trip_id    INTEGER NOT NULL REFERENCES trips(trip_id),
  seat_no    TEXT NOT NULL,              -- যেমন 'KA-3'
  status     TEXT NOT NULL DEFAULT 'free' CHECK (status IN ('free', 'held', 'sold')),
  holder_id  INTEGER,
  hold_until TEXT,                       -- UTC
  PRIMARY KEY (trip_id, seat_no),
  CHECK ((status = 'free') = (holder_id IS NULL)),
  CHECK ((status = 'held') = (hold_until IS NOT NULL))
);
INSERT INTO trips VALUES (1, 'EXP-701', '2026-11-05'), (2, 'EXP-702', '2026-11-05');
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 6)
INSERT INTO trip_seats (trip_id, seat_no) SELECT t.trip_id, 'KA-' || n.i FROM trips t, n;
UPDATE trip_seats SET status = 'sold', holder_id = 400 WHERE trip_id = 1 AND seat_no IN ('KA-1', 'KA-2');
SELECT t.train_no, t.journey_on,
  SUM(s.status = 'free') AS free_seats, SUM(s.status = 'sold') AS sold
FROM trips t JOIN trip_seats s ON s.trip_id = t.trip_id
GROUP BY t.trip_id ORDER BY t.trip_id;

-- 06:00 UTC: যাত্রী 501 আসন KA-3 ধরলেন (১০ মিনিটের hold)
UPDATE trip_seats SET status = 'held', holder_id = 501, hold_until = '2026-10-25 06:10:00'
WHERE trip_id = 1 AND seat_no = 'KA-3'
  AND (status = 'free' OR (status = 'held' AND hold_until < '2026-10-25 06:00:00'));
-- একই মুহূর্তে যাত্রী 502 একই আসন চাইলেন
UPDATE trip_seats SET status = 'held', holder_id = 502, hold_until = '2026-10-25 06:10:00'
WHERE trip_id = 1 AND seat_no = 'KA-3'
  AND (status = 'free' OR (status = 'held' AND hold_until < '2026-10-25 06:00:00'));
-- 06:15: 501 টাকা দেননি, hold-এর মেয়াদ শেষ — এবার 502 পারেন
UPDATE trip_seats SET status = 'held', holder_id = 502, hold_until = '2026-10-25 06:25:00'
WHERE trip_id = 1 AND seat_no = 'KA-3'
  AND (status = 'free' OR (status = 'held' AND hold_until < '2026-10-25 06:15:00'));
-- 06:18: পেমেন্ট সফল — শুধু নিজের hold থাকলেই বিক্রি
UPDATE trip_seats SET status = 'sold', hold_until = NULL
WHERE trip_id = 1 AND seat_no = 'KA-3' AND status = 'held' AND holder_id = 502
  AND hold_until >= '2026-10-25 06:18:00';
-- 06:20: দেরিতে আসা 501-এর পেমেন্ট — আসন আর তাঁর নয়
UPDATE trip_seats SET status = 'sold', hold_until = NULL
WHERE trip_id = 1 AND seat_no = 'KA-3' AND status = 'held' AND holder_id = 501
  AND hold_until >= '2026-10-25 06:20:00';
SELECT seat_no, status, holder_id, hold_until FROM trip_seats WHERE trip_id = 1;

-- ১. "যেকোনো একটি খালি আসন দাও" — অন্যদের লক করা সারিতে অপেক্ষা না করে
WITH pick AS (
  SELECT trip_id, seat_no FROM trip_seats
  WHERE trip_id = $1 AND status = 'free'
  ORDER BY seat_no LIMIT 1
  FOR UPDATE SKIP LOCKED)
UPDATE trip_seats s
SET status = 'held', holder_id = $2, hold_until = now() + interval '10 minutes'
FROM pick WHERE s.trip_id = pick.trip_id AND s.seat_no = pick.seat_no
RETURNING s.seat_no;

-- ২. অংশের টিকিট: একই আসন ঢাকা→ভৈরব আর ভৈরব→চট্টগ্রাম, কিন্তু ছেদ নিষেধ
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE tickets ADD CONSTRAINT no_overlap EXCLUDE USING gist
  (trip_id WITH =, seat_no WITH =, int4range(from_stop, to_stop) WITH &&)
  WHERE (status <> 'cancelled');

-- ই-কমার্স: db-shop-এর ওপর cart
CREATE TABLE carts (
  cart_id     INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL UNIQUE REFERENCES customers(customer_id),
  updated_at  TEXT NOT NULL
);
CREATE TABLE cart_items (
  cart_id    INTEGER NOT NULL REFERENCES carts(cart_id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(product_id),
  quantity   INTEGER NOT NULL CHECK (quantity BETWEEN 1 AND 10),
  PRIMARY KEY (cart_id, product_id)
);
INSERT INTO carts VALUES (1, 3, '2026-10-25 05:00:00'), (2, 4, '2026-10-25 05:05:00');
INSERT INTO cart_items VALUES (1, 102, 2), (1, 104, 1), (2, 103, 1), (2, 108, 2);
-- cart দেখানো: আজকের দাম আর মজুত আছে কি না
SELECT c.customer_id, p.name, ci.quantity, p.price,
  ci.quantity * p.price AS line_total,
  CASE WHEN p.stock >= ci.quantity THEN 'ok' ELSE 'out of stock' END AS availability
FROM carts c JOIN cart_items ci ON ci.cart_id = c.cart_id
JOIN products p ON p.product_id = ci.product_id
ORDER BY c.cart_id, p.product_id;

-- Sabbir Hasan (customer 3, cart 1)-এর checkout — সব বা কিছুই না
BEGIN;
INSERT INTO orders VALUES (1011, 3, '2026-10-25', 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 1011, ci.product_id, ci.quantity, p.price            -- আজকের দামের snapshot
FROM cart_items ci JOIN products p ON p.product_id = ci.product_id WHERE ci.cart_id = 1;
UPDATE products SET stock = stock - (SELECT quantity FROM cart_items
  WHERE cart_id = 1 AND product_id = products.product_id)
WHERE product_id IN (SELECT product_id FROM cart_items WHERE cart_id = 1);
DELETE FROM cart_items WHERE cart_id = 1;
COMMIT;
SELECT product_id, name, stock FROM products WHERE product_id IN (102, 104);
SELECT * FROM order_items WHERE order_id = 1011;
-- Nusrat Jahan-এর cart-এ Power Bank (মজুত ০) — একই checkout
BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 103;

CREATE TABLE drivers (
  driver_id INTEGER PRIMARY KEY, name TEXT NOT NULL,
  vehicle   TEXT NOT NULL CHECK (vehicle IN ('bike', 'car', 'cng'))
);
CREATE TABLE driver_locations (           -- শুধু শেষ অবস্থান; পুরো ইতিহাস অন্য জায়গায়
  driver_id  INTEGER PRIMARY KEY REFERENCES drivers(driver_id),
  lat REAL NOT NULL, lng REAL NOT NULL,
  is_free    INTEGER NOT NULL CHECK (is_free IN (0, 1)), updated_at TEXT NOT NULL
);
CREATE INDEX free_by_lat ON driver_locations(is_free, lat);
CREATE TABLE trips (
  trip_id    INTEGER PRIMARY KEY, rider_id INTEGER NOT NULL,
  driver_id  INTEGER REFERENCES drivers(driver_id), fare_paisa INTEGER CHECK (fare_paisa >= 0),
  status     TEXT NOT NULL CHECK (status IN ('requested', 'accepted', 'started', 'completed', 'cancelled')),
  CHECK (status IN ('requested', 'cancelled') OR driver_id IS NOT NULL)
);
CREATE UNIQUE INDEX one_active_trip ON trips(driver_id) WHERE status IN ('accepted', 'started');
INSERT INTO drivers VALUES (1, 'Jamal', 'bike'), (2, 'Rubel', 'car'), (3, 'Sumon', 'bike'), (4, 'Liton', 'cng'), (5, 'Shirin', 'bike');
INSERT INTO driver_locations VALUES
 (1, 23.7937, 90.4066, 1, '2026-10-25 05:59:58'), (2, 23.7800, 90.4070, 1, '2026-10-25 05:59:55'),
 (3, 23.7639, 90.3925, 1, '2026-10-25 05:59:57'), (4, 23.7790, 90.4040, 1, '2026-10-25 05:59:50'),
 (5, 23.7461, 90.3742, 1, '2026-10-25 05:59:59');
-- যাত্রী মহাখালীতে; ১° অক্ষাংশ ≈ ১১১.৩ কিমি, এখানে ১° দ্রাঘিমা ≈ ১০১.৯ কিমি
SELECT d.name, l.lat, l.lng,
  ROUND(abs(l.lat - 23.7780) * 111.3 + abs(l.lng - 90.4050) * 101.9, 2) AS approx_km
FROM driver_locations l JOIN drivers d ON d.driver_id = l.driver_id
WHERE l.is_free = 1 AND d.vehicle = 'bike'
  AND l.lat BETWEEN 23.7780 - 0.027 AND 23.7780 + 0.027      -- ±৩ কিমি
  AND l.lng BETWEEN 90.4050 - 0.029 AND 90.4050 + 0.029
ORDER BY approx_km LIMIT 3;

INSERT INTO trips (trip_id, rider_id, status) VALUES (9001, 77, 'requested'), (9002, 78, 'requested');
-- Jamal 9001 নিলেন — শুধু 'requested' অবস্থায় থাকলেই
UPDATE trips SET driver_id = 1, status = 'accepted' WHERE trip_id = 9001 AND status = 'requested';
-- একই সেকেন্ডে Sumon-ও 9001 নিতে চাইলেন
UPDATE trips SET driver_id = 3, status = 'accepted' WHERE trip_id = 9001 AND status = 'requested';
UPDATE driver_locations SET is_free = 0 WHERE driver_id = 1;
SELECT trip_id, driver_id, status FROM trips;
-- অ্যাপের বাগে Jamal-কে 9002-ও দেওয়া হলো
UPDATE trips SET driver_id = 1, status = 'accepted' WHERE trip_id = 9002 AND status = 'requested';

CREATE TABLE users (user_id INTEGER PRIMARY KEY, handle TEXT NOT NULL UNIQUE,
  followers_count INTEGER NOT NULL DEFAULT 0);    -- ক্যাশ করা গণনা
CREATE TABLE follows (
  follower_id INTEGER NOT NULL REFERENCES users(user_id),
  followee_id INTEGER NOT NULL REFERENCES users(user_id),
  PRIMARY KEY (follower_id, followee_id), CHECK (follower_id <> followee_id)
);
CREATE INDEX follows_by_followee ON follows(followee_id, follower_id);
CREATE TABLE posts (
  post_id   INTEGER PRIMARY KEY,                 -- সময়-ক্রমের id: বড় id = নতুন
  author_id INTEGER NOT NULL REFERENCES users(user_id),
  body      TEXT NOT NULL CHECK (length(body) <= 500)
);
CREATE INDEX posts_by_author ON posts(author_id, post_id);
CREATE TABLE feed_items (                       -- প্রতিটি ব্যবহারকারীর ফিডের "ডাকবাক্স"
  user_id INTEGER NOT NULL REFERENCES users(user_id),
  post_id INTEGER NOT NULL REFERENCES posts(post_id), PRIMARY KEY (user_id, post_id));
INSERT INTO users VALUES (1, 'rahim', 0), (2, 'karima', 2), (3, 'sabbir', 0), (4, 'cricket_star', 2500000);
INSERT INTO follows VALUES (1, 2), (3, 2), (1, 4), (3, 4), (2, 4);
INSERT INTO posts VALUES (101, 2, 'মিরপুরে আজ খুব জ্যাম'), (102, 4, 'আজ ম্যাচ, সবাই দোয়া করবেন'),
 (103, 2, 'নতুন বই পড়া শুরু'), (104, 3, 'প্রথম পোস্ট');
INSERT INTO feed_items VALUES (1, 101), (3, 101), (1, 103), (3, 103);   -- karima-র পোস্ট আগেই ছড়ানো
SELECT p.post_id, u.handle, p.body            -- fan-out on read: Rahim (1)-এর ফিড
FROM posts p JOIN users u ON u.user_id = p.author_id
WHERE p.author_id IN (SELECT followee_id FROM follows WHERE follower_id = 1)
ORDER BY p.post_id DESC LIMIT 2;
-- পরের পাতা (keyset): শেষ দেখা post_id 102-এর চেয়ে পুরোনো
SELECT p.post_id, p.body FROM posts p WHERE p.post_id < 102
  AND p.author_id IN (SELECT followee_id FROM follows WHERE follower_id = 1)
ORDER BY p.post_id DESC LIMIT 2;

-- karima নতুন পোস্ট দিলেন: fan-out on write — প্রতিটি অনুসারীর ডাকবাক্সে একটি সারি
INSERT INTO posts VALUES (105, 2, 'সন্ধ্যায় বৃষ্টি হবে?');
INSERT INTO feed_items (user_id, post_id)
SELECT follower_id, 105 FROM follows WHERE followee_id = 2;
EXPLAIN QUERY PLAN SELECT follower_id FROM follows WHERE followee_id = 2;
-- Sabbir (3)-এর ফিড: ডাকবাক্স + অনুসরণ করা তারকার পোস্ট (hybrid)
SELECT post_id FROM feed_items WHERE user_id = 3
UNION
SELECT p.post_id FROM posts p
JOIN follows f ON f.followee_id = p.author_id AND f.follower_id = 3
JOIN users u ON u.user_id = p.author_id AND u.followers_count >= 1000000
ORDER BY post_id DESC LIMIT 20;

CREATE TABLE conversations (conv_id INTEGER PRIMARY KEY, title TEXT,
  kind TEXT NOT NULL CHECK (kind IN ('direct', 'group')));
CREATE TABLE members (
  conv_id INTEGER NOT NULL REFERENCES conversations(conv_id), user_id INTEGER NOT NULL,
  last_read_seq INTEGER NOT NULL DEFAULT 0,      -- কোন বার্তা পর্যন্ত পড়েছেন
  PRIMARY KEY (conv_id, user_id));
CREATE INDEX members_by_user ON members(user_id);
CREATE TABLE messages (
  conv_id       INTEGER NOT NULL, seq INTEGER NOT NULL,   -- seq: প্রতিটি চ্যাটে ১, ২, ৩ …
  sender_id     INTEGER NOT NULL,
  client_msg_id TEXT NOT NULL,                   -- ফোনে বানানো id
  body          TEXT NOT NULL, sent_at TEXT NOT NULL,
  PRIMARY KEY (conv_id, seq), UNIQUE (sender_id, client_msg_id),
  FOREIGN KEY (conv_id, sender_id) REFERENCES members(conv_id, user_id)
);
INSERT INTO conversations VALUES (1, NULL, 'direct'), (2, 'Family', 'group');
INSERT INTO members VALUES (1, 1, 2), (1, 2, 1), (2, 1, 1), (2, 2, 3), (2, 3, 3);
INSERT INTO messages VALUES
 (1, 1, 1, 'c1-001', 'কাল দেখা হবে?', '2026-10-25 04:00:00'),
 (1, 2, 2, 'c2-001', 'হ্যাঁ, বিকেলে', '2026-10-25 04:02:00'),
 (2, 1, 2, 'c2-002', 'শুক্রবার দাওয়াত', '2026-10-25 04:10:00'),
 (2, 2, 3, 'c3-001', 'কয়টায়?', '2026-10-25 04:11:00'),
 (2, 3, 2, 'c2-003', 'দুপুর ১টা', '2026-10-25 04:12:00');
-- user 1-এর inbox: শেষ বার্তা আর না-পড়া বার্তার সংখ্যা
SELECT m.conv_id, COALESCE(c.title, 'direct') AS chat,
  (SELECT body FROM messages x WHERE x.conv_id = m.conv_id ORDER BY seq DESC LIMIT 1) AS last_msg,
  (SELECT COUNT(*) FROM messages x WHERE x.conv_id = m.conv_id
     AND x.seq > m.last_read_seq AND x.sender_id <> m.user_id) AS unread
FROM members m JOIN conversations c ON c.conv_id = m.conv_id
WHERE m.user_id = 1 ORDER BY m.conv_id;

-- Sabbir (3) Family গ্রুপে লিখলেন; পরের seq = সর্বোচ্চ + ১
INSERT INTO messages (conv_id, seq, sender_id, client_msg_id, body, sent_at)
SELECT 2, COALESCE(MAX(seq), 0) + 1, 3, 'c3-002', 'আমি মিষ্টি আনব', '2026-10-25 04:20:00'
FROM messages WHERE conv_id = 2
ON CONFLICT (sender_id, client_msg_id) DO NOTHING;
-- নেটওয়ার্ক দুর্বল, ফোন একই বার্তা আবার পাঠাল
INSERT INTO messages (conv_id, seq, sender_id, client_msg_id, body, sent_at)
SELECT 2, COALESCE(MAX(seq), 0) + 1, 3, 'c3-002', 'আমি মিষ্টি আনব', '2026-10-25 04:20:05'
FROM messages WHERE conv_id = 2
ON CONFLICT (sender_id, client_msg_id) DO NOTHING;
-- Rahim (1) গ্রুপটি খুলে seq 4 পর্যন্ত পড়লেন; পয়েন্টার কখনো পেছায় না
UPDATE members SET last_read_seq = max(last_read_seq, 4) WHERE conv_id = 2 AND user_id = 1;
-- seq 4-এর "Seen by" — প্রেরক বাদে
SELECT user_id FROM members
WHERE conv_id = 2 AND last_read_seq >= 4 AND user_id <> 3;