import json
import subprocess
from datetime import datetime
from pathlib import Path
from typing import Any

from sqlalchemy.orm import Session

from app.config import settings
from app.core.exceptions import AppException
from common_logging import get_logger

logger = get_logger(__name__)


class TenantExportService:

    def __init__(self, db: Session):
        self.db = db

    def export_postgresql(self, tenant_id: int, output_dir: Path) -> dict[str, Any]:
        output_dir.mkdir(parents=True, exist_ok=True)
        schema_name = f"tenant_{tenant_id}"
        from urllib.parse import urlparse

        parsed = urlparse(settings.DATABASE_URL)
        db_host = parsed.hostname or "localhost"
        db_port = parsed.port or 5432
        db_name = parsed.path.lstrip("/")
        db_user = parsed.username
        db_password = parsed.password
        schema_file = output_dir / f"{schema_name}.sql"
        import shutil

        pg_dump = shutil.which("pg_dump") or "/opt/homebrew/opt/postgresql@15/bin/pg_dump"
        cmd = [
            pg_dump,
            "-h",
            db_host,
            "-p",
            str(db_port),
            "-U",
            db_user,
            "-d",
            db_name,
            "-n",
            schema_name,
            "--no-owner",
            "--no-acl",
            "-f",
            str(schema_file),
        ]
        env = {"PGPASSWORD": db_password}
        result = subprocess.run(cmd, env=env, capture_output=True, text=True)
        if result.returncode != 0:
            raise AppException(f"PostgreSQL export failed: {result.stderr}")
        public_file = output_dir / "public_tenant_data.sql"
        psql = shutil.which("psql") or "/opt/homebrew/opt/postgresql@15/bin/psql"
        queries = [
            f"COPY (SELECT * FROM public.users WHERE tenant_id = {tenant_id}) TO STDOUT;",
            f"COPY (SELECT r.* FROM public.roles r WHERE r.tenant_id = {tenant_id} OR r.tenant_id IS NULL) TO STDOUT;",
            f"COPY (SELECT ur.* FROM public.user_roles ur JOIN public.users u ON ur.user_id = u.id WHERE u.tenant_id = {tenant_id}) TO STDOUT;",
            f"COPY (SELECT * FROM public.role_permissions WHERE tenant_id = {tenant_id}) TO STDOUT;",
            f"COPY (SELECT * FROM public.casbin_rule WHERE v1 = '{tenant_id}') TO STDOUT;",
        ]
        with open(public_file, "w") as f:
            for query in queries:
                cmd = [
                    psql,
                    "-h",
                    db_host,
                    "-p",
                    str(db_port),
                    "-U",
                    db_user,
                    "-d",
                    db_name,
                    "-c",
                    query,
                ]
                result = subprocess.run(cmd, env=env, capture_output=True, text=True)
                if result.returncode == 0:
                    f.write(result.stdout + "\n")
        stats = self._get_postgresql_stats(tenant_id)
        return {"schema_file": str(schema_file), "public_file": str(public_file), "stats": stats}

    def _get_postgresql_stats(self, tenant_id: int) -> dict[str, int]:
        from sqlalchemy import text

        schema_name = f"tenant_{tenant_id}"
        result = self.db.execute(
            text(
                f"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{schema_name}'"
            )
        )
        table_count = result.scalar()
        result = self.db.execute(
            text(f"SELECT COUNT(*) FROM public.users WHERE tenant_id = {tenant_id}")
        )
        user_count = result.scalar()
        return {"tables": table_count or 0, "users": user_count or 0}

    def create_manifest(
        self, tenant_id: int, output_dir: Path, export_results: dict[str, Any]
    ) -> Path:
        manifest = {
            "tenant_id": tenant_id,
            "export_time": datetime.utcnow().isoformat(),
            "version": "1.0",
            "results": export_results,
        }
        manifest_file = output_dir / "manifest.json"
        with open(manifest_file, "w") as f:
            json.dump(manifest, f, indent=2, ensure_ascii=False)
        return manifest_file