function removeBetween(startMarker, endMarker, replacement = '') {
    const start = text.indexOf(startMarker);
    if (start === -1) {
        console.log('Missing start:', startMarker);
        return;
    }
    const end = text.indexOf(endMarker, start);
    if (end === -1) {
        console.log('Missing end for:', startMarker);
        return;
    }
    text = text.slice(0, start) + replacement + text.slice(end + endMarker.length);
    console.log('Removed between', startMarker.slice(0, 40), '...');
}

removeBetween(
    "INSERT INTO tests\n(test_code,name,short_name,category_id,department_id,price,normal_range,unit,sample_type,\n processing_time,method,interpretation,result_type,sub_parameters,test_structure,\n print_order,bold_result,show_department_heading,show_test_heading,show_in_report,status)\nSELECT 'CBC','CBC (Complete Blood Count)'",
    "INSERT INTO tests\n(name,short_name,category_id,department_id,price,normal_range,unit,sample_type,\n processing_time,method,interpretation,result_type,sub_parameters,test_structure,\n print_order,bold_result,show_department_heading,show_test_heading,show_in_report,status)\nSELECT 'Thyroid Function Test (T3, T4, TSH)'",
    "-- CBC (Complete Blood Count) excluded from installer — add manually via Admin Tests.\n\nINSERT INTO tests\n(name,short_name,category_id,department_id,price,normal_range,unit,sample_type,\n processing_time,method,interpretation,result_type,sub_parameters,test_structure,\n print_order,bold_result,show_department_heading,show_test_heading,show_in_report,status)\nSELECT 'Thyroid Function Test (T3, T4, TSH)'"
);

removeBetween(
    'DELETE FROM test_formulas WHERE test_id=@cbc_id;',
    'COMMIT;',
    'COMMIT;'
);

removeBetween(
    '\n-- CBC - Complete Blood Count\n',
    '\n-- HbA1C\n',
    '\n-- CBC excluded from installer.\n\n-- HbA1C\n'
);

removeBetween(
    '\n-- LFTs - Liver Function Tests (full sub-parameters)\n',
    '\n-- HCV PCR\n',
    '\n-- LFT excluded from installer.\n\n-- HCV PCR\n'
);

removeBetween(
    '\n-- Lipid Profile (full sub-parameters)\n',
    '\n-- HCV PCR\n',
    '\n-- Lipid Profile excluded from installer.\n\n-- HCV PCR\n'
);

removeBetween(
    '\n-- UCE - Urine Complete Examination (full sub-parameters)\n',
    '\n-- TFT (Thyroid Function Test)',
    '\n-- Urine C/E excluded from installer.\n\n-- TFT (Thyroid Function Test)'
);

removeBetween(
    '\n-- RFTs\n',
    '\n-- Typhidot\n',
    '\n-- RFT excluded from installer.\n\n-- Typhidot\n'
);

removeBetween(
    '\n-- TSH, Prolactin\n(28,6,',
    '\n(29,6,',
    '\n-- Standalone TSH excluded from installer.\n(29,6,'
);

removeBetween(
    '\n-- ============================================================\n-- 6. HEMATOLOGY — Complete Blood Count (CBC)',
    '\n-- ============================================================\n-- 7. BIOCHEMISTRY — Iron',
    '\n-- 6. CBC excluded from installer.\n\n-- ============================================================\n-- 7. BIOCHEMISTRY — Iron'
);

removeBetween(
    '\n-- ============================================================\n-- 34. URINE — Complete Urinalysis / UCE',
    '\n-- ============================================================\n-- 35. SPECIAL REPORT — HbA1c',
    '\n-- 34. Urine C/E excluded from installer.\n\n-- ============================================================\n-- 35. SPECIAL REPORT — HbA1c'
);

removeBetween(
    "\nINSERT INTO `tests` (`category_id`,`name`,`short_name`,`normal_range`,`unit`,`price`,`sample_type`,`processing_time`,`result_type`,`status`)\nSELECT @cat_endo,'TSH (Thyroid Stimulating Hormone)'",
    "\nINSERT INTO `tests` (`category_id`,`name`,`short_name`,`normal_range`,`unit`,`price`,`sample_type`,`processing_time`,`result_type`,`status`)\nSELECT @cat_coag,'TT (Thrombin Time)'",
    "\n-- TSH insert excluded from installer.\n\nINSERT INTO `tests` (`category_id`,`name`,`short_name`,`normal_range`,`unit`,`price`,`sample_type`,`processing_time`,`result_type`,`status`)\nSELECT @cat_coag,'TT (Thrombin Time)'"
);

removeBetween(
    '\n-- ============================================================\n-- TSH Level - Detailed with interpretation',
    '\n-- ============================================================\n-- LH Level',
    '\n-- TSH Level excluded from installer.\n\n-- ============================================================\n-- LH Level'
);

removeBetween(
    '\n-- Graphical Fasting Lipid Profile from the supplied OneLABz report.',
    "\n-- iPTH: ensure range and add description",
    '\n-- Lipid Profile patch excluded from installer.\n\n-- iPTH: ensure range and add description'
);

text = text.replace(
    /\nUPDATE `tests` SET `report_pattern` = 'graphical_range'[\s\S]*?WHERE `name` IN \('Lipid Profile'[\s\S]*?\);\n/,
    '\n'
);

text = text.replace(
    /\n-- Core endocrinology reference ranges\.\nUPDATE `tests` SET\n  `short_name`='TSH'[\s\S]*?WHERE LOWER\(`name`\) IN \('tsh','thyroid stimulating hormone','serum tsh'\);\n/,
    '\n-- Standalone TSH reference update excluded from installer.\n'
);

// Remove duplicate sections if script re-run
text = text.replace(/\n-- INSTALLER EXCLUSION — core panel tests[\s\S]*?COMMIT;\n/g, '\n');

const cbcBlock = text.match(/SET @cbc_cat=[\s\S]*?DELETE FROM test_formulas WHERE test_id=@cbc_id;[\s\S]*?\n\n(INSERT INTO tests\n\(name,short_name,category_id)/);
if (cbcBlock) {
    text = text.replace(
        /SET @cbc_cat=[\s\S]*?DELETE FROM test_formulas WHERE test_id=@cbc_id;[\s\S]*?\n\n(INSERT INTO tests\n\(name,short_name,category_id)/,
        'SET @cbc_cat=(SELECT id FROM test_categories WHERE name=\'Hematology\' LIMIT 1);\nSET @cbc_dep=(SELECT id FROM laboratory_departments WHERE name=\'Hematology\' LIMIT 1);\nSET @tsh_cat=(SELECT id FROM test_categories WHERE name=\'Special Chemistry\' LIMIT 1);\nSET @tsh_dep=(SELECT id FROM laboratory_departments WHERE name=\'Special Chemistry\' LIMIT 1);\n\n-- CBC (Complete Blood Count) excluded from installer — add manually via Admin Tests.\n\n$1'
    );
    console.log('Stripped CBC from cbc_tsh block');
}

replaceBlock('-- Consolidated from 2026_07_26_lft_test_settings.sql', '-- LFT excluded from installer.\n');
replaceBlock('-- Consolidated from 2026_07_26_fasting_lipid_profile_settings.sql', '-- Lipid Profile excluded from installer.\n');
replaceBlock('-- Renal Function Test replacement from Rfts.png', '-- RFT excluded from installer.\n');
replaceBlock('-- Canonical Urine Complete Examination replacement from AdminTest screenshot', '-- Urine C/E excluded from installer.\n');
replaceBlock('-- Canonical standalone TSH replacement from tsh.png', '-- Standalone TSH excluded from installer.\n');

if (!text.includes('INSTALLER EXCLUSION')) {
    text = text.trimEnd() + EXCLUSION_BLOCK;
}