import gzip
import json
import os
import re
import shutil
import subprocess
import time

BASE = '/www/backup/txiaw-mysql-fix-20260831'
config = open('/www/wwwroot/www.txiaw.com/Runtime/Conf/config.php', encoding='utf-8').read()
cfg = {m.group(1).lower(): m.group(3) for m in re.finditer(r'''['"](db_[a-z_]+)['"]\s*=>\s*(['"])(.*?)\2''', config, re.I)}
assert cfg['db_name'] == 'xiaxia'
env = dict(os.environ, MYSQL_PWD=cfg.get('db_pwd', cfg.get('db_password')))
auth = ['--no-defaults', '-u' + cfg['db_user'], '-h' + cfg['db_host'], '--connect-timeout=5']
mysql = ['/www/server/mysql/bin/mysql'] + auth + ['-B']

def sql(query, timeout=20):
    p = subprocess.run(mysql + ['-e', query], env=env, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True, timeout=timeout)
    if p.returncode:
        raise RuntimeError(p.stderr[:1500])
    return p.stdout

schema = sql('SHOW CREATE TABLE xiaxia.gxl_news; SHOW INDEX FROM xiaxia.gxl_news;')
schema_path = BASE + '/gxl_news-schema-before.txt'
if not os.path.exists(schema_path):
    with open(schema_path, 'x') as f:
        f.write(schema)
    os.chmod(schema_path, 0o600)

indexes = {}
for line in sql('SHOW INDEX FROM xiaxia.gxl_news;').splitlines()[1:]:
    cols = line.split('\t')
    indexes.setdefault(cols[2], []).append((int(cols[3]), cols[4]))
compatible = [name for name, values in indexes.items() if [col for _, col in sorted(values)][:2] == ['news_status', 'news_addtime']]
if compatible:
    print('SUITABLE_INDEX_ALREADY_EXISTS', compatible, flush=True)
else:
    if 'idx_news_status_addtime' in indexes:
        raise RuntimeError('Index name exists with different columns; refusing replacement')
    backup = BASE + '/gxl_news-before.sql.gz'
    if not os.path.exists(backup):
        partial = backup + '.partial'
        if os.path.exists(partial):
            with gzip.open(partial, 'rb') as previous:
                if previous.read(1):
                    raise RuntimeError('Nonempty partial backup exists; inspect before retry')
            os.rename(partial, partial + '.empty-failed-' + str(int(time.time())))
        print('BACKING_UP_GXL_NEWS_WITH_CONSISTENT_SNAPSHOT', flush=True)
        dump_auth = [a for a in auth if not a.startswith('--connect-timeout=')]
        dump_args = ['/www/server/mysql/bin/mysqldump'] + dump_auth + ['--single-transaction', '--quick', '--skip-lock-tables', '--skip-add-locks', '--hex-blob', '--set-gtid-purged=OFF', 'xiaxia', 'gxl_news']
        with open(BASE + '/dump-stderr.txt', 'wb') as err:
            p = subprocess.Popen(dump_args, env=env, stdout=subprocess.PIPE, stderr=err)
            with open(partial, 'xb') as raw:
                os.chmod(partial, 0o600)
                with gzip.GzipFile(fileobj=raw, mode='wb', compresslevel=1) as target:
                    shutil.copyfileobj(p.stdout, target, 1024 * 1024)
            p.stdout.close()
            if p.wait(timeout=90) != 0:
                raise RuntimeError('Backup failed; see private dump-stderr.txt. No ALTER performed.')
        with gzip.open(partial, 'rb') as check:
            while check.read(1024 * 1024):
                pass
        os.replace(partial, backup)
    print('BACKUP_VERIFIED', backup, os.path.getsize(backup), flush=True)
    print('ADDING_SECONDARY_INDEX_INPLACE_LOCK_NONE', flush=True)
    started = time.monotonic()
    sql('SET SESSION lock_wait_timeout=5; ALTER TABLE xiaxia.gxl_news ADD INDEX idx_news_status_addtime (news_status, news_addtime), ALGORITHM=INPLACE, LOCK=NONE;', timeout=90)
    print('INDEX_ADDED_SECONDS', round(time.monotonic() - started, 3), flush=True)

query = 'SELECT SQL_NO_CACHE news_id,news_cid,news_name FROM xiaxia.gxl_news WHERE news_status=1 ORDER BY news_addtime DESC LIMIT 100'
plan = sql('EXPLAIN ' + query + ';')
print('EXPLAIN_AFTER\n' + plan, flush=True)
if 'Using filesort' in plan:
    raise RuntimeError('The optimizer still sorts; inspect before claiming success')
samples = []
for _ in range(3):
    started = time.monotonic()
    result = sql(query + ';')
    samples.append({'wall_seconds_including_client': round(time.monotonic() - started, 4), 'rows': len(result.splitlines()) - 1})
assert all(s['rows'] == 100 for s in samples)
metrics = sql(query + "; SHOW SESSION STATUS WHERE Variable_name IN ('Sort_rows','Sort_scan','Sort_range','Handler_read_key','Handler_read_next','Handler_read_prev');")
metrics = metrics[metrics.rfind('Variable_name\tValue'):]
print('QUERY_SAMPLES', json.dumps(samples), flush=True)
print('SESSION_COUNTERS_AFTER_ONE_QUERY\n' + metrics, flush=True)
with open(BASE + '/index-result.json', 'w') as f:
    json.dump({'explain': plan, 'samples': samples, 'session_counters': metrics}, f, indent=2)