"""Isolated real API/Portal verification; no production records are written."""
import base64
import hashlib
import json
import os
from pathlib import Path
import secrets
import socket
import subprocess
import time
import urllib.request

root = Path(__file__).resolve().parent.parent
mysql = r'C:\xampp\mysql\bin\mysql.exe'
common = ['--protocol=tcp','--host=127.0.0.1','--port=3306','--user=root']
schema = 'tourapp_review_test_' + str(time.time_ns())
user = 'review_test_' + str(time.time_ns())
password = secrets.token_hex(24)
assert schema.startswith('tourapp_review_test_') and schema.replace('_','').isalnum()
stop = root/'work/agency_review_verification.stop'
disable = root/'work/agency_review_verification.disable-demo'
config_file = root/'portal/.tmp/review-verification.config.ts'
state_file = root/'work/agency_review_verification_state.json'
if state_file.exists():
    raise RuntimeError('A prior verification state exists; inspect and clean its isolated resources first.')
for file in [stop, disable]:
    if file.exists(): file.unlink()
for port in [18787,4174]:
    with socket.socket() as sock:
        if sock.connect_ex(('127.0.0.1',port)) == 0:
            raise RuntimeError(f'Port {port} is occupied; no existing process replaced.')

def sql(command, database=None):
    args = [mysql,*common,'--default-character-set=utf8mb4','--batch','--skip-column-names']
    if database: args.append(database)
    return subprocess.run(args,input=command.encode(),capture_output=True,check=True).stdout.decode().strip()

def insert(command):
    return int(sql(command+'; SELECT LAST_INSERT_ID();',schema).splitlines()[-1])

def checksums(database):
    names = sql('SHOW TABLES',database).splitlines()
    tables = ','.join('`'+database+'`.`'+name+'`' for name in names)
    return sql('CHECKSUM TABLE '+tables+' EXTENDED')

def ready(url, process):
    for _ in range(100):
        if process.poll() is not None: raise RuntimeError('Verification process exited; inspect its log.')
        try:
            urllib.request.urlopen(url,timeout=1).close()
            return
        except Exception: time.sleep(.1)
    raise RuntimeError('Verification service did not become ready.')

def end(process):
    if process and process.poll() is None:
        process.terminate()
        try: process.wait(timeout=5)
        except subprocess.TimeoutExpired: process.kill(); process.wait(timeout=5)

before = checksums('tourist_grouping_db')
(root/'work/agency_review_db_checksums_before.tsv').write_text(before,encoding='utf-8')
created = False
accounts = []
backend = portal = None
handles = []
try:
    sql('CREATE DATABASE `'+schema+'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
    created = True
    state_file.write_text(json.dumps({'schema':schema,'user':user},indent=2),encoding='utf-8')
    definitions = subprocess.run([r'C:\xampp\mysql\bin\mysqldump.exe',*common,'--no-data','--skip-add-drop-table','tourist_grouping_db'],capture_output=True,check=True).stdout
    subprocess.run([mysql,*common,schema],input=definitions,capture_output=True,check=True)
    for host in ['localhost','127.0.0.1']:
        sql("CREATE USER '"+user+"'@'"+host+"' IDENTIFIED BY '"+password+"'")
        accounts.append(host)
        sql("GRANT SELECT, INSERT, UPDATE, DELETE ON `"+schema+"`.* TO '"+user+"'@'"+host+"'")
    salt = b'agency-review-ui'
    digest = hashlib.pbkdf2_hmac('sha256',b'ReviewFixture_2026!',salt,60000)
    encoded = 'pbkdf2_sha256$60000$'+base64.urlsafe_b64encode(salt).decode()+'$'+base64.urlsafe_b64encode(digest).decode()
    for name, company in [('review-real-fixture','บริษัทตัวอย่าง · มีรีวิวหลังจบทริป'),('review-empty-fixture','บริษัทตัวอย่าง · ยังไม่มีรีวิว')]:
        agency = insert("INSERT INTO agencies (username,password_hash,company_name,contact_name,email,status) VALUES ('"+name+"','"+encoded+"','"+company+"','ผู้ประสานงานตัวอย่าง','"+name+"@example.invalid','approved')")
        if name == 'review-empty-fixture': continue
        group = insert("INSERT INTO tour_groups (group_name,country,status) VALUES ('Review fixture','ประเทศไทย','confirmed')")
        package = insert(f"INSERT INTO tour_packages (agency_id,package_name,origin_location,destination_location,price_per_person) VALUES ({agency},'Review fixture','กรุงเทพ','เชียงใหม่',10000)")
        bid = insert(f"INSERT INTO bids (group_id,package_id,price,status) VALUES ({group},{package},10000,'selected')")
        sql(f"INSERT INTO trip_meetings (group_id,bid_id,meet_at,meet_location,contact_name,status,completed_at) VALUES ({group},{bid},NOW(),'จุดนัดพบตัวอย่าง','ผู้ประสานงานตัวอย่าง','completed',NOW())",schema)
        for i,(score,comment) in enumerate([(5,'บริการดี ให้ข้อมูลรายละเอียดทริปชัดเจน'),(4,'ตอบคำถามรวดเร็วและประสานงานสะดวก'),(5,'โปรแกรมทัวร์เข้าใจง่าย ราคาเหมาะสม')]):
            member = insert(f"INSERT INTO users (group_id,username,password_hash,fname,lname,email,status) VALUES ({group},'review-fixture-{i}','fixture-only','สมาชิก','ตัวอย่าง','member-{i}@example.invalid','active')")
            sql(f"INSERT INTO payments (user_id,bid_id,amount,payment_method,status) VALUES ({member},{bid},10000,'cash','paid'); INSERT INTO trip_reviews (user_id,group_id,bid_id,trip_rating,agency_rating,comment,created_at) VALUES ({member},{group},{bid},3,{score},'{comment}',TIMESTAMPADD(DAY,-{i+1},NOW()))",schema)
    fixture_before = checksums(schema)
    env = os.environ.copy()
    env.update(DB_HOST='127.0.0.1',DB_PORT='3306',DB_NAME=schema,DB_USER=user,DB_PASSWORD=password,
      TRAVELIN_PORT='18787',TRAVELIN_ENV='demo',TRAVELIN_ENABLE_DEMO_REVIEWS='true',
      TRAVELIN_ENABLE_DEVELOPMENT_ACCOUNTS='0',GEMINI_API_KEY='unused-fixture-key',
      TRAVELIN_REVIEW_TEST_BASE_URL='http://127.0.0.1:18787',TRAVELIN_REVIEW_TEST_DB_NAME=schema)
    for key in ['TRAVELIN_ADMIN_USERNAME','TRAVELIN_ADMIN_EMAIL','TRAVELIN_ADMIN_PASSWORD','TRAVELIN_ADMIN_DISPLAY_NAME']:
        env.pop(key,None)
    def launch_backend():
        out = open(root/'work/agency_review_verification_backend.log','ab')
        handles.append(out)
        process = subprocess.Popen([r'C:\src\flutter\bin\cache\dart-sdk\bin\dart.exe','run','server/gemini_proxy.dart'],cwd=root,env=env,stdout=out,stderr=out)
        ready('http://127.0.0.1:18787/api/health',process)
        return process
    backend = launch_backend()
    tests = subprocess.run([r'C:\src\flutter\bin\flutter.bat','test','test/server/agency_review_api_database_test.dart','--reporter','expanded'],cwd=root,env=env,capture_output=True)
    (root/'work/agency_review_database_tests.log').write_bytes(tests.stdout+tests.stderr)
    print((tests.stdout+tests.stderr).decode('utf-8',errors='replace'),flush=True)
    if tests.returncode: raise RuntimeError('Review API/database tests failed.')
    config_file.parent.mkdir(exist_ok=True)
    config_file.write_text("import { mergeConfig } from 'vite';\nimport base from '../vite.config';\nexport default mergeConfig(base, { server: { proxy: { '/api': { target: 'http://127.0.0.1:18787' } } } });\n",encoding='utf-8')
    out = open(root/'work/agency_review_verification_portal.log','ab')
    handles.append(out)
    portal = subprocess.Popen([r'C:\Program Files\nodejs\node.exe','node_modules/vite/bin/vite.js','--config','.tmp/review-verification.config.ts','--host','127.0.0.1','--port','4174','--strictPort'],cwd=root/'portal',stdout=out,stderr=out)
    ready('http://127.0.0.1:4174/agency/login',portal)
    print('READY: isolated actual Portal http://127.0.0.1:4174/agency/login; demo reviews enabled.',flush=True)
    print('Fixture login names: review-real-fixture / review-empty-fixture (fixture-only password documented in script).',flush=True)
    started = time.monotonic()
    while not stop.exists() and time.monotonic()-started < 1200:
        if disable.exists() and env['TRAVELIN_ENABLE_DEMO_REVIEWS'] == 'true':
            end(backend)
            env['TRAVELIN_ENABLE_DEMO_REVIEWS'] = 'false'
            backend = launch_backend()
            print('READY: demo reviews disabled; sign in again for empty-state verification.',flush=True)
        time.sleep(.2)
    assert checksums(schema) == fixture_before, 'Review-only verification unexpectedly wrote fixture business data.'
finally:
    end(portal); end(backend)
    for handle in handles: handle.close()
    if config_file.exists(): config_file.unlink()
    for host in accounts: sql("DROP USER '"+user+"'@'"+host+"'")
    if created: sql('DROP DATABASE `'+schema+'`')
    if state_file.exists(): state_file.unlink()
    current = checksums('tourist_grouping_db')
    print('Temporary review schema/accounts removed.',flush=True)
    print('Application DB 33-table checksum unchanged: '+str(current==before),flush=True)
    if current != before:
        print('An existing app backend was running independently; inspect concurrent activity.',flush=True)