"""ICT World hosted-checkout proof-of-concept. Not a regulated payment gateway.
Run behind HTTPS; not production hardened. No SMS-based auto-approval.
"""
import hashlib, hmac, json, os, secrets, sqlite3, time, urllib.parse, ipaddress, socket
from contextlib import contextmanager
from decimal import Decimal, InvalidOperation
from fastapi import FastAPI, Header, HTTPException, Request
from fastapi.responses import HTMLResponse, RedirectResponse
from pydantic import BaseModel, Field

DB = os.environ.get('ICTW_DB', 'ictw.sqlite3')
BASE = os.environ.get('ICTW_PUBLIC_BASE', 'http://127.0.0.1:8000').rstrip('/')
ADMIN = os.environ.get('ICTW_BOOTSTRAP_ADMIN_TOKEN', '')
app = FastAPI(title='ICT World Hosted Checkout MVP', version='0.6.0')

@contextmanager
def db():
    connection = sqlite3.connect(DB, timeout=10)
    connection.row_factory = sqlite3.Row
    connection.execute('PRAGMA foreign_keys=ON')
    try:
        yield connection
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    finally:
        connection.close()

def init_db():
    with db() as c:
        c.executescript('''
        CREATE TABLE IF NOT EXISTS clients (
          id TEXT PRIMARY KEY, name TEXT NOT NULL, website TEXT NOT NULL,
          webhook TEXT, api_hash TEXT NOT NULL UNIQUE, webhook_secret TEXT NOT NULL,
          personal_number TEXT, merchant_number TEXT, qr_url TEXT,
          amount_fee_per_thousand TEXT NOT NULL DEFAULT '20', per_tx_fee TEXT NOT NULL DEFAULT '3'
        );
        CREATE TABLE IF NOT EXISTS orders (
          id TEXT PRIMARY KEY, client_id TEXT NOT NULL REFERENCES clients(id),
          merchant_order_id TEXT NOT NULL, amount TEXT NOT NULL, mode TEXT NOT NULL,
          status TEXT NOT NULL DEFAULT 'pending', proof_ref TEXT, created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL, checkout_token_hash TEXT NOT NULL UNIQUE,
          fee_amount TEXT, fee_tx TEXT,
          UNIQUE(client_id, merchant_order_id)
        );
        CREATE TABLE IF NOT EXISTS merchant_sessions (
          token_hash TEXT PRIMARY KEY, client_id TEXT NOT NULL REFERENCES clients(id),
          expires_at INTEGER NOT NULL, csrf_hash TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS orders_status_idx ON orders(status, created_at);
        ''')
init_db()

def admin_only(token):
    if not ADMIN or not token or not secrets.compare_digest(token, ADMIN):
        raise HTTPException(403, 'Admin bootstrap token required')

def normalize_https(url, allow_empty=False):
    if allow_empty and not url: return None
    parts = urllib.parse.urlparse(str(url))
    if parts.scheme != 'https' or not parts.hostname or parts.username or parts.password or parts.fragment:
        raise HTTPException(422, 'Only plain HTTPS URLs are supported')
    # Prevent obvious loopback/internal destination for webhook SSRF (DNS rebind requires additional egress controls).
    hostname = parts.hostname.lower()
    if hostname in ('localhost',) or hostname.endswith(('.localhost', '.local', '.internal')):
        raise HTTPException(422, 'Internal hosts are not allowed')
    try:
        addr = ipaddress.ip_address(hostname)
        if not addr.is_global: raise HTTPException(422, 'Public HTTPS destination required')
    except ValueError:
        pass
    return url

def money(value):
    try:
        amt = Decimal(str(value)).quantize(Decimal('0.01'))
        if not amt.is_finite() or amt <= 0 or amt > Decimal('10000000'):
            raise ValueError
        return str(amt)
    except (ValueError, InvalidOperation):
        raise HTTPException(422, 'Invalid amount')

class ClientCreate(BaseModel):
    name: str = Field(min_length=2, max_length=100)
    website: str
    webhook: str | None = None
    personal_number: str | None = None
    merchant_number: str | None = None
    qr_url: str | None = None
    amount_fee_per_thousand: str = '20'
    per_tx_fee: str = '3'

@app.post('/admin/clients')
def create_client(body: ClientCreate, x_admin_token: str | None = Header(None)):
    admin_only(x_admin_token)
    website = normalize_https(body.website)
    webhook = normalize_https(body.webhook, True)
    qr_url = normalize_https(body.qr_url, True)
    for val in (body.amount_fee_per_thousand, body.per_tx_fee):
        try:
            n = Decimal(val)
            if not n.is_finite() or n < 0 or n > 100000: raise ValueError
        except (InvalidOperation, ValueError):
            raise HTTPException(422, 'Invalid fee')
    client_id = 'ICTW-' + secrets.token_hex(6).upper()
    raw_key = 'ictw_live_' + secrets.token_urlsafe(32)
    wh_secret = secrets.token_urlsafe(32)
    with db() as c:
        c.execute('INSERT INTO clients VALUES(?,?,?,?,?,?,?,?,?,?,?)',
            (client_id,body.name,website,webhook,hashlib.sha256(raw_key.encode()).hexdigest(),wh_secret,
             body.personal_number,body.merchant_number,qr_url,body.amount_fee_per_thousand,body.per_tx_fee))
    return {'client_id':client_id,'api_key':raw_key,'webhook_secret':wh_secret,
            'warning':'Secrets shown once. Store server-side only. Demo does not automatically send webhooks.'}

def require_client(bearer):
    if not bearer or not bearer.startswith('Bearer '): raise HTTPException(401, 'Bearer token required')
    hashed = hashlib.sha256(bearer[7:].encode()).hexdigest()
    with db() as c:
        client = c.execute('SELECT * FROM clients WHERE api_hash=?',(hashed,)).fetchone()
    if not client: raise HTTPException(401, 'Invalid API key')
    return client

class CheckoutCreate(BaseModel):
    merchant_order_id: str = Field(min_length=1,max_length=100)
    amount: str
    mode: str = Field(pattern='^(personal|merchant)$')

@app.post('/v1/checkouts')
def new_checkout(body: CheckoutCreate, authorization: str | None = Header(None)):
    client = require_client(authorization)
    amt=money(body.amount)
    if body.mode=='personal' and not client['personal_number']: raise HTTPException(422,'Personal payment not configured')
    if body.mode=='merchant' and not (client['merchant_number'] or client['qr_url']): raise HTTPException(422,'Merchant payment not configured')
    token = secrets.token_urlsafe(32)
    oid = 'PAY-' + secrets.token_hex(9).upper()
    ts = int(time.time())
    with db() as c:
        try:
            c.execute('''INSERT INTO orders(id,client_id,merchant_order_id,amount,mode,status,
            created_at,updated_at,checkout_token_hash) VALUES(?,?,?,?,?,?,?,?,?)''',
            (oid,client['id'],body.merchant_order_id,amt,body.mode,'pending',ts,ts,hashlib.sha256(token.encode()).hexdigest()))
        except sqlite3.IntegrityError:
            raise HTTPException(409,'merchant_order_id already exists; use another unique order ID')
    return {'payment_id':oid,'checkout_url':f'{BASE}/pay/{token}', 'status':'pending'}

def token_order(token):
    h=hashlib.sha256(token.encode()).hexdigest()
    with db() as c:
        row=c.execute('''SELECT o.*, c.name AS business, c.personal_number, c.merchant_number,
             c.qr_url FROM orders o JOIN clients c ON o.client_id=c.id WHERE checkout_token_hash=?''',(h,)).fetchone()
    if not row: raise HTTPException(404,'Payment link not found')
    return row

from html import escape
@app.get('/pay/{token}', response_class=HTMLResponse)
def checkout_page(token: str):
    o = token_order(token)
    number = o['personal_number'] if o['mode']=='personal' else o['merchant_number']
    image = f'<img src="{escape(o["qr_url"],quote=True)}" alt="Merchant QR" style="max-width:300px;width:100%;height:auto">' if o['mode']=='merchant' and o['qr_url'] else ''
    form = f'''<form method="post" action="/pay/{escape(token,quote=True)}/proof">
      <label>Transaction ID (optional if screenshot reference)<input name="transaction_id" maxlength="120"></label>
      <label>Screenshot reference URL (demo only)<input name="screenshot_ref" maxlength="500" placeholder="HTTPS URL of already-uploaded proof"></label>
      <button>Submit payment evidence</button></form>'''
    # No external file upload in demo: proof reference is descriptive only, not payment verification.
    return f'''<!doctype html><html lang="bn"><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
    <title>ICT World Checkout</title><style>body{{font:16px system-ui;max-width:490px;margin:40px auto;padding:20px}}label{{display:block;margin:16px 0}}input{{display:block;padding:12px;width:94%}}button{{padding:12px;background:#1769aa;color:white;border:0}}.qr{{text-align:center}}</style>
    <h2>{escape(o['business'])} — পেমেন্ট</h2><p>অর্ডার: {escape(o['merchant_order_id'])}</p><h3>৳{escape(o['amount'])}</h3>
    <p>পদ্ধতি: {escape(o['mode'])} | অবস্থা: {escape(o['status'])}</p>
    <p>Receiving Number: {escape(number or 'QR only')}</p><div class="qr">{image}</div>
    <p>টাকা পাঠানোর পরে TrxID বা Screenshot Reference জমা দিন। এটি নিজে পেমেন্ট যাচাই নয়।</p>{form}
    <p>১৫ মিনিটেও নিশ্চিত SMS না এলে Pending Review হবে; Paid হবে না।</p></html>'''

# Form needs python-multipart; instead parse URL-encoded body directly, size-limited.
@app.post('/pay/{token}/proof', response_class=HTMLResponse)
async def proof(token: str, request: Request):
    o=token_order(token)
    raw=await request.body()
    if len(raw)>4096: raise HTTPException(413,'Too large')
    values=urllib.parse.parse_qs(raw.decode('utf-8',errors='replace'))
    txid=(values.get('transaction_id',[''])[0]).strip()[:120]
    ref=(values.get('screenshot_ref',[''])[0]).strip()[:500]
    if not txid and not ref: raise HTTPException(422,'TrxID or screenshot reference is required')
    if ref: normalize_https(ref)
    if o['status']!='pending': raise HTTPException(409,'Payment submission closed')
    with db() as c:
        c.execute('UPDATE orders SET proof_ref=?, updated_at=? WHERE id=?',
            (json.dumps({'trxid':txid,'screenshot_ref':ref}),int(time.time()),o['id']))
    return HTMLResponse('<meta charset="utf-8"><h2>প্রমাণ জমা হয়েছে</h2><p>পেমেন্ট Pending রয়েছে। আসল টাকা পাওয়ার নিশ্চয়তা নয়।</p>')

@app.get('/v1/payments/{payment_id}')
def payment_status(payment_id: str, authorization: str | None = Header(None)):
    client = require_client(authorization)
    with db() as c:
        r=c.execute('SELECT * FROM orders WHERE id=? AND client_id=?',(payment_id,client['id'])).fetchone()
    if not r: raise HTTPException(404,'Not found')
    return {'payment_id':r['id'],'order_id':r['merchant_order_id'],'amount':r['amount'],'status':r['status']}

@app.post('/admin/review-timeouts')
def review_timeouts(x_admin_token: str | None = Header(None)):
    admin_only(x_admin_token)
    with db() as c:
        cur=c.execute("UPDATE orders SET status='pending_review',updated_at=? WHERE status='pending' AND created_at<?",
            (int(time.time()), int(time.time())-900))
    return {'moved_to_pending_review':cur.rowcount}

class AdminDecision(BaseModel):
    payment_id: str
    status: str = Field(pattern='^(verified_success|cancelled|pending_review)$')

@app.post('/admin/decisions')
def manual_decision(body: AdminDecision, x_admin_token: str | None = Header(None)):
    admin_only(x_admin_token)
    with db() as c:
        o=c.execute('SELECT * FROM orders WHERE id=?',(body.payment_id,)).fetchone()
        if not o: raise HTTPException(404,'Payment not found')
        if o['status'] in ('verified_success','cancelled'): raise HTTPException(409,'Terminal status is immutable')
        if body.status=='verified_success':
            client=c.execute('SELECT * FROM clients WHERE id=?',(o['client_id'],)).fetchone()
            amt=Decimal(o['amount'])
            fee1=(amt*Decimal(client['amount_fee_per_thousand'])/1000).quantize(Decimal('0.01'))
            fee2=Decimal(client['per_tx_fee']).quantize(Decimal('0.01'))
            c.execute('UPDATE orders SET status=?,fee_amount=?,fee_tx=?,updated_at=? WHERE id=?',
                (body.status,str(fee1),str(fee2),int(time.time()),body.payment_id))
        else:
            c.execute('UPDATE orders SET status=?,updated_at=? WHERE id=?',(body.status,int(time.time()),body.payment_id))
    return {'payment_id':body.payment_id,'status':body.status,'note':'Manual decision. SMS auto-approval is intentionally not implemented.'}

@app.get('/health')
def health(): return {'ok':True,'demo':True}


# Merchant-facing MVP. API keys remain server-side after one-time login.
# Staging only: add reverse-proxy rate limits, real staff accounts/2FA and audit trails before production.
def page(body, title='ICT World Merchant'):
    return HTMLResponse('''<!doctype html><html lang="bn"><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>{escape(title)}</title><style>body{font:16px system-ui;max-width:920px;margin:25px auto;padding:16px;background:#f7fafc;color:#14243a}
main{background:#fff;border:1px solid #dce6ef;border-radius:12px;padding:22px}input,select,button{padding:10px;margin:5px 0;max-width:100%;box-sizing:border-box}
input{width:100%}button{border:0;border-radius:6px;background:#145b9c;color:#fff;cursor:pointer}table{border-collapse:collapse;width:100%;font-size:14px}td,th{border-bottom:1px solid #e2e8f0;padding:10px;text-align:left;overflow-wrap:anywhere}
label{display:block;margin:10px 0}a{color:#145b9c}.muted{color:#576273}.link{overflow-wrap:anywhere;background:#eef4f9;padding:12px}</style><main>''' + body + '</main></html>')

def form_fields(request_data):
    return urllib.parse.parse_qs(request_data.decode('utf-8', errors='replace'))

def field(data, key):
    return data.get(key, [''])[0].strip()

def merchant_session(request):
    raw=request.cookies.get('ictw_merchant_session', '')
    if not raw or len(raw)>128: raise HTTPException(401,'Please sign in at /merchant')
    digest=hashlib.sha256(raw.encode()).hexdigest()
    with db() as c:
        session=c.execute('SELECT * FROM merchant_sessions WHERE token_hash=? AND expires_at>?',(digest,int(time.time()))).fetchone()
        if not session: raise HTTPException(401,'Session expired; sign in again')
        client=c.execute('SELECT * FROM clients WHERE id=?',(session['client_id'],)).fetchone()
    return client,session

def csrf_check(data, session):
    candidate=field(data,'csrf')
    if not candidate or not secrets.compare_digest(hashlib.sha256(candidate.encode()).hexdigest(),session['csrf_hash']):
        raise HTTPException(403,'Invalid form token')

def merchant_csrf(request):
    raw=request.cookies.get('ictw_merchant_csrf','')
    if not raw: raise HTTPException(403,'Missing form token')
    return raw

@app.get('/merchant', response_class=HTMLResponse)
def merchant_login_form():
    return page('<h2>ICT World — Merchant Login</h2><p>নিজের API Key দিয়ে নিরাপদ HTTPS সংযোগে Login করুন।</p>'
      '<form method="post" action="/merchant/login"><label>API Key<input type="password" name="api_key" required autocomplete="off"></label>'
      '<button>Login</button></form><p class="muted">এটি পরীক্ষামূলক Dashboard। লাইভ ব্যবহারের জন্য পৃথক User Login/2FA প্রয়োজন।</p>')

@app.post('/merchant/login')
async def merchant_login(request: Request):
    raw=await request.body()
    if len(raw)>2048: raise HTTPException(413,'Too large')
    key=field(form_fields(raw),'api_key')
    client=require_client('Bearer '+key)
    token=secrets.token_urlsafe(32); csrf=secrets.token_urlsafe(32)
    with db() as c:
        c.execute('INSERT INTO merchant_sessions VALUES(?,?,?,?)',
          (hashlib.sha256(token.encode()).hexdigest(),client['id'],int(time.time())+8*3600,hashlib.sha256(csrf.encode()).hexdigest()))
    response=RedirectResponse('/merchant/dashboard',status_code=303)
    response.set_cookie('ictw_merchant_session',token,httponly=True,secure=BASE.startswith('https://'),samesite='strict',max_age=8*3600,path='/merchant')
    response.set_cookie('ictw_merchant_csrf',csrf,httponly=True,secure=BASE.startswith('https://'),samesite='strict',max_age=8*3600,path='/merchant')
    return response

@app.get('/merchant/dashboard',response_class=HTMLResponse)
def merchant_dashboard(request: Request):
    client,_=merchant_session(request)
    csrf=merchant_csrf(request)
    with db() as c:
        orders=c.execute('SELECT * FROM orders WHERE client_id=? ORDER BY created_at DESC LIMIT 100',(client['id'],)).fetchall()
    rows=[]
    for o in orders:
        fee='—'
        if o['status']=='verified_success':
            fee=str(Decimal(o['fee_amount'] or '0')+Decimal(o['fee_tx'] or '0'))
        rows.append('<tr><td>'+escape(o['merchant_order_id'])+'</td><td>৳'+escape(o['amount'])+'</td><td>'+escape(o['mode'])+'</td><td>'+escape(o['status'])+'</td><td>'+escape(fee)+'</td></tr>')
    modes=[]
    if client['personal_number']: modes.append('<option value="personal">Personal</option>')
    if client['merchant_number'] or client['qr_url']: modes.append('<option value="merchant">Merchant</option>')
    content='<h2>Merchant Dashboard — '+escape(client['name'])+'</h2><p class="muted">Client ID: '+escape(client['id'])+' | Website: '+escape(client['website'])+'</p>'
    content+='<p><a href="/merchant/settings">⚙ Payment & Website Settings</a></p>'
    content+='<h3>Generate Payment Link</h3><p>প্রতিটি অর্ডারের জন্য আলাদা Amount ও Order ID দিন।</p>'
    content+='<form method="post" action="/merchant/create-link"><input type="hidden" name="csrf" value="'+escape(csrf,quote=True)+'">'
    content+='<label>Order ID <input name="order_id" maxlength="100" required></label><label>Amount (BDT) <input name="amount" type="number" step="0.01" min="0.01" required></label>'
    content+='<label>Payment Mode <select name="mode">'+''.join(modes)+'</select></label><button>Create Payment Link</button></form>'
    content+='<h3>Recent Transactions</h3><p class="muted">Charge শুধু Verified Success লেনদেনের জন্য। এটি Billing হিসাব; টাকা স্বয়ংক্রিয়ভাবে কাটা হয়নি।</p>'
    content+='<div style="overflow-x:auto"><table><thead><tr><th>Order</th><th>Amount</th><th>Mode</th><th>Status</th><th>Charge (৳)</th></tr></thead><tbody>'+''.join(rows)+'</tbody></table></div>'
    content+='<form method="post" action="/merchant/logout"><input type="hidden" name="csrf" value="'+escape(csrf,quote=True)+'"><button>Logout</button></form>'
    return page(content)

@app.post('/merchant/create-link',response_class=HTMLResponse)
async def merchant_create_link(request: Request):
    client,session=merchant_session(request)
    raw=await request.body()
    if len(raw)>4096: raise HTTPException(413,'Too large')
    data=form_fields(raw); csrf_check(data,session)
    order_id=field(data,'order_id'); mode=field(data,'mode'); amount=field(data,'amount')
    if not (1<=len(order_id)<=100): raise HTTPException(422,'Order ID required')
    # Reuse the same tenant-scoped creation rules without disclosing or retrieving the API key.
    amt=money(amount)
    if mode not in ('personal','merchant'): raise HTTPException(422,'Invalid payment mode')
    if mode=='personal' and not client['personal_number']: raise HTTPException(422,'Personal payment not configured')
    if mode=='merchant' and not (client['merchant_number'] or client['qr_url']): raise HTTPException(422,'Merchant payment not configured')
    token=secrets.token_urlsafe(32); oid='PAY-'+secrets.token_hex(9).upper(); ts=int(time.time())
    with db() as c:
        try:
            c.execute('INSERT INTO orders(id,client_id,merchant_order_id,amount,mode,status, created_at,updated_at,checkout_token_hash) VALUES(?,?,?,?,?,?,?,?,?)',
             (oid,client['id'],order_id,amt,mode,'pending',ts,ts,hashlib.sha256(token.encode()).hexdigest()))
        except sqlite3.IntegrityError: raise HTTPException(409,'Order ID already exists')
    url=BASE+'/pay/'+token
    content='<h2>Payment Link Created</h2><p>এটি নিজের Website-এর Pay Now বাটনে বসান।</p><p class="link" id="paylink">'+escape(url)+'</p>'
    content+='<button type="button" onclick="navigator.clipboard.writeText(document.getElementById(\'paylink\').textContent).then(()=>this.textContent=\'Copied!\')">Copy Payment Link</button>'
    content+='<p><a href="/merchant/dashboard">Back to Dashboard</a></p>'
    return page(content)

@app.post('/merchant/logout')
async def merchant_logout(request: Request):
    _,session=merchant_session(request)
    raw=await request.body(); csrf_check(form_fields(raw),session)
    token=request.cookies.get('ictw_merchant_session','')
    with db() as c: c.execute('DELETE FROM merchant_sessions WHERE token_hash=?',(hashlib.sha256(token.encode()).hexdigest(),))
    resp=RedirectResponse('/merchant', status_code=303)
    resp.delete_cookie('ictw_merchant_session',path='/merchant')
    resp.delete_cookie('ictw_merchant_csrf',path='/merchant')
    return resp


# v0.6: Self-service payment and website settings (merchant-scoped and CSRF-protected).
@app.get('/merchant/settings',response_class=HTMLResponse)
def merchant_settings(request: Request):
    client,_=merchant_session(request)
    csrf=merchant_csrf(request)
    def field_html(label,key,value):
        return '<label>'+escape(label)+'<input name="'+key+'" value="'+escape(value or '',quote=True)+'" maxlength="500"></label>'
    content='<h2>Merchant Settings</h2><p><a href="/merchant/dashboard">← Dashboard</a></p>'
    content+='<p class="muted">Client ID: '+escape(client['id'])+'. Payment receiving details only; no SMS-based payment confirmation.</p>'
    content+='<form method="post" action="/merchant/settings"><input type="hidden" name="csrf" value="'+escape(csrf,quote=True)+'">'
    content+=field_html('Website URL (HTTPS)','website',client['website'])
    content+=field_html('Personal receiving number','personal_number',client['personal_number'])
    content+=field_html('Merchant receiving number','merchant_number',client['merchant_number'])
    content+=field_html('Merchant QR HTTPS image URL','qr_url',client['qr_url'])
    content+='<button>Save Settings</button></form><p class="muted">Changes will appear on existing pending checkout links. Confirm the receiving account before publishing.</p>'
    return page(content,'ICT World Merchant Settings')

@app.post('/merchant/settings')
async def merchant_save_settings(request: Request):
    client,session=merchant_session(request)
    raw=await request.body()
    if len(raw)>5000: raise HTTPException(413,'Too large')
    d=form_fields(raw)
    csrf_check(d,session)
    website=normalize_https(field(d,'website'))
    qr=normalize_https(field(d,'qr_url'),True)
    personal=field(d,'personal_number')
    merchant=field(d,'merchant_number')
    if len(website)>500 or (qr and len(qr)>500): raise HTTPException(422,'URL too long')
    # Demo stores only simple receiving labels; no trust in displayed numbers for automatic verification.
    for n in (personal,merchant):
        if len(n)>40 or any(ch not in '0123456789+ -' for ch in n):
            raise HTTPException(422,'Invalid receiving number')
    with db() as c:
        c.execute('UPDATE clients SET website=?,personal_number=?,merchant_number=?,qr_url=? WHERE id=?',
            (website, personal or None,merchant or None,qr,client['id']))
    return RedirectResponse('/merchant/settings',status_code=303)

@app.get('/admin/client-summary')
def admin_client_summary(x_admin_token: str | None = Header(None)):
    admin_only(x_admin_token)
    with db() as c:
        clients=c.execute("""SELECT c.id,c.name,c.website,c.personal_number,c.merchant_number,
              c.amount_fee_per_thousand,c.per_tx_fee,COUNT(o.id) AS transaction_count,
              COALESCE(SUM(CASE WHEN o.status='verified_success' THEN 1 ELSE 0 END),0) AS verified_count
              FROM clients c LEFT JOIN orders o ON c.id=o.client_id GROUP BY c.id ORDER BY c.name""").fetchall()
    return {'clients':[dict(row) for row in clients], 'warning':'Staging only; full Super Admin UI, license and secure SMS connection not yet implemented.'}