CREATE TABLE IF NOT EXISTS vehicles (
 id INT AUTO_INCREMENT PRIMARY KEY,
 vehicle_no VARCHAR(64) NOT NULL UNIQUE,
 plate_no VARCHAR(64) NULL,
 operator VARCHAR(128) NULL,
 vin VARCHAR(64) NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sensors (
 id INT AUTO_INCREMENT PRIMARY KEY,
 vehicle_id INT NULL,
 name VARCHAR(128) NOT NULL,
 ip VARCHAR(64) NOT NULL,
 port INT NOT NULL DEFAULT 5211,
 ibis_version VARCHAR(16) NOT NULL DEFAULT '2.1',
 door_no INT NOT NULL DEFAULT 1,
 api_token VARCHAR(128) NULL,
 last_seen DATETIME NULL,
 last_status VARCHAR(32) DEFAULT 'unknown',
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(vehicle_id), CONSTRAINT fk_sensors_vehicle FOREIGN KEY(vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS subscriptions (
 id INT AUTO_INCREMENT PRIMARY KEY,
 sensor_id INT NOT NULL,
 reply_url VARCHAR(255) NOT NULL,
 status VARCHAR(32) NOT NULL DEFAULT 'created',
 response_code INT NULL,
 response_body TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(sensor_id), CONSTRAINT fk_sub_sensor FOREIGN KEY(sensor_id) REFERENCES sensors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS counting_records (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 sensor_id INT NULL,
 vehicle_id INT NULL,
 sensor_ip VARCHAR(64) NULL,
 xml_timestamp DATETIME NULL,
 received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 raw_xml MEDIUMTEXT NOT NULL,
 INDEX(sensor_id), INDEX(vehicle_id), INDEX(received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS counting_events (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 record_id BIGINT NOT NULL,
 sensor_id INT NULL,
 vehicle_id INT NULL,
 door_no INT NULL,
 object_class VARCHAR(64) NOT NULL DEFAULT 'Adult',
 in_count INT NOT NULL DEFAULT 0,
 out_count INT NOT NULL DEFAULT 0,
 quality VARCHAR(64) NULL,
 open_state VARCHAR(64) NULL,
 event_time DATETIME NULL,
 received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(record_id), INDEX(sensor_id), INDEX(vehicle_id), INDEX(event_time),
 CONSTRAINT fk_event_record FOREIGN KEY(record_id) REFERENCES counting_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS app_logs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 level VARCHAR(16) NOT NULL DEFAULT 'info',
 message TEXT NOT NULL,
 context TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS gtfs_routes (route_id VARCHAR(128) PRIMARY KEY, short_name VARCHAR(128), long_name VARCHAR(255));
CREATE TABLE IF NOT EXISTS gtfs_stops (stop_id VARCHAR(128) PRIMARY KEY, stop_name VARCHAR(255), lat DECIMAL(10,7) NULL, lon DECIMAL(10,7) NULL);