-- Special Access Links Table
-- Allows administrators to create special URLs that display specific hidden products
-- Use case: VIP access, sponsor packages, partner deals, time-limited offers

CREATE TABLE IF NOT EXISTS `special_access_links` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Link identification
    `link_code` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique code used in URL (e.g., VIP2025)',
    `link_name` VARCHAR(200) NOT NULL COMMENT 'Admin-friendly name for the link',
    `description` TEXT COMMENT 'Optional notes about purpose/target audience',
    
    -- Product configuration
    `product_ids` JSON NOT NULL COMMENT 'Array of product IDs to show with this link',
    `include_visible_products` TINYINT(1) DEFAULT 1 COMMENT '1=show normal visible products too, 0=show only special products',
    
    -- Status and validity
    `is_active` TINYINT(1) DEFAULT 1 COMMENT 'Enable/disable the link',
    `expires_at` DATETIME DEFAULT NULL COMMENT 'Optional expiration date/time',
    
    -- Usage tracking
    `access_count` INT DEFAULT 0 COMMENT 'Number of times link was accessed',
    `last_accessed_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Last time the link was used',
    `conversion_count` INT DEFAULT 0 COMMENT 'Number of completed registrations from this link',
    
    -- Audit trail
    `created_by` INT DEFAULT NULL COMMENT 'Admin user ID who created this link',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX `idx_link_code` (`link_code`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_created_by` (`created_by`),
    
    -- Foreign key for audit trail
    FOREIGN KEY (`created_by`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
COMMENT='Special access links for displaying hidden products via custom URLs';

-- Activity log table extension (if not already tracked)
-- The activity_log table should already exist, this is just for documentation
-- Log entries will use:
-- - action: 'create_special_link', 'update_special_link', 'delete_special_link', 'toggle_special_link'
-- - details: JSON with link_code, link_name, changes

-- Example usage queries:
-- 1. Validate and get active link:
--    SELECT * FROM special_access_links 
--    WHERE link_code = ? AND is_active = 1 
--    AND (expires_at IS NULL OR expires_at > NOW())

-- 2. Track link access:
--    UPDATE special_access_links 
--    SET access_count = access_count + 1, last_accessed_at = NOW() 
--    WHERE link_code = ?

-- 3. Get link statistics:
--    SELECT link_name, link_code, access_count, conversion_count,
--           ROUND((conversion_count / access_count * 100), 2) as conversion_rate
--    FROM special_access_links 
--    WHERE access_count > 0
--    ORDER BY access_count DESC



