-- =====================================================
-- LIMS Complete Database Structure
-- Database Migration Script for pafda_config
-- =====================================================
-- This script creates all necessary tables for the
-- Laboratory Information Management System
-- Generated from: pafda_config database
-- Date: 2025-12-07
-- =====================================================

-- Set SQL mode and character set
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- Database: pafda_config
-- =====================================================

-- =====================================================
-- RBAC & User Management Tables
-- =====================================================

-- Table: users
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) UNIQUE NOT NULL,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login` TIMESTAMP NULL,
  INDEX `idx_username` (`username`),
  INDEX `idx_email` (`email`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `role_name` VARCHAR(100) UNIQUE NOT NULL,
  `role_code` VARCHAR(50) UNIQUE NOT NULL,
  `description` TEXT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_role_code` (`role_code`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `permission_name` VARCHAR(100) UNIQUE NOT NULL,
  `permission_code` VARCHAR(50) UNIQUE NOT NULL,
  `resource` VARCHAR(100) NOT NULL COMMENT 'Page or module name',
  `action` ENUM('create', 'read', 'update', 'delete', 'execute') NOT NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_resource` (`resource`),
  INDEX `idx_action` (`action`),
  INDEX `idx_permission_code` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: role_permissions
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `role_id` INT NOT NULL,
  `permission_id` INT NOT NULL,
  `granted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `granted_by` INT NULL,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`granted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `unique_role_permission` (`role_id`, `permission_id`),
  INDEX `idx_role` (`role_id`),
  INDEX `idx_permission` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Location & Organization Tables
-- =====================================================

-- Table: provinces
CREATE TABLE IF NOT EXISTS `provinces` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `province_name` VARCHAR(100) NOT NULL,
  `province_code` VARCHAR(10) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_province_name` (`province_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: divisions
CREATE TABLE IF NOT EXISTS `divisions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `division_name` VARCHAR(100) NOT NULL,
  `division_code` VARCHAR(10) NULL,
  `province_id` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`province_id`) REFERENCES `provinces`(`id`) ON DELETE SET NULL,
  INDEX `idx_division_name` (`division_name`),
  INDEX `idx_province` (`province_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: districts
CREATE TABLE IF NOT EXISTS `districts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `district_name` VARCHAR(100) NOT NULL,
  `district_code` VARCHAR(10) NULL,
  `division_id` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`division_id`) REFERENCES `divisions`(`id`) ON DELETE SET NULL,
  INDEX `idx_district_name` (`district_name`),
  INDEX `idx_division` (`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: organizational_units
CREATE TABLE IF NOT EXISTS `organizational_units` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `unit_name` VARCHAR(255) NOT NULL,
  `unit_code` VARCHAR(50) NULL,
  `unit_type` VARCHAR(50) NULL,
  `parent_unit_id` INT NULL,
  `district_id` INT NULL,
  `address` TEXT NULL,
  `contact_person` VARCHAR(255) NULL,
  `contact_phone` VARCHAR(20) NULL,
  `contact_email` VARCHAR(255) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`parent_unit_id`) REFERENCES `organizational_units`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE SET NULL,
  INDEX `idx_unit_name` (`unit_name`),
  INDEX `idx_unit_code` (`unit_code`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Sample Management Tables
-- =====================================================

-- Table: sample_collection_sites
CREATE TABLE IF NOT EXISTS `sample_collection_sites` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `site_name` VARCHAR(255) NOT NULL,
  `site_code` VARCHAR(50) NULL,
  `district_id` INT NULL,
  `address` TEXT NULL,
  `contact_person` VARCHAR(255) NULL,
  `contact_phone` VARCHAR(20) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`district_id`) REFERENCES `districts`(`id`) ON DELETE SET NULL,
  INDEX `idx_site_name` (`site_name`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: containers
CREATE TABLE IF NOT EXISTS `containers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `container_name` VARCHAR(100) NOT NULL,
  `container_code` VARCHAR(50) NULL,
  `description` TEXT NULL,
  `capacity` VARCHAR(50) NULL,
  `material` VARCHAR(100) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_container_name` (`container_name`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: packaging_types
CREATE TABLE IF NOT EXISTS `packaging_types` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `packaging_name` VARCHAR(100) NOT NULL,
  `packaging_code` VARCHAR(50) NULL,
  `description` TEXT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_packaging_name` (`packaging_name`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: samples
CREATE TABLE IF NOT EXISTS `samples` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sample_number` VARCHAR(100) UNIQUE NOT NULL,
  `sample_name` VARCHAR(255) NOT NULL,
  `sample_type` VARCHAR(100) NULL,
  `collection_site_id` INT NULL,
  `collection_date` DATE NULL,
  `received_date` DATE NULL,
  `container_id` INT NULL,
  `packaging_type_id` INT NULL,
  `quantity` VARCHAR(50) NULL,
  `unit` VARCHAR(20) NULL,
  `status` ENUM('pending', 'in_progress', 'completed', 'rejected', 'cancelled') DEFAULT 'pending',
  `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
  `notes` TEXT NULL,
  `created_by` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`collection_site_id`) REFERENCES `sample_collection_sites`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`packaging_type_id`) REFERENCES `packaging_types`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_sample_number` (`sample_number`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: sample_accessioning
CREATE TABLE IF NOT EXISTS `sample_accessioning` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sample_id` INT NOT NULL,
  `accession_number` VARCHAR(100) UNIQUE NOT NULL,
  `received_by` INT NULL,
  `received_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `condition` VARCHAR(100) NULL,
  `temperature` VARCHAR(50) NULL,
  `storage_location` VARCHAR(255) NULL,
  `assigned_to` INT NULL,
  `status` ENUM('received', 'accessioned', 'assigned', 'in_analysis', 'completed', 'returned') DEFAULT 'received',
  `notes` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`sample_id`) REFERENCES `samples`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_accession_number` (`accession_number`),
  INDEX `idx_sample` (`sample_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Testing & Analysis Tables
-- =====================================================

-- Table: methods
CREATE TABLE IF NOT EXISTS `methods` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `method_name` VARCHAR(255) NOT NULL,
  `method_code` VARCHAR(50) NULL,
  `description` TEXT NULL,
  `standard_reference` VARCHAR(255) NULL,
  `category` VARCHAR(100) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_method_name` (`method_name`),
  INDEX `idx_method_code` (`method_code`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: instruments
CREATE TABLE IF NOT EXISTS `instruments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `instrument_name` VARCHAR(255) NOT NULL,
  `instrument_code` VARCHAR(50) NULL,
  `model` VARCHAR(100) NULL,
  `manufacturer` VARCHAR(100) NULL,
  `serial_number` VARCHAR(100) NULL,
  `location` VARCHAR(255) NULL,
  `calibration_date` DATE NULL,
  `next_calibration_date` DATE NULL,
  `status` ENUM('operational', 'maintenance', 'calibration', 'out_of_service') DEFAULT 'operational',
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_instrument_name` (`instrument_name`),
  INDEX `idx_status` (`status`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ingredients
CREATE TABLE IF NOT EXISTS `ingredients` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ingredient_name` VARCHAR(255) NOT NULL,
  `ingredient_code` VARCHAR(50) NULL,
  `cas_number` VARCHAR(50) NULL,
  `category` VARCHAR(100) NULL,
  `description` TEXT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_ingredient_name` (`ingredient_name`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: panel_companies
CREATE TABLE IF NOT EXISTS `panel_companies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(255) NOT NULL,
  `company_code` VARCHAR(50) NULL,
  `registration_number` VARCHAR(100) NULL,
  `address` TEXT NULL,
  `contact_person` VARCHAR(255) NULL,
  `contact_phone` VARCHAR(20) NULL,
  `contact_email` VARCHAR(255) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_company_name` (`company_name`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: analyst_test_results
CREATE TABLE IF NOT EXISTS `analyst_test_results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `barcode` VARCHAR(50) NOT NULL,
  `analyst_id` VARCHAR(255) NOT NULL,
  `analyst_name` VARCHAR(255) NOT NULL,
  `test_results` JSON NULL DEFAULT NULL COMMENT 'JSON object containing test results',
  `comments` TEXT NULL DEFAULT NULL,
  `reviewer_comments` TEXT NULL DEFAULT NULL,
  `details` TEXT NULL DEFAULT NULL,
  `attachments` JSON NULL DEFAULT NULL COMMENT 'JSON array of file attachments',
  `reviewer_attachments` JSON NULL DEFAULT NULL COMMENT 'JSON array of reviewer attachments',
  `status` ENUM('pending', 'in-progress', 'draft', 'completed', 'approved', 'rejected') DEFAULT 'pending',
  `started_date` TIMESTAMP NULL DEFAULT NULL,
  `completed_date` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `review_status` ENUM('pending', 'draft', 'reviewed', 'returned', 'approved') DEFAULT 'pending',
  `reviewed_by` VARCHAR(255) NULL DEFAULT NULL,
  `reviewed_at` DATETIME NULL DEFAULT NULL,
  INDEX `idx_barcode` (`barcode`),
  INDEX `idx_analyst_id` (`analyst_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: test_transfer
CREATE TABLE IF NOT EXISTS `test_transfer` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sample_id` INT NOT NULL,
  `test_id` INT NULL,
  `from_user_id` INT NULL,
  `to_user_id` INT NULL,
  `transfer_type` ENUM('assignment', 'reassignment', 'return') DEFAULT 'assignment',
  `reason` TEXT NULL,
  `transferred_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `transferred_by` INT NULL,
  FOREIGN KEY (`sample_id`) REFERENCES `samples`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`test_id`) REFERENCES `analyst_test_results`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`from_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`to_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`transferred_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_sample` (`sample_id`),
  INDEX `idx_from_user` (`from_user_id`),
  INDEX `idx_to_user` (`to_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Review & Quality Control Tables
-- =====================================================

-- Table: rejection_reasons
CREATE TABLE IF NOT EXISTS `rejection_reasons` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `reason_code` VARCHAR(50) UNIQUE NOT NULL,
  `reason_description` TEXT NOT NULL,
  `category` VARCHAR(100) NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_reason_code` (`reason_code`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: reviewer_comments
CREATE TABLE IF NOT EXISTS `reviewer_comments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sample_id` INT NOT NULL,
  `test_result_id` INT NULL,
  `reviewer_id` INT NOT NULL,
  `comment_type` ENUM('approval', 'rejection', 'query', 'note') DEFAULT 'note',
  `comment_text` TEXT NOT NULL,
  `rejection_reason_id` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`sample_id`) REFERENCES `samples`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`test_result_id`) REFERENCES `analyst_test_results`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`rejection_reason_id`) REFERENCES `rejection_reasons`(`id`) ON DELETE SET NULL,
  INDEX `idx_sample` (`sample_id`),
  INDEX `idx_reviewer` (`reviewer_id`),
  INDEX `idx_type` (`comment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Commit Transaction
-- =====================================================
COMMIT;

-- =====================================================
-- Verification Queries
-- =====================================================
-- Run these to verify tables were created successfully:
-- SHOW TABLES;
-- SELECT COUNT(*) as total_tables FROM information_schema.tables WHERE table_schema = 'pafda_config';
