-- ============================================================
-- FMS – Fleet Management System
-- Drop old tables (child → parent order) and recreate with
-- fully English table + column names.
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS fms_assignments;
DROP TABLE IF EXISTS fms_fuel_logs;
DROP TABLE IF EXISTS fms_repairs;
DROP TABLE IF EXISTS fms_maintenances;
DROP TABLE IF EXISTS fms_deadlines;
DROP TABLE IF EXISTS fms_vehicles;
DROP TABLE IF EXISTS fms_workshops;
DROP TABLE IF EXISTS fms_sites;
DROP TABLE IF EXISTS fms_construction_sites;
DROP TABLE IF EXISTS fms_fuel;
SET FOREIGN_KEY_CHECKS = 1;

-- ── fms_sites ────────────────────────────────────────────────
CREATE TABLE `fms_sites` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `name`            varchar(200) NOT NULL,
  `city`            varchar(100) DEFAULT NULL,
  `address`         varchar(300) DEFAULT NULL,
  `project_manager` varchar(100) DEFAULT NULL,
  `start_date`      date DEFAULT NULL,
  `end_date`        date DEFAULT NULL,
  `status`          enum('planned','active','completed') NOT NULL DEFAULT 'active',
  `notes`           text,
  `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;

-- ── fms_workshops ─────────────────────────────────────────────
CREATE TABLE `fms_workshops` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `name`            varchar(200) NOT NULL,
  `address`         varchar(300) DEFAULT NULL,
  `phone`           varchar(30)  DEFAULT NULL,
  `email`           varchar(150) DEFAULT NULL,
  `contact_person`  varchar(150) DEFAULT NULL,
  `specialization`  varchar(300) DEFAULT NULL,
  `notes`           text,
  `is_active`       tinyint(1) NOT NULL DEFAULT 1,
  `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;

-- ── fms_vehicles ──────────────────────────────────────────────
CREATE TABLE `fms_vehicles` (
  `id`                    int NOT NULL AUTO_INCREMENT,
  `name`                  varchar(200) NOT NULL,
  `type`                  enum('car','truck','excavator','crane','trailer','equipment') NOT NULL DEFAULT 'car',
  `license_plate`         varchar(30)  DEFAULT NULL,
  `internal_number`       varchar(50)  DEFAULT NULL,
  `manufacturer`          varchar(100) DEFAULT NULL,
  `model_name`            varchar(100) DEFAULT NULL,
  `year`                  year         DEFAULT NULL,
  `vin`                   varchar(50)  DEFAULT NULL,
  `color`                 varchar(50)  DEFAULT NULL,
  `mileage`               int          DEFAULT NULL,
  `next_service_mileage`  int          DEFAULT NULL,
  `operating_hours`       int          DEFAULT NULL,
  `next_service_hours`    int          DEFAULT NULL,
  `fuel_type`             enum('diesel','petrol','electric','none') NOT NULL DEFAULT 'diesel',
  `status`                enum('active','maintenance','repair','blocked','sold') NOT NULL DEFAULT 'active',
  `site_id`               int          DEFAULT NULL,
  `driver_id`             int          DEFAULT NULL COMMENT 'FK → employee.employee_id',
  `purchase_date`         date         DEFAULT NULL,
  `purchase_price`        decimal(12,2) DEFAULT NULL,
  `notes`                 text,
  `image`                 varchar(500) DEFAULT NULL,
  `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_fms_vehicles_site`   (`site_id`),
  KEY `fk_fms_vehicles_driver` (`driver_id`),
  CONSTRAINT `fk_fms_vehicles_site`   FOREIGN KEY (`site_id`)   REFERENCES `fms_sites` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_fms_vehicles_driver` FOREIGN KEY (`driver_id`) REFERENCES `employee` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── fms_deadlines ─────────────────────────────────────────────
CREATE TABLE `fms_deadlines` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `vehicle_id`      int NOT NULL,
  `type`            enum('tuev_hu','uvv','sv_inspection','insurance','vehicle_tax','adr','driver_license','other') NOT NULL DEFAULT 'other',
  `title`           varchar(200) NOT NULL,
  `due_date`        date NOT NULL,
  `completed_date`  date DEFAULT NULL,
  `reminder_days`   int NOT NULL DEFAULT 30,
  `notes`           text,
  `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_fms_deadlines_vehicle` (`vehicle_id`),
  CONSTRAINT `fk_fms_deadlines_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `fms_vehicles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── fms_maintenances ──────────────────────────────────────────
CREATE TABLE `fms_maintenances` (
  `id`                  int NOT NULL AUTO_INCREMENT,
  `vehicle_id`          int NOT NULL,
  `type`                enum('oil_change','minor_service','major_service','tuev_hu','uvv_inspection','sv_inspection','tire_change','hydraulic_service','chain_service','timing_belt','other') NOT NULL DEFAULT 'other',
  `description`         varchar(300) DEFAULT NULL,
  `due_date`            date DEFAULT NULL,
  `due_mileage`         int  DEFAULT NULL,
  `due_hours`           int  DEFAULT NULL,
  `completed_date`      date DEFAULT NULL,
  `completed_mileage`   int  DEFAULT NULL,
  `completed_hours`     int  DEFAULT NULL,
  `next_date`           date DEFAULT NULL,
  `next_mileage`        int  DEFAULT NULL,
  `next_hours`          int  DEFAULT NULL,
  `workshop_id`         int  DEFAULT NULL,
  `cost`                decimal(10,2) DEFAULT NULL,
  `status`              enum('open','completed','overdue') NOT NULL DEFAULT 'open',
  `notes`               text,
  `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_fms_maintenances_vehicle`  (`vehicle_id`),
  KEY `fk_fms_maintenances_workshop` (`workshop_id`),
  CONSTRAINT `fk_fms_maintenances_vehicle`  FOREIGN KEY (`vehicle_id`)  REFERENCES `fms_vehicles`  (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fms_maintenances_workshop` FOREIGN KEY (`workshop_id`) REFERENCES `fms_workshops` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── fms_repairs ───────────────────────────────────────────────
CREATE TABLE `fms_repairs` (
  `id`               int NOT NULL AUTO_INCREMENT,
  `vehicle_id`       int NOT NULL,
  `workshop_id`      int DEFAULT NULL,
  `damage_date`      date NOT NULL,
  `repair_date`      date DEFAULT NULL,
  `title`            varchar(200) NOT NULL,
  `description`      text,
  `mileage`          int  DEFAULT NULL,
  `operating_hours`  int  DEFAULT NULL,
  `total_cost`       decimal(10,2) DEFAULT NULL,
  `parts_cost`       decimal(10,2) DEFAULT NULL,
  `labor_cost`       decimal(10,2) DEFAULT NULL,
  `status`           enum('reported','in_workshop','completed','invoiced') NOT NULL DEFAULT 'reported',
  `invoice_number`   varchar(100) DEFAULT NULL,
  `invoice_file`     varchar(500) DEFAULT NULL,
  `damage_photos`    json DEFAULT NULL,
  `created_by`       int DEFAULT NULL COMMENT 'FK → employee.employee_id',
  `notes`            text,
  `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_fms_repairs_vehicle`  (`vehicle_id`),
  KEY `fk_fms_repairs_workshop` (`workshop_id`),
  CONSTRAINT `fk_fms_repairs_vehicle`  FOREIGN KEY (`vehicle_id`)  REFERENCES `fms_vehicles`  (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fms_repairs_workshop` FOREIGN KEY (`workshop_id`) REFERENCES `fms_workshops` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── fms_fuel_logs ─────────────────────────────────────────────
CREATE TABLE `fms_fuel_logs` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `vehicle_id`      int NOT NULL,
  `driver_id`       int DEFAULT NULL COMMENT 'FK → employee.employee_id',
  `fueled_at`       datetime NOT NULL,
  `fuel_type`       enum('diesel','petrol','adblue','electric_kwh') NOT NULL DEFAULT 'diesel',
  `quantity`        decimal(8,2) NOT NULL,
  `price_per_unit`  decimal(6,3) DEFAULT NULL,
  `total_price`     decimal(10,2) DEFAULT NULL,
  `mileage`         int DEFAULT NULL,
  `operating_hours` int DEFAULT NULL,
  `station`         varchar(200) DEFAULT NULL,
  `receipt_file`    varchar(500) DEFAULT NULL,
  `notes`           varchar(300) DEFAULT NULL,
  `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_fms_fuel_vehicle` (`vehicle_id`),
  KEY `fk_fms_fuel_driver`  (`driver_id`),
  CONSTRAINT `fk_fms_fuel_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `fms_vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fms_fuel_driver`  FOREIGN KEY (`driver_id`)  REFERENCES `employee` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── fms_assignments ───────────────────────────────────────────
CREATE TABLE `fms_assignments` (
  `id`          int NOT NULL AUTO_INCREMENT,
  `vehicle_id`  int NOT NULL,
  `site_id`     int DEFAULT NULL,
  `driver_id`   int DEFAULT NULL COMMENT 'FK → employee.employee_id',
  `start_date`  date NOT NULL,
  `end_date`    date DEFAULT NULL,
  `purpose`     varchar(300) DEFAULT NULL,
  `notes`       text,
  `created_by`  int DEFAULT NULL COMMENT 'FK → employee.employee_id',
  `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_fms_assign_vehicle` (`vehicle_id`),
  KEY `fk_fms_assign_site`    (`site_id`),
  KEY `fk_fms_assign_driver`  (`driver_id`),
  CONSTRAINT `fk_fms_assign_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `fms_vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fms_assign_site`    FOREIGN KEY (`site_id`)    REFERENCES `fms_sites` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_fms_assign_driver`  FOREIGN KEY (`driver_id`)  REFERENCES `employee` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Test data
-- ============================================================
INSERT INTO `fms_sites` (`name`,`city`,`address`,`project_manager`,`start_date`,`end_date`,`status`,`is_deleted`,`created_at`,`updated_at`) VALUES
('Neubau Hauptstraße 14','Bochum',  'Hauptstraße 14, 44787 Bochum',       'Max Mustermann','2026-01-10','2026-08-31','active',  0,NOW(),NOW()),
('Kanal Westenfeld',    'Arnsberg','Westenfelder Str. 80, 59823 Arnsberg','Hans Schmidt',  '2026-02-01','2026-06-30','active',  0,NOW(),NOW()),
('Parkhaus Süd',        'Dortmund','Südring 5, 44137 Dortmund',           'Lisa Meier',    '2026-05-01',NULL,        'planned', 0,NOW(),NOW());

INSERT INTO `fms_workshops` (`name`,`address`,`phone`,`email`,`contact_person`,`specialization`,`is_active`,`is_deleted`,`created_at`,`updated_at`) VALUES
('Kfz Meier GmbH',          'Industriestr. 12, 44787 Bochum', '0234-123456', 'info@kfz-meier.de',   'Thomas Meier', 'PKW, Transporter, LKW',           1,0,NOW(),NOW()),
('Nutzfahrzeuge Schmidt',    'Gewerbepark 8, 59823 Arnsberg',  '02931-654321','info@nfz-schmidt.de', 'Karl Schmidt', 'LKW, Anhänger, Auflieger',        1,0,NOW(),NOW()),
('Baumaschinen Technik GmbH','Werkstattweg 3, 44137 Dortmund', '0231-987654', 'info@bmt-do.de',      'Jürgen Weber', 'Bagger, Kran, Hebebühne, Geräte', 1,0,NOW(),NOW());

INSERT INTO `fms_vehicles` (`name`,`type`,`license_plate`,`internal_number`,`manufacturer`,`model_name`,`year`,`mileage`,`operating_hours`,`fuel_type`,`color`,`status`,`site_id`,`purchase_date`,`purchase_price`,`notes`,`is_deleted`,`created_at`,`updated_at`) VALUES
('VW Transporter T6',     'car',      'BO-BU 100','PKW-01','Volkswagen', 'Transporter T6', 2021,48500, NULL,'diesel', 'Silber',  'active',     1,'2021-03-15',32500.00,'Hauptfahrzeug Bauleitung',      0,NOW(),NOW()),
('MAN TGS Kipper',        'truck',    'BO-BU 101','LKW-01','MAN',        'TGS 18.400',     2019,187000,NULL,'diesel', 'Rot',     'active',     2,'2019-07-01',89000.00,'Kipper 15t Nutzlast',           0,NOW(),NOW()),
('CAT 320 Bagger',        'excavator',NULL,       'BAG-01','Caterpillar','320',             2020,NULL,  4200,'diesel', 'Gelb',    'maintenance',1,'2020-09-10',145000.00,'Hydraulikservice überfällig',  0,NOW(),NOW()),
('Liebherr LTM 1050 Kran','crane',    NULL,       'KRN-01','Liebherr',   'LTM 1050-3.1',   2018,NULL,  2800,'diesel', 'Gelb',    'active',     3,'2018-05-20',380000.00,'50t Mobilkran',                0,NOW(),NOW()),
('Wacker Rüttelplatte',   'equipment',NULL,       'RPL-01','Wacker',     'DPU 6555',        2022,NULL,   320,'petrol', 'Orange',  'active',     1,'2022-04-01',2800.00, 'Verdichtungsgerät',            0,NOW(),NOW()),
('BMW 320d Dienstwagen',  'car',      'BO-BU 050','PKW-02','BMW',        '320d',            2023,22000, NULL,'diesel', 'Schwarz', 'active',  NULL,'2023-01-10',44500.00,'Dienstwagen Geschäftsführung', 0,NOW(),NOW()),
('Humbaur Tieflader',     'trailer',  'BO-BU 199','ANH-01','Humbaur',    'HBT 30 3058 BS',  2020,NULL,  NULL,'none',   'Grau',    'active',     2,'2020-06-15',18500.00,'Tieflader für Bagger-Transport',0,NOW(),NOW());

INSERT INTO `fms_deadlines` (`vehicle_id`,`type`,`title`,`due_date`,`reminder_days`,`is_deleted`,`created_at`,`updated_at`) VALUES
(1,'tuev_hu',     'TÜV Hauptuntersuchung',      DATE_ADD(CURDATE(),INTERVAL 45 DAY), 30,0,NOW(),NOW()),
(1,'insurance',   'KFZ-Versicherung',            DATE_ADD(CURDATE(),INTERVAL 180 DAY),30,0,NOW(),NOW()),
(2,'tuev_hu',     'TÜV Hauptuntersuchung',      DATE_ADD(CURDATE(),INTERVAL 90 DAY), 30,0,NOW(),NOW()),
(2,'uvv',         'UVV-Prüfung',                DATE_ADD(CURDATE(),INTERVAL 8 DAY),  14,0,NOW(),NOW()),
(3,'sv_inspection','SV-Prüfung Hydraulikanlage',DATE_ADD(CURDATE(),INTERVAL -10 DAY),30,0,NOW(),NOW()),
(4,'sv_inspection','SV-Prüfung Kran (BG)',       DATE_ADD(CURDATE(),INTERVAL 61 DAY), 30,0,NOW(),NOW()),
(4,'uvv',         'UVV-Prüfung Kran',           DATE_ADD(CURDATE(),INTERVAL 120 DAY),30,0,NOW(),NOW()),
(6,'tuev_hu',     'TÜV Hauptuntersuchung',      DATE_ADD(CURDATE(),INTERVAL 25 DAY), 30,0,NOW(),NOW()),
(6,'insurance',   'KFZ-Versicherung',            DATE_ADD(CURDATE(),INTERVAL 240 DAY),30,0,NOW(),NOW());

INSERT INTO `fms_maintenances` (`vehicle_id`,`type`,`description`,`due_date`,`due_mileage`,`status`,`workshop_id`,`is_deleted`,`created_at`,`updated_at`) VALUES
(1,'oil_change',       'Ölwechsel + Filter',            DATE_ADD(CURDATE(),INTERVAL 30 DAY), 52000, 'open',     1,0,NOW(),NOW()),
(2,'major_service',    'Große Inspektion 200.000 km',    DATE_ADD(CURDATE(),INTERVAL 15 DAY), 200000,'open',     2,0,NOW(),NOW()),
(3,'hydraulic_service','Hydrauliköl + Filter wechseln',  DATE_ADD(CURDATE(),INTERVAL -5 DAY), NULL,  'overdue',  3,0,NOW(),NOW()),
(3,'chain_service',    'Kettentension prüfen',            DATE_ADD(CURDATE(),INTERVAL 60 DAY), NULL,  'open',     3,0,NOW(),NOW()),
(4,'major_service',    'Jahresinspektion Kran',           DATE_ADD(CURDATE(),INTERVAL 90 DAY), NULL,  'open',     3,0,NOW(),NOW()),
(6,'tire_change',      'Sommerreifen montieren',          DATE_ADD(CURDATE(),INTERVAL 7 DAY),  NULL,  'open',     1,0,NOW(),NOW());

SELECT CONCAT('OK: ',TABLE_NAME) AS result
FROM information_schema.TABLES
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE 'fms_%'
ORDER BY TABLE_NAME;
