#!/usr/bin/env python3
"""Run the performance index SQL migration on the production server."""
import os, sys, shlex
sys.path.insert(0, str(__import__('pathlib').Path(__file__).resolve().parents[1]))

SERVER_HOST = "91.107.131.171"
SERVER_PORT = 2435
SSH_USER    = "root"
SSH_PASS    = os.environ.get("DANESHYAR_SSH_PASSWORD", "")

DB_HOST = "sql.24.svpj.link"
DB_PORT = 3306
DB_NAME = "db_118623"
DB_USER = "db_118623"
DB_PASS = os.environ.get("DANESHYAR_DB_PASSWORD", "tWe7E6q4JThcB1px")

SQL = """
ALTER TABLE grades
  ADD INDEX IF NOT EXISTS idx_grades_school_status        (school_id, status),
  ADD INDEX IF NOT EXISTS idx_grades_school_student_status (school_id, student_id, status),
  ADD INDEX IF NOT EXISTS idx_grades_school_class_status  (school_id, classroom_id, status),
  ADD INDEX IF NOT EXISTS idx_grades_school_updated       (school_id, updated_at);

ALTER TABLE attendance_records
  ADD INDEX IF NOT EXISTS idx_attrec_school_status_created (school_id, status, created_at),
  ADD INDEX IF NOT EXISTS idx_attrec_student_school_status (student_id, school_id, status);

ALTER TABLE students
  ADD INDEX IF NOT EXISTS idx_students_school_status (school_id, status),
  ADD INDEX IF NOT EXISTS idx_students_user_id       (user_id);

ALTER TABLE class_students
  ADD INDEX IF NOT EXISTS idx_cs_school_classroom_status (school_id, classroom_id, status),
  ADD INDEX IF NOT EXISTS idx_cs_school_student_status   (school_id, student_id, status);

ALTER TABLE licenses
  ADD INDEX IF NOT EXISTS idx_licenses_school_status_expires (school_id, status, expires_at);

ALTER TABLE attendance_sessions
  ADD INDEX IF NOT EXISTS idx_attsess_school_class_date (school_id, classroom_id, date);

ALTER TABLE exam_sessions
  ADD INDEX IF NOT EXISTS idx_exam_sessions_school (school_id);
"""

try:
    import paramiko
except ImportError:
    print("ERROR: pip install paramiko"); sys.exit(1)

client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
print(f"Connecting to {SSH_USER}@{SERVER_HOST}:{SERVER_PORT} ...")
client.connect(SERVER_HOST, port=SERVER_PORT, username=SSH_USER, password=SSH_PASS, timeout=30)
print("Connected.\n")

cmd = (
    f"mysql -h {shlex.quote(DB_HOST)} -P {DB_PORT} "
    f"-u {shlex.quote(DB_USER)} -p{shlex.quote(DB_PASS)} "
    f"{shlex.quote(DB_NAME)} -e {shlex.quote(SQL)}"
)

print("Running index migration ...")
stdin, stdout, stderr = client.exec_command(cmd, timeout=120)
out = stdout.read().decode()
err = stderr.read().decode()
rc  = stdout.channel.recv_exit_status()

if out: print("STDOUT:", out)
# filter out the harmless CLI password warning
relevant_err = "\n".join(l for l in err.splitlines() if "password on the command line" not in l)
if relevant_err.strip(): print("STDERR:", relevant_err)
if rc == 0:
    print("\nIndex migration complete. Exit code: 0")
else:
    print(f"\nFailed with exit code {rc}")
    print("Full STDERR:", err)

client.close()