#!/usr/bin/env python3
"""Remove core panel tests from database_installer.sql seed blocks."""
from pathlib import Path
import re

ROOT = Path(__file__).resolve().parents[1]
SQL = ROOT / "install" / "database_installer.sql"
text = SQL.read_text(encoding="utf-8")

EXCLUSION_BLOCK = """
-- ============================================================
-- INSTALLER EXCLUSION — core panel tests (add manually in Admin)
-- TSH, Lipid Profile, CBC, Urine C/E, LFT, RFT are NOT seeded.
-- ============================================================
START TRANSACTION;

UPDATE tests SET status = 0
WHERE (
    LOWER(TRIM(name)) IN (
        'tsh (thyroid stimulating hormone)',
        'tsh',
        'tsh level (thyroid stimulating hormone)',
        'serum tsh',
        'thyroid stimulating hormone',
        'complete blood count (cbc)',
        'cbc (complete blood count)',
        'urine complete examination',
        'lipid profile',
        'fasting lipid profile (flp)',
        'lfts (liver function test)',
        'liver function test (lft)',
        'rfts ( renal function tests)',
        'renal function test (rfts)'
    )
    OR test_code IN ('0333', '1118', '0025', '0046', 'CBC')
    OR (
        LOWER(TRIM(short_name)) IN ('tsh', 'cbc', 'uce', 'urine c/e', 'lipid', 'flp', 'lfts', 'lft', 'rfts', 'rft')
        AND LOWER(TRIM(name)) NOT LIKE '%+%'
        AND LOWER(TRIM(name)) NOT LIKE '%thyroid function test%'
        AND LOWER(TRIM(name)) NOT LIKE '%t3%'
        AND LOWER(TRIM(name)) NOT LIKE '%t4%'
    )
);

COMMIT;
"""

# Remove initial bulk INSERT rows (SECTION 13)
initial_removals = [
    r"\n-- CBC - Complete Blood Count\n\(1,1,'Complete Blood Count \(CBC\)'.*?\),\n",
    r"\n-- LFTs - Liver Function Tests \(full sub-parameters\)\n\(4,2,'LFTs \(Liver Function Test\)'.*?\),\n",
    r"\n-- Lipid Profile \(full sub-parameters\)\n\(5,2,'Lipid Profile'.*?\),\n",
    r"\n-- UCE - Urine Complete Examination \(full sub-parameters\)\n\(8,5,'Urine Complete Examination'.*?\),\n",
    r"\n-- RFTs\n\(10,8,'RFTs \( Renal Function Tests\)'.*?\),\n",
    r"\n-- TSH, Prolactin\n\(28,6,'TSH \(Thyroid Stimulating Hormone\)'.*?\),\n",
]
for pattern in initial_removals:
    text, n = re.subn(pattern, "\n", text, count=1, flags=re.DOTALL)
    if n:
        print(f"Removed initial insert: {pattern[:40]}...")

# Remove standalone TSH section (~1581)
text, n = re.subn(
    r"\n-- ============================================================\n"
    r"-- TSH \(Standalone\).*?"
    r"WHERE `name` = 'TSH' AND \(`short_name` = 'TSH' OR `short_name` IS NULL\);\n",
    "\n-- TSH (Standalone) excluded from installer — add manually via Admin Tests.\n",
    text,
    count=1,
    flags=re.DOTALL,
)
print(f"Removed TSH standalone section: {n}")

# Remove CBC section 6
text, n = re.subn(
    r"\n-- ============================================================\n"
    r"-- 6\. HEMATOLOGY — Complete Blood Count \(CBC\).*?"
    r"WHERE `name` IN \('CBC','Complete Blood Count \(CBC\)'\) AND `short_name` IN \('CBC'\);\n",
    "\n-- 6. CBC excluded from installer — add manually via Admin Tests.\n",
    text,
    count=1,
    flags=re.DOTALL,
)
print(f"Removed CBC section 6: {n}")

# Remove urine section 34
text, n = re.subn(
    r"\n-- ============================================================\n"
    r"-- 34\. URINE — Complete Urinalysis / UCE.*?"
    r"WHERE `name` = 'Urine Complete Examination';\n",
    "\n-- 34. Urine Complete Examination excluded from installer — add manually via Admin Tests.\n",
    text,
    count=1,
    flags=re.DOTALL,
)
print(f"Removed urine section 34: {n}")

# Remove duplicate TSH insert
text, n = re.subn(
    r"\nINSERT INTO `tests` \(`category_id`,`name`,`short_name`.*?"
    r"WHERE NOT EXISTS \(SELECT 1 FROM `tests` WHERE `name`='TSH \(Thyroid Stimulating Hormone\)'\);\n",
    "\n-- TSH insert excluded from installer.\n",
    text,
    count=1,
    flags=re.DOTALL,
)
print(f"Removed duplicate TSH insert: {n}")

# Remove TSH Level insert block
text, n = re.subn(
    r"\n-- ============================================================\n"
    r"-- TSH Level - Detailed with interpretation\n"
    r"-- ============================================================\n"
    r"INSERT INTO `tests`.*?WHERE NOT EXISTS \(SELECT 1 FROM `tests` WHERE `name`='TSH Level \(Thyroid Stimulating Hormone\)'\);\n",
    "\n-- TSH Level excluded from installer.\n",
    text,
    count=1,
    flags=re.DOTALL,
)
print(f"Removed TSH Level block: {n}")

# Lipid Profile card patch — skip
text = re.sub(
    r"\n-- Graphical Fasting Lipid Profile from the supplied OneLABz report\..*?WHERE `name` = 'Lipid Profile';\n",
    "\n-- Lipid Profile patch excluded from installer.\n",
    text,
    count=1,
    flags=re.DOTALL,
)

text = re.sub(
    r"\nUPDATE `tests` SET `report_pattern` = 'graphical_range'.*?WHERE `name` IN \('Lipid Profile'.*?\);\n",
    "\n",
    text,
    count=1,
    flags=re.DOTALL,
)

# Core endocrinology TSH update
text = re.sub(
    r"\n-- Core endocrinology reference ranges\.\nUPDATE `tests` SET\n  `short_name`='TSH'.*?WHERE LOWER\(`name`\) IN \('tsh','thyroid stimulating hormone','serum tsh'\);\n",
    "\n-- Standalone TSH reference update excluded from installer.\n",
    text,
    count=1,
    flags=re.DOTALL,
)

# Replace canonical blocks with exclusion stubs
block_replacements = [
    (
        "-- Consolidated from 2026_07_26_cbc_tsh_test_settings.sql",
        "-- Consolidated from 2026_07_26_cbc_tsh_test_settings.sql\n"
        "-- NOTE: CBC seeding removed; TFT panel seeding retained below.\n",
    ),
]

# Remove CBC insert/update/formulas from cbc_tsh block, keep TFT
cbc_block = re.search(
    r"(SET @cbc_cat=.*?SET @tsh_dep=.*?)\n\nINSERT INTO tests\n\(test_code,name,short_name.*?DELETE FROM test_formulas WHERE test_id=@cbc_id;.*?\n\n(INSERT INTO tests\n\(name,short_name,category_id)",
    text,
    flags=re.DOTALL,
)
if cbc_block:
    text = (
        text[: cbc_block.start()]
        + cbc_block.group(1)
        + "\n\n-- CBC (Complete Blood Count) excluded from installer — add manually via Admin Tests.\n\n"
        + cbc_block.group(2)
        + text[cbc_block.end() :]
    )
    print("Stripped CBC from cbc_tsh block")

# Replace whole transaction blocks
for marker, replacement in [
    ("-- Consolidated from 2026_07_26_lft_test_settings.sql", "-- LFT excluded from installer.\n"),
    ("-- Consolidated from 2026_07_26_fasting_lipid_profile_settings.sql", "-- Lipid Profile excluded from installer.\n"),
    ("-- Renal Function Test replacement from Rfts.png", "-- RFT excluded from installer.\n"),
    ("-- Canonical Urine Complete Examination replacement", "-- Urine C/E excluded from installer.\n"),
    ("-- Canonical standalone TSH replacement", "-- Standalone TSH excluded from installer.\n"),
]:
    pattern = rf"\n{re.escape(marker)}.*?COMMIT;\n"
    text, n = re.subn(pattern, f"\n{replacement}\n", text, count=1, flags=re.DOTALL)
    print(f"Replaced block {marker}: {n}")

if "INSTALLER EXCLUSION" not in text:
    text = text.rstrip() + EXCLUSION_BLOCK

SQL.write_text(text, encoding="utf-8")
print("Done.")
