-- Migration 046: Device registration hardening + batch app logs table
-- Safe to re-run (uses IF NOT EXISTS / IF NOT EXISTS guards).

-- ── ftt_devices: ensure device_id is the primary key and all columns exist ─────

CREATE TABLE IF NOT EXISTS ftt_devices (
    device_id VARCHAR(128) NOT NULL PRIMARY KEY,
    install_uuid VARCHAR(64) NOT NULL DEFAULT '',
    device_name VARCHAR(255) NOT NULL DEFAULT '',
    platform VARCHAR(32) NOT NULL DEFAULT 'android',
    app_version VARCHAR(32) NOT NULL DEFAULT '',
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    registered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    extra_json TEXT
);

-- Add columns if missing (ALTER TABLE … ADD COLUMN IF NOT EXISTS is MySQL 8+;
-- for MariaDB compatibility we use a stored-proc guard)
DELIMITER $$
CREATE PROCEDURE ftt_046_add_col(IN tbl VARCHAR(64), IN col VARCHAR(64), IN def TEXT)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = tbl AND column_name = col
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', tbl, '` ADD COLUMN `', col, '` ', def);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

CALL ftt_046_add_col('ftt_devices', 'install_uuid',   "VARCHAR(64)  NOT NULL DEFAULT ''");
CALL ftt_046_add_col('ftt_devices', 'device_name',    "VARCHAR(255) NOT NULL DEFAULT ''");
CALL ftt_046_add_col('ftt_devices', 'platform',       "VARCHAR(32)  NOT NULL DEFAULT 'android'");
CALL ftt_046_add_col('ftt_devices', 'app_version',    "VARCHAR(32)  NOT NULL DEFAULT ''");
CALL ftt_046_add_col('ftt_devices', 'last_seen',      "DATETIME DEFAULT CURRENT_TIMESTAMP");
CALL ftt_046_add_col('ftt_devices', 'registered_at',  "DATETIME DEFAULT CURRENT_TIMESTAMP");
CALL ftt_046_add_col('ftt_devices', 'extra_json',     "TEXT");

DROP PROCEDURE IF EXISTS ftt_046_add_col;

-- ── ftt_app_logs: batch app log table used by add_log_batch.php ─────────────

CREATE TABLE IF NOT EXISTS ftt_app_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL DEFAULT '',
    install_uuid VARCHAR(64) NOT NULL DEFAULT '',
    level VARCHAR(10) NOT NULL DEFAULT 'INFO',
    tag VARCHAR(64) NOT NULL DEFAULT 'FTT',
    message TEXT NOT NULL,
    detail TEXT,
    tab_id VARCHAR(64),
    batch_ts BIGINT UNSIGNED DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_device (device_id),
    INDEX idx_created (created_at),
    INDEX idx_level (level)
);