import asyncio
import os
import sys

# Ensure UTF-8 output
if sys.platform == 'win32':
    sys.stdout.reconfigure(encoding='utf-8')

sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import select
from app.core.database import AsyncSessionLocal
from app.models.qa_models import (
    QaChecksheetTemplate,
    QaChecksheetSection,
    QaCheckpointItem,
)

async def export_all():
    async with AsyncSessionLocal() as db:
        templates = (await db.execute(
            select(QaChecksheetTemplate).order_by(QaChecksheetTemplate.template_code)
        )).scalars().all()
        
        lines = []
        lines.append('# Complete QA Checksheet Items (Extracted Master Repository)')
        lines.append('**Source Documents:** `docs/qa checksheet.pdf` (signed ISO plant standard) and `docs/Inprocess checksheet  April. - 2026 - 2000bus.xls`.\n')
        
        total_items = 0
        total_sections = 0
        
        for t in templates:
            lines.append(f"## {t.template_name} (`{t.template_code}`)")
            lines.append(f"- **Format No:** `{t.format_no or 'N/A'}` | **Revision:** `{t.revision or 'N/A'}`")
            if t.description:
                lines.append(f"- **Description:** {t.description}")
            lines.append("")
            
            sections = (await db.execute(
                select(QaChecksheetSection)
                .where(QaChecksheetSection.template_id == t.template_id)
                .order_by(QaChecksheetSection.section_no)
            )).scalars().all()
            
            total_sections += len(sections)
            
            for s in sections:
                items = (await db.execute(
                    select(QaCheckpointItem)
                    .where(QaCheckpointItem.section_id == s.section_id)
                    .order_by(QaCheckpointItem.item_no)
                )).scalars().all()
                
                total_items += len(items)
                lines.append(f"### Section {s.section_no}: {s.section_name} ({len(items)} items)")
                
                if not items:
                    lines.append("*No checkpoints in this section.*\n")
                    continue
                
                lines.append("| # | Checkpoint Description | Class | Method | Standard Spec / Tolerance | Freq |")
                lines.append("| :--- | :--- | :--- | :--- | :--- | :--- |")
                
                for it in items:
                    spec_parts = []
                    if it.standard_specification:
                        spec_parts.append(it.standard_specification)
                    if it.requires_measurement and (it.min_value is not None or it.max_value is not None):
                        spec_parts.append(f"[{it.min_value or '—'} to {it.max_value or '—'} {it.unit_of_measure or ''}]")
                    if it.model_specs:
                        spec_parts.append(f"Dynamic ({len(it.model_specs)} models)")
                    if it.sub_points:
                        codes = ", ".join(sp.get('code', '') for sp in it.sub_points)
                        spec_parts.append(f"Multi-point ({len(it.sub_points)} pts: {codes})")
                    
                    spec_text = " • ".join(spec_parts) if spec_parts else "—"
                    desc = it.checkpoint_desc.replace("\n", " ")
                    lines.append(f"| {it.item_no} | {desc} | {it.checkpoint_class} | {it.verification_method or 'Visual'} | {spec_text} | {it.frequency or '1.0'} |")
                
                lines.append("")
            lines.append("---\n")
            
        export_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'docs', 'EXTRACTED_QA_CHECKLIST_ITEMS.md'))
        with open(export_path, 'w', encoding='utf-8') as f:
            f.write("\n".join(lines))
            
        print(f"Exported {len(templates)} templates, {total_sections} sections, and {total_items} items.")
        print(f"File saved to: {export_path}")

if __name__ == '__main__':
    asyncio.run(export_all())