"""
Separate database configuration for GCP Cloud SQL PostgreSQL.

  Instance : larmedix-db
  Database : larmedix

Priority order for connection:
  1. DATABASE_URL env var (recommended for HuggingFace Secrets / GCP)
     e.g. postgresql+psycopg2://USER:PASSWORD@HOST:5432/larmedix
  2. Cloud SQL Python Connector (if USE_CLOUD_SQL_CONNECTOR=true)
     Requires: INSTANCE_CONNECTION_NAME="PROJECT:REGION:larmedix-db"
  3. TCP / Unix-socket from individual env vars:
     DB_USER, DB_PASSWORD, DB_HOST (public IP or 127.0.0.1 via proxy),
     DB_PORT (default 5432), DB_NAME (default larmedix),
     DB_UNIX_SOCKET (e.g. /cloudsql/PROJECT:REGION:larmedix-db)
  4. Local SQLite fallback (dev only): sqlite:///hospital.db

On first successful connection, init_database(app) automatically
creates all tables (db.create_all()) and seeds the superadmin user.
We will plug real GCP credentials in later via env vars / secrets —
no code change needed.
"""
import os


# ---- GCP defaults (instance / database names are fixed) ----
GCP_INSTANCE_NAME = os.environ.get("GCP_INSTANCE_NAME", "larmedix-db")
DEFAULT_DB_NAME = os.environ.get("DB_NAME", "larmedix")


def get_database_uri():
    """Build the SQLAlchemy database URI from environment."""
    # 1. Full URL wins (HF Space Secrets -> DATABASE_URL)
    database_url = os.environ.get("DATABASE_URL")
    if database_url:
        # Some providers give postgres:// -> fix to postgresql://
        if database_url.startswith("postgres://"):
            database_url = database_url.replace("postgres://", "postgresql://", 1)
        # Default to psycopg2 driver if no driver specified
        if database_url.startswith("postgresql://"):
            database_url = database_url.replace(
                "postgresql://", "postgresql+psycopg2://", 1
            )
        return database_url

    # 2. Individual parts -> build TCP URL (Cloud SQL public IP / proxy)
    db_user = os.environ.get("DB_USER")
    db_password = os.environ.get("DB_PASSWORD", "")
    db_host = os.environ.get("DB_HOST")  # Cloud SQL public IP
    db_port = os.environ.get("DB_PORT", "5432")
    db_name = os.environ.get("DB_NAME", DEFAULT_DB_NAME)

    # Unix socket path (Cloud Run / GCE with cloud-sql-proxy):
    # e.g. /cloudsql/PROJECT:REGION:larmedix-db
    unix_socket = os.environ.get("DB_UNIX_SOCKET")

    if db_user and unix_socket:
        # Connect via Unix domain socket
        return (
            f"postgresql+psycopg2://{db_user}:{db_password}@/{db_name}"
            f"?host={unix_socket}"
        )

    if db_user and db_host:
        return (
            f"postgresql+psycopg2://{db_user}:{db_password}"
            f"@{db_host}:{db_port}/{db_name}"
        )

    # 3. Local fallback (dev / HF build without secrets)
    return "sqlite:///hospital.db"


def is_postgres(uri: str) -> bool:
    return uri.startswith("postgresql") or uri.startswith("postgres")


def get_engine_options(uri: str) -> dict:
    """Sensible pool defaults for Cloud SQL Postgres."""
    if is_postgres(uri):
        return {
            "pool_size": int(os.environ.get("DB_POOL_SIZE", "5")),
            "max_overflow": int(os.environ.get("DB_MAX_OVERFLOW", "10")),
            "pool_timeout": 30,
            "pool_recycle": 1800,
            "pool_pre_ping": True,
        }
    # SQLite (local dev)
    return {"connect_args": {"timeout": 30}}


def init_database(app):
    """
    Call once inside app context after db.init_app(app).
    Automatically creates all tables on first connect + seeds superadmin.
    Safe to call on every startup (create_all is idempotent).
    """
    # Import here to avoid circular imports (models defines db)
    from models import db, User
    from werkzeug.security import generate_password_hash

    with app.app_context():
        db.create_all()

        # Auto-migrate: older Postgres DBs have user.password VARCHAR(150),
        # but Werkzeug scrypt hashes are ~160+ chars. Widen if needed.
        # Also widen filepath columns for Firebase Storage URLs.
        try:
            from sqlalchemy import text
            if is_postgres(str(db.engine.url)):
                with db.engine.begin() as conn:
                    conn.execute(text(
                        'ALTER TABLE "user" ALTER COLUMN password TYPE VARCHAR(500)'
                    ))
                    conn.execute(text(
                        'ALTER TABLE report ALTER COLUMN filepath TYPE VARCHAR(1024)'
                    ))
                    conn.execute(text(
                        'ALTER TABLE patient_profile ALTER COLUMN insurance_document TYPE VARCHAR(1024)'
                    ))
        except Exception as e:
            print(f"column migrate skipped: {e}")

        # Superadmin lives in the DB (User table, role=superadmin).
        # Seeded once with defaults; afterwards DB is source of truth.
        # Changing Secrets will NOT overwrite it — edit via SQL/app.
        sa = User.query.filter_by(role="superadmin").first()
        if not sa:
            db.session.add(
                User(
                    email="superadmin@system.com",
                    username="superadmin",
                    password=generate_password_hash("superadmin123"),
                    role="superadmin",
                    hospital_id=None,
                )
            )
            db.session.commit()
            print("Superadmin seeded (superadmin@system.com).")