Database Fundamentals

-- Bank Transfer — ACID transaction এর classic example
BEGIN TRANSACTION;

-- Step 1: Sender থেকে টাকা কাটো
UPDATE accounts SET balance = balance - 5000
WHERE account_id = 101;

-- Step 2: Balance negative হলে সব undo — Atomicity!
IF (SELECT balance FROM accounts WHERE account_id = 101) < 0 THEN
    ROLLBACK;  -- দুটো UPDATE এর কোনোটাই হবে না
    RAISE EXCEPTION 'Insufficient balance';
END IF;

-- Step 3: Receiver এ টাকা যোগ করুন
UPDATE accounts SET balance = balance + 5000
WHERE account_id = 202;

-- Step 4: Audit log রাখুন
INSERT INTO transfer_log (from_id, to_id, amount, created_at)
VALUES (101, 202, 5000, NOW());

COMMIT;  -- সব ঠিক থাকলে permanent save — Durability!

-- 1. Basic index — single column
CREATE INDEX idx_users_email ON users(email);

-- 2. Composite index — দুটো column একসাথে filter করলেন
--    WHERE email = ? AND created_at > ?
CREATE INDEX idx_users_email_date ON users(email, created_at);

-- 3. Partial index — শুধু active users (ছোট এবং fast)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- 4. Query plan দেখুন — index use হচ্ছে কি?
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- "Index Scan" → ভালো ✅  |  "Seq Scan" → index নেই ⚠️

const { Pool } = require('pg');
const redis = require('redis');

// Connection Pool — connection বারবার create করা expensive!
const primaryDB = new Pool({ host: 'primary-db', max: 20 });
const replicaDB = new Pool({ host: 'replica-db', max: 40 });
const cache = redis.createClient();

// Cache-aside Pattern — সবচেয়ে common caching strategy
async function getUser(userId) {
  const cacheKey = `user:${userId}`;

  // 1. Cache check করুন
  const cached = await cache.get(cacheKey);
  if (cached) return JSON.parse(cached);  // Cache HIT ✅

  // 2. Cache miss — Replica থেকে read (Primary এ load দিও না)
  const result = await replicaDB.query(
    'SELECT * FROM users WHERE id = $1', [userId]
  );
  const user = result.rows[0];

  // 3. Cache এ store করুন (TTL: 1 hour)
  await cache.setEx(cacheKey, 3600, JSON.stringify(user));

  return user;
}

// Write — Primary তে
async function updateUser(userId, data) {
  await primaryDB.query('UPDATE users SET name=$1 WHERE id=$2', [data.name, userId]);
  await cache.del(`user:${userId}`);  // Cache invalidate করুন!
}