SQL ও PostgreSQL

docker run -d --name pg \
  -e POSTGRES_USER=app -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=food \
  -p 5432:5432 postgres:17

# database এর ভেতরে ঢোকো
docker exec -it pg psql -U app -d food

CREATE TABLE restaurants (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    area        TEXT NOT NULL,
    rating      NUMERIC(2,1) DEFAULT 0,
    is_open     BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE customers (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    phone       TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id             SERIAL PRIMARY KEY,
    customer_id    INT NOT NULL REFERENCES customers(id),
    restaurant_id  INT NOT NULL REFERENCES restaurants(id),
    total          INT NOT NULL CHECK (total >= 0),       -- পয়সায়
    status         TEXT NOT NULL DEFAULT 'pending',
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO restaurants (name, area, rating) VALUES
  ('Kacchi House', 'Dhanmondi', 4.6),
  ('Burger Lab', 'Gulshan', 4.2),
  ('Pitha Ghor', 'Mirpur', 4.8),
  ('Noodle Bar', 'Dhanmondi', 3.9);

INSERT INTO customers (name, phone) VALUES
  ('Rahim', '01711111111'), ('Nusrat', '01822222222'), ('Karim', '01933333333');

INSERT INTO orders (customer_id, restaurant_id, total, status, created_at) VALUES
  (1, 1, 45000, 'delivered', now() - interval '3 days'),
  (1, 2, 32000, 'delivered', now() - interval '2 days'),
  (2, 1, 90000, 'delivered', now() - interval '1 day'),
  (2, 3, 15000, 'cancelled', now() - interval '1 day'),
  (3, 1, 45000, 'pending',   now()),
  (1, 3, 22000, 'delivered', now());

SELECT * FROM restaurants;                          -- সব column (production code এ * এড়িয়ো)

SELECT name, rating FROM restaurants
WHERE area = 'Dhanmondi' AND is_open
ORDER BY rating DESC
LIMIT 10;

SELECT name FROM restaurants WHERE name ILIKE '%burger%';   -- case ছাড়া খোঁজা
SELECT * FROM orders WHERE status IN ('pending', 'confirmed');
SELECT * FROM orders WHERE created_at >= now() - interval '7 days';
SELECT * FROM restaurants WHERE rating BETWEEN 4.0 AND 4.7;

SELECT o.id, c.name AS customer, r.name AS restaurant, o.total / 100.0 AS taka, o.status
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN restaurants r ON r.id = o.restaurant_id
ORDER BY o.created_at DESC;

-- যে restaurant এ এখনো কোনো order হয়নি, সেটাও দেখাও
SELECT r.name, COUNT(o.id) AS order_count
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.id
GROUP BY r.id, r.name
ORDER BY order_count DESC;

-- প্রতি restaurant এর মোট বিক্রি (শুধু delivered)
SELECT r.name,
       COUNT(*)             AS orders,
       SUM(o.total) / 100.0 AS revenue_taka,
       AVG(o.total) / 100.0 AS avg_order_taka
FROM orders o
JOIN restaurants r ON r.id = o.restaurant_id
WHERE o.status = 'delivered'
GROUP BY r.name
HAVING SUM(o.total) > 30000          -- group এর উপর শর্ত
ORDER BY revenue_taka DESC;

-- দিন অনুযায়ী order
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY day
ORDER BY day;

-- গড়ের চেয়ে বড় order
SELECT id, total FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- CTE (WITH): জটিল query কে নাম দেওয়া ধাপে ভাঙা
WITH customer_spend AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
)
SELECT c.name, cs.spent / 100.0 AS taka
FROM customer_spend cs
JOIN customers c ON c.id = cs.customer_id
WHERE cs.spent > 50000;

-- প্রতিটা customer এর order, সাথে তার কততম order আর এ পর্যন্ত মোট
SELECT customer_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order,
       SUM(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders
ORDER BY customer_id, created_at;

-- প্রতিটা area র সেরা restaurant
SELECT * FROM (
    SELECT name, area, rating,
           RANK() OVER (PARTITION BY area ORDER BY rating DESC) AS rnk
    FROM restaurants
) t WHERE rnk = 1;

UPDATE orders SET status = 'confirmed' WHERE id = 5;

DELETE FROM orders WHERE status = 'cancelled' AND created_at < now() - interval '1 year';

-- না থাকলে যোগ, থাকলে বদল (upsert)
INSERT INTO customers (name, phone) VALUES ('Rahim Uddin', '01711111111')
ON CONFLICT (phone) DO UPDATE SET name = EXCLUDED.name;

-- কী বদলালো সেটা ফেরত দাও
UPDATE orders SET status = 'delivered' WHERE id = 5 RETURNING id, status;

BEGIN;
UPDATE wallets SET balance = balance - 50000 WHERE user_id = 1;
UPDATE wallets SET balance = balance + 50000 WHERE user_id = 2;
COMMIT;      -- দুটো একসাথে স্থায়ী হলো। মাঝে সমস্যা হলে ROLLBACK;

# ❌ ভয়ানক বিপদ
phone = request_data["phone"]
cursor.execute(f"SELECT * FROM customers WHERE phone = '{phone}'")
# কেউ phone হিসেবে পাঠালো:  ' OR '1'='1
# query হয়ে গেল: SELECT * FROM customers WHERE phone = '' OR '1'='1'  → সবার data!

# ✓ সবসময় parameter দিয়ে
cursor.execute("SELECT * FROM customers WHERE phone = %s", (phone,))