Design URL Shortener (TinyURL)

# Base62 encoding for URL shortening
BASE62 = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

def encode_base62(num: int) -> str:
    """Auto-increment ID কে Base62 string এ convert করুন"""
    if num == 0:
        return BASE62[0]
    result = ""
    while num > 0:
        result = BASE62[num % 62] + result
        num //= 62
    return result.zfill(7)  # always 7 chars

# Example: ID 125 → "0000020", ID 3521614606208 → "abcdefg"
def create_short_url(long_url: str, db, cache) -> str:
    # 1. DB-তে save করুন, auto-increment ID পান
    url_id = db.insert({"long_url": long_url, "created_at": now()})

    # 2. ID কে Base62 encode করুন
    short_code = encode_base62(url_id)

    # 3. DB update করুন short_code দিয়ে
    db.update(url_id, {"short_code": short_code})

    # 4. Cache-এ রাখুন future reads এর জন্য
    cache.set(short_code, long_url, ttl=86400)

    return f"https://tinyurl.com/{short_code}"

CREATE TABLE urls (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  short_code   VARCHAR(10) UNIQUE NOT NULL,
  long_url     TEXT NOT NULL,
  user_id      BIGINT,           -- who created it
  created_at   TIMESTAMP DEFAULT NOW(),
  expires_at   TIMESTAMP,        -- NULL = never expires
  click_count  BIGINT DEFAULT 0
);

-- short_code এ index — সবচেয়ে বেশি query এখানে
CREATE INDEX idx_short_code ON urls (short_code);

-- Analytics table (separate)
CREATE TABLE url_clicks (
  id         BIGINT PRIMARY KEY,
  short_code VARCHAR(10),
  clicked_at TIMESTAMP,
  ip_address VARCHAR(45),
  user_agent TEXT
);