import sys
sys.path.insert(0, "/var/www/html/metafoliadorv2")

from urllib.parse import urlparse

from fastapi import APIRouter, Depends, HTTPException, Request, UploadFile, File
from fastapi.responses import JSONResponse
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from pydantic import BaseModel, Field
from typing import Optional

from app.models.main_database import get_main_db, MainBase
from app.models.main_user import MainUser
from app.models.main_customer import Customer
from app.api.main.auth import get_main_current_user
from app.config import settings

router = APIRouter(prefix="/main/api/customers", tags=["main-customers"])


def _parse_main_db_url():
    u = urlparse(settings.main_database_url)
    return {
        "user": u.username or "metafoliador",
        "password": u.password or "",
        "host": u.hostname or "localhost",
        "port": u.port or 5432,
    }


def _build_tenant_db_name(subdomain: str) -> str:
    return f"metafoliador_tenant_{subdomain}"


def _build_tenant_db_url(subdomain: str) -> str:
    db_name = _build_tenant_db_name(subdomain)
    p = _parse_main_db_url()
    return f"postgresql://{p['user']}:{p['password']}@{p['host']}:{p['port']}/{db_name}"


def _extract_db_name_from_url(db_url: str) -> str:
    u = urlparse(db_url)
    return u.path.lstrip("/") if u.path else ""


class CreateCustomerRequest(BaseModel):
    name: str = Field(..., description="Nombre del cliente")
    subdomain: str = Field(..., description="Subdominio del tenant")
    custom_domain: Optional[str] = Field(None, description="Dominio personalizado (opcional)")


class UpdateCustomerRequest(BaseModel):
    name: Optional[str] = Field(None, description="Nombre del cliente")
    subdomain: Optional[str] = Field(None, description="Subdominio del tenant")
    custom_domain: Optional[str] = Field(None, description="Dominio personalizado")
    active: Optional[bool] = Field(None, description="Estado activo/inactivo del tenant")


class CustomerResponse(BaseModel):
    id: int
    name: str
    subdomain: str
    custom_domain: Optional[str] = None
    db_name: str
    active: bool


def _customer_to_dict(c: Customer) -> dict:
    return {
        "id": c.id,
        "name": c.name,
        "subdomain": c.subdomain,
        "custom_domain": c.custom_domain,
        "db_name": _extract_db_name_from_url(c.db_url),
        "active": c.active,
        "background_image": c.background_image,
    }


@router.get("", summary="Listar clientes", description="Lista todos los clientes registrados.")
async def list_customers(
    db: Session = Depends(get_main_db),
    current_user: MainUser = Depends(get_main_current_user),
):
    customers = db.query(Customer).order_by(Customer.name).all()
    return [_customer_to_dict(c) for c in customers]


@router.post("", status_code=201, summary="Crear cliente", description="Crea un nuevo cliente y su base de datos tenant. La BD se nombra automaticamente como metafoliador_tenant_{subdominio}.")
async def create_customer(
    body: CreateCustomerRequest,
    db: Session = Depends(get_main_db),
    current_user: MainUser = Depends(get_main_current_user),
):
    existing = db.query(Customer).filter(Customer.subdomain == body.subdomain).first()
    if existing:
        raise HTTPException(status_code=400, detail="Ya existe un cliente con ese subdominio")

    if body.custom_domain:
        existing_domain = db.query(Customer).filter(Customer.custom_domain == body.custom_domain).first()
        if existing_domain:
            raise HTTPException(status_code=400, detail="Ya existe un cliente con ese dominio personalizado")

    db_name = _build_tenant_db_name(body.subdomain)
    db_url = _build_tenant_db_url(body.subdomain)
    conn_params = _parse_main_db_url()

    try:
        _create_tenant_database(db_name, conn_params)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error al crear la base de datos del tenant: {str(e)}")

    try:
        _run_tenant_migrations(db_url)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error al ejecutar migraciones en la BD del tenant: {str(e)}")

    try:
        _seed_tenant_user(db_url, current_user.email, current_user.name)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error al crear usuario en el tenant: {str(e)}")

    try:
        _seed_tenant_permissions(db_url)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error al sembrar permisos en el tenant: {str(e)}")

    customer = Customer(
        name=body.name,
        subdomain=body.subdomain,
        custom_domain=body.custom_domain,
        db_url=db_url,
        active=True,
    )
    db.add(customer)
    db.commit()
    db.refresh(customer)

    return _customer_to_dict(customer)


@router.put("/{customer_id}", summary="Actualizar cliente", description="Edita los datos del cliente. No permite modificar la conexión a la BD ya que se usa la misma que la BD principal.")
async def update_customer(
    customer_id: int,
    body: UpdateCustomerRequest,
    db: Session = Depends(get_main_db),
    current_user: MainUser = Depends(get_main_current_user),
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Cliente no encontrado")

    if body.name is not None:
        customer.name = body.name
    if body.subdomain is not None:
        existing = db.query(Customer).filter(
            Customer.subdomain == body.subdomain,
            Customer.id != customer_id,
        ).first()
        if existing:
            raise HTTPException(status_code=400, detail="Ya existe otro cliente con ese subdominio")
        customer.subdomain = body.subdomain
    if body.custom_domain is not None:
        if body.custom_domain:
            existing = db.query(Customer).filter(
                Customer.custom_domain == body.custom_domain,
                Customer.id != customer_id,
            ).first()
            if existing:
                raise HTTPException(status_code=400, detail="Ya existe otro cliente con ese dominio")
        customer.custom_domain = body.custom_domain if body.custom_domain else None
    if body.active is not None:
        customer.active = body.active

    db.commit()
    db.refresh(customer)

    return _customer_to_dict(customer)


@router.get("/{customer_id}", summary="Obtener cliente", description="Obtiene los datos de un cliente por ID.")
async def get_customer(
    customer_id: int,
    db: Session = Depends(get_main_db),
    current_user: MainUser = Depends(get_main_current_user),
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Cliente no encontrado")
    return _customer_to_dict(customer)


def _create_tenant_database(db_name: str, conn_params: dict):
    import psycopg2
    from psycopg2 import sql
    from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

    conn = psycopg2.connect(
        dbname="postgres",
        user=conn_params["user"],
        password=conn_params["password"],
        host=conn_params["host"],
        port=conn_params["port"],
    )
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = conn.cursor()
    cur.execute(
        sql.SQL("CREATE DATABASE {} WITH ENCODING 'UTF8' TEMPLATE template0").format(
            sql.Identifier(db_name)
        )
    )
    cur.close()
    conn.close()


def _run_tenant_migrations(db_url: str):
    import subprocess
    import os

    env = os.environ.copy()
    result = subprocess.run(
        [
            "/var/www/html/metafoliadorv2/venv/bin/alembic",
            "-x", f"db_url={db_url}",
            "upgrade", "head",
        ],
        cwd="/var/www/html/metafoliadorv2",
        env=env,
        capture_output=True,
        text=True,
    )
    if result.returncode != 0:
        raise RuntimeError(f"Migraciones fallaron: {result.stderr.strip() or result.stdout.strip()}")


def _seed_tenant_user(db_url: str, email: str, name: str):
    import psycopg2
    from app.services.auth_service import hash_password
    from app.models.main_database import MainSessionLocal
    from app.models.main_user import MainUser

    main_db = MainSessionLocal()
    try:
        main_user = main_db.query(MainUser).filter(MainUser.email == email).first()
        password_hash = main_user.password_hash if main_user else hash_password("Changeme123!")
    finally:
        main_db.close()

    u = _parse_main_db_url()
    conn = psycopg2.connect(
        dbname=_extract_db_name_from_url(db_url),
        user=u["user"],
        password=u["password"],
        host=u["host"],
        port=u["port"],
    )
    try:
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO users (email, password_hash, name, role, active) VALUES (%s, %s, %s, %s, %s) ON CONFLICT (email) DO NOTHING",
            (email, password_hash, name or "Administrador", "admin", True),
        )
        conn.commit()
        cur.close()
    finally:
        conn.close()


def _seed_tenant_permissions(db_url: str):
    import psycopg2

    permissions_data = [
        ("dashboard.view", "Ver Dashboard", "Dashboard"),
        ("conexiones.view", "Ver Conexiones", "Conexiones API"),
        ("conexiones.create", "Crear Conexion", "Conexiones API"),
        ("conexiones.edit", "Editar Conexion", "Conexiones API"),
        ("conexiones.delete", "Eliminar Conexion", "Conexiones API"),
        ("conexiones.sync", "Sincronizar Sitios", "Conexiones API"),
        ("sitios.view", "Ver Sitios", "Sitios"),
        ("sitios.create", "Crear Sitio", "Sitios"),
        ("sitios.edit", "Editar Sitio", "Sitios"),
        ("sitios.delete", "Eliminar Sitio", "Sitios"),
        ("sitios.sync", "Sincronizar Sitio", "Sitios"),
        ("carpetas.view", "Ver Cuadro de Clasificación", "Series, Subseries y Expedientes"),
        ("carpetas.sync", "Sincronizar Series, Subseries y Expedientes", "Series, Subseries y Expedientes"),
        ("index.view", "Ver Indice Electronico", "Indice Electronico"),
        ("index.generate", "Generar Indice", "Indice Electronico"),
        ("usuarios.view", "Ver Usuarios", "Usuarios"),
        ("usuarios.create", "Crear Usuario", "Usuarios"),
        ("usuarios.edit", "Editar Usuario", "Usuarios"),
        ("usuarios.delete", "Eliminar Usuario", "Usuarios"),
        ("roles.view", "Ver Roles", "Roles"),
        ("roles.create", "Crear Rol", "Roles"),
        ("roles.edit", "Editar Rol", "Roles"),
        ("roles.delete", "Eliminar Rol", "Roles"),
        ("jobs.view", "Ver Trabajos", "Trabajos en Cola"),
        ("jobs.cancel", "Cancelar Trabajos", "Trabajos en Cola"),
        ("jobs.purge", "Limpiar Cola", "Trabajos en Cola"),
        ("listener-logs.view", "Ver Logs API", "Logs API"),
        ("reportes.generate", "Generar Reportes", "Reportes"),
        ("reportes.create", "Crear Reporte Externo", "Reportes"),
        ("reportes.edit", "Editar Reporte Externo", "Reportes"),
        ("reportes.delete", "Eliminar Reporte Externo", "Reportes"),
        ("reportes.toggle", "Activar/Inactivar Reportes", "Reportes"),
        ("tipologias.view", "Ver Tipologías", "Tipologías"),
        ("tipologias.create", "Crear Tipología", "Tipologías"),
        ("tipologias.edit", "Editar Tipología", "Tipologías"),
        ("tipologias.delete", "Eliminar Tipología", "Tipologías"),
    ]

    u = _parse_main_db_url()
    conn = psycopg2.connect(
        dbname=_extract_db_name_from_url(db_url),
        user=u["user"],
        password=u["password"],
        host=u["host"],
        port=u["port"],
    )
    try:
        cur = conn.cursor()

        for code, name, module in permissions_data:
            cur.execute(
                "INSERT INTO permissions (code, name, module, created_at) VALUES (%s, %s, %s, now()) ON CONFLICT (code) DO NOTHING",
                (code, name, module),
            )

        cur.execute(
            "INSERT INTO roles (name, all_permissions, description, created_at) VALUES (%s, %s, %s, now()) ON CONFLICT (name) DO NOTHING",
            ("admin", True, "Administrador del sistema con acceso total"),
        )
        cur.execute(
            "INSERT INTO roles (name, all_permissions, description, created_at) VALUES (%s, %s, %s, now()) ON CONFLICT (name) DO NOTHING",
            ("user", False, "Usuario basico sin permisos especiales"),
        )

        cur.execute(
            "INSERT INTO role_permissions (role_id, permission_id) "
            "SELECT r.id, p.id FROM roles r, permissions p WHERE r.name = 'admin' "
            "ON CONFLICT (role_id, permission_id) DO NOTHING"
        )

        conn.commit()
        cur.close()
    finally:
        conn.close()


@router.post("/{customer_id}/background", summary="Subir imagen de fondo", description="Sube una imagen de fondo para el login del tenant.")
async def upload_background(
    customer_id: int,
    file: UploadFile = File(...),
    db: Session = Depends(get_main_db),
    current_user: MainUser = Depends(get_main_current_user),
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Cliente no encontrado")

    import os
    import time

    upload_dir = "/var/www/html/metafoliadorv2/static/img/img_background"
    os.makedirs(upload_dir, exist_ok=True)

    ext = os.path.splitext(file.filename or "img.jpg")[1].lower()
    if ext not in (".jpg", ".jpeg", ".png", ".gif", ".webp"):
        raise HTTPException(status_code=400, detail="Formato no permitido. Use JPG, PNG, GIF o WebP")

    if customer.background_image:
        old_path = os.path.join(upload_dir, customer.background_image)
        if os.path.exists(old_path):
            os.remove(old_path)

    filename = f"{customer.subdomain}_{int(time.time())}{ext}"
    filepath = os.path.join(upload_dir, filename)

    content = await file.read()
    with open(filepath, "wb") as f:
        f.write(content)

    customer.background_image = filename
    db.commit()

    return {
        "ok": True,
        "background_image": filename,
        "url": f"/static/img/img_background/{filename}",
    }