-- ============================================================================
-- BLOOD TEST DATABASE CORRECTIONS — January 2026
-- Based on actual PDF from Sullivan Nicolaides Pathology
-- Run in Supabase SQL Editor
-- Generated: 7 February 2026
-- ============================================================================

-- ============================================================================
-- STEP 1: Update January 2026 Panel metadata
-- ============================================================================
UPDATE chl_bloodtest_panels
SET
  lab_name = 'Sullivan Nicolaides Pathology',
  ordered_by = 'Dr Mark J Friel',
  panel_name = 'Comprehensive',
  health_score = 72,
  markers_optimal = 25,
  markers_good = 12,
  markers_warning = 4,
  markers_high = 6,
  markers_low = 0,
  analysis_summary = 'Strong health foundation with excellent inflammation (CRP 0.4), testosterone (27.0), and liver function. Primary concern: lipid profile trending up over 7 years (ApoB 1.12, LDL 4.1 H, TC 6.1 H). Strategy: 3-month dietary intervention then retest April 2026.',
  updated_at = NOW()
WHERE id = 'bt202601';

-- ============================================================================
-- STEP 2: Delete ALL existing Jan 2026 markers (they were fabricated)
-- ============================================================================
DELETE FROM chl_bloodtest_markers WHERE panel_id = 'bt202601';

-- ============================================================================
-- STEP 3: Insert CORRECT Jan 2026 markers from actual PDF
-- ============================================================================
INSERT INTO chl_bloodtest_markers (id, panel_id, name, short_name, category, sort_order, value, unit, ref_low, ref_high, optimal_low, optimal_high, status, notes) VALUES

-- Electrolytes
('m26_na',     'bt202601', 'Sodium',           'Na',     'electrolytes', 1,  139,   'mmol/L', 135, 145, 136, 142, 'good', NULL),
('m26_k',      'bt202601', 'Potassium',        'K',      'electrolytes', 2,  4.5,   'mmol/L', 3.5, 5.5, 3.8, 5.0, 'good', NULL),
('m26_cl',     'bt202601', 'Chloride',         'Cl',     'electrolytes', 3,  104,   'mmol/L', 95,  110, 98,  106, 'good', NULL),
('m26_bicarb', 'bt202601', 'Bicarbonate',      'HCO3',   'electrolytes', 4,  28,    'mmol/L', 20,  32,  22,  30,  'good', NULL),
('m26_ag',     'bt202601', 'Anion Gap',        'AG',     'electrolytes', 5,  7,     'mmol/L', NULL, 16,  NULL, 12, 'optimal', NULL),
('m26_ca',     'bt202601', 'Calcium (Corr.)',  'Ca',     'electrolytes', 6,  2.42,  'mmol/L', 2.10, 2.60, 2.20, 2.50, 'good', NULL),
('m26_phos',   'bt202601', 'Phosphate',        'PO4',    'electrolytes', 7,  1.22,  'mmol/L', 0.80, 1.50, 0.90, 1.40, 'good', NULL),

-- Kidney
('m26_urea',   'bt202601', 'Urea',             'Urea',   'kidney', 10, 7.4,  'mmol/L', 3.5, 8.5, 3.5, 7.0, 'borderline', 'Trending up: 4.7→4.9→6.5→7.4. Monitor with kidney function'),
('m26_urate',  'bt202601', 'Urate',            'Urate',  'kidney', 11, 0.316,'mmol/L', 0.200, 0.500, 0.200, 0.400, 'good', NULL),
('m26_creat',  'bt202601', 'Creatinine',       'Creat',  'kidney', 12, 89,   'umol/L', 60, 110, 60, 100, 'good', 'Improved from 105 in 2024'),
('m26_egfr',   'bt202601', 'eGFR',             'eGFR',   'kidney', 13, 86,   'mL/min', 59, NULL, 90, NULL, 'borderline', 'Recovering from 72 in 2024. Still below optimal >90'),

-- Metabolic
('m26_gluc',   'bt202601', 'Random Glucose',   'Gluc',   'metabolic', 20, 5.3,  'mmol/L', 3.6, 7.7, 4.0, 5.5, 'good', 'Non-fasting. Request fasting glucose next panel'),
('m26_hba1c',  'bt202601', 'HbA1c',            'HbA1c',  'metabolic', 21, 5.4,  '%',      NULL, 6.5, NULL, 5.2, 'borderline', 'Improved from 5.5 in 2024. Above longevity optimal <5.2%'),
('m26_hba1ci', 'bt202601', 'HbA1c (IFCC)',     'HbA1c-I','metabolic', 22, 35,   'mmol/mol', NULL, 48, NULL, 33, 'borderline', 'Improved from 36 in 2024'),
('m26_ins',    'bt202601', 'Insulin',           'Ins',    'metabolic', 23, 7,    'mU/L',   3, 15, 3, 7, 'good', 'Good insulin sensitivity. Fasting ref only'),
('m26_hcy',    'bt202601', 'Homocysteine',      'Hcy',    'metabolic', 24, 10.0, 'umol/L', 4, 15, 4, 8, 'borderline', 'Above longevity optimal <8. B-vitamin optimization needed'),

-- Liver
('m26_tp',     'bt202601', 'Total Protein',    'TP',     'liver', 30, 77,   'g/L',  63, 80, 65, 78, 'good', NULL),
('m26_alb',    'bt202601', 'Albumin',          'Alb',    'liver', 31, 43,   'g/L',  32, 44, 38, 44, 'optimal', NULL),
('m26_glob',   'bt202601', 'Globulin',         'Glob',   'liver', 32, 34,   'g/L',  23, 43, 25, 38, 'good', NULL),
('m26_bili',   'bt202601', 'T Bilirubin',      'Bili',   'liver', 33, 16,   'umol/L', NULL, 21, NULL, 17, 'good', 'Was 25 H in 2019 and 21 H in 2024'),
('m26_alp',    'bt202601', 'ALP',              'ALP',    'liver', 34, 61,   'U/L',  35, 110, 40, 100, 'good', NULL),
('m26_ast',    'bt202601', 'AST',              'AST',    'liver', 35, 26,   'U/L',  10, 40, 10, 30, 'good', NULL),
('m26_alt',    'bt202601', 'ALT',              'ALT',    'liver', 36, 14,   'U/L',  5, 40, 5, 25, 'optimal', 'Consistently excellent: 15→14→16→14'),
('m26_ggt',    'bt202601', 'GGT',              'GGT',    'liver', 37, 15,   'U/L',  5, 50, 5, 30, 'optimal', 'Consistently excellent: 11→10→12→15'),
('m26_ld',     'bt202601', 'LD',               'LD',     'liver', 38, 188,  'U/L',  NULL, 250, NULL, 200, 'good', NULL),

-- Lipids
('m26_tc',     'bt202601', 'Total Cholesterol', 'TC',    'lipids', 40, 6.1,  'mmol/L', NULL, 5.6, NULL, 5.0, 'high', '7-year uptrend: 4.3→4.2→5.0→6.1'),
('m26_tg',     'bt202601', 'Triglycerides',     'TG',    'lipids', 41, 1.4,  'mmol/L', NULL, 2.1, NULL, 0.9, 'borderline', 'Doubled from 0.7 in 2024 but still in range'),
('m26_hdl',    'bt202601', 'HDL Cholesterol',   'HDL',   'lipids', 42, 1.44, 'mmol/L', 0.89, NULL, 1.5, NULL, 'good', 'Stable: 1.42→1.44'),
('m26_ldl',    'bt202601', 'LDL Cholesterol',   'LDL',   'lipids', 43, 4.1,  'mmol/L', NULL, 4.1, NULL, 2.6, 'high', 'At upper ref limit. 3.3→4.1 (+24%)'),
('m26_ratio',  'bt202601', 'Chol/HDL Ratio',    'TC/HDL','lipids', 44, 4.2,  'ratio',  NULL, 4.6, NULL, 3.5, 'borderline', 'Deteriorating: 3.5→4.2'),
('m26_nhdlc',  'bt202601', 'Non HDLC',          'nHDLC', 'lipids', 45, 4.66, 'mmol/L', NULL, 3.81, NULL, 2.5, 'high', 'Most significant subclinical atherosclerosis marker per NVDPA'),
('m26_apob',   'bt202601', 'Apolipoprotein B',  'ApoB',  'lipids', 46, 1.12, 'g/L',   0.49, 1.73, 0.49, 0.70, 'warning', 'Above longevity targets. ESC: <1.0 moderate, <0.80 high, <0.65 very-high risk'),
('m26_apoa1',  'bt202601', 'Apolipoprotein A1', 'ApoA1', 'lipids', 47, 1.63, 'g/L',   0.95, 1.86, 1.20, 1.86, 'good', NULL),
('m26_abrat',  'bt202601', 'ApoB/ApoA1 Ratio',  'B/A1', 'lipids', 48, 0.69, 'ratio',  NULL, NULL, NULL, 0.60, 'borderline', 'Below high-risk threshold of 0.90'),
('m26_lpa',    'bt202601', 'Lipoprotein(a)',    'Lp(a)', 'lipids', 49, 72,   'nmol/L', NULL, 75, NULL, 30, 'borderline', 'Genetic. Low risk per Australian Atherosclerosis Society (<100)'),

-- Inflammation
('m26_crp',    'bt202601', 'CRP',              'CRP',    'inflammation', 50, 0.4, 'mg/L', NULL, 5, NULL, 0.5, 'optimal', 'Exceptional. 62→16→0.4. Below Attia elite target <0.5'),

-- Thyroid
('m26_tsh',    'bt202601', 'TSH',              'TSH',    'hormones', 55, 1.0, 'mIU/L', 0.3, 4.0, 1.0, 2.5, 'optimal', 'Euthyroid'),

-- Hormones
('m26_testo',  'bt202601', 'Testosterone',      'Testo', 'hormones', 60, 27.0, 'nmol/L', 11.0, 40.0, 15, 30, 'optimal', 'Excellent for age 51 — top quartile'),
('m26_shbg',   'bt202601', 'SHBG',             'SHBG',   'hormones', 61, 76,   'nmol/L', 10.0, 70.0, 20, 50, 'high', 'Flagged HIGH by lab. Reducing free T availability'),
('m26_ft',     'bt202601', 'Calculated Free Testosterone', 'FreeT', 'hormones', 62, 336, 'pmol/L', 260, 740, 260, 740, 'good', 'Healthy range despite high SHBG. In older males may drop to 90'),
('m26_e2',     'bt202601', 'Oestradiol',        'E2',    'hormones', 63, 50,   'pmol/L', NULL, 165, NULL, 165, 'good', 'Reported as <50. Low — monitor alongside SHBG'),

-- Haematology
('m26_hb',     'bt202601', 'Haemoglobin',       'Hb',    'haematology', 70, 156, 'g/L', 135, 175, 140, 170, 'optimal', 'Trending up: 145→142→146→156'),
('m26_hct',    'bt202601', 'Haematocrit',       'Hct',   'haematology', 71, 0.46, '',   0.40, 0.54, 0.40, 0.50, 'good', NULL),
('m26_rcc',    'bt202601', 'RCC',               'RCC',   'haematology', 72, 5.3,  '10*12/L', 4.5, 6.5, 4.5, 6.0, 'good', NULL),
('m26_mcv',    'bt202601', 'MCV',               'MCV',   'haematology', 73, 86,   'fL', 80, 100, 82, 98, 'good', NULL),
('m26_wcc',    'bt202601', 'WCC',               'WCC',   'haematology', 74, 7.9,  '10*9/L', 3.5, 10.0, 4.0, 8.0, 'good', 'Normalized from 10.1 H in 2019'),
('m26_neut',   'bt202601', 'Neutrophils',       'Neut',  'haematology', 75, 5.25, '10*9/L', 1.5, 6.5, 2.0, 6.0, 'good', 'Normalized from 7.69 H in 2019'),
('m26_lymph',  'bt202601', 'Lymphocytes',       'Lymph', 'haematology', 76, 2.02, '10*9/L', 1.0, 4.0, 1.2, 3.5, 'good', NULL),
('m26_mono',   'bt202601', 'Monocytes',         'Mono',  'haematology', 77, 0.39, '10*9/L', 0, 0.9, 0, 0.8, 'optimal', NULL),
('m26_eos',    'bt202601', 'Eosinophils',       'Eos',   'haematology', 78, 0.14, '10*9/L', 0, 0.6, 0, 0.5, 'optimal', NULL),
('m26_baso',   'bt202601', 'Basophils',         'Baso',  'haematology', 79, 0.05, '10*9/L', 0, 0.15, 0, 0.10, 'optimal', NULL),
('m26_plt',    'bt202601', 'Platelets',         'Plt',   'haematology', 80, 282,  '10*9/L', 150, 400, 150, 350, 'good', 'Trending up: 219→226→251→282. Still in range');

-- ============================================================================
-- STEP 4: Add/correct historical markers for October 2019 panel
-- ============================================================================
DELETE FROM chl_bloodtest_markers WHERE panel_id = 'bt201910';

INSERT INTO chl_bloodtest_markers (id, panel_id, name, short_name, category, sort_order, value, unit, ref_low, ref_high, optimal_low, optimal_high, status, notes) VALUES
-- Electrolytes
('m19_na',     'bt201910', 'Sodium',           'Na',     'electrolytes', 1,  141,   'mmol/L', 135, 145, 136, 142, 'good', NULL),
('m19_k',      'bt201910', 'Potassium',        'K',      'electrolytes', 2,  4.5,   'mmol/L', 3.5, 5.5, 3.8, 5.0, 'good', NULL),
('m19_cl',     'bt201910', 'Chloride',         'Cl',     'electrolytes', 3,  103,   'mmol/L', 95,  110, 98,  106, 'good', NULL),
('m19_bicarb', 'bt201910', 'Bicarbonate',      'HCO3',   'electrolytes', 4,  30,    'mmol/L', 20,  32,  22,  30,  'good', NULL),
('m19_ag',     'bt201910', 'Anion Gap',        'AG',     'electrolytes', 5,  8,     'mmol/L', NULL, 16,  NULL, 12, 'optimal', NULL),
('m19_ca',     'bt201910', 'Calcium (Corr.)',  'Ca',     'electrolytes', 6,  2.35,  'mmol/L', 2.10, 2.60, 2.20, 2.50, 'good', NULL),
('m19_phos',   'bt201910', 'Phosphate',        'PO4',    'electrolytes', 7,  0.84,  'mmol/L', 0.80, 1.50, 0.90, 1.40, 'good', NULL),
-- Kidney
('m19_urea',   'bt201910', 'Urea',             'Urea',   'kidney', 10, 4.7,  'mmol/L', 3.5, 8.5, 3.5, 7.0, 'good', NULL),
('m19_urate',  'bt201910', 'Urate',            'Urate',  'kidney', 11, 0.276,'mmol/L', 0.200, 0.500, 0.200, 0.400, 'good', NULL),
('m19_creat',  'bt201910', 'Creatinine',       'Creat',  'kidney', 12, 80,   'umol/L', 60, 110, 60, 100, 'good', NULL),
('m19_egfr',   'bt201910', 'eGFR',             'eGFR',   'kidney', 13, 90,   'mL/min', 59, NULL, 90, NULL, 'optimal', 'Reported as >90'),
-- Metabolic
('m19_gluc',   'bt201910', 'Random Glucose',   'Gluc',   'metabolic', 20, 5.4,  'mmol/L', 3.6, 7.7, 4.0, 5.5, 'good', NULL),
-- Liver
('m19_tp',     'bt201910', 'Total Protein',    'TP',     'liver', 30, 76,   'g/L',  63, 80, 65, 78, 'good', NULL),
('m19_alb',    'bt201910', 'Albumin',          'Alb',    'liver', 31, 41,   'g/L',  32, 44, 38, 44, 'optimal', NULL),
('m19_glob',   'bt201910', 'Globulin',         'Glob',   'liver', 32, 35,   'g/L',  23, 43, 25, 38, 'good', NULL),
('m19_bili',   'bt201910', 'T Bilirubin',      'Bili',   'liver', 33, 25,   'umol/L', NULL, 21, NULL, 17, 'high', 'Flagged HIGH'),
('m19_alp',    'bt201910', 'ALP',              'ALP',    'liver', 34, 55,   'U/L',  35, 110, 40, 100, 'good', NULL),
('m19_alt',    'bt201910', 'ALT',              'ALT',    'liver', 36, 15,   'U/L',  5, 40, 5, 25, 'optimal', NULL),
('m19_ggt',    'bt201910', 'GGT',              'GGT',    'liver', 37, 11,   'U/L',  5, 50, 5, 30, 'optimal', NULL),
-- Lipids
('m19_tc',     'bt201910', 'Total Cholesterol', 'TC',    'lipids', 40, 4.3,  'mmol/L', NULL, 5.6, NULL, 5.0, 'optimal', NULL),
-- Inflammation
('m19_crp',    'bt201910', 'CRP',              'CRP',    'inflammation', 50, 62, 'mg/L', NULL, 5, NULL, 0.5, 'high', 'Likely acute infection — WCC 10.1 H and Neutrophils 7.69 H'),
-- Haematology
('m19_hb',     'bt201910', 'Haemoglobin',       'Hb',    'haematology', 70, 145, 'g/L', 135, 175, 140, 170, 'optimal', NULL),
('m19_hct',    'bt201910', 'Haematocrit',       'Hct',   'haematology', 71, 0.43, '',   0.40, 0.54, 0.40, 0.50, 'good', NULL),
('m19_rcc',    'bt201910', 'RCC',               'RCC',   'haematology', 72, 4.9,  '10*12/L', 4.5, 6.5, 4.5, 6.0, 'good', NULL),
('m19_mcv',    'bt201910', 'MCV',               'MCV',   'haematology', 73, 87,   'fL', 80, 100, 82, 98, 'good', NULL),
('m19_wcc',    'bt201910', 'WCC',               'WCC',   'haematology', 74, 10.1, '10*9/L', 3.5, 10.0, 4.0, 8.0, 'high', 'Flagged HIGH — likely infection'),
('m19_neut',   'bt201910', 'Neutrophils',       'Neut',  'haematology', 75, 7.69, '10*9/L', 1.5, 6.5, 2.0, 6.0, 'high', 'Flagged HIGH'),
('m19_lymph',  'bt201910', 'Lymphocytes',       'Lymph', 'haematology', 76, 1.64, '10*9/L', 1.0, 4.0, 1.2, 3.5, 'good', NULL),
('m19_mono',   'bt201910', 'Monocytes',         'Mono',  'haematology', 77, 0.62, '10*9/L', 0, 0.9, 0, 0.8, 'good', NULL),
('m19_eos',    'bt201910', 'Eosinophils',       'Eos',   'haematology', 78, 0.08, '10*9/L', 0, 0.6, 0, 0.5, 'optimal', NULL),
('m19_baso',   'bt201910', 'Basophils',         'Baso',  'haematology', 79, 0.02, '10*9/L', 0, 0.15, 0, 0.10, 'optimal', NULL),
('m19_plt',    'bt201910', 'Platelets',         'Plt',   'haematology', 80, 219,  '10*9/L', 150, 400, 150, 350, 'good', NULL);

-- Update Oct 2019 panel metadata
UPDATE chl_bloodtest_panels
SET
  lab_name = 'Sullivan Nicolaides Pathology',
  health_score = 65,
  markers_optimal = 10,
  markers_good = 15,
  markers_warning = 0,
  markers_high = 4,
  markers_low = 0,
  analysis_summary = 'CRP 62 H and WCC 10.1 H suggest acute infection at time of draw. Lipids excellent (TC 4.3). Liver function excellent. Otherwise healthy baseline.',
  updated_at = NOW()
WHERE id = 'bt201910';

-- ============================================================================
-- STEP 5: Add/correct historical markers for December 2021 panel
-- ============================================================================
DELETE FROM chl_bloodtest_markers WHERE panel_id = 'bt202112';

INSERT INTO chl_bloodtest_markers (id, panel_id, name, short_name, category, sort_order, value, unit, ref_low, ref_high, optimal_low, optimal_high, status, notes) VALUES
-- Electrolytes
('m21_na',     'bt202112', 'Sodium',           'Na',     'electrolytes', 1,  137,   'mmol/L', 135, 145, 136, 142, 'good', NULL),
('m21_k',      'bt202112', 'Potassium',        'K',      'electrolytes', 2,  3.9,   'mmol/L', 3.5, 5.5, 3.8, 5.0, 'good', NULL),
('m21_cl',     'bt202112', 'Chloride',         'Cl',     'electrolytes', 3,  104,   'mmol/L', 95,  110, 98,  106, 'good', NULL),
('m21_bicarb', 'bt202112', 'Bicarbonate',      'HCO3',   'electrolytes', 4,  25,    'mmol/L', 20,  32,  22,  30,  'good', NULL),
('m21_ag',     'bt202112', 'Anion Gap',        'AG',     'electrolytes', 5,  8,     'mmol/L', NULL, 16,  NULL, 12, 'optimal', NULL),
('m21_ca',     'bt202112', 'Calcium (Corr.)',  'Ca',     'electrolytes', 6,  2.20,  'mmol/L', 2.10, 2.60, 2.20, 2.50, 'good', NULL),
('m21_phos',   'bt202112', 'Phosphate',        'PO4',    'electrolytes', 7,  0.97,  'mmol/L', 0.80, 1.50, 0.90, 1.40, 'good', NULL),
-- Kidney
('m21_urea',   'bt202112', 'Urea',             'Urea',   'kidney', 10, 4.9,  'mmol/L', 3.5, 8.5, 3.5, 7.0, 'good', NULL),
('m21_urate',  'bt202112', 'Urate',            'Urate',  'kidney', 11, 0.356,'mmol/L', 0.200, 0.500, 0.200, 0.400, 'good', NULL),
('m21_creat',  'bt202112', 'Creatinine',       'Creat',  'kidney', 12, 94,   'umol/L', 60, 110, 60, 100, 'good', NULL),
('m21_egfr',   'bt202112', 'eGFR',             'eGFR',   'kidney', 13, 83,   'mL/min', 59, NULL, 90, NULL, 'borderline', 'Below optimal >90'),
-- Metabolic
('m21_gluc',   'bt202112', 'Random Glucose',   'Gluc',   'metabolic', 20, 6.3,  'mmol/L', 3.6, 7.7, 4.0, 5.5, 'good', 'Non-fasting — higher than other draws'),
-- Liver
('m21_tp',     'bt202112', 'Total Protein',    'TP',     'liver', 30, 68,   'g/L',  63, 80, 65, 78, 'good', NULL),
('m21_alb',    'bt202112', 'Albumin',          'Alb',    'liver', 31, 40,   'g/L',  32, 44, 38, 44, 'good', NULL),
('m21_glob',   'bt202112', 'Globulin',         'Glob',   'liver', 32, 28,   'g/L',  23, 43, 25, 38, 'good', NULL),
('m21_bili',   'bt202112', 'T Bilirubin',      'Bili',   'liver', 33, 14,   'umol/L', NULL, 21, NULL, 17, 'optimal', NULL),
('m21_alp',    'bt202112', 'ALP',              'ALP',    'liver', 34, 55,   'U/L',  35, 110, 40, 100, 'good', NULL),
('m21_alt',    'bt202112', 'ALT',              'ALT',    'liver', 36, 14,   'U/L',  5, 40, 5, 25, 'optimal', NULL),
('m21_ggt',    'bt202112', 'GGT',              'GGT',    'liver', 37, 10,   'U/L',  5, 50, 5, 30, 'optimal', NULL),
-- Lipids
('m21_tc',     'bt202112', 'Total Cholesterol', 'TC',    'lipids', 40, 4.2,  'mmol/L', NULL, 5.6, NULL, 5.0, 'optimal', NULL),
-- Inflammation
('m21_crp',    'bt202112', 'CRP',              'CRP',    'inflammation', 50, 16, 'mg/L', NULL, 5, NULL, 0.5, 'high', 'Improving from 62 in 2019 but still elevated'),
-- Haematology
('m21_hb',     'bt202112', 'Haemoglobin',       'Hb',    'haematology', 70, 142, 'g/L', 135, 175, 140, 170, 'optimal', NULL),
('m21_hct',    'bt202112', 'Haematocrit',       'Hct',   'haematology', 71, 0.41, '',   0.40, 0.54, 0.40, 0.50, 'good', NULL),
('m21_rcc',    'bt202112', 'RCC',               'RCC',   'haematology', 72, 4.8,  '10*12/L', 4.5, 6.5, 4.5, 6.0, 'good', NULL),
('m21_mcv',    'bt202112', 'MCV',               'MCV',   'haematology', 73, 86,   'fL', 80, 100, 82, 98, 'good', NULL),
('m21_wcc',    'bt202112', 'WCC',               'WCC',   'haematology', 74, 8.7,  '10*9/L', 3.5, 10.0, 4.0, 8.0, 'good', 'Improving from 10.1 H'),
('m21_neut',   'bt202112', 'Neutrophils',       'Neut',  'haematology', 75, 6.51, '10*9/L', 1.5, 6.5, 2.0, 6.0, 'high', 'Flagged HIGH — still slightly elevated'),
('m21_lymph',  'bt202112', 'Lymphocytes',       'Lymph', 'haematology', 76, 1.37, '10*9/L', 1.0, 4.0, 1.2, 3.5, 'good', NULL),
('m21_mono',   'bt202112', 'Monocytes',         'Mono',  'haematology', 77, 0.65, '10*9/L', 0, 0.9, 0, 0.8, 'good', NULL),
('m21_eos',    'bt202112', 'Eosinophils',       'Eos',   'haematology', 78, 0.18, '10*9/L', 0, 0.6, 0, 0.5, 'optimal', NULL),
('m21_baso',   'bt202112', 'Basophils',         'Baso',  'haematology', 79, 0.03, '10*9/L', 0, 0.15, 0, 0.10, 'optimal', NULL),
('m21_plt',    'bt202112', 'Platelets',         'Plt',   'haematology', 80, 226,  '10*9/L', 150, 400, 150, 350, 'good', NULL),
-- Haemolysis
('m21_hemo',   'bt202112', 'Haemolysis Index',  'HI',   'other', 90, 22, 'mg/dL', NULL, 40, NULL, 40, 'good', NULL);

-- Update Dec 2021 panel metadata
UPDATE chl_bloodtest_panels
SET
  lab_name = 'Sullivan Nicolaides Pathology',
  health_score = 62,
  markers_optimal = 9,
  markers_good = 16,
  markers_warning = 0,
  markers_high = 2,
  markers_low = 0,
  analysis_summary = 'CRP 16 H still elevated (improving from 62). Lipids excellent (TC 4.2). Liver function excellent. Neutrophils 6.51 H slightly above range.',
  updated_at = NOW()
WHERE id = 'bt202112';

-- ============================================================================
-- STEP 6: Add/correct historical markers for May 2024 panel
-- ============================================================================
DELETE FROM chl_bloodtest_markers WHERE panel_id = 'bt202405';

INSERT INTO chl_bloodtest_markers (id, panel_id, name, short_name, category, sort_order, value, unit, ref_low, ref_high, optimal_low, optimal_high, status, notes) VALUES
-- Electrolytes
('m24_na',     'bt202405', 'Sodium',           'Na',     'electrolytes', 1,  143,   'mmol/L', 135, 145, 136, 142, 'good', NULL),
('m24_k',      'bt202405', 'Potassium',        'K',      'electrolytes', 2,  4.1,   'mmol/L', 3.5, 5.5, 3.8, 5.0, 'good', NULL),
('m24_cl',     'bt202405', 'Chloride',         'Cl',     'electrolytes', 3,  107,   'mmol/L', 95,  110, 98,  106, 'good', NULL),
('m24_bicarb', 'bt202405', 'Bicarbonate',      'HCO3',   'electrolytes', 4,  27,    'mmol/L', 20,  32,  22,  30,  'good', NULL),
('m24_ag',     'bt202405', 'Anion Gap',        'AG',     'electrolytes', 5,  9,     'mmol/L', NULL, 16,  NULL, 12, 'optimal', NULL),
('m24_ca',     'bt202405', 'Calcium (Corr.)',  'Ca',     'electrolytes', 6,  2.36,  'mmol/L', 2.10, 2.60, 2.20, 2.50, 'good', NULL),
('m24_phos',   'bt202405', 'Phosphate',        'PO4',    'electrolytes', 7,  1.21,  'mmol/L', 0.80, 1.50, 0.90, 1.40, 'good', NULL),
-- Kidney
('m24_urea',   'bt202405', 'Urea',             'Urea',   'kidney', 10, 6.5,  'mmol/L', 3.5, 8.5, 3.5, 7.0, 'good', NULL),
('m24_urate',  'bt202405', 'Urate',            'Urate',  'kidney', 11, 0.335,'mmol/L', 0.200, 0.500, 0.200, 0.400, 'good', NULL),
('m24_creat',  'bt202405', 'Creatinine',       'Creat',  'kidney', 12, 105,  'umol/L', 60, 110, 60, 100, 'warning', 'Elevated — possibly dehydration or creatine timing'),
('m24_egfr',   'bt202405', 'eGFR',             'eGFR',   'kidney', 13, 72,   'mL/min', 59, NULL, 90, NULL, 'warning', 'Concerning dip below normal threshold of 90'),
-- Metabolic
('m24_gluc',   'bt202405', 'Random Glucose',   'Gluc',   'metabolic', 20, 4.8,  'mmol/L', 3.6, 7.7, 4.0, 5.5, 'good', NULL),
('m24_hba1c',  'bt202405', 'HbA1c',            'HbA1c',  'metabolic', 21, 5.5,  '%',      NULL, 6.5, NULL, 5.2, 'borderline', 'Above longevity optimal'),
('m24_hba1ci', 'bt202405', 'HbA1c (IFCC)',     'HbA1c-I','metabolic', 22, 36,   'mmol/mol', NULL, 48, NULL, 33, 'borderline', NULL),
-- Liver
('m24_tp',     'bt202405', 'Total Protein',    'TP',     'liver', 30, 72,   'g/L',  63, 80, 65, 78, 'good', NULL),
('m24_alb',    'bt202405', 'Albumin',          'Alb',    'liver', 31, 42,   'g/L',  32, 44, 38, 44, 'optimal', NULL),
('m24_glob',   'bt202405', 'Globulin',         'Glob',   'liver', 32, 30,   'g/L',  23, 43, 25, 38, 'good', NULL),
('m24_bili',   'bt202405', 'T Bilirubin',      'Bili',   'liver', 33, 21,   'umol/L', NULL, 21, NULL, 17, 'high', 'At upper limit — flagged'),
('m24_alp',    'bt202405', 'ALP',              'ALP',    'liver', 34, 50,   'U/L',  35, 110, 40, 100, 'good', NULL),
('m24_ast',    'bt202405', 'AST',              'AST',    'liver', 35, 24,   'U/L',  10, 40, 10, 30, 'good', NULL),
('m24_alt',    'bt202405', 'ALT',              'ALT',    'liver', 36, 16,   'U/L',  5, 40, 5, 25, 'optimal', NULL),
('m24_ggt',    'bt202405', 'GGT',              'GGT',    'liver', 37, 12,   'U/L',  5, 50, 5, 30, 'optimal', NULL),
('m24_ld',     'bt202405', 'LD',               'LD',     'liver', 38, 169,  'U/L',  NULL, 250, NULL, 200, 'good', NULL),
-- Lipids
('m24_tc',     'bt202405', 'Total Cholesterol', 'TC',    'lipids', 40, 5.0,  'mmol/L', NULL, 5.6, NULL, 5.0, 'borderline', 'At optimal upper limit, trending up'),
('m24_tg',     'bt202405', 'Triglycerides',     'TG',    'lipids', 41, 0.7,  'mmol/L', NULL, 2.1, NULL, 0.9, 'optimal', NULL),
('m24_hdl',    'bt202405', 'HDL Cholesterol',   'HDL',   'lipids', 42, 1.42, 'mmol/L', 0.89, NULL, 1.5, NULL, 'good', NULL),
('m24_ldl',    'bt202405', 'LDL Cholesterol',   'LDL',   'lipids', 43, 3.3,  'mmol/L', NULL, 4.1, NULL, 2.6, 'borderline', 'Above longevity optimal'),
('m24_ratio',  'bt202405', 'Chol/HDL Ratio',    'TC/HDL','lipids', 44, 3.5,  'ratio',  NULL, 4.6, NULL, 3.5, 'good', NULL),
('m24_nhdlc',  'bt202405', 'Non HDLC',          'nHDLC', 'lipids', 45, 3.58, 'mmol/L', NULL, 3.81, NULL, 2.5, 'borderline', NULL),
-- Thyroid
('m24_tsh',    'bt202405', 'TSH',              'TSH',    'hormones', 55, 0.8, 'mIU/L', 0.3, 4.0, 1.0, 2.5, 'good', NULL),
-- Haematology
('m24_hb',     'bt202405', 'Haemoglobin',       'Hb',    'haematology', 70, 146, 'g/L', 135, 175, 140, 170, 'optimal', NULL),
('m24_hct',    'bt202405', 'Haematocrit',       'Hct',   'haematology', 71, 0.44, '',   0.40, 0.54, 0.40, 0.50, 'good', NULL),
('m24_rcc',    'bt202405', 'RCC',               'RCC',   'haematology', 72, 4.9,  '10*12/L', 4.5, 6.5, 4.5, 6.0, 'good', NULL),
('m24_mcv',    'bt202405', 'MCV',               'MCV',   'haematology', 73, 89,   'fL', 80, 100, 82, 98, 'good', NULL),
('m24_wcc',    'bt202405', 'WCC',               'WCC',   'haematology', 74, 7.1,  '10*9/L', 3.5, 10.0, 4.0, 8.0, 'good', NULL),
('m24_neut',   'bt202405', 'Neutrophils',       'Neut',  'haematology', 75, 5.13, '10*9/L', 1.5, 6.5, 2.0, 6.0, 'good', NULL),
('m24_lymph',  'bt202405', 'Lymphocytes',       'Lymph', 'haematology', 76, 1.46, '10*9/L', 1.0, 4.0, 1.2, 3.5, 'good', NULL),
('m24_mono',   'bt202405', 'Monocytes',         'Mono',  'haematology', 77, 0.36, '10*9/L', 0, 0.9, 0, 0.8, 'optimal', NULL),
('m24_eos',    'bt202405', 'Eosinophils',       'Eos',   'haematology', 78, 0.09, '10*9/L', 0, 0.6, 0, 0.5, 'optimal', NULL),
('m24_baso',   'bt202405', 'Basophils',         'Baso',  'haematology', 79, 0.04, '10*9/L', 0, 0.15, 0, 0.10, 'optimal', NULL),
('m24_plt',    'bt202405', 'Platelets',         'Plt',   'haematology', 80, 251,  '10*9/L', 150, 400, 150, 350, 'good', NULL),
-- Haemolysis
('m24_hemo',   'bt202405', 'Haemolysis Index',  'HI',   'other', 90, 4, 'mg/dL', NULL, 40, NULL, 40, 'optimal', NULL);

-- Update May 2024 panel metadata
UPDATE chl_bloodtest_panels
SET
  lab_name = 'Sullivan Nicolaides Pathology',
  health_score = 68,
  markers_optimal = 12,
  markers_good = 16,
  markers_warning = 2,
  markers_high = 1,
  markers_low = 0,
  analysis_summary = 'Lipids starting to trend up (TC 5.0). eGFR concerning dip to 72. Creatinine 105 elevated (possibly dehydration/creatine). CRP not tested this panel. Liver function excellent. HbA1c 5.5 borderline.',
  updated_at = NOW()
WHERE id = 'bt202405';

-- ============================================================================
-- STEP 7: Update action items (delete old fabricated ones, insert correct ones)
-- ============================================================================
DELETE FROM chl_bloodtest_actions WHERE panel_id = 'bt202601';

INSERT INTO chl_bloodtest_actions (id, panel_id, title, description, category, priority, related_markers, status, due_date) VALUES
('a26_01', 'bt202601',
  'Remove coconut oil from tea',
  'Coconut oil is ~82% saturated fat — primary dietary driver of LDL/ApoB increase. Switch to plain tea or add EVOO to meals instead. Expected LDL impact: -0.3 to -0.5 mmol/L.',
  'diet', 'urgent', ARRAY['Total Cholesterol','LDL Cholesterol','Apolipoprotein B'], 'pending', '2026-02-14'),

('a26_02', 'bt202601',
  'Reduce eggs from 4 to 2 daily',
  'Currently consuming ~800mg dietary cholesterol/day. 7-year TC uptrend (4.3→6.1) suggests hyper-responder status. Reduce to 2 eggs, add fat-free Greek yoghurt for protein. Expected LDL impact: -0.2 to -0.3 mmol/L.',
  'diet', 'urgent', ARRAY['Total Cholesterol','LDL Cholesterol','Apolipoprotein B'], 'pending', '2026-02-14'),

('a26_03', 'bt202601',
  'Make fish primary dinner protein (3-4x/week)',
  'Increase salmon/fish dinners to 3-4x/week. Omega-3 EPA/DHA lowers TG, raises HDL, reduces inflammation. Limit red meat to 1x/week max.',
  'diet', 'high', ARRAY['Triglycerides','HDL Cholesterol','CRP'], 'pending', '2026-02-14'),

('a26_04', 'bt202601',
  'Add soluble fiber to morning routine',
  '5g psyllium husk in water before first meal, or oats with breakfast. Soluble fiber binds bile acids and lowers LDL 5-10%. Expected LDL impact: -0.2 to -0.4 mmol/L.',
  'diet', 'high', ARRAY['LDL Cholesterol','Total Cholesterol'], 'pending', '2026-02-14'),

('a26_05', 'bt202601',
  'Start methylated B-complex',
  'Homocysteine 10.0 above longevity optimal (<8). Start methylfolate 800mcg + methylcobalamin 1000mcg + P5P 50mg. Danish ancestry = possible MTHFR variants.',
  'supplement', 'high', ARRAY['Homocysteine'], 'pending', '2026-02-21'),

('a26_06', 'bt202601',
  'Start high-dose omega-3 (3-4g EPA/DHA)',
  'For lipid support beyond dietary fish. Per AHA: 2-4g EPA/DHA for elevated triglycerides. Also supports HDL and anti-inflammatory effects.',
  'supplement', 'high', ARRAY['Triglycerides','HDL Cholesterol','Apolipoprotein B'], 'pending', '2026-02-21'),

('a26_07', 'bt202601',
  'Book appointment with Dr Friel',
  'Discuss: (1) Lipid profile and ApoB 1.12, (2) Request CAC scan, (3) Request APOE genotype test, (4) Timeline for pharmacological intervention if diet changes insufficient. Request statin/ezetimibe prescription to have ready if April retest unsatisfactory.',
  'doctor', 'high', ARRAY['Apolipoprotein B','LDL Cholesterol','Non HDLC'], 'pending', '2026-02-21'),

('a26_08', 'bt202601',
  'Increase resistance training to 4x/week',
  'Primary lever for SHBG reduction (76 H) and testosterone optimization. Compound movements with progressive overload. Per Huberman: resistance training is the #1 behavioral intervention for hormone optimization.',
  'lifestyle', 'medium', ARRAY['SHBG','Testosterone'], 'pending', '2026-02-28'),

('a26_09', 'bt202601',
  'Increase Vitamin D supplementation',
  'Current ~85 nmol/L below longevity optimal (100-150). Despite outdoor lifestyle, sunscreen blocks UVB. Add 2000-4000 IU D3 daily above Blueprint dose. Include Vitamin D in April retest.',
  'supplement', 'medium', ARRAY['Vitamin D'], 'pending', '2026-03-01'),

('a26_10', 'bt202601',
  'Schedule comprehensive retest — April 2026',
  'Repeat full Jan 2026 panel plus: Omega-3 Index, fasting glucose, Vitamin D, IGF-1, DHEA-S. FASTING collection (first thing AM, 12+ hours fasted). Consider stopping creatine 48h before draw for accurate eGFR.',
  'test', 'medium', NULL, 'pending', '2026-04-15'),

('a26_11', 'bt202601',
  'Consider boron supplementation for SHBG',
  'SHBG 76 (H) — elevated, reducing free T availability. Boron 6-10mg/day may reduce SHBG by ~10% (Naghii 2011). Generally safe. Start at 6mg/day.',
  'supplement', 'low', ARRAY['SHBG','Calculated Free Testosterone'], 'pending', '2026-03-01');

-- ============================================================================
-- STEP 8: Update the analysis_report for Jan 2026 panel
-- ============================================================================
UPDATE chl_bloodtest_panels
SET analysis_report = '# Blood Test Analysis — January 2026

## Summary
Comprehensive blood work collected 21 Jan 2026 at Sullivan Nicolaides Pathology. 47 markers tested across routine chemistry, lipid profile, apolipoproteins, CRP, thyroid, hormones, metabolic markers, and full blood count.

## Key Findings

### Excellent
- CRP 0.4 mg/L — exceptional inflammation control (below Attia elite <0.5 target). Dramatic improvement: 62→16→0.4
- Testosterone 27.0 nmol/L — top quartile for age 51 (ref 11-40)
- Free Testosterone 336 pmol/L — healthy despite high SHBG
- Liver function pristine — ALT 14, GGT 15, AST 26, all well within range
- Insulin 7 mU/L — good insulin sensitivity
- Haemoglobin 156 g/L — strong, trending up
- WCC normalized (7.9) — was 10.1 H in 2019

### Needs Attention — Lipid Profile
- TC 6.1 (H) — 7-year uptrend: 4.3→4.2→5.0→6.1
- LDL 4.1 (H) — at upper reference limit
- ApoB 1.12 — above longevity targets (ESC: <1.0 moderate risk)
- Non-HDLC 4.66 (H) — significant marker per NVDPA
- Drivers: coconut oil, 4 daily eggs, red meat frequency

### Monitor
- HbA1c 5.4% — above longevity optimal <5.2 (improved from 5.5)
- Homocysteine 10.0 — above optimal <8
- eGFR 86 — recovering from 72 dip in 2024
- SHBG 76 (H) — reducing free T availability
- Urea 7.4 — trending up but in range

## Strategy
3-month lifestyle-first intervention (dietary changes: remove coconut oil, reduce eggs, increase fish, add fiber). Retest April 2026. If ApoB still >1.0, discuss pharmacological options (statin/ezetimibe) with Dr Friel. Request CAC scan to assess actual plaque burden.

## Full Report
See docs/health-report-2026-01.md for comprehensive analysis including diet-to-marker connections, exercise recommendations, supplement assessment, and expert framework cross-reference.'
WHERE id = 'bt202601';

-- ============================================================================
-- DONE! Verify with:
-- SELECT name, value, unit, status FROM chl_bloodtest_markers WHERE panel_id = 'bt202601' ORDER BY sort_order;
-- SELECT * FROM chl_bloodtest_actions WHERE panel_id = 'bt202601' ORDER BY priority;
-- SELECT id, date, health_score, markers_optimal, markers_good, markers_warning, markers_high FROM chl_bloodtest_panels ORDER BY date;
-- ============================================================================
