-- Create user_tile_service_token table for tile access control
CREATE TABLE IF NOT EXISTS `user_tile_service_token` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `token_name` VARCHAR(255) NOT NULL,
    `token_id` VARCHAR(255) UNIQUE NOT NULL,
    `allowed_origins` TEXT NULL COMMENT 'Comma-separated list of allowed CORS origins',
    `allowed_referers` TEXT NULL COMMENT 'Comma-separated list of allowed referer URLs',
    `is_active` BOOLEAN DEFAULT TRUE,
    `max_requests_per_hour` INT DEFAULT 1000,
    `total_requests` BIGINT DEFAULT 0,
    `last_used` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_token_id` (`token_id`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_expires_at` (`expires_at`),
    
    -- Foreign key constraint
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create tile_service_usage_log table for tracking usage
CREATE TABLE IF NOT EXISTS `tile_service_usage_log` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `token_id` VARCHAR(255) NOT NULL,
    `user_id` INT NOT NULL,
    `tile_name` VARCHAR(255) NOT NULL,
    `request_method` VARCHAR(10) NOT NULL,
    `request_path` VARCHAR(500) NOT NULL,
    `origin` VARCHAR(500) NULL,
    `referer` VARCHAR(500) NULL,
    `user_agent` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `response_status` INT NOT NULL,
    `response_size_bytes` BIGINT DEFAULT 0,
    `request_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes for performance and analytics
    INDEX `idx_token_id` (`token_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_request_time` (`request_time`),
    INDEX `idx_tile_name` (`tile_name`),
    INDEX `idx_response_status` (`response_status`),
    
    -- Composite indexes for common queries
    INDEX `idx_token_time` (`token_id`, `request_time`),
    INDEX `idx_user_time` (`user_id`, `request_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for testing (optional)
-- INSERT INTO `user_tile_service_token` (`user_id`, `token_name`, `token_id`, `allowed_origins`, `allowed_referers`)
-- VALUES (1, 'Development Token', 'tst_dev_' + SUBSTRING(MD5(RAND()), 1, 32), 'http://localhost:3000,http://localhost:8080', 'http://localhost:3000/*');
