-- =====================================================================
-- 화장품 제조 AI 품질예측 솔루션 (Cosmetics Quality Prediction System)
-- DB Schema for MySQL 5.7+ / 8.0
-- charset: utf8mb4
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. 제품 마스터
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_product (
    product_code  VARCHAR(20)  NOT NULL,
    product_name  VARCHAR(100) NOT NULL,
    category      VARCHAR(50)  DEFAULT NULL COMMENT '스킨/로션/크림/에센스 등',
    is_active     TINYINT(1)   DEFAULT 1,
    created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='제품 마스터';

-- ---------------------------------------------------------------------
-- 2. 원료 마스터
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_material (
    material_code VARCHAR(20)  NOT NULL,
    material_name VARCHAR(100) NOT NULL,
    material_grp  VARCHAR(50)  DEFAULT NULL COMMENT '점증제/중화제/유화제/보습제/방부제 등',
    unit          VARCHAR(10)  DEFAULT 'g',
    is_active     TINYINT(1)   DEFAULT 1,
    PRIMARY KEY (material_code),
    KEY idx_grp (material_grp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='원료 마스터';

-- ---------------------------------------------------------------------
-- 3. 표준 배합(처방) - 제품별 표준 투입량
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_formula (
    formula_id    BIGINT AUTO_INCREMENT,
    product_code  VARCHAR(20) NOT NULL,
    formula_ver   VARCHAR(10) NOT NULL DEFAULT 'V1',
    material_code VARCHAR(20) NOT NULL,
    std_ratio     DECIMAL(8,4) NOT NULL COMMENT '표준 배합비(%)',
    tolerance_pct DECIMAL(6,3) DEFAULT 2.000 COMMENT '허용 편차율(%)',
    PRIMARY KEY (formula_id),
    KEY idx_product_ver (product_code, formula_ver),
    KEY idx_material (material_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='표준 배합 처방';

-- ---------------------------------------------------------------------
-- 4. 품질 관리 기준 (Spec Limits) - 제품별 물성 상/하한
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_spec (
    spec_id       BIGINT AUTO_INCREMENT,
    product_code  VARCHAR(20) NOT NULL,
    prop_name     VARCHAR(20) NOT NULL COMMENT 'ph/hardness/specific_grav/content_pct/viscosity',
    spec_low      DECIMAL(12,4) NOT NULL,
    spec_high     DECIMAL(12,4) NOT NULL,
    spec_target   DECIMAL(12,4) DEFAULT NULL,
    PRIMARY KEY (spec_id),
    UNIQUE KEY uq_product_prop (product_code, prop_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='품질 관리 기준';

-- ---------------------------------------------------------------------
-- 5. 배치 헤더 (제조 배치)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_batch (
    batch_no      VARCHAR(30) NOT NULL,
    product_code  VARCHAR(20) NOT NULL,
    formula_ver   VARCHAR(10) DEFAULT 'V1',
    batch_size    DECIMAL(14,3) DEFAULT NULL COMMENT '제조 총량(g)',
    mix_rpm       INT          DEFAULT NULL COMMENT '교반 RPM',
    mix_minutes   INT          DEFAULT NULL COMMENT '교반 시간(분)',
    mix_temp      DECIMAL(6,2) DEFAULT NULL COMMENT '교반 온도(℃)',
    status        ENUM('WEIGHING','PREDICTED','MIXED','INSPECTED','DONE','REWORK')
                  DEFAULT 'WEIGHING',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (batch_no),
    KEY idx_product (product_code),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='제조 배치 헤더';

-- ---------------------------------------------------------------------
-- 6. 배치별 원료 계근 실적
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_weighing (
    weighing_id   BIGINT AUTO_INCREMENT,
    batch_no      VARCHAR(30) NOT NULL,
    material_code VARCHAR(20) NOT NULL,
    std_qty       DECIMAL(14,3) DEFAULT NULL COMMENT '표준 투입량(g)',
    actual_qty    DECIMAL(14,3) NOT NULL     COMMENT '실제 계근량(g)',
    deviation_pct DECIMAL(8,3)  DEFAULT NULL COMMENT '편차율(%) = (actual-std)/std*100',
    weigh_seq     INT           DEFAULT NULL COMMENT '투입 순서',
    weighed_at    DATETIME      DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (weighing_id),
    KEY idx_batch (batch_no),
    KEY idx_material (material_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='원료 계근 실적';

-- ---------------------------------------------------------------------
-- 7. 배치별 품질검사 결과 (학습 라벨)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_quality (
    quality_id    BIGINT AUTO_INCREMENT,
    batch_no      VARCHAR(30) NOT NULL,
    ph            DECIMAL(6,2)  DEFAULT NULL,
    viscosity     DECIMAL(12,2) DEFAULT NULL,
    hardness      DECIMAL(10,2) DEFAULT NULL,
    specific_grav DECIMAL(8,4)  DEFAULT NULL,
    content_pct   DECIMAL(8,3)  DEFAULT NULL,
    result_flag   ENUM('PASS','FAIL','REWORK') DEFAULT NULL,
    inspected_at  DATETIME      DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (quality_id),
    UNIQUE KEY uq_batch (batch_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='품질검사 결과(학습 라벨)';

-- ---------------------------------------------------------------------
-- 8. 예측 결과
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_prediction (
    pred_id       BIGINT AUTO_INCREMENT,
    batch_no      VARCHAR(30) NOT NULL,
    pred_ph       DECIMAL(6,2)  DEFAULT NULL,
    pred_hardness DECIMAL(10,2) DEFAULT NULL,
    pred_sg       DECIMAL(8,4)  DEFAULT NULL,
    pred_content  DECIMAL(8,3)  DEFAULT NULL,
    pred_visc     DECIMAL(12,2) DEFAULT NULL,
    pred_result   ENUM('PASS','FAIL','REWORK') DEFAULT NULL,
    fail_prob     DECIMAL(6,4)  DEFAULT NULL COMMENT '부적합 확률 0~1',
    model_ver     VARCHAR(20)   DEFAULT NULL,
    predicted_at  DATETIME      DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (pred_id),
    KEY idx_batch (batch_no),
    KEY idx_predicted (predicted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 품질 예측 결과';

-- ---------------------------------------------------------------------
-- 9. 경고 로그
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_warning (
    warning_id    BIGINT AUTO_INCREMENT,
    batch_no      VARCHAR(30) NOT NULL,
    prop_name     VARCHAR(20) DEFAULT NULL,
    level         ENUM('INFO','WARNING','CRITICAL') NOT NULL,
    message       VARCHAR(500) NOT NULL,
    is_ack        TINYINT(1)  DEFAULT 0 COMMENT '작업자 확인 여부',
    created_at    DATETIME    DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (warning_id),
    KEY idx_batch (batch_no),
    KEY idx_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='품질 이상 사전 경고 로그';

-- ---------------------------------------------------------------------
-- 10. 원료 영향도(SHAP) 저장
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cq_impact (
    impact_id     BIGINT AUTO_INCREMENT,
    batch_no      VARCHAR(30) NOT NULL,
    prop_name     VARCHAR(20) NOT NULL,
    feature_name  VARCHAR(60) NOT NULL,
    shap_value    DECIMAL(12,6) NOT NULL,
    direction     ENUM('UP','DOWN') NOT NULL,
    rank_no       INT DEFAULT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (impact_id),
    KEY idx_batch_prop (batch_no, prop_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='원료별 품질 영향도(SHAP)';

SET FOREIGN_KEY_CHECKS = 1;
