Database

-- tc-mechanic : schema complet / full schema (idempotent, a executer sur la base du serveur)
-- Les tables sont aussi creees automatiquement au demarrage de la ressource (sv_bootstrap.lua lit ce fichier).
-- Tables are also created automatically at resource start (sv_bootstrap.lua reads this file).
-- Ne pas ajouter de point-virgule dans les commentaires / no semicolon inside comments.

CREATE TABLE IF NOT EXISTS `tc_mech_settings` (
    `skey`       VARCHAR(64)  NOT NULL,
    `svalue`     LONGTEXT     NOT NULL,
    `updated_by` VARCHAR(96)  DEFAULT NULL,
    `updated_at` INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tc_mech_points` (
    `id`         INT          NOT NULL AUTO_INCREMENT,
    `job`        VARCHAR(64)  NOT NULL,
    `ptype`      VARCHAR(16)  NOT NULL,
    `label`      VARCHAR(64)  NOT NULL,
    `x`          DOUBLE       NOT NULL,
    `y`          DOUBLE       NOT NULL,
    `z`          DOUBLE       NOT NULL,
    `h`          DOUBLE       NOT NULL DEFAULT 0,
    `radius`     FLOAT        NOT NULL DEFAULT 2.5,
    `blip`       TINYINT      NOT NULL DEFAULT 0,
    `enabled`    TINYINT      NOT NULL DEFAULT 1,
    `created_by` VARCHAR(96)  DEFAULT NULL,
    `created_at` INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_job` (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tc_mech_orders` (
    `id`             INT          NOT NULL AUTO_INCREMENT,
    `job`            VARCHAR(64)  NOT NULL,
    `otype`          VARCHAR(16)  NOT NULL,
    `status`         VARCHAR(16)  NOT NULL,
    `plate`          VARCHAR(16)  NOT NULL,
    `model`          VARCHAR(32)  NOT NULL DEFAULT '',
    `vehicle_label`  VARCHAR(64)  NOT NULL DEFAULT '',
    `veh_class`      INT          NOT NULL DEFAULT 0,
    `is_bike`        TINYINT      NOT NULL DEFAULT 0,
    `customer_ref`   VARCHAR(64)  NOT NULL DEFAULT '',
    `customer_name`  VARCHAR(96)  NOT NULL DEFAULT '',
    `mechanic_ident` VARCHAR(96)  DEFAULT NULL,
    `mechanic_name`  VARCHAR(96)  DEFAULT NULL,
    `point_id`       INT          DEFAULT NULL,
    `target`         LONGTEXT     NOT NULL,
    `quote_lines`    LONGTEXT     NOT NULL,
    `total`          INT          NOT NULL DEFAULT 0,
    `pay_method`     VARCHAR(12)  DEFAULT NULL,
    `note`           VARCHAR(255) DEFAULT NULL,
    `created_at`     INT          NOT NULL DEFAULT 0,
    `updated_at`     INT          NOT NULL DEFAULT 0,
    `closed_at`      INT          DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_job_status` (`job`, `status`),
    KEY `idx_plate` (`plate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tc_mech_order_steps` (
    `id`         INT          NOT NULL AUTO_INCREMENT,
    `order_id`   INT          NOT NULL,
    `idx`        INT          NOT NULL,
    `kind`       VARCHAR(12)  NOT NULL,
    `ref`        VARCHAR(32)  NOT NULL,
    `label`      VARCHAR(96)  NOT NULL,
    `data`       LONGTEXT     NOT NULL,
    `status`     VARCHAR(12)  NOT NULL DEFAULT 'pending',
    `done_by`    VARCHAR(96)  DEFAULT NULL,
    `done_at`    INT          DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tc_mech_vehicles` (
    `plate`      VARCHAR(16)  NOT NULL,
    `mileage`    DOUBLE       NOT NULL DEFAULT 0,
    `wear`       LONGTEXT     NOT NULL,
    `extras`     LONGTEXT     NOT NULL,
    `updated_at` INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`plate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tc_mech_journal` (
    `id`          INT          NOT NULL AUTO_INCREMENT,
    `category`    VARCHAR(16)  NOT NULL,
    `job`         VARCHAR(64)  DEFAULT NULL,
    `plate`       VARCHAR(16)  DEFAULT NULL,
    `order_id`    INT          DEFAULT NULL,
    `actor_ident` VARCHAR(96)  DEFAULT NULL,
    `actor_name`  VARCHAR(96)  DEFAULT NULL,
    `action`      VARCHAR(48)  NOT NULL,
    `detail`      TEXT         DEFAULT NULL,
    `mileage`     DOUBLE       DEFAULT NULL,
    `created_at`  INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_plate` (`plate`),
    KEY `idx_job` (`job`),
    KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Caisse interne : utilisee uniquement si aucun systeme de societe n'est detecte (Config.Bridge.Society = 'internal')
CREATE TABLE IF NOT EXISTS `tc_mech_funds` (
    `job`        VARCHAR(64)  NOT NULL,
    `balance`    BIGINT       NOT NULL DEFAULT 0,
    `updated_at` INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;