-- ============================================================
-- Balance Laboratory Specific Table
-- Run this after the main schema.sql
-- Fields already in requests/companies/equipment are NOT
-- duplicated here — they are joined at query time.
-- ============================================================
USE `metlab_db`;

CREATE TABLE IF NOT EXISTS `balance_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,

  -- ── Administrative Data (A) ──────────────────────────────
  -- NOTE: laboratory_number  → requests.request_number  (read-only, not stored here)
  -- NOTE: item_calibrated    → equipment.equipment_name (read-only, not stored here)
  -- NOTE: customer_details   → companies.company_name   (read-only, not stored here)
  -- NOTE: serial_number      → equipment.serial_number  (read-only, not stored here)
  -- NOTE: manufacturer       → equipment.manufacturer   (read-only, not stored here)
  -- NOTE: type               → equipment.model          (read-only, not stored here)
  `certification_number`      VARCHAR(100),
  `accreditation`             VARCHAR(50),
  `calibration_date`          DATE,
  `max_capacity`              VARCHAR(50),
  `unit`                      VARCHAR(10),
  `min_capacity`              VARCHAR(50),
  `unitsec`                   VARCHAR(10),
  `test_method`               TEXT,
  `place_of_test`             VARCHAR(200),
  `high_air_circulation`      VARCHAR(5),
  `high_vibration`            VARCHAR(5),
  `temp_during_cal_start`     VARCHAR(20),
  `temp_during_cal_end`       VARCHAR(20),

  -- ── Administrative Data (B) ──────────────────────────────
  `applied_load`              VARCHAR(50),
  `deviation`                 VARCHAR(50),
  `preloading_executed`       VARCHAR(5),
  `balance_adjusted`          VARCHAR(5),
  `chng_temp`                 VARCHAR(10),
  `accuracy_class_of_weight`  VARCHAR(5),
  `lab_temperature`           VARCHAR(20),
  `site_temperature`          VARCHAR(20),
  `time_waited`               VARCHAR(50),
  `calibrated_by`             VARCHAR(150),
  `calibrated_date`           DATE,
  `checked_by`                VARCHAR(150),
  `date_checked`              DATE,
  `due_date`                  DATE,
  `comments`                  TEXT,

  -- ── Test of Error Indicator (5 load points: 0,25,50,75,100) ──
  `nominal_value_0`    VARCHAR(30), `load_0`    VARCHAR(30), `reference_weight_0`   VARCHAR(30), `indication_0`   VARCHAR(30), `deviation_of_indication_0`   VARCHAR(30),
  `nominal_value_25`   VARCHAR(30), `load_25`   VARCHAR(30), `reference_weight_25`  VARCHAR(30), `indication_25`  VARCHAR(30), `deviation_of_indication_25`  VARCHAR(30),
  `nominal_value_50`   VARCHAR(30), `load_50`   VARCHAR(30), `reference_weight_50`  VARCHAR(30), `indication_50`  VARCHAR(30), `deviation_of_indication_50`  VARCHAR(30),
  `nominal_value_75`   VARCHAR(30), `load_75`   VARCHAR(30), `reference_weight_75`  VARCHAR(30), `indication_75`  VARCHAR(30), `deviation_of_indication_75`  VARCHAR(30),
  `nominal_value_100`  VARCHAR(30), `load_100`  VARCHAR(30), `reference_weight_100` VARCHAR(30), `indication_100` VARCHAR(30), `deviation_of_indication_100` VARCHAR(30),

  -- ── MPE and Uncertainty of Standard ──
  `mpe_1` VARCHAR(30), `mpe_2` VARCHAR(30), `mpe_3` VARCHAR(30), `mpe_4` VARCHAR(30), `mpe_5` VARCHAR(30),
  `u_of_std_value_1` VARCHAR(30), `u_of_std_value_2` VARCHAR(30), `u_of_std_value_3` VARCHAR(30), `u_of_std_value_4` VARCHAR(30), `u_of_std_value_5` VARCHAR(30),

  -- ── Repeatability Test ──
  `applied_load_rt`   VARCHAR(50),
  `repeatability1`    VARCHAR(30), `repeatability2`  VARCHAR(30), `repeatability3`  VARCHAR(30), `repeatability4`  VARCHAR(30), `repeatability5`  VARCHAR(30),
  `repeat_value_1`    VARCHAR(30), `repeat_value_2`  VARCHAR(30), `repeat_value_3`  VARCHAR(30), `repeat_value_4`  VARCHAR(30), `repeat_value_5`  VARCHAR(30),
  `standard_deviation` VARCHAR(30),

  -- ── Eccentricity Test ──
  `applied_load_et`           VARCHAR(50),
  `EC_Indication1_Centre`     VARCHAR(30), `EC_Deviation_Centre`  VARCHAR(30), `EC_Abs_Deviation_Centre` VARCHAR(30),
  `EC_Indication_FL`          VARCHAR(30), `EC_Deviation_FL`      VARCHAR(30), `EC_Abs_Deviation_FL`     VARCHAR(30),
  `EC_Indication_BL`          VARCHAR(30), `EC_Deviation_BL`      VARCHAR(30), `EC_Abs_Deviation_BL`     VARCHAR(30),
  `EC_Indication_BR`          VARCHAR(30), `EC_Deviation_BR`      VARCHAR(30), `EC_Abs_Deviation_BR`     VARCHAR(30),
  `EC_Indication_FR`          VARCHAR(30), `EC_Deviation_FR`      VARCHAR(30), `EC_Abs_Deviation_FR`     VARCHAR(30),
  `max_difference`            VARCHAR(30),

  -- ── Uncertainty Calculation (5 columns) ──
  `Idig_1`       VARCHAR(30), `Idig_2`       VARCHAR(30), `Idig_3`       VARCHAR(30), `Idig_4`       VARCHAR(30), `Idig_5`       VARCHAR(30),
  `u_IdigL_1`    VARCHAR(30), `u_IdigL_2`    VARCHAR(30), `u_IdigL_3`    VARCHAR(30), `u_IdigL_4`    VARCHAR(30), `u_IdigL_5`    VARCHAR(30),
  `Iecc_value_1` VARCHAR(30), `Iecc_value_2` VARCHAR(30), `Iecc_value_3` VARCHAR(30), `Iecc_value_4` VARCHAR(30), `Iecc_value_5` VARCHAR(30),
  `u_omc_1`      VARCHAR(30), `u_omc_2`      VARCHAR(30), `u_omc_3`      VARCHAR(30), `u_omc_4`      VARCHAR(30), `u_omc_5`      VARCHAR(30),
  `u_mB_1`       VARCHAR(30), `u_mB_2`       VARCHAR(30), `u_mB_3`       VARCHAR(30), `u_mB_4`       VARCHAR(30), `u_mB_5`       VARCHAR(30),
  `u_mD_1`       VARCHAR(30), `u_mD_2`       VARCHAR(30), `u_mD_3`       VARCHAR(30), `u_mD_4`       VARCHAR(30), `u_mD_5`       VARCHAR(30),
  `u_mconv_1`    VARCHAR(30), `u_mconv_2`    VARCHAR(30), `u_mconv_3`    VARCHAR(30), `u_mconv_4`    VARCHAR(30), `u_mconv_5`    VARCHAR(30),
  `u_E_1`        VARCHAR(30), `u_E_2`        VARCHAR(30), `u_E_3`        VARCHAR(30), `u_E_4`        VARCHAR(30), `u_E_5`        VARCHAR(30),
  `Veff_value_1` VARCHAR(30), `Veff_value_2` VARCHAR(30), `Veff_value_3` VARCHAR(30), `Veff_value_4` VARCHAR(30), `Veff_value_5` VARCHAR(30),
  `K_value_1`    VARCHAR(30), `K_value_2`    VARCHAR(30), `K_value_3`    VARCHAR(30), `K_value_4`    VARCHAR(30), `K_value_5`    VARCHAR(30),
  `U_value_1`    VARCHAR(30), `U_value_2`    VARCHAR(30), `U_value_3`    VARCHAR(30), `U_value_4`    VARCHAR(30), `U_value_5`    VARCHAR(30),

  `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;
