-- ============================================================
-- MetLAB - Metrology Laboratory Management System
-- Ghana Standards Authority
-- Database Schema v1.0
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `metlab_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `metlab_db`;

-- ============================================================
-- LABORATORIES
-- ============================================================
CREATE TABLE `laboratories` (
  `lab_id`       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lab_name`     VARCHAR(200) NOT NULL,
  `lab_code`     VARCHAR(50)  NOT NULL UNIQUE,
  `department`   VARCHAR(200) NOT NULL,
  `description`  TEXT,
  `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE `users` (
  `user_id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `officer_name`     VARCHAR(150) NOT NULL,
  `email`            VARCHAR(150) NOT NULL UNIQUE,
  `phone_number`     VARCHAR(20),
  `role`             ENUM('admin','front_desk','lab_officer') NOT NULL DEFAULT 'front_desk',
  `password`         VARCHAR(255) NOT NULL,
  `signature`        VARCHAR(255) COMMENT 'path to uploaded signature image',
  `encryption_code`  VARCHAR(255) COMMENT 'bcrypt-hashed OTP sent to phone',
  `otp_expires_at`   DATETIME,
  `is_active`        TINYINT(1) NOT NULL DEFAULT 1,
  `last_login`       DATETIME,
  `date_created`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- USER ↔ LABORATORY ASSIGNMENTS (many-to-many)
-- ============================================================
CREATE TABLE `user_lab_assignments` (
  `assignment_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED NOT NULL,
  `lab_id`        INT UNSIGNED NOT NULL,
  `assigned_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_user_lab` (`user_id`, `lab_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
  FOREIGN KEY (`lab_id`)  REFERENCES `laboratories`(`lab_id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- COMPANIES
-- ============================================================
CREATE TABLE `companies` (
  `company_id`   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(200) NOT NULL,
  `address`      TEXT,
  `phone`        VARCHAR(30),
  `email`        VARCHAR(150),
  `created_by`   INT UNSIGNED,
  `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- CONTACT PERSONS
-- ============================================================
CREATE TABLE `contact_persons` (
  `contact_id`   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id`   INT UNSIGNED NOT NULL,
  `contact_name` VARCHAR(150) NOT NULL,
  `phone_number` VARCHAR(30),
  `email`        VARCHAR(150),
  `position`     VARCHAR(100),
  `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- EQUIPMENT
-- ============================================================
CREATE TABLE `equipment` (
  `equipment_id`    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id`      INT UNSIGNED NOT NULL,
  `equipment_name`  VARCHAR(200) NOT NULL,
  `model`           VARCHAR(100),
  `serial_number`   VARCHAR(100),
  `manufacturer`    VARCHAR(150),
  `range_capacity`  VARCHAR(100),
  `date_created`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- CALIBRATION / TESTING REQUESTS
-- ============================================================
CREATE TABLE `requests` (
  `request_id`      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `request_number`  VARCHAR(50) NOT NULL UNIQUE,
  `company_id`      INT UNSIGNED NOT NULL,
  `equipment_id`    INT UNSIGNED NOT NULL,
  `lab_id`          INT UNSIGNED NOT NULL,
  `contact_id`      INT UNSIGNED,
  `created_by`      INT UNSIGNED NOT NULL,
  `assigned_to`     INT UNSIGNED COMMENT 'lab officer assigned',
  `request_date`    DATE NOT NULL,
  `status`          ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `priority`        ENUM('normal','urgent') NOT NULL DEFAULT 'normal',
  `notes`           TEXT,
  `date_created`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `date_updated`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`)   REFERENCES `companies`(`company_id`),
  FOREIGN KEY (`equipment_id`) REFERENCES `equipment`(`equipment_id`),
  FOREIGN KEY (`lab_id`)       REFERENCES `laboratories`(`lab_id`),
  FOREIGN KEY (`contact_id`)   REFERENCES `contact_persons`(`contact_id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`)   REFERENCES `users`(`user_id`),
  FOREIGN KEY (`assigned_to`)  REFERENCES `users`(`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- LABORATORY WORK FORMS (generic, reusable)
-- ============================================================
CREATE TABLE `lab_work_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,
  -- Environmental Conditions
  `temperature_min`      DECIMAL(8,3),
  `temperature_max`      DECIMAL(8,3),
  `humidity_min`         DECIMAL(6,2),
  `humidity_max`         DECIMAL(6,2),
  `pressure_min`         DECIMAL(10,3),
  `pressure_max`         DECIMAL(10,3),
  `place_of_test`        VARCHAR(200),
  -- Standards / Reference Equipment
  `reference_equipment`  TEXT COMMENT 'JSON array of reference equipment used',
  -- Calibration Procedure
  `procedure_used`       VARCHAR(300),
  `norm_standard`        VARCHAR(200),
  -- Measurement Results (stored as JSON for flexibility across 9 labs)
  `measurement_data`     LONGTEXT COMMENT 'JSON: results tables',
  -- Uncertainty
  `uncertainty_statement` TEXT,
  `coverage_factor`      DECIMAL(5,2) DEFAULT 2.00,
  `confidence_level`     VARCHAR(20) DEFAULT '95%',
  -- Remarks
  `remarks`              TEXT,
  -- Status
  `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;

-- ============================================================
-- CERTIFICATES
-- ============================================================
CREATE TABLE `certificates` (
  `cert_id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `cert_number`      VARCHAR(100) NOT NULL UNIQUE,
  `request_id`       INT UNSIGNED NOT NULL,
  `form_id`          INT UNSIGNED NULL COMMENT 'ID in the lab-specific form table (no FK — table varies by lab type)',
  `issued_by`        INT UNSIGNED NOT NULL,
  `pdf_path`         VARCHAR(500),
  `xml_path`         VARCHAR(500),
  `issue_date`       DATE NOT NULL,
  `date_created`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`request_id`) REFERENCES `requests`(`request_id`),
  FOREIGN KEY (`issued_by`)  REFERENCES `users`(`user_id`)
) ENGINE=InnoDB;

-- ============================================================
-- ACTIVITY LOGS
-- ============================================================
CREATE TABLE `activity_logs` (
  `log_id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED,
  `user_role`     VARCHAR(30),
  `action`        VARCHAR(100) NOT NULL,
  `module`        VARCHAR(50)  NOT NULL,
  `description`   TEXT,
  `record_id`     INT UNSIGNED COMMENT 'ID of affected record',
  `ip_address`    VARCHAR(45),
  `user_agent`    VARCHAR(300),
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default Admin
INSERT INTO `users` (`officer_name`, `email`, `role`, `password`, `is_active`) VALUES
('System Administrator', 'admin@gsa.gov.gh', 'admin', '$2y$12$X4kv7j5Z1vZ5X4kv7j5ZXe8YfQhI9WnJ6mL3nP2qR7sT0uV1wY2z', 1);
-- Default password: Admin@12345 (bcrypt - regenerate in production)

-- Nine Laboratories
INSERT INTO `laboratories` (`lab_name`, `lab_code`, `department`) VALUES
('Mass and Related Quantities Laboratory',    'MRQL', 'Scientific Metrology'),
('Length and Dimensional Metrology',          'LDM',  'Scientific Metrology'),
('Temperature and Humidity Laboratory',       'THL',  'Scientific Metrology'),
('Electrical Quantities Laboratory',          'EQL',  'Scientific Metrology'),
('Pressure and Vacuum Laboratory',            'PVL',  'Scientific Metrology'),
('Flow Measurement Laboratory',               'FML',  'Scientific Metrology'),
('Force and Torque Laboratory',               'FTL',  'Scientific Metrology'),
('Optical and Photometry Laboratory',         'OPL',  'Scientific Metrology'),
('Chemical and Analytical Laboratory',        'CAL',  'Scientific Metrology');
