-- ============================================================
-- PMS – Neue Tabellen für bestehende Datenbank ausführen
-- Nur ausführen wenn Tabellen noch NICHT existieren!
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- 1. Nummernkreis AN- / AU-
CREATE TABLE IF NOT EXISTS `pms_nr_counter` (
  `id`       int NOT NULL AUTO_INCREMENT,
  `type`     enum('offer','order') NOT NULL        COMMENT 'Typ: Angebot oder Auftrag',
  `year`     smallint NOT NULL                     COMMENT '2-stellig, z.B. 26 für 2026',
  `last_nr`  int NOT NULL DEFAULT '0'              COMMENT 'Letzte vergebene Nummer',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_type_year` (`type`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2. Projektkategorien 200 / 400 / 600 / 800
CREATE TABLE IF NOT EXISTS `pms_project_category` (
  `id`          int NOT NULL AUTO_INCREMENT,
  `nr_prefix`   smallint NOT NULL                  COMMENT 'Nummernkreis: 200 / 400 / 600 / 800',
  `name`        varchar(100) NOT NULL              COMMENT 'z.B. Interne Projekte',
  `description` text                               COMMENT 'Beschreibung der Kategorie',
  `sort`        tinyint NOT NULL DEFAULT '0',
  `is_deleted`  tinyint(1) NOT NULL DEFAULT '0',
  `created_at`  timestamp NULL DEFAULT NULL,
  `updated_at`  timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_nr_prefix` (`nr_prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 3. Angebotstypen je Kategorie
CREATE TABLE IF NOT EXISTS `pms_project_type` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `fk_category_id`  int NOT NULL                   COMMENT 'Zugehörige Kategorie',
  `name`            varchar(100) NOT NULL           COMMENT 'z.B. Dienstleistung (LV-Kunde)',
  `has_lv`          tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = Leistungsverzeichnis vom Kunden',
  `description`     text                            COMMENT 'Kurzbeschreibung des Typs',
  `sort`            tinyint NOT NULL DEFAULT '0',
  `is_deleted`      tinyint(1) NOT NULL DEFAULT '0',
  `created_at`      timestamp NULL DEFAULT NULL,
  `updated_at`      timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ppt_category` (`fk_category_id`),
  CONSTRAINT `fk_ppt_category` FOREIGN KEY (`fk_category_id`) REFERENCES `pms_project_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 4. Unter-Kostenstellen 201 / 401 / 601 / 801
CREATE TABLE IF NOT EXISTS `pms_sub_cost_center` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `fk_category_id`  int NOT NULL                   COMMENT 'Zugehörige Kategorie',
  `code`            smallint NOT NULL               COMMENT 'z.B. 201 / 401 / 601 / 801',
  `name`            varchar(150) NOT NULL           COMMENT 'z.B. Park I / Allianz',
  `is_deleted`      tinyint(1) NOT NULL DEFAULT '0',
  `created_at`      timestamp NULL DEFAULT NULL,
  `updated_at`      timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pscc_category` (`fk_category_id`),
  CONSTRAINT `fk_pscc_category` FOREIGN KEY (`fk_category_id`) REFERENCES `pms_project_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 5. Status (Angebot / Auftrag / Abteilung)
CREATE TABLE IF NOT EXISTS `pms_status` (
  `id`         int NOT NULL AUTO_INCREMENT,
  `type`       enum('offer','order','dept') NOT NULL COMMENT 'offer=Angebot / order=Auftrag / dept=Abteilung',
  `name`       varchar(100) NOT NULL                COMMENT 'Statusbezeichnung',
  `color`      varchar(30) NOT NULL DEFAULT 'secondary' COMMENT 'Bootstrap-Farbe',
  `sort`       tinyint NOT NULL DEFAULT '0',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 6. Angebote Haupttabelle
CREATE TABLE IF NOT EXISTS `pms_offers` (
  `id`                      int NOT NULL AUTO_INCREMENT,
  `offer_nr`                varchar(20) NOT NULL               COMMENT 'Format: AN-26/0001',
  `fk_category_id`          int NOT NULL                       COMMENT 'Kategorie 200/400/600/800',
  `fk_type_id`              int NOT NULL                       COMMENT 'Angebotstyp',
  `fk_sub_cost_center_id`   int DEFAULT NULL                   COMMENT 'Unter-KST z.B. 401',
  `fk_customer_id`          int DEFAULT NULL                   COMMENT 'Kunde',
  `fk_customer_contact_id`  int DEFAULT NULL                   COMMENT 'Ansprechpartner beim Kunden',
  `title`                   varchar(250) NOT NULL              COMMENT 'Angebotstitel',
  `service_location`        varchar(250) DEFAULT NULL          COMMENT 'Leistungsort / Baustellenadresse',
  `lv_number`               varchar(100) DEFAULT NULL          COMMENT 'LV-Nummer vom Kunden (nur LV-Typen)',
  `submission_deadline`     date DEFAULT NULL                  COMMENT 'Abgabefrist (nur LV-Typen)',
  `execution_from`          date DEFAULT NULL                  COMMENT 'Ausführungsbeginn',
  `execution_to`            date DEFAULT NULL                  COMMENT 'Ausführungsende',
  `priority`                enum('normal','urgent','emergency') DEFAULT NULL COMMENT 'Priorität (nur Reparatur)',
  `maintenance_cycle`       enum('monthly','quarterly','semi_annual','annual') DEFAULT NULL COMMENT 'Wartungsturnus (nur Wartung)',
  `next_maintenance`        date DEFAULT NULL                  COMMENT 'Nächste Wartung (nur Wartung)',
  `offer_date`              date NOT NULL                      COMMENT 'Angebotsdatum',
  `valid_until`             date DEFAULT NULL                  COMMENT 'Gültig bis',
  `status`                  enum('draft','sent','accepted','rejected','expired','converted') NOT NULL DEFAULT 'draft' COMMENT 'Entwurf/Versendet/Angenommen/Abgelehnt/Abgelaufen/In Auftrag',
  `net_total`               decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Nettosumme',
  `discount_percent`        decimal(5,2) DEFAULT NULL          COMMENT 'Nachlass in %',
  `vat_rate`                decimal(4,2) NOT NULL DEFAULT '19.00' COMMENT 'MwSt.-Satz',
  `vat_amount`              decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'MwSt.-Betrag',
  `gross_total`             decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Bruttosumme',
  `payment_term`            varchar(100) DEFAULT NULL          COMMENT 'Zahlungsziel',
  `cash_discount`           decimal(4,2) DEFAULT NULL          COMMENT 'Skonto in %',
  `notes`                   text                               COMMENT 'Hinweise (im PDF sichtbar)',
  `internal_notes`          text                               COMMENT 'Interne Notizen (nicht im PDF)',
  `fk_project_location_id`  int DEFAULT NULL                   COMMENT 'Bauvorhaben → project_location',
  `fk_status_id`            int DEFAULT NULL                   COMMENT 'Firmen-Status → pms_status (type=offer)',
  `fk_status_dept_id`       int DEFAULT NULL                   COMMENT 'Abteilungs-Status → pms_status (type=dept)',
  `fk_created_by`           int DEFAULT NULL                   COMMENT 'Erstellt von (Mitarbeiter)',
  `is_deleted`              tinyint(1) NOT NULL DEFAULT '0',
  `created_at`              timestamp NULL DEFAULT NULL,
  `updated_at`              timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_offer_nr` (`offer_nr`),
  KEY `fk_po_project_location` (`fk_project_location_id`),
  KEY `fk_po_category`        (`fk_category_id`),
  KEY `fk_po_type`            (`fk_type_id`),
  KEY `fk_po_sub_cost_center` (`fk_sub_cost_center_id`),
  KEY `fk_po_status`          (`fk_status_id`),
  KEY `fk_po_status_dept`     (`fk_status_dept_id`),
  CONSTRAINT `fk_po_category`        FOREIGN KEY (`fk_category_id`)        REFERENCES `pms_project_category` (`id`),
  CONSTRAINT `fk_po_type`            FOREIGN KEY (`fk_type_id`)            REFERENCES `pms_project_type` (`id`),
  CONSTRAINT `fk_po_sub_cost_center` FOREIGN KEY (`fk_sub_cost_center_id`) REFERENCES `pms_sub_cost_center` (`id`),
  CONSTRAINT `fk_po_status`          FOREIGN KEY (`fk_status_id`)          REFERENCES `pms_status` (`id`),
  CONSTRAINT `fk_po_status_dept`     FOREIGN KEY (`fk_status_dept_id`)     REFERENCES `pms_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 7. Angebot-Positionen (bis 4 Ebenen)
CREATE TABLE IF NOT EXISTS `pms_offer_positions` (
  `id`           int NOT NULL AUTO_INCREMENT,
  `fk_offer_id`  int NOT NULL                       COMMENT 'Zugehöriges Angebot',
  `parent_id`    int DEFAULT NULL                   COMMENT 'Übergeordnete Position, NULL = Ebene 1',
  `level`        tinyint NOT NULL DEFAULT '1'       COMMENT 'Ebene: 1 / 2 / 3 / 4',
  `pos_number`   varchar(20) NOT NULL               COMMENT 'z.B. 01 / 01.01 / 01.01.001 / 01.01.001.1',
  `title`        varchar(250) NOT NULL              COMMENT 'Kurzbezeichnung der Position',
  `description`  text                               COMMENT 'Langtext / Leistungsbeschreibung',
  `note`         text                               COMMENT 'Hinweis zur Position',
  `quantity`     decimal(10,3) DEFAULT NULL         COMMENT 'Menge',
  `unit`         varchar(20) DEFAULT NULL           COMMENT 'Einheit: m² / m³ / lfm / Stk / Std / Pauschal',
  `unit_price`   decimal(12,2) DEFAULT NULL         COMMENT 'Einheitspreis (EP)',
  `total_price`  decimal(12,2) DEFAULT NULL         COMMENT 'Gesamtpreis: quantity × unit_price (auto)',
  `sort`         int NOT NULL DEFAULT '0'           COMMENT 'Reihenfolge innerhalb der Ebene',
  `is_deleted`   tinyint(1) NOT NULL DEFAULT '0',
  `created_at`   timestamp NULL DEFAULT NULL,
  `updated_at`   timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pop_offer`  (`fk_offer_id`),
  KEY `fk_pop_parent` (`parent_id`),
  CONSTRAINT `fk_pop_offer`  FOREIGN KEY (`fk_offer_id`) REFERENCES `pms_offers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pop_parent` FOREIGN KEY (`parent_id`)   REFERENCES `pms_offer_positions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 8. Angebot-Timeline (Laufkanal)
CREATE TABLE IF NOT EXISTS `pms_offer_timeline` (
  `id`             int NOT NULL AUTO_INCREMENT,
  `fk_offer_id`    int NOT NULL                     COMMENT 'Zugehöriges Angebot',
  `fk_employee_id` int NOT NULL                     COMMENT 'Mitarbeiter der den Eintrag erstellt hat',
  `contact_type`   tinyint NOT NULL DEFAULT '4'     COMMENT '0=Anruf 1=E-Mail 2=Post 3=Persönlich 4=Info',
  `contact_dst`    varchar(255) DEFAULT NULL         COMMENT 'Kontaktiert: Name des Ansprechpartners',
  `message`        text NOT NULL                    COMMENT 'Nachricht / Hinweis',
  `is_deleted`     tinyint(1) NOT NULL DEFAULT '0',
  `created_at`     timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pot_offer`    (`fk_offer_id`),
  KEY `fk_pot_employee` (`fk_employee_id`),
  CONSTRAINT `fk_pot_offer`    FOREIGN KEY (`fk_offer_id`)    REFERENCES `pms_offers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pot_employee` FOREIGN KEY (`fk_employee_id`) REFERENCES `employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 9. Aufträge Haupttabelle
CREATE TABLE IF NOT EXISTS `pms_orders` (
  `id`                      int NOT NULL AUTO_INCREMENT,
  `order_nr`                varchar(20) NOT NULL               COMMENT 'Format: AU-26/0001',
  `fk_offer_id`             int DEFAULT NULL                   COMMENT 'Ursprungs-Angebot',
  `fk_category_id`          int NOT NULL                       COMMENT 'Kategorie 200/400/600/800',
  `fk_type_id`              int NOT NULL                       COMMENT 'Auftragstyp',
  `fk_sub_cost_center_id`   int DEFAULT NULL                   COMMENT 'Unter-KST z.B. 401',
  `fk_customer_id`          int DEFAULT NULL                   COMMENT 'Kunde',
  `fk_customer_contact_id`  int DEFAULT NULL                   COMMENT 'Ansprechpartner beim Kunden',
  `title`                   varchar(250) NOT NULL              COMMENT 'Auftragstitel',
  `service_location`        varchar(250) DEFAULT NULL          COMMENT 'Leistungsort / Baustellenadresse',
  `execution_from`          date DEFAULT NULL                  COMMENT 'Ausführungsbeginn',
  `execution_to`            date DEFAULT NULL                  COMMENT 'Ausführungsende',
  `status`                  enum('new','in_progress','completed','cancelled') NOT NULL DEFAULT 'new' COMMENT 'Neu/In Ausführung/Abgeschlossen/Storniert',
  `net_total`               decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Nettosumme',
  `discount_percent`        decimal(5,2) DEFAULT NULL          COMMENT 'Nachlass in %',
  `vat_rate`                decimal(4,2) NOT NULL DEFAULT '19.00' COMMENT 'MwSt.-Satz',
  `vat_amount`              decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'MwSt.-Betrag',
  `gross_total`             decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Bruttosumme',
  `payment_term`            varchar(100) DEFAULT NULL          COMMENT 'Zahlungsziel',
  `notes`                   text                               COMMENT 'Hinweise',
  `internal_notes`          text                               COMMENT 'Interne Notizen',
  `fk_project_location_id`  int DEFAULT NULL                   COMMENT 'Bauvorhaben → project_location',
  `fk_status_id`            int DEFAULT NULL                   COMMENT 'Firmen-Status → pms_status (type=order)',
  `fk_status_dept_id`       int DEFAULT NULL                   COMMENT 'Abteilungs-Status → pms_status (type=dept)',
  `fk_created_by`           int DEFAULT NULL                   COMMENT 'Erstellt von (Mitarbeiter)',
  `is_deleted`              tinyint(1) NOT NULL DEFAULT '0',
  `created_at`              timestamp NULL DEFAULT NULL,
  `updated_at`              timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_order_nr` (`order_nr`),
  KEY `fk_pord_project_location` (`fk_project_location_id`),
  KEY `fk_pord_offer`           (`fk_offer_id`),
  KEY `fk_pord_category`        (`fk_category_id`),
  KEY `fk_pord_type`            (`fk_type_id`),
  KEY `fk_pord_sub_cost_center` (`fk_sub_cost_center_id`),
  KEY `fk_pord_status`          (`fk_status_id`),
  KEY `fk_pord_status_dept`     (`fk_status_dept_id`),
  CONSTRAINT `fk_pord_offer`           FOREIGN KEY (`fk_offer_id`)           REFERENCES `pms_offers` (`id`),
  CONSTRAINT `fk_pord_category`        FOREIGN KEY (`fk_category_id`)        REFERENCES `pms_project_category` (`id`),
  CONSTRAINT `fk_pord_type`            FOREIGN KEY (`fk_type_id`)            REFERENCES `pms_project_type` (`id`),
  CONSTRAINT `fk_pord_sub_cost_center` FOREIGN KEY (`fk_sub_cost_center_id`) REFERENCES `pms_sub_cost_center` (`id`),
  CONSTRAINT `fk_pord_status`          FOREIGN KEY (`fk_status_id`)          REFERENCES `pms_status` (`id`),
  CONSTRAINT `fk_pord_status_dept`     FOREIGN KEY (`fk_status_dept_id`)     REFERENCES `pms_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 10. Auftrag-Positionen (bis 4 Ebenen)
CREATE TABLE IF NOT EXISTS `pms_order_positions` (
  `id`           int NOT NULL AUTO_INCREMENT,
  `fk_order_id`  int NOT NULL                       COMMENT 'Zugehöriger Auftrag',
  `parent_id`    int DEFAULT NULL                   COMMENT 'Übergeordnete Position, NULL = Ebene 1',
  `level`        tinyint NOT NULL DEFAULT '1'       COMMENT 'Ebene: 1 / 2 / 3 / 4',
  `pos_number`   varchar(20) NOT NULL               COMMENT 'z.B. 01 / 01.01 / 01.01.001 / 01.01.001.1',
  `title`        varchar(250) NOT NULL              COMMENT 'Kurzbezeichnung der Position',
  `description`  text                               COMMENT 'Langtext / Leistungsbeschreibung',
  `note`         text                               COMMENT 'Hinweis zur Position',
  `quantity`     decimal(10,3) DEFAULT NULL         COMMENT 'Menge',
  `unit`         varchar(20) DEFAULT NULL           COMMENT 'Einheit: m² / m³ / lfm / Stk / Std / Pauschal',
  `unit_price`   decimal(12,2) DEFAULT NULL         COMMENT 'Einheitspreis (EP)',
  `total_price`  decimal(12,2) DEFAULT NULL         COMMENT 'Gesamtpreis: quantity × unit_price (auto)',
  `sort`         int NOT NULL DEFAULT '0'           COMMENT 'Reihenfolge innerhalb der Ebene',
  `is_deleted`   tinyint(1) NOT NULL DEFAULT '0',
  `created_at`   timestamp NULL DEFAULT NULL,
  `updated_at`   timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ordp_order`  (`fk_order_id`),
  KEY `fk_ordp_parent` (`parent_id`),
  CONSTRAINT `fk_ordp_order`  FOREIGN KEY (`fk_order_id`) REFERENCES `pms_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ordp_parent` FOREIGN KEY (`parent_id`)   REFERENCES `pms_order_positions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 11. Auftrag-Timeline (Laufkanal)
CREATE TABLE IF NOT EXISTS `pms_order_timeline` (
  `id`             int NOT NULL AUTO_INCREMENT,
  `fk_order_id`    int NOT NULL                     COMMENT 'Zugehöriger Auftrag',
  `fk_employee_id` int NOT NULL                     COMMENT 'Mitarbeiter der den Eintrag erstellt hat',
  `contact_type`   tinyint NOT NULL DEFAULT '4'     COMMENT '0=Anruf 1=E-Mail 2=Post 3=Persönlich 4=Info',
  `message`        text NOT NULL                    COMMENT 'Nachricht / Hinweis',
  `is_deleted`     tinyint(1) NOT NULL DEFAULT '0',
  `created_at`     timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ordt_order`    (`fk_order_id`),
  KEY `fk_ordt_employee` (`fk_employee_id`),
  CONSTRAINT `fk_ordt_order`    FOREIGN KEY (`fk_order_id`)    REFERENCES `pms_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ordt_employee` FOREIGN KEY (`fk_employee_id`) REFERENCES `employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------
-- Seed-Daten
-- ------------------------------------------------------------

INSERT IGNORE INTO `pms_project_category` (`nr_prefix`, `name`, `description`, `sort`, `created_at`) VALUES
(200, 'Interne Projekte',  'Firma entwickelt intern – Ankauf, Planung, Bau, Verkauf', 1, NOW()),
(400, 'Rahmenprojekte',    'Stammkunden mit vereinbarten Konditionen', 2, NOW()),
(600, 'Großprojekte',      'Große Bauprojekte und öffentliche Ausschreibungen', 3, NOW()),
(800, 'Privatprojekte',    'Einfache Angebote für Privatkunden', 4, NOW());

INSERT IGNORE INTO `pms_project_type` (`fk_category_id`, `name`, `has_lv`, `sort`, `created_at`) VALUES
((SELECT id FROM pms_project_category WHERE nr_prefix=200), 'Projektentwicklung',               0, 1, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=400), 'Dienstleistung (LV-Kunde)',        1, 1, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=400), 'Dienstleistung (Eigenes Angebot)', 0, 2, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=400), 'Wartung',                          0, 3, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=400), 'Reparatur',                        0, 4, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=600), 'Dienstleistung (LV-Kunde)',        1, 1, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=600), 'Generalunternehmer',               1, 2, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=600), 'Generalübernehmer',                0, 3, NOW()),
((SELECT id FROM pms_project_category WHERE nr_prefix=800), 'Dienstleistung (Eigenes Angebot)', 0, 1, NOW());

INSERT IGNORE INTO `pms_status` (`type`, `name`, `color`, `sort`, `created_at`) VALUES
('offer', 'Neu',                 'secondary', 1, NOW()),
('offer', 'In Kalkulation',      'primary',   2, NOW()),
('offer', 'Angebot erstellt',    'info',      3, NOW()),
('offer', 'Angebot versendet',   'warning',   4, NOW()),
('offer', 'In Verhandlung',      'warning',   5, NOW()),
('offer', 'Beauftragt',          'success',   6, NOW()),
('offer', 'Abgesagt',            'danger',    7, NOW()),
('offer', 'Abgelaufen',          'dark',      8, NOW()),
('order', 'Neu',                 'secondary', 1, NOW()),
('order', 'In Planung',          'primary',   2, NOW()),
('order', 'In Ausführung',       'info',      3, NOW()),
('order', 'Abnahme',             'warning',   4, NOW()),
('order', 'Abgeschlossen',       'success',   5, NOW()),
('order', 'Storniert',           'danger',    6, NOW()),
('dept',  'Unbearbeitet',        'secondary', 1, NOW()),
('dept',  'In Bearbeitung',      'primary',   2, NOW()),
('dept',  'Erledigt',            'success',   3, NOW()),
('dept',  'Pausiert',            'warning',   4, NOW());

INSERT IGNORE INTO `pms_nr_counter` (`type`, `year`, `last_nr`) VALUES
('offer',    YEAR(NOW()) - 2000, 0),
('order',    YEAR(NOW()) - 2000, 0),
('rechnung', YEAR(NOW()) - 2000, 0);

-- DMS-Dateiverwaltung
CREATE TABLE IF NOT EXISTS `dms_files` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `fk_offer_id`     int NULL DEFAULT NULL         COMMENT 'Verknüpfung zum Angebot',
  `fk_order_id`     int NULL DEFAULT NULL         COMMENT 'Verknüpfung zum Auftrag',
  `re_nr`           varchar(20) NULL DEFAULT NULL COMMENT 'Rechnungsnummer z.B. RE-26/0001',
  `fk_project_id`   int NULL DEFAULT NULL         COMMENT 'Verknüpfung zum Projekt',
  `category`        varchar(50) NOT NULL           COMMENT 'z.B. angebot, auftragsbestaetigung, rechnung',
  `filename`        varchar(255) NOT NULL,
  `filepath`        varchar(500) NOT NULL          COMMENT 'Relativer Pfad ab storage/app/dms/',
  `original_name`   varchar(255) NULL DEFAULT NULL,
  `filesize`        int NULL DEFAULT NULL          COMMENT 'Dateigröße in Bytes',
  `year`            smallint NULL DEFAULT NULL,
  `version`         smallint NOT NULL DEFAULT '1',
  `fk_created_by`   int NULL DEFAULT NULL,
  `is_deleted`      tinyint(1) NOT NULL DEFAULT '0',
  `created_at`      timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_offer`   (`fk_offer_id`),
  KEY `idx_order`   (`fk_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS=1;
