-- ============================================================================
-- Migration: Add subdomain support for shortlinks
-- Version: 008
-- Date: 2025-12-26
-- ============================================================================
-- Purpose: Enable random subdomain generation for shortlinks
-- Format: {random}.example.com/slug instead of example.com/slug
-- ============================================================================

-- Add subdomain column to shortlinks table
ALTER TABLE shortlinks
ADD COLUMN subdomain VARCHAR(50) NULL AFTER slug,
ADD UNIQUE INDEX uk_subdomain_slug (subdomain, slug);

-- Update existing shortlinks to have NULL subdomain (use base domain)
UPDATE shortlinks SET subdomain = NULL WHERE subdomain IS NULL;

-- Add index for faster lookups
CREATE INDEX idx_subdomain ON shortlinks(subdomain);

-- ============================================================================
-- USAGE NOTES:
-- ============================================================================
--
-- Subdomain column:
--   - NULL = Use base domain (example.com/slug)
--   - Value = Use subdomain (abc123.example.com/slug)
--
-- Format:
--   - Random: Generated by SubdomainGenerator service
--   - Length: 6-8 characters (alphanumeric)
--   - Example: "xy7k2m", "abc123de"
--
-- Unique constraint:
--   - (subdomain, slug) must be unique
--   - Allows same slug on different subdomains
--   - Example:
--     - test.example.com/promo  ✅
--     - sale.example.com/promo  ✅ (different subdomain)
--     - test.example.com/promo  ❌ (duplicate)
--
-- ============================================================================