-- ============================================
-- 폐기 반출 시스템 고도화 - DB 마이그레이션
-- ============================================

-- 1. products 테이블에 원가 컬럼 추가 (없는 경우)
-- ============================================
-- 이미 존재하면 에러 발생 (PHP에서 처리)
ALTER TABLE products 
ADD COLUMN cost_price DECIMAL(10,2) DEFAULT 0 COMMENT '매입 원가 (부가세 제외)';

-- 2. disposal_removals 테이블 고도화
-- ============================================
ALTER TABLE disposal_removals 
-- 손실 금액 관련 (원가 + 판매가 기준)
ADD COLUMN total_cost_loss DECIMAL(10,2) DEFAULT 0 COMMENT '원가 손실 (회계 기준)',
ADD COLUMN total_revenue_loss DECIMAL(10,2) DEFAULT 0 COMMENT '매출 손실 (기회비용)',
ADD COLUMN total_margin_loss DECIMAL(10,2) DEFAULT 0 COMMENT '마진 손실 (순이익 영향)',

-- 승인 워크플로우
ADD COLUMN requires_approval BOOLEAN DEFAULT TRUE COMMENT '승인 필요 여부',
ADD COLUMN approval_level ENUM('none', 'manager', 'branch', 'owner') DEFAULT 'manager' COMMENT '필요 승인 레벨',
ADD COLUMN approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' COMMENT '승인 상태',
ADD COLUMN approved_by INT NULL COMMENT '승인자 user_id',
ADD COLUMN approved_at DATETIME NULL COMMENT '승인 일시',
ADD COLUMN rejection_reason TEXT NULL COMMENT '반려 사유',

-- 사진 첨부
ADD COLUMN photos JSON NULL COMMENT '첨부 사진 경로 배열 ["path1.jpg", "path2.jpg"]',

-- 반출 일정
ADD COLUMN preferred_date DATE NULL COMMENT '희망 반출일',
ADD COLUMN preferred_time ENUM('morning', 'afternoon', 'evening') NULL COMMENT '희망 시간대',
ADD COLUMN vehicle_info VARCHAR(255) NULL COMMENT '차량 정보',
ADD COLUMN actual_removal_date DATETIME NULL COMMENT '실제 반출 완료 일시',

-- 처리 비용 (외부 업체 이용 시)
ADD COLUMN removal_cost DECIMAL(10,2) DEFAULT 0 COMMENT '반출 처리 비용 (운송비 등)',

-- 알림
ADD COLUMN notification_sent BOOLEAN DEFAULT FALSE COMMENT '알림 발송 여부',
ADD COLUMN notified_at DATETIME NULL COMMENT '알림 발송 일시';

-- 3. 인덱스 추가 (성능 최적화)
-- ============================================
CREATE INDEX idx_disposal_approval_status ON disposal_removals(approval_status);
CREATE INDEX idx_disposal_preferred_date ON disposal_removals(preferred_date);
CREATE INDEX idx_disposal_created_at ON disposal_removals(created_at);
CREATE INDEX idx_disposal_approved_by ON disposal_removals(approved_by);

-- 4. 차량 관리 테이블 생성 (선택적)
-- ============================================
CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_name VARCHAR(100) NOT NULL COMMENT '차량명 (예: 1톤 트럭)',
    vehicle_number VARCHAR(50) NOT NULL COMMENT '차량 번호',
    vehicle_type ENUM('truck', 'van', 'external') DEFAULT 'truck' COMMENT '차량 구분',
    capacity_kg INT DEFAULT 1000 COMMENT '적재 용량 (kg)',
    driver_name VARCHAR(100) NULL COMMENT '운전자명',
    driver_phone VARCHAR(20) NULL COMMENT '운전자 연락처',
    is_active BOOLEAN DEFAULT TRUE COMMENT '사용 가능 여부',
    notes TEXT NULL COMMENT '비고',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_vehicle_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='차량 정보 관리';

-- 5. 승인 임계값 설정 테이블 (선택적)
-- ============================================
CREATE TABLE IF NOT EXISTS disposal_approval_thresholds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    approval_level ENUM('manager', 'branch', 'owner') NOT NULL COMMENT '승인 레벨',
    min_amount DECIMAL(10,2) DEFAULT 0 COMMENT '최소 금액',
    max_amount DECIMAL(10,2) DEFAULT 999999.99 COMMENT '최대 금액',
    is_active BOOLEAN DEFAULT TRUE COMMENT '활성화 여부',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_approval_level (approval_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='폐기 반출 승인 임계값 설정';

-- 기본 임계값 데이터 삽입
INSERT INTO disposal_approval_thresholds (approval_level, min_amount, max_amount) 
VALUES 
    ('manager', 0, 100000),           -- 0 ~ 10만원: 매니저 승인
    ('branch', 100000, 500000),       -- 10만원 ~ 50만원: 지점장 승인
    ('owner', 500000, 999999999.99)   -- 50만원 이상: 점주 승인
ON DUPLICATE KEY UPDATE 
    min_amount = VALUES(min_amount),
    max_amount = VALUES(max_amount);

-- 6. 외래키 제약조건 추가
-- ============================================
ALTER TABLE disposal_removals 
ADD CONSTRAINT fk_disposal_approved_by 
    FOREIGN KEY (approved_by) REFERENCES users(id) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE;

-- 7. 기존 데이터 마이그레이션 (선택적)
-- ============================================
-- 기존 disposal_removals의 총액 계산 (상품 가격 정보가 있다면)
UPDATE disposal_removals dr
LEFT JOIN (
    SELECT 
        dri.disposal_removal_id,
        SUM(p.cost_price * dri.quantity) as cost_loss,
        SUM(p.price * dri.quantity) as revenue_loss
    FROM disposal_removal_items dri
    JOIN products p ON dri.product_id = p.id
    GROUP BY dri.disposal_removal_id
) calc ON dr.id = calc.disposal_removal_id
SET 
    dr.total_cost_loss = COALESCE(calc.cost_loss, 0),
    dr.total_revenue_loss = COALESCE(calc.revenue_loss, 0),
    dr.total_margin_loss = COALESCE(calc.revenue_loss - calc.cost_loss, 0)
WHERE dr.total_cost_loss = 0 OR dr.total_cost_loss IS NULL;
