# Phase 6 end-to-end: masters import, go-live checklist, help, backups / restore, health, CSP.
import os, re, sys, io, csv, gzip, subprocess, requests
B = os.environ.get('CSSD_URL', 'http://localhost:8080')
ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
LOG = ROOT + '/storage/logs/otp.log'
ok = bad = 0
def check(cond, msg):
    global ok, bad
    if cond: ok += 1; print('  ✓', msg)
    else: bad += 1; print('  ✗', msg)
def csrf(h):
    m = re.search(r'name="_csrf" value="([0-9a-f]+)"', h); return m.group(1) if m else ''
def otp(): return open(LOG).read().strip().splitlines()[-1].split()[-1]
def get(s, p): return s.get(B + p)
def post(s, p, data, page=None, files=None):
    d = dict(data); d['_csrf'] = csrf(get(s, page or p).text); return s.post(B + p, data=d, files=files)
def sql(q): return subprocess.run(['mysql', '-uroot', '-N', '-e', q], capture_output=True, text=True).stdout.strip()
def n(q): return int(sql(q) or 0)
def secret(r): return re.search(r'Temporary password <span class="secret">([^<]+)<', r.text).group(1).replace('&amp;', '&')
def err(t): return re.search(r'(Warning|Fatal error|Deprecated|Notice)\b[^<]{0,3}:', t)
def csvb(rows): o = io.StringIO(); csv.writer(o).writerows(rows); return o.getvalue().encode('utf-8-sig')
CODE, DB = 'GOLV', 'cssd_golv'
sql(f"DROP DATABASE IF EXISTS {DB}; DELETE FROM cssd_control.tenants WHERE client_code='{CODE}'; DELETE FROM cssd_control.login_attempts;")
subprocess.run(['rm', '-rf', ROOT + '/storage/backups/' + CODE])

print('Set-up')
L1 = requests.Session()
post(L1, '/admin/login', {'email': 'superadmin@example.com', 'password': 'Admin#2026x'}); post(L1, '/admin/otp', {'code': otp()})
pw = secret(post(L1, '/admin/tenants/new', {'client_code': CODE, 'name': 'Go-Live General Hospital', 'hospital_type': 'Single hospital', 'status': 'active', 'deployment': 'cloud', 'his_type': 'none', 'country': 'India',
    'valid_till': '2027-12-31', 'admin_name': 'G Admin', 'admin_username': 'admin', 'admin_email': 'gadmin@example.com'}, '/admin/tenants/new'))
A = requests.Session()
post(A, '/login', {'mode': 'password', 'client_code': CODE, 'username': 'admin', 'password': pw}); post(A, '/login/otp', {'code': otp()})
post(A, '/app/profile', {'action': 'password', 'current_password': pw, 'new_password': 'Golv#2026a', 'confirm_password': 'Golv#2026a'}, '/app/profile')

print('Go-live checklist (empty hospital)')
r = get(A, '/app/setup')
pct0 = int(re.search(r'<b style="font-size:22px">(\d+)%', r.text).group(1))
check(r.status_code == 200 and not err(r.text) and pct0 < 50, f'checklist renders, starts low ({pct0}%)')
check('Go-live &amp; import' in get(A, '/app').text, 'menu link for hospital admin')

print('Templates')
t = get(A, '/app/setup/template/instrument_sets.csv')
check(t.headers['content-type'].startswith('text/csv') and t.text.lstrip('﻿').startswith('code,name,department_code,pack_type,method') and 'units' in t.text.splitlines()[0], 'set template has friendly columns incl. units')
check(get(A, '/app/setup/template/nope.csv').status_code == 404, 'unknown template → 404')

def imp(kind, rows, commit=True, raw=None, name='f.csv'):
    r = post(A, '/app/setup/import', {'kind': kind, 'step': 'check'}, f'/app/setup/import?kind={kind}', files={'file': (name, raw if raw is not None else csvb(rows))})
    errs = re.findall(r'<tr><td>(\d+)</td><td>([^<]+)</td></tr>', r.text)
    tok = re.search(r'name="token" value="([a-f0-9]+)"', r.text)
    if commit and tok and not errs:
        r = post(A, '/app/setup/import', {'kind': kind, 'step': 'commit', 'token': tok.group(1)}, f'/app/setup/import?kind={kind}')
    return r, errs

print('Import masters in order')
r, e = imp('departments', [['branch_code', 'code', 'name', 'type', 'location'], ['MAIN', 'ot1b', 'OT Block B', 'ot', '2F'], ['MAIN', 'WARD5', 'Ward 5', 'Ward', '5F'], ['MAIN', 'ER2', 'Casualty', 'Emergency', 'GF']])
check(not e and 'Departments / OTs: 3 added' in r.text, 'departments imported (code upper-cased, type case-tolerant)')
check(sql(f"SELECT type FROM {DB}.departments WHERE code='OT1B'") == 'OT', 'select value normalised to "OT"')
r, e = imp('instruments', [['code', 'name', 'category', 'unit_cost', 'max_cycles', 'material', 'track_individually'],
    ['SC-1', 'Mayo scissors', 'Scissors', '1500', '1000', 'Stainless steel', 'no'], ['FC-1', 'Artery forceps', 'Clamps / haemostats', '600', '', '', ''], ['NEWCAT-1', 'Special retractor', 'Brand-new category', '9000', '500', '', 'yes']])
check(not e and 'Instruments: 3 added' in r.text, 'instruments imported')
check(n(f"SELECT COUNT(*) FROM {DB}.instrument_categories WHERE name='Brand-new category'") == 1, 'unknown category auto-created')
check(sql(f"SELECT track_individually FROM {DB}.instruments WHERE code='NEWCAT-1'") == '1', 'yes → bool 1')
r, e = imp('instrument_sets', [['code', 'name', 'department_code', 'pack_type', 'method', 'par_level', 'tat_hours', 'is_critical', 'units'],
    ['MAJ', 'Major set', 'OT1B', 'Rigid container', 'Steam', '3', '8', 'yes', '3'], ['DRS', 'Dressing set', 'WARD5', 'Peel pouch', 'steam', '6', '6', 'no', '6']])
check(not e and 'Sets / trays: 2 added' in r.text, 'sets imported')
check(n(f"SELECT COUNT(*) FROM {DB}.set_units u JOIN {DB}.instrument_sets s ON s.id=u.set_id WHERE s.code='DRS'") == 6 and sql(f"SELECT tag_code FROM {DB}.set_units u JOIN {DB}.instrument_sets s ON s.id=u.set_id WHERE s.code='MAJ' ORDER BY unit_no LIMIT 1") == 'MAJ-01', 'physical trays created with tag codes')
r, e = imp('set_items', [['set_code', 'instrument_code', 'qty'], ['MAJ', 'SC-1', '1'], ['MAJ', 'FC-1', '8'], ['DRS', 'FC-1', '1']])
check(not e and n(f"SELECT SUM(qty) FROM {DB}.set_items") == 10, 'tray lists imported')
r, e = imp('set_items', [['set_code', 'instrument_code', 'qty'], ['MAJ', 'FC-1', '10']])
check('0 added, 1 updated' in r.text and n(f"SELECT qty FROM {DB}.set_items si JOIN {DB}.instruments i ON i.id=si.instrument_id WHERE i.code='FC-1' AND si.set_id=(SELECT id FROM {DB}.instrument_sets WHERE code='MAJ')") == 10, 're-upload updates the quantity')
r, e = imp('sterilizers', [['branch_code', 'code', 'name', 'type', 'install_date', 'cost_per_cycle'], ['MAIN', 'AC1', 'Autoclave 1', 'Steam', '01/04/2023', '1200']])
check(not e and sql(f"SELECT install_date FROM {DB}.sterilizers WHERE code='AC1'") == '2023-04-01', 'sterilizer imported; dd/mm/yyyy date accepted')
r, e = imp('washers', [['branch_code', 'code', 'name', 'type'], ['MAIN', 'WD1', 'Washer 1', 'Washer-disinfector']])
check(not e, 'washer imported')
r, e = imp('vendors', [['name', 'type', 'phone'], ['Ortho Lend', 'Loaner / implant company', '98000']])
check(not e, 'vendor imported (matched on name)')
r, e = imp('surgeons', [['name', 'speciality', 'department_code'], ['Dr. Test Surgeon', 'Ortho', 'OT1B']])
check(not e, 'surgeon imported')
r, e = imp('procedures', [['name', 'speciality', 'department_code'], ['Knee arthroscopy', 'Ortho', 'OT1B']])
r2, e2 = imp('procedure_sets', [['procedure', 'set_code', 'qty'], ['Knee arthroscopy', 'MAJ', '1']])
check(not e and not e2 and n(f"SELECT COUNT(*) FROM {DB}.procedure_sets") == 1, 'procedure + preference card imported')

print('Validation & safety')
r, e = imp('instrument_sets', [['code', 'name', 'method'], ['OK1', 'Fine set', 'Steam'], ['BAD1', 'Bad dept', 'Steam']], commit=False)
r, e = imp('instrument_sets', [['code', 'name', 'department_code', 'method'], ['OK1', 'Fine set', 'OT1B', 'Steam'], ['BAD1', 'Bad set', 'NOWHERE', 'Steam'], ['BAD2', 'Bad method', 'OT1B', 'Microwave']])
check(len(e) == 2 and any('NOWHERE' in m for _, m in e) and any(l == '4' for l, _ in e), 'bad department code and bad method reported with line numbers')
check('Nothing has been saved' in r.text and 'name="token"' not in r.text and n(f"SELECT COUNT(*) FROM {DB}.instrument_sets WHERE code='OK1'") == 0, 'all-or-nothing: no import button, nothing saved')
r, e = imp('instruments', [['name', 'unit_cost'], ['No code', '5']])
check(any('Missing columns' in m and 'code' in m for _, m in e), 'missing required column reported')
r, e = imp('instruments', None, raw=b'PK\x03\x04xlsxdata', name='book.xlsx')
check('That is an Excel file' in r.text, 'xlsx upload gets a "save as CSV" message')
r, e = imp('instruments', None, raw='code;name;unit_cost\nSC-1;;1750\n'.encode(), commit=True)
check(not e and sql(f"SELECT CONCAT(name,'|',unit_cost,'|',max_cycles) FROM {DB}.instruments WHERE code='SC-1'") == 'Mayo scissors|1750.00|1000', '; delimiter accepted; blank cell keeps current value')
r = post(A, '/app/setup/import', {'kind': 'users', 'step': 'commit', 'token': 'a' * 24}, '/app/setup/import')
check('expired' in r.text, 'forged / expired token refused')

print('Users import')
r, e = imp('users', [['name', 'username', 'role', 'email', 'department_code', 'pin'], ['Tara Tech', 'tara', 'CSSD Technician', '', '', '4826'], ['Sam Sup', 'sam', 'CSSD Supervisor', 'sam@example.com', '', ''],
    ['Nina Nurse', 'nina', 'OT / Ward Nurse', '', 'OT1B', ''], ['Bad Role', 'badrole', 'Astronaut', '', '', '']], commit=False)
check(any('Astronaut' in m for _, m in e), 'unknown role rejected')
r, e = imp('users', [['name', 'username', 'role', 'email', 'department_code', 'pin'], ['Tara Tech', 'tara', 'CSSD Technician', '', '', '4826'], ['Sam Sup', 'sam', 'CSSD Supervisor', 'sam@example.com', '', ''],
    ['Nina Nurse', 'nina', 'OT / Ward Nurse', '', 'OT1B', '']])
check(not e and 'New user logins' in r.text, 'users imported; logins panel shown')
c = get(A, '/app/setup/credentials.csv')
rows = list(csv.reader(io.StringIO(c.text)))
check(len(rows) == 4 and rows[0][3] == 'Temporary password', 'one-time credentials CSV')
check(get(A, '/app/setup/credentials.csv').status_code == 404, 'second download refused')
creds = {r[1]: r[3] for r in rows[1:]}
T = requests.Session()
rr = post(T, '/login', {'mode': 'password', 'client_code': CODE, 'username': 'tara', 'password': creds['tara']}, '/login')
check('/app/profile' in rr.url, 'imported user must set a new password at first sign-in')
post(T, '/app/profile', {'action': 'password', 'current_password': creds['tara'], 'new_password': 'Tara#2026a', 'confirm_password': 'Tara#2026a'}, '/app/profile')
T2 = requests.Session()
rr = post(T2, '/login', {'mode': 'pin', 'client_code': CODE, 'username': 'tara', 'pin': '4826'}, '/login?mode=pin')
check('/app' in rr.url, 'imported floor PIN works')
check(get(T2, '/app/setup').status_code == 403 and get(T2, '/app/setup/import').status_code == 403, 'technician cannot open set-up / import')
check(n(f"SELECT COUNT(*) FROM {DB}.audit_log WHERE action='imported'") >= 10, 'every import audited')

print('Checklist after import')
r = get(A, '/app/setup')
pct1 = int(re.search(r'<b style="font-size:22px">(\d+)%', r.text).group(1))
check(pct1 > pct0 and pct1 < 100, f'readiness rose to {pct1}% (dry run still pending)')

print('Help')
h = get(T2, '/app/help')
check(h.status_code == 200 and h.text.index('CSSD Technician') < h.text.index('CSSD Supervisor') and '— your role' in h.text, 'help shows own role first')
check('window.print()' in get(T2, '/app/help?print=1&role=CSSD%20Technician').text, 'printable role card')

print('Backups (Level 1)')
tid = sql(f"SELECT id FROM cssd_control.tenants WHERE client_code='{CODE}'")
r = post(L1, f'/admin/tenants/{tid}/backup', {}, f'/admin/tenants/{tid}')
check('Backup created' in r.text, 'Back up now')
name = re.search(rf'({CODE}-\d{{8}}-\d{{6}}(?:-\d+)?\.sql\.gz)', r.text).group(1)
d = get(L1, f'/admin/tenants/{tid}/backup/{name}')
check(d.status_code == 200 and gzip.decompress(d.content).startswith(b'-- CareSterile backup'), 'download is a valid gzip dump')
check(get(L1, f'/admin/tenants/{tid}/backup/..%2F..%2Fconfig.php').status_code == 404 and get(L1, f'/admin/tenants/{tid}/backup/DEMO-20260101-000000.sql.gz').status_code == 404, 'path tricks / other client files refused')
check(get(requests.Session(), f'/admin/tenants/{tid}/backup/{name}').url.endswith('/admin/login'), 'download needs Level 1 login')
check(n("SELECT COUNT(*) FROM cssd_control.platform_audit WHERE action IN ('backup_created','backup_downloaded')") >= 2, 'backup actions audited')
check(requests.get(B + '/storage/backups/' + CODE + '/' + name).status_code in (403, 404), 'backup folder not reachable from the web')

print('CLI backup / restore round trip')
def checksum():
    tabs = sql(f"SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='{DB}' AND table_name<>'audit_log'").split(',')
    return sql(' '.join(f'CHECKSUM TABLE {DB}.`{t}`;' for t in tabs))
before = checksum()
o = subprocess.run(['php', ROOT + '/install/backup.php', f'--code={CODE}'], capture_output=True, text=True)
check(o.returncode == 0 and CODE in o.stdout, 'install/backup.php --code works')
f = sorted(x for x in os.listdir(ROOT + '/storage/backups/' + CODE) if x.endswith('.sql.gz'))[-1]
sql(f"UPDATE {DB}.instruments SET name='CHANGED'; DELETE FROM {DB}.set_items;")
o = subprocess.run(['php', ROOT + '/install/restore.php', f'--code={CODE}', f'--file={ROOT}/storage/backups/{CODE}/{f}'], capture_output=True, text=True)
check('Re-run with --yes' in o.stdout and sql(f"SELECT COUNT(*) FROM {DB}.set_items") == '0', 'restore refuses without --yes')
o = subprocess.run(['php', ROOT + '/install/restore.php', f'--code={CODE}', f'--file={ROOT}/storage/backups/{CODE}/{f}', '--yes'], capture_output=True, text=True)
check('Restored' in o.stdout and 'Safety backup' in o.stdout, 'restore takes a safety backup then restores')
check(checksum() == before, 'restored data identical table-for-table')
check(get(A, '/app/setup').status_code == 200, 'hospital works after restore')

print('Health & security')
subprocess.run(['php', ROOT + '/install/worker.php'], capture_output=True)
r = get(L1, '/admin/health')
check(r.status_code == 200 and not err(r.text) and 'Worker (every 5 min)' in r.text and CODE in r.text, 'Level 1 system health page')
check(re.search(r'Worker \(every 5 min\)</b><br><span class="muted small">last run', r.text) is not None, 'worker heartbeat recorded')
j = requests.get(B + '/health')
check(j.status_code == 200 and j.json().get('ok') is True and 'tenants' not in j.text, '/health for uptime monitors (no details)')
check("default-src 'self'" in get(A, '/app').headers.get('Content-Security-Policy', ''), 'Content-Security-Policy header set')
o = subprocess.run(['php', ROOT + '/install/check.php'], capture_output=True, text=True)
check('Server' in o.stdout and 'Databases' in o.stdout, 'install/check.php pre-flight runs')

print(f'\n{ok} passed, {bad} failed')
sys.exit(1 if bad else 0)
