-- ============================================================================
-- Activity Logs for Shortlinks - Real-time Click Tracking
-- ============================================================================
-- Migration: 004_create_activity_logs_shortlinks.sql
-- Purpose: Track every click with detailed metadata for analytics
-- ============================================================================

CREATE TABLE IF NOT EXISTS `shortlink_clicks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Shortlink reference
    `shortlink_id` BIGINT UNSIGNED NOT NULL,
    `slug` VARCHAR(100) NOT NULL,

    -- Request metadata
    `ip_address` VARCHAR(45) NOT NULL COMMENT 'IPv4 or IPv6',
    `user_agent` TEXT NOT NULL,
    `referer` VARCHAR(2048) DEFAULT NULL COMMENT 'HTTP Referer header',
    `accept_language` VARCHAR(255) DEFAULT NULL,

    -- Parsed user agent info
    `device_type` ENUM('desktop', 'mobile', 'tablet', 'bot', 'unknown') DEFAULT 'unknown',
    `browser` VARCHAR(50) DEFAULT NULL,
    `os` VARCHAR(50) DEFAULT NULL,
    `is_bot` TINYINT(1) DEFAULT 0 COMMENT '1 if crawler/bot detected',

    -- Geolocation (optional - requires GeoIP)
    `country_code` CHAR(2) DEFAULT NULL COMMENT 'ISO 3166-1 alpha-2',
    `country_name` VARCHAR(100) DEFAULT NULL,
    `city` VARCHAR(100) DEFAULT NULL,

    -- Timestamp
    `clicked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Performance metrics
    `redirect_time_ms` INT UNSIGNED DEFAULT NULL COMMENT 'Time to redirect in milliseconds',

    -- Indexes for fast queries
    INDEX idx_shortlink_id (shortlink_id),
    INDEX idx_slug (slug),
    INDEX idx_clicked_at (clicked_at DESC),
    INDEX idx_device_type (device_type),
    INDEX idx_is_bot (is_bot),
    INDEX idx_country (country_code),
    INDEX idx_shortlink_clicked (shortlink_id, clicked_at DESC),

    -- Foreign key
    FOREIGN KEY fk_shortlink_clicks_shortlink_id (shortlink_id)
        REFERENCES shortlinks(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC
COMMENT='Real-time click tracking for shortlinks with detailed metadata';

-- ============================================================================
-- Performance Metrics Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS `performance_metrics` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Metric type
    `metric_type` ENUM('redirect', 'database', 'cache', 'api') NOT NULL,
    `operation` VARCHAR(100) NOT NULL COMMENT 'Operation name',

    -- Performance data
    `duration_ms` INT UNSIGNED NOT NULL COMMENT 'Execution time in milliseconds',
    `memory_mb` DECIMAL(10,2) DEFAULT NULL COMMENT 'Memory usage in MB',

    -- Metadata
    `metadata` JSON DEFAULT NULL COMMENT 'Additional context',

    -- Timestamp
    `recorded_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_metric_type (metric_type),
    INDEX idx_operation (operation),
    INDEX idx_recorded_at (recorded_at DESC),
    INDEX idx_type_recorded (metric_type, recorded_at DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC
COMMENT='Performance metrics for monitoring and optimization';

-- ============================================================================
-- System Stats Table (for real-time monitoring)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `system_stats` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Stats type
    `stat_type` ENUM('disk', 'memory', 'cache', 'database') NOT NULL,

    -- Values
    `total` BIGINT UNSIGNED DEFAULT NULL COMMENT 'Total capacity',
    `used` BIGINT UNSIGNED DEFAULT NULL COMMENT 'Used amount',
    `free` BIGINT UNSIGNED DEFAULT NULL COMMENT 'Free amount',
    `percentage` DECIMAL(5,2) DEFAULT NULL COMMENT 'Usage percentage',

    -- Additional data
    `details` JSON DEFAULT NULL,

    -- Timestamp
    `recorded_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_stat_type (stat_type),
    INDEX idx_recorded_at (recorded_at DESC),
    INDEX idx_type_recorded (stat_type, recorded_at DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System resource monitoring';

-- ============================================================================
-- Cleanup old data (run periodically via cron)
-- ============================================================================

-- Example cleanup queries (uncomment and adjust as needed):

-- Delete shortlink clicks older than 90 days
-- DELETE FROM shortlink_clicks WHERE clicked_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete performance metrics older than 30 days
-- DELETE FROM performance_metrics WHERE recorded_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Delete system stats older than 7 days
-- DELETE FROM system_stats WHERE recorded_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- ============================================================================
-- Verification
-- ============================================================================

-- Show all tables
SHOW TABLES LIKE '%shortlink%';
SHOW TABLES LIKE '%performance%';
SHOW TABLES LIKE '%system%';

-- Describe tables
-- DESCRIBE shortlink_clicks;
-- DESCRIBE performance_metrics;
-- DESCRIBE system_stats;