"""Executed in memory over SSH; reads one JSON request from stdin.

Only the masterAPP tables are written. No source database access or remote files.
"""

import hashlib
import json
import secrets
import string
import sys
from datetime import datetime, timezone


class UserError(Exception):
    pass


def email_key(value):
    return (value or "").strip().lower()


def snapshot(cursor, lock=False):
    suffix = " FOR UPDATE" if lock else ""
    cursor.execute("SELECT id, project_name, server_id FROM google_credentials ORDER BY id" + suffix)
    projects = cursor.fetchall()
    cursor.execute("SELECT id, google_credential_id, email FROM google_api_accounts ORDER BY id" + suffix)
    accounts = cursor.fetchall()
    return projects, accounts


def fingerprint(projects, accounts):
    return hashlib.sha256(json.dumps([projects, accounts], sort_keys=True, separators=(",", ":"), default=str).encode()).hexdigest()


def distribute(projects, weights, servers, baseline=None):
    loads = {server: {"accounts": 0, "projects": 0} for server in servers}
    for server, count in (baseline or []):
        if server in loads:
            loads[server]["accounts"] += count
            loads[server]["projects"] += 1
    assignments = {}
    # Largest complete projects first; stable input order breaks ties.
    for project in sorted(projects, key=lambda item: -weights.get(item, 0)):
        server = min(servers, key=lambda item: (loads[item]["accounts"], loads[item]["projects"]))
        assignments[project] = server
        loads[server]["accounts"] += weights.get(project, 0)
        loads[server]["projects"] += 1
    return assignments, [{"server_id": server, **loads[server]} for server in servers]


def make_plan(projects, accounts, request):
    servers = request.get("servers", [])
    if not servers or len(servers) > 500 or len(set(servers)) != len(servers) or any(type(s) is not int or s < 1 or s > 2147483647 for s in servers):
        raise UserError("Enter valid, unique server IDs, one per line.")
    operation = request.get("operation")
    if operation not in ("import", "rebalance"):
        raise UserError("Unknown operation.")
    replace = operation == "import" and request.get("mode") == "replace"
    if operation == "import" and request.get("mode") not in ("append", "replace"):
        raise UserError("Choose whether to add accounts or replace existing data.")

    original_projects = projects
    original_accounts = accounts
    if replace:
        projects, accounts = [], []
    project_map = {str(p["id"]): dict(p) for p in projects}
    weights = {key: 0 for key in project_map}
    email_map = {}
    for account in accounts:
        credential_id = str(account["google_credential_id"])
        if credential_id not in project_map:
            raise UserError("masterAPP contains accounts without a matching project. Repair those links before continuing.")
        weights[credential_id] += 1
        key = email_key(account["email"])
        if key in email_map and operation == "import":
            raise UserError("masterAPP already contains duplicate account emails. Resolve those duplicates before importing.")
        email_map[key] = account

    inserted, updated = 0, 0
    credential_data = {}
    account_writes = []
    if operation == "rebalance":
        affected = list(project_map)
        if not accounts:
            raise UserError("masterAPP has no accounts to distribute yet. Import accounts first.")
    else:
        records = request.get("records", [])
        if not records or len(records) > 20000:
            raise UserError("Select between 1 and 20,000 accounts for an import.")
        names = {}
        for key, project in project_map.items():
            name = project["project_name"]
            if name in names:
                raise UserError("masterAPP contains duplicate project names. Resolve those duplicates before importing.")
            names[name] = key
        seen_emails = set()
        for row in records:
            required = ("email", "project_name", "client_id", "client_secret", "redirect_uri", "access_token", "refresh_token")
            if any(not isinstance(row.get(field), str) or not row[field].strip() for field in required):
                raise UserError("A selected account has incomplete tokens or project credentials. Check availability again.")
            email = email_key(row["email"])
            if email in seen_emails:
                raise UserError("The selection contains repeated account emails. Check availability again.")
            seen_emails.add(email)
            name = row["project_name"]
            if name not in names:
                key = "new:" + name
                names[name] = key
                project_map[key] = {"id": None, "project_name": name, "server_id": None}
                weights[key] = 0
            key = names[name]
            credentials = {field: row[field] for field in ("project_name", "client_id", "client_secret", "redirect_uri")}
            if key in credential_data and credential_data[key] != credentials:
                raise UserError("Selected accounts contain conflicting credentials for the same project.")
            credential_data[key] = credentials
            existing = email_map.get(email)
            if existing:
                updated += 1
                weights[str(existing["google_credential_id"])] -= 1
            else:
                inserted += 1
            weights[key] += 1
            account_writes.append({"id": existing["id"] if existing else None, "project_key": key, "record": row})
        affected = list(credential_data)

    unaffected = [(p["server_id"], weights[key]) for key, p in project_map.items() if key not in affected]
    assignments, distribution = distribute(affected, weights, servers, unaffected)
    summary = {
        "operation": operation,
        "mode": "replace" if replace else "append",
        "selected_accounts": len(account_writes) if operation == "import" else len(accounts),
        "inserted": inserted,
        "updated": updated,
        "affected_projects": len(affected),
        "affected_accounts": sum(weights[key] for key in affected),
        "existing_accounts": len(original_accounts),
        "existing_projects": len(original_projects),
        "deleted_accounts": len(original_accounts) if replace else 0,
        "deleted_projects": len(original_projects) if replace else 0,
        "total_accounts": len(accounts) + inserted,
        "distribution": distribution,
        "projects": [
            {"project_name": project_map[key]["project_name"], "server_id": assignments[key], "accounts": weights[key]}
            for key in affected
        ],
        "sample": [{"source_id": w["record"]["source_id"], "email": w["record"]["email"], "project_name": w["record"]["project_name"]} for w in account_writes[:12]],
    }
    return {"summary": summary, "assignments": assignments, "project_map": project_map, "credentials": credential_data, "accounts": account_writes}


def apply_plan(cursor, plan, request):
    now = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
    if request["operation"] == "import" and request["mode"] == "replace":
        # DELETE (not TRUNCATE) preserves rollback and auto-increment continuity.
        cursor.execute("DELETE FROM google_api_accounts")
        cursor.execute("DELETE FROM google_credentials")
    ids = {key: project["id"] for key, project in plan["project_map"].items()}
    for key, server_id in plan["assignments"].items():
        credentials = plan["credentials"].get(key)
        if credentials:
            values = (credentials["project_name"], server_id, credentials["client_id"], credentials["client_secret"], credentials["redirect_uri"])
            if ids[key] is None:
                cursor.execute(
                    "INSERT INTO google_credentials (user_id, project_name, server_id, client_id, client_secret, redirect_uri, refreshedd_at, created_at, updated_at) VALUES (1, %s, %s, %s, %s, %s, NULL, %s, %s)",
                    values + (now, now),
                )
                ids[key] = cursor.lastrowid
            else:
                cursor.execute(
                    "UPDATE google_credentials SET user_id = 1, project_name = %s, server_id = %s, client_id = %s, client_secret = %s, redirect_uri = %s, updated_at = %s WHERE id = %s",
                    values + (now, ids[key]),
                )
        else:
            cursor.execute("UPDATE google_credentials SET server_id = %s, updated_at = %s WHERE id = %s", (server_id, now, ids[key]))
    for account in plan["accounts"]:
        row = account["record"]
        values = (ids[account["project_key"]], row["email"], row["access_token"], row["refresh_token"])
        if account["id"] is None:
            # Preserve the existing Python import's six-letter token_type convention.
            token_type = "".join(secrets.choice(string.ascii_lowercase) for _ in range(6))
            cursor.execute(
                "INSERT INTO google_api_accounts (google_credential_id, email, access_token, refresh_token, name, token_type, token_expires_at, last_connection_at, created_at, updated_at) VALUES (%s, %s, %s, %s, NULL, %s, NULL, NULL, %s, %s)",
                values + (token_type, now, now),
            )
        else:
            cursor.execute(
                "UPDATE google_api_accounts SET google_credential_id = %s, email = %s, access_token = %s, refresh_token = %s, token_expires_at = NULL, updated_at = %s WHERE id = %s",
                values + (now, account["id"]),
            )


def verify_schema(cursor):
    required = {
        "google_credentials": {"id", "user_id", "project_name", "server_id", "client_id", "client_secret", "redirect_uri", "refreshedd_at", "created_at", "updated_at"},
        "google_api_accounts": {"id", "google_credential_id", "email", "name", "access_token", "token_type", "refresh_token", "token_expires_at", "last_connection_at", "created_at", "updated_at"},
    }
    cursor.execute("SELECT TABLE_NAME AS table_name, ENGINE AS engine FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('google_credentials', 'google_api_accounts')")
    tables = {row["table_name"]: row["engine"] for row in cursor.fetchall()}
    if set(tables) != set(required):
        raise UserError("The configured database does not contain both required masterAPP tables.")
    if any((engine or "").upper() != "INNODB" for engine in tables.values()):
        raise UserError("Both masterAPP tables must use InnoDB so a failed import can roll back.")
    for table, columns in required.items():
        cursor.execute("SELECT COLUMN_NAME AS column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", (table,))
        existing = {row["column_name"] for row in cursor.fetchall()}
        if not columns.issubset(existing):
            raise UserError("The masterAPP table structure is missing required columns.")


def process(request, connector):
    database = request["database"]
    connection = connector.connect(
        host=database.get("host", "127.0.0.1"), port=int(database.get("port", 3306)),
        user=database["username"], password=database.get("password", ""), database=database["name"],
        connection_timeout=12, autocommit=False, charset="utf8mb4",
    )
    cursor = connection.cursor(dictionary=True)
    locked = False
    lock_name = "master_api:" + hashlib.sha256(database["name"].encode()).hexdigest()[:40]
    try:
        verify_schema(cursor)
        connection.rollback()
        execute = request["action"] == "execute"
        if execute:
            cursor.execute("SELECT GET_LOCK(%s, 5) AS acquired", (lock_name,))
            if cursor.fetchone()["acquired"] != 1:
                raise UserError("Another masterAPP operation is running. Please wait and create a new review.")
            locked = True
            connection.commit()
        connection.start_transaction(isolation_level="REPEATABLE READ")
        projects, accounts = snapshot(cursor, lock=execute)
        current_fingerprint = fingerprint(projects, accounts)
        if request["action"] == "inspect":
            weights = {str(p["id"]): 0 for p in projects}
            for account in accounts:
                key = str(account["google_credential_id"])
                weights[key] = weights.get(key, 0) + 1
            distribution = {}
            for project in projects:
                server = project["server_id"]
                distribution.setdefault(server, {"server_id": server, "accounts": 0, "projects": 0})
                distribution[server]["accounts"] += weights[str(project["id"])]
                distribution[server]["projects"] += 1
            return {"connected": True, "accounts": len(accounts), "projects": len(projects), "distribution": list(distribution.values())}
        if request["action"] not in ("preview", "execute"):
            raise UserError("Unknown action.")
        if execute and request.get("fingerprint") != current_fingerprint:
            raise UserError("masterAPP accounts or server assignments changed after the review. Create a new review.")
        plan = make_plan(projects, accounts, request)
        if execute:
            apply_plan(cursor, plan, request)
            connection.commit()
            return plan["summary"]
        return {**plan["summary"], "fingerprint": current_fingerprint}
    except Exception:
        connection.rollback()
        raise
    finally:
        connection.rollback()
        if locked:
            cursor.execute("SELECT RELEASE_LOCK(%s)", (lock_name,))
            cursor.fetchone()
        cursor.close()
        connection.close()


def main():
    print("MASTER_API_READY", flush=True)
    try:
        request = json.loads(sys.stdin.readline())
        try:
            import mysql.connector as connector
        except ImportError:
            raise UserError("Install mysql-connector-python for the configured Python interpreter on masterAPP.")
        result = {"ok": True, "data": process(request, connector)}
    except UserError as error:
        result = {"ok": False, "message": str(error)}
    except Exception as error:
        # Never return SQL, connection credentials, tokens, or raw driver messages.
        code = getattr(error, "errno", None)
        messages = {
            1045: "The masterAPP database login failed. Check the database credentials.",
            1049: "The configured masterAPP database does not exist.",
            1451: "Other masterAPP tables reference this data, so it could not be deleted. The import was rolled back.",
            1452: "masterAPP rejected a linked ID. Check that all entered server IDs and user ID 1 exist. The operation was rolled back.",
            1062: "A masterAPP uniqueness rule conflicts with this import. The operation was rolled back.",
            1205: "masterAPP is busy updating these records. The operation was rolled back; create a new review.",
            1213: "A concurrent database update interrupted the operation. It was rolled back; create a new review.",
        }
        result = {"ok": False, "message": messages.get(code, "The masterAPP database operation failed. Check connection settings and table compatibility. If the connection was interrupted, verify masterAPP before retrying.")}
    print("MASTER_API_RESULT " + json.dumps(result, separators=(",", ":")), flush=True)


if __name__ == "__main__":
    main()