-- GPS Tracking: Location data, geofences, idle logging

CREATE TABLE IF NOT EXISTS gps_locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NULL,
    tracker_device_id VARCHAR(200) NULL,
    latitude DECIMAL(10,7) NOT NULL,
    longitude DECIMAL(10,7) NOT NULL,
    speed_kmh DECIMAL(6,1) DEFAULT 0,
    heading DECIMAL(5,1) DEFAULT 0,
    altitude INT DEFAULT 0,
    accuracy INT DEFAULT 0,
    ignition TINYINT(1) DEFAULT NULL,
    recorded_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gps_vehicle (vehicle_id, recorded_at),
    INDEX idx_gps_tracker (tracker_device_id, recorded_at),
    INDEX idx_gps_recorded (recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS geofence_zones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    zone_type ENUM('circle','polygon','rectangle') DEFAULT 'circle',
    center_lat DECIMAL(10,7) NULL,
    center_lng DECIMAL(10,7) NULL,
    radius_meters INT DEFAULT 100,
    polygon_data JSON NULL,
    geojson JSON NULL,
    color VARCHAR(7) DEFAULT '#3388ff',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_gz_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS geofence_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    geofence_id BIGINT UNSIGNED NOT NULL,
    vehicle_id BIGINT UNSIGNED NULL,
    event_type ENUM('entry','exit','dwell') NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    event_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ge_geofence (geofence_id, event_at),
    INDEX idx_ge_vehicle (vehicle_id, event_at),
    FOREIGN KEY (geofence_id) REFERENCES geofence_zones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('gps_tracking.view', 'View GPS tracking dashboard'),
('gps_tracking.history', 'View GPS route history'),
('gps_tracking.geofence', 'Manage geofence zones'),
('gps_tracking.api', 'Push GPS data via API');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code LIKE 'gps_tracking.%';