-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema cyberpal_database_nist
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `cyberpal_database_nist` ;

-- -----------------------------------------------------
-- Schema cyberpal_database_nist
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `cyberpal_database_nist` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `cyberpal_database_nist` ;

-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Categories`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `component_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 24
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `component_id` ON `cyberpal_database_nist`.`Categories` (`component_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ClientData`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ClientData` (
  `ApplicationName` VARCHAR(255) NULL DEFAULT NULL,
  `Description` TEXT NULL DEFAULT NULL,
  `DivisionDept` VARCHAR(255) NULL DEFAULT NULL,
  `Tier` INT NULL DEFAULT NULL,
  `BusinessOwner` VARCHAR(255) NULL DEFAULT NULL,
  `BizImpact` VARCHAR(50) NULL DEFAULT NULL,
  `FIPS-Confidentiality` VARCHAR(20) NULL DEFAULT NULL,
  `FIPS-Integrity` VARCHAR(20) NULL DEFAULT NULL,
  `FIPS-Availability` VARCHAR(20) NULL DEFAULT NULL,
  `ProjectLead` VARCHAR(255) NULL DEFAULT NULL,
  `PII` VARCHAR(10) NULL DEFAULT NULL,
  `NotesComments` TEXT NULL DEFAULT NULL)
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Companies`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Companies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `alias` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_company_name` ON `cyberpal_database_nist`.`Companies` (`name` ASC) VISIBLE;

CREATE INDEX `idx_company_alias` ON `cyberpal_database_nist`.`Companies` (`alias` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Components`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Components` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Departments`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Departments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `state_entity` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `org_code` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 238
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_department_name` ON `cyberpal_database_nist`.`Departments` (`name` ASC) VISIBLE;

CREATE INDEX `idx_department_state_entity` ON `cyberpal_database_nist`.`Departments` (`state_entity` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Owasp_Categories`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Owasp_Categories` (
  `id` INT NOT NULL DEFAULT '0',
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT NULL DEFAULT NULL)
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Owasp_Category_Scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Owasp_Category_Scores` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `category_id` INT NOT NULL,
  `possibility_score` FLOAT NULL DEFAULT NULL,
  `impact_score` FLOAT NULL DEFAULT NULL,
  `risk_score` FLOAT NULL DEFAULT NULL,
  `status` ENUM('Applicable', 'Not Applicable') NULL DEFAULT 'Applicable',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 59
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Owasp_Category_Scores` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Owasp_Questions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Owasp_Questions` (
  `id` INT NOT NULL DEFAULT '0',
  `category_id` INT NULL DEFAULT NULL,
  `question_text` TEXT NULL DEFAULT NULL)
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`PII_Scans`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`PII_Scans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(10) NOT NULL,
  `redacted_content` TEXT NOT NULL,
  `scan_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 44
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`PII_Scans` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`POAM_BLOB`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`POAM_BLOB` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_data` LONGBLOB NULL DEFAULT NULL,
  `uploaded_date` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 59
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`QuestionSets`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`QuestionSets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_type` VARCHAR(255) NULL DEFAULT NULL,
  `user_role` VARCHAR(255) NULL DEFAULT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Questions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Questions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `set_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_type` VARCHAR(255) NULL DEFAULT NULL,
  `question` TEXT NULL DEFAULT NULL,
  `subcategory_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `category_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `component_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 137
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `set_id` ON `cyberpal_database_nist`.`Questions` (`set_id` ASC) VISIBLE;

CREATE INDEX `subcategory_id` ON `cyberpal_database_nist`.`Questions` (`subcategory_id` ASC) VISIBLE;

CREATE INDEX `category_id` ON `cyberpal_database_nist`.`Questions` (`category_id` ASC) VISIBLE;

CREATE INDEX `component_id` ON `cyberpal_database_nist`.`Questions` (`component_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_detect`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_detect` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_final_scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_final_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `govern_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `identify_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `protect_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `detect_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `respond_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `recover_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `maturity_level` DECIMAL(5,2) NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `department_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `simm5300c_final_scores_ibfk_1` ON `cyberpal_database_nist`.`SIMM5300C_2025_final_scores` (`user_id` ASC) VISIBLE;

CREATE INDEX `fk_final_scores_department` ON `cyberpal_database_nist`.`SIMM5300C_2025_final_scores` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_govern`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_govern` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_identify`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_identify` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_protect`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_protect` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_recover`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_recover` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_respond`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_respond` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `Assessed Criteria` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_name` ENUM('govern', 'identify', 'protect', 'detect', 'respond', 'recover') NOT NULL,
  `row_id` BIGINT UNSIGNED NOT NULL,
  `score` DECIMAL(3,1) NOT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `updated_by` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `department_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 70
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `unique_score` ON `cyberpal_database_nist`.`SIMM5300C_2025_scores` (`table_name` ASC, `row_id` ASC, `created_by` ASC, `department_id` ASC) VISIBLE;

CREATE INDEX `fk_scores_department` ON `cyberpal_database_nist`.`SIMM5300C_2025_scores` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_2025_subcategory_mapping_score_calculation`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_2025_subcategory_mapping_score_calculation` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_name` ENUM('govern', 'identify', 'protect', 'detect', 'respond', 'recover') NOT NULL,
  `row_id` BIGINT UNSIGNED NOT NULL,
  `subcategories` JSON NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 39
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `unique_row_mapping` ON `cyberpal_database_nist`.`SIMM5300C_2025_subcategory_mapping_score_calculation` (`table_name` ASC, `row_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_department_assessments`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_department_assessments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `department` VARCHAR(255) NULL DEFAULT NULL,
  `assessment_year` INT NOT NULL,
  `component_type` ENUM('identify', 'protect', 'detect', 'respond', 'recover') NOT NULL,
  `status` ENUM('not_started', 'in_progress', 'completed') NULL DEFAULT 'not_started',
  `last_accessed_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_dept_assessment` ON `cyberpal_database_nist`.`SIMM5300C_department_assessments` (`department` ASC, `assessment_year` ASC, `component_type` ASC) VISIBLE;

CREATE INDEX `last_accessed_by` ON `cyberpal_database_nist`.`SIMM5300C_department_assessments` (`last_accessed_by` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_final_scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_final_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `identify_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `protect_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `detect_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `respond_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `recover_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `maturity_level` DECIMAL(5,2) NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `department_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `simm5300c_final_scores_ibfk_1` ON `cyberpal_database_nist`.`SIMM5300C_final_scores` (`user_id` ASC) VISIBLE;

CREATE INDEX `fk_final_scores_department` ON `cyberpal_database_nist`.`SIMM5300C_final_scores` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM5300C_subcategory_mapping_score_calculation`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM5300C_subcategory_mapping_score_calculation` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_name` ENUM('identify', 'protect', 'detect', 'respond', 'recover') NOT NULL,
  `row_id` BIGINT UNSIGNED NOT NULL,
  `subcategories` JSON NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 32
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_row_mapping` ON `cyberpal_database_nist`.`SIMM5300C_subcategory_mapping_score_calculation` (`table_name` ASC, `row_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_5330E_form`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_5330E_form` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `entity_name` VARCHAR(255) NOT NULL,
  `org_code` VARCHAR(50) NOT NULL,
  `no_host_relationship` TINYINT(1) NOT NULL,
  `meets_criteria` TINYINT(1) NOT NULL,
  `host_entity_name` VARCHAR(255) NULL DEFAULT NULL,
  `host_entity_org_code` VARCHAR(50) NULL DEFAULT NULL,
  `hosted_entity_name` VARCHAR(255) NULL DEFAULT NULL,
  `hosted_entity_org_code` VARCHAR(50) NULL DEFAULT NULL,
  `host_entity_cio` VARCHAR(255) NULL DEFAULT NULL,
  `host_entity_iso` VARCHAR(255) NULL DEFAULT NULL,
  `host_agency_cio` VARCHAR(255) NULL DEFAULT NULL,
  `host_agency_iso` VARCHAR(255) NULL DEFAULT NULL,
  `hosted_entity_cio` VARCHAR(255) NULL DEFAULT NULL,
  `hosted_entity_iso` VARCHAR(255) NULL DEFAULT NULL,
  `hosted_agency_cio` VARCHAR(255) NULL DEFAULT NULL,
  `hosted_agency_iso` VARCHAR(255) NULL DEFAULT NULL,
  `created_by_id` INT NOT NULL,
  `created_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_5330E_form_audit_logs`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_5330E_form_audit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `form_id` INT NOT NULL,
  `action_by_id` INT NOT NULL,
  `action_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `form_data` JSON NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `form_id` ON `cyberpal_database_nist`.`SIMM_5330E_form_audit_logs` (`form_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_5360B_form`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_5360B_form` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_name` VARCHAR(255) NOT NULL,
  `employee_telephone` VARCHAR(50) NOT NULL,
  `supervisor_manager_name` VARCHAR(255) NOT NULL,
  `supervisor_manager_telephone` VARCHAR(50) NOT NULL,
  `office_branch` VARCHAR(255) NOT NULL,
  `employee_email` VARCHAR(255) NOT NULL,
  `supervisor_manager_email` VARCHAR(255) NOT NULL,
  `assets_data` JSON NOT NULL,
  `remote_systems_data` JSON NOT NULL,
  `created_by_id` INT NOT NULL,
  `created_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_5360B_form_audit_logs`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_5360B_form_audit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `form_id` INT NOT NULL,
  `action_by_id` INT NOT NULL,
  `action_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `form_data` JSON NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `form_id` ON `cyberpal_database_nist`.`SIMM_5360B_form_audit_logs` (`form_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_audit_logs`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `score_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `old_value` DECIMAL(3,1) NULL DEFAULT NULL,
  `new_value` DECIMAL(3,1) NULL DEFAULT NULL,
  `action_timestamp` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `department_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 239
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `score_id` ON `cyberpal_database_nist`.`SIMM_audit_logs` (`score_id` ASC) VISIBLE;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`SIMM_audit_logs` (`user_id` ASC) VISIBLE;

CREATE INDEX `fk_audit_department` ON `cyberpal_database_nist`.`SIMM_audit_logs` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SIMM_scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SIMM_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_name` ENUM('identify', 'protect', 'detect', 'respond', 'recover') NOT NULL,
  `row_id` BIGINT UNSIGNED NOT NULL,
  `score` DECIMAL(3,1) NOT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `updated_by` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `department_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 44
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_score` ON `cyberpal_database_nist`.`SIMM_scores` (`table_name` ASC, `row_id` ASC, `created_by` ASC, `department_id` ASC) VISIBLE;

CREATE INDEX `fk_scores_department` ON `cyberpal_database_nist`.`SIMM_scores` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Saves`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Saves` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Saves` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `question_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `subcategory` VARCHAR(255) NULL DEFAULT NULL,
  `component` VARCHAR(255) NULL DEFAULT NULL,
  `category` VARCHAR(255) NULL DEFAULT NULL,
  `score` INT NULL DEFAULT NULL,
  `comments` TEXT NULL DEFAULT NULL,
  `target_state` INT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_submitted` TINYINT(1) NULL DEFAULT '0',
  `submitted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 1168
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Scores` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`SensitiveActionLog`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`SensitiveActionLog` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `action_type` ENUM('PASSWORD_CHANGE', 'MFA_TOGGLE', 'ROLE_CHANGE', 'ADMIN_GRANT') NOT NULL,
  `details` JSON NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`SensitiveActionLog` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Subcategories`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Subcategories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `component_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `category_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 113
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `component_id` ON `cyberpal_database_nist`.`Subcategories` (`component_id` ASC) VISIBLE;

CREATE INDEX `category_id` ON `cyberpal_database_nist`.`Subcategories` (`category_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Submissions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Submissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `submitted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `final_score` DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 60
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Submissions` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`WAC_certification_form`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`WAC_certification_form` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `department_id` INT NOT NULL,
  `contact_name` VARCHAR(255) NOT NULL,
  `contact_email` VARCHAR(255) NOT NULL,
  `agency_website` VARCHAR(255) NOT NULL,
  `certification_date` DATE NOT NULL,
  `cio_name` VARCHAR(255) NOT NULL,
  `director_name` VARCHAR(255) NOT NULL,
  `created_by_id` INT NOT NULL,
  `created_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `department_id` ON `cyberpal_database_nist`.`WAC_certification_form` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`WAC_form_audit_logs`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`WAC_form_audit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `form_id` INT NOT NULL,
  `action_by_id` INT NOT NULL,
  `action_by_email` VARCHAR(255) NULL DEFAULT NULL,
  `form_data` JSON NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `form_id` ON `cyberpal_database_nist`.`WAC_form_audit_logs` (`form_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Web_Scanning_Audit_logs`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Web_Scanning_Audit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `action` VARCHAR(255) NOT NULL,
  `details` TEXT NULL DEFAULT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Web_Scanning_Audit_logs` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`Web_Scanning_scans`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`Web_Scanning_scans` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `site_url` VARCHAR(255) NOT NULL,
  `scan_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `vulnerabilities` JSON NOT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `scan_status` ENUM('pending', 'in_progress', 'completed', 'failed') NULL DEFAULT 'pending',
  `total_vulnerabilities` INT NULL DEFAULT '0',
  `high_severity` INT NULL DEFAULT '0',
  `medium_severity` INT NULL DEFAULT '0',
  `low_severity` INT NULL DEFAULT '0',
  `scan_duration` INT NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 9
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`Web_Scanning_scans` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`assessment_score_history`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`assessment_score_history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `assessment_score` DECIMAL(5,2) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 56
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`detect`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`detect` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `OIS_Foundational_Objective` VARCHAR(1000) NULL DEFAULT NULL,
  `Maturity_Level` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`identify`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`identify` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `OIS_Foundational_Objective` VARCHAR(1000) NULL DEFAULT NULL,
  `Maturity_Level` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`owasp_overall_scores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`owasp_overall_scores` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `overall_possibility` FLOAT NOT NULL,
  `overall_impact` FLOAT NOT NULL,
  `overall_risk_score` FLOAT NOT NULL,
  `risk_level` ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Low',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 13
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`owasp_overall_scores` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`owasp_trend_analysis`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`owasp_trend_analysis` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `year` YEAR NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `parent_category` VARCHAR(100) NULL DEFAULT NULL,
  `category_status` ENUM('New', 'Renamed', 'Merged', 'Unchanged', 'Split') NULL DEFAULT 'Unchanged',
  `rank` INT NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `change_from_previous_year` VARCHAR(10) NULL DEFAULT 'No Change',
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 101
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_compliance_statuses`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_compliance_statuses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `requirement_id` INT NOT NULL,
  `compliance_status` VARCHAR(255) NOT NULL,
  `score` INT NULL DEFAULT '0',
  `calculated_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 253
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`pci_compliance_statuses` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_objectives`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_objectives` (
  `objective_id` INT NOT NULL AUTO_INCREMENT,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`objective_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_question_level_compliance`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_question_level_compliance` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `question_id` INT NOT NULL,
  `requirement_id` INT NOT NULL,
  `compliance_status` VARCHAR(255) NOT NULL,
  `score` INT NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`pci_question_level_compliance` (`user_id` ASC) VISIBLE;

CREATE INDEX `question_id` ON `cyberpal_database_nist`.`pci_question_level_compliance` (`question_id` ASC) VISIBLE;

CREATE INDEX `requirement_id` ON `cyberpal_database_nist`.`pci_question_level_compliance` (`requirement_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_questions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_questions` (
  `question_id` INT NOT NULL AUTO_INCREMENT,
  `requirement_id` INT NOT NULL,
  `text` TEXT NOT NULL,
  `weight` FLOAT NOT NULL DEFAULT '1',
  `question_number` VARCHAR(10) NULL DEFAULT NULL,
  PRIMARY KEY (`question_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 37
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `requirement_id` ON `cyberpal_database_nist`.`pci_questions` (`requirement_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_requirements`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_requirements` (
  `requirement_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `compliance_level` ENUM('high', 'medium', 'low') NULL DEFAULT 'medium',
  `objective_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`requirement_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 13
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `objective_id` ON `cyberpal_database_nist`.`pci_requirements` (`objective_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pci_total_compliance_score`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pci_total_compliance_score` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `total_score` DECIMAL(5,2) NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`pci_total_compliance_score` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`poam`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`poam` (
  `Poam_Id` INT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `State_Entity` VARCHAR(255) NULL DEFAULT NULL,
  `Organization_Code` VARCHAR(255) NULL DEFAULT NULL,
  `Agency` VARCHAR(255) NULL DEFAULT NULL,
  `New_Added_During_Quarter` INT NULL DEFAULT NULL,
  `Completed_During_Quarter` INT NULL DEFAULT NULL,
  `Date_Created` DATETIME NULL DEFAULT NULL,
  `Updated_Date` DATETIME NULL DEFAULT NULL,
  `department_id` INT NULL DEFAULT NULL,
  `is_archived` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Poam_Id`))
ENGINE = MyISAM
AUTO_INCREMENT = 27
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `user_id` ON `cyberpal_database_nist`.`poam` (`user_id` ASC) VISIBLE;

CREATE INDEX `fk_department` ON `cyberpal_database_nist`.`poam` (`department_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`poam_audit`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`poam_audit` (
  `poam_audit_id` BIGINT NOT NULL AUTO_INCREMENT,
  `event_type` VARCHAR(50) NOT NULL,
  `poam_details_id` INT NULL DEFAULT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `timestamp` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `old_data` JSON NULL DEFAULT NULL,
  `new_data` JSON NULL DEFAULT NULL,
  `notes` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`poam_audit_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 897
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_poam_details` ON `cyberpal_database_nist`.`poam_audit` (`poam_details_id` ASC) VISIBLE;

CREATE INDEX `fk_users` ON `cyberpal_database_nist`.`poam_audit` (`user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`poam_details`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`poam_details` (
  `Poam_Details_Id` INT NOT NULL AUTO_INCREMENT,
  `Poam_Id` INT NOT NULL,
  `NIST_Families` VARCHAR(255) NULL DEFAULT NULL,
  `SAM_And_SIMM_Policies` VARCHAR(255) NULL DEFAULT NULL,
  `Weakness_Or_Area_Noncompliance` TEXT NULL DEFAULT NULL,
  `Asset_Risk_Description` TEXT NULL DEFAULT NULL,
  `Poam_Source` VARCHAR(255) NULL DEFAULT NULL,
  `Risk_Response` TEXT NULL DEFAULT NULL,
  `Compensating_Controls` TEXT NULL DEFAULT NULL,
  `Plan_of_Action` TEXT NULL DEFAULT NULL,
  `Shared_Device` VARCHAR(255) NULL DEFAULT NULL,
  `Threat_Likelihood` VARCHAR(255) NULL DEFAULT NULL,
  `Event_Impact` VARCHAR(255) NULL DEFAULT NULL,
  `Risk_Rating` VARCHAR(255) NULL DEFAULT NULL,
  `First_Identified` DATE NULL DEFAULT NULL,
  `Start_Date_Or_Projected_Start` DATE NULL DEFAULT NULL,
  `Projected_Completion_Date` DATE NULL DEFAULT NULL,
  `Actual_Completion_Date` DATE NULL DEFAULT NULL,
  `Projected_Days_Open` INT NULL DEFAULT NULL,
  `Actual_Days_Open` INT NULL DEFAULT NULL,
  `Assigned_to` TEXT NULL DEFAULT NULL,
  `Poam_Status` VARCHAR(255) NULL DEFAULT NULL,
  `Central_Barrier_Or_Constraint` TEXT NULL DEFAULT NULL,
  `Projected_Remediation_Cost` VARCHAR(255) NULL DEFAULT NULL,
  `Status_of_BCP` VARCHAR(255) NULL DEFAULT NULL,
  `BCP_Amount` DECIMAL(15,2) NULL DEFAULT NULL,
  `BCP_Governers_Budget_Year` VARCHAR(4) NULL DEFAULT NULL,
  `BCP_Title` VARCHAR(255) NULL DEFAULT NULL,
  `BCP_Disposition` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`Poam_Details_Id`))
ENGINE = MyISAM
AUTO_INCREMENT = 794
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `Poam_Id` ON `cyberpal_database_nist`.`poam_details` (`Poam_Id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`protect`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`protect` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `OIS_Foundational_Objective` VARCHAR(1000) NULL DEFAULT NULL,
  `Maturity_Level` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_assessments`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_assessments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_name` VARCHAR(255) NOT NULL,
  `status` ENUM('draft', 'completed', 'exported') NULL DEFAULT 'draft',
  `pta_result` ENUM('pia_required', 'pia_not_required') NULL DEFAULT NULL,
  `start_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completion_time` TIMESTAMP NULL DEFAULT NULL,
  `user_id` INT NOT NULL,
  `department_id` INT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `last_section_worked` VARCHAR(10) NULL DEFAULT NULL,
  `last_accessed_at` TIMESTAMP NULL DEFAULT NULL,
  `is_draft_saved` TINYINT(1) NULL DEFAULT '0',
  `progress_percentage` DECIMAL(5,2) NULL DEFAULT '0.00',
  `is_archived` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_user_project` ON `cyberpal_database_nist`.`pta_assessments` (`user_id` ASC, `department_id` ASC, `project_name` ASC) VISIBLE;

CREATE INDEX `idx_status` ON `cyberpal_database_nist`.`pta_assessments` (`status` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_answers`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_answers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `assessment_id` INT NOT NULL,
  `section_code` VARCHAR(10) NOT NULL,
  `question_code` VARCHAR(100) NULL DEFAULT NULL,
  `primary_answer` TEXT NULL DEFAULT NULL,
  `explanation_text` TEXT NULL DEFAULT NULL,
  `user_id` INT NOT NULL,
  `department_id` INT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `pta_answers_ibfk_1`
    FOREIGN KEY (`assessment_id`)
    REFERENCES `cyberpal_database_nist`.`pta_assessments` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 3213
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `unique_assessment_question` ON `cyberpal_database_nist`.`pta_answers` (`assessment_id` ASC, `question_code` ASC) VISIBLE;

CREATE INDEX `idx_assessment_section` ON `cyberpal_database_nist`.`pta_answers` (`assessment_id` ASC, `section_code` ASC) VISIBLE;

CREATE INDEX `idx_question_code` ON `cyberpal_database_nist`.`pta_answers` (`question_code` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_assessments_archive`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_assessments_archive` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `original_assessment_id` INT NOT NULL,
  `project_name` VARCHAR(255) NOT NULL,
  `status` ENUM('completed') NULL DEFAULT 'completed',
  `last_updated` TIMESTAMP NULL DEFAULT NULL,
  `completion_time` TIMESTAMP NULL DEFAULT NULL,
  `user_id` INT NOT NULL,
  `department_id` INT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `archived_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_user_archived` ON `cyberpal_database_nist`.`pta_assessments_archive` (`user_id` ASC, `department_id` ASC) VISIBLE;

CREATE INDEX `idx_original_id` ON `cyberpal_database_nist`.`pta_assessments_archive` (`original_assessment_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_audit_log`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_audit_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `assessment_id` INT NOT NULL,
  `action_type` ENUM('created', 'draft_saved', 'answer_updated', 'answers_saved', 'exported', 'signed', 'deleted') NULL DEFAULT NULL,
  `question_code` VARCHAR(20) NULL DEFAULT NULL,
  `old_value` TEXT NULL DEFAULT NULL,
  `new_value` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `pta_audit_log_ibfk_1`
    FOREIGN KEY (`assessment_id`)
    REFERENCES `cyberpal_database_nist`.`pta_assessments` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 583
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_assessment_action` ON `cyberpal_database_nist`.`pta_audit_log` (`assessment_id` ASC, `action_type` ASC) VISIBLE;

CREATE INDEX `idx_audit_time` ON `cyberpal_database_nist`.`pta_audit_log` (`created_at` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_export_history`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_export_history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `assessment_id` INT NOT NULL,
  `export_type` ENUM('draft', 'final', 'reprint') NULL DEFAULT 'final',
  `export_status` ENUM('success', 'failed') NOT NULL,
  `export_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `file_name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `pta_export_history_ibfk_1`
    FOREIGN KEY (`assessment_id`)
    REFERENCES `cyberpal_database_nist`.`pta_assessments` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 93
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_assessment_exports` ON `cyberpal_database_nist`.`pta_export_history` (`assessment_id` ASC, `export_time` ASC) VISIBLE;

CREATE INDEX `idx_export_status` ON `cyberpal_database_nist`.`pta_export_history` (`export_status` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_sections`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_sections` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `section_code` VARCHAR(20) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `parent_section_id` INT NULL DEFAULT NULL,
  `display_order` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `pta_sections_ibfk_1`
    FOREIGN KEY (`parent_section_id`)
    REFERENCES `cyberpal_database_nist`.`pta_sections` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 12
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `uq_section_code` ON `cyberpal_database_nist`.`pta_sections` (`section_code` ASC) VISIBLE;

CREATE INDEX `parent_section_id` ON `cyberpal_database_nist`.`pta_sections` (`parent_section_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`pta_questions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`pta_questions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `section_code` VARCHAR(20) NOT NULL,
  `question_code` VARCHAR(50) NOT NULL,
  `question_text` TEXT NOT NULL,
  `answer_type` ENUM('yes_no', 'text', 'options', 'signature', 'date') NOT NULL,
  `options_set` VARCHAR(50) NULL DEFAULT NULL,
  `has_explanation` TINYINT(1) NULL DEFAULT '0',
  `template_field` VARCHAR(100) NULL DEFAULT NULL,
  `conditional_skip` VARCHAR(100) NULL DEFAULT NULL,
  `question_group` VARCHAR(50) NULL DEFAULT NULL,
  `is_required` TINYINT(1) NULL DEFAULT '1',
  `display_order` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `pta_questions_ibfk_1`
    FOREIGN KEY (`section_code`)
    REFERENCES `cyberpal_database_nist`.`pta_sections` (`section_code`))
ENGINE = InnoDB
AUTO_INCREMENT = 158
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_section_code_order` ON `cyberpal_database_nist`.`pta_questions` (`section_code` ASC, `display_order` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`recover`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`recover` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `OIS_Foundational_Objective` VARCHAR(1000) NULL DEFAULT NULL,
  `Maturity_Level` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`respond`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`respond` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` VARCHAR(1000) NULL DEFAULT NULL,
  `Subcategory` VARCHAR(1000) NULL DEFAULT NULL,
  `OIS_Foundational_Objective` VARCHAR(1000) NULL DEFAULT NULL,
  `Maturity_Level` VARCHAR(2000) NULL DEFAULT NULL,
  `Information_Source` VARCHAR(1000) NULL DEFAULT NULL,
  `Score` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`users`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `username` VARCHAR(255) NULL DEFAULT NULL,
  `mfa_secret` VARCHAR(64) NULL DEFAULT NULL,
  `mfa_enabled` TINYINT(1) NULL DEFAULT '0',
  `backup_codes` JSON NULL DEFAULT NULL,
  `last_login` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sector` ENUM('Public', 'Private') NOT NULL,
  `department_id` INT NULL DEFAULT NULL,
  `company_id` INT NULL DEFAULT NULL,
  `is_admin` TINYINT(1) NULL DEFAULT '0',
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'User who created this account',
  `last_password_change` DATETIME NULL DEFAULT NULL,
  `is_active` TINYINT(1) NULL DEFAULT '1' COMMENT 'Account active status',
  `locked_until` DATETIME NULL DEFAULT NULL COMMENT 'Account locked until this time',
  `failed_login_attempts` INT NULL DEFAULT '0',
  `role` ENUM('Admin', 'User') NULL DEFAULT 'User',
  `refresh_token` VARCHAR(255) NULL DEFAULT NULL,
  `refresh_token_expires_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 23
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `email` ON `cyberpal_database_nist`.`users` (`email` ASC) VISIBLE;

CREATE INDEX `fk_department` ON `cyberpal_database_nist`.`users` (`department_id` ASC) VISIBLE;

CREATE INDEX `fk_company` ON `cyberpal_database_nist`.`users` (`company_id` ASC) VISIBLE;

CREATE INDEX `created_by` ON `cyberpal_database_nist`.`users` (`created_by` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`targets`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`targets` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `department_id` INT NOT NULL,
  `created_by_user_id` BIGINT UNSIGNED NOT NULL,
  `target_url` VARCHAR(500) NULL DEFAULT NULL,
  `target_ip` VARCHAR(45) NULL DEFAULT NULL,
  `target_name` VARCHAR(255) NULL DEFAULT NULL,
  `target_description` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_target_created_by`
    FOREIGN KEY (`created_by_user_id`)
    REFERENCES `cyberpal_database_nist`.`users` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_target_department`
    FOREIGN KEY (`department_id`)
    REFERENCES `cyberpal_database_nist`.`departments` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `idx_dept_targets` ON `cyberpal_database_nist`.`targets` (`department_id` ASC) VISIBLE;

CREATE INDEX `fk_target_created_by` ON `cyberpal_database_nist`.`targets` (`created_by_user_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`tools`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`tools` (
  `id` BIGINT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `tool_type` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`scans`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`scans` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `department_id` INT NOT NULL,
  `initiated_by_user_id` BIGINT UNSIGNED NOT NULL,
  `tool_id` BIGINT NULL DEFAULT NULL,
  `target_id` BIGINT NULL DEFAULT NULL,
  `status_name` VARCHAR(50) NOT NULL,
  `scan_type` VARCHAR(20) NULL DEFAULT 'light' COMMENT 'light, deep',
  `vpn_scan` TINYINT(1) NULL DEFAULT '0',
  `progress` TINYINT NULL DEFAULT '0',
  `start_time` TIMESTAMP NULL DEFAULT NULL,
  `end_time` TIMESTAMP NULL DEFAULT NULL,
  `duration` INT NULL DEFAULT '0' COMMENT 'in seconds',
  `num_tests` INT NULL DEFAULT '0',
  `num_finished_tests` INT NULL DEFAULT '0',
  `status_message` TEXT NULL DEFAULT NULL,
  `scan_name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'friendly name for the scan',
  `external_scan_id` BIGINT NULL DEFAULT NULL COMMENT 'ID from pentest-tools API',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_scan_department`
    FOREIGN KEY (`department_id`)
    REFERENCES `cyberpal_database_nist`.`departments` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_scan_initiated_by`
    FOREIGN KEY (`initiated_by_user_id`)
    REFERENCES `cyberpal_database_nist`.`users` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_scan_target`
    FOREIGN KEY (`target_id`)
    REFERENCES `cyberpal_database_nist`.`targets` (`id`)
    ON DELETE SET NULL,
  CONSTRAINT `fk_scan_tool`
    FOREIGN KEY (`tool_id`)
    REFERENCES `cyberpal_database_nist`.`tools` (`id`)
    ON DELETE SET NULL)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `idx_dept_scans` ON `cyberpal_database_nist`.`scans` (`department_id` ASC) VISIBLE;

CREATE INDEX `idx_status` ON `cyberpal_database_nist`.`scans` (`status_name` ASC) VISIBLE;

CREATE INDEX `idx_external_scan_id` ON `cyberpal_database_nist`.`scans` (`external_scan_id` ASC) VISIBLE;

CREATE INDEX `idx_dept_status` ON `cyberpal_database_nist`.`scans` (`department_id` ASC, `status_name` ASC) VISIBLE;

CREATE INDEX `fk_scan_initiated_by` ON `cyberpal_database_nist`.`scans` (`initiated_by_user_id` ASC) VISIBLE;

CREATE INDEX `fk_scan_tool` ON `cyberpal_database_nist`.`scans` (`tool_id` ASC) VISIBLE;

CREATE INDEX `fk_scan_target` ON `cyberpal_database_nist`.`scans` (`target_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`scan_findings`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`scan_findings` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `scan_id` BIGINT NOT NULL,
  `severity` VARCHAR(20) NOT NULL COMMENT 'critical, high, medium, low, info',
  `title` VARCHAR(500) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `affected_url` VARCHAR(1000) NULL DEFAULT NULL,
  `solution` TEXT NULL DEFAULT NULL,
  `vuln_references` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_finding_scan`
    FOREIGN KEY (`scan_id`)
    REFERENCES `cyberpal_database_nist`.`scans` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `idx_scan_severity` ON `cyberpal_database_nist`.`scan_findings` (`scan_id` ASC, `severity` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`scan_result_summaries`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`scan_result_summaries` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `scan_id` BIGINT NOT NULL,
  `summary_text` TEXT NULL DEFAULT NULL,
  `critical_count` INT NULL DEFAULT '0',
  `high_count` INT NULL DEFAULT '0',
  `medium_count` INT NULL DEFAULT '0',
  `low_count` INT NULL DEFAULT '0',
  `info_count` INT NULL DEFAULT '0',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_summary_scan`
    FOREIGN KEY (`scan_id`)
    REFERENCES `cyberpal_database_nist`.`scans` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `unique_scan_summary` ON `cyberpal_database_nist`.`scan_result_summaries` (`scan_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`score_history`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`score_history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `component` VARCHAR(100) NOT NULL,
  `category` VARCHAR(1000) NOT NULL,
  `subcategory` VARCHAR(1000) NOT NULL,
  `score` JSON NOT NULL,
  `final_score` DECIMAL(5,2) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = MyISAM
AUTO_INCREMENT = 292
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_sections`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_sections` (
  `section_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`section_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_questions_cat`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_questions_cat` (
  `question_id` INT NOT NULL,
  `section_id` INT NOT NULL,
  `text` TEXT NOT NULL,
  PRIMARY KEY (`question_id`),
  CONSTRAINT `fk_questions_section`
    FOREIGN KEY (`section_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_sections` (`section_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `fk_questions_section` ON `cyberpal_database_nist`.`ssp_questions_cat` (`section_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_short_records`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_short_records` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `dept_id` BIGINT UNSIGNED NOT NULL DEFAULT '0',
  `title` VARCHAR(255) NOT NULL DEFAULT 'Untitled',
  `status` ENUM('in_progress', 'completed') NOT NULL DEFAULT 'in_progress',
  `archived` TINYINT(1) NOT NULL DEFAULT '0',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_user_dept` ON `cyberpal_database_nist`.`ssp_short_records` (`user_id` ASC, `dept_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_short_lists`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_short_lists` (
  `record_id` BIGINT UNSIGNED NOT NULL,
  `list_key` VARCHAR(64) NOT NULL,
  `items` JSON NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`record_id`, `list_key`),
  CONSTRAINT `fk_short_lists_record`
    FOREIGN KEY (`record_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_short_records` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_short_sections`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_short_sections` (
  `record_id` BIGINT UNSIGNED NOT NULL,
  `section_key` VARCHAR(64) NOT NULL,
  `data` JSON NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`record_id`, `section_key`),
  CONSTRAINT `fk_short_sections_record`
    FOREIGN KEY (`record_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_short_records` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_user_answers`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_user_answers` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `dept_id` BIGINT UNSIGNED NOT NULL DEFAULT '0',
  `record_id` BIGINT UNSIGNED NOT NULL,
  `section_id` INT NOT NULL,
  `question_id` INT NOT NULL,
  `status` ENUM('Yes', 'Yes with CCW', 'No', 'N/A', 'Not Tested') NOT NULL,
  PRIMARY KEY (`user_id`, `dept_id`, `record_id`, `section_id`, `question_id`),
  CONSTRAINT `fk_answers_question`
    FOREIGN KEY (`question_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_questions_cat` (`question_id`),
  CONSTRAINT `fk_answers_record`
    FOREIGN KEY (`record_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_short_records` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_answers_section`
    FOREIGN KEY (`section_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_sections` (`section_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `fk_answers_record` ON `cyberpal_database_nist`.`ssp_user_answers` (`record_id` ASC) VISIBLE;

CREATE INDEX `fk_answers_section` ON `cyberpal_database_nist`.`ssp_user_answers` (`section_id` ASC) VISIBLE;

CREATE INDEX `fk_answers_question` ON `cyberpal_database_nist`.`ssp_user_answers` (`question_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`ssp_user_submissions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`ssp_user_submissions` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `dept_id` BIGINT UNSIGNED NOT NULL DEFAULT '0',
  `record_id` BIGINT UNSIGNED NOT NULL,
  `score` DECIMAL(5,2) NULL DEFAULT '0.00',
  `status` ENUM('draft', 'submitted') NULL DEFAULT 'submitted',
  `submitted_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_submissions_record`
    FOREIGN KEY (`record_id`)
    REFERENCES `cyberpal_database_nist`.`ssp_short_records` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE UNIQUE INDEX `uniq_submission` ON `cyberpal_database_nist`.`ssp_user_submissions` (`user_id` ASC, `dept_id` ASC, `record_id` ASC) VISIBLE;

CREATE INDEX `fk_submissions_record` ON `cyberpal_database_nist`.`ssp_user_submissions` (`record_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`trp_submissions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`trp_submissions` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `submission_date` DATE NULL DEFAULT NULL,
  `entity_name` VARCHAR(255) NOT NULL,
  `org_code` VARCHAR(50) NOT NULL,
  `compliance_status` ENUM('full_compliance', 'not_compliance') NOT NULL,
  `compliance_target_date` DATE NULL DEFAULT NULL,
  `trp_follows_simm` TINYINT(1) NULL DEFAULT '0',
  `trp_does_not_follow` TINYINT(1) NULL DEFAULT '0',
  `has_additional_explanation` TINYINT(1) NULL DEFAULT '0',
  `contact_name` VARCHAR(255) NULL DEFAULT NULL,
  `contact_phone` VARCHAR(50) NULL DEFAULT NULL,
  `contact_email` VARCHAR(255) NULL DEFAULT NULL,
  `entity_head_name` VARCHAR(255) NULL DEFAULT NULL,
  `signature_date` DATE NULL DEFAULT NULL,
  `mission_critical_systems` INT NULL DEFAULT '0',
  `state_critical_systems` INT NULL DEFAULT '0',
  `other_systems` INT NULL DEFAULT '0',
  `web_applications` INT NULL DEFAULT '0',
  `critical_infrastructure` INT NULL DEFAULT '0',
  `total_systems` INT NULL DEFAULT '0',
  `status` VARCHAR(20) NULL DEFAULT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `trp_submissions_ibfk_1`
    FOREIGN KEY (`created_by`)
    REFERENCES `cyberpal_database_nist`.`users` (`id`)
    ON DELETE RESTRICT)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_entity_name` ON `cyberpal_database_nist`.`trp_submissions` (`entity_name` ASC) VISIBLE;

CREATE INDEX `idx_org_code` ON `cyberpal_database_nist`.`trp_submissions` (`org_code` ASC) VISIBLE;

CREATE INDEX `idx_status` ON `cyberpal_database_nist`.`trp_submissions` (`status` ASC) VISIBLE;

CREATE INDEX `idx_created_by` ON `cyberpal_database_nist`.`trp_submissions` (`created_by` ASC) VISIBLE;

CREATE INDEX `idx_submission_date` ON `cyberpal_database_nist`.`trp_submissions` (`submission_date` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`trp_cross_references`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`trp_cross_references` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `trp_submission_id` BIGINT NOT NULL,
  `page_1_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_1_1` TEXT NULL DEFAULT NULL,
  `page_1_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_1_2` TEXT NULL DEFAULT NULL,
  `page_1_3` VARCHAR(50) NULL DEFAULT NULL,
  `comment_1_3` TEXT NULL DEFAULT NULL,
  `page_1_4` VARCHAR(50) NULL DEFAULT NULL,
  `comment_1_4` TEXT NULL DEFAULT NULL,
  `page_1_5` VARCHAR(50) NULL DEFAULT NULL,
  `comment_1_5` TEXT NULL DEFAULT NULL,
  `page_2_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_2_1` TEXT NULL DEFAULT NULL,
  `page_2_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_2_2` TEXT NULL DEFAULT NULL,
  `page_3_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_3_1` TEXT NULL DEFAULT NULL,
  `page_3_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_3_2` TEXT NULL DEFAULT NULL,
  `page_4_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_4_1` TEXT NULL DEFAULT NULL,
  `page_4_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_4_2` TEXT NULL DEFAULT NULL,
  `page_4_3` VARCHAR(50) NULL DEFAULT NULL,
  `comment_4_3` TEXT NULL DEFAULT NULL,
  `page_4_4` VARCHAR(50) NULL DEFAULT NULL,
  `comment_4_4` TEXT NULL DEFAULT NULL,
  `page_5_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_5_1` TEXT NULL DEFAULT NULL,
  `page_5_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_5_2` TEXT NULL DEFAULT NULL,
  `page_6_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_6_1` TEXT NULL DEFAULT NULL,
  `page_6_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_6_2` TEXT NULL DEFAULT NULL,
  `page_6_3` VARCHAR(50) NULL DEFAULT NULL,
  `comment_6_3` TEXT NULL DEFAULT NULL,
  `page_6_4` VARCHAR(50) NULL DEFAULT NULL,
  `comment_6_4` TEXT NULL DEFAULT NULL,
  `page_7_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_7_1` TEXT NULL DEFAULT NULL,
  `page_7_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_7_2` TEXT NULL DEFAULT NULL,
  `page_7_3` VARCHAR(50) NULL DEFAULT NULL,
  `comment_7_3` TEXT NULL DEFAULT NULL,
  `page_8_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_8_1` TEXT NULL DEFAULT NULL,
  `page_8_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_8_2` TEXT NULL DEFAULT NULL,
  `page_9_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_9_1` TEXT NULL DEFAULT NULL,
  `page_10_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_10_1` TEXT NULL DEFAULT NULL,
  `page_10_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_10_2` TEXT NULL DEFAULT NULL,
  `page_11_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_11_1` TEXT NULL DEFAULT NULL,
  `page_supp_1_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_supp_1_1` TEXT NULL DEFAULT NULL,
  `page_supp_1_2` VARCHAR(50) NULL DEFAULT NULL,
  `comment_supp_1_2` TEXT NULL DEFAULT NULL,
  `page_supp_2_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_supp_2_1` TEXT NULL DEFAULT NULL,
  `page_supp_3_1` VARCHAR(50) NULL DEFAULT NULL,
  `comment_supp_3_1` TEXT NULL DEFAULT NULL,
  `page_appendices` VARCHAR(50) NULL DEFAULT NULL,
  `comment_appendices` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `trp_cross_references_ibfk_1`
    FOREIGN KEY (`trp_submission_id`)
    REFERENCES `cyberpal_database_nist`.`trp_submissions` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_trp_submission` ON `cyberpal_database_nist`.`trp_cross_references` (`trp_submission_id` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `cyberpal_database_nist`.`trp_files`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`trp_files` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `trp_submission_id` BIGINT NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(100) NOT NULL,
  `uploaded_by` BIGINT UNSIGNED NOT NULL,
  `uploaded_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `trp_files_ibfk_1`
    FOREIGN KEY (`trp_submission_id`)
    REFERENCES `cyberpal_database_nist`.`trp_submissions` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `trp_files_ibfk_2`
    FOREIGN KEY (`uploaded_by`)
    REFERENCES `cyberpal_database_nist`.`users` (`id`)
    ON DELETE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idx_trp_submission` ON `cyberpal_database_nist`.`trp_files` (`trp_submission_id` ASC) VISIBLE;

CREATE INDEX `idx_uploaded_by` ON `cyberpal_database_nist`.`trp_files` (`uploaded_by` ASC) VISIBLE;

USE `cyberpal_database_nist` ;

-- -----------------------------------------------------
-- Placeholder table for view `cyberpal_database_nist`.`active_scans`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`active_scans` (`id` INT, `department_id` INT, `initiated_by_user_id` INT, `tool_id` INT, `target_id` INT, `status_name` INT, `scan_type` INT, `progress` INT, `start_time` INT, `scan_name` INT, `tool_name` INT, `target_url` INT, `department_name` INT, `initiated_by_username` INT);

-- -----------------------------------------------------
-- Placeholder table for view `cyberpal_database_nist`.`scan_summary_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`scan_summary_view` (`id` INT, `department_id` INT, `initiated_by_user_id` INT, `status_name` INT, `scan_type` INT, `progress` INT, `start_time` INT, `end_time` INT, `duration` INT, `scan_name` INT, `critical_count` INT, `high_count` INT, `medium_count` INT, `low_count` INT, `info_count` INT, `tool_name` INT, `target_url` INT, `department_name` INT, `initiated_by_username` INT);

-- -----------------------------------------------------
-- Placeholder table for view `cyberpal_database_nist`.`trp_submissions_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cyberpal_database_nist`.`trp_submissions_list` (`id` INT, `entity_name` INT, `org_code` INT, `submission_date` INT, `compliance_status` INT, `status` INT, `total_systems` INT, `entity_head_name` INT, `created_at` INT, `updated_at` INT, `created_by_username` INT, `created_by_email` INT);

-- -----------------------------------------------------
-- procedure UpdateQuarterlyCounts
-- -----------------------------------------------------

USE `cyberpal_database_nist`;
DROP procedure IF EXISTS `cyberpal_database_nist`.`UpdateQuarterlyCounts`;

DELIMITER $$
USE `cyberpal_database_nist`$$
CREATE DEFINER=`cyberpal`@`localhost` PROCEDURE `UpdateQuarterlyCounts`(IN poamId INT)
BEGIN
    -- Step 1: Determine the current quarter
    DECLARE current_month INT;
    DECLARE current_year INT;
    DECLARE quarter_start DATE;
    DECLARE quarter_end DATE;
    DECLARE new_added INT;
    DECLARE completed INT;

    -- Get current month and year
    SET current_month = MONTH(CURRENT_DATE());
    SET current_year = YEAR(CURRENT_DATE());

    -- Set the quarter start based on the current month
    SET quarter_start = CASE
        WHEN current_month IN (11, 12, 1) THEN CONCAT(current_year - 1, '-11-01')  -- Set to previous year's November
        WHEN current_month IN (2, 3, 4) THEN CONCAT(current_year, '-02-01')
        WHEN current_month IN (5, 6, 7) THEN CONCAT(current_year, '-05-01')
        WHEN current_month IN (8, 9, 10) THEN CONCAT(current_year, '-08-01')
    END;

    -- Calculate the quarter end
    SET quarter_end = DATE_FORMAT(DATE_ADD(quarter_start, INTERVAL 3 MONTH) - INTERVAL 1 DAY, '%Y-%m-%d');

    -- Step 2: Count records added during the current quarter for the specific Poam_Id
    SELECT COUNT(*) INTO new_added
    FROM poam_details
    WHERE Poam_Id = poamId 
    AND First_Identified >= quarter_start 
    AND First_Identified <= quarter_end;

    -- Step 3: Count records completed during the current quarter for the specific Poam_Id
    SELECT COUNT(*) INTO completed
    FROM poam_details
    WHERE Poam_Id = poamId
    AND Actual_Completion_Date >= quarter_start 
    AND Actual_Completion_Date <= quarter_end;

    -- Step 4: Update the poam table with the calculated counts
    UPDATE poam
    SET New_Added_During_Quarter = new_added,
        Completed_During_Quarter = completed,
        Updated_Date = NOW()
    WHERE Poam_Id = poamId;  -- Use the provided Poam_Id

END$$

DELIMITER ;

-- -----------------------------------------------------
-- View `cyberpal_database_nist`.`active_scans`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `cyberpal_database_nist`.`active_scans` ;
USE `cyberpal_database_nist`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`cyberpal`@`localhost` SQL SECURITY DEFINER VIEW `cyberpal_database_nist`.`active_scans` AS select `s`.`id` AS `id`,`s`.`department_id` AS `department_id`,`s`.`initiated_by_user_id` AS `initiated_by_user_id`,`s`.`tool_id` AS `tool_id`,`s`.`target_id` AS `target_id`,`s`.`status_name` AS `status_name`,`s`.`scan_type` AS `scan_type`,`s`.`progress` AS `progress`,`s`.`start_time` AS `start_time`,`s`.`scan_name` AS `scan_name`,`t`.`name` AS `tool_name`,`tg`.`target_url` AS `target_url`,`d`.`name` AS `department_name`,`u`.`username` AS `initiated_by_username` from ((((`cyberpal_database_nist`.`scans` `s` left join `cyberpal_database_nist`.`tools` `t` on((`s`.`tool_id` = `t`.`id`))) left join `cyberpal_database_nist`.`targets` `tg` on((`s`.`target_id` = `tg`.`id`))) left join `cyberpal_database_nist`.`departments` `d` on((`s`.`department_id` = `d`.`id`))) left join `cyberpal_database_nist`.`users` `u` on((`s`.`initiated_by_user_id` = `u`.`id`))) where (`s`.`status_name` in ('running','queued','starting'));

-- -----------------------------------------------------
-- View `cyberpal_database_nist`.`scan_summary_view`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `cyberpal_database_nist`.`scan_summary_view` ;
USE `cyberpal_database_nist`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`cyberpal`@`localhost` SQL SECURITY DEFINER VIEW `cyberpal_database_nist`.`scan_summary_view` AS select `s`.`id` AS `id`,`s`.`department_id` AS `department_id`,`s`.`initiated_by_user_id` AS `initiated_by_user_id`,`s`.`status_name` AS `status_name`,`s`.`scan_type` AS `scan_type`,`s`.`progress` AS `progress`,`s`.`start_time` AS `start_time`,`s`.`end_time` AS `end_time`,`s`.`duration` AS `duration`,`s`.`scan_name` AS `scan_name`,`srs`.`critical_count` AS `critical_count`,`srs`.`high_count` AS `high_count`,`srs`.`medium_count` AS `medium_count`,`srs`.`low_count` AS `low_count`,`srs`.`info_count` AS `info_count`,`t`.`name` AS `tool_name`,`tg`.`target_url` AS `target_url`,`d`.`name` AS `department_name`,`u`.`username` AS `initiated_by_username` from (((((`cyberpal_database_nist`.`scans` `s` left join `cyberpal_database_nist`.`scan_result_summaries` `srs` on((`s`.`id` = `srs`.`scan_id`))) left join `cyberpal_database_nist`.`tools` `t` on((`s`.`tool_id` = `t`.`id`))) left join `cyberpal_database_nist`.`targets` `tg` on((`s`.`target_id` = `tg`.`id`))) left join `cyberpal_database_nist`.`departments` `d` on((`s`.`department_id` = `d`.`id`))) left join `cyberpal_database_nist`.`users` `u` on((`s`.`initiated_by_user_id` = `u`.`id`)));

-- -----------------------------------------------------
-- View `cyberpal_database_nist`.`trp_submissions_list`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `cyberpal_database_nist`.`trp_submissions_list` ;
USE `cyberpal_database_nist`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`cyberpal`@`localhost` SQL SECURITY DEFINER VIEW `cyberpal_database_nist`.`trp_submissions_list` AS select `ts`.`id` AS `id`,`ts`.`entity_name` AS `entity_name`,`ts`.`org_code` AS `org_code`,`ts`.`submission_date` AS `submission_date`,`ts`.`compliance_status` AS `compliance_status`,`ts`.`status` AS `status`,`ts`.`total_systems` AS `total_systems`,`ts`.`entity_head_name` AS `entity_head_name`,`ts`.`created_at` AS `created_at`,`ts`.`updated_at` AS `updated_at`,`u`.`username` AS `created_by_username`,`u`.`email` AS `created_by_email` from (`cyberpal_database_nist`.`trp_submissions` `ts` left join `cyberpal_database_nist`.`users` `u` on((`ts`.`created_by` = `u`.`id`))) order by `ts`.`created_at` desc;
USE `cyberpal_database_nist`;

DELIMITER $$

USE `cyberpal_database_nist`$$
DROP TRIGGER IF EXISTS `cyberpal_database_nist`.`trg_poam_delete` $$
USE `cyberpal_database_nist`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `cyberpal_database_nist`.`trg_poam_delete`
AFTER DELETE ON `cyberpal_database_nist`.`poam_details`
FOR EACH ROW
BEGIN
    INSERT INTO poam_audit (event_type, poam_details_id, user_id, old_data, notes)
    VALUES ('DELETE', OLD.Poam_Details_Id, @current_user_id, 
            JSON_OBJECT(
                'Poam_Details_Id', OLD.Poam_Details_Id,
                'Poam_Id', OLD.Poam_Id,
                'NIST_Families', OLD.NIST_Families,
                'SAM_And_SIMM_Policies', OLD.SAM_And_SIMM_Policies,
                'Weakness_Or_Area_Noncompliance', OLD.Weakness_Or_Area_Noncompliance,
                'Asset_Risk_Description', OLD.Asset_Risk_Description,
                'Poam_Source', OLD.Poam_Source,
                'Risk_Response', OLD.Risk_Response,
                'Compensating_Controls', OLD.Compensating_Controls,
                'Plan_of_Action', OLD.Plan_of_Action,
                'Shared_Device', OLD.Shared_Device,
                'Threat_Likelihood', OLD.Threat_Likelihood,
                'Event_Impact', OLD.Event_Impact,
                'Risk_Rating', OLD.Risk_Rating,
                'First_Identified', OLD.First_Identified,
                'Start_Date_Or_Projected_Start', OLD.Start_Date_Or_Projected_Start,
                'Projected_Completion_Date', OLD.Projected_Completion_Date,
                'Actual_Completion_Date', OLD.Actual_Completion_Date,
                'Projected_Days_Open', OLD.Projected_Days_Open,
                'Actual_Days_Open', OLD.Actual_Days_Open,
                'Assigned_to', OLD.Assigned_to,
                'Poam_Status', OLD.Poam_Status,
                'Central_Barrier_Or_Constraint', OLD.Central_Barrier_Or_Constraint,
                'Projected_Remediation_Cost', OLD.Projected_Remediation_Cost,
                'Status_of_BCP', OLD.Status_of_BCP,
                'BCP_Amount', OLD.BCP_Amount,
                'BCP_Governers_Budget_Year', OLD.BCP_Governers_Budget_Year,
                'BCP_Title', OLD.BCP_Title,
                'BCP_Disposition', OLD.BCP_Disposition
            ), 
            'Record deleted'
    );
END$$


USE `cyberpal_database_nist`$$
DROP TRIGGER IF EXISTS `cyberpal_database_nist`.`trg_poam_insert` $$
USE `cyberpal_database_nist`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `cyberpal_database_nist`.`trg_poam_insert`
AFTER INSERT ON `cyberpal_database_nist`.`poam_details`
FOR EACH ROW
BEGIN
    INSERT INTO poam_audit (event_type, poam_details_id, user_id, new_data, notes)
    VALUES ('INSERT', NEW.Poam_Details_Id, @current_user_id, 
            JSON_OBJECT(
                'Poam_Details_Id', NEW.Poam_Details_Id,
                'Poam_Id', NEW.Poam_Id,
                'NIST_Families', NEW.NIST_Families,
                'SAM_And_SIMM_Policies', NEW.SAM_And_SIMM_Policies,
                'Weakness_Or_Area_Noncompliance', NEW.Weakness_Or_Area_Noncompliance,
                'Asset_Risk_Description', NEW.Asset_Risk_Description,
                'Poam_Source', NEW.Poam_Source,
                'Risk_Response', NEW.Risk_Response,
                'Compensating_Controls', NEW.Compensating_Controls,
                'Plan_of_Action', NEW.Plan_of_Action,
                'Shared_Device', NEW.Shared_Device,
                'Threat_Likelihood', NEW.Threat_Likelihood,
                'Event_Impact', NEW.Event_Impact,
                'Risk_Rating', NEW.Risk_Rating,
                'First_Identified', NEW.First_Identified,
                'Start_Date_Or_Projected_Start', NEW.Start_Date_Or_Projected_Start,
                'Projected_Completion_Date', NEW.Projected_Completion_Date,
                'Actual_Completion_Date', NEW.Actual_Completion_Date,
                'Projected_Days_Open', NEW.Projected_Days_Open,
                'Actual_Days_Open', NEW.Actual_Days_Open,
                'Assigned_to', NEW.Assigned_to,
                'Poam_Status', NEW.Poam_Status,
                'Central_Barrier_Or_Constraint', NEW.Central_Barrier_Or_Constraint,
                'Projected_Remediation_Cost', NEW.Projected_Remediation_Cost,
                'Status_of_BCP', NEW.Status_of_BCP,
                'BCP_Amount', NEW.BCP_Amount,
                'BCP_Governers_Budget_Year', NEW.BCP_Governers_Budget_Year,
                'BCP_Title', NEW.BCP_Title,
                'BCP_Disposition', NEW.BCP_Disposition
            ), 
            'New record inserted'
    );
END$$


USE `cyberpal_database_nist`$$
DROP TRIGGER IF EXISTS `cyberpal_database_nist`.`trg_poam_update` $$
USE `cyberpal_database_nist`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `cyberpal_database_nist`.`trg_poam_update`
AFTER UPDATE ON `cyberpal_database_nist`.`poam_details`
FOR EACH ROW
BEGIN
    -- Check if any of the relevant columns have changed
    IF (OLD.Poam_Id <> NEW.Poam_Id OR
        OLD.NIST_Families <> NEW.NIST_Families OR
        OLD.SAM_And_SIMM_Policies <> NEW.SAM_And_SIMM_Policies OR
        OLD.Weakness_Or_Area_Noncompliance <> NEW.Weakness_Or_Area_Noncompliance OR
        OLD.Asset_Risk_Description <> NEW.Asset_Risk_Description OR
        OLD.Poam_Source <> NEW.Poam_Source OR
        OLD.Risk_Response <> NEW.Risk_Response OR
        OLD.Compensating_Controls <> NEW.Compensating_Controls OR
        OLD.Plan_of_Action <> NEW.Plan_of_Action OR
        OLD.Shared_Device <> NEW.Shared_Device OR
        OLD.Threat_Likelihood <> NEW.Threat_Likelihood OR
        OLD.Event_Impact <> NEW.Event_Impact OR
        OLD.Risk_Rating <> NEW.Risk_Rating OR
        OLD.First_Identified <> NEW.First_Identified OR
        OLD.Start_Date_Or_Projected_Start <> NEW.Start_Date_Or_Projected_Start OR
        OLD.Projected_Completion_Date <> NEW.Projected_Completion_Date OR
        OLD.Actual_Completion_Date <> NEW.Actual_Completion_Date OR
        OLD.Projected_Days_Open <> NEW.Projected_Days_Open OR
        OLD.Actual_Days_Open <> NEW.Actual_Days_Open OR
        OLD.Assigned_to <> NEW.Assigned_to OR
        OLD.Poam_Status <> NEW.Poam_Status OR
        OLD.Central_Barrier_Or_Constraint <> NEW.Central_Barrier_Or_Constraint OR
        OLD.Projected_Remediation_Cost <> NEW.Projected_Remediation_Cost OR
        OLD.Status_of_BCP <> NEW.Status_of_BCP OR
        OLD.BCP_Amount <> NEW.BCP_Amount OR
        OLD.BCP_Governers_Budget_Year <> NEW.BCP_Governers_Budget_Year OR
        OLD.BCP_Title <> NEW.BCP_Title OR
        OLD.BCP_Disposition <> NEW.BCP_Disposition) THEN

        -- Insert into audit table only if there are changes
        INSERT INTO poam_audit (event_type, poam_details_id, user_id, old_data, new_data, notes)
        VALUES ('UPDATE', NEW.Poam_Details_Id, @current_user_id, 
                JSON_OBJECT(
                    'Poam_Details_Id', OLD.Poam_Details_Id,
                    'Poam_Id', OLD.Poam_Id,
                    'NIST_Families', OLD.NIST_Families,
                    'SAM_And_SIMM_Policies', OLD.SAM_And_SIMM_Policies,
                    'Weakness_Or_Area_Noncompliance', OLD.Weakness_Or_Area_Noncompliance,
                    'Asset_Risk_Description', OLD.Asset_Risk_Description,
                    'Poam_Source', OLD.Poam_Source,
                    'Risk_Response', OLD.Risk_Response,
                    'Compensating_Controls', OLD.Compensating_Controls,
                    'Plan_of_Action', OLD.Plan_of_Action,
                    'Shared_Device', OLD.Shared_Device,
                    'Threat_Likelihood', OLD.Threat_Likelihood,
                    'Event_Impact', OLD.Event_Impact,
                    'Risk_Rating', OLD.Risk_Rating,
                    'First_Identified', OLD.First_Identified,
                    'Start_Date_Or_Projected_Start', OLD.Start_Date_Or_Projected_Start,
                    'Projected_Completion_Date', OLD.Projected_Completion_Date,
                    'Actual_Completion_Date', OLD.Actual_Completion_Date,
                    'Projected_Days_Open', OLD.Projected_Days_Open,
                    'Actual_Days_Open', OLD.Actual_Days_Open,
                    'Assigned_to', OLD.Assigned_to,
                    'Poam_Status', OLD.Poam_Status,
                    'Central_Barrier_Or_Constraint', OLD.Central_Barrier_Or_Constraint,
                    'Projected_Remediation_Cost', OLD.Projected_Remediation_Cost,
                    'Status_of_BCP', OLD.Status_of_BCP,
                    'BCP_Amount', OLD.BCP_Amount,
                    'BCP_Governers_Budget_Year', OLD.BCP_Governers_Budget_Year,
                    'BCP_Title', OLD.BCP_Title,
                    'BCP_Disposition', OLD.BCP_Disposition
                ),
                JSON_OBJECT(
                    'Poam_Details_Id', NEW.Poam_Details_Id,
                    'Poam_Id', NEW.Poam_Id,
                    'NIST_Families', NEW.NIST_Families,
                    'SAM_And_SIMM_Policies', NEW.SAM_And_SIMM_Policies,
                    'Weakness_Or_Area_Noncompliance', NEW.Weakness_Or_Area_Noncompliance,
                    'Asset_Risk_Description', NEW.Asset_Risk_Description,
                    'Poam_Source', NEW.Poam_Source,
                    'Risk_Response', NEW.Risk_Response,
                    'Compensating_Controls', NEW.Compensating_Controls,
                    'Plan_of_Action', NEW.Plan_of_Action,
                    'Shared_Device', NEW.Shared_Device,
                    'Threat_Likelihood', NEW.Threat_Likelihood,
                    'Event_Impact', NEW.Event_Impact,
                    'Risk_Rating', NEW.Risk_Rating,
                    'First_Identified', NEW.First_Identified,
                    'Start_Date_Or_Projected_Start', NEW.Start_Date_Or_Projected_Start,
                    'Projected_Completion_Date', NEW.Projected_Completion_Date,
                    'Actual_Completion_Date', NEW.Actual_Completion_Date,
                    'Projected_Days_Open', NEW.Projected_Days_Open,
                    'Actual_Days_Open', NEW.Actual_Days_Open,
                    'Assigned_to', NEW.Assigned_to,
                    'Poam_Status', NEW.Poam_Status,
                    'Central_Barrier_Or_Constraint', NEW.Central_Barrier_Or_Constraint,
                    'Projected_Remediation_Cost', NEW.Projected_Remediation_Cost,
                    'Status_of_BCP', NEW.Status_of_BCP,
                    'BCP_Amount', NEW.BCP_Amount,
                    'BCP_Governers_Budget_Year', NEW.BCP_Governers_Budget_Year,
                    'BCP_Title', NEW.BCP_Title,
                    'BCP_Disposition', NEW.BCP_Disposition
                ), 
                'Record updated'
        );
    END IF;
END$$


DELIMITER ;

