-- ============================================================================
-- Mobile App Backend Upgradation - Direct MySQL Queries
-- Run these queries directly on your MySQL server
-- ============================================================================

-- 1. Add geofencing columns to location_list table
-- ============================================================================

ALTER TABLE `location_list` 
ADD COLUMN `latitude` DECIMAL(10, 8) NULL AFTER `loccode`,
ADD COLUMN `longitude` DECIMAL(11, 8) NULL AFTER `latitude`,
ADD COLUMN `geofence_radius` INT NOT NULL DEFAULT 1000 COMMENT 'Radius in meters' AFTER `longitude`,
ADD COLUMN `region` VARCHAR(100) NULL COMMENT 'Region grouping like Haridwar' AFTER `geofence_radius`;

-- 2. Add agent info and location tracking to ticket_booking table
-- ============================================================================
ALTER TABLE `ticket_booking` 
ADD COLUMN `agent_name` VARCHAR(255) NULL AFTER `partner_id`,
ADD COLUMN `agent_mobile` VARCHAR(20) NULL AFTER `agent_name`,
ADD COLUMN `agent_id` VARCHAR(50) NULL AFTER `agent_mobile`,
ADD COLUMN `booking_latitude` DECIMAL(10, 8) NULL COMMENT 'Agent location at booking' AFTER `agent_id`,
ADD COLUMN `booking_longitude` DECIMAL(11, 8) NULL COMMENT 'Agent location at booking' AFTER `booking_latitude`,
ADD COLUMN `location_validated_at` TIMESTAMP NULL AFTER `booking_longitude`;

-- 3. Modify customer_list table - make optional fields nullable
-- ============================================================================
ALTER TABLE `customer_list` 
MODIFY COLUMN `email` VARCHAR(255) NULL,
MODIFY COLUMN `state_id` BIGINT UNSIGNED NULL,
MODIFY COLUMN `district_id` BIGINT UNSIGNED NULL;

-- 4. Add persistent login fields to partners table
-- ============================================================================
ALTER TABLE `partners` 
ADD COLUMN `device_id` VARCHAR(255) NULL COMMENT 'For persistent login' AFTER `mobile`,
ADD COLUMN `fcm_token` VARCHAR(500) NULL COMMENT 'For push notifications' AFTER `device_id`,
ADD COLUMN `last_login_at` TIMESTAMP NULL AFTER `fcm_token`;

-- ============================================================================
-- Optional: Sample data for testing geofencing
-- ============================================================================

-- Update Chandi Devi location with coordinates (example)
-- UPDATE `location_list` 
-- SET `latitude` = 30.3165, 
--     `longitude` = 78.0322, 
--     `geofence_radius` = 1000,
--     `region` = 'Haridwar'
-- WHERE `loccode` = 'CHD';

-- Update Mansa Devi location with coordinates (example)
-- UPDATE `location_list` 
-- SET `latitude` = 30.3265, 
--     `longitude` = 78.0422, 
--     `geofence_radius` = 1000,
--     `region` = 'Haridwar'
-- WHERE `loccode` = 'MNS';

-- ============================================================================
-- Verification Queries
-- ============================================================================

-- Check if columns were added to location_list table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'location_list' 
AND COLUMN_NAME IN ('latitude', 'longitude', 'geofence_radius', 'region')
ORDER BY ORDINAL_POSITION;

-- Check if columns were added to ticket_booking table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ticket_booking' 
AND COLUMN_NAME IN ('agent_name', 'agent_mobile', 'agent_id', 'booking_latitude', 'booking_longitude', 'location_validated_at')
ORDER BY ORDINAL_POSITION;

-- Check if columns were added to partners table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'partners' 
AND COLUMN_NAME IN ('device_id', 'fcm_token', 'last_login_at')
ORDER BY ORDINAL_POSITION;

-- Check customer_list nullable columns
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customer_list' 
AND COLUMN_NAME IN ('email', 'state_id', 'district_id')
ORDER BY ORDINAL_POSITION;

-- ============================================================================
-- IMPORTANT NOTES:
-- ============================================================================
-- 1. Backup your database before running these queries
-- 2. Run queries one section at a time
-- 3. Check for errors after each ALTER TABLE statement
-- 4. Use verification queries to confirm changes
-- 5. Update location coordinates via admin panel after running queries
-- ============================================================================
