"""
Standard clinical catalog seeder — idempotent DB insert.

Mirrors munalmedical-fe `DEFAULT_CLINICAL_TESTS`. Covers Biochemistry
(LFT incl. SGPT/ALT, SGOT/AST, ALP, GGT, protein, albumin, globulin;
RFT/KFT incl. BUN, creatinine, uric acid; electrolytes Na/K/Cl/Ca/Mg/P;
lipid profile; cardiac Troponin, CK-MB; Amylase, Lipase; FBS, PPBS,
HbA1c, OGTT; iron studies; Vit D3/B12), Hematology (CBC incl. TLC,
DLC, RDW, MPV; ESR; PBS; Retic; Blood group; PT/INR; aPTT; BT/CT;
D-dimer; Fibrinogen), Clinical Pathology (urine RE, stool RE, UPT),
Microbiology (cultures, Gram, AFB, fungal, KOH), Serology (HIV, HBsAg,
HCV, Dengue, Widal, CRP, RA, ASO), Molecular (PCR, GeneXpert),
and X-Ray body-part panels.

Param tuple: (name, unit, gender, low, high, label). gender is
"male"/"female"/"other"/None. Rows sharing a param name seed multiple
ranges (e.g. HbA1c slabs, male/female Hb).

Usage:
    cd munalmedical-be
    .venv/bin/python scripts/seed_standard_catalog.py
    .venv/bin/python scripts/seed_standard_catalog.py --delete
"""

import asyncio
import sys
from decimal import Decimal

sys.path.insert(0, ".")

from app.database import AsyncSessionLocal  # noqa: E402
from app.models.test import TestResultType  # noqa: E402
from app.repositories.lab_repository import LabRepository  # noqa: E402
from app.repositories.test_repository import TestCatalogRepository  # noqa: E402
from app.schemas.test import (  # noqa: E402
    TestCategoryCreate,
    TestCreate,
    TestParameterCreate,
    TestParameterRangeCreate,
    TestParameterUpdate,
)

# category -> list of (code, name, price, result_type, [(param, unit, gender, low, high, label)])
CATALOG: dict[str, list[tuple]] = {
    "Biochemistry": [
        (
            "LFT",
            "Liver Function Test (LFT)",
            950,
            "composite",
            [
                ("SGPT (ALT)", "U/L", None, 7, 56, None),
                ("SGOT (AST)", "U/L", None, 10, 40, None),
                ("Alkaline Phosphatase (ALP)", "U/L", None, 44, 147, None),
                ("Gamma-GT (GGT)", "U/L", None, 9, 48, None),
                ("Total Bilirubin", "mg/dL", None, 0.1, 1.2, None),
                ("Direct Bilirubin", "mg/dL", None, 0, 0.3, None),
                ("Total Protein", "g/dL", None, 6.0, 8.3, None),
                ("Serum Albumin", "g/dL", None, 3.5, 5.5, None),
                ("Globulin", "g/dL", None, 2.3, 3.5, None),
                ("A/G Ratio", "ratio", None, 1.0, 2.0, None),
            ],
        ),
        ("SGPT", "SGPT (ALT)", 300, "numeric", [("SGPT/ALT", "U/L", None, 7, 56, None)]),
        ("SGOT", "SGOT (AST)", 300, "numeric", [("SGOT/AST", "U/L", None, 10, 40, None)]),
        (
            "KFT",
            "Kidney Function Test (KFT)",
            750,
            "composite",
            [
                ("Blood Urea Nitrogen (BUN)", "mg/dL", None, 7, 20, None),
                ("Blood Urea", "mg/dL", None, 15, 45, None),
                ("Serum Creatinine", "mg/dL", None, 0.6, 1.3, None),
                ("Serum Uric Acid", "mg/dL", None, 3.4, 7.0, None),
                ("Sodium (Na+)", "mmol/L", None, 136, 145, None),
                ("Potassium (K+)", "mmol/L", None, 3.5, 5.1, None),
            ],
        ),
        (
            "ELECT",
            "Serum Electrolytes",
            400,
            "composite",
            [
                ("Sodium (Na+)", "mmol/L", None, 136, 145, None),
                ("Potassium (K+)", "mmol/L", None, 3.5, 5.1, None),
                ("Chloride (Cl-)", "mmol/L", None, 98, 107, None),
                ("Calcium (Total)", "mg/dL", None, 8.5, 10.5, None),
                ("Magnesium", "mg/dL", None, 1.7, 2.2, None),
                ("Phosphorus", "mg/dL", None, 2.5, 4.5, None),
            ],
        ),
        (
            "LIPID",
            "Lipid Profile Panel",
            800,
            "composite",
            [
                ("Total Cholesterol", "mg/dL", None, None, 200, "Desirable"),
                ("HDL Cholesterol", "mg/dL", None, 40, None, None),
                ("LDL Cholesterol", "mg/dL", None, None, 100, "Optimal"),
                ("VLDL Cholesterol", "mg/dL", None, 2, 30, None),
                ("Triglycerides", "mg/dL", None, None, 150, "Normal"),
                ("TC/HDL Ratio", "ratio", None, None, 5, "Desirable"),
            ],
        ),
        ("TROP", "Troponin I", 1200, "numeric", [("Troponin I", "ng/mL", None, 0, 0.04, None)]),
        ("CKMB", "CK-MB", 800, "numeric", [("CK-MB", "ng/mL", None, 0, 5, None)]),
        ("AMY", "Serum Amylase", 500, "numeric", [("Amylase", "U/L", None, 28, 100, None)]),
        ("LIP", "Serum Lipase", 600, "numeric", [("Lipase", "U/L", None, 13, 60, None)]),
        (
            "FBS",
            "Fasting Blood Glucose",
            150,
            "numeric",
            [("Glucose (Fasting)", "mg/dL", None, 70, 99, None)],
        ),
        (
            "PPBS",
            "Post-Prandial Blood Sugar",
            150,
            "numeric",
            [("Glucose (PP)", "mg/dL", None, None, 140, None)],
        ),
        (
            "HBA1C",
            "HbA1c Glycated Hemoglobin",
            850,
            "numeric",
            [
                ("HbA1c (Glycated Hb)", "%", None, 4, 5.6, "Non-diabetic"),
                ("HbA1c (Glycated Hb)", "%", None, 5.7, 6.4, "Pre-diabetic"),
                ("HbA1c (Glycated Hb)", "%", None, 6.5, 14, "Diabetic"),
            ],
        ),
        (
            "OGTT",
            "Oral Glucose Tolerance Test (OGTT)",
            600,
            "composite",
            [
                ("OGTT Fasting", "mg/dL", None, 70, 100, None),
                ("OGTT 2-hour", "mg/dL", None, None, 140, None),
            ],
        ),
        (
            "IRON",
            "Iron Studies",
            1200,
            "composite",
            [
                ("Serum Iron", "ug/dL", "male", 65, 175, None),
                ("Serum Iron", "ug/dL", "female", 50, 170, None),
                ("TIBC", "ug/dL", None, 250, 450, None),
                ("Ferritin", "ng/mL", "male", 30, 400, None),
                ("Ferritin", "ng/mL", "female", 15, 150, None),
            ],
        ),
        (
            "VITD",
            "Vitamin D3 (25-OH)",
            1800,
            "numeric",
            [("Vitamin D3", "ng/mL", None, 30, 100, "Sufficient")],
        ),
        ("VITB12", "Vitamin B12", 1500, "numeric", [("Vitamin B12", "pg/mL", None, 200, 900, None)]),
    ],
    "Endocrinology": [
        (
            "TFT",
            "Thyroid Function Test (TFT)",
            1200,
            "composite",
            [
                ("Total Triiodothyronine (T3)", "ng/mL", None, 0.8, 2.0, None),
                ("Total Thyroxine (T4)", "ug/dL", None, 5.1, 14.1, None),
                ("Free T3 (FT3)", "pg/mL", None, 2.0, 4.4, None),
                ("Free T4 (FT4)", "ng/dL", None, 0.93, 1.7, None),
                ("Thyroid Stimulating Hormone (TSH)", "uIU/mL", None, 0.27, 4.2, None),
            ],
        ),
    ],
    "Hematology": [
        (
            "CBC",
            "Complete Blood Count (CBC)",
            450,
            "composite",
            [
                ("Hemoglobin", "g/dL", "male", 13, 17, None),
                ("Hemoglobin", "g/dL", "female", 12, 15, None),
                ("Total RBC Count", "10^6/uL", "male", 4.5, 5.5, None),
                ("Total RBC Count", "10^6/uL", "female", 3.8, 4.8, None),
                ("Total WBC Count (TLC)", "10^3/uL", None, 4, 11, None),
                ("Platelet Count", "10^3/uL", None, 150, 450, None),
                ("Packed Cell Volume (PCV)", "%", "male", 40, 50, None),
                ("Packed Cell Volume (PCV)", "%", "female", 36, 46, None),
                ("Mean Corpuscular Volume (MCV)", "fL", None, 80, 100, None),
                ("Mean Corpuscular Hb (MCH)", "pg", None, 27, 32, None),
                ("MCHC", "g/dL", None, 32, 36, None),
                ("RDW-CV", "%", None, 11.5, 14.5, None),
                ("MPV", "fL", None, 7.5, 11.5, None),
                ("Neutrophils", "%", None, 40, 80, None),
                ("Lymphocytes", "%", None, 20, 40, None),
                ("Eosinophils", "%", None, 1, 6, None),
                ("Basophils", "%", None, 0, 2, None),
                ("Monocytes", "%", None, 2, 10, None),
            ],
        ),
        (
            "ESR",
            "Erythrocyte Sedimentation Rate (ESR)",
            150,
            "numeric",
            [
                ("ESR (Westergren)", "mm/hr", "male", 0, 15, None),
                ("ESR (Westergren)", "mm/hr", "female", 0, 20, None),
            ],
        ),
        ("PBS", "Peripheral Blood Smear (PBS)", 350, "text", [("PBS Finding", None, None, None, None, None)]),
        ("RETIC", "Reticulocyte Count", 300, "numeric", [("Reticulocytes", "%", None, 0.5, 2.5, None)]),
        (
            "BGRP",
            "Blood Group + Rh Typing",
            200,
            "text",
            [("Blood Group", None, None, None, None, None), ("Rh Factor", None, None, None, None, None)],
        ),
        (
            "PTINR",
            "Prothrombin Time (PT/INR)",
            450,
            "composite",
            [
                ("Prothrombin Time", "sec", None, 11, 13.5, None),
                ("INR", "ratio", None, 0.9, 1.2, None),
            ],
        ),
        (
            "APTT",
            "Activated Partial Thromboplastin Time (aPTT)",
            450,
            "numeric",
            [("aPTT", "sec", None, 25, 35, None)],
        ),
        (
            "BTCT",
            "Bleeding Time / Clotting Time (BT/CT)",
            200,
            "composite",
            [
                ("Bleeding Time", "min", None, 2, 7, None),
                ("Clotting Time", "min", None, 3, 10, None),
            ],
        ),
        ("DDIMER", "D-Dimer", 900, "numeric", [("D-Dimer", "ng/mL", None, 0, 500, None)]),
        ("FIB", "Fibrinogen Assay", 800, "numeric", [("Fibrinogen", "mg/dL", None, 200, 400, None)]),
    ],
    "Clinical Pathology": [
        (
            "URINE-RE",
            "Urine Routine & Microscopic",
            200,
            "composite",
            [
                ("Color / Appearance", None, None, None, None, None),
                ("Specific Gravity", None, None, None, None, None),
                ("Reaction / pH", None, None, None, None, None),
                ("Protein (Albumin)", None, None, None, None, None),
                ("Glucose / Sugar", None, None, None, None, None),
                ("Pus Cells", "/hpf", None, None, None, None),
                ("RBCs", "/hpf", None, None, None, None),
            ],
        ),
        (
            "STOOL",
            "Stool Routine & Microscopic",
            200,
            "composite",
            [
                ("Color / Consistency", None, None, None, None, None),
                ("Occult Blood", None, None, None, None, None),
                ("Ova / Cysts", None, None, None, None, None),
                ("Pus Cells", "/hpf", None, None, None, None),
                ("RBCs", "/hpf", None, None, None, None),
            ],
        ),
        ("UPT", "Urine Pregnancy Test (UPT)", 200, "text", [("UPT Result", None, None, None, None, None)]),
    ],
    "Microbiology": [
        (
            "UCULT",
            "Urine Culture & Sensitivity",
            800,
            "text",
            [("Culture Result", None, None, None, None, None), ("Sensitivity", None, None, None, None, None)],
        ),
        (
            "BCULT",
            "Blood Culture & Sensitivity",
            1200,
            "text",
            [("Culture Result", None, None, None, None, None), ("Sensitivity", None, None, None, None, None)],
        ),
        (
            "SCULT",
            "Sputum Culture & Sensitivity",
            800,
            "text",
            [("Culture Result", None, None, None, None, None)],
        ),
        (
            "WCULT",
            "Wound / Pus Swab Culture",
            800,
            "text",
            [("Culture Result", None, None, None, None, None)],
        ),
        ("FCULT", "Body Fluid Culture", 900, "text", [("Culture Result", None, None, None, None, None)]),
        ("GRAM", "Gram Stain", 250, "text", [("Gram Stain Finding", None, None, None, None, None)]),
        ("AFB", "AFB Smear / ZN Stain", 300, "text", [("AFB Result", None, None, None, None, None)]),
        ("FUNG", "Fungal Culture", 700, "text", [("Fungal Culture Result", None, None, None, None, None)]),
        ("KOH", "KOH Mount", 250, "text", [("KOH Finding", None, None, None, None, None)]),
    ],
    "Serology": [
        ("HIV", "HIV Rapid", 400, "text", [("HIV Result", None, None, None, None, None)]),
        ("HBSAG", "HBsAg", 400, "text", [("HBsAg Result", None, None, None, None, None)]),
        ("HCV", "Anti-HCV", 450, "text", [("HCV Result", None, None, None, None, None)]),
        (
            "DEN",
            "Dengue IgM / IgG",
            700,
            "composite",
            [("Dengue IgM", None, None, None, None, None), ("Dengue IgG", None, None, None, None, None)],
        ),
        ("WIDAL", "Widal Test", 400, "text", [("Widal Result", None, None, None, None, None)]),
        ("CRP", "C-Reactive Protein (CRP)", 600, "numeric", [("CRP", "mg/L", None, 0, 5, None)]),
        ("RA", "Rheumatoid Factor (RA)", 500, "numeric", [("RA Factor", "IU/mL", None, 0, 14, None)]),
        ("ASO", "ASO Titre", 600, "numeric", [("ASO Titre", "IU/mL", None, 0, 200, None)]),
    ],
    "Molecular Diagnostics": [
        (
            "PCR",
            "PCR Testing",
            1500,
            "text",
            [("PCR Result", None, None, None, None, None), ("Ct Value", "Ct", None, None, None, None)],
        ),
        (
            "XPERT",
            "GeneXpert MTB/RIF",
            2000,
            "composite",
            [
                ("MTB Detected", None, None, None, None, None),
                ("Rifampicin Resistance", None, None, None, None, None),
            ],
        ),
    ],
    "X-Ray": [
        (
            "XR-CHEST",
            "Chest X-Ray PA + Lateral",
            700,
            "composite",
            [
                ("Chest PA view", None, None, None, None, None),
                ("Chest Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-ABD",
            "X-Ray Abdomen Erect / Supine",
            600,
            "composite",
            [
                ("Abdomen Erect view", None, None, None, None, None),
                ("Abdomen Supine view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        ("XR-KUB", "X-Ray KUB", 600, "text", [("Impression", None, None, None, None, None)]),
        ("XR-PELVIS", "X-Ray Pelvis AP", 600, "text", [("Impression", None, None, None, None, None)]),
        (
            "XR-SKULL",
            "X-Ray Skull AP / Lateral",
            700,
            "composite",
            [
                ("Skull AP view", None, None, None, None, None),
                ("Skull Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-PNS",
            "X-Ray PNS (Sinuses)",
            500,
            "composite",
            [
                ("PNS Caldwell view", None, None, None, None, None),
                ("PNS Waters view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-SPINE-C",
            "X-Ray Cervical Spine AP / Lateral",
            600,
            "composite",
            [
                ("Cervical AP view", None, None, None, None, None),
                ("Cervical Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-SPINE-T",
            "X-Ray Thoracic Spine AP / Lateral",
            650,
            "composite",
            [
                ("Thoracic AP view", None, None, None, None, None),
                ("Thoracic Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-SPINE-L",
            "X-Ray Lumbo-sacral Spine AP / Lateral",
            650,
            "composite",
            [
                ("Lumbo-sacral AP view", None, None, None, None, None),
                ("Lumbo-sacral Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-SHOULDER",
            "X-Ray Shoulder AP / Axillary",
            550,
            "composite",
            [
                ("Shoulder AP view", None, None, None, None, None),
                ("Shoulder Axillary view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-ELBOW",
            "X-Ray Elbow AP / Lateral",
            500,
            "composite",
            [
                ("Elbow AP view", None, None, None, None, None),
                ("Elbow Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-HAND",
            "X-Ray Wrist + Hand AP / Oblique",
            500,
            "composite",
            [
                ("Wrist/Hand AP view", None, None, None, None, None),
                ("Wrist/Hand Oblique view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-HIP",
            "X-Ray Hip AP / Lateral",
            600,
            "composite",
            [
                ("Hip AP view", None, None, None, None, None),
                ("Hip Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-KNEE",
            "X-Ray Knee AP / Lateral",
            550,
            "composite",
            [
                ("Knee AP view", None, None, None, None, None),
                ("Knee Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
        (
            "XR-FOOT",
            "X-Ray Ankle + Foot AP / Lateral",
            550,
            "composite",
            [
                ("Ankle/Foot AP view", None, None, None, None, None),
                ("Ankle/Foot Lateral view", None, None, None, None, None),
                ("Impression", None, None, None, None, None),
            ],
        ),
    ],
}

PACKAGES: list[dict] = [
    {
        "code": "PKG-ABROAD",
        "name": "Abroad Medical Screening Package",
        "description": (
            "Comprehensive pre-departure and visa medical screening for abroad visiting and employment "
            "(CBC, Blood Group, Urine RE, Stool RE, LFT, KFT, HIV, HBsAg, HCV, Chest X-Ray)"
        ),
        "price": Decimal("3500.00"),
        "test_codes": ["CBC", "BGRP", "URINE-RE", "STOOL", "LFT", "KFT", "HIV", "HBSAG", "HCV", "XR-CHEST"],
    },
    {
        "code": "PKG-FBC",
        "name": "Executive Full Body Checkup",
        "description": (
            "Comprehensive full body wellness screening (CBC, LFT, KFT, Lipid Profile, Urine RE, FBS)"
        ),
        "price": Decimal("3200.00"),
        "test_codes": ["CBC", "LFT", "KFT", "LIPID", "URINE-RE", "FBS"],
    },
]


async def main() -> None:
    repo = TestCatalogRepository()
    counts = {"categories": 0, "tests": 0, "params": 0, "ranges": 0, "packages": 0, "package_items": 0}
    async with AsyncSessionLocal() as db:
        lab_id = await LabRepository().get_default_lab_id(db)
        for cat_name, tests in CATALOG.items():
            cat = await repo.get_category_by_name(db, lab_id, cat_name)
            if not cat:
                cat = await repo.create_category(db, TestCategoryCreate(lab_id=lab_id, name=cat_name))
                counts["categories"] += 1
            for code, name, price, rtype, params in tests:
                test = await repo.get_test_by_code(db, lab_id, code)
                if not test:
                    test = await repo.create_test(
                        db,
                        TestCreate(
                            lab_id=lab_id,
                            category_id=cat.id,
                            name=name,
                            code=code,
                            price=Decimal(str(price)),
                            result_type=TestResultType(rtype),
                        ),
                    )
                    counts["tests"] += 1
                existing_params = {p.name.lower(): p for p in await repo.list_parameters(db, test.id)}
                # Split the panel price across unique analytes so members sum to the panel.
                param_names = list(dict.fromkeys(pname for pname, *_ in params))
                n = len(param_names)
                base, rem = divmod(int(price), n) if n else (0, 0)
                split = {name: base + (1 if i < rem else 0) for i, name in enumerate(param_names)}
                for order, (pname, unit, gender, low, high, label) in enumerate(params):
                    param = existing_params.get(pname.lower())
                    if not param:
                        param = await repo.create_parameter(
                            db,
                            test.id,
                            TestParameterCreate(
                                name=pname,
                                unit=unit,
                                price=Decimal(split.get(pname, 0)),
                                sort_order=order,
                            ),
                        )
                        counts["params"] += 1
                    elif param.price is None:
                        param = await repo.update_parameter(
                            db, param, TestParameterUpdate(price=Decimal(split.get(pname, 0)))
                        )
                    if low is None and high is None and not label:
                        continue
                    stored = await repo.list_ranges(db, param.id)
                    dup = any(
                        (
                            r.gender is None
                            and gender is None
                            or r.gender is not None
                            and gender is not None
                            and str(r.gender) == gender
                        )
                        and (
                            r.numeric_low is None
                            and low is None
                            or r.numeric_low is not None
                            and low is not None
                            and float(r.numeric_low) == float(low)
                        )
                        and (
                            r.numeric_high is None
                            and high is None
                            or r.numeric_high is not None
                            and high is not None
                            and float(r.numeric_high) == float(high)
                        )
                        and (r.text_normal or None) == label
                        for r in stored
                    )
                    if dup:
                        continue
                    await repo.create_range(
                        db,
                        param.id,
                        TestParameterRangeCreate(
                            gender=gender,  # type: ignore[arg-type]
                            numeric_low=Decimal(str(low)) if low is not None else None,
                            numeric_high=Decimal(str(high)) if high is not None else None,
                            text_normal=label,
                        ),
                    )
                    counts["ranges"] += 1

        # Seed standard health packages
        from app.repositories.package_repository import PackageRepository
        from app.schemas.package import TestPackageCreate

        pkg_repo = PackageRepository()
        for pdef in PACKAGES:
            pkg = await pkg_repo.get_by_code_including_deleted(db, lab_id, pdef["code"])
            if not pkg:
                pkg = await pkg_repo.create(
                    db,
                    TestPackageCreate(
                        lab_id=lab_id,
                        name=pdef["name"],
                        code=pdef["code"],
                        description=pdef["description"],
                        price=pdef["price"],
                        is_active=True,
                    ),
                )
                counts["packages"] += 1
            elif pkg.deleted_at is not None:
                await pkg_repo.restore_from_create(
                    db,
                    pkg,
                    TestPackageCreate(
                        lab_id=lab_id,
                        name=pdef["name"],
                        code=pdef["code"],
                        description=pdef["description"],
                        price=pdef["price"],
                        is_active=True,
                    ),
                )
            existing_pkg = await pkg_repo.get_with_items(db, pkg.id)
            existing_test_ids = {it.test_id for it in (existing_pkg.items if existing_pkg else [])}
            for order, t_code in enumerate(pdef["test_codes"]):
                test = await repo.get_test_by_code(db, lab_id, t_code)
                if test and test.id not in existing_test_ids:
                    await pkg_repo.add_item(db, pkg.id, test.id, parameter_id=None, sort_order=order)
                    counts["package_items"] += 1

        await db.commit()
    print(f"Seed complete: {counts}")


async def delete_seeded() -> None:
    """Remove seeded tests, then seeded categories left empty.

    A category is deleted ONLY when zero tests remain in it — user panels
    (custom codes, pre-existing data) keep their category alive. Emptiness
    is checked BEFORE delete because delete_category cascades to its panels.
    """
    from app.repositories.package_repository import PackageRepository
    from app.repositories.test_repository import TestCatalogRepository

    repo = TestCatalogRepository()
    pkg_repo = PackageRepository()
    codes = {code for tests in CATALOG.values() for code, *_ in tests}
    removed = 0
    cats_removed = 0
    async with AsyncSessionLocal() as db:
        lab_id = await LabRepository().get_default_lab_id(db)
        for pdef in PACKAGES:
            pkg = await pkg_repo.get_by_code(db, lab_id, pdef["code"])
            if pkg:
                order_count = await pkg_repo.get_package_order_count(db, pkg.id)
                if order_count == 0:
                    await pkg_repo.hard_delete_package(db, pkg)
                else:
                    await pkg_repo.delete(db, pkg)
        for code in sorted(codes):
            test = await repo.get_test_by_code(db, lab_id, code)
            if not test:
                continue
            order_count = await repo.get_test_order_count(db, test.id)
            if order_count == 0:
                await repo.hard_delete_test(db, test)
            else:
                await repo.delete_test(db, test)
            removed += 1
        for cat_name in CATALOG:
            cat = await repo.get_category_by_name(db, lab_id, cat_name)
            if not cat:
                continue
            _, total = await repo.list_tests(db, limit=1, offset=0, lab_id=lab_id, category_id=cat.id)
            if total == 0:
                await repo.delete_category(db, cat)
                cats_removed += 1
        await db.commit()
    print(f"Delete complete: {removed} seeded panel(s) and {cats_removed} emptied categor(ies) removed")


if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser(description="Seed or delete the standard clinical catalog")
    parser.add_argument("--delete", action="store_true", help="Delete seeded panels instead of seeding")
    args = parser.parse_args()
    if args.delete:
        asyncio.run(delete_seeded())
    else:
        asyncio.run(main())