-- Initialize database with required extensions and default data
-- Run this script on fresh MySQL installation

-- Create database if not exists (usually done by Docker)
-- CREATE DATABASE IF NOT EXISTS transporter CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Enable spatial extensions (for geolocation queries)
-- MySQL 8.0+ has spatial functions built-in

-- Create indexes for common queries (optional, Alembic handles most)
-- These can be run after initial migration if needed

-- Insert default system configurations
INSERT INTO system_configs (`key`, `value`, `value_type`, `category`, `label`, `description`, `is_editable`) VALUES
('app_name', 'Transporter', 'string', 'general', 'Application Name', 'Name of the application', true),
('currency', 'USD', 'string', 'general', 'Currency', 'Default currency for transactions', true),
('default_map_provider', 'mapbox', 'string', 'maps', 'Default Map Provider', 'Default map provider to use', true),
('driver_search_radius_km', '10', 'float', 'ride', 'Driver Search Radius', 'Maximum distance to search for drivers (km)', true),
('bid_expiry_seconds', '60', 'integer', 'ride', 'Bid Expiry Time', 'Time before fare bids expire (seconds)', true),
('default_commission_percentage', '15', 'float', 'payment', 'Commission Rate', 'Platform commission percentage', true),
('min_wallet_topup', '500', 'integer', 'payment', 'Minimum Top-up', 'Minimum wallet top-up amount (cents)', true),
('min_withdrawal', '1000', 'integer', 'payment', 'Minimum Withdrawal', 'Minimum withdrawal amount (cents)', true)
ON DUPLICATE KEY UPDATE `key` = `key`;

-- Insert default map provider configurations
INSERT INTO map_provider_configs (`provider_name`, `display_name`, `description`, `is_active`, `priority`) VALUES
('mapbox', 'Mapbox', 'Mapbox Maps and Navigation API', true, 100),
('google_maps', 'Google Maps', 'Google Maps Platform API', false, 90),
('openstreetmap', 'OpenStreetMap', 'Open source map data', false, 50)
ON DUPLICATE KEY UPDATE `provider_name` = `provider_name`;

-- Insert default vehicle categories
INSERT INTO vehicle_categories (`name`, `display_name`, `description`, `max_passengers`, `base_fare`, `per_km_rate`, `per_minute_rate`, `minimum_fare`, `icon_url`, `sort_order`) VALUES
('economy', 'Economy', 'Affordable everyday rides', 4, 200, 100, 15, 500, '/icons/economy.png', 1),
('comfort', 'Comfort', 'More space and comfort', 4, 350, 150, 20, 800, '/icons/comfort.png', 2),
('premium', 'Premium', 'Premium vehicles for luxury rides', 4, 500, 250, 35, 1500, '/icons/premium.png', 3),
('suv', 'SUV', 'Spacious SUV for groups', 6, 450, 200, 30, 1200, '/icons/suv.png', 4),
('xl', 'XL', 'Extra large vehicles', 7, 600, 300, 40, 2000, '/icons/xl.png', 5)
ON DUPLICATE KEY UPDATE `name` = `name`;