import asyncio
import os
import sys
import uuid
import xlrd

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,
    QaStationTemplateMap,
)
from app.models.rm_models import StationMaster

EXCEL_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'docs', 'Inprocess checksheet  April. - 2026 - 2000bus.xls'))

async def seed_field_issues():
    async with AsyncSessionLocal() as db:
        # Check if QS_FIELD_ISSUES exists
        existing = (await db.execute(
            select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == 'QS_FIELD_ISSUES')
        )).scalar_one_or_none()
        
        # Get PDI or FVI station
        pdi_station = (await db.execute(
            select(StationMaster).where(StationMaster.station_code.ilike('%PDI%'))
        )).scalars().first()
        
        if not pdi_station:
            pdi_station = (await db.execute(
                select(StationMaster).where(StationMaster.station_code.ilike('%FVI%'))
            )).scalars().first()
            
        station_id = pdi_station.station_id if pdi_station else None
        
        if not existing:
            template = QaChecksheetTemplate(
                template_code='QS_FIELD_ISSUES',
                template_name='Field Complaints & Quality Gate Checksheet',
                format_no='AA/QA/FC/FM-013',
                revision='00',
                description='Starline / Skyline / Legacy Bus Complain Points & Field Defect Audit (Page 3 of QA Standard)',
                station_id=station_id,
                is_active=True,
            )
            db.add(template)
            await db.flush()
            template_id = template.template_id
            print(f"Created template QS_FIELD_ISSUES: {template_id}")
        else:
            template_id = existing.template_id
            existing.format_no = 'AA/QA/FC/FM-013'
            existing.revision = '00'
            existing.template_name = 'Field Complaints & Quality Gate Checksheet'
            print(f"Found existing QS_FIELD_ISSUES: {template_id}")

        # Check mapping
        if station_id:
            m_exist = (await db.execute(
                select(QaStationTemplateMap).where(
                    QaStationTemplateMap.template_id == template_id,
                    QaStationTemplateMap.station_id == station_id
                )
            )).first()
            if not m_exist:
                db.add(QaStationTemplateMap(
                    template_id=template_id,
                    station_id=station_id,
                ))
                print(f"Mapped QS_FIELD_ISSUES to station {pdi_station.station_name}")

        # Read excel sheet 'Field issues'
        wb = xlrd.open_workbook(EXCEL_PATH)
        s = wb.sheet_by_name('Field issues')
        
        # Create section
        sec = (await db.execute(
            select(QaChecksheetSection).where(
                QaChecksheetSection.template_id == template_id,
                QaChecksheetSection.section_no == 1
            )
        )).scalar_one_or_none()
        
        if not sec:
            sec = QaChecksheetSection(
                template_id=template_id,
                section_no=1,
                section_name='Starline / Skyline / Legacy Bus Field Complain Points',
                sort_order=1
            )
            db.add(sec)
            await db.flush()
            print(f"Created section 1 for QS_FIELD_ISSUES: {sec.section_id}")
        
        # Insert 37 items from row 6 to 42
        item_no = 1
        for r in range(6, s.nrows):
            val_sn = str(s.cell_value(r, 0)).strip()
            area_desc = str(s.cell_value(r, 1)).strip()
            checkpoint_desc = str(s.cell_value(r, 2)).strip()
            
            if not checkpoint_desc:
                continue
                
            full_desc = f"{area_desc}: {checkpoint_desc}" if area_desc else checkpoint_desc
            
            # Check if item exists
            item_exist = (await db.execute(
                select(QaCheckpointItem).where(
                    QaCheckpointItem.section_id == sec.section_id,
                    QaCheckpointItem.item_no == item_no
                )
            )).scalar_one_or_none()
            
            if not item_exist:
                db.add(QaCheckpointItem(
                    section_id=sec.section_id,
                    item_no=item_no,
                    checkpoint_desc=full_desc,
                    checkpoint_class='CRITICAL' if any(w in checkpoint_desc.lower() for w in ['crack', 'leak', 'fire', 'broken', 'drumming']) else 'MAJOR',
                    verification_method='VISUAL',
                    requires_measurement=False,
                    standard_specification='Free of defects / No complaints',
                    frequency='1.0',
                    sort_order=item_no,
                ))
            else:
                item_exist.checkpoint_desc = full_desc
            item_no += 1
            
        await db.commit()
        print(f"Successfully seeded {item_no - 1} items for QS_FIELD_ISSUES (AA/QA/FC/FM-013)!")

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