-- =============================================================
-- Migration: Add tracking session support for Swiggy/Zomato-style path
-- Phase 1: tracking_sessions table
-- Phase 2: alter agent_location_tracking
-- Phase 3: tracking_geofence_events table
-- =============================================================

START TRANSACTION;

-- 1. Create tracking_sessions table
CREATE TABLE IF NOT EXISTS tracking_sessions (
  id VARCHAR(36) NOT NULL,
  agent_id INT NOT NULL,
  com_id INT NOT NULL,
  socket_id VARCHAR(100) NULL,
  device_id VARCHAR(100) NULL,
  started_at DATETIME NOT NULL COMMENT 'device timestamp when session started',
  ended_at DATETIME NULL COMMENT 'device timestamp when session ended',
  received_connected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'server received_at for session start',
  received_disconnected_at DATETIME NULL COMMENT 'server received_at for session end',
  start_latitude DECIMAL(10,8) NULL,
  start_longitude DECIMAL(11,8) NULL,
  end_latitude DECIMAL(10,8) NULL,
  end_longitude DECIMAL(11,8) NULL,
  distance_meters DECIMAL(10,2) NOT NULL DEFAULT 0,
  duration_seconds INT NOT NULL DEFAULT 0,
  point_count INT NOT NULL DEFAULT 0,
  average_speed_kmh DECIMAL(5,2) NULL,
  max_speed_kmh DECIMAL(5,2) NULL,
  disconnect_reason ENUM('user_stopped','network_loss','timeout','app_background','manual') NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_session_agent (agent_id),
  INDEX idx_session_com (com_id),
  INDEX idx_session_agent_time (agent_id, started_at),
  INDEX idx_session_status (ended_at, agent_id),
  CONSTRAINT fk_session_agent FOREIGN KEY (agent_id) REFERENCES collection_agent(id) ON DELETE CASCADE,
  CONSTRAINT fk_session_company FOREIGN KEY (com_id) REFERENCES company(com_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2. Alter agent_location_tracking — add session + metadata columns
SET @exist_check := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME='agent_location_tracking' AND COLUMN_NAME='tracking_session_id');
SET @sql := IF(@exist_check = 0,
  'ALTER TABLE agent_location_tracking
    ADD COLUMN tracking_session_id VARCHAR(36) NULL AFTER id,
    ADD COLUMN recorded_at DATETIME NULL AFTER created_at,
    ADD COLUMN received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER recorded_at,
    ADD COLUMN battery_level DECIMAL(5,2) NULL AFTER received_at,
    ADD COLUMN battery_charging TINYINT(1) NULL AFTER battery_level,
    ADD COLUMN gps_provider VARCHAR(20) NULL AFTER battery_charging,
    ADD COLUMN mock_location TINYINT(1) NULL AFTER gps_provider,
    ADD INDEX idx_session_loc (tracking_session_id, recorded_at),
    ADD INDEX idx_agent_recorded (agent_id, recorded_at)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Migrate existing rows: set recorded_at = created_at where recorded_at IS NULL
UPDATE agent_location_tracking SET recorded_at = created_at WHERE recorded_at IS NULL;

-- 4. Create tracking_geofence_events table
CREATE TABLE IF NOT EXISTS tracking_geofence_events (
  id INT NOT NULL AUTO_INCREMENT,
  tracking_session_id VARCHAR(36) NULL,
  agent_id INT NOT NULL,
  com_id INT NOT NULL,
  event_type ENUM('arrived_store','left_store','arrived_customer','left_customer','delivery_completed','pause','resume') NOT NULL,
  latitude DECIMAL(10,8) NULL,
  longitude DECIMAL(11,8) NULL,
  accuracy DECIMAL(10,2) NULL,
  recorded_at DATETIME NULL COMMENT 'device timestamp',
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_geofence_session (tracking_session_id, recorded_at),
  INDEX idx_geofence_agent_time (agent_id, recorded_at),
  INDEX idx_geofence_type (event_type),
  CONSTRAINT fk_geofence_session FOREIGN KEY (tracking_session_id) REFERENCES tracking_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_geofence_agent FOREIGN KEY (agent_id) REFERENCES collection_agent(id) ON DELETE CASCADE,
  CONSTRAINT fk_geofence_company FOREIGN KEY (com_id) REFERENCES company(com_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

COMMIT;