-- Leistungspositionen-Bibliothek
-- Ausführen in: phpMyAdmin → mxnbau_mgt

CREATE TABLE IF NOT EXISTS `pms_service_position_categories` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trade_nr`   VARCHAR(3)   NOT NULL UNIQUE COMMENT 'Gewerk-Nummer z.B. 012',
    `name`       VARCHAR(100) NOT NULL        COMMENT 'Gewerk-Name',
    `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP    NULL DEFAULT NULL,
    `updated_at` TIMESTAMP    NULL DEFAULT NULL,
    INDEX `idx_trade_nr` (`trade_nr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pms_service_positions` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT UNSIGNED NOT NULL,
    `trade_nr`    VARCHAR(3)   NOT NULL         COMMENT 'Denorm. für schnellen Filter',
    `position_nr` VARCHAR(11)  NOT NULL UNIQUE  COMMENT 'Eindeutige Positionsnummer',
    `search_term` VARCHAR(100) NULL             COMMENT 'Suchbegriff Großbuchstaben',
    `short_text`  VARCHAR(255) NOT NULL         COMMENT 'Kurztext Deutsch',
    `unit`        VARCHAR(10)  NULL             COMMENT 'Mengeneinheit',
    `unit_price`  DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Richtpreis',
    `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP    NULL DEFAULT NULL,
    `updated_at`  TIMESTAMP    NULL DEFAULT NULL,
    INDEX `idx_trade_nr`  (`trade_nr`),
    INDEX `idx_short_text`(`short_text`(50)),
    CONSTRAINT `fk_sp_category`
        FOREIGN KEY (`category_id`) REFERENCES `pms_service_position_categories`(`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
