-- Tracker Integration (connect any tracking software API)
CREATE TABLE IF NOT EXISTS tracker_integrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    provider VARCHAR(50) NOT NULL DEFAULT 'generic',
    base_url VARCHAR(500) NULL,
    api_key VARCHAR(500) NULL,
    api_secret VARCHAR(500) NULL,
    auth_type VARCHAR(30) NOT NULL DEFAULT 'header',
    additional_config JSON NULL,
    poll_interval_seconds INT NOT NULL DEFAULT 300,
    status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
    last_checked_at DATETIME NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tracker_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracker_device_mappings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    tracker_device_id VARCHAR(200) NOT NULL,
    tracker_device_name VARCHAR(255) NULL,
    vehicle_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mapping_integration (integration_id),
    KEY idx_mapping_vehicle (vehicle_id),
    CONSTRAINT fk_mapping_integration FOREIGN KEY (integration_id) REFERENCES tracker_integrations(id) ON DELETE CASCADE,
    CONSTRAINT fk_mapping_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions
INSERT IGNORE INTO permissions (code, description) VALUES
('tracker_integration.view', 'View tracker integrations'),
('tracker_integration.create', 'Create tracker integrations'),
('tracker_integration.edit', 'Edit tracker integrations'),
('tracker_integration.delete', 'Delete tracker integrations'),
('tracker_integration.devices', 'Manage device mappings');

-- Grant to all roles
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE p.code IN (
    'tracker_integration.view','tracker_integration.create',
    'tracker_integration.edit','tracker_integration.delete',
    'tracker_integration.devices'
);