ALTER TABLE poam
ADD COLUMN is_archived BOOLEAN NOT NULL DEFAULT FALSE;


-- 1) Parent: one row per SSP instance
CREATE TABLE IF NOT EXISTS 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,
  PRIMARY KEY (id),
  KEY idx_user_dept (user_id, dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) Sections (JSON) per record
CREATE TABLE IF NOT EXISTS 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 ssp_short_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) Repeatable lists (JSON) per record
CREATE TABLE IF NOT EXISTS 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 ssp_short_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) Catalog (static)
CREATE TABLE IF NOT EXISTS ssp_sections (
  section_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssp_questions_cat (
  question_id INT PRIMARY KEY,
  section_id  INT NOT NULL,
  text        TEXT NOT NULL,
  CONSTRAINT fk_questions_section
    FOREIGN KEY (section_id) REFERENCES ssp_sections(section_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) Per-record submission header
CREATE TABLE IF NOT EXISTS 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) DEFAULT 0,
  status    ENUM('draft','submitted') DEFAULT 'submitted',
  submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_submission (user_id, dept_id, record_id),
  CONSTRAINT fk_submissions_record
    FOREIGN KEY (record_id) REFERENCES ssp_short_records(id) ON DELETE CASCADE
  -- Optional FK (only if users.id exists and is BIGINT UNSIGNED):
  -- ,CONSTRAINT fk_submissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6) Per-record answers
CREATE TABLE IF NOT EXISTS 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_record
    FOREIGN KEY (record_id) REFERENCES ssp_short_records(id) ON DELETE CASCADE,
  CONSTRAINT fk_answers_section
    FOREIGN KEY (section_id) REFERENCES ssp_sections(section_id),
  CONSTRAINT fk_answers_question
    FOREIGN KEY (question_id) REFERENCES ssp_questions_cat(question_id)
  -- Optional FK (only if users.id exists and is BIGINT UNSIGNED):
  -- ,CONSTRAINT fk_answers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


UPDATE ssp_short_sections
SET data = JSON_SET(
            JSON_REMOVE(data, '$.availabilty'),
            '$.availability',
            COALESCE(JSON_EXTRACT(data, '$.availability'), JSON_EXTRACT(data, '$.availabilty'))
          )
WHERE section_key='cia' AND JSON_CONTAINS_PATH(data, 'one', '$.availabilty');




ALTER TABLE users ADD COLUMN refresh_token VARCHAR(255);
ALTER TABLE users ADD COLUMN refresh_token_expires_at DATETIME;



CREATE TABLE trp_submissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    submission_date DATE NOT 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, -- only for not_compliance
    trp_follows_simm BOOLEAN DEFAULT FALSE,
    trp_does_not_follow BOOLEAN DEFAULT FALSE,
    has_additional_explanation BOOLEAN DEFAULT FALSE,
    contact_name VARCHAR(255) NULL,
    contact_phone VARCHAR(50) NULL,
    contact_email VARCHAR(255) NULL,
    entity_head_name VARCHAR(255) NOT NULL,
    signature_date DATE NOT NULL,
    mission_critical_systems INT DEFAULT 0,
    state_critical_systems INT DEFAULT 0,
    other_systems INT DEFAULT 0,
    web_applications INT DEFAULT 0,
    critical_infrastructure INT DEFAULT 0,
    total_systems INT DEFAULT 0,
    status ENUM('draft', 'submitted') DEFAULT 'draft',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_entity_name (entity_name),
    INDEX idx_org_code (org_code),
    INDEX idx_status (status),
    INDEX idx_created_by (created_by),
    INDEX idx_submission_date (submission_date)
);

-- Cross Reference Mapping table - stores all the page/comment mapping
CREATE TABLE trp_cross_references (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    trp_submission_id BIGINT NOT NULL,
    page_1_1 VARCHAR(50) NULL,
    comment_1_1 TEXT NULL,
    page_1_2 VARCHAR(50) NULL,
    comment_1_2 TEXT NULL,
    page_1_3 VARCHAR(50) NULL,
    comment_1_3 TEXT NULL,
    page_1_4 VARCHAR(50) NULL,
    comment_1_4 TEXT NULL,
    page_1_5 VARCHAR(50) NULL,
    comment_1_5 TEXT NULL,
    page_2_1 VARCHAR(50) NULL,
    comment_2_1 TEXT NULL,
    page_2_2 VARCHAR(50) NULL,
    comment_2_2 TEXT NULL,
    page_3_1 VARCHAR(50) NULL,
    comment_3_1 TEXT NULL,
    page_3_2 VARCHAR(50) NULL,
    comment_3_2 TEXT NULL,
    page_4_1 VARCHAR(50) NULL,
    comment_4_1 TEXT NULL,
    page_4_2 VARCHAR(50) NULL,
    comment_4_2 TEXT NULL,
    page_4_3 VARCHAR(50) NULL,
    comment_4_3 TEXT NULL,
    page_4_4 VARCHAR(50) NULL,
    comment_4_4 TEXT NULL,
    page_5_1 VARCHAR(50) NULL,
    comment_5_1 TEXT NULL,
    page_5_2 VARCHAR(50) NULL,
    comment_5_2 TEXT NULL,
    page_6_1 VARCHAR(50) NULL,
    comment_6_1 TEXT NULL,
    page_6_2 VARCHAR(50) NULL,
    comment_6_2 TEXT NULL,
    page_6_3 VARCHAR(50) NULL,
    comment_6_3 TEXT NULL,
    page_6_4 VARCHAR(50) NULL,
    comment_6_4 TEXT NULL,
    page_7_1 VARCHAR(50) NULL,
    comment_7_1 TEXT NULL,
    page_7_2 VARCHAR(50) NULL,
    comment_7_2 TEXT NULL,
    page_7_3 VARCHAR(50) NULL,
    comment_7_3 TEXT NULL,
    page_8_1 VARCHAR(50) NULL,
    comment_8_1 TEXT NULL,
    page_8_2 VARCHAR(50) NULL,
    comment_8_2 TEXT NULL,
    page_9_1 VARCHAR(50) NULL,
    comment_9_1 TEXT NULL,
    page_10_1 VARCHAR(50) NULL,
    comment_10_1 TEXT NULL,
    page_10_2 VARCHAR(50) NULL,
    comment_10_2 TEXT NULL,
    page_11_1 VARCHAR(50) NULL,
    comment_11_1 TEXT NULL,
    page_supp_1_1 VARCHAR(50) NULL,
    comment_supp_1_1 TEXT NULL,
    page_supp_1_2 VARCHAR(50) NULL,
    comment_supp_1_2 TEXT NULL,
    page_supp_2_1 VARCHAR(50) NULL,
    comment_supp_2_1 TEXT NULL,
    page_supp_3_1 VARCHAR(50) NULL,
    comment_supp_3_1 TEXT NULL,
    page_appendices VARCHAR(50) NULL,
    comment_appendices TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (trp_submission_id) REFERENCES trp_submissions(id) ON DELETE CASCADE,
    INDEX idx_trp_submission (trp_submission_id)
);

CREATE TABLE trp_files (
    id BIGINT PRIMARY KEY 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 DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (trp_submission_id) REFERENCES trp_submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_trp_submission (trp_submission_id),
    INDEX idx_uploaded_by (uploaded_by)
);

CREATE VIEW trp_submissions_list AS
SELECT 
    ts.id,
    ts.entity_name,
    ts.org_code,
    ts.submission_date,
    ts.compliance_status,
    ts.status,
    ts.total_systems,
    ts.entity_head_name,
    ts.created_at,
    ts.updated_at,
    u.username as created_by_username,
    u.email as created_by_email
FROM trp_submissions ts
LEFT JOIN users u ON ts.created_by = u.id
ORDER BY ts.created_at DESC;

ALTER TABLE trp_submissions 
MODIFY COLUMN entity_head_name VARCHAR(255) NULL,
MODIFY COLUMN signature_date DATE NULL;

ALTER TABLE trp_submissions MODIFY COLUMN status VARCHAR(20);

ALTER TABLE trp_submissions MODIFY COLUMN submission_date DATE NULL;

