-- ============================================================
-- SI-BONE CAPA Database Schema
-- Database: sibone_capa (InMotion cPanel: cpanelusername_sibone_capa)
-- Standard: 21 CFR 820.100 / ISO 13485:2016 §8.5.2
--
-- Run this script in cPanel > phpMyAdmin after selecting
-- the sibone_capa database.
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Disable strict mode for this session to allow multiple DATETIME DEFAULT CURRENT_TIMESTAMP columns.
-- This is required for MySQL 5.7 on InMotion Hosting shared plans.
SET SESSION sql_mode = '';

-- ── PRIMARY RECORDS TABLE ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `capa_records` (

  -- Identity
  `id`                      INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `capa_number`             VARCHAR(30)      NOT NULL COMMENT 'e.g. CAPA-2026-0001',
  `revision`                VARCHAR(10)      DEFAULT '00',
  `status`                  VARCHAR(30)      DEFAULT 'Draft'
                            COMMENT 'Draft | In Review | In Progress | Closed',

  -- ── PHASE 1: CAPA REQUEST ──────────────────────────────────────────────────
  `date_opened`             DATE             DEFAULT NULL,
  `date_requested`          DATE             DEFAULT NULL,
  `requested_by`            VARCHAR(255)     DEFAULT NULL,
  `department`              VARCHAR(100)     DEFAULT NULL,
  `capa_source`             VARCHAR(100)     DEFAULT NULL,
  `source_ref`              VARCHAR(500)     DEFAULT NULL,
  `problem_description`     TEXT             DEFAULT NULL,
  `affected_products`       TEXT             DEFAULT NULL,
  `lot_numbers`             VARCHAR(500)     DEFAULT NULL,
  `qty_affected`            VARCHAR(200)     DEFAULT NULL,
  `safety_impact`           VARCHAR(50)      DEFAULT NULL  COMMENT 'yes | no | unk',
  `regulatory_report`       VARCHAR(50)      DEFAULT NULL  COMMENT 'yes | no | unk',
  `additional_background`   TEXT             DEFAULT NULL,

  -- Phase 1 signatures
  `sig1a_name`              VARCHAR(255)     DEFAULT NULL,
  `sig1a_date`              DATE             DEFAULT NULL,
  `sig1b_name`              VARCHAR(255)     DEFAULT NULL,
  `sig1b_date`              DATE             DEFAULT NULL,

  -- ── PHASE 2: REVIEW ───────────────────────────────────────────────────────
  `review_date`             DATE             DEFAULT NULL,
  `reviewed_by`             VARCHAR(255)     DEFAULT NULL,
  `capa_type`               VARCHAR(20)      DEFAULT NULL  COMMENT 'CA | PA | CAPA',
  `review_decision`         VARCHAR(20)      DEFAULT NULL  COMMENT 'continue | close',
  `close_justification`     TEXT             DEFAULT NULL,
  `capa_owner`              VARCHAR(255)     DEFAULT NULL,
  `target_date`             DATE             DEFAULT NULL,
  `scope_rationale`         TEXT             DEFAULT NULL,

  -- Phase 2 signatures
  `sig2a_name`              VARCHAR(255)     DEFAULT NULL,
  `sig2a_date`              DATE             DEFAULT NULL,
  `sig2b_name`              VARCHAR(255)     DEFAULT NULL,
  `sig2b_date`              DATE             DEFAULT NULL,
  `sig2c_name`              VARCHAR(255)     DEFAULT NULL,
  `sig2c_date`              DATE             DEFAULT NULL,

  -- ── PHASE 3: CONTAINMENT ──────────────────────────────────────────────────
  `containment_required`    VARCHAR(10)      DEFAULT NULL  COMMENT 'yes | no',
  `no_contain_rationale`    TEXT             DEFAULT NULL,
  `containment_desc`        TEXT             DEFAULT NULL,
  `containment_exp_date`    DATE             DEFAULT NULL,
  `containment_act_date`    DATE             DEFAULT NULL,
  `containment_evidence`    TEXT             DEFAULT NULL,

  -- Phase 3 signatures
  `sig3a_name`              VARCHAR(255)     DEFAULT NULL,
  `sig3a_date`              DATE             DEFAULT NULL,
  `sig3b_name`              VARCHAR(255)     DEFAULT NULL,
  `sig3b_date`              DATE             DEFAULT NULL,

  -- ── PHASE 4: ROOT CAUSE ───────────────────────────────────────────────────
  `lead_investigator`       VARCHAR(255)     DEFAULT NULL,
  `rca_exp_date`            DATE             DEFAULT NULL,
  `investigator2`           VARCHAR(255)     DEFAULT NULL,
  `investigator3`           VARCHAR(255)     DEFAULT NULL,
  `investigator4`           VARCHAR(255)     DEFAULT NULL,
  `rca_investigation`       TEXT             DEFAULT NULL,
  `rca_summary`             TEXT             DEFAULT NULL,

  -- Phase 4 signatures
  `sig4a_name`              VARCHAR(255)     DEFAULT NULL,
  `sig4a_date`              DATE             DEFAULT NULL,
  `sig4b_name`              VARCHAR(255)     DEFAULT NULL,
  `sig4b_date`              DATE             DEFAULT NULL,
  `sig4c_name`              VARCHAR(255)     DEFAULT NULL,
  `sig4c_date`              DATE             DEFAULT NULL,

  -- ── PHASE 5: CA/PA PLANNING ───────────────────────────────────────────────
  `ca_required`             VARCHAR(10)      DEFAULT NULL,
  `ca_no_rationale`         TEXT             DEFAULT NULL,
  `ca_plan`                 TEXT             DEFAULT NULL,
  `pa_required`             VARCHAR(10)      DEFAULT NULL,
  `pa_no_rationale`         TEXT             DEFAULT NULL,
  `pa_plan`                 TEXT             DEFAULT NULL,
  `impact_regulatory`       VARCHAR(10)      DEFAULT NULL,
  `impact_regulatory_text`  TEXT             DEFAULT NULL,
  `impact_safety`           VARCHAR(10)      DEFAULT NULL,
  `impact_safety_text`      TEXT             DEFAULT NULL,
  `impact_validation`       VARCHAR(10)      DEFAULT NULL,
  `impact_validation_text`  TEXT             DEFAULT NULL,
  `impact_notes`            TEXT             DEFAULT NULL,
  `implementer`             VARCHAR(255)     DEFAULT NULL,
  `phase5_target_date`      DATE             DEFAULT NULL,
  `participant2`            VARCHAR(255)     DEFAULT NULL,
  `participant3`            VARCHAR(255)     DEFAULT NULL,
  `participant4`            VARCHAR(255)     DEFAULT NULL,

  -- Phase 5 signatures
  `sig5a_name`              VARCHAR(255)     DEFAULT NULL,
  `sig5a_date`              DATE             DEFAULT NULL,
  `sig5b_name`              VARCHAR(255)     DEFAULT NULL,
  `sig5b_date`              DATE             DEFAULT NULL,
  `sig5c_name`              VARCHAR(255)     DEFAULT NULL,
  `sig5c_date`              DATE             DEFAULT NULL,

  -- ── AUDIT METADATA ────────────────────────────────────────────────────────
  `submitter_ip`            VARCHAR(45)      DEFAULT NULL,
  `created_at`              DATETIME         NOT NULL,
  `updated_at`              DATETIME         NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_capa_number` (`capa_number`),
  KEY `idx_status`           (`status`),
  KEY `idx_date_opened`      (`date_opened`),
  KEY `idx_department`       (`department`)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='SI-BONE CAPA records — 21 CFR 820.100 / ISO 13485 §8.5.2';


-- ── AUDIT LOG TABLE ───────────────────────────────────────────────────────────
-- Provides a lightweight server-side audit trail per 21 CFR 11 §10(e).
-- Every save and submit action is recorded as an immutable INSERT.

CREATE TABLE IF NOT EXISTS `capa_audit_log` (

  `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `capa_number`  VARCHAR(30)   NOT NULL,
  `action`       VARCHAR(50)   NOT NULL COMMENT 'save | submit_phase',
  `submitter_ip` VARCHAR(45)   DEFAULT NULL,
  `created_at`   DATETIME      NOT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_capa_number` (`capa_number`),
  KEY `idx_created_at`  (`created_at`)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Immutable audit trail for CAPA record saves and submissions';


-- ── VERIFY ────────────────────────────────────────────────────────────────────
SELECT 'Schema installed successfully' AS result;
SHOW TABLES;