from fastapi import FastAPI, Request, HTTPException
from fastapi.responses import HTMLResponse, FileResponse, JSONResponse, Response
from fastapi.staticfiles import StaticFiles
from fastapi.middleware.cors import CORSMiddleware
from fastapi.templating import Jinja2Templates
from typing import List
import os
import pandas as pd
import logging
import json
from pydantic import BaseModel, validator
import threading
from datetime import datetime
import sys
import csv

app = FastAPI()

# Add CORS middleware
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Set up templates and static files
templates = Jinja2Templates(directory="templates")
app.mount("/static", StaticFiles(directory="static"), name="static")
app.mount("/assets", StaticFiles(directory="templates/assets"), name="assets")

# Constants
OAT_NATURE_DATA_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "oat_nature_data")
OAT_NATURE_JSON_FILE = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only_data.json')

# Helper function to clean dataframe for JSON serialization
def clean_dataframe_for_json(df):
    """Clean pandas DataFrame for JSON serialization by handling NaN and infinity values."""
    if df.empty:
        return []
    # Replace NaN, infinity values with None/null
    df_clean = df.replace([float('inf'), float('-inf')], None)
    
    # Convert to object type to allow None values, then replace NaN
    for col in df_clean.columns:
        if df_clean[col].isna().any():
            df_clean[col] = df_clean[col].astype('object').where(df_clean[col].notna(), None)
    
    return df_clean.to_dict('records')

def get_schools_df():
    try:
        school_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_schools.csv')
        if not os.path.exists(school_file):
            # Create the file with headers if it doesn't exist
            df = pd.DataFrame(columns=['URN', 'Establishment name'])
            df.to_csv(school_file, index=False)
            return df
        
        # Read the CSV file
        df = pd.read_csv(school_file)
        
        # Clean up any empty rows
        df = df.dropna(how='all')
        
        # Ensure URN is numeric and remove any decimal points
        df['URN'] = df['URN'].fillna(0).astype(int)
        
        # Remove any rows where URN is 0
        df = df[df['URN'] != 0]
        
        # Rename columns to match our API response format
        df = df.rename(columns={
            'URN': 'urn',
            'Establishment name': 'schoolName'
        })
        
        return df
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error accessing schools data: {str(e)}")

def run_script():
    # Force reload the module and reconfigure its logging
    import logging
    
    log_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.log')
    
    # Remove the module from cache to force reload
    modules_to_remove = [
        'oat_nature_data_utils.explore_oat_arcgis_fields',
        'oat_nature_data_utils'
    ]
    for mod_name in modules_to_remove:
        if mod_name in sys.modules:
            del sys.modules[mod_name]
    
    # Now import the module fresh
    from oat_nature_data_utils.explore_oat_arcgis_fields import logger as script_logger
    
    # Reconfigure the logger to ensure it writes to our log file
    script_logger.setLevel(logging.INFO)
    
    # Remove existing file handlers
    for handler in script_logger.handlers[:]:
        if isinstance(handler, logging.FileHandler):
            script_logger.removeHandler(handler)
            handler.close()
    
    # Add new file handler that writes to our log file
    file_handler = logging.FileHandler(log_file, mode='a', encoding='utf-8')
    file_handler.setLevel(logging.INFO)
    file_formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
    file_handler.setFormatter(file_formatter)
    script_logger.addHandler(file_handler)
    
    # Also ensure root logger has the handler
    root_logger = logging.getLogger()
    root_logger.setLevel(logging.INFO)
    # Check if root logger already has our file handler
    has_file_handler = any(isinstance(h, logging.FileHandler) and h.baseFilename == log_file for h in root_logger.handlers)
    if not has_file_handler:
        root_logger.addHandler(file_handler)
    
    # Use the script's logger
    logger = script_logger
    
    try:
        logger.info("Script thread started...")
        
        # Import required modules
        try:
            import pandas as pd
            import geopandas as gpd
            from arcgis.features import FeatureLayer
            logger.info("Successfully imported required modules")
        except ImportError as e:
            logger.error(f"Error importing required modules: {e}")
            logger.error("Please ensure all required packages are installed: pandas, geopandas, arcgis")
            logger.info("SCRIPT_FAILED")
            return
        except Exception as e:
            logger.error(f"Unexpected error importing modules: {e}")
            logger.info("SCRIPT_FAILED")
            return
        
        # Load OAT URNs
        try:
            school_file = os.path.join(OAT_NATURE_DATA_DIR, "OAT_schools.csv")
            oat_df = pd.read_csv(school_file)
            logger.info(f"Successfully loaded OAT URNs from CSV. Found {len(oat_df)} schools.")
        except FileNotFoundError:
            logger.error("OAT_schools.csv file not found. Please ensure the file exists.")
            logger.info("SCRIPT_FAILED")
            return
        except Exception as e:
            logger.error(f"Error loading OAT URNs: {e}")
            logger.info("SCRIPT_FAILED")
            return
        
        # Export data - this will use the detailed logging from explore_oat_arcgis_fields.py
        try:
            from oat_nature_data_utils.explore_oat_arcgis_fields import export_all_layers_to_excel, layers
            logger.info("Starting data export process...")
            export_all_layers_to_excel(layers, oat_df)
            logger.info("Successfully exported data")
            logger.info("SCRIPT_COMPLETED_SUCCESSFULLY")
        except ImportError as e:
            logger.error(f"Error importing export functions: {e}")
            logger.info("SCRIPT_FAILED")
            return
        except Exception as e:
            logger.error(f"Error exporting data: {e}")
            logger.info("SCRIPT_FAILED")
            return
            
    except Exception as e:
        logger.error(f"Error in run_script: {e}")
        logger.info("SCRIPT_FAILED")
        return

# School Management Models
class School(BaseModel):
    urn: int
    schoolName: str

    @validator('urn')
    def validate_urn(cls, v):
        if v <= 0:
            raise ValueError('URN must be a positive number')
        return v

    @validator('schoolName')
    def validate_school_name(cls, v):
        if not v.strip():
            raise ValueError('School name cannot be empty')
        return v.strip()

class CarbonBaselineEntry(BaseModel):
    CarbonArea: str
    CarbonValueKGCO2e: float

# Routes
@app.get("/favicon.ico")
async def favicon():
    return FileResponse("templates/assets/favicon.ico")

@app.get("/oat-nature", response_class=HTMLResponse)
async def get_oat_nature_page(request: Request):
    return templates.TemplateResponse("oat_nature.html", {"request": request})

@app.get("/api/oat-nature/data")
async def get_oat_nature_data():
    """Get OAT Nature data for visualization"""
    try:
        # First, try to load from JSON file (faster)
        if os.path.exists(OAT_NATURE_JSON_FILE):
            try:
                with open(OAT_NATURE_JSON_FILE, 'r', encoding='utf-8') as f:
                    result = json.load(f)
                logging.info("Loaded OAT nature data from JSON file")
                return JSONResponse(content=result)
            except Exception as e:
                logging.warning(f"Error reading JSON file, falling back to Excel: {str(e)}")
        
        # Fallback to Excel file (backward compatibility)
        excel_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.xlsx')
        if not os.path.exists(excel_file):
            raise HTTPException(status_code=404, detail="OAT data file not found. Please update the dataset first.")
        
        logging.info("Loading OAT nature data from Excel file (JSON not available)")
        
        # Load all sheets from the Excel file
        excel_data = pd.read_excel(excel_file, sheet_name=None)
        
        # Load school names for selection
        school_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_schools.csv')
        school_data = pd.read_csv(school_file)
        schools = [{"urn": str(row['URN']), "name": row['Establishment name']} for _, row in school_data.iterrows()]
        
        # Process data for visualization
        result = {
            "schools": schools,
            "site_boundaries": clean_dataframe_for_json(excel_data.get('Site_Boundaries', pd.DataFrame())),
            "microhabitats": clean_dataframe_for_json(excel_data.get('Mapper_Microhabitats', pd.DataFrame())),
            "lines": clean_dataframe_for_json(excel_data.get('Mapper_Lines', pd.DataFrame())),
            "areas": clean_dataframe_for_json(excel_data.get('Mapper_Areas', pd.DataFrame()))
        }
        
        return JSONResponse(content=result)
    except Exception as e:
        logging.error(f"Error loading OAT nature data: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Error loading data: {str(e)}")

@app.post("/api/oat-nature/update-dataset")
async def update_oat_dataset():
    try:
        # Clear the log file before starting
        try:
            log_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.log')
            # Clear the file - this will be overwritten by the script's logging
            with open(log_file, 'w', encoding='utf-8') as f:
                f.write('Starting update process...\n')
                f.flush()
        except Exception as e:
            raise HTTPException(
                status_code=500,
                detail=f"Error accessing log file: {str(e)}"
            )
        
        # Start the script in a background thread
        try:
            thread = threading.Thread(target=run_script)
            thread.daemon = True  # Make thread daemon so it exits when main thread exits
            thread.start()
            
            return {"success": True, "message": "Update process started"}
        except Exception as e:
            raise HTTPException(
                status_code=500,
                detail=f"Error starting update process: {str(e)}"
            )
            
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Unexpected error: {str(e)}"
        )

@app.get("/api/oat-nature/schools")
async def get_schools():
    try:
        df = get_schools_df()
        schools = df.to_dict('records')
        return schools
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/oat-nature/schools/{urn}")
async def get_school(urn: int):
    try:
        df = get_schools_df()
        school = df[df['urn'] == urn].to_dict('records')
        if not school:
            raise HTTPException(status_code=404, detail="School not found")
        return school[0]
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.post("/api/oat-nature/schools")
async def add_school(school: School):
    try:
        df = get_schools_df()
        if school.urn in df['urn'].values:
            raise HTTPException(status_code=400, detail="School URN already exists")
        
        # Create new row with correct column structure
        new_row = pd.DataFrame([{
            'urn': school.urn,
            'schoolName': school.schoolName
        }])
        
        # Concatenate with existing data
        df = pd.concat([df, new_row], ignore_index=True)
        
        # Convert back to original column names for saving
        df = df.rename(columns={
            'urn': 'URN',
            'schoolName': 'Establishment name'
        })
        
        # Save to CSV
        school_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_schools.csv')
        df.to_csv(school_file, index=False)
        return school
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.put("/api/oat-nature/schools/{urn}")
async def update_school(urn: int, school: School):
    try:
        df = get_schools_df()
        if urn not in df['urn'].values:
            raise HTTPException(status_code=404, detail="School not found")
        
        # Update using the original column names
        df.loc[df['urn'] == urn, 'schoolName'] = school.schoolName
        # Convert back to original column names for saving
        df = df.rename(columns={
            'urn': 'URN',
            'schoolName': 'Establishment name'
        })
        school_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_schools.csv')
        df.to_csv(school_file, index=False)
        return school
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.delete("/api/oat-nature/schools/{urn}")
async def delete_school(urn: int):
    try:
        df = get_schools_df()
        if urn not in df['urn'].values:
            raise HTTPException(status_code=404, detail="School not found")
        
        df = df[df['urn'] != urn]
        # Convert back to original column names for saving
        df = df.rename(columns={
            'urn': 'URN',
            'schoolName': 'Establishment name'
        })
        school_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_schools.csv')
        df.to_csv(school_file, index=False)
        return {"message": "School deleted successfully"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/oat-nature-admin")
async def admin_page(request: Request):
    return templates.TemplateResponse("oat_nature_admin.html", {"request": {}})

@app.get("/api/oat-nature/download-excel")
async def download_excel():
    """Download the OAT ArcGIS Excel file"""
    try:
        excel_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.xlsx')
        if not os.path.exists(excel_file):
            raise HTTPException(status_code=404, detail="Excel file not found")
        
        return FileResponse(
            excel_file,
            media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            filename='OAT_ArcGIS_OAT_Only.xlsx'
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/oat-nature/download-sheet/{sheet_name}")
async def download_sheet(sheet_name: str, urns: str = None):
    """Download a specific sheet from the OAT ArcGIS Excel file as CSV"""
    try:
        excel_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.xlsx')
        if not os.path.exists(excel_file):
            raise HTTPException(status_code=404, detail="Excel file not found")
        
        # Map sheet names to actual Excel sheet names
        sheet_mapping = {
            'site_boundaries': 'Site_Boundaries',
            'microhabitats': 'Mapper_Microhabitats',
            'lines': 'Mapper_Lines',
            'areas': 'Mapper_Areas'
        }
        
        excel_sheet_name = sheet_mapping.get(sheet_name)
        if not excel_sheet_name:
            raise HTTPException(status_code=400, detail="Invalid sheet name")
        
        # Read the Excel file
        df = pd.read_excel(excel_file, sheet_name=excel_sheet_name)
        
        # Filter by URNs if provided
        if urns:
            urn_list = [int(urn.strip()) for urn in urns.split(',') if urn.strip().isdigit()]
            if urn_list:
                df = df[df['Urn'].isin(urn_list)]
        
        # Create CSV response
        from io import StringIO
        output = StringIO()
        df.to_csv(output, index=False)
        csv_content = output.getvalue()
        
        # Create filename
        filename = f"{sheet_name}_{datetime.now().strftime('%Y%m%d')}.csv"
        
        return Response(
            content=csv_content,
            media_type='text/csv',
            headers={'Content-Disposition': f'attachment; filename="{filename}"'}
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/oat-nature/last-update")
async def get_last_update():
    try:
        excel_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.xlsx')
        if not os.path.exists(excel_file):
            return {"lastUpdate": None}
        
        # Get the last modified time of the Excel file
        last_modified = os.path.getmtime(excel_file)
        last_update = datetime.fromtimestamp(last_modified).strftime('%d/%m/%Y %H:%M:%S')
        return {"lastUpdate": last_update}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/oat-nature/log")
async def get_oat_nature_log():
    """Get the contents of the OAT ArcGIS log file"""
    try:
        log_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_ArcGIS_OAT_Only.log')
        if not os.path.exists(log_file):
            return JSONResponse(content={"error": "Log file not found"}, status_code=404)
        
        with open(log_file, 'r') as f:
            log_content = f.read()
        return Response(content=log_content, media_type="text/plain")
    except Exception as e:
        logging.error(f"Error reading log file: {str(e)}")
        return JSONResponse(content={"error": str(e)}, status_code=500)

@app.get("/api/oat-nature/carbon-baseline")
async def get_carbon_baseline():
    try:
        # Define the carbon values directly in the code as a fallback
        default_values = {
            'Vegetable and fruit trees': 0.5,
            'Grass and wildflowers': 8.5,
            'Hedges and bushes': 0.1,
            'Wet places': 14.5,
            'Ground without plants': 5.2,
            'Trees': 30.5,
            'Flower Gardens': 0.47,
            'Unknown': 0
        }
        
        try:
            carbon_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_carbon_baseline.csv')
            carbon_values = {}
            with open(carbon_file, 'r') as file:
                reader = csv.DictReader(file)
                for row in reader:
                    habitat = row['CarbonArea'].strip()
                    value = float(row['CarbonValueKGCO2e'].strip())
                    carbon_values[habitat] = value
            
            if carbon_values:
                return JSONResponse(content=carbon_values)
            else:
                app.logger.warning("No values read from CSV, using default values")
                return JSONResponse(content=default_values)
                
        except Exception as e:
            app.logger.error(f"Error reading CSV, using default values: {str(e)}")
            return JSONResponse(content=default_values)
            
    except Exception as e:
        app.logger.error(f"Unexpected error: {str(e)}")
        return JSONResponse(
            status_code=500,
            content={"error": str(e)}
        )

@app.post("/api/oat-nature/carbon-baseline")
async def update_carbon_baseline(data: List[CarbonBaselineEntry]):
    """
    Update the OAT_carbon_baseline.csv file with new values.
    Expects a list of objects: {"CarbonArea": str, "CarbonValueKGCO2e": float}
    """
    try:
        # Write to CSV
        carbon_file = os.path.join(OAT_NATURE_DATA_DIR, 'OAT_carbon_baseline.csv')
        with open(carbon_file, 'w', newline='') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=['CarbonArea', 'CarbonValueKGCO2e'])
            writer.writeheader()
            for row in data:
                writer.writerow(row.dict())
        return {"success": True, "message": "Carbon baseline updated successfully."}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/oat-nature/download-formula")
async def download_formula():
    """Download the formula methodology document"""
    try:
        file_path = os.path.join(OAT_NATURE_DATA_DIR, "Formula_for_Carbon_Storage.docx")
        if not os.path.exists(file_path):
            raise HTTPException(status_code=404, detail="Formula document not found")
        
        return FileResponse(
            file_path,
            media_type='application/vnd.openxmlformats-officedocument.wordprocessingml.document',
            filename='Formula_for_Carbon_Storage.docx'
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))