-- 030: Mitarbeiter-Zeiterfassung (work_employees + work_hours).
--
-- Modernisiertes Schema, angelehnt an die Alt-Tabellen aus seaside_pas.sql:
--  - AUTO_INCREMENT PKs, FK mit ON DELETE CASCADE
--  - Pause als Minuten (SMALLINT UNSIGNED) statt TIME-String
--  - Aktiv-Flag + Soll-Stunden auf Mitarbeiter
--  - Notiz pro Tag und sauberer Timestamp-Tracking
--
-- Hängt an /?page=reports/worktime; Statistiken werden monatlich aggregiert.

CREATE TABLE IF NOT EXISTS `work_employees` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name`  VARCHAR(100) NOT NULL,
    `max_hours`  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_work_employees_active` (`is_active`),
    KEY `idx_work_employees_name`   (`last_name`, `first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `work_hours` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id`    INT UNSIGNED NOT NULL,
    `date`           DATE         NOT NULL,
    `start_time`     TIME         NOT NULL,
    `end_time`       TIME         NOT NULL,
    `break_minutes`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `note`           VARCHAR(255) DEFAULT NULL,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_work_hours_emp_date` (`employee_id`, `date`),
    KEY `idx_work_hours_date`     (`date`),
    CONSTRAINT `fk_work_hours_employee`
        FOREIGN KEY (`employee_id`) REFERENCES `work_employees` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------------------
-- Bestandsdaten aus `pacim_import` übernehmen, falls die DB vorhanden ist.
-- Die Alt-Tabelle nutzte `break_duration TIME` — das wird hier auf Minuten
-- normalisiert (`TIME_TO_SEC(...) / 60`). INSERT IGNORE schützt vor PK-
-- Konflikten, falls die Migration ein zweites Mal läuft.
-- -------------------------------------------------------------------------

SET @import_db := 'pacim_import';

SET @has_employees := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                          WHERE TABLE_SCHEMA = @import_db AND TABLE_NAME = 'work_employees');
SET @sql := IF(@has_employees > 0,
    "INSERT IGNORE INTO `work_employees` (`id`, `first_name`, `last_name`, `max_hours`, `is_active`, `created_at`)
     SELECT `id`, `first_name`, `last_name`, COALESCE(`max_hours`, 0), 1, NOW()
       FROM `pacim_import`.`work_employees`",
    "DO 0");
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_hours := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                      WHERE TABLE_SCHEMA = @import_db AND TABLE_NAME = 'work_hours');
SET @sql := IF(@has_hours > 0,
    "INSERT IGNORE INTO `work_hours`
        (`id`, `employee_id`, `date`, `start_time`, `end_time`, `break_minutes`, `note`, `created_at`, `updated_at`)
     SELECT h.`id`, h.`employee_id`, h.`date`, h.`start_time`, h.`end_time`,
            CAST(TIME_TO_SEC(COALESCE(h.`break_duration`, '00:00:00')) / 60 AS UNSIGNED),
            NULL, NOW(), NOW()
       FROM `pacim_import`.`work_hours` h
       INNER JOIN `work_employees` we ON we.`id` = h.`employee_id`",
    "DO 0");
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;