SQL

database.sql

CREATE TABLE `origen_vendings_groups` (
    `id`          INT          NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(100) NOT NULL,
    `category`    ENUM('drinks','snacks','coffee','cigarettes','custom') NOT NULL DEFAULT 'drinks',
    `enabled`     TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_category` (`category`),
    KEY `idx_enabled`  (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration (run once on existing databases):
-- ALTER TABLE `origen_vendings_groups`
--   MODIFY `category` ENUM('drinks','snacks','coffee','cigarettes','custom') NOT NULL DEFAULT 'drinks';

CREATE TABLE `origen_vendings_group_props` (
    `id`         INT          NOT NULL AUTO_INCREMENT,
    `group_id`   INT          NOT NULL,
    `prop_model` VARCHAR(100) NOT NULL,
    `prop_label` VARCHAR(150) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_group_prop` (`group_id`, `prop_model`),
    KEY `idx_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration (run once on existing databases):
-- ALTER TABLE `origen_vendings_group_props`
--   ADD COLUMN `prop_label` VARCHAR(150) NOT NULL DEFAULT '' AFTER `prop_model`;

CREATE TABLE `origen_vendings_group_items` (
    `id`         INT           NOT NULL AUTO_INCREMENT,
    `group_id`   INT           NOT NULL,
    `item_name`  VARCHAR(100)  NOT NULL,
    `label`      VARCHAR(150)  NOT NULL,
    `price`      INT           NOT NULL DEFAULT 0,
    `use_stock`  TINYINT(1)    NOT NULL DEFAULT 0,
    `stock`      INT           DEFAULT NULL,
    `created_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_group_item` (`group_id`, `item_name`),
    KEY `idx_group`    (`group_id`),
    KEY `idx_item`     (`item_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `origen_vendings_machines` (
    `id`          INT           NOT NULL AUTO_INCREMENT,
    `group_id`    INT           NOT NULL,
    `name`        VARCHAR(150)  NOT NULL,
    `prop_model`  VARCHAR(100)  NOT NULL,
    `x`           FLOAT         NOT NULL DEFAULT 0,
    `y`           FLOAT         NOT NULL DEFAULT 0,
    `z`           FLOAT         NOT NULL DEFAULT 0,
    `heading`     FLOAT         NOT NULL DEFAULT 0,
    `spawn_prop`  TINYINT(1)    NOT NULL DEFAULT 1,
    `enabled`     TINYINT(1)    NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_group_id` (`group_id`),
    KEY `idx_enabled`  (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;