"""Company database provisioning.

The platform keeps its control-plane data (users, company registrations,
company metadata) in the main Django database.  When a company is approved,
this module creates a dedicated MySQL database and database user for that
company.

SECURITY NOTE: at the site owner's explicit request, the generated tenant
database password is stored in PLAIN TEXT (Project.database_password),
readable directly in MySQL and in the Super Admin panel. This means anyone
with access to the platform database, a database backup, or the Django
admin can read every company's database password directly. This is a
deliberate tradeoff for operational convenience — see README.md for the
reasoning and how to switch back to encrypted storage if you change your
mind later.
"""
import re
import secrets
from pathlib import Path

from django.conf import settings
from django.utils import timezone


def _safe_identifier(value: str, fallback: str, max_len: int = 64) -> str:
    value = re.sub(r"[^a-zA-Z0-9_]+", "_", value or "").strip("_").lower()
    if not value:
        value = fallback
    return value[:max_len]


def _quote_identifier(identifier: str) -> str:
    if not re.fullmatch(r"[A-Za-z0-9_]+", identifier):
        raise ValueError("Unsafe MySQL identifier")
    return f"`{identifier}`"


def generate_credentials(company_name: str, company_id) -> dict:
    # MySQL usernames are limited to 32 characters.  Keep the unique company
    # suffix inside that limit; the previous implementation truncated the
    # suffix for long company names, causing different companies to reuse the
    # same MySQL username and then fail with 1045 when CREATE USER IF NOT EXISTS
    # kept the old password.
    suffix = str(company_id).replace("-", "")[:10]
    user_slug_max = 32 - len("cu_") - len(suffix) - 1
    slug = _safe_identifier(company_name, "company", max(1, user_slug_max))
    db_name = _safe_identifier(f"construction_{slug}_{suffix}", "construction_company", 64)
    db_user = _safe_identifier(f"cu_{slug}_{suffix}", "company_user", 32)
    password = secrets.token_urlsafe(24)
    return {
        "database_name": db_name,
        "database_user": db_user,
        "database_password": password,
        "database_host": getattr(settings, "DB_HOST", "127.0.0.1"),
        "database_port": int(getattr(settings, "DB_PORT", "3306")),
    }


def provision_company_database(project):
    """Create the physical company DB + dedicated MySQL user.

    Returns the generated credentials.  The caller is responsible for storing
    only the encrypted password on the Project record.

    In SQLite quick-start mode (DJANGO_DB_ENGINE=sqlite), there's no MySQL
    server to create a database/user on — instead each company simply gets
    its own .sqlite3 file under tenant_dbs/, so the whole registration ->
    approval -> data-entry flow can still be tried out and demoed without
    installing MySQL first.
    """
    engine = getattr(settings, "DJANGO_DB_ENGINE", "mysql")
    creds = generate_credentials(project.name, project.id)

    if engine == "sqlite":
        db_dir = Path(settings.BASE_DIR) / "tenant_dbs"
        db_dir.mkdir(parents=True, exist_ok=True)
        return {
            **creds,
            "provisioned_at": timezone.now(),
            "database_status": "provisioned",
        }

    import MySQLdb  # imported lazily so non-MySQL setups don't need this installed

    root_user = getattr(settings, "DB_USER", "root")
    root_password = getattr(settings, "DB_PASSWORD", "")
    host = getattr(settings, "DB_HOST", "127.0.0.1")
    port = int(getattr(settings, "DB_PORT", "3306"))
    # The MySQL account host must match the host Django uses for the tenant
    # connection.  Keep an explicit override for deployments that need it.
    user_host = getattr(settings, "TENANT_DB_USER_HOST", "") or host
    if user_host in {"127.0.0.1", "::1"}:
        # Local MySQL commonly treats loopback connections as localhost.
        # Creating both local variants avoids an otherwise confusing 1045.
        user_hosts = ["localhost", user_host] if user_host != "localhost" else ["localhost", "127.0.0.1"]
    else:
        user_hosts = [user_host]

    db_name = creds["database_name"]
    db_user = creds["database_user"]
    db_password = creds["database_password"]

    conn = None
    try:
        conn = MySQLdb.connect(host=host, user=root_user, passwd=root_password, port=port, charset="utf8mb4")
        cur = conn.cursor()
        cur.execute(
            f"CREATE DATABASE IF NOT EXISTS {_quote_identifier(db_name)} "
            "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        )
        # CREATE USER IF NOT EXISTS does NOT replace the password of an
        # already-existing account.  That is unsafe for retrying approvals,
        # especially with legacy projects that used a non-unique username.
        # Always set the generated password explicitly.
        for account_host in user_hosts:
            cur.execute(
                "CREATE USER IF NOT EXISTS %s@%s IDENTIFIED BY %s",
                (db_user, account_host, db_password),
            )
            cur.execute(
                "ALTER USER %s@%s IDENTIFIED BY %s",
                (db_user, account_host, db_password),
            )
            cur.execute(
                f"GRANT ALL PRIVILEGES ON {_quote_identifier(db_name)}.* TO %s@%s",
                (db_user, account_host),
            )
        cur.execute("FLUSH PRIVILEGES")
        conn.commit()
    except Exception:
        if conn:
            conn.rollback()
        # Best-effort cleanup so a failed approval does not leave an orphan DB.
        try:
            if conn:
                cur = conn.cursor()
                cur.execute(f"DROP DATABASE IF EXISTS {_quote_identifier(db_name)}")
                for account_host in user_hosts:
                    cur.execute("DROP USER IF EXISTS %s@%s", (db_user, account_host))
                conn.commit()
        except Exception:
            pass
        raise
    finally:
        if conn:
            conn.close()

    return {
        **creds,
        "provisioned_at": timezone.now(),
        "database_status": "provisioned",
    }


def delete_company_database(project):
    """Delete the physical company DB. Used only by explicit Super Admin delete."""
    db_name = project.database_name
    db_user = project.database_user
    if not db_name:
        return

    if getattr(settings, "DJANGO_DB_ENGINE", "mysql") == "sqlite":
        db_file = Path(settings.BASE_DIR) / "tenant_dbs" / f"{db_name}.sqlite3"
        db_file.unlink(missing_ok=True)
        return

    import MySQLdb  # imported lazily so non-MySQL setups don't need this installed

    host = getattr(settings, "DB_HOST", "127.0.0.1")
    port = int(getattr(settings, "DB_PORT", "3306"))
    root_user = getattr(settings, "DB_USER", "root")
    root_password = getattr(settings, "DB_PASSWORD", "")
    user_host = getattr(settings, "TENANT_DB_USER_HOST", "") or host
    if user_host in {"127.0.0.1", "::1"}:
        user_hosts = ["localhost", user_host] if user_host != "localhost" else ["localhost", "127.0.0.1"]
    else:
        user_hosts = [user_host]
    conn = None
    try:
        conn = MySQLdb.connect(host=host, user=root_user, passwd=root_password, port=port, charset="utf8mb4")
        cur = conn.cursor()
        cur.execute(f"DROP DATABASE IF EXISTS {_quote_identifier(db_name)}")
        if db_user:
            for account_host in user_hosts:
                cur.execute("DROP USER IF EXISTS %s@%s", (db_user, account_host))
        cur.execute("FLUSH PRIVILEGES")
        conn.commit()
    finally:
        if conn:
            conn.close()