-- Add device_id to 002 (ftt_*) telemetry tables. Idempotent: skips if column/index already exists.
-- Run after 003. Requires 002 to have been applied (ftt_app_logs, ftt_crash_logs, ftt_reports exist).

-- Add column to ftt_crash_logs if missing
SET @db = DATABASE();
SET @tbl = 'ftt_crash_logs';
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'device_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE ftt_crash_logs ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add column to ftt_app_logs if missing
SET @tbl = 'ftt_app_logs';
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'device_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE ftt_app_logs ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add column to ftt_reports if missing
SET @tbl = 'ftt_reports';
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'device_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE ftt_reports ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER user_token_hash', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes only if they don't exist (by index name)
SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ftt_crash_logs' AND INDEX_NAME = 'idx_crash_logs_device');
SET @sql = IF(@idx_exists = 0, 'CREATE INDEX idx_crash_logs_device ON ftt_crash_logs (device_id)', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ftt_app_logs' AND INDEX_NAME = 'idx_app_logs_device');
SET @sql = IF(@idx_exists = 0, 'CREATE INDEX idx_app_logs_device ON ftt_app_logs (device_id)', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ftt_reports' AND INDEX_NAME = 'idx_reports_device');
SET @sql = IF(@idx_exists = 0, 'CREATE INDEX idx_reports_device ON ftt_reports (device_id)', 'DO 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;