""" Enrich QA checksheets with dynamic model_specs, sub_points, and reference_diagram_url extracted directly from official controlled standard 'docs/qa checksheet.pdf'. """ import asyncio import os import sys from decimal import Decimal sys.stdout.reconfigure(encoding='utf-8') sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))) from sqlalchemy import select, update from app.core.database import AsyncSessionLocal from app.models.qa_models import ( QaChecksheetTemplate, QaChecksheetSection, QaCheckpointItem, ) FRP_HEIGHT_SPECS = { "LEGACY": {"target": 1218, "min": 1216, "max": 1220, "uom": "mm", "tol": "±2"}, "STARLINE": {"target": 1147, "min": 1145, "max": 1149, "uom": "mm", "tol": "±2"}, "NEW_STARLINE": {"target": 1147, "min": 1145, "max": 1149, "uom": "mm", "tol": "±2"}, "SKYLINE": {"target": 1147, "min": 1145, "max": 1149, "uom": "mm", "tol": "±2"}, "LEGACY_GEN4": {"target": 1125, "min": 1123, "max": 1127, "uom": "mm", "tol": "±2"}, "PRO_AMTS": {"target": 1280, "min": 1278, "max": 1282, "uom": "mm", "tol": "±2"}, "VE31": {"target": 1280, "min": 1278, "max": 1282, "uom": "mm", "tol": "±2"}, } FRP_WIDTH_SPECS = { "LEGACY": {"target": 1980, "min": 1975, "max": 1985, "uom": "mm", "tol": "±5"}, "STARLINE": {"target": 1950, "min": 1945, "max": 1955, "uom": "mm", "tol": "±5"}, "NEW_STARLINE": {"target": 1950, "min": 1945, "max": 1955, "uom": "mm", "tol": "±5"}, "SKYLINE": {"target": 2150, "min": 2148, "max": 2152, "uom": "mm", "tol": "±2"}, "LEGACY_GEN4": {"target": 2200, "min": 2195, "max": 2205, "uom": "mm", "tol": "±5"}, "PRO_AMTS": {"target": 2200, "min": 2195, "max": 2205, "uom": "mm", "tol": "±5"}, "VE31": {"target": 2200, "min": 2195, "max": 2205, "uom": "mm", "tol": "±5"}, } FRP_DIAG_SPECS = { "LEGACY": {"target": 2250, "min": 2243, "max": 2257, "uom": "mm", "tol": "±7"}, "STARLINE": {"target": 2235, "min": 2228, "max": 2242, "uom": "mm", "tol": "±7"}, "NEW_STARLINE": {"target": 2235, "min": 2228, "max": 2242, "uom": "mm", "tol": "±7"}, "SKYLINE": {"target": 2410, "min": 2403, "max": 2417, "uom": "mm", "tol": "±7"}, "LEGACY_GEN4": {"target": 2200, "min": 2193, "max": 2207, "uom": "mm", "tol": "±7"}, "PRO_AMTS": {"target": 2420, "min": 2413, "max": 2427, "uom": "mm", "tol": "±7"}, "VE31": {"target": 2420, "min": 2413, "max": 2427, "uom": "mm", "tol": "±7"}, } DOOR_SUB_POINTS = [ {"code": "A", "label": "Point A (Hinge Top)", "spec": "8 ± 2 mm", "min": 6.0, "max": 10.0, "uom": "mm", "tool": "T.scale"}, {"code": "B", "label": "Point B (Hinge Mid)", "spec": "8 ± 2 mm", "min": 6.0, "max": 10.0, "uom": "mm", "tool": "T.scale"}, {"code": "C", "label": "Point C (Hinge Bottom)", "spec": "8 ± 2 mm", "min": 6.0, "max": 10.0, "uom": "mm", "tool": "T.scale"}, {"code": "D", "label": "Point D (Lock Top)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "E", "label": "Point E (Lock Mid)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "F", "label": "Point F (Lock Bottom)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "G", "label": "Point G (Bottom Rear)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "H", "label": "Point H (Bottom Front)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "I", "label": "Point I (Header Top)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, {"code": "J", "label": "Point J (Header Outer)", "spec": "6 ± 2 mm", "min": 4.0, "max": 8.0, "uom": "mm", "tool": "T.scale"}, ] DOC_CONTROL_NUMBERS = { "QS_OUTER": {"format_no": "AA/QA/IPS-OP/IR-006", "revision": "00"}, "QS_INNER": {"format_no": "AA/QA/IPS-IP/IR-007", "revision": "00"}, "QS_SURFACER": {"format_no": "AA/QA/IPS-S/IR-008", "revision": "00"}, "QS_TOP_COAT": {"format_no": "AA/QA/IPS-TC/IR-009", "revision": "00"}, "QS_TRIM": {"format_no": "AA/QA/IPS-TR/IR-010", "revision": "06"}, "QS_FVI": {"format_no": "AA/QA/FVI/IR-011", "revision": "00"}, "QS_PDI": {"format_no": "AA/QA/PDI/IR-012", "revision": "00"}, "QS_FAPS": {"format_no": "AA/QA/FAPS/CS-005", "revision": "02"}, "QS_SHORTAGE": {"format_no": "AA/QA/MSDO/OR-003", "revision": "00"}, "QS_VECV": {"format_no": "AA/QA/VECV/FM-014", "revision": "06"}, "QS_CTC_SUBASSY": {"format_no": "AA/QA/CTC/04", "revision": "05"}, } async def main(): async with AsyncSessionLocal() as db: print("1. Updating Official QA Document Control Numbers...") for code, meta in DOC_CONTROL_NUMBERS.items(): tpl = (await db.execute(select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == code))).scalar_one_or_none() if tpl: tpl.format_no = meta["format_no"] tpl.revision = meta["revision"] print(f" ✓ {code} -> {meta['format_no']} (Rev {meta['revision']})") print("\n2. Enriching Front FRP Dimensional Checkpoints in QS_OUTER...") outer_tpl = (await db.execute(select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == "QS_OUTER"))).scalar_one_or_none() if outer_tpl: items = (await db.execute( select(QaCheckpointItem).join(QaChecksheetSection) .where(QaChecksheetSection.template_id == outer_tpl.template_id) )).scalars().all() for it in items: desc = it.checkpoint_desc.lower() if "height" in desc and "middle" not in desc: it.model_specs = FRP_HEIGHT_SPECS it.reference_diagram_url = "/uploads/qa_diagrams/front_frp_aperture.svg" it.checkpoint_desc = "Front FRP Aperture Center Height 'H'" print(f" ✓ Added model_specs & diagram to Front FRP Height (Item #{it.item_no})") elif "width top" in desc: it.model_specs = FRP_WIDTH_SPECS it.reference_diagram_url = "/uploads/qa_diagrams/front_frp_aperture.svg" it.checkpoint_desc = "Front FRP Aperture Width Top" print(f" ✓ Added model_specs & diagram to Front FRP Width Top (Item #{it.item_no})") elif "diagonal" in desc and ("c-d" in desc or "c" in desc): it.model_specs = FRP_DIAG_SPECS it.reference_diagram_url = "/uploads/qa_diagrams/front_frp_aperture.svg" it.checkpoint_desc = "Front FRP Aperture Diagonals C & D" print(f" ✓ Added model_specs & diagram to Front FRP Diagonals (Item #{it.item_no})") elif "gap b/w driver door" in desc or "ctc matrix" in desc: it.sub_points = DOOR_SUB_POINTS it.reference_diagram_url = "/uploads/qa_diagrams/ctc_door_aperture.svg" print(f" ✓ Added 10 sub_points (A-J) & diagram to Door Clearance (Item #{it.item_no})") print("\n3. Enriching Door Aperture Clearances in QS_CTC_SUBASSY...") ctc_tpl = (await db.execute(select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == "QS_CTC_SUBASSY"))).scalar_one_or_none() if ctc_tpl: ctc_items = (await db.execute( select(QaCheckpointItem).join(QaChecksheetSection) .where(QaChecksheetSection.template_id == ctc_tpl.template_id) )).scalars().all() for it in ctc_items: it.reference_diagram_url = "/uploads/qa_diagrams/ctc_door_aperture.svg" print(f" ✓ Added CTC Door Aperture diagram to all {len(ctc_items)} CTC checkpoints") print("\n4. Enriching FAPS Matrix in QS_FAPS...") faps_tpl = (await db.execute(select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == "QS_FAPS"))).scalar_one_or_none() if faps_tpl: faps_items = (await db.execute( select(QaCheckpointItem).join(QaChecksheetSection) .where(QaChecksheetSection.template_id == faps_tpl.template_id) )).scalars().all() for it in faps_items: it.reference_diagram_url = "/uploads/qa_diagrams/faps_control_panel.svg" print(f" ✓ Added Safex Control Panel diagram to all {len(faps_items)} FAPS checkpoints") print("\n5. Enriching Seat Pitch & Clearances in QS_PDI...") pdi_tpl = (await db.execute(select(QaChecksheetTemplate).where(QaChecksheetTemplate.template_code == "QS_PDI"))).scalar_one_or_none() if pdi_tpl: pdi_items = (await db.execute( select(QaCheckpointItem).join(QaChecksheetSection) .where(QaChecksheetSection.template_id == pdi_tpl.template_id) )).scalars().all() for it in pdi_items: if "seat pitch" in it.checkpoint_desc.lower(): it.model_specs = { "SCHOOL": {"target": 680, "min": 680, "max": 750, "uom": "mm", "tol": "Min 680mm"}, "STAFF": {"target": 815, "min": 815, "max": 900, "uom": "mm", "tol": "Min 815mm (Pushback)"}, "PUSHBACK": {"target": 815, "min": 815, "max": 900, "uom": "mm", "tol": "Min 815mm"}, } print(f" ✓ Added Body Type model_specs to Seat Pitch (Item #{it.item_no})") await db.commit() print("\n=======================================================") print("ALL DYNAMIC QA CHECKPOINTS SUCCESSFULLY ENRICHED!") print("=======================================================") if __name__ == '__main__': asyncio.run(main())