-- Create user_tile_link_names table for custom tile naming
-- This allows users to create friendly names for their tiles

CREATE TABLE IF NOT EXISTS `user_tile_link_names` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `tile_unique_name` VARCHAR(255) NOT NULL,
    `link_name` VARCHAR(255) NOT NULL,
    `full_link_name` VARCHAR(255) NOT 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_tile_unique_name` (`tile_unique_name`),
    INDEX `idx_full_link_name` (`full_link_name`),
    INDEX `idx_user_tile` (`user_id`, `tile_unique_name`),
    
    -- Unique constraints
    UNIQUE KEY `unique_full_link_name` (`full_link_name`),
    UNIQUE KEY `unique_user_tile_link` (`user_id`, `tile_unique_name`)
    
    -- Note: Foreign key constraint removed to avoid dependency issues
    -- The application will handle referential integrity
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add some example data for testing (optional - uncomment to use)
-- INSERT INTO `user_tile_link_names` (`user_id`, `tile_unique_name`, `link_name`, `full_link_name`) VALUES
-- (1, '20251011T174432Z-9a9a7a29', 'my-lakes', 'link_1_my-lakes'),
-- (1, '20251011T174533Z-4a934321', 'satellite-imagery', 'link_1_satellite-imagery');
