from flask import Blueprint, request, jsonify
import logging
import pytz
import os
import json
import pandas as pd
from zoneinfo import ZoneInfo
from datetime import datetime, timedelta
from bigquery.bq import BigQuery, Table
from utility.media import Facebook
from utility.line import OA, Audience
from utility.function import Function
from utility.keyword import Keywords, BigQueryKeyword
from firebase.firebase import Firebase
from utility.authorization import CloudAuthorization as CAuth
from utility.authorization import TokenAuthorization
logging.basicConfig(level=logging.INFO)

keyword = Blueprint('keyword', __name__, url_prefix='/keyword')
fb = Firebase(host=os.environ.get("FIREBASE_HOST"))

@keyword.route('/available/channel', methods=['POST','GET', 'PUT','DELETE'])
def available_channel():
    
    local_key = request.headers.get('Authorization')
    bearer = local_key.replace("Bearer ", "")
    userAuth = TokenAuthorization(access_token=bearer)
    userData = userAuth.validate()

    if request.method == 'GET':
        param = request.args
        for req in ['property_id']:
            if req not in param:
                return {"status":"error",'message': f"{req} is require"}, 400
            
        property_id = param.get('property_id')
        facebook_page_id_list = fb.db.reference(f'property/{property_id}/channel/facebook').get() or {}
        line_channel_id_list = fb.db.reference(f'property/{property_id}/channel/line').get() or {}
        
        fb_result = []
        for page_id, page_info in facebook_page_id_list.items():
            name = (page_info or {}).get("name")
            if name:  # keep only those that have a name
                fb_result.append({"name": name, "id": page_id})
        
        line_result = []
        for page_id, page_info in line_channel_id_list.items():
            name = (page_info or {}).get("name")
            if name:  # keep only those that have a name
                line_result.append({"name": name, "id": page_id})
        
        channel_dict = {
            "facebook_page" : fb_result,
            "facebook_post" : fb_result,
            "facebook_comment" : fb_result,
            "line_chat" : line_result
        }
        
        return {"status":"ok",'message': 'Success', 'data': channel_dict}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405


@keyword.route('/template_list', methods=['POST','GET', 'PUT','DELETE'])
def template_list():
    
    local_key = request.headers.get('Authorization')
    bearer = local_key.replace("Bearer ", "")
    userAuth = TokenAuthorization(access_token=bearer)
    userData = userAuth.validate()

    if request.method == 'GET':
        templates = fb.db.reference('template/keyword/').get()
        category_map = Keywords.summarize_minimal(templates)
        
        return {"status":"ok",'message': 'Success', 'data': category_map}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405

   
@keyword.route('/template_details', methods=['GET'])
def template_details():
    
    local_key = request.headers.get('Authorization')
    bearer = local_key.replace("Bearer ", "")
    userAuth = TokenAuthorization(access_token=bearer)
    userData = userAuth.validate()
    
    if request.method == 'GET':
        param = request.args
        for req in ['property_id', 'template_name']:
            if req not in param:
                return {"status":"error",'message': f"{req} is require"}, 400
            
        template_name = param.get('template_name')
        
        if template_name == 'all':
            templates = fb.db.reference('template/keyword/').get()
            if not templates:
                return {"status":"error",'message': 'No templates found'}, 404
            return {"status":"ok",'message': 'Success', 'data': templates}, 200
        
        else:
            template = fb.db.reference(f'template/keyword/{template_name}').get()
            
            if not template:
                return {"status":"error",'message': 'Template not found'}, 404
        
        return {"status":"ok",'message': 'Success', 'data': template}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405


@keyword.route('/check_existing_keyword', methods=['POST'])
def check_existing_keyword():
    if request.method == 'POST':
        
        data = request.get_json()
        for req in ['property_id', 'category_name']:
            if req not in data:
                return {"status":"error",'message': f"{req} is require to check existing keyword category"}, 400
        
        category_name = data.get('category_name')
        property_id = data.get('property_id')
        
        check_exist = fb.db.reference(f'account/{property_id}/keyword/{category_name}').get()
        if check_exist:
            return {"status":"error","message": f"Keyword Category: {category_name} already exists for this property"}, 400
        else:
            return {"status": "ok",'message': f'Keyword Category: {category_name} does not exist for this property'}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405
        

@keyword.route('/list_all_keyword', methods=['GET'])
def list_all_keyword():
    if request.method == 'GET':
        param = request.args
        for req in ['property_id']:
            if req not in param:
                return {"status":"error",'message': f"{req} is require to read keyword category"}, 400
            
        property_id = request.args.get('property_id')
        existing_keyword_categories = fb.db.reference(f'account/{property_id}/keyword/').get()
        
        keywords_summary = []
        for key, data in existing_keyword_categories.items():
            if not isinstance(data, dict):
                continue

            name = data.get("name", key)
            createddate = data.get("createddate") or data.get("createdate")
            status = data.get("status", "inactive")

            keywords_summary.append({
                "name": name, 
                "createddate": createddate,
                "status": status,
            })
    
        if not existing_keyword_categories:
            return{"status": "error", "message": f"Keyword doesn't exist for this property"}, 400
        return {"status":"ok",'message': 'Success', 'data': keywords_summary}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405
    
    
@keyword.route('/keyword_category', methods=['GET','POST','PUT','DELETE'])
def create_keyword_category():
    
    local_key = request.headers.get('Authorization')
    bearer = local_key.replace("Bearer ", "")
    userAuth = TokenAuthorization(access_token=bearer)
    userData = userAuth.validate()
    
    if request.method == 'GET':
        param = request.args
        for req in ['property_id','category_name']:
            if req not in param:
                return {"status":"error",'message': f"{req} is require to read keyword category"}, 400
            
        property_id = request.args.get('property_id')
        category_name = request.args.get('category_name')
        existing_keyword_categories = fb.db.reference(f'account/{property_id}/keyword/{category_name}').get()
        
        if not existing_keyword_categories:
            return{"status": "error", "message": f"Keyword {category_name} doesn't exist for this property"}, 400
        return {"status":"ok",'message': 'Success', 'data': existing_keyword_categories}, 200
    
    elif request.method == 'POST':
        data = request.get_json()
        for req in ['property_id', 'category_name', 'structure', 'apply_to', 'status']:
            if req not in data:
                return {"status":"error",'message': f"{req} is require to create keyword category"}, 400
        
        category_name = data.get('category_name')
        property_id = data.get('property_id')
        structure = data.get('structure')
        apply_to = data.get('apply_to')
        status = data.get('status')
        
        check_exist = fb.db.reference(f'account/{property_id}/keyword/{category_name}').get()
        if check_exist:
            return {"status":"error","message": f"Keyword Category: {category_name} already exists for this property"}, 400
        
        fb.db.reference(f'account/{property_id}/keyword/{category_name}').set(structure)
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/name').set(category_name)
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/apply_to').set(apply_to)
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/status').set(status)
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/level').set("category_1")
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/createdate').set(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/lastupdate').set(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        
        return {"status":"ok",'message': f'Create Keyword Category {category_name} successfully!'}, 200

    elif request.method == 'PUT':
        data = request.get_json()
        for req in ['property_id', 'category_name', 'structure', 'apply_to','status']:
            if req not in data:
                return {"status":"error",'message': f"{req} is require to create keyword category"}, 400
            
        category_name = data.get('category_name')
        property_id = data.get('property_id')
        structure = data.get('structure')
        apply_to = data.get('apply_to')
        status = data.get('status')
        
        check_exist = fb.db.reference(f'account/{property_id}/keyword/{category_name}').get()
        if not check_exist:
            return{"status": "error", "message": "Keyword doesn't exist for this property"}, 400
        else:
            createdate = fb.db.reference(f'account/{property_id}/keyword/{category_name}/createdate').get()
            fb.db.reference(f'account/{property_id}/keyword/{category_name}').delete()
            
            fb.db.reference(f'account/{property_id}/keyword/{category_name}').set(structure)
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/name').set(category_name)
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/apply_to').set(apply_to)
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/status').set(status)
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/level').set("category_1")
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/createdate').set(createdate)
            fb.db.reference(f'account/{property_id}/keyword/{category_name}/lastupdate').set(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            
            return {"status":"ok",'message': f'Create Keyword Category {category_name} successfully!'}, 200
    
    elif request.method == 'DELETE':
        param = request.args
        
        for req in ['property_id','keyword_name']:
            if req not in param:
                return {"status":"error",'message': f"{req} is require"}, 400
        
        property_id = request.args.get('property_id')
        keyword_name = request.args.get('keyword_name')
        
        check_exist = fb.db.reference(f'account/{property_id}/keyword/{keyword_name}').get()
        if not check_exist:
            return{"status": "error", "message": "Keyword doesn't exist for this property"}, 400
        else:
            fb.db.reference(f'account/{property_id}/keyword/{keyword_name}').delete()
            return {"status":"ok",'message': f'Delete keyword category: {keyword_name} successfully!'}, 200
    return {"status":"error",'message': 'Method not allowed'}, 405


@keyword.route('/update/status', methods=['POST'])
def update_status():
    if request.method == 'POST':
        data = request.get_json()
        for req in ['property_id', 'category_name', 'status']:
            if req not in data:
                return {"status":"error",'message': f"{req} is require to update status"}, 400
        
        category_name = data.get('category_name')
        property_id = data.get('property_id')
        status = data.get('status')
        fb.db.reference(f'account/{property_id}/keyword/{category_name}/status').set(status)
        return {"status":"ok",'message': f'Update {category_name} status to {status} successfully!'}, 200
    
    return {"status":"error",'message': 'Method not allowed'}, 405
    
    
@keyword.route('/message_labeling', methods=['POST'])
def message_labeling():
    tz = ZoneInfo("Asia/Bangkok")
    now = datetime.now(tz)
    
    end_local   = now.replace(minute=0, second=0, microsecond=0)
    start_local = end_local - timedelta(hours=2)

    # if your BigQuery field is TIMESTAMP, convert to UTC strings
    fmt = "%Y-%m-%d %H:%M:%S"
    datetime_start = start_local.astimezone(tz).strftime(fmt)  # 'YYYY-MM-DD HH:MM:SS'
    datetime_end   = end_local.astimezone(tz).strftime(fmt)
    
    bq = BigQuery()
    property_list = list(fb.db.reference(f'property').get().keys())
    for property_id in property_list:
        
        message_schema = Table.tableMessageLabeling()
        bq.ensure_table(f'customer-360-profile.client_{property_id}.message_labeling',message_schema)
        bq.ensure_table(f'customer-360-profile.client_{property_id}.message_labeling_temp',message_schema)

        kw_tree = fb.db.reference(f'account/{property_id}/keyword').get() or {}
        if kw_tree:
            flat_list = Keywords.flatten_keyword_tree_to_list(
                kw_tree,
                excluded_fields=("apply_to","createdate","lastupdate","name","status")  # <- your requested exclusions
            )
            
            ## facebook comment
            fb_pageId_list = list(fb.db.reference(f'property/{property_id}/channel/facebook').get().keys())
            apply_to_channel = "facebook_comment"
            if fb_pageId_list:
                for fb_pageId in fb_pageId_list:
                    schema_dict = {list(d.keys())[0]: list(d.values())[0] for d in flat_list}
                    apply_to_map = Keywords.build_apply_to_map_if_contains_id(fb, property_id, required_id=fb_pageId, channel_key=apply_to_channel)
                    schema_dict = Keywords.filter_schema_by_apply_to(schema_dict, apply_to_map)

                    facebook_comment_query = BigQueryKeyword.facebook_comment_query(property_id,fb_pageId,datetime_start,datetime_end)
                    facebook_comment_df = bq.get_query_df(facebook_comment_query)
                    facebook_comment_df["labeling"] = facebook_comment_df["comment_text"].apply(lambda s: Keywords.label_to_bq(s, schema_dict))
                    facebook_comment_df = facebook_comment_df.drop(columns=['comment_type'])
                    facebook_comment_df = facebook_comment_df.rename(columns={
                        "facebook_name" : "pageName",
                        "ps_id" : "user_id",
                        "comment_text" : "message"
                    })
                    kw_list = list(fb.db.reference(f'account/{property_id}/keyword').get().keys())
                    priority_map = Keywords.load_group_priorities_deep(fb, property_id, groups=kw_list)
                    facebook_comment_df["single_grouping"] = facebook_comment_df["labeling"].apply(
                        lambda row: Keywords.choose_single_group_labels(
                            row,
                            priority_map=priority_map,
                            ascending_priority=True,          # change to False if higher numbers = higher priority
                            limit_to_groups=None,          # or ["purpose"] to emit only purpose
                            drop_excluded=True
                        )
                    )
                    fb_channel_name = fb.db.reference(f'property/{property_id}/channel/facebook/{fb_pageId}/name').get()
                    facebook_comment_df['pageName'] = fb_channel_name
                    bq.delete_data('customer-360-profile',f'client_{property_id}',"message_labeling_temp")
                    bq.load_data_df(f'client_{property_id}',"message_labeling_temp",facebook_comment_df)
                    bq.delete_when_match('customer-360-profile',f'client_{property_id}',"message_labeling",
                                        f'client_{property_id}','message_labeling_temp', 
                                        "ON ori.eventTimeStamp = temp.eventTimeStamp AND ori.user_id = temp.user_id AND ori.eventId = temp.eventId AND ori.eventName = temp.eventName ")
                    bq.load_data_df(f'client_{property_id}',"message_labeling",facebook_comment_df)
                    
            
            ## facebook chat
            facebook_channel_id_list = list(fb.db.reference(f'property/{property_id}/channel/facebook').get().keys())
            apply_to_channel = "facebook_message"
            if facebook_channel_id_list:
                for facebook_channel_id in facebook_channel_id_list:
                    schema_dict = {list(d.keys())[0]: list(d.values())[0] for d in flat_list}
                    apply_to_map = Keywords.build_apply_to_map_if_contains_id(fb, property_id, required_id=facebook_channel_id, channel_key=apply_to_channel)
                    schema_dict = Keywords.filter_schema_by_apply_to(schema_dict, apply_to_map)
                    
                    facebook_message_query = BigQueryKeyword.facebook_message_query(property_id,facebook_channel_id,datetime_start,datetime_end)
                    fb_chat_df = bq.get_query_df(facebook_message_query)
                    fb_chat_df["labeling"] = fb_chat_df["message"].apply(lambda s: Keywords.label_to_bq(s, schema_dict))
                    fb_channel_name = fb.db.reference(f'property/{property_id}/channel/facebook/{facebook_channel_id}/name').get()
                    fb_chat_df['pageName'] = fb_channel_name
                    fb_chat_df = fb_chat_df.rename(columns={
                        "line_uid" : "user_id"
                    })
                    kw_list = list(fb.db.reference(f'account/{property_id}/keyword').get().keys())
                    priority_map = Keywords.load_group_priorities_deep(fb, property_id, groups=kw_list)
                    fb_chat_df["single_grouping"] = fb_chat_df["labeling"].apply(
                        lambda row: Keywords.choose_single_group_labels(
                            row,
                            priority_map=priority_map,
                            ascending_priority=True,          # change to False if higher numbers = higher priority
                            limit_to_groups=None,          # or ["purpose"] to emit only purpose
                            drop_excluded=True
                        )
                    )
                    bq.delete_data('customer-360-profile',f'client_{property_id}',"message_labeling_temp")
                    bq.load_data_df(f'client_{property_id}',"message_labeling_temp",fb_chat_df)
                    bq.delete_when_match('customer-360-profile',f'client_{property_id}',"message_labeling",
                                        f'client_{property_id}','message_labeling_temp', 
                                        "ON ori.eventTimeStamp = temp.eventTimeStamp AND ori.user_id = temp.user_id AND ori.eventId = temp.eventId AND ori.eventName = temp.eventName ")
                    bq.load_data_df(f'client_{property_id}',"message_labeling",fb_chat_df)
                                
            ## facebook post
            ## --- future task when there will be post data in BigQuery ----
            
            ## line chat
            line_id_list = list(fb.db.reference(f'property/{property_id}/channel/line').get().keys())
            apply_to_channel = "line_chat"
            if line_id_list:
                for line_id in line_id_list:
                    schema_dict = {list(d.keys())[0]: list(d.values())[0] for d in flat_list}
                    apply_to_map = Keywords.build_apply_to_map_if_contains_id(fb, property_id, required_id=line_id, channel_key=apply_to_channel)
                    schema_dict = Keywords.filter_schema_by_apply_to(schema_dict, apply_to_map)

                    line_chat_query = BigQueryKeyword.line_chat_query(property_id,line_id,datetime_start,datetime_end)
                    line_chat_df = bq.get_query_df(line_chat_query)
                    line_chat_df["labeling"] = line_chat_df["message"].apply(lambda s: Keywords.label_to_bq(s, schema_dict))
                    line_chat_df['eventTimeStamp'] = pd.to_datetime(line_chat_df['eventTimeStamp'])
                    line_channel_name = fb.db.reference(f'property/{property_id}/channel/line/{line_id}/name').get()
                    line_chat_df['pageName'] = line_channel_name
                    line_chat_df = line_chat_df.rename(columns={
                        "line_uid" : "user_id"
                    })
                    
                    kw_list = list(fb.db.reference(f'account/{property_id}/keyword').get().keys())
                    priority_map = Keywords.load_group_priorities_deep(fb, property_id, groups=kw_list)
                    line_chat_df["single_grouping"] = line_chat_df["labeling"].apply(
                        lambda row: Keywords.choose_single_group_labels(
                            row,
                            priority_map=priority_map,
                            ascending_priority=True,          # change to False if higher numbers = higher priority
                            limit_to_groups=None,          # or ["purpose"] to emit only purpose
                            drop_excluded=True
                        )
                    )
                    
                    bq.delete_data('customer-360-profile',f'client_{property_id}',"message_labeling_temp")
                    bq.load_data_df(f'client_{property_id}',"message_labeling_temp",line_chat_df)
                    bq.delete_when_match('customer-360-profile',f'client_{property_id}',"message_labeling",
                                        f'client_{property_id}','message_labeling_temp', 
                                        "ON ori.eventTimeStamp = temp.eventTimeStamp AND ori.user_id = temp.user_id AND ori.eventId = temp.eventId AND ori.eventName = temp.eventName ")
                    bq.load_data_df(f'client_{property_id}',"message_labeling",line_chat_df)
        
    return {"status":"ok",'message': 'Success', 'data': 'label keyword successfully'}, 200