main.py

from fastapi import FastAPI, Query, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from fastapi.staticfiles import StaticFiles
from datetime import date
from dateutil.relativedelta import relativedelta
from pydantic import BaseModel
from typing import Optional, List
import os
import psycopg2
from psycopg2.extras import RealDictCursor
from dotenv import load_dotenv
import logging
import json
from fastapi_cache import FastAPICache
from fastapi_cache.backends.inmemory import InMemoryBackend
from fastapi_cache.decorator import cache

# Setup logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

load_dotenv()

class CashFlowRequest(BaseModel):
    start_date: date
    end_date: date
    total_capital: float
    currency: str = "USD"
    project_name: Optional[str] = None

app = FastAPI(
    title="Construction Cash Flow API",
    description="S-curve rational polynomial cash flow calculator and project manager",
    version="1.1.2",
)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["GET", "POST"],
    allow_headers=["*"],
)

@app.on_event("startup")
async def startup():
    FastAPICache.init(InMemoryBackend(), prefix="fastapi-cache")

def get_db_conn():
    # If on Render, prefer Internal URL. If local, prefer public DATABASE_URL.
    is_render = os.getenv("RENDER") is not None
    db_url = None
    
    if is_render:
        db_url = os.getenv("INTERNAL_DATABASE_URL") or os.getenv("DATABASE_URL")
    else:
        db_url = os.getenv("DATABASE_URL") or os.getenv("INTERNAL_DATABASE_URL")
    
    if not db_url:
        logger.error("No database URL found in environment variables.")
        return None
    
    try:
        # On Render, external connections usually need sslmode=require
        conn_str = db_url
        if "render.com" in conn_str and "sslmode" not in conn_str:
            separator = "&" if "?" in conn_str else "?"
            conn_str += f"{separator}sslmode=require"
            
        # Add a connect_timeout to prevent hanging the server on connection issues
        return psycopg2.connect(conn_str, cursor_factory=RealDictCursor, connect_timeout=5)
    except Exception as e:
        logger.error(f"Database connection error: {e}")
        return None


_A =  0.707205007505421
_B = -0.0667363632084369
_C =  0.0104156261597405
_D =  0.00174018878670432
_E = -0.00125942062519033
_F = -0.0000219638781679794
_G =  0.0000116670093891432
_H =  1.35120107553353e-7
_I =  1.21646666783187e-7
_J = -3.14406828446384e-10

def _cumulative_pct(x: float) -> float:
    num = _A + _C*x + _E*x**2 + _G*x**3 + _I*x**4
    den = 1  + _B*x + _D*x**2 + _F*x**3 + _H*x**4 + _J*x**5
    return max(0.0, min(100.0, num / den))

def _compute_cashflow(start: date, end: date, total_capital: float, currency: str):
    if end <= start:
        raise ValueError("end date must be after start date")

    duration_months = (
        (end.year - start.year) * 12 + (end.month - start.month)
        + (end.day - start.day) / 30.0
    )

    total_months = int(duration_months * 1.20) + 1
    rows = []
    prev_cum = 0.0
    peak_month = None
    peak_value = 0.0

    for m in range(total_months + 1):
        month_date = start + relativedelta(months=m)
        pct_of_time = min((m / duration_months) * 100.0, 120.0)
        cum_pct    = _cumulative_pct(pct_of_time)
        period_pct = max(0.0, cum_pct - prev_cum)
        cashflow   = round(total_capital * period_pct / 100.0, 2)
        cum_cf     = round(total_capital * cum_pct / 100.0, 2)

        row = {
            "month_number":  m,
            "month_start":   month_date.isoformat(),
            "pct_of_time":   round(pct_of_time, 4),
            "cum_pct":       round(cum_pct, 4),
            "period_pct":    round(period_pct, 4),
            "cashflow":      cashflow,
            "cum_cashflow":  cum_cf,
            "phase": (
                "early"     if pct_of_time <= 40 else
                "peak"      if pct_of_time <= 80 else
                "wind_down"
            ),
        }
        rows.append(row)
        if cashflow > peak_value:
            peak_value = cashflow
            peak_month = row
        prev_cum = cum_pct
        if pct_of_time >= 120.0:
            break

    mid50 = next((r for r in rows if r["cum_pct"] >= 50), rows[-1])
    summary = {
        "project_name":       None,
        "start_date":         start.isoformat(),
        "end_date":           end.isoformat(),
        "duration_months":    round(duration_months, 2),
        "effective_months":   total_months,
        "total_capital":      total_capital,
        "currency":           currency,
        "peak_monthly_spend": round(peak_value, 2),
        "peak_month_number":  peak_month["month_number"] if peak_month else None,
        "peak_month_date":    peak_month["month_start"]  if peak_month else None,
        "half_capital_month": mid50["month_number"],
        "half_capital_date":  mid50["month_start"],
    }
    
    milestones_def = [
        {"id": "M1", "name": "Mobilisation", "threshold": 10},
        {"id": "M2", "name": "Quarter capital", "threshold": 25},
        {"id": "M3", "name": "Half capital", "threshold": 50},
        {"id": "M4", "name": "Three-quarter", "threshold": 75},
        {"id": "M5", "name": "Practical compl.", "threshold": 90},
        {"id": "M6", "name": "Final closeout", "threshold": 100},
    ]
    
    milestones = []
    for m_def in milestones_def:
        target = m_def["threshold"]
        m_row = next((r for r in rows if r["cum_pct"] >= target - 0.001), rows[-1])
        milestones.append({
            "id": m_def["id"],
            "name": m_def["name"],
            "threshold": f"{m_def['threshold']}% capital",
            "target_date": m_row["month_start"][:7],
            "month_number": f"M{m_row['month_number']}",
            "capital_deployed": m_row["cum_cashflow"],
            "phase": m_row["phase"]
        })

    return summary, rows, milestones

@app.get("/api/health", tags=["health"])
def health():
    db_url = os.getenv("DATABASE_URL")
    internal_url = os.getenv("INTERNAL_DATABASE_URL")
    
    return {
        "status": "ok", 
        "version": "1.1.2", 
        "db_url_present": db_url is not None,
        "internal_url_present": internal_url is not None,
        "db_url_preview": f"{db_url[:15]}..." if db_url else None,
        "environment": "render" if os.getenv("RENDER") else "local"
    }

@app.get("/api/meta", tags=["projects"])
@cache(expire=3600)  # Cache for 1 hour
def get_metadata():
    conn = get_db_conn()
    if not conn: return {"countries": [], "sectors": [], "statuses": []}
    try:
        cur = conn.cursor()
        cur.execute("SELECT country, COUNT(*) as count FROM projects WHERE country IS NOT NULL AND country != '' GROUP BY country ORDER BY country")
        countries = [{"name": r["country"], "count": r["count"]} for r in cur.fetchall()]
        
        cur.execute("SELECT sector, COUNT(*) as count FROM projects WHERE sector IS NOT NULL AND sector != '' GROUP BY sector ORDER BY sector")
        sectors = [{"name": r["sector"], "count": r["count"]} for r in cur.fetchall()]
        
        cur.execute("SELECT status, COUNT(*) as count FROM projects WHERE status IS NOT NULL AND status != '' GROUP BY status ORDER BY status")
        statuses = [{"name": r["status"], "count": r["count"]} for r in cur.fetchall()]
        
        cur.close()
        conn.close()
        return {"countries": countries, "sectors": sectors, "statuses": statuses}
    except:
        if conn: conn.close()
        return {"countries": [], "sectors": [], "statuses": []}

@app.get("/api/countries", tags=["projects"])
@cache(expire=3600)  # Cache for 1 hour
def get_countries():
    conn = get_db_conn()
    if not conn: return []
    try:
        cur = conn.cursor()
        cur.execute("SELECT country, COUNT(*) as count FROM projects WHERE country IS NOT NULL AND country != '' GROUP BY country ORDER BY country")
        countries = [{"name": r["country"], "count": r["count"]} for r in cur.fetchall()]
        cur.close()
        conn.close()
        return countries
    except:
        if conn: conn.close()
        return []

@app.get("/api/projects", tags=["projects"])
@cache(expire=3600)
def get_projects(
    limit: int = 50, 
    offset: int = 0,
    country: Optional[str] = None,
    sector: Optional[str] = None,
    status: Optional[str] = None,
    search: Optional[str] = None
):
    conn = get_db_conn()
    if not conn:
        raise HTTPException(status_code=503, detail="Database connection failed")

    try:
        cur = conn.cursor()
        
        query = """
            SELECT p.id, p.project_name, p.country, p.sector, p.status, p.total_capital, 
                   p.currency, p.start_date, p.end_date, p.developer, p.summary,
                   p.main_contractor, p.lead_consultant, p.architect, p.pmc, p.cost_consultant,
                   p.structural_engineer, p.mep_contractor, p.landscape_architect
            FROM projects p
            LEFT JOIN project_summary s ON p.id = s.project_id
            WHERE 1=1
        """
        params = []
        
        if country:
            query += " AND p.country = %s"
            params.append(country)
        if sector:
            query += " AND p.sector = %s"
            params.append(sector)
        if status:
            query += " AND p.status = %s"
            params.append(status)
        if search:
            query += " AND (p.project_name ILIKE %s OR p.developer ILIKE %s)"
            params.extend([f"%{search}%", f"%{search}%"])
            
        query += " ORDER BY (p.start_date IS NOT NULL) DESC, p.start_date DESC, p.created_at DESC LIMIT %s OFFSET %s"
        params.extend([limit, offset])
        
        # Get total count for paging
        count_query = f"SELECT COUNT(*) as total FROM projects p WHERE 1=1"
        count_params = []
        if country:
            count_query += " AND p.country = %s"
            count_params.append(country)
        if sector:
            count_query += " AND p.sector = %s"
            count_params.append(sector)
        if status:
            count_query += " AND p.status = %s"
            count_params.append(status)
        if search:
            count_query += " AND (p.project_name ILIKE %s OR p.developer ILIKE %s)"
            count_params.extend([f"%{search}%", f"%{search}%"])
            
        cur.execute(count_query, tuple(count_params))
        total = cur.fetchone()["total"]
        
        cur.execute(query, tuple(params))
        projects = cur.fetchall()
        cur.close()
        conn.close()
        return {"projects": projects, "total": total}
    except Exception as e:
        if conn: conn.close()
        logger.error(f"Query error: {e}")
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/projects/{project_id}/cashflow", tags=["projects"])
@cache(expire=3600)
def get_project_cashflow(project_id: str):
    conn = get_db_conn()
    if not conn:
        raise HTTPException(status_code=503, detail="Database connection failed")

    try:
        cur = conn.cursor()
        
        # 1. Fetch Summary (All project metadata + summary metrics)
        cur.execute("""
            SELECT p.*, s.effective_months, s.peak_monthly_spend, s.peak_month_number, 
                   s.peak_month_date, s.half_capital_month, s.half_capital_date
            FROM projects p
            LEFT JOIN project_summary s ON p.id = s.project_id
            WHERE p.id = %s
        """, (project_id,))
        summary = cur.fetchone()
        if not summary:
            raise HTTPException(status_code=404, detail="Project not found")

        # 2. Fetch Monthly Data
        cur.execute("SELECT * FROM cashflow_monthly WHERE project_id = %s ORDER BY month_number", (project_id,))
        monthly = cur.fetchall()

        # 3. Calculate Milestones (thresholds)
        milestones_def = [
            {"id": "M1", "name": "Mobilisation", "threshold": 10},
            {"id": "M2", "name": "Quarter capital", "threshold": 25},
            {"id": "M3", "name": "Half capital", "threshold": 50},
            {"id": "M4", "name": "Three-quarter", "threshold": 75},
            {"id": "M5", "name": "Practical compl.", "threshold": 90},
            {"id": "M6", "name": "Final closeout", "threshold": 100},
        ]
        
        milestones = []
        for m_def in milestones_def:
            target = m_def["threshold"]
            m_row = next((r for r in monthly if r["cum_pct"] >= target - 0.001), monthly[-1] if monthly else None)
            if m_row:
                milestones.append({
                    "id": m_def["id"],
                    "name": m_def["name"],
                    "threshold": f"{m_def['threshold']}% capital",
                    "target_date": m_row["month_start"].isoformat()[:7] if hasattr(m_row["month_start"], 'isoformat') else str(m_row["month_start"])[:7],
                    "month_number": f"M{m_row['month_number']}",
                    "capital_deployed": float(m_row["cum_cashflow"]),
                    "phase": m_row["phase"]
                })

        cur.close()
        conn.close()
        
        # Helper to format dates robustly
        def fmt_date(d):
            if not d: return None
            if hasattr(d, "isoformat"): return d.isoformat()
            return str(d) # Already a string or other type

        # Helper to parse and clean JSON fields (handles double-encoding)
        def clean_json_field(val):
            if not val: return []
            try:
                # If it's a string, try parsing it
                data = json.loads(val) if isinstance(val, str) else val
                # If it's STILL a string (double encoded), parse again
                if isinstance(data, str): data = json.loads(data)
                return data if isinstance(data, list) else [data]
            except:
                return [val] if val else []

        formatted_summary = {
            "id":                 str(summary["id"]),
            "project_name":       summary["project_name"],
            "description":        summary["description"],
            "city":               summary["city"],
            "country":            summary["country"],
            "site":               summary["site"],
            "sector":             summary["sector"],
            "status":             summary["status"],
            "funding_type":       summary["funding_type"],
            "developer":          summary["developer"],
            "main_contractor":    summary["main_contractor"],
            "lead_consultant":    summary["lead_consultant"],
            "architect":          summary["architect"],
            "pmc":                summary["pmc"],
            "cost_consultant":    summary["cost_consultant"],
            "structural_engineer": summary["structural_engineer"],
            "mep_contractor":     summary["mep_contractor"],
            "landscape_architect": summary["landscape_architect"],
            "capacity":           summary["capacity"],
            "capacity_unit":      summary["capacity_unit"],
            "size_sqm":           summary["size_sqm"],
            "start_date":         fmt_date(summary["start_date"]),
            "end_date":           fmt_date(summary["end_date"]),
            "contract_award_date": fmt_date(summary["contract_award_date"]),
            "duration_months":    float(summary["duration_months"]) if summary["duration_months"] else 0,
            "effective_months":   summary.get("effective_months", 0) or 0,
            "total_capital":      float(summary["total_capital"]) if summary["total_capital"] else 0,
            "currency":           summary["currency"],
            "peak_monthly_spend": float(summary["peak_monthly_spend"]) if summary["peak_monthly_spend"] else 0,
            "peak_month_number":  summary["peak_month_number"],
            "peak_month_date":    fmt_date(summary["peak_month_date"]),
            "half_capital_month": summary["half_capital_month"],
            "half_capital_date":  fmt_date(summary["half_capital_date"]),
            "sources":            clean_json_field(summary["sources"]),
            "tags":               clean_json_field(summary["tags"]),
            "x_links":            clean_json_field(summary["x_links"]),
            "last_audited":       fmt_date(summary["last_audited"]),
            "project_summary":    summary["summary"],
        }

        # Format monthly rows
        formatted_monthly = []
        for r in monthly:
            formatted_monthly.append({
                "month_number": r["month_number"],
                "month_start":  r["month_start"].isoformat(),
                "pct_of_time":  float(r["pct_of_time"]),
                "cum_pct":      float(r["cum_pct"]),
                "period_pct":   float(r["period_pct"]),
                "cashflow":     float(r["cashflow"]),
                "cum_cashflow": float(r["cum_cashflow"]),
                "phase":        r["phase"]
            })

        return JSONResponse({"summary": formatted_summary, "monthly": formatted_monthly, "milestones": milestones})
    except Exception as e:
        if conn: conn.close()
        logger.error(f"Cashflow detail error: {e}")
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/cashflow", tags=["cashflow"])
def get_cashflow(
    start_date:    date  = Query(...),
    end_date:      date  = Query(...),
    total_capital: float = Query(...),
    currency:      str   = Query("USD"),
    project_name:  str   = Query(None),
):
    try:
        summary, rows, milestones = _compute_cashflow(start_date, end_date, total_capital, currency)
        summary["project_name"] = project_name
        return JSONResponse({"summary": summary, "monthly": rows, "milestones": milestones})
    except ValueError as e:
        raise HTTPException(status_code=422, detail=str(e))

@app.post("/api/cashflow", tags=["cashflow"])
def post_cashflow(body: CashFlowRequest):
    try:
        summary, rows, milestones = _compute_cashflow(
            body.start_date, body.end_date, body.total_capital, body.currency
        )
        summary["project_name"] = body.project_name
        return JSONResponse({"summary": summary, "monthly": rows, "milestones": milestones})
    except ValueError as e:
        raise HTTPException(status_code=422, detail=str(e))

# Serve frontend
static_dir = os.path.join(os.path.dirname(__file__), "static")
if os.path.exists(static_dir):
    app.mount("/", StaticFiles(directory=static_dir, html=True), name="static")