-- SQL to create the python_processing_log table
-- This table logs all tile processing operations for user statistics

CREATE TABLE `python_processing_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `job_id` varchar(255) NOT NULL,
  `unique_name` varchar(255) NOT NULL,
  `tile_type` enum('mbtiles','geojson','unknown') NOT NULL,
  `data_type` varchar(50) DEFAULT NULL,
  `original_filename` varchar(255) NOT NULL,
  `original_size_bytes` bigint(20) DEFAULT NULL,
  `original_size_human` varchar(50) DEFAULT NULL,
  `output_size_bytes` bigint(20) DEFAULT NULL,
  `output_size_human` varchar(50) DEFAULT NULL,
  `size_reduction_percent` decimal(5,2) DEFAULT NULL,
  `conversion_seconds` decimal(8,3) DEFAULT NULL,
  `feature_count` int(11) DEFAULT NULL,
  `layer_count` int(11) DEFAULT NULL,
  `layer_names` text DEFAULT NULL,
  `bounds` text DEFAULT NULL,
  `minzoom` int(11) DEFAULT NULL,
  `maxzoom` int(11) DEFAULT NULL,
  `status` enum('completed','error') NOT NULL DEFAULT 'completed',
  `error_message` text DEFAULT NULL,
  `converter` varchar(100) DEFAULT NULL,
  `converted_by` varchar(100) DEFAULT 'map.one',
  `command_line` text DEFAULT NULL,
  `processing_started_at` datetime DEFAULT NULL,
  `processing_finished_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_job_id` (`job_id`),
  KEY `idx_unique_name` (`unique_name`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_user_created` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Create a view for easy user statistics
CREATE VIEW `user_processing_stats` AS
SELECT 
    user_id,
    COUNT(*) as total_jobs,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_jobs,
    COUNT(CASE WHEN status = 'error' THEN 1 END) as failed_jobs,
    SUM(COALESCE(original_size_bytes, 0)) as total_original_bytes,
    SUM(COALESCE(output_size_bytes, 0)) as total_output_bytes,
    SUM(COALESCE(conversion_seconds, 0)) as total_processing_seconds,
    AVG(COALESCE(conversion_seconds, 0)) as avg_processing_seconds,
    SUM(COALESCE(layer_count, 0)) as total_layers,
    MAX(created_at) as last_used,
    MIN(created_at) as first_used
FROM python_processing_log 
GROUP BY user_id;
