from fastapi import Request, Query, Depends, Header, APIRouter, HTTPException, UploadFile, File, Form
from fastapi.responses import JSONResponse
from utility.function import Function
from connectors.firebase.firebase import Firebase
from utility.authorization import TokenAuthorization
from connectors.cloudStorage.bucketstorage import Storage
import pytz
import os
import uuid
from datetime import datetime
from werkzeug.utils import secure_filename
from models.automation_v1 import *
from utility.jsonschemaValidate import *
from api.feature.automation.node import *
from utility.function import Function
from typing import List, Optional, Tuple

import logging
logging.basicConfig(level=logging.INFO)
timezone_utc = pytz.utc

fb = Firebase(host=os.environ.get("FIREBASE_HOST"))
MAGIC_REFERENCE_DATE = datetime(2020, 1, 6, 0, 0, 0).strftime('%Y-%m-%d %H:%M:%S')

router = APIRouter()

LOGGING_PREFIX = "api_feature_automation_v1"

# Pagination helper functions
def _apply_filters(items: List[dict], filters: List[str]) -> List[dict]:
    """Apply filters to items list"""
    if not filters or len(filters) == 0:
        return items
    
    search_filters = []
    for filter_str in filters:
        if ':' in filter_str:
            field, value = filter_str.split(':', 1)
            if field and value is not None:
                search_filters.append({"field": field, "value": value})
    
    if not search_filters:
        return items
    
    # Group filters by value (for OR search across different fields with same value)
    value_groups = {}
    for sf in search_filters:
        value = sf["value"]
        if value not in value_groups:
            value_groups[value] = []
        value_groups[value].append(sf["field"])
    
    # Apply filters
    filtered = []
    for item in items:
        matches_all_values = True
        for search_value, fields in value_groups.items():
            # Check if searchValue matches in ANY of the fields (OR across fields)
            value_matches = False
            for field in fields:
                # v1 uses "name" field (stored in Firebase as "name")
                # Also check automation_id for search
                field_value = None
                if field == 'name':
                    # v1 stores name at top level
                    field_value = item.get('name')
                elif field == 'automation_id':
                    field_value = item.get('automation_id')
                else:
                    field_value = item.get(field)
                
                if field_value is not None:
                    field_str = str(field_value).lower()
                    value_str = str(search_value).lower()
                    if value_str in field_str:  # Partial match (contains)
                        value_matches = True
                        break
            if not value_matches:
                matches_all_values = False
                break
        if matches_all_values:
            filtered.append(item)
    
    return filtered

def _apply_sort(items: List[dict], sort_by: Optional[str], order: Optional[str]) -> List[dict]:
    """Apply sorting to items list"""
    if not sort_by:
        return items
    
    order_desc = order == "desc"
    # Use sorted() to avoid modifying original list
    return sorted(items, key=lambda x: (x.get(sort_by) is None, x.get(sort_by)), reverse=order_desc)

def _apply_pagination_start_length(items: List[dict], start: int, length: int) -> Tuple[List[dict], dict]:
    """Apply pagination to items list using start/length"""
    total = len(items)
    paginated_items = items[start:start + length] if length > 0 else items[start:]
    
    pagination = {
        "start": start,
        "length": length if length > 0 else total,
        "total": total,
        "hasMore": start + length < total if length > 0 else False
    }
    
    return paginated_items, pagination

@router.post("", description="Create a new automation")
async def feature_automation_create(request: CreateAutomation, default_request: Request):
    try:
        now = datetime.now(timezone_utc).strftime('%Y-%m-%d %H:%M:%S')
        user_id = Function.getUserIDFromHeader(default_request.headers)
        jsonFormated = automationJson(request.body)
        
        #Validate json
        if jsonFormated['status'] != 'ok':
            return JSONResponse(status_code=400, content={'status': 'error', 'message': jsonFormated['message']})
        
        automation_id = str(uuid.uuid4())
        
        #Save to Firbase
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/display").set(True)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/json").set(request.body)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/active").set(False)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/name").set(request.name)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/createdate").set(now)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/lastupdate").set(now)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/last_triggered").set(MAGIC_REFERENCE_DATE)
        fb.db.reference().child(f"account/{request.property_id}/automation/{automation_id}/lastupdate_by").set(user_id)

        #Gen schedual list
        from connectors.cloudSQL.cloudsql import CloudSQL
        csql = CloudSQL("asia-southeast1", os.environ.get("INSTANCE_NAME"))
        csql.create_engine(os.environ.get("POSTGRES_USER"), os.environ.get("POSTGRES_PASSWORD"), os.environ.get("POSTGRES_DB"))
        
        last_triggered = MAGIC_REFERENCE_DATE
        startingNode = request.body.get('starting_node')
        automateNodes = request.body.get('nodes')
        
        for node in automateNodes:
            if node.get('id') == startingNode:
                trigNode = TriggerNode(node["id"], node["name"], node['trigger'], node.get("cron",None), node.get("interval",None), last_triggered, now, node['datebegin'], node['dateend'], node['alwaywhen'])
                listDateSchedual, setDatePartition = trigNode.generate_schedule()

                #Create partition 
                csql.create_partition("automation_trigger", setDatePartition)

                #Save data
                import pandas as pd

                df = pd.DataFrame(listDateSchedual, columns=['datetime_trigger'])
                df["datetime_trigger"] = (
                    pd.to_datetime(df["datetime_trigger"])
                    .dt.tz_localize("Asia/Bangkok", ambiguous="NaT", nonexistent="shift_forward")
                    .dt.tz_convert("UTC")
                )
                df['automation_id'] = automation_id
                df['automation_name'] = request.name
                df['status'] = 'pending'
                df['property_id'] = request.property_id
                df['active_status'] = False
                df['log_id'] = [uuid.uuid4() for _ in range(len(df))]
                csql.create_engine(os.environ.get("POSTGRES_USER"), os.environ.get("POSTGRES_PASSWORD"), os.environ.get("POSTGRES_DB"))
                csql.insert_dataframe(df, "automation_trigger")

        #Find content node
        try:
            for node in automateNodes:
                if 'type' in node and node['type'] == 'destination':
                    if 'content' in node:
                        channel_type = node['channel_type']
                        content_id = node['content']['content_id']

                        #Get list content
                        contentAutomations = fb.db.reference().child(f"account/{request.property_id}/content/{channel_type}/{content_id}/automations").get()
                        contentAutomations = [] if not contentAutomations else contentAutomations
                        contentAutomations.append(automation_id)
                        contentAutomationsNew = list(set(contentAutomations))
                        fb.db.reference().child(f"account/{request.property_id}/content/{channel_type}/{content_id}/automations").set(contentAutomationsNew)
        except Exception as e:
            logging.error(e)
        
        return JSONResponse(status_code=200, content={'status': 'ok', 'automation_id': automation_id})

    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.put("", description="Update an existing automation")
async def feature_automation_update(request: CreateAutomation, default_request: Request = None):
    try:
        now = datetime.now(timezone_utc).strftime('%Y-%m-%d %H:%M:%S')
        user_id = Function.getUserIDFromHeader(default_request.headers)
        jsonFormated = automationJson(request.body)
        
        #Validate json
        if jsonFormated['status'] != 'ok':
            return JSONResponse(status_code=400, content={'status': 'error', 'message': jsonFormated['message']})
        
        # Check automation_id exist
        automationContext = fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}").get(shallow=True)
        if not automationContext:
            return JSONResponse(status_code=400, content={'status': 'not found', 'message': f"Automation ID: {request.automation_id} not fond"})

        fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/lastupdate").set(now)
        fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/json").set(request.body)
        fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/lastupdate_by").set(user_id)
        
        if request.name:
            fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/name").set(request.name)
        
        return JSONResponse(status_code=200, content={'status': 'ok', 'message': f'Automation {request.automation_id} was updated successfully'})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.get("", response_model=DataResponse, description="Get automation details")
async def feature_automation_get(params: GetAutomation = Depends()):
    try:
        if params.display_type == 'list':
            if params.automation_id != 'all':
                automation_context = fb.db.reference().child(f"account/{params.property_id}/automation/{params.automation_id}").get()
                ordered_automation_desc = [automation_context]
            else:
                automation_context = fb.db.reference().child(f"account/{params.property_id}/automation").get()
                if automation_context:
                    returnAutomation = []
                    for a in automation_context:
                        au = automation_context[a]
                        if au['display']:
                            au.update({'automation_id': a})
                            returnAutomation.append(au)
                    
                    ordered_automation_desc = sorted(returnAutomation, key=lambda x: x.get('createdate', ''), reverse=True)
                    
                else:
                    return JSONResponse(status_code=400, content={'status': 'not found', 'message': f'Property {params.property_id} has no automation flow', 'data': []})
            
            # Apply filters
            if params.filter and len(params.filter) > 0:
                ordered_automation_desc = _apply_filters(ordered_automation_desc, params.filter)
            
            # Apply sorting
            if params.sort_by:
                ordered_automation_desc = _apply_sort(ordered_automation_desc, params.sort_by, params.order)
            elif params.automation_id == 'all':
                # Default sort by createdate desc if no sort_by specified
                ordered_automation_desc = sorted(ordered_automation_desc, key=lambda x: x.get('createdate', ''), reverse=True)
            
            # Apply pagination
            if params.automation_id == 'all':
                # Determine pagination parameters
                if params.start is not None and params.length is not None:
                    start = max(0, params.start)  # Ensure non-negative
                    length = max(1, params.length)  # Ensure at least 1
                elif params.page is not None and params.page_size is not None:
                    start = max(0, (params.page - 1) * params.page_size)
                    length = max(1, params.page_size)
                else:
                    start = 0
                    length = len(ordered_automation_desc) if len(ordered_automation_desc) > 0 else 10
                
                paginated_items, pagination = _apply_pagination_start_length(ordered_automation_desc, start, length)
                
                return JSONResponse(status_code=200, content={
                    'status': 'ok', 
                    'data': {
                        'items': paginated_items,
                        'pagination': pagination
                    },
                    'message': 'Automation flow retrieved successfully'
                })
            else:
                return JSONResponse(status_code=200, content={'status': 'ok', 'data': ordered_automation_desc, 'message': 'Automation flow retrieved successfully'})
        
        elif params.display_type == 'calendar':
            if not params.date_start and not params.date_end:
                return JSONResponse(status_code=400, content={'status': 'not found', 'message': f'Display type calendar must provide date_start and date_end'})
            
            archiveList = fb.db.reference().child(f"account/{params.property_id}/automation_archive").get()
            archiveList = [] if not archiveList else archiveList
            
            from connectors.cloudSQL.cloudsql import CloudSQL
            from utility.dashboard.genQuery import GenerateQuery
            import pandas as pd
            genq = GenerateQuery(property_id=params.property_id)
            csql = CloudSQL("asia-southeast1", os.environ.get("INSTANCE_NAME"))
            csql.create_engine(os.environ.get("POSTGRES_USER"), os.environ.get("POSTGRES_PASSWORD"), os.environ.get("POSTGRES_DB"))
            query = genq.getAutomationCalendar(params.date_start, params.date_end, params.status)
            result = csql.query(query)
            df_calendar = pd.DataFrame(result, columns=['datetime_trigger', 'automation_id', 'automation_name', 'status', 'active_status'])
            if df_calendar.empty:
                return JSONResponse(status_code=200, content={'status': 'ok', 'data': [], 'message': 'No automation trigger found in the given date range'})
            
            df_calendar['datetime_trigger'] = pd.to_datetime(df_calendar['datetime_trigger']).dt.strftime('%Y-%m-%d %H:%M:%S')
            if archiveList:
                df_calendar = df_calendar[~df_calendar['automation_id'].isin(archiveList)]
            df_calendar.sort_values(by='datetime_trigger', inplace=True)
            result_json = df_calendar.to_dict(orient='records')
            return JSONResponse(status_code=200, content={'status': 'ok', 'data': result_json, 'message': 'Calendar automation triggers retrieved successfully'})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.delete("", description="Delete (archive) an automation")
async def feature_automation_delete(request: GetAutomation, default_request: Request):
    try:
        #Get archive
        archiveList = fb.db.reference().child(f"account/{request.property_id}/automation_archive").get()
        archiveList = [] if not archiveList else archiveList

        archiveList.append(request.automation_id)
        setArchive = list(set(archiveList))
        fb.db.reference().child(f"account/{request.property_id}/automation_archive").set(setArchive)
        fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/display").set(False)

        listActiveAutomation = fb.db.reference().child(f"account/{request.property_id}/automation_active_index").get()
        try:
            listActiveAutomation.remove(request.automation_id)
            fb.db.reference().child(f"account/{request.property_id}/automation_active_index").set(listActiveAutomation)
        except ValueError as e:
            logging.info(f"Automation ID {request.automation_id} not in active list, no need to remove.")
        
        return JSONResponse(status_code=200, content={'status': 'ok', 'message': f'Automation {request.automation_id} was archived successfully'})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.post("/execute", response_model=DataResponse, description="Execute an automation immediately")
async def feature_automation_execute(request: ExecuteAutomation, default_request: Request):
    try:
        automation = fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}").get()
        if not automation:
            return JSONResponse(status_code=400, content={'status': 'not found', 'message': f"Automation ID: {request.automation_id} not fond"})
        executor = FlowExecutor(request.property_id, automation)
        result = executor.execute()
        
        return JSONResponse(status_code=200, content={'status': 'ok', 'data': result, 'message': f'Automation {request.automation_id} executed successfully'})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.post("/status", description="Toggle automation status active/inactive")
async def feature_automation_status_toggle(request: AutomationStatusUpdate, default_request: Request):
    try:
        #Check audience exist
        automationExist = fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}").get(shallow=True)
        if not automationExist:
            return {'status': 'not fond', 'message': f'Automation Id {request.automation_id} not fond'},400

        fb.db.reference().child(f"account/{request.property_id}/automation/{request.automation_id}/active").set(request.status)
        
        if request.status == True:
            #Set path automation_active_index
            listActiveAutomation = fb.db.reference().child(f"account/{request.property_id}/automation_active_index").get()
            if not listActiveAutomation:
                listActiveAutomation = []
        
            listActiveAutomation.append(request.automation_id)
            fb.db.reference().child(f"account/{request.property_id}/automation_active_index").set(list(set(listActiveAutomation)))
        else:
            #Remove path automation_active_index
            listActiveAutomation = fb.db.reference().child(f"account/{request.property_id}/automation_active_index").get()
            if listActiveAutomation:
                if request.automation_id in listActiveAutomation:
                    listActiveAutomation.remove(request.automation_id)
                    fb.db.reference().child(f"account/{request.property_id}/automation_active_index").set(list(set(listActiveAutomation)))
                    
        #Update to Postgres
        from utility.dashboard.genQuery import GenerateQuery
        from connectors.cloudSQL.cloudsql import CloudSQL
        csql = CloudSQL("asia-southeast1", os.environ.get("INSTANCE_NAME"))
        csql.create_engine(os.environ.get("POSTGRES_USER"), os.environ.get("POSTGRES_PASSWORD"), os.environ.get("POSTGRES_DB"))
        genq = GenerateQuery(request.property_id)
        query = genq.updateAutomationStatus(request.automation_id, request.status)
        result = csql.query(query)
        return JSONResponse(status_code=200, content={'status': 'ok', 'message': f'Change status automation {request.automation_id} has been change status to {"Active" if request.status == True else "Inactive" }'})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.get("/calculate", response_model=DataResponse, description="Calculate audience data for automation")
async def feature_automation_calculate_schedule(params: GetAudienceForAutomation = Depends()):
    try:
        context = fb.db.reference().child(f"account/{params.property_id}/audience/{params.audience_id}/cache/profile").get()
        channelContext = fb.db.reference().child(f"property/{params.property_id}/channel").get()
        if context:
            import pandas as pd
            df = pd.DataFrame(context)
            df = df.loc[df['channel_type'].isin(["facebook", "line"])]
            channelContextTransform = []
            
            for i in channelContext:
                if i != 'ga4':
                    for id in channelContext[i]:
                        name = channelContext[i][id]['name']
                        channelContextTransform.append(
                            {
                                "id": id,
                                "name": name
                            }
                        )
            df_channel = pd.DataFrame(channelContextTransform)
            df_channel.rename(columns={'id': 'page_id'}, inplace=True)
            df_merge =  df.merge(df_channel, on='page_id')
            final_result = df_merge.pivot_table(index=['channel_type', 'name', 'page_id'], values='id', aggfunc=pd.Series.nunique).reset_index().rename(columns={'id': 'count'})
            result_json = final_result.to_dict(orient='records')

            return JSONResponse(status_code=200, content={'status': "ok", 'data': result_json, 'message': f"Audience {params.audience_id} cache data retrieved successfully"})
        else:
            return JSONResponse(status_code=400, content={'status': 'not_found', 'message': f"Audience {params.audience_id} have no cache data", 'data': []})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})

@router.get("/stat", response_model=DataResponse, description="Get automation execution statistics")
async def feature_automation_stat(params: BaseAutomation = Depends()):
    try:
        logs = fb.db.reference().child(f"account/{params.property_id}/automation/{params.automation_id}/logs").get()
        if not logs:
            return JSONResponse(status_code=404, content={'status': 'not_found', 'message': f"Automation ID {params.automation_id} not have any log", 'data': []})
        return JSONResponse(status_code=200, content={'status': 'ok', 'data': logs, 'message': f"Automation ID {params.automation_id} logs retrieved successfully"})
    except Exception as e:
        logging.error(f"Error parsing {LOGGING_PREFIX}: {e}")
        return JSONResponse(status_code=500, content={'status': 'error', 'message': str(e)})