-- ============================================================================
-- Migration: Add domain selection for shortlinks
-- Version: 009
-- Date: 2025-12-26
-- ============================================================================
-- Purpose: Enable domain selection when creating shortlinks
-- Options: Global domain (random) or specific domain
-- ============================================================================

-- Add domain_id column to shortlinks table
ALTER TABLE shortlinks
ADD COLUMN domain_id INT UNSIGNED NULL AFTER subdomain,
ADD INDEX idx_domain_id (domain_id),
ADD FOREIGN KEY fk_shortlink_domain (domain_id)
    REFERENCES domains(id)
    ON DELETE SET NULL;

-- Add use_global_domain flag
ALTER TABLE shortlinks
ADD COLUMN use_global_domain TINYINT(1) NOT NULL DEFAULT 0 AFTER domain_id;

-- ============================================================================
-- USAGE NOTES:
-- ============================================================================
--
-- Domain selection modes:
--
-- 1. GLOBAL DOMAIN (Random):
--    - use_global_domain = 1
--    - domain_id = NULL (or specific domain if user was assigned at time of click)
--    - System randomly selects domain from active domains on EACH redirect
--    - Format: {random-subdomain}.{random-domain}/slug
--
-- 2. SPECIFIC DOMAIN:
--    - use_global_domain = 0
--    - domain_id = specific domain ID
--    - Always uses the selected domain
--    - Format: {random-subdomain}.{specific-domain}/slug
--
-- Examples:
--   Global domain:
--     - use_global_domain = 1
--     - URL: xy7k2m.example.com/promo (on first click)
--     - URL: ab4de7.example02.com/promo (on second click) - different domain
--
--   Specific domain:
--     - use_global_domain = 0, domain_id = 3 (example02.com)
--     - URL: xy7k2m.example02.com/promo (always example02.com)
--
-- ============================================================================