-- Adminer 4.7.6 MySQL dump

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `auditor_competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditor_name` varchar(255) NOT NULL,
  `auditor_email` varchar(255) NOT NULL,
  `auditor_phone` varchar(20) NOT NULL,
  `education_level` enum('below_high_school','high_school','some_college','associate_degree','trade_school','bachelor_degree','advanced_degree','audit_training','other_specialized_training') NOT NULL,
  `certifications` text NOT NULL,
  `experience` text NOT NULL,
  `training` text NOT NULL,
  `technical_expertise` text NOT NULL,
  `competency_assessment` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `auditor_email` (`auditor_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `audit_profile_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `company_name` varchar(255) NOT NULL,
  `auditor_id` varchar(100) NOT NULL,
  `site_type` enum('single','multiple') NOT NULL,
  `audit_type` enum('internal','supplyChain') NOT NULL,
  `standards` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `audit_profile_info` (`id`, `company_id`, `company_name`, `auditor_id`, `site_type`, `audit_type`, `standards`, `created_at`) VALUES
(1,	1,	'Jax Code Academy, Inc.',	'1',	'single',	'internal',	'ISO 9001',	'2025-02-19 15:23:49'),
(2,	2,	'Synergy Solutions',	'2',	'multiple',	'supplyChain',	'ISO 27001',	'2025-02-19 15:23:49'),
(3,	3,	'FitZone Clinic',	'3',	'single',	'internal',	'ISO 13485',	'2025-02-19 15:23:49');

CREATE TABLE `corrective_action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_id` int(11) NOT NULL,
  `finding_id` int(11) NOT NULL,
  `corrective_action_description` text NOT NULL,
  `responsible_person` varchar(255) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('Pending','In Progress','Completed') DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_id` (`audit_id`),
  KEY `finding_id` (`finding_id`),
  CONSTRAINT `corrective_action_ibfk_1` FOREIGN KEY (`audit_id`) REFERENCES `audit_profile_info` (`id`),
  CONSTRAINT `corrective_action_ibfk_2` FOREIGN KEY (`finding_id`) REFERENCES `findings` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `corrective_action` (`id`, `audit_id`, `finding_id`, `corrective_action_description`, `responsible_person`, `due_date`, `status`, `created_at`) VALUES
(1,	1,	1,	'Implement a revised document review process',	'John Doe',	'2025-03-15',	'In Progress',	'2025-02-19 15:23:49'),
(2,	1,	2,	'Ensure all staff complete training records by next audit',	'Jane Smith',	'2025-03-20',	'Pending',	'2025-02-19 15:23:49'),
(3,	2,	3,	'Engage supplier in compliance training and review',	'Phillip Randazzo',	'2025-03-30',	'In Progress',	'2025-02-19 15:23:49'),
(4,	3,	4,	'Update medical device labels to match regulatory standards',	'Stephen Fitzmeyer',	'2025-04-01',	'Pending',	'2025-02-19 15:23:49');

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `unique_number` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `email` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `findings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_id` int(11) NOT NULL,
  `finding_description` text NOT NULL,
  `severity_level` enum('Low','Medium','High') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_id` (`audit_id`),
  CONSTRAINT `findings_ibfk_1` FOREIGN KEY (`audit_id`) REFERENCES `audit_profile_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `findings` (`id`, `audit_id`, `finding_description`, `severity_level`, `created_at`) VALUES
(1,	1,	'Nonconformance in documentation process',	'Medium',	'2025-02-19 15:23:49'),
(2,	1,	'Missing training records',	'Low',	'2025-02-19 15:23:49'),
(3,	2,	'Supplier compliance issue detected',	'High',	'2025-02-19 15:23:49'),
(4,	3,	'Incorrect labeling on medical devices',	'Medium',	'2025-02-19 15:23:49');

CREATE TABLE `myisopro_audit_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organization_id` int(11) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('planned','in_progress','complete') DEFAULT 'planned',
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization_id` (`organization_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `myisopro_audit_plans_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `myisopro_organizations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `myisopro_audit_plans_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `myisopro_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `myisopro_audit_plans` (`id`, `organization_id`, `start_date`, `end_date`, `status`, `user_id`) VALUES
(4,	1,	'2025-03-01',	'2025-03-05',	'planned',	1),
(5,	2,	'2025-03-10',	'2025-03-15',	'in_progress',	2),
(6,	3,	'2025-03-20',	'2025-03-25',	'complete',	3);

CREATE TABLE `myisopro_organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `site_type` enum('single','multiple') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `myisopro_organizations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `myisopro_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `myisopro_organizations` (`id`, `name`, `address`, `site_type`, `created_at`, `user_id`) VALUES
(1,	'Jax Code Academy, Inc.',	'14615 Lagoon Dr., Jacksonville Beach, FL, 32250',	'single',	'2025-02-19 15:27:43',	1),
(2,	'Synergy Solutions',	'727 Brookmont Ave E, Jacksonville, FL, 32211',	'multiple',	'2025-02-19 15:27:43',	2),
(3,	'FitZone Clinic',	'245 Riverside Ave, Jacksonville Beach, FL, 32250',	'single',	'2025-02-19 15:27:43',	3);

CREATE TABLE `myisopro_sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organization_id` int(11) NOT NULL,
  `site_name` varchar(255) NOT NULL,
  `address` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `myisopro_sites_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `myisopro_organizations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE `notes_evidence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_id` int(11) NOT NULL,
  `note_text` text NOT NULL,
  `evidence` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_id` (`audit_id`),
  CONSTRAINT `notes_evidence_ibfk_1` FOREIGN KEY (`audit_id`) REFERENCES `audit_profile_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `notes_evidence` (`id`, `audit_id`, `note_text`, `evidence`, `created_at`) VALUES
(1,	1,	'Reviewed documentation compliance.',	'Attached report.pdf',	'2025-02-19 15:23:49'),
(2,	2,	'Supplier audit findings recorded.',	'Audit checklist completed.',	'2025-02-19 15:23:49'),
(3,	3,	'Medical device labeling inconsistencies noted.',	'Photographic evidence included.',	'2025-02-19 15:23:49');

CREATE TABLE `plan_scope` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_id` int(11) NOT NULL,
  `audit_scope` text NOT NULL,
  `audit_objectives` text NOT NULL,
  `audit_criteria` text NOT NULL,
  `plan_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_id` (`audit_id`),
  CONSTRAINT `plan_scope_ibfk_1` FOREIGN KEY (`audit_id`) REFERENCES `audit_profile_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `report_formatting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_id` int(11) NOT NULL,
  `header_text` varchar(255) DEFAULT NULL,
  `footer_text` varchar(255) DEFAULT NULL,
  `font_style` varchar(50) DEFAULT NULL,
  `margin_top` decimal(5,2) DEFAULT NULL,
  `margin_bottom` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `audit_id` (`audit_id`),
  CONSTRAINT `report_formatting_ibfk_1` FOREIGN KEY (`audit_id`) REFERENCES `audit_profile_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `user_progress` (
  `user_id` int(11) NOT NULL,
  `progress_scope` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `user_progress` (`user_id`, `progress_scope`) VALUES
(1,	0);

-- 2025-03-02 20:09:17
