from flask import Blueprint, request
import requests
import logging
from datetime import datetime
from firebase.firebase import Firebase
import pytz 
import utility.function as func
import os
import uuid
from bigquery.bq import BigQuery
from feature.audience.craft import BQCraft, Query
from utility.jsonschemaValidate import *
from feature.automation.node import *
logging.basicConfig(level=logging.INFO)

evntFunction = func.Event()
feature_automation = Blueprint('feature_automation', __name__, url_prefix='/feature/automation')

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

@feature_automation.route('/',  methods=['GET', 'POST', 'PUT', 'DELETE'])
@feature_automation.route('',  methods=['GET', 'POST', 'PUT', 'DELETE'])
def feature_automation_func():
    try:
        if request.method == 'POST':
            data = request.get_json()
            for req in ['property_id', 'name', 'json']:
                if req not in data:
                    return {'message': f"{req} is require"}, 400
            
            now = datetime.now(timezone)
            time = now.strftime('%Y-%m-%d %H:%M:%S')
            property_id = data.get('property_id')
            json = data.get('json')
            name = data.get('name')
            jsonFormated = automationJson(json)

            #Validate json
            if jsonFormated['status'] != 'ok':
                return jsonFormated, 500
            
            automation_id = str(uuid.uuid4())

            #Save to Firbase
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/display").set(True)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/json").set(json)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/active").set(False)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/name").set(name)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/createdate").set(time)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/lastupdate").set(time)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/last_triggered").set(MAGIC_REFERENCE_DATE)

            #Gen schedual list
            from utility.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 = json.get('starting_node')
            automateNodes = json.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'] = name
                    df['status'] = 'pending'
                    df['property_id'] = 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/{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/{property_id}/content/{channel_type}/{content_id}/automations").set(contentAutomationsNew)
            except Exception as e:
                logging.error(e)

            return {'status': 'ok', 'automation_id': automation_id}, 200

        elif request.method == 'GET':
            
            data = request.args
            for req in ['property_id', 'automation_id']:
                if req not in data:
                    return {"status":"error",'message': f"{req} is require"}, 400
            
            property_id = data.get('property_id')
            automation_id = data.get('automation_id')
            display_type = data.get('display_type', 'list')
            if display_type == 'list':
                if automation_id != 'all':
                    automation_context = fb.db.reference().child(f"account/{property_id}/automation/{automation_id}").get()
                    ordered_automation_desc = [automation_context]
                else:
                    automation_context = fb.db.reference().child(f"account/{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['createdate'], reverse=True)
                        
                    else:
                        return {'status': 'not fond', 'message': f'Propery {property_id} has no automation flow'}, 400
                
                return {'status': 'ok', 'data': ordered_automation_desc}, 200
            
            elif display_type == 'calendar':
                date_start = data.get('date_start', None)
                date_end = data.get('date_end', None)
                status = data.get('status', 'all')
                if not date_start and not date_end:
                    return {'status': 'not fond', 'message': f'Disply type calendar must provide date_start and date_end'}, 400
                
                archiveList = fb.db.reference().child(f"account/{property_id}/automation_archive").get()
                archiveList = [] if not archiveList else archiveList
                
                from utility.cloudsql import CloudSQL
                from dashboard.genQuery import GenerateQuery
                import pandas as pd
                genq = GenerateQuery(property_id=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(date_start, date_end, 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 {'status': 'ok', 'data': []}, 200
                
                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 {'status': 'ok', 'data': result_json}, 200

        elif request.method == 'PUT':
            data = request.get_json()
            for req in ['property_id', 'automation_id', 'json']:
                if req not in data:
                    return {"status":"error",'message': f"{req} is require"}, 400
            
            property_id = data.get('property_id')
            automation_id = data.get('automation_id')
            json = data.get('json')
            name = data.get('name', None)

            jsonFormated = automationJson(json)

            #Validate json
            if jsonFormated['status'] != 'ok':
                return jsonFormated, 500

            # Check automation_id exist
            automationContext = fb.db.reference().child(f"account/{property_id}/automation/{automation_id}").get(shallow=True)
            if not automationContext:
                return {'status': 'not found', 'message': f"Automation ID: {automation_id} not fond"}, 400
            
            now = datetime.now(timezone)
            time = now.strftime('%Y-%m-%d %H:%M:%S')
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/lastupdate").set(time)
            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/json").set(json)
            
            if name:
                fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/name").set(name)

            return {'status': 'ok', 'message': f"Autoantion ID {automation_id} was updated"}, 200

        elif request.method == 'DELETE':
            data = request.get_json()
            for req in ['property_id', 'automation_id']:
                if req not in data:
                    return {'message': f"{req} is require"}, 400
            
            property_id = data.get('property_id')
            automation_id = data.get('automation_id')

            #Get archive
            archiveList = fb.db.reference().child(f"account/{property_id}/automation_archive").get()
            archiveList = [] if not archiveList else archiveList

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

            # fb.db.reference().child(f"account/{property_id}/automation/{automation_id}").delete()
            
            listActiveAutomation = fb.db.reference().child(f"account/{property_id}/automation_active_index").get()
            
            try:
                listActiveAutomation.remove(automation_id)
                fb.db.reference().child(f"account/{property_id}/automation_active_index").set(listActiveAutomation)
            except ValueError as e:
                logging.info(f"Automation ID {automation_id} not in active list, no need to remove.")
                
            return {'status': 'ok', 'message': f"Autoantion ID {automation_id} was deleted"}, 200
        
        return {'status': 'error', 'message': 'Method not allowed'}, 405
    except Exception as e:
        logging.error(e)
        return {"status": "error", "message": e}, 500

@feature_automation.route('/execute',  methods=['POST'])
@feature_automation.route('/execute/',  methods=['POST'])
def feature_automation_execute():
    try:
        if request.method == 'POST':
            data = request.get_json()
            for req in ['property_id', 'automation_id']:
                if req not in data:
                    return {'message': f"{req} is require"}, 400
            
            #Get automation flow
            property_id = data.get('property_id')
            automation_id = data.get('automation_id')

            automation = fb.db.reference().child(f"account/{property_id}/automation/{automation_id}").get()
            if not automation:
                return {'status': 'not found', 'message': f"Automation ID: {automation_id} not fond"}, 400
            
            # automationJson = automation['json']

            executor = FlowExecutor(property_id, automation)
            result = executor.execute()

            return {'status': 'ok', 'data': result}, 200
            
        
        return {'status': 'error', 'message': 'Method not allowed'}, 405

    except Exception as e:
        logging.error(e)
        return {"status": "error", "message": e}, 500
    
@feature_automation.route('/status',  methods=['POST'])
def feature_automation_status():
    try:
        if request.method == 'POST':
            data = request.get_json()
            for req in ['property_id', 'user_id', 'automation_id', 'status']:
                if req not in data:
                    return {'message': f"{req} is require"}, 400

            property_id = data.get("property_id", None)
            user_id = data.get("user_id", "No user")
            automation_id = data.get("automation_id", None)
            status = data.get("status", None)
            try:
                status = bool(status)
            except ValueError:
                return {'message': f'''status value must be boolean type'''}, 400
            
            #Check audience exist
            automationExist = fb.db.reference().child(f"account/{property_id}/automation/{automation_id}").get(shallow=True)
            if not automationExist:
                return {'status': 'not fond', 'message': f'Automation Id {automation_id} not fond'},400

            fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/active").set(status)
            
            if status == True:
                #Set path automation_active_index
                listActiveAutomation = fb.db.reference().child(f"account/{property_id}/automation_active_index").get()
                if not listActiveAutomation:
                    listActiveAutomation = []
            
                listActiveAutomation.append(automation_id)
                fb.db.reference().child(f"account/{property_id}/automation_active_index").set(list(set(listActiveAutomation)))
            else:
                #Remove path automation_active_index
                listActiveAutomation = fb.db.reference().child(f"account/{property_id}/automation_active_index").get()
                if listActiveAutomation:
                    if automation_id in listActiveAutomation:
                        listActiveAutomation.remove(automation_id)
                        fb.db.reference().child(f"account/{property_id}/automation_active_index").set(list(set(listActiveAutomation)))
            
            #Update to Postgres
            from dashboard.genQuery import GenerateQuery
            from utility.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(property_id)
            query = genq.updateAutomationStatus(automation_id, status)
            result = csql.query(query)
            

            return {'status': 'ok', 'message': f'Change status automation {automation_id} has been change status to {"Active" if status == True else "Inactive" }'},200

        return {'status': 'error', 'message': 'Method not allowed'}, 405
    except Exception as e:
        logging.error(e)
        return {"status": "error", "message": e}, 500
    
@feature_automation.route('/calculate',  methods=['GET'])
@feature_automation.route('/calculate/',  methods=['GET'])
def feature_automation_cal_size_func():
    try:
        if request.method == 'GET':
            data = request.args
            for req in ['property_id', 'audience_id']:
                if req not in data:
                    return {"status":"error",'message': f"{req} is require"}, 400
            
            property_id = data.get("property_id")
            audience_id = data.get("audience_id")

            if not property_id:
                return {'status': 'error', 'message': 'Please define property_id'}
            if not audience_id:
                return {'status': 'error', 'message': 'Please define audience_id'}
            
            context = fb.db.reference().child(f"account/{property_id}/audience/{audience_id}/cache/profile").get()
            channelContext = fb.db.reference().child(f"property/{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 {'status': "ok", 'data': result_json}, 200

            else:
                return {'status': 'not_found', 'message': f"Audinec {audience_id} have no cache data"},400

        return {'status': 'error', 'message': 'Method not allowed'}, 405
    except Exception as e:
        logging.error(e)
        return {"status": "error", "message": e}, 500
    
@feature_automation.route('/stats',  methods=['GET'])
@feature_automation.route('/stats/',  methods=['GET'])
def feature_automation_stats():
    try:
        if request.method == 'GET':
            data = request.args
            for req in ['property_id', 'automation_id']:
                if req not in data:
                    return {"status":"error",'message': f"{req} is require"}, 400
            
            property_id = data.get("property_id")
            automation_id = data.get("automation_id")

            logs = fb.db.reference().child(f"account/{property_id}/automation/{automation_id}/logs").get()
            if not logs:
                return {'status': 'not_found', 'message': f"Automation ID {automation_id} not have any log"}, 404

            return {'status': 'ok', 'data': logs}, 200
        return {'status': 'error', 'message': 'Method not allowed'}, 405
    except Exception as e:
        logging.error(e)
        return {"status": "error", "message": e}, 500