from __future__ import annotations

import argparse
import os
import shutil
import socket
import subprocess
import sys
from dataclasses import dataclass
from pathlib import Path

try:
    import psycopg
    from psycopg import sql
    from psycopg.errors import InvalidCatalogName
except ImportError:  # pragma: no cover - handled at runtime on unbootstrapped machines
    psycopg = None
    sql = None
    InvalidCatalogName = None

from services.shared.config.base import (
    PROJECT_ROOT,
    resolve_env_file_candidates,
    resolve_preferred_env_file,
)


def _configure_stdio() -> None:
    """Avoid UnicodeEncodeError on Windows shells with non-UTF-8 encodings."""
    for stream in (sys.stdout, sys.stderr):
        reconfigure = getattr(stream, "reconfigure", None)
        if reconfigure is None:
            continue
        try:
            reconfigure(errors="backslashreplace")
        except Exception:
            continue


def safe_text(value: object) -> str:
    text = str(value)
    encoding = getattr(sys.stdout, "encoding", None) or "utf-8"
    try:
        text.encode(encoding)
    except UnicodeEncodeError:
        return text.encode(encoding, errors="backslashreplace").decode(encoding)
    return text


_configure_stdio()


PLACEHOLDER_VALUES = {
    "your_db_host",
    "your_db_user",
    "your_db_password",
    "your_db_name",
    "your_host",
    "your_username",
    "your_password",
}
LOCAL_INVALID_DB_HOSTS = {"db"}
RELEVANT_ENV_KEYS = (
    "DB_DIALECT",
    "DB_HOST",
    "DB_PORT",
    "DB_USER",
    "DB_PASSWORD",
    "DB_NAME",
    "DB_CHARSET",
)
MAINTENANCE_DATABASES = ("postgres", "template1")
SCHEMA_SCRIPT = (
    PROJECT_ROOT / "services" / "crawler" / "mindspider" / "schema" / "init_database.py"
)


@dataclass(frozen=True)
class DatabaseConfig:
    dialect: str
    host: str
    port: int
    user: str
    password: str
    database: str
    charset: str


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        description=(
            "Diagnose and prepare the local PostgreSQL database used by the "
            "pure-local BettaFish workflow."
        ),
    )
    parser.add_argument(
        "--env-file",
        help="Optional env file override. Defaults to .env.local first, then .env.",
    )
    parser.add_argument(
        "--check-only",
        action="store_true",
        help="Only validate connectivity and configuration without creating anything.",
    )
    parser.add_argument(
        "--ensure-db",
        action="store_true",
        help="Create the target database when it does not exist yet.",
    )
    parser.add_argument(
        "--apply-schema",
        action="store_true",
        help="Run the existing MindSpider schema initializer after connectivity is ready.",
    )
    parser.add_argument(
        "--maintenance-db",
        help="Optional maintenance database override used for CREATE DATABASE.",
    )
    parser.add_argument(
        "--timeout",
        type=float,
        default=3.0,
        help="TCP/database connect timeout in seconds. Default: 3.0",
    )
    return parser.parse_args()


def read_env_values(path: Path) -> dict[str, str]:
    values: dict[str, str] = {}
    if not path.exists():
        return values

    for raw_line in path.read_text(encoding="utf-8", errors="replace").splitlines():
        line = raw_line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        values[key.strip()] = value.strip()
    return values


def resolve_runtime_env_file(explicit: str | None) -> Path | None:
    candidates = resolve_env_file_candidates(explicit_env=explicit)
    existing = [path for path in candidates if path.exists()]
    if existing:
        return resolve_preferred_env_file(tuple(existing))
    return None


def load_effective_env_values(env_file: Path) -> dict[str, str]:
    values = read_env_values(env_file)
    for key in RELEVANT_ENV_KEYS:
        override = os.getenv(key)
        if override not in ("", None):
            values[key] = override.strip()
    return values


def build_database_config(values: dict[str, str]) -> DatabaseConfig:
    raw_port = (values.get("DB_PORT") or "5432").strip()
    try:
        port = int(raw_port)
    except ValueError as exc:
        raise ValueError(f"DB_PORT is not a valid integer: {raw_port}") from exc

    return DatabaseConfig(
        dialect=(values.get("DB_DIALECT") or "postgresql").strip().lower(),
        host=(values.get("DB_HOST") or "").strip(),
        port=port,
        user=(values.get("DB_USER") or "").strip(),
        password=(values.get("DB_PASSWORD") or "").strip(),
        database=(values.get("DB_NAME") or "").strip(),
        charset=(values.get("DB_CHARSET") or "utf8mb4").strip(),
    )


def validate_database_config(config: DatabaseConfig) -> list[str]:
    issues: list[str] = []

    if config.dialect not in {"postgres", "postgresql"}:
        issues.append(
            f"DB_DIALECT={config.dialect!r} is not supported by this helper. "
            "Use postgresql for the pure-local workflow."
        )

    if not config.host or config.host.lower() in PLACEHOLDER_VALUES:
        issues.append("DB_HOST is still empty or a placeholder value.")
    elif config.host.lower() in LOCAL_INVALID_DB_HOSTS:
        issues.append("DB_HOST still points to the Docker service name `db`.")

    if not config.user or config.user.lower() in PLACEHOLDER_VALUES:
        issues.append("DB_USER is still empty or a placeholder value.")

    if not config.password or config.password.lower() in PLACEHOLDER_VALUES:
        issues.append("DB_PASSWORD is still empty or a placeholder value.")

    if not config.database or config.database.lower() in PLACEHOLDER_VALUES:
        issues.append("DB_NAME is still empty or a placeholder value.")

    if config.port <= 0 or config.port > 65535:
        issues.append(f"DB_PORT={config.port} is outside the valid TCP range.")

    return issues


def mask_secret(value: str) -> str:
    if not value:
        return "<empty>"
    if len(value) <= 2:
        return "*" * len(value)
    return f"{value[0]}{'*' * (len(value) - 2)}{value[-1]}"


def print_config_summary(env_file: Path, config: DatabaseConfig) -> None:
    try:
        env_label = env_file.relative_to(PROJECT_ROOT)
    except ValueError:
        env_label = env_file

    print(f"[postgres] Using env file: {env_label}")
    print("[postgres] Effective configuration:")
    print(f"  - DB_DIALECT={config.dialect}")
    print(f"  - DB_HOST={config.host}")
    print(f"  - DB_PORT={config.port}")
    print(f"  - DB_USER={config.user}")
    print(f"  - DB_PASSWORD={mask_secret(config.password)}")
    print(f"  - DB_NAME={config.database}")


def test_tcp_port(host: str, port: int, *, timeout: float) -> bool:
    try:
        with socket.create_connection((host, port), timeout=timeout):
            return True
    except OSError:
        return False


def print_tcp_failure_hint(config: DatabaseConfig) -> None:
    print(
        f"[postgres] Unable to reach PostgreSQL at {config.host}:{config.port}. "
        "Start the local PostgreSQL service first."
    )
    if shutil.which("psql") is None and shutil.which("pg_ctl") is None:
        print(
            "[postgres] PostgreSQL client/server binaries were not found in PATH. "
            "This usually means PostgreSQL is not installed locally yet."
        )
    print("[postgres] Suggested next steps:")
    print("  1. Install or start a local PostgreSQL 15+ service.")
    print("  2. Re-run: python -m scripts.dev.prepare_local_postgres --check-only")
    print(
        "  3. If the server is up but the database/schema is missing, run: "
        "python -m scripts.dev.prepare_local_postgres --ensure-db --apply-schema"
    )


def connect_database(
    config: DatabaseConfig,
    database_name: str,
    *,
    timeout: float,
    autocommit: bool = False,
):
    if psycopg is None:
        raise RuntimeError(
            "psycopg is not installed. Run `python -m scripts.dev.bootstrap_local` "
            "or install infra/python/requirements-local.txt first."
        )

    return psycopg.connect(
        host=config.host,
        port=config.port,
        user=config.user,
        password=config.password,
        dbname=database_name,
        connect_timeout=max(1, int(timeout)),
        autocommit=autocommit,
    )


def iter_exception_chain(exc: BaseException):
    seen: set[int] = set()
    current: BaseException | None = exc
    while current is not None and id(current) not in seen:
        yield current
        seen.add(id(current))
        current = current.__cause__ or current.__context__


def is_missing_database_error(exc: BaseException) -> bool:
    for current in iter_exception_chain(exc):
        if InvalidCatalogName is not None and isinstance(current, InvalidCatalogName):
            return True
        sqlstate = getattr(current, "sqlstate", None)
        if sqlstate == "3D000":
            return True
        message = str(current).lower()
        if "does not exist" in message and "database" in message:
            return True
    return False


def is_authentication_error(exc: BaseException) -> bool:
    for current in iter_exception_chain(exc):
        sqlstate = getattr(current, "sqlstate", None)
        if sqlstate in {"28P01", "28000"}:
            return True
        message = str(current).lower()
        if (
            "password authentication failed" in message
            or "authentication failed" in message
            or "password" in message
        ):
            return True
    return False


def probe_target_database(config: DatabaseConfig, *, timeout: float) -> tuple[bool, str | None]:
    try:
        with connect_database(config, config.database, timeout=timeout) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT current_database(), current_user")
                current_database, current_user = cur.fetchone()
            print(
                "[postgres] Target database is reachable: "
                f"database={current_database}, user={current_user}"
            )
            return True, None
    except Exception as exc:  # pragma: no cover - depends on local database state
        if is_missing_database_error(exc):
            print(f"[postgres] Target database `{config.database}` does not exist yet.")
            return False, "missing_database"

        if is_authentication_error(exc):
            print(
                "[postgres] Authentication failed for the configured PostgreSQL "
                f"user `{config.user}`. Check DB_USER/DB_PASSWORD in the active env "
                "file or create/reset the local role with matching credentials."
            )
            print(f"[postgres] Driver detail: {safe_text(exc)}")
            return False, "auth_failed"

        print(
            "[postgres] Unable to connect to the target database with the current "
            f"credentials: {safe_text(exc)}"
        )
        return False, "connect_failed"


def connect_maintenance_database(
    config: DatabaseConfig,
    *,
    timeout: float,
    preferred_database: str | None,
):
    ordered: list[str] = []
    if preferred_database:
        ordered.append(preferred_database)
    for candidate in MAINTENANCE_DATABASES:
        if candidate not in ordered:
            ordered.append(candidate)

    last_error: Exception | None = None
    for database_name in ordered:
        try:
            conn = connect_database(
                config,
                database_name,
                timeout=timeout,
                autocommit=True,
            )
            print(
                f"[postgres] Maintenance connection established via database `{database_name}`."
            )
            return conn, database_name
        except Exception as exc:  # pragma: no cover - depends on local database state
            last_error = exc

    if last_error is not None:
        raise last_error
    raise RuntimeError("Unable to connect to a PostgreSQL maintenance database.")


def database_exists(conn, database_name: str) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (database_name,))
        return cur.fetchone() is not None


def ensure_database_exists(conn, database_name: str) -> bool:
    if database_exists(conn, database_name):
        print(f"[postgres] Database `{database_name}` already exists.")
        return False

    with conn.cursor() as cur:
        cur.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(database_name)))

    print(f"[postgres] Created database `{database_name}`.")
    return True


def run_schema_initializer(env_file: Path, values: dict[str, str]) -> int:
    if not SCHEMA_SCRIPT.exists():
        print(f"[postgres] Schema script was not found: {SCHEMA_SCRIPT}")
        return 1

    env = os.environ.copy()
    env["BETTAFISH_ENV_FILE"] = str(env_file)
    env.setdefault("PYTHONIOENCODING", "utf-8")
    env.setdefault("PYTHONUTF8", "1")
    env.setdefault("PYTHONUNBUFFERED", "1")
    existing_pythonpath = env.get("PYTHONPATH", "").strip()
    project_pythonpath = str(PROJECT_ROOT)
    env["PYTHONPATH"] = (
        project_pythonpath
        if not existing_pythonpath
        else f"{project_pythonpath}{os.pathsep}{existing_pythonpath}"
    )
    for key in RELEVANT_ENV_KEYS:
        if values.get(key):
            env[key] = values[key]

    print(f"[postgres] Applying schema with: {SCHEMA_SCRIPT}")
    result = subprocess.run(
        [sys.executable, "-X", "utf8", str(SCHEMA_SCRIPT)],
        cwd=PROJECT_ROOT,
        env=env,
    )
    if result.returncode == 0:
        print("[postgres] Schema initialization completed.")
    else:
        print(
            "[postgres] Schema initialization failed. "
            "See the output above for details."
        )
    return result.returncode


def main() -> int:
    args = parse_args()
    env_file = resolve_runtime_env_file(args.env_file)

    if env_file is None:
        print(
            "[postgres] No env file was found. Copy `.env.local.example` to "
            "`.env.local` first."
        )
        return 1

    values = load_effective_env_values(env_file)
    try:
        config = build_database_config(values)
    except ValueError as exc:
        print(f"[postgres] {exc}")
        return 1

    print_config_summary(env_file, config)

    issues = validate_database_config(config)
    if issues:
        print("[postgres] The current configuration is not ready for pure-local PostgreSQL:")
        for issue in issues:
            print(f"  - {issue}")
        return 1

    if not test_tcp_port(config.host, config.port, timeout=args.timeout):
        print_tcp_failure_hint(config)
        return 1

    target_ready, target_status = probe_target_database(config, timeout=args.timeout)
    allow_maintenance_recovery = args.ensure_db and target_status == "connect_failed"
    if target_status == "auth_failed":
        return 1
    if target_status == "connect_failed" and not allow_maintenance_recovery:
        return 1

    if args.check_only and not args.ensure_db and not args.apply_schema:
        if target_ready:
            print("[postgres] Check-only verification passed.")
            return 0
        print(
            "[postgres] Check-only verification failed because the target database "
            "is missing."
        )
        print(
            "[postgres] Run: python -m scripts.dev.prepare_local_postgres "
            "--ensure-db --apply-schema"
        )
        return 1

    maintenance_conn = None
    try:
        if not target_ready or args.ensure_db:
            maintenance_conn, _ = connect_maintenance_database(
                config,
                timeout=args.timeout,
                preferred_database=args.maintenance_db,
            )

        if args.ensure_db:
            ensure_database_exists(maintenance_conn, config.database)

        if not target_ready:
            target_ready, target_status = probe_target_database(config, timeout=args.timeout)
            if target_status in {"connect_failed", "auth_failed"}:
                return 1

        if not target_ready:
            print(
                "[postgres] The target database is still unavailable. "
                "Run with --ensure-db after the PostgreSQL service is ready."
            )
            return 1

        if args.apply_schema:
            schema_code = run_schema_initializer(env_file, values)
            if schema_code != 0:
                return schema_code
    finally:
        if maintenance_conn is not None:
            maintenance_conn.close()

    print("[postgres] Local PostgreSQL preparation completed.")
    return 0


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