db_template.sql

CREATE TABLE IF NOT EXISTS `system` (
    `key` VARCHAR(20) NOT NULL PRIMARY KEY,
    `value` VARCHAR(20) NOT NULL
);

INSERT INTO `system` VALUES("version", 1) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);

-- accounts table structure
CREATE TABLE IF NOT EXISTS `accounts` (
    `akey` VARCHAR(6) NOT NULL PRIMARY KEY,
    `pw_hash` VARCHAR(255) NOT NULL,
    `lastactivity` INT(10) DEFAULT 0,
    `token` VARCHAR(20) NOT NULL UNIQUE
);

-- settings table structure
CREATE TABLE IF NOT EXISTS `settings` (
    `user` VARCHAR(6) NOT NULL,
    `akey` VARCHAR(6) NOT NULL,
    `webhook` VARCHAR(100) DEFAULT NULL,
    `telegram` INT(100) DEFAULT 0,
    `abrp` VARCHAR(36) DEFAULT NULL,
    `summary` TINYINT(1) DEFAULT 0,
    `soc` INT(3) DEFAULT 70,
    `lng` VARCHAR(20) DEFAULT 'en',
    `push` TINYINT(1) DEFAULT 0,
    `car` VARCHAR(100) DEFAULT NULL,
    `consumption` FLOAT(99, 2) DEFAULT 13,
    `device` VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (`user`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

CREATE TABLE IF NOT EXISTS `notificationMail` (
    `akey` VARCHAR(6) NOT NULL,
    `mail` VARCHAR(1000) NOT NULL,
    `verified` BOOLEAN NOT NULL DEFAULT FALSE,
    `identifier` BINARY(16) NOT NULL,
    PRIMARY KEY (`akey`),
    UNIQUE KEY (`identifier`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

CREATE TABLE IF NOT EXISTS `mailLock` (
    `hash` BINARY(32) NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `weight` INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (`hash`)
);

-- sync table structure
CREATE TABLE IF NOT EXISTS `sync` (
    `user` VARCHAR(6) NOT NULL,
    `akey` VARCHAR(6) NOT NULL,
    `soc_display` FLOAT DEFAULT 0,
    `soc_bms` FLOAT DEFAULT 0,
    `soh` FLOAT DEFAULT 0,
    `charging` TINYINT(1) DEFAULT 0,
    `rapid_charge_port` TINYINT(1) DEFAULT 0,
    `normal_charge_port` TINYINT(1) DEFAULT 0,
    `slow_charge_port` TINYINT(1) DEFAULT 0,
    `aux_battery_voltage` FLOAT DEFAULT 0,
    `dc_battery_voltage` FLOAT DEFAULT 0,
    `dc_battery_current` FLOAT DEFAULT 0,
    `dc_battery_power` FLOAT DEFAULT 0,
    `cumulative_energy_charged` FLOAT DEFAULT 0,
    `cumulative_energy_discharged` FLOAT DEFAULT 0,
    `battery_min_temperature` FLOAT DEFAULT 0,
    `battery_max_temperature` FLOAT DEFAULT 0,
    `battery_inlet_temperature` FLOAT DEFAULT 0,
    `latitude` DECIMAL(10, 8) DEFAULT 0,
    `longitude` DECIMAL(11, 8) DEFAULT 0,
    `gps_speed` FLOAT DEFAULT 0,
    `accuracy` INT(100) DEFAULT 0,
    `location_timestamp` INT(20) DEFAULT 0,
    `last_soc` INT(13) DEFAULT 0,
    `last_extended` INT(13) DEFAULT 0,
    `last_notification` INT(13) DEFAULT 0,
    `last_location` INT(13) DEFAULT 0,
    `last_qr` INT(13) DEFAULT 0,
    PRIMARY KEY (`user`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

-- statistics table structure
CREATE TABLE IF NOT EXISTS `statistics` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `akey` VARCHAR(6) NOT NULL,
    `soc_display` FLOAT DEFAULT NULL,
    `soc_bms` FLOAT DEFAULT NULL,
    `soh` FLOAT DEFAULT NULL,
    `charging` TINYINT(1) DEFAULT NULL,
    `rapid_charge_port` TINYINT(1) DEFAULT NULL,
    `normal_charge_port` TINYINT(1) DEFAULT NULL,
    `slow_charge_port` TINYINT(1) DEFAULT NULL,
    `aux_battery_voltage` FLOAT DEFAULT NULL,
    `dc_battery_voltage` FLOAT DEFAULT NULL,
    `dc_battery_current` FLOAT DEFAULT NULL,
    `dc_battery_power` FLOAT DEFAULT NULL,
    `cumulative_energy_charged` FLOAT DEFAULT NULL,
    `cumulative_energy_discharged` FLOAT DEFAULT NULL,
    `battery_min_temperature` FLOAT DEFAULT NULL,
    `battery_max_temperature` FLOAT DEFAULT NULL,
    `battery_inlet_temperature` FLOAT DEFAULT NULL,
    `latitude` DECIMAL(10, 8) DEFAULT NULL,
    `longitude` DECIMAL(11, 8) DEFAULT NULL,
    `gps_speed` FLOAT DEFAULT NULL,
    `accuracy` INT(100) DEFAULT NULL,
    `location_timestamp` INT(20) DEFAULT NULL,
    `timestamp` INT(13) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

-- qr table structure
CREATE TABLE IF NOT EXISTS `qr` (
    `code` VARCHAR(12) NOT NULL UNIQUE,
    `akey` VARCHAR(6) NOT NULL,
    PRIMARY KEY (`code`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

-- debug table structure
CREATE TABLE IF NOT EXISTS `debug` (
    `id` int NOT NULL AUTO_INCREMENT,
    `akey` VARCHAR(6) NOT NULL,
    `data` MEDIUMTEXT DEFAULT NULL,
    `timestamp` int(10) DEFAULT 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

-- logs table structure
CREATE TABLE IF NOT EXISTS `logs` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `akey` VARCHAR(6) NOT NULL,
    `start` INT(10) DEFAULT 0,
    `end` INT(10) DEFAULT 0,
    `charge` TINYINT(1) DEFAULT 0,
    `autogenerated` TINYINT(1) DEFAULT 1,
    `archived` TINYINT(1) DEFAULT 0,
    `title` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);

-- login table structure for web interface
CREATE TABLE IF NOT EXISTS `login` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mail` VARCHAR(100) NOT NULL,
    `pw_hash` VARCHAR(255) NOT NULL,
    `last_login` INT(10) DEFAULT 0,
    PRIMARY KEY (`id`)
);

-- devices table structure for web interface
CREATE TABLE IF NOT EXISTS `devices` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user` INT NOT NULL,
    `akey` VARCHAR(6) NOT NULL UNIQUE,
    `alias` VARCHAR(255) DEFAULT NULL,
    `token` VARCHAR(20) NOT NULL UNIQUE,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES `login`(`id`),
    FOREIGN KEY (`akey`) REFERENCES `accounts`(`akey`)
);