-- SQL script to create tables used by the Battery Health Integration (BHI) features

CREATE TABLE IF NOT EXISTS op_bhi_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    direction VARCHAR(10) NOT NULL,
    action VARCHAR(100) DEFAULT '',
    payload TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS op_bhi_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    message_count INT NOT NULL,
    start_ts TIMESTAMP NULL,
    end_ts TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS op_bhi_session_meta (
    id INT PRIMARY KEY,
    last_msg_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS op_bhi_odometer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    value INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS op_bhi_soc (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    value INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ensure op_redirects has a flag to control MQTT forwarding
ALTER TABLE op_redirects
    ADD COLUMN IF NOT EXISTS mqtt_enabled TINYINT(1) NOT NULL DEFAULT 0;
