-- ============================================================
-- Temperature and Humidity Laboratory (THL) Specific Table
-- Run this after the main schema.sql
--
-- Fields NOT stored here (pulled via JOIN from existing tables):
--   equipment_name  → equipment.equipment_name
--   manufacturer    → equipment.manufacturer
--   model/type      → equipment.model
--   serial_number   → equipment.serial_number
--   company_name    → companies.company_name
--   request_number  → requests.request_number
-- ============================================================
USE `metlab_db`;

CREATE TABLE IF NOT EXISTS `humidity_lab_forms` (
  `form_id`                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `request_id`                  INT UNSIGNED NOT NULL UNIQUE,
  `officer_id`                  INT UNSIGNED NOT NULL,
  `lab_id`                      INT UNSIGNED NOT NULL,

  -- ── Certificate / Admin ─────────────────────────────────
  `certificate_number`          VARCHAR(100),
  `calibration_date`            VARCHAR(100)    COMMENT 'e.g. 2024-01-30 to 2024-01-31',
  `date_of_issue`               DATE,
  `instrument_type`             VARCHAR(150),
  `place_of_test`               VARCHAR(200),
  `procedure`                   VARCHAR(300)    DEFAULT 'DKD-R 5-8:2019 (Humidity) / DKD R-5-1:2018 (Temperature)',
  `calibrated_by`               VARCHAR(150),
  `checked_by`                  VARCHAR(150),
  `due_date`                    DATE,
  `comments`                    TEXT,

  -- ── Instrument resolution ───────────────────────────────
  `res_rh_eut`                  DECIMAL(8,4)    DEFAULT 0.1   COMMENT '%RH resolution of EUT',
  `res_temp_eut`                DECIMAL(8,4)    DEFAULT 0.1   COMMENT '°C resolution of EUT',
  `num_of_measurements`         TINYINT UNSIGNED DEFAULT 5    COMMENT 'Readings per setpoint',

  -- ── Setpoints: up to 5 setpoints ────────────────────────
  -- Each setpoint stores: ref temps, ref RH (2 sensors), EUT readings,
  -- corrections, averages, deviations, and computed uncertainties.

  -- Setpoint 1
  `sp1_setpoint_label`          VARCHAR(20),
  `sp1_ref_temps`               TEXT            COMMENT 'JSON array of reference temperature readings',
  `sp1_correction_temp`         DECIMAL(8,4)    DEFAULT 0,
  `sp1_ref_rh1_readings`        TEXT            COMMENT 'JSON array of Ref RH Sensor 1 readings',
  `sp1_correction_rh1`          DECIMAL(8,4)    DEFAULT 0,
  `sp1_ref_rh2_readings`        TEXT            COMMENT 'JSON array of Ref RH Sensor 2 readings',
  `sp1_correction_rh2`          DECIMAL(8,4)    DEFAULT 0,
  `sp1_eut_temps`               TEXT            COMMENT 'JSON array of EUT temperature readings',
  `sp1_eut_rhs`                 TEXT            COMMENT 'JSON array of EUT RH readings',
  -- Computed (stored for certificate display, recalculated on view)
  `sp1_avg_ref_temp`            DECIMAL(10,4),
  `sp1_corrected_ref_temp`      DECIMAL(10,4),
  `sp1_corrected_ref_rh`        DECIMAL(10,4),
  `sp1_avg_eut_temp`            DECIMAL(10,4),
  `sp1_std_eut_temp`            DECIMAL(10,6),
  `sp1_avg_eut_rh`              DECIMAL(10,4),
  `sp1_std_eut_rh`              DECIMAL(10,6),
  `sp1_temp_deviation`          DECIMAL(10,4),
  `sp1_rh_deviation`            DECIMAL(10,4),
  `sp1_temp_uncertainty`        DECIMAL(10,4),
  `sp1_rh_uncertainty`          DECIMAL(10,4),
  `sp1_include_temp`            TINYINT(1)      DEFAULT 0    COMMENT '1 if RH was ~50%, temp results included',

  -- Setpoint 2
  `sp2_setpoint_label`          VARCHAR(20),
  `sp2_ref_temps`               TEXT,
  `sp2_correction_temp`         DECIMAL(8,4)    DEFAULT 0,
  `sp2_ref_rh1_readings`        TEXT,
  `sp2_correction_rh1`          DECIMAL(8,4)    DEFAULT 0,
  `sp2_ref_rh2_readings`        TEXT,
  `sp2_correction_rh2`          DECIMAL(8,4)    DEFAULT 0,
  `sp2_eut_temps`               TEXT,
  `sp2_eut_rhs`                 TEXT,
  `sp2_avg_ref_temp`            DECIMAL(10,4),
  `sp2_corrected_ref_temp`      DECIMAL(10,4),
  `sp2_corrected_ref_rh`        DECIMAL(10,4),
  `sp2_avg_eut_temp`            DECIMAL(10,4),
  `sp2_std_eut_temp`            DECIMAL(10,6),
  `sp2_avg_eut_rh`              DECIMAL(10,4),
  `sp2_std_eut_rh`              DECIMAL(10,6),
  `sp2_temp_deviation`          DECIMAL(10,4),
  `sp2_rh_deviation`            DECIMAL(10,4),
  `sp2_temp_uncertainty`        DECIMAL(10,4),
  `sp2_rh_uncertainty`          DECIMAL(10,4),
  `sp2_include_temp`            TINYINT(1)      DEFAULT 0,

  -- Setpoint 3
  `sp3_setpoint_label`          VARCHAR(20),
  `sp3_ref_temps`               TEXT,
  `sp3_correction_temp`         DECIMAL(8,4)    DEFAULT 0,
  `sp3_ref_rh1_readings`        TEXT,
  `sp3_correction_rh1`          DECIMAL(8,4)    DEFAULT 0,
  `sp3_ref_rh2_readings`        TEXT,
  `sp3_correction_rh2`          DECIMAL(8,4)    DEFAULT 0,
  `sp3_eut_temps`               TEXT,
  `sp3_eut_rhs`                 TEXT,
  `sp3_avg_ref_temp`            DECIMAL(10,4),
  `sp3_corrected_ref_temp`      DECIMAL(10,4),
  `sp3_corrected_ref_rh`        DECIMAL(10,4),
  `sp3_avg_eut_temp`            DECIMAL(10,4),
  `sp3_std_eut_temp`            DECIMAL(10,6),
  `sp3_avg_eut_rh`              DECIMAL(10,4),
  `sp3_std_eut_rh`              DECIMAL(10,6),
  `sp3_temp_deviation`          DECIMAL(10,4),
  `sp3_rh_deviation`            DECIMAL(10,4),
  `sp3_temp_uncertainty`        DECIMAL(10,4),
  `sp3_rh_uncertainty`          DECIMAL(10,4),
  `sp3_include_temp`            TINYINT(1)      DEFAULT 0,

  -- Setpoint 4
  `sp4_setpoint_label`          VARCHAR(20),
  `sp4_ref_temps`               TEXT,
  `sp4_correction_temp`         DECIMAL(8,4)    DEFAULT 0,
  `sp4_ref_rh1_readings`        TEXT,
  `sp4_correction_rh1`          DECIMAL(8,4)    DEFAULT 0,
  `sp4_ref_rh2_readings`        TEXT,
  `sp4_correction_rh2`          DECIMAL(8,4)    DEFAULT 0,
  `sp4_eut_temps`               TEXT,
  `sp4_eut_rhs`                 TEXT,
  `sp4_avg_ref_temp`            DECIMAL(10,4),
  `sp4_corrected_ref_temp`      DECIMAL(10,4),
  `sp4_corrected_ref_rh`        DECIMAL(10,4),
  `sp4_avg_eut_temp`            DECIMAL(10,4),
  `sp4_std_eut_temp`            DECIMAL(10,6),
  `sp4_avg_eut_rh`              DECIMAL(10,4),
  `sp4_std_eut_rh`              DECIMAL(10,6),
  `sp4_temp_deviation`          DECIMAL(10,4),
  `sp4_rh_deviation`            DECIMAL(10,4),
  `sp4_temp_uncertainty`        DECIMAL(10,4),
  `sp4_rh_uncertainty`          DECIMAL(10,4),
  `sp4_include_temp`            TINYINT(1)      DEFAULT 0,

  -- Setpoint 5
  `sp5_setpoint_label`          VARCHAR(20),
  `sp5_ref_temps`               TEXT,
  `sp5_correction_temp`         DECIMAL(8,4)    DEFAULT 0,
  `sp5_ref_rh1_readings`        TEXT,
  `sp5_correction_rh1`          DECIMAL(8,4)    DEFAULT 0,
  `sp5_ref_rh2_readings`        TEXT,
  `sp5_correction_rh2`          DECIMAL(8,4)    DEFAULT 0,
  `sp5_eut_temps`               TEXT,
  `sp5_eut_rhs`                 TEXT,
  `sp5_avg_ref_temp`            DECIMAL(10,4),
  `sp5_corrected_ref_temp`      DECIMAL(10,4),
  `sp5_corrected_ref_rh`        DECIMAL(10,4),
  `sp5_avg_eut_temp`            DECIMAL(10,4),
  `sp5_std_eut_temp`            DECIMAL(10,6),
  `sp5_avg_eut_rh`              DECIMAL(10,4),
  `sp5_std_eut_rh`              DECIMAL(10,6),
  `sp5_temp_deviation`          DECIMAL(10,4),
  `sp5_rh_deviation`            DECIMAL(10,4),
  `sp5_temp_uncertainty`        DECIMAL(10,4),
  `sp5_rh_uncertainty`          DECIMAL(10,4),
  `sp5_include_temp`            TINYINT(1)      DEFAULT 0,

  `num_setpoints`               TINYINT UNSIGNED DEFAULT 3,
  `form_status`                 ENUM('draft','submitted','approved') NOT NULL DEFAULT 'draft',
  `date_created`                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `date_updated`                TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (`request_id`)    REFERENCES `requests`(`request_id`) ON DELETE CASCADE,
  FOREIGN KEY (`officer_id`)    REFERENCES `users`(`user_id`),
  FOREIGN KEY (`lab_id`)        REFERENCES `laboratories`(`lab_id`)
) ENGINE=InnoDB;
