#!/usr/bin/env python3
"""Render MySQL 5.6 account/database initialization SQL without raw passwords."""

import argparse
import re
from pathlib import Path

from render_cloud_sql import load_env, required, sql_hex


IDENTIFIER_RE = re.compile(r"^[A-Za-z0-9_]+$")
USER_RE = re.compile(r"^[A-Za-z0-9_.-]+$")


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--project-env", type=Path, required=True)
    parser.add_argument("--secrets-env", type=Path, required=True)
    parser.add_argument("--output", type=Path, required=True)
    args = parser.parse_args()

    values = load_env(args.project_env)
    values.update(load_env(args.secrets_env))
    store_db = required(values, "STORE_DB_NAME")
    ai_db = required(values, "AI_API_DB_NAME")
    user = required(values, "MYSQL_APP_USER")
    host = required(values, "MYSQL_APP_HOST")
    password = required(values, "MYSQL_APP_PASSWORD")
    for identifier in (store_db, ai_db):
        if not IDENTIFIER_RE.fullmatch(identifier):
            raise ValueError("database names must contain only letters, digits, and underscores")
    if not USER_RE.fullmatch(user):
        raise ValueError("invalid MYSQL_APP_USER")
    if host not in {"localhost", "127.0.0.1"}:
        raise ValueError("MYSQL_APP_HOST must be localhost or 127.0.0.1 for single-host V1")

    account = f"'{user}'@'{host}'"
    grants = (
        "SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP,REFERENCES,"
        "CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,CREATE VIEW,SHOW VIEW,"
        "CREATE ROUTINE,ALTER ROUTINE,EVENT,TRIGGER"
    )
    statements = [
        "SET sql_log_bin = 0;",
        f"SET @app_password = {sql_hex(password)};",
        f"CREATE DATABASE IF NOT EXISTS `{store_db}` "
        "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;",
        f"CREATE DATABASE IF NOT EXISTS `{ai_db}` "
        "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;",
        "SET @create_user_sql = CONCAT("
        f"{sql_hex('CREATE USER ' + account + ' IDENTIFIED BY ')}, "
        "QUOTE(@app_password));",
        "PREPARE create_user_stmt FROM @create_user_sql;",
        "EXECUTE create_user_stmt;",
        "DEALLOCATE PREPARE create_user_stmt;",
        f"GRANT {grants} ON `{store_db}`.* TO {account};",
        f"GRANT {grants} ON `{ai_db}`.* TO {account};",
        "FLUSH PRIVILEGES;",
        "SET @app_password = NULL;",
        f"SHOW GRANTS FOR {account};",
    ]
    args.output.write_text("\n".join(statements) + "\n", encoding="utf-8")
    args.output.chmod(0o600)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())