# Source: Existing pattern from models/material.py, models/hcp_profile.py [VERIFIED: codebase]

class Skill(Base, TimestampMixin):
    """Training skill with lifecycle: draft -> review -> published -> archived + failed."""
    __tablename__ = "skills"

    # From YAML frontmatter
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[str] = mapped_column(Text, default="")
    product: Mapped[str] = mapped_column(String(255), default="", index=True)
    therapeutic_area: Mapped[str] = mapped_column(String(255), default="")
    compatibility: Mapped[str] = mapped_column(String(255), default="")  # e.g. "Requires python3"
    metadata_json: Mapped[str] = mapped_column(Text, default="{}")  # Extended frontmatter fields as JSON
    tags: Mapped[str] = mapped_column(Text, default="")  # comma-separated

    # Markdown body (Coaching Protocol content)
    content: Mapped[str] = mapped_column(Text, default="")  # Full Markdown body of SKILL.md

    # Lifecycle
    status: Mapped[str] = mapped_column(String(20), default="draft")  # draft/review/published/archived/failed
    current_version: Mapped[int] = mapped_column(default=1)
    created_by: Mapped[str] = mapped_column(String(36), ForeignKey("users.id"), nullable=False)

    # Quality evaluation
    structure_check_passed: Mapped[bool | None] = mapped_column(default=None)
    structure_check_score: Mapped[int | None] = mapped_column(default=None)
    quality_score: Mapped[int | None] = mapped_column(default=None)
    quality_verdict: Mapped[str | None] = mapped_column(String(20), default=None)

    # Conversion tracking
    conversion_status: Mapped[str | None] = mapped_column(String(20), default=None)  # pending/processing/completed/failed

    # Relationships
    versions = relationship("SkillVersion", back_populates="skill", order_by="SkillVersion.version_number.desc()")
    resources = relationship("SkillResource", back_populates="skill")


class SkillVersion(Base, TimestampMixin):
    """Versioned snapshot of a skill's content."""
    __tablename__ = "skill_versions"

    skill_id: Mapped[str] = mapped_column(String(36), ForeignKey("skills.id"), nullable=False, index=True)
    version_number: Mapped[int] = mapped_column(nullable=False)
    content: Mapped[str] = mapped_column(Text, default="")  # Markdown body snapshot
    metadata_json: Mapped[str] = mapped_column(Text, default="{}")  # Frontmatter snapshot
    change_notes: Mapped[str] = mapped_column(Text, default="")
    is_active: Mapped[bool] = mapped_column(default=True)

    skill = relationship("Skill", back_populates="versions")


class SkillResource(Base, TimestampMixin):
    """A resource file belonging to a Skill package (references/, scripts/, assets/)."""
    __tablename__ = "skill_resources"

    skill_id: Mapped[str] = mapped_column(String(36), ForeignKey("skills.id"), nullable=False, index=True)
    resource_type: Mapped[str] = mapped_column(String(20), nullable=False)  # reference/script/asset
    filename: Mapped[str] = mapped_column(String(255), nullable=False)
    path: Mapped[str] = mapped_column(String(500), nullable=False)  # Storage path
    content_type: Mapped[str] = mapped_column(String(100), default="")
    file_size: Mapped[int] = mapped_column(default=0)
    extraction_status: Mapped[str | None] = mapped_column(String(20), default=None)  # For reference files: pending/processing/completed/failed

    skill = relationship("Skill", back_populates="resources")


# Scenario model extension (existing table, add FK):
# class Scenario(Base, TimestampMixin):
#     ...existing fields...
#     skill_id: Mapped[str | None] = mapped_column(String(36), ForeignKey("skills.id"), nullable=True)

# Source: Existing patterns from material_service.py + scoring_engine.py [VERIFIED: codebase]

async def extract_sop_from_skill_resources(
    db: AsyncSession,
    skill_id: str,
) -> str:
    """Extract Coaching Protocol content from Skill reference materials.

    Pipeline (aligned with coaching-skill-creator 5-phase flow):
    1. Load reference files from SkillResource (type='reference')
    2. Extract text → convert to Markdown (preserving structure)
    3. Semantic chunking by heading/section boundaries + size limit
    4. Per-chunk extraction of key points, knowledge, assessment criteria
    5. Merge + deduplicate → generate complete Coaching Protocol (Markdown body)
    """
    storage = get_storage()
    skill = await get_skill(db, skill_id)

    # 1. Collect all reference resources
    references = [r for r in skill.resources if r.resource_type == "reference"]
    all_markdown = ""
    for ref in references:
        content = await storage.read(ref.path)
        markdown = convert_to_markdown(content, ref.content_type)
        all_markdown += markdown + "\n\n"

    # 2. Semantic chunking (configurable chunk size)
    chunk_limit = await get_admin_config("skill_chunk_token_limit", default=80000)
    chunks = semantic_chunk(all_markdown, max_tokens=chunk_limit)

    # 3. Per-chunk extraction + merge
    extracted_parts = []
    for chunk in chunks:
        part = await call_sop_extraction(chunk)
        extracted_parts.append(part)

    # 4. Merge and deduplicate into final Coaching Protocol
    coaching_protocol = merge_extractions(extracted_parts)
    return coaching_protocol  # Markdown body for skill.content

# Source: Existing pattern from agent_sync_service.py build_agent_instructions() [VERIFIED: codebase]
# + SkillManager pattern from reference code [VERIFIED: skill_manager.py]

async def build_skill_augmented_instructions(
    db: AsyncSession,
    profile: HcpProfile,
    scenario_id: str | None = None,
    template: str | None = None,
) -> str:
    """Build agent instructions with Skill SOP content from associated Scenario."""
    base_instructions = build_agent_instructions(profile.to_prompt_dict(), template)

    if not scenario_id:
        return base_instructions

    # Load Scenario → Skill association
    scenario = await get_scenario(db, scenario_id)
    if not scenario or not scenario.skill_id:
        return base_instructions

    skill = await get_skill(db, scenario.skill_id)
    if not skill or skill.status != "published":
        return base_instructions

    # Use SkillManager pattern: compose_instructions() merges Skill content
    # skill.content contains the Markdown body (Coaching Protocol)
    skill_block = f"## Training Skill: {skill.name}\n\n{skill.content}"
    return f"{base_instructions}\n\n# SOP Training Instructions\n{skill_block}"

# Backend: Python zipfile (stdlib) for creation
import zipfile
import io
import yaml

async def export_skill_package(db: AsyncSession, skill_id: str) -> bytes:
    """Export a skill as a ZIP package (agentskills.io compatible)."""
    skill = await get_skill(db, skill_id)
    buffer = io.BytesIO()
    with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
        # Reconstruct SKILL.md from DB fields
        frontmatter = {
            "name": skill.name,
            "description": skill.description,
            "compatibility": skill.compatibility,
            **(json.loads(skill.metadata_json) if skill.metadata_json else {}),
        }
        skill_md = f"---\n{yaml.dump(frontmatter, allow_unicode=True)}---\n\n{skill.content}"
        zf.writestr("SKILL.md", skill_md)

        # Resources (references/, scripts/, assets/)
        for resource in skill.resources:
            content = await storage.read(resource.path)
            zf.writestr(f"{resource.resource_type}s/{resource.filename}", content)
    return buffer.getvalue()

# Source: Extending existing pattern from material upload [VERIFIED: material_service.py, storage/local.py]

def extract_text_from_pdf(content: bytes) -> str:
    """Extract text from PDF bytes using pdfplumber (better table support)."""
    import pdfplumber
    import io
    pages = []
    with pdfplumber.open(io.BytesIO(content)) as pdf:
        for page in pdf.pages:
            text = page.extract_text()
            if text:
                pages.append(text)
    return "\n\n".join(pages)


def extract_text_from_docx(content: bytes) -> str:
    """Extract text from DOCX bytes using python-docx."""
    from docx import Document
    import io
    doc = Document(io.BytesIO(content))
    paragraphs = [p.text for p in doc.paragraphs if p.text.strip()]
    return "\n".join(paragraphs)


def extract_text_from_pptx(content: bytes) -> str:
    """Extract text from PPTX bytes using python-pptx."""
    from pptx import Presentation
    import io
    prs = Presentation(io.BytesIO(content))
    slides_text = []
    for slide in prs.slides:
        texts = []
        for shape in slide.shapes:
            if hasattr(shape, "text") and shape.text.strip():
                texts.append(shape.text)
        if texts:
            slides_text.append("\n".join(texts))
    return "\n\n---\n\n".join(slides_text)


def extract_text(content: bytes, content_type: str) -> str:
    """Dispatch text extraction based on content type."""
    if "pdf" in content_type:
        return extract_text_from_pdf(content)
    elif "wordprocessing" in content_type or content_type.endswith(".docx"):
        return extract_text_from_docx(content)
    elif "presentation" in content_type or content_type.endswith(".pptx"):
        return extract_text_from_pptx(content)
    return ""

# Source: Extending pattern from prompt_builder.py [VERIFIED: codebase]

SOP_EXTRACTION_PROMPT = """You are an expert medical training content designer. 
Analyze the following training materials and extract a structured Standard Operating Procedure (SOP) for Medical Representative (MR) training.

## Input Materials
{document_text}

## Required Output Format
Return ONLY valid JSON in this exact format:
{{
  "sop_steps": [
    {{
      "step_number": 1,
      "title": "Step title",
      "description": "Detailed description of what MR should do",
      "key_points": ["point 1", "point 2"],
      "expected_duration_minutes": 5
    }}
  ],
  "assessment_criteria": [
    {{
      "criterion": "Assessment item description",
      "weight": 20,
      "passing_score": 70
    }}
  ],
  "key_knowledge_points": [
    {{
      "topic": "Knowledge topic",
      "content": "Key information the MR must know",
      "importance": "high"
    }}
  ],
  "summary": "Brief 2-3 sentence summary of the skill"
}}

Rules:
1. Extract ALL actionable training steps from the materials
2. Assessment criteria weights must total 100
3. Knowledge points should cover product, mechanism, clinical data, and safety
4. Use the same language as the source materials (Chinese or English)
5. Be specific and actionable - avoid generic instructions"""

# Source: Following exact pattern from api/materials.py [VERIFIED: codebase]

router = APIRouter(prefix="/skills", tags=["skills"])

@router.post("", response_model=SkillOut, status_code=201)
async def create_skill(
    data: SkillCreate,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(require_role("admin")),
):
    """Create a new skill. Admin only."""
    skill = await skill_service.create_skill(db, data, user.id)
    return skill


# Static routes BEFORE parameterized /{id} routes (Gotcha #3)
@router.get("/published", response_model=PaginatedResponse[SkillListOut])
async def list_published_skills(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    search: str | None = Query(None),
    db: AsyncSession = Depends(get_db),
    _user: User = Depends(require_role("admin")),
):
    """List published skills for Skill Hub (Admin only — MR accesses Skills via Scenarios)."""
    items, total = await skill_service.get_published_skills(db, page=page, page_size=page_size, search=search)
    return PaginatedResponse.create(items=[SkillListOut.model_validate(i) for i in items], total=total, page=page, page_size=page_size)


@router.get("/{skill_id}", response_model=SkillOut)
async def get_skill(skill_id: str, db: AsyncSession = Depends(get_db), _user: User = Depends(require_role("admin"))):
    """Get a skill with full details. Admin only."""
    return await skill_service.get_skill(db, skill_id)

# skill_validation_service.py — structure validation

@dataclass
class StructureCheckResult:
    passed: bool
    score: int  # 0-100
    issues: list[ValidationIssue]

@dataclass
class ValidationIssue:
    severity: str  # "error" | "warning" | "info"
    dimension: str  # 检查维度
    message: str
    suggestion: str

async def check_skill_structure(skill: Skill) -> StructureCheckResult:
    """Layer 1: 纯规则结构检查，无 AI 调用。"""
    issues = []

    # 1. SOP 完整性检查
    sop = skill.content  # Markdown body (Coaching Protocol)
    sop_steps = sop.get("sop_steps", [])
    if len(sop_steps) < 3:
        issues.append(ValidationIssue(
            severity="error", dimension="sop_completeness",
            message="SOP 步骤少于 3 步，培训流程不完整",
            suggestion="补充缺失的对话阶段（开场、需求挖掘、产品介绍、异议处理、收尾）"
        ))

    REQUIRED_STAGES = ["开场/Opening", "产品介绍/Product", "收尾/Closing"]
    # 检查关键阶段是否覆盖（模糊匹配标题）
    step_titles = " ".join([s.get("title", "") for s in sop_steps])
    for stage in REQUIRED_STAGES:
        keywords = stage.split("/")
        if not any(kw.lower() in step_titles.lower() for kw in keywords):
            issues.append(ValidationIssue(
                severity="warning", dimension="sop_completeness",
                message=f"缺少 '{stage}' 相关阶段",
                suggestion=f"建议添加 {stage} 环节"
            ))

    # 2. 考核项检查
    criteria = json.loads(skill.assessment_criteria)
    if len(criteria) < 2:
        issues.append(ValidationIssue(
            severity="error", dimension="assessment_coverage",
            message="考核项少于 2 项，无法多维度评估",
            suggestion="至少需要 2 个考核维度"
        ))
    total_weight = sum(c.get("weight", 0) for c in criteria)
    if criteria and abs(total_weight - 100) > 1:
        issues.append(ValidationIssue(
            severity="error", dimension="assessment_coverage",
            message=f"考核权重总和为 {total_weight}，应为 100",
            suggestion="调整各考核项权重使总和为 100"
        ))

    # 3. 知识点检查
    knowledge = json.loads(skill.key_knowledge_points)
    if len(knowledge) < 1:
        issues.append(ValidationIssue(
            severity="warning", dimension="knowledge_accuracy",
            message="无关键知识点，MR 无法获得产品知识培训",
            suggestion="补充产品、机制、临床数据等知识点"
        ))

    # 4. 必填字段检查
    if not skill.name or len(skill.name.strip()) < 2:
        issues.append(ValidationIssue(
            severity="error", dimension="basic_info",
            message="Skill 名称为空或过短", suggestion="填写有意义的 Skill 名称"
        ))
    if not skill.description or len(skill.description.strip()) < 10:
        issues.append(ValidationIssue(
            severity="warning", dimension="basic_info",
            message="Skill 描述过短", suggestion="补充详细描述，便于 Skill Hub 展示"
        ))

    error_count = sum(1 for i in issues if i.severity == "error")
    passed = error_count == 0
    score = max(0, 100 - error_count * 25 - sum(1 for i in issues if i.severity == "warning") * 10)

    return StructureCheckResult(passed=passed, score=score, issues=issues)

# skill_evaluation_service.py — AI quality assessment

SKILL_EVALUATION_PROMPT = """你是一位资深医药培训设计专家。请对以下 AI Coach 培训 Skill 进行全面质量评估。

## Skill 信息
名称：{skill_name}
描述：{skill_description}
产品领域：{product}

## SOP 内容
{sop_content}

## 考核标准
{assessment_criteria}

## 关键知识点
{key_knowledge_points}

## 源培训材料摘要（如有）
{source_material_summary}

## 评估要求
请从以下 6 个维度对该 Skill 进行评分（每项 0-100 分）并给出详细改进建议：

1. **SOP完整性** (sop_completeness)：是否覆盖完整的 MR-HCP 对话流程？是否有遗漏的关键阶段？
2. **考核覆盖度** (assessment_coverage)：考核项是否全面覆盖了 SOP 中的关键技能点？考核权重分配是否合理？
3. **知识准确性** (knowledge_accuracy)：知识点是否与源材料一致？是否有错误或过时信息？（无源材料时评估内容的专业性）
4. **难度合理性** (difficulty_calibration)：SOP 复杂度是否适合目标学员？HCP 互动难度是否循序渐进？
5. **对话逻辑性** (conversation_logic)：SOP 步骤之间是否逻辑连贯？是否有矛盾或死循环路径？
6. **可执行性** (executability)：HCP Agent 能否根据此 SOP 驱动有效的模拟对话？指令是否足够具体？

返回 JSON 格式：
{{
  "overall_score": 85,
  "overall_verdict": "PASS",  // PASS (>=70) / NEEDS_REVIEW (50-69) / FAIL (<50)
  "dimensions": [
    {{
      "name": "sop_completeness",
      "score": 90,
      "verdict": "PASS",
      "strengths": ["覆盖了完整的 F2F 拜访流程"],
      "improvements": ["建议增加异议处理的具体示例"],
      "critical_issues": []
    }}
  ],
  "summary": "该 Skill 整体质量良好...",
  "top_3_improvements": ["...", "...", "..."]
}}"""

@dataclass
class SkillEvaluationResult:
    overall_score: int  # 0-100
    overall_verdict: str  # PASS / NEEDS_REVIEW / FAIL
    dimensions: list[DimensionScore]
    summary: str
    top_improvements: list[str]

async def evaluate_skill_quality(
    db: AsyncSession,
    skill_id: str,
    include_source_materials: bool = True,
) -> SkillEvaluationResult:
    """Layer 2: AI 驱动的多维度质量评估。"""
    skill = await get_skill(db, skill_id)

    # 获取源材料摘要（如果有关联材料）
    source_summary = ""
    if include_source_materials and skill.materials:
        source_summary = await _get_material_summary(db, skill.materials)

    prompt = SKILL_EVALUATION_PROMPT.format(
        skill_name=skill.name,
        skill_description=skill.description,
        product=skill.product,
        sop_content=skill.sop_content,
        assessment_criteria=skill.assessment_criteria,
        key_knowledge_points=skill.key_knowledge_points,
        source_material_summary=source_summary or "无源材料",
    )

    # 使用 Azure OpenAI JSON mode
    result = await call_azure_openai(prompt, response_format={"type": "json_object"})
    return SkillEvaluationResult(**result)

# skills.py router extension
@router.post("/{skill_id}/check-structure", response_model=StructureCheckOut)
async def check_skill_structure(skill_id: str, ...):
    """Layer 1: Instant structure validation."""

@router.post("/{skill_id}/evaluate-quality", response_model=QualityEvaluationOut)
async def evaluate_skill_quality(skill_id: str, ...):
    """Layer 2: AI quality assessment (async, takes 10-30s)."""

@router.get("/{skill_id}/evaluation", response_model=SkillEvaluationSummaryOut)
async def get_skill_evaluation(skill_id: str, ...):
    """Get latest evaluation results (Layer 1 + Layer 2)."""

class SkillFeedback(Base, TimestampMixin):
    """客户对 Skill 的反馈意见。"""
    __tablename__ = "skill_feedbacks"

    skill_id: Mapped[str] = mapped_column(String(36), ForeignKey("skills.id"), nullable=False, index=True)
    reviewer_name: Mapped[str] = mapped_column(String(255), nullable=False)  # 反馈人姓名
    reviewer_role: Mapped[str] = mapped_column(String(100), default="")  # 角色（培训经理/医学部等）
    dimension: Mapped[str] = mapped_column(String(50), default="general")  # 反馈维度
    content: Mapped[str] = mapped_column(Text, nullable=False)  # 反馈内容
    status: Mapped[str] = mapped_column(String(20), default="open")  # open/addressed/dismissed

    skill = relationship("Skill", back_populates="feedbacks")

@router.post("/{skill_id}/preview-link", response_model=PreviewLinkOut)
async def generate_preview_link(skill_id: str, expires_days: int = 7, ...):
    """生成 Skill 预览链接（Admin only）。"""

@router.get("/preview/{skill_id}", response_model=SkillPreviewOut)
async def get_skill_preview(skill_id: str, token: str = Query(...), ...):
    """公开预览页面（无需登录，token 验证）。"""

@router.post("/preview/{skill_id}/feedback", response_model=FeedbackOut)
async def submit_preview_feedback(skill_id: str, token: str, data: FeedbackCreate, ...):
    """提交预览反馈（无需登录，token 验证）。"""

@router.get("/{skill_id}/feedbacks", response_model=list[FeedbackOut])
async def list_skill_feedbacks(skill_id: str, ...):
    """查看 Skill 的所有反馈（Admin only）。"""