from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.staticfiles import StaticFiles
from fastapi.middleware.cors import CORSMiddleware
# Reload dashboard routes
from app.database import engine, check_db_connection
# from slowapi.errors import RateLimitExceeded
# from slowapi.middleware import SlowAPIMiddleware

from app.routes.commercial_auto_routes import router as commercial_auto_router
from app.routes.group_health_routes import router as group_health_router
from app.routes.group_life_routes import router as group_life_router
from app.routes.ocean_marine_routes import router as ocean_marine_router
from app.routes.commercial_property_routes import router as commercial_property_router
from app.routes.workers_compensation_routes import router as workers_compensation_router
from app.routes.business_owner_policy_routes import router as business_owner_policy_router
from app.routes.agriculture_insurance_routes import router as agriculture_insurance_router
from app.routes.umbrella_insurance_routes import router as umbrella_insurance_router
from app.routes.health_insurance_routes import router as health_insurance_router
from app.routes.life_insurance_routes import router as life_insurance_router
from app.routes.annuity_insurance_routes import router as annuity_insurance_router
from app.routes.renters_insurance_routes import router as renters_insurance_router
from app.routes.CyberliabilityRouter import CyberliabilityRouter
from app.routes.BusinessumbrellaRouter import BusinessumbrellaRouter
from app.routes.GeneralliabilityRouter import GeneralLiabilityRouter
from app.routes.business_insurance_main_routes import router as business_insurance_main_router
#from app.routes.GrouplifeRouter import GroupLifeRouter
from app.routes.UnifiedcommercialRouter import UnifiedcommercialRouter
from app.routes.ProfessionalliabilityRouter import ProfessionalLiabilityRouter
from app.routes.PersonalautoinsuranceRouter import PersonalAutoInsuranceRouter
from app.routes.HomeinsuranceRouter import HomeInsuranceRouter
from app.routes.business_insurance_routes import router as business_insurance_router
from app.routes.auth_routes import router as auth_router
from app.routes.dashboard_routes import router as dashboard_router
from app.routes.referral_routes import router as referral_router
import logging
import os

from fastapi.exceptions import RequestValidationError
from starlette.exceptions import HTTPException as StarletteHTTPException

app = FastAPI(
    title="Insurance Border API",
    description="Backend services for the Insurance Border Platform.",
    version="1.0.0",
)


@app.exception_handler(StarletteHTTPException)
async def http_exception_handler(request: Request, exc: StarletteHTTPException):
    return JSONResponse(
        status_code=exc.status_code,
        content={"detail": exc.detail or "An error occurred", "status_code": exc.status_code},
    )


@app.exception_handler(RequestValidationError)
async def validation_exception_handler(request: Request, exc: RequestValidationError):
    from fastapi.encoders import jsonable_encoder
    return JSONResponse(
        status_code=422,
        content={"detail": jsonable_encoder(exc.errors()), "message": "Validation Error"},
    )


@app.exception_handler(Exception)
async def global_exception_handler(request: Request, exc: Exception):
    logger = logging.getLogger("uvicorn.error")
    logger.error(f"Global unhandled exception on {request.url.path}: {exc}", exc_info=True)
    return JSONResponse(
        status_code=500,
        content={"detail": "An internal server error occurred.", "error": str(exc)},
    )


@app.on_event("startup")
def on_startup():
    # Register security models and all insurance models in SQLModel metadata
    import app.models
    import app.models.security_models
    from sqlmodel import SQLModel, text
    from db import engine
    import logging
    logger = logging.getLogger("uvicorn.error")
    logger.info("Creating database tables on startup...")
    SQLModel.metadata.create_all(engine)
    logger.info("Database tables created successfully.")
    
    # Run DB migration for users table
    try:
        with engine.begin() as conn:
            from sqlalchemy import inspect
            inspector = inspect(conn)
            user_cols = [c['name'] for c in inspector.get_columns('users')] if 'users' in inspector.get_table_names() else []
            
            user_migrations = [
                ('role', "ALTER TABLE users ADD COLUMN role VARCHAR DEFAULT 'customer' NOT NULL"),
                ('mobile', "ALTER TABLE users ADD COLUMN mobile VARCHAR UNIQUE NULL"),
                ('password_changed_at', "ALTER TABLE users ADD COLUMN password_changed_at TIMESTAMP NULL"),
                ('is_email_verified', "ALTER TABLE users ADD COLUMN is_email_verified BOOLEAN DEFAULT FALSE NOT NULL"),
                ('is_mobile_verified', "ALTER TABLE users ADD COLUMN is_mobile_verified BOOLEAN DEFAULT FALSE NOT NULL"),
                ('failed_login_attempts', "ALTER TABLE users ADD COLUMN failed_login_attempts INTEGER DEFAULT 0 NOT NULL"),
                ('lockout_until', "ALTER TABLE users ADD COLUMN lockout_until TIMESTAMP NULL"),
                ('country', "ALTER TABLE users ADD COLUMN country VARCHAR NULL"),
                ('state', "ALTER TABLE users ADD COLUMN state VARCHAR NULL"),
                ('city', "ALTER TABLE users ADD COLUMN city VARCHAR NULL"),
                ('area_code', "ALTER TABLE users ADD COLUMN area_code VARCHAR NULL"),
                ('avatar', "ALTER TABLE users ADD COLUMN avatar TEXT NULL"),
            ]

            for col_name, sql_stmt in user_migrations:
                if col_name not in user_cols:
                    logger.info(f"Adding {col_name} column to users table...")
                    try:
                        conn.execute(text(sql_stmt))
                    except Exception as e:
                        logger.warning(f"Could not add column {col_name}: {e}")

            # Check and add lead_source column to all insurance tables
            from sqlalchemy import inspect
            inspector = inspect(conn)
            all_tables = inspector.get_table_names()
            skip_tables = {"users", "refresh_tokens", "verification_tokens", "audit_logs", "alembic_version"}
            for t_name in all_tables:
                if t_name not in skip_tables:
                    cols = [c['name'] for c in inspector.get_columns(t_name)]
                    if 'lead_source' not in cols:
                        logger.info(f"Adding lead_source column to {t_name} table...")
                        conn.execute(text(f"ALTER TABLE {t_name} ADD COLUMN lead_source VARCHAR NULL"))
            
            # Check for agriculture paths
            agri_columns = [c['name'] for c in inspector.get_columns('agriculture_insurance')] if 'agriculture_insurance' in all_tables else []
            if 'gps_field_map_path' not in agri_columns and 'agriculture_insurance' in all_tables:
                conn.execute(text("ALTER TABLE agriculture_insurance ADD COLUMN gps_field_map_path VARCHAR NULL"))
            if 'vet_records_path' not in agri_columns and 'agriculture_insurance' in all_tables:
                conn.execute(text("ALTER TABLE agriculture_insurance ADD COLUMN vet_records_path VARCHAR NULL"))
                
            # Add user_id and deleted_at columns to other tables on PostgreSQL
            if conn.dialect.name != "sqlite":
                for table_name in SQLModel.metadata.tables.keys():
                    if table_name in ["users", "refresh_tokens", "verification_tokens", "audit_logs"]:
                        continue
                    # Check if user_id exists
                    res_user_id = conn.execute(text(
                        "SELECT EXISTS (SELECT 1 FROM information_schema.columns "
                        "WHERE table_name = :table_name AND column_name = 'user_id')"
                    ), {"table_name": table_name}).scalar()
                    if not res_user_id:
                        logger.info(f"Adding user_id column to {table_name} table...")
                        conn.execute(text(f"ALTER TABLE {table_name} ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE SET NULL"))

                    # Check if deleted_at exists
                    res_deleted_at = conn.execute(text(
                        "SELECT EXISTS (SELECT 1 FROM information_schema.columns "
                        "WHERE table_name = :table_name AND column_name = 'deleted_at')"
                    ), {"table_name": table_name}).scalar()
                    if not res_deleted_at:
                        logger.info(f"Adding deleted_at column to {table_name} table...")
                        conn.execute(text(f"ALTER TABLE {table_name} ADD COLUMN deleted_at TIMESTAMP NULL"))
    except Exception as e:
        logger.error(f"Migration error: {e}")


@app.get("/")
def root():
    """Root endpoint — confirms the backend is running."""
    return {"message": "Backend running", "service": "Insurance Border API"}


# Include routers
app.include_router(commercial_auto_router)
app.include_router(group_health_router)
app.include_router(group_life_router)
app.include_router(ocean_marine_router)
app.include_router(commercial_property_router)
app.include_router(workers_compensation_router)
app.include_router(business_owner_policy_router)
app.include_router(agriculture_insurance_router)
app.include_router(umbrella_insurance_router)
app.include_router(health_insurance_router)
app.include_router(life_insurance_router)
app.include_router(annuity_insurance_router)
app.include_router(renters_insurance_router)

app.include_router(CyberliabilityRouter)
app.include_router(BusinessumbrellaRouter)
app.include_router(GeneralLiabilityRouter)
app.include_router(business_insurance_main_router)
#app.include_router(GroupLifeRouter)
app.include_router(UnifiedcommercialRouter)
app.include_router(ProfessionalLiabilityRouter)
app.include_router(PersonalAutoInsuranceRouter)
app.include_router(HomeInsuranceRouter)

app.include_router(business_insurance_router)
app.include_router(auth_router, prefix="/api/auth")
app.include_router(auth_router, prefix="/api/v1/auth")
app.include_router(dashboard_router)
app.include_router(referral_router)

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
UPLOADS_DIR = os.path.join(BASE_DIR, "uploads")
STATIC_DIR = os.path.join(BASE_DIR, "static")

# Mount static and uploads directories
app.mount("/static", StaticFiles(directory=STATIC_DIR), name="static")
app.mount("/uploads", StaticFiles(directory=UPLOADS_DIR), name="uploads")

app.add_middleware(
    CORSMiddleware,
    allow_origins=[
        "http://localhost:5173",
        "http://localhost:3000",
        "http://localhost:8000",
        "https://dev.insuranceborder.com",
        "http://dev.insuranceborder.com",
        "https://qa.insuranceborder.com",
        "http://qa.insuranceborder.com",
        "https://staging.insuranceborder.com",
        "http://staging.insuranceborder.com",
        "https://insuranceborder.com",
        "http://insuranceborder.com",
        "https://www.insuranceborder.com",
    ],
    allow_origin_regex=r"https?://.*\.insuranceborder\.com.*",
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

from app.core.middleware import JWTAuthenticationMiddleware
app.add_middleware(JWTAuthenticationMiddleware)

@app.get("/health")
async def health_check():
    """Health check endpoint for monitoring."""
    return {"status": "ok", "service": "Insurance Border API"}


@app.get("/db-test")
def db_test():
    """Verifies the database connection is reachable."""
    if check_db_connection():
        return {"database": "connected"}
    return JSONResponse(
        status_code=503,
        content={"database": "unreachable", "detail": "Could not connect to the database."},
    )


from pydantic import BaseModel
from typing import Optional
from sqlmodel import Session, text
from fastapi import Depends, HTTPException
from db import get_session

class SaveReferralSourceRequest(BaseModel):
    reference_number: Optional[str] = None
    quote_id: Optional[str] = None
    lead_source: str
    quote_type: Optional[str] = None

@app.post("/api/v1/save-referral-source")
def save_referral_source(
    payload: SaveReferralSourceRequest,
    session: Session = Depends(get_session)
):
    ref = payload.reference_number
    qid = payload.quote_id
    source = payload.lead_source

    if not ref and not qid:
        raise HTTPException(status_code=400, detail="Either reference_number or quote_id must be provided")

    updated = False
    tables = [
        "life_insurance", "health_insurance", "homeinsurance", "personalautoinsurance",
        "agriculture_insurance", "annuity_insurance", "business_owner_policy",
        "commercial_property", "commercial_auto", "renters_insurance", "umbrella_insurance"
    ]

    for table in tables:
        try:
            if ref:
                stmt = text(f"UPDATE {table} SET lead_source = :source WHERE transaction_no = :ref")
                res = session.execute(stmt, {"source": source, "ref": ref})
                if res.rowcount > 0:
                    updated = True
            if not updated and qid and qid.isdigit():
                stmt = text(f"UPDATE {table} SET lead_source = :source WHERE id = :qid")
                res = session.execute(stmt, {"source": source, "qid": int(qid)})
                if res.rowcount > 0:
                    updated = True
        except Exception:
            continue

    if updated:
        session.commit()
        return {"status": "success", "message": "Referral source updated successfully", "lead_source": source}

    return {"status": "acknowledged", "message": "Referral source received", "lead_source": source}