

CREATE TABLE Components (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) ,
    description TEXT
);

CREATE TABLE Categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    component_id BIGINT UNSIGNED ,
    name VARCHAR(255) ,
    description TEXT,
    FOREIGN KEY (component_id) REFERENCES Components(id)
);

CREATE TABLE Subcategories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    component_id BIGINT UNSIGNED ,
    category_id BIGINT UNSIGNED ,
    name VARCHAR(255) ,
    description TEXT,
    FOREIGN KEY (component_id) REFERENCES Components(id),
    FOREIGN KEY (category_id) REFERENCES Categories(id)
);


CREATE TABLE Scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    question_id BIGINT UNSIGNED,
    subcategory VARCHAR(255),
    component VARCHAR(255),
    category VARCHAR(255),
    score INT,
    comments TEXT,
    target_state INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_submitted BOOLEAN DEFAULT FALSE,
    submitted_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

CREATE TABLE QuestionSets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_type VARCHAR(255) ,
    user_role VARCHAR(255),
    name VARCHAR(255) 
);

CREATE TABLE Questions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    set_id BIGINT UNSIGNED ,
    user_type VARCHAR(255) ,
    question TEXT,
    subcategory_id BIGINT UNSIGNED ,
    category_id BIGINT UNSIGNED ,
    component_id BIGINT UNSIGNED ,
    FOREIGN KEY (set_id) REFERENCES QuestionSets(id),
    FOREIGN KEY (subcategory_id) REFERENCES Subcategories(id),
    FOREIGN KEY (category_id) REFERENCES Categories(id),
    FOREIGN KEY (component_id) REFERENCES Components(id)
);


CREATE TABLE Departments (
    dept_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) 
);


CREATE TABLE Submissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

CREATE TABLE assessment_score_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT(20) UNSIGNED NOT NULL,
    assessment_score DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);


CREATE TABLE `PCI_requirements` (
    `requirement_id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `description` text,
    `compliance_level` enum('high', 'medium', 'low') DEFAULT 'medium',
    `objective_id` int DEFAULT NULL,
    PRIMARY KEY (`requirement_id`),
    KEY `objective_id` (`objective_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 13 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `PCI_compliance_statuses` (
    `requirement_id` int NOT NULL,
    `compliance_status` varchar(255) NOT NULL,
    PRIMARY KEY (`requirement_id`),
    CONSTRAINT `compliance_statuses_fk_requirements` FOREIGN KEY (`requirement_id`) 
        REFERENCES `PCI_requirements` (`requirement_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `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) DEFAULT NULL,
    PRIMARY KEY (`question_id`),
    KEY `requirement_id` (`requirement_id`),
    CONSTRAINT `questions_fk_requirements` FOREIGN KEY (`requirement_id`) 
        REFERENCES `PCI_requirements` (`requirement_id`) ON DELETE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 61 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

ALTER TABLE Submissions ADD COLUMN final_score DECIMAL(5,2) NOT NULL;

ALTER TABLE Scores ADD COLUMN submission_id BIGINT UNSIGNED;


-- self-assessment SIMM schema
-- Update category names to match correct format
UPDATE Categories 
SET name = 'Response Communications' 
WHERE name = 'Respond Communications';

UPDATE Categories 
SET name = 'Response Analysis' 
WHERE name = 'Analysis';

UPDATE Categories 
SET name = 'Response Mitigation' 
WHERE name = 'Mitigation';

UPDATE Categories 
SET name = 'Response Improvements' 
WHERE name = 'Respond Improvements';
-- create a new user

-- Drop the existing table if needed


CREATE TABLE PII_Scans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(10) NOT NULL,  -- To store extensions like .txt, .csv, etc.
    redacted_content TEXT NOT NULL,
    scan_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES Users(id)
);



CREATE TABLE `Owasp_Categories` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `Owasp_Category_Scores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int NOT NULL,
  `possibility_score` float DEFAULT NULL,
  `impact_score` float DEFAULT NULL,
  `risk_score` float DEFAULT NULL,
  `status` enum('Applicable','Not Applicable') DEFAULT 'Applicable',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `Owasp_Questions` (
  `id` int NOT NULL DEFAULT '0',
  `category_id` int DEFAULT NULL,
  `question_text` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE PII_Scans 
MODIFY COLUMN user_id BIGINT UNSIGNED NULL;

select * from `Owasp_Category_Scores`