-- 능력제 인센티브 체크리스트 시스템 테이블 생성
-- 생성일: 2026-01-02

-- 1. 능력제 체크리스트 템플릿
CREATE TABLE IF NOT EXISTS performance_checklist_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '템플릿 이름',
    category ENUM('daily', 'weekly', 'monthly') DEFAULT 'daily' COMMENT '주기',
    target_role VARCHAR(50) COMMENT '대상 역할',
    is_active TINYINT(1) DEFAULT 1 COMMENT '활성 여부',
    created_by INT COMMENT '생성자 (점주)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_active (is_active),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='능력제 인센티브 체크리스트 템플릿';

-- 2. 체크리스트 항목 (업무)
CREATE TABLE IF NOT EXISTS performance_checklist_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    item_text VARCHAR(500) NOT NULL COMMENT '업무 내용',
    incentive_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '인센티브 금액 (원)',
    is_required TINYINT(1) DEFAULT 0 COMMENT '필수 여부',
    display_order INT DEFAULT 0 COMMENT '표시 순서',
    is_active TINYINT(1) DEFAULT 1 COMMENT '활성 여부',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES performance_checklist_templates(id) ON DELETE CASCADE,
    INDEX idx_template (template_id),
    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='능력제 체크리스트 업무 항목';

-- 3. 실제 수행 기록 (일별)
CREATE TABLE IF NOT EXISTS performance_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL COMMENT '수행 직원',
    template_id INT NOT NULL,
    record_date DATE NOT NULL COMMENT '수행일',
    total_items INT DEFAULT 0 COMMENT '총 항목 수',
    completed_items INT DEFAULT 0 COMMENT '완료 항목 수',
    total_incentive DECIMAL(10,2) DEFAULT 0.00 COMMENT '총 인센티브 금액',
    earned_incentive DECIMAL(10,2) DEFAULT 0.00 COMMENT '획득 인센티브',
    completion_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '완료율 %',
    status ENUM('pending', 'peer_approved', 'peer_rejected', 'owner_approved', 'owner_rejected', 'paid') DEFAULT 'pending' COMMENT '상태',
    submitted_at TIMESTAMP NULL COMMENT '제출 시간',
    peer_approved_by INT NULL COMMENT '동료 승인자 (다음 근무자)',
    peer_approved_at TIMESTAMP NULL COMMENT '동료 승인 시간',
    peer_comment TEXT COMMENT '동료 의견',
    owner_approved_by INT NULL COMMENT '점주 승인자',
    owner_approved_at TIMESTAMP NULL COMMENT '점주 승인 시간',
    owner_comment TEXT COMMENT '점주 의견',
    notes TEXT COMMENT '비고',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (template_id) REFERENCES performance_checklist_templates(id),
    FOREIGN KEY (peer_approved_by) REFERENCES users(id),
    FOREIGN KEY (owner_approved_by) REFERENCES users(id),
    UNIQUE KEY unique_user_date_template (user_id, record_date, template_id),
    INDEX idx_user_date (user_id, record_date),
    INDEX idx_status (status),
    INDEX idx_submitted (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='일별 능력제 수행 기록';

-- 4. 항목별 세부 완료 기록
CREATE TABLE IF NOT EXISTS performance_record_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    record_id INT NOT NULL,
    item_id INT NOT NULL,
    is_completed TINYINT(1) DEFAULT 0 COMMENT '완료 여부',
    completed_at TIMESTAMP NULL COMMENT '완료 시간',
    incentive_earned DECIMAL(10,2) DEFAULT 0.00 COMMENT '획득 인센티브',
    notes TEXT COMMENT '메모',
    proof_image VARCHAR(255) NULL COMMENT '증빙 사진 경로',
    peer_verified TINYINT(1) DEFAULT 0 COMMENT '동료 검증 완료',
    peer_comment VARCHAR(500) COMMENT '동료 의견',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (record_id) REFERENCES performance_records(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES performance_checklist_items(id),
    INDEX idx_record (record_id),
    INDEX idx_completed (is_completed),
    INDEX idx_peer_verified (peer_verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='항목별 완료 세부 기록';

-- 5. 월별 인센티브 집계
CREATE TABLE IF NOT EXISTS performance_monthly_summary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    year_month VARCHAR(7) NOT NULL COMMENT 'YYYY-MM',
    total_records INT DEFAULT 0 COMMENT '총 기록 수',
    total_days_worked INT DEFAULT 0 COMMENT '근무일 수',
    total_incentive DECIMAL(10,2) DEFAULT 0.00 COMMENT '총 인센티브',
    average_completion_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '평균 완료율',
    status ENUM('pending', 'calculated', 'approved', 'paid') DEFAULT 'pending' COMMENT '상태',
    calculated_at TIMESTAMP NULL COMMENT '계산 시간',
    approved_by INT NULL COMMENT '승인자',
    approved_at TIMESTAMP NULL COMMENT '승인 시간',
    paid_at TIMESTAMP NULL COMMENT '지급 시간',
    notes TEXT COMMENT '비고',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    UNIQUE KEY unique_user_month (user_id, year_month),
    INDEX idx_year_month (year_month),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='월별 인센티브 집계';
