-- ============================================================================
-- GUARDiA MES — PostgreSQL 스키마 (mes_db)
-- AI 기반 제조실행시스템 (WMS + MES + QMS 통합)
-- 보안 불변: 거래처/인사 PII *_enc AES-256-GCM 저장·응답 마스킹, 스택트레이스 미노출
-- 모든 테이블 IF NOT EXISTS / 시드 admin(SuperAdmin, bcrypt admin123) + 데모 데이터
-- ============================================================================

-- ── 운영자 계정 (auth/admin) — RBAC: SUPERADMIN/MANAGER/WORKER/VIEWER ──────────
CREATE TABLE IF NOT EXISTS mes_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(500) NOT NULL,
    display_name VARCHAR(200),
    role VARCHAR(50) DEFAULT 'VIEWER',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);
-- 기본 관리자 (비밀번호: admin123 / BCrypt $2a). 운영 배포 시 즉시 변경.
INSERT INTO mes_user (username, password_hash, display_name, role)
VALUES ('admin', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '최고관리자', 'SUPERADMIN')
ON CONFLICT (username) DO NOTHING;
INSERT INTO mes_user (username, password_hash, display_name, role)
VALUES ('manager', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '생산관리자', 'MANAGER')
ON CONFLICT (username) DO NOTHING;
INSERT INTO mes_user (username, password_hash, display_name, role)
VALUES ('worker', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '현장작업자', 'WORKER')
ON CONFLICT (username) DO NOTHING;

CREATE TABLE IF NOT EXISTS mes_audit_log (
    id BIGSERIAL PRIMARY KEY,
    actor VARCHAR(200),
    action VARCHAR(100),
    target VARCHAR(300),
    detail TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_audit_action ON mes_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_mes_audit_actor ON mes_audit_log(actor);

CREATE TABLE IF NOT EXISTS mes_setting (
    key VARCHAR(200) PRIMARY KEY,
    value TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO mes_setting (key, value) VALUES
('ollama_text_model', 'llama3'),
('ollama_vision_model', 'llava'),
('default_warehouse', 'WH-MAIN'),
('oee_target', '0.85'),
('itsm_equipment_fault_to_sr', 'true')
ON CONFLICT (key) DO NOTHING;

-- ============================================================================
-- 기준정보 (master)
-- ============================================================================
CREATE TABLE IF NOT EXISTS mes_item (
    id BIGSERIAL PRIMARY KEY,
    item_code VARCHAR(100) UNIQUE NOT NULL,
    item_name VARCHAR(300),
    item_type VARCHAR(50) DEFAULT 'PRODUCT',   -- RAW(자재)/SEMI(반제품)/PRODUCT(제품)/SUBSIDIARY(부자재)
    unit VARCHAR(30) DEFAULT 'EA',
    barcode VARCHAR(200),
    spec VARCHAR(500),
    safety_stock DOUBLE PRECISION DEFAULT 0,
    warehouse_code VARCHAR(100),
    attributes JSONB,
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_item_type ON mes_item(item_type);

CREATE TABLE IF NOT EXISTS mes_bom (
    id BIGSERIAL PRIMARY KEY,
    parent_item_code VARCHAR(100) NOT NULL,
    child_item_code VARCHAR(100) NOT NULL,
    qty_per DOUBLE PRECISION DEFAULT 1,
    unit VARCHAR(30) DEFAULT 'EA',
    seq INTEGER DEFAULT 1,
    bom_version VARCHAR(50) DEFAULT 'V1',
    loss_rate DOUBLE PRECISION DEFAULT 0,
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_bom_parent ON mes_bom(parent_item_code);

CREATE TABLE IF NOT EXISTS mes_routing (
    id BIGSERIAL PRIMARY KEY,
    item_code VARCHAR(100) NOT NULL,
    op_seq INTEGER DEFAULT 10,
    process_code VARCHAR(100),
    process_name VARCHAR(200),
    equipment_code VARCHAR(100),
    std_time_min DOUBLE PRECISION DEFAULT 0,
    setup_time_min DOUBLE PRECISION DEFAULT 0,
    routing_version VARCHAR(50) DEFAULT 'V1',
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_routing_item ON mes_routing(item_code);

CREATE TABLE IF NOT EXISTS mes_equipment (
    id BIGSERIAL PRIMARY KEY,
    equipment_code VARCHAR(100) UNIQUE NOT NULL,
    equipment_name VARCHAR(300),
    process_code VARCHAR(100),
    warehouse_code VARCHAR(100),
    check_cycle_days INTEGER DEFAULT 30,
    last_checked_at TIMESTAMP,
    parameters JSONB,
    run_status VARCHAR(30) DEFAULT 'IDLE',     -- RUN/IDLE/DOWN/MAINT
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mes_partner (
    id BIGSERIAL PRIMARY KEY,
    partner_code VARCHAR(100) UNIQUE NOT NULL,
    partner_name VARCHAR(300),
    partner_type VARCHAR(50) DEFAULT 'SUPPLIER',   -- SUPPLIER/CUSTOMER/BOTH
    biz_no VARCHAR(50),
    contact_name VARCHAR(200),
    contact_phone_enc VARCHAR(500),     -- AES-256-GCM 암호화
    contact_email_enc VARCHAR(500),     -- AES-256-GCM 암호화
    address VARCHAR(500),
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mes_warehouse (
    id BIGSERIAL PRIMARY KEY,
    warehouse_code VARCHAR(100) UNIQUE NOT NULL,
    warehouse_name VARCHAR(300),
    warehouse_type VARCHAR(50) DEFAULT 'GENERAL',  -- RAW/WIP/FG/GENERAL
    address VARCHAR(500),
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mes_location (
    id BIGSERIAL PRIMARY KEY,
    warehouse_code VARCHAR(100) NOT NULL,
    location_code VARCHAR(100) NOT NULL,
    zone VARCHAR(50),
    aisle VARCHAR(50),
    rack VARCHAR(50),
    bin VARCHAR(50),
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_location_wh ON mes_location(warehouse_code);

-- ============================================================================
-- MES (제조실행)
-- ============================================================================
CREATE TABLE IF NOT EXISTS mes_workorder (
    id BIGSERIAL PRIMARY KEY,
    wo_no VARCHAR(100) UNIQUE NOT NULL,
    item_code VARCHAR(100) NOT NULL,
    plan_qty DOUBLE PRECISION NOT NULL,
    produced_qty DOUBLE PRECISION DEFAULT 0,
    defect_qty DOUBLE PRECISION DEFAULT 0,
    unit VARCHAR(30) DEFAULT 'EA',
    due_date DATE,
    warehouse_code VARCHAR(100),
    status VARCHAR(30) DEFAULT 'CREATED',  -- CREATED/RELEASED/INPROGRESS/DONE/CLOSED/CANCELLED
    priority VARCHAR(20) DEFAULT 'MEDIUM',
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_wo_status ON mes_workorder(status);

-- 작업관리(job) — 작업/작업자/설비 배정·할당·이력 ★사용자 강조
CREATE TABLE IF NOT EXISTS mes_job (
    id BIGSERIAL PRIMARY KEY,
    job_no VARCHAR(100) UNIQUE NOT NULL,
    workorder_id BIGINT,
    wo_no VARCHAR(100),
    item_code VARCHAR(100),
    op_seq INTEGER,
    process_code VARCHAR(100),
    process_name VARCHAR(200),
    equipment_code VARCHAR(100),
    worker_id VARCHAR(100),         -- 배정 작업자
    assigned_qty DOUBLE PRECISION DEFAULT 0,
    done_qty DOUBLE PRECISION DEFAULT 0,
    status VARCHAR(30) DEFAULT 'ASSIGNED',  -- ASSIGNED/STARTED/PAUSED/DONE/CANCELLED
    planned_start TIMESTAMP,
    planned_end TIMESTAMP,
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_job_wo ON mes_job(workorder_id);
CREATE INDEX IF NOT EXISTS idx_mes_job_worker ON mes_job(worker_id);
CREATE INDEX IF NOT EXISTS idx_mes_job_status ON mes_job(status);

CREATE TABLE IF NOT EXISTS mes_production (
    id BIGSERIAL PRIMARY KEY,
    workorder_id BIGINT,
    wo_no VARCHAR(100),
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    good_qty DOUBLE PRECISION DEFAULT 0,
    defect_qty DOUBLE PRECISION DEFAULT 0,
    defect_code VARCHAR(100),
    process_code VARCHAR(100),
    equipment_code VARCHAR(100),
    worker_id VARCHAR(100),
    warehouse_code VARCHAR(100),
    reported_at TIMESTAMP DEFAULT NOW(),
    created_by VARCHAR(200)
);
CREATE INDEX IF NOT EXISTS idx_mes_prod_wo ON mes_production(wo_no);
CREATE INDEX IF NOT EXISTS idx_mes_prod_lot ON mes_production(lot_no);

CREATE TABLE IF NOT EXISTS mes_process_progress (
    id BIGSERIAL PRIMARY KEY,
    workorder_id BIGINT,
    wo_no VARCHAR(100),
    op_seq INTEGER,
    process_code VARCHAR(100),
    process_name VARCHAR(200),
    equipment_code VARCHAR(100),
    worker_id VARCHAR(100),
    status VARCHAR(30) DEFAULT 'WAIT',  -- WAIT/RUNNING/DONE
    input_qty DOUBLE PRECISION DEFAULT 0,
    output_qty DOUBLE PRECISION DEFAULT 0,
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_proc_wo ON mes_process_progress(workorder_id);

CREATE TABLE IF NOT EXISTS mes_oee (
    id BIGSERIAL PRIMARY KEY,
    equipment_code VARCHAR(100) NOT NULL,
    record_date DATE DEFAULT CURRENT_DATE,
    planned_time_min DOUBLE PRECISION DEFAULT 0,
    run_time_min DOUBLE PRECISION DEFAULT 0,
    downtime_min DOUBLE PRECISION DEFAULT 0,
    downtime_code VARCHAR(100),
    ideal_cycle_min DOUBLE PRECISION DEFAULT 0,
    total_count DOUBLE PRECISION DEFAULT 0,
    good_count DOUBLE PRECISION DEFAULT 0,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_oee_eqp ON mes_oee(equipment_code, record_date);

CREATE TABLE IF NOT EXISTS mes_plan (
    id BIGSERIAL PRIMARY KEY,
    plan_no VARCHAR(100) UNIQUE NOT NULL,
    item_code VARCHAR(100),
    plan_qty DOUBLE PRECISION DEFAULT 0,
    start_date DATE,
    end_date DATE,
    status VARCHAR(30) DEFAULT 'PLANNED',  -- PLANNED/CONFIRMED/RELEASED/DONE
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- WMS (창고관리)
-- ============================================================================
CREATE TABLE IF NOT EXISTS mes_inventory (
    id BIGSERIAL PRIMARY KEY,
    item_code VARCHAR(100) NOT NULL,
    warehouse_code VARCHAR(100),
    location_code VARCHAR(100),
    lot_no VARCHAR(100),
    qty DOUBLE PRECISION DEFAULT 0,
    unit VARCHAR(30) DEFAULT 'EA',
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_inv_item ON mes_inventory(item_code);
CREATE INDEX IF NOT EXISTS idx_mes_inv_wh ON mes_inventory(warehouse_code);

CREATE TABLE IF NOT EXISTS mes_inventory_txn (
    id BIGSERIAL PRIMARY KEY,
    txn_type VARCHAR(30) NOT NULL,     -- RECEIVE/ISSUE/PRODUCE/CONSUME/TRANSFER/ADJUST/SHIP
    item_code VARCHAR(100),
    from_warehouse VARCHAR(100),
    from_location VARCHAR(100),
    to_warehouse VARCHAR(100),
    to_location VARCHAR(100),
    lot_no VARCHAR(100),
    qty DOUBLE PRECISION DEFAULT 0,
    ref_type VARCHAR(50),              -- WORKORDER/RECEIVING/SHIPPING/STOCKTAKE/TRANSFER
    ref_no VARCHAR(100),
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_txn_item ON mes_inventory_txn(item_code);
CREATE INDEX IF NOT EXISTS idx_mes_txn_ref ON mes_inventory_txn(ref_type, ref_no);
CREATE INDEX IF NOT EXISTS idx_mes_txn_lot ON mes_inventory_txn(lot_no);

CREATE TABLE IF NOT EXISTS mes_receiving (
    id BIGSERIAL PRIMARY KEY,
    receiving_no VARCHAR(100) UNIQUE NOT NULL,
    partner_code VARCHAR(100),
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    qty DOUBLE PRECISION DEFAULT 0,
    warehouse_code VARCHAR(100),
    location_code VARCHAR(100),
    status VARCHAR(30) DEFAULT 'REQUESTED',   -- REQUESTED/INSPECTING/ACCEPTED/REJECTED
    inspection_required BOOLEAN DEFAULT FALSE,
    inspection_result VARCHAR(20),            -- PASS/FAIL
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_recv_status ON mes_receiving(status);

-- 출하관리(shipping) — 출하지시·피킹/패킹·출하검사·출하실적·납품서/거래명세서 ★사용자 강조
CREATE TABLE IF NOT EXISTS mes_shipping (
    id BIGSERIAL PRIMARY KEY,
    shipping_no VARCHAR(100) UNIQUE NOT NULL,
    partner_code VARCHAR(100),          -- 고객
    order_ref VARCHAR(100),             -- 주문/수주 참조
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    plan_qty DOUBLE PRECISION DEFAULT 0,
    picked_qty DOUBLE PRECISION DEFAULT 0,
    packed_qty DOUBLE PRECISION DEFAULT 0,
    shipped_qty DOUBLE PRECISION DEFAULT 0,
    warehouse_code VARCHAR(100),
    location_code VARCHAR(100),
    status VARCHAR(30) DEFAULT 'ORDERED', -- ORDERED/PICKING/PACKING/INSPECTED/SHIPPED/CANCELLED
    inspection_required BOOLEAN DEFAULT FALSE,
    inspection_result VARCHAR(20),        -- PASS/FAIL
    delivery_no VARCHAR(100),             -- 납품서 번호
    statement_no VARCHAR(100),            -- 거래명세서 번호
    ship_date DATE,
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_ship_status ON mes_shipping(status);
CREATE INDEX IF NOT EXISTS idx_mes_ship_partner ON mes_shipping(partner_code);

CREATE TABLE IF NOT EXISTS mes_stocktake (
    id BIGSERIAL PRIMARY KEY,
    stocktake_no VARCHAR(100) NOT NULL,
    item_code VARCHAR(100),
    warehouse_code VARCHAR(100),
    location_code VARCHAR(100),
    lot_no VARCHAR(100),
    system_qty DOUBLE PRECISION DEFAULT 0,
    counted_qty DOUBLE PRECISION DEFAULT 0,
    diff_qty DOUBLE PRECISION DEFAULT 0,
    status VARCHAR(30) DEFAULT 'COUNTED',  -- COUNTED/ADJUSTED
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_stk_no ON mes_stocktake(stocktake_no);

CREATE TABLE IF NOT EXISTS mes_lotserial (
    id BIGSERIAL PRIMARY KEY,
    lot_no VARCHAR(100) NOT NULL,
    serial_no VARCHAR(150),
    item_code VARCHAR(100),
    qty DOUBLE PRECISION DEFAULT 0,
    source_type VARCHAR(50),           -- PRODUCTION/RECEIVING
    source_ref VARCHAR(100),
    mfg_date DATE,
    expiry_date DATE,
    status VARCHAR(30) DEFAULT 'ACTIVE',  -- ACTIVE/CONSUMED/SHIPPED/EXPIRED
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_lot_no ON mes_lotserial(lot_no);
CREATE INDEX IF NOT EXISTS idx_mes_lot_item ON mes_lotserial(item_code);

-- ============================================================================
-- QMS (품질관리)
-- ============================================================================
CREATE TABLE IF NOT EXISTS mes_inspection (
    id BIGSERIAL PRIMARY KEY,
    inspection_no VARCHAR(100) UNIQUE NOT NULL,
    inspection_type VARCHAR(20) DEFAULT 'IQC',  -- IQC(수입)/IPQC(공정)/OQC(출하)
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    ref_type VARCHAR(50),              -- RECEIVING/WORKORDER/SHIPPING
    ref_no VARCHAR(100),
    spec_code VARCHAR(100),
    sample_size INTEGER DEFAULT 1,
    measurements JSONB,                -- [{item, measured, lsl, usl, judge}] 가변구조
    result VARCHAR(20) DEFAULT 'PENDING',  -- PENDING/PASS/FAIL
    defect_qty DOUBLE PRECISION DEFAULT 0,
    inspector VARCHAR(100),
    remark TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_insp_type ON mes_inspection(inspection_type);
CREATE INDEX IF NOT EXISTS idx_mes_insp_result ON mes_inspection(result);

CREATE TABLE IF NOT EXISTS mes_spec (
    id BIGSERIAL PRIMARY KEY,
    spec_code VARCHAR(100) UNIQUE NOT NULL,
    item_code VARCHAR(100),
    inspection_type VARCHAR(20) DEFAULT 'IQC',
    characteristic VARCHAR(200),       -- 검사 특성(치수/외관 등)
    lsl DOUBLE PRECISION,
    usl DOUBLE PRECISION,
    target DOUBLE PRECISION,
    unit VARCHAR(30),
    aql DOUBLE PRECISION,              -- 합격품질수준
    items JSONB,                       -- 다중 검사항목 정의
    status VARCHAR(30) DEFAULT 'ACTIVE',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mes_ncr (
    id BIGSERIAL PRIMARY KEY,
    ncr_no VARCHAR(100) UNIQUE NOT NULL,
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    ref_type VARCHAR(50),
    ref_no VARCHAR(100),
    defect_code VARCHAR(100),
    defect_qty DOUBLE PRECISION DEFAULT 0,
    description TEXT,
    disposition VARCHAR(50),           -- USE_AS_IS/REWORK/SCRAP/RETURN
    severity VARCHAR(20) DEFAULT 'MINOR',  -- MINOR/MAJOR/CRITICAL
    status VARCHAR(30) DEFAULT 'OPEN',     -- OPEN/REVIEW/DISPOSITIONED/CLOSED
    capa_id BIGINT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_ncr_status ON mes_ncr(status);

CREATE TABLE IF NOT EXISTS mes_capa (
    id BIGSERIAL PRIMARY KEY,
    capa_no VARCHAR(100) UNIQUE NOT NULL,
    ncr_no VARCHAR(100),
    capa_type VARCHAR(20) DEFAULT 'CORRECTIVE',  -- CORRECTIVE/PREVENTIVE
    title VARCHAR(300),
    root_cause TEXT,
    action_plan TEXT,                  -- 8D/시정조치 계획
    owner VARCHAR(100),
    due_date DATE,
    effectiveness VARCHAR(50),         -- 효과성 검증 결과
    status VARCHAR(30) DEFAULT 'OPEN', -- OPEN/INPROGRESS/VERIFY/CLOSED
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_capa_status ON mes_capa(status);

CREATE TABLE IF NOT EXISTS mes_spc_sample (
    id BIGSERIAL PRIMARY KEY,
    chart_code VARCHAR(100) NOT NULL,  -- 관리도 식별(품목+특성)
    item_code VARCHAR(100),
    characteristic VARCHAR(200),
    subgroup_no INTEGER,
    sample_values JSONB,               -- 부분군 측정값 배열
    sample_mean DOUBLE PRECISION,
    sample_range DOUBLE PRECISION,
    measured_at TIMESTAMP DEFAULT NOW(),
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mes_spc_chart ON mes_spc_sample(chart_code);

CREATE TABLE IF NOT EXISTS mes_certificate (
    id BIGSERIAL PRIMARY KEY,
    certificate_no VARCHAR(100) UNIQUE NOT NULL,
    cert_type VARCHAR(30) DEFAULT 'COA',  -- COA(성적서)/MSDS/TEST_REPORT
    item_code VARCHAR(100),
    lot_no VARCHAR(100),
    partner_code VARCHAR(100),
    ref_type VARCHAR(50),
    ref_no VARCHAR(100),
    issued_date DATE DEFAULT CURRENT_DATE,
    content JSONB,                     -- 시험 항목/결과
    file_path VARCHAR(500),
    status VARCHAR(30) DEFAULT 'ISSUED',
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 데모 데이터 (시연·테스트용 — ON CONFLICT 무시)
-- ============================================================================
INSERT INTO mes_warehouse (warehouse_code, warehouse_name, warehouse_type, created_by) VALUES
('WH-MAIN', '메인창고', 'GENERAL', 'system'),
('WH-RAW', '자재창고', 'RAW', 'system'),
('WH-FG', '완제품창고', 'FG', 'system')
ON CONFLICT (warehouse_code) DO NOTHING;

INSERT INTO mes_location (warehouse_code, location_code, zone, aisle, rack, bin) VALUES
('WH-MAIN', 'A-01-01', 'A', '01', '01', '01'),
('WH-RAW', 'R-01-01', 'R', '01', '01', '01'),
('WH-FG', 'F-01-01', 'F', '01', '01', '01')
ON CONFLICT DO NOTHING;

INSERT INTO mes_partner (partner_code, partner_name, partner_type, biz_no, contact_name, created_by) VALUES
('SUP-001', '대한정밀소재', 'SUPPLIER', '123-45-67890', '김자재', 'system'),
('CUS-001', '현대모비스', 'CUSTOMER', '220-81-00001', '이고객', 'system')
ON CONFLICT (partner_code) DO NOTHING;

INSERT INTO mes_item (item_code, item_name, item_type, unit, safety_stock, warehouse_code, created_by) VALUES
('FG-1001', '완제품 컨트롤러 모듈', 'PRODUCT', 'EA', 50, 'WH-FG', 'system'),
('SEMI-2001', '반제품 PCB 어셈블리', 'SEMI', 'EA', 100, 'WH-MAIN', 'system'),
('RAW-3001', '원자재 알루미늄 하우징', 'RAW', 'EA', 200, 'WH-RAW', 'system'),
('RAW-3002', '원자재 커넥터', 'RAW', 'EA', 500, 'WH-RAW', 'system')
ON CONFLICT (item_code) DO NOTHING;

INSERT INTO mes_bom (parent_item_code, child_item_code, qty_per, unit, seq, created_by) VALUES
('FG-1001', 'SEMI-2001', 1, 'EA', 1, 'system'),
('FG-1001', 'RAW-3001', 1, 'EA', 2, 'system'),
('SEMI-2001', 'RAW-3002', 4, 'EA', 1, 'system')
ON CONFLICT DO NOTHING;

INSERT INTO mes_routing (item_code, op_seq, process_code, process_name, equipment_code, std_time_min, created_by) VALUES
('FG-1001', 10, 'P-ASSY', '조립', 'EQ-ASSY-01', 5.0, 'system'),
('FG-1001', 20, 'P-TEST', '검사', 'EQ-TEST-01', 2.0, 'system')
ON CONFLICT DO NOTHING;

INSERT INTO mes_equipment (equipment_code, equipment_name, process_code, warehouse_code, check_cycle_days, run_status, created_by) VALUES
('EQ-ASSY-01', '조립설비 1호기', 'P-ASSY', 'WH-MAIN', 30, 'IDLE', 'system'),
('EQ-TEST-01', '검사설비 1호기', 'P-TEST', 'WH-MAIN', 60, 'IDLE', 'system')
ON CONFLICT (equipment_code) DO NOTHING;

INSERT INTO mes_inventory (item_code, warehouse_code, location_code, lot_no, qty, unit) VALUES
('RAW-3001', 'WH-RAW', 'R-01-01', 'LOT-RAW3001-001', 150, 'EA'),
('RAW-3002', 'WH-RAW', 'R-01-01', 'LOT-RAW3002-001', 600, 'EA'),
('SEMI-2001', 'WH-MAIN', 'A-01-01', 'LOT-SEMI2001-001', 80, 'EA')
ON CONFLICT DO NOTHING;

INSERT INTO mes_workorder (wo_no, item_code, plan_qty, unit, due_date, warehouse_code, status, priority, created_by) VALUES
('WO-DEMO-0001', 'FG-1001', 100, 'EA', CURRENT_DATE + 7, 'WH-FG', 'RELEASED', 'HIGH', 'system'),
('WO-DEMO-0002', 'SEMI-2001', 200, 'EA', CURRENT_DATE + 3, 'WH-MAIN', 'CREATED', 'MEDIUM', 'system')
ON CONFLICT (wo_no) DO NOTHING;

INSERT INTO mes_job (job_no, workorder_id, wo_no, item_code, op_seq, process_code, process_name, equipment_code, worker_id, assigned_qty, status, created_by)
SELECT 'JOB-DEMO-0001', wo.id, 'WO-DEMO-0001', 'FG-1001', 10, 'P-ASSY', '조립', 'EQ-ASSY-01', 'worker', 100, 'ASSIGNED', 'system'
FROM mes_workorder wo WHERE wo.wo_no = 'WO-DEMO-0001'
ON CONFLICT (job_no) DO NOTHING;

INSERT INTO mes_spec (spec_code, item_code, inspection_type, characteristic, lsl, usl, target, unit, aql, created_by) VALUES
('SPEC-FG1001-DIM', 'FG-1001', 'OQC', '외형 치수', 9.8, 10.2, 10.0, 'mm', 1.0, 'system'),
('SPEC-RAW3001-DIM', 'RAW-3001', 'IQC', '하우징 두께', 1.9, 2.1, 2.0, 'mm', 1.5, 'system')
ON CONFLICT (spec_code) DO NOTHING;

INSERT INTO mes_inspection (inspection_no, inspection_type, item_code, lot_no, spec_code, sample_size, result, inspector, created_by) VALUES
('INS-DEMO-0001', 'IQC', 'RAW-3001', 'LOT-RAW3001-001', 'SPEC-RAW3001-DIM', 5, 'PASS', 'worker', 'system')
ON CONFLICT (inspection_no) DO NOTHING;
