-- ===========================================
-- FLOWBOT DCI v3.0 - DATABASE MIGRATIONS
-- ===========================================
-- Execute these migrations to upgrade from v2.2 to v3.0
-- Run: mysql -u user -p database < v3_migrations.sql

-- ================================================
-- MIGRATION 001: Create process_history table
-- ================================================
-- Stores all process records for dashboard history

CREATE TABLE IF NOT EXISTS process_history (
    id VARCHAR(32) PRIMARY KEY COMMENT 'Process unique identifier',
    user_id INT DEFAULT NULL COMMENT 'Optional user association',
    name VARCHAR(255) DEFAULT NULL COMMENT 'Optional process name',
    total_urls INT NOT NULL DEFAULT 0 COMMENT 'Total URLs to process',
    processed_urls INT DEFAULT 0 COMMENT 'URLs processed so far',
    success_count INT DEFAULT 0 COMMENT 'Successful extractions',
    failed_count INT DEFAULT 0 COMMENT 'Failed extractions',
    status ENUM('pending', 'running', 'paused', 'completed', 'failed', 'cancelled') DEFAULT 'pending' COMMENT 'Current process status',
    mode ENUM('fast', 'standard', 'thorough') DEFAULT 'standard' COMMENT 'Processing mode',
    current_phase INT DEFAULT 0 COMMENT 'Current processing phase (0-3)',
    current_url VARCHAR(2048) DEFAULT NULL COMMENT 'URL currently being processed',
    options JSON COMMENT 'Processing options (auto-export, notifications, etc)',
    error_message TEXT DEFAULT NULL COMMENT 'Last error message if failed',
    started_at TIMESTAMP NULL COMMENT 'When processing started',
    completed_at TIMESTAMP NULL COMMENT 'When processing finished',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update time',

    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_user (user_id),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- MIGRATION 002: Create activity_log table
-- ================================================
-- Stores activity events for the dashboard feed

CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    process_id VARCHAR(32) DEFAULT NULL COMMENT 'Associated process ID',
    event_type VARCHAR(50) NOT NULL COMMENT 'Event type: process_started, url_success, url_failed, process_completed, etc',
    severity ENUM('info', 'success', 'warning', 'error') DEFAULT 'info' COMMENT 'Event severity level',
    message TEXT NOT NULL COMMENT 'Human-readable message',
    metadata JSON COMMENT 'Additional event data (URL, error code, etc)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Event timestamp',

    INDEX idx_process (process_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created (created_at),
    INDEX idx_severity (severity),
    INDEX idx_process_created (process_id, created_at),

    FOREIGN KEY (process_id) REFERENCES process_history(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- MIGRATION 003: Create domain_stats table
-- ================================================
-- Stores domain-level statistics for success rate estimation

CREATE TABLE IF NOT EXISTS domain_stats (
    domain VARCHAR(255) PRIMARY KEY COMMENT 'Domain name',
    total_requests INT DEFAULT 0 COMMENT 'Total fetch attempts',
    success_count INT DEFAULT 0 COMMENT 'Successful fetches',
    error_403_count INT DEFAULT 0 COMMENT 'HTTP 403 errors',
    error_timeout_count INT DEFAULT 0 COMMENT 'Timeout errors',
    error_other_count INT DEFAULT 0 COMMENT 'Other errors',
    avg_response_time INT DEFAULT 0 COMMENT 'Average response time in ms',
    last_success TIMESTAMP NULL COMMENT 'Last successful fetch',
    last_failure TIMESTAMP NULL COMMENT 'Last failed fetch',
    is_social_media TINYINT(1) DEFAULT 0 COMMENT 'Whether domain is social media',
    success_rate DECIMAL(5,2) GENERATED ALWAYS AS (
        CASE WHEN total_requests > 0
        THEN ROUND((success_count / total_requests) * 100, 2)
        ELSE 0 END
    ) STORED COMMENT 'Calculated success rate percentage',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_success_rate (success_rate),
    INDEX idx_social_media (is_social_media),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- MIGRATION 004: Create process_urls table
-- ================================================
-- Stores URLs for each process (for pause/resume functionality)

CREATE TABLE IF NOT EXISTS process_urls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    process_id VARCHAR(32) NOT NULL COMMENT 'Associated process ID',
    url VARCHAR(2048) NOT NULL COMMENT 'URL to process',
    domain VARCHAR(255) COMMENT 'Extracted domain',
    status ENUM('pending', 'processing', 'success', 'failed', 'skipped') DEFAULT 'pending' COMMENT 'URL processing status',
    phase INT DEFAULT 0 COMMENT 'Phase in which URL was processed',
    retry_count INT DEFAULT 0 COMMENT 'Number of retry attempts',
    http_code INT DEFAULT NULL COMMENT 'HTTP response code',
    response_time INT DEFAULT NULL COMMENT 'Response time in ms',
    error_message VARCHAR(500) DEFAULT NULL COMMENT 'Error message if failed',
    metadata_extracted TINYINT(1) DEFAULT 0 COMMENT 'Whether metadata was extracted',
    processed_at TIMESTAMP NULL COMMENT 'When URL was processed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_process (process_id),
    INDEX idx_status (status),
    INDEX idx_domain (domain),
    INDEX idx_process_status (process_id, status),

    FOREIGN KEY (process_id) REFERENCES process_history(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- MIGRATION 005: Create settings table
-- ================================================
-- Stores user/system settings

CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(100) PRIMARY KEY COMMENT 'Setting key',
    `value` TEXT COMMENT 'Setting value',
    `type` ENUM('string', 'int', 'float', 'bool', 'json') DEFAULT 'string' COMMENT 'Value type',
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'Setting description',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO settings (`key`, `value`, `type`, `description`) VALUES
    ('default_mode', 'standard', 'string', 'Default processing mode'),
    ('auto_export', 'true', 'bool', 'Auto-export results when complete'),
    ('browser_notifications', 'true', 'bool', 'Enable browser notifications'),
    ('include_failed_in_export', 'false', 'bool', 'Include failed URLs in export'),
    ('dashboard_refresh_interval', '10', 'int', 'Dashboard auto-refresh interval in seconds'),
    ('process_refresh_interval', '2', 'int', 'Processing page refresh interval in seconds'),
    ('max_concurrent_processes', '3', 'int', 'Maximum concurrent processes allowed'),
    ('activity_log_retention_days', '30', 'int', 'Days to keep activity logs')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- ================================================
-- VERIFICATION QUERIES
-- ================================================
-- Run these to verify migrations were successful

-- SELECT COUNT(*) as tables_created FROM information_schema.tables
-- WHERE table_schema = DATABASE()
-- AND table_name IN ('process_history', 'activity_log', 'domain_stats', 'process_urls', 'settings');

-- SHOW TABLES;

-- ================================================
-- END OF MIGRATIONS
-- ================================================
