import datetime, time
from fastapi import APIRouter, Body, Depends, Header, HTTPException, Path, Query, Response, status
from pydantic import BaseModel, EmailStr, Field
from typing import Dict, List, Optional, Set, Union

from app.lib_general import log, logging, common_route_params, Common_Route_Params
from app.config import settings
from app.db_sql import sql_enable_part, sql_insert, sql_update, sql_insert_or_update, sql_limit_offset_part, sql_select, sql_delete, redis_lookup_id_random

from app.routers.api_crud import delete_obj_template, get_obj_template, get_obj_li_template, patch_obj_template, post_obj_template

from app.methods.event_badge_methods import get_event_badge_rec_list, load_event_badge_obj
from app.methods.event_badge_methods import email_event_badge_review_url, load_event_badge_obj

from app.models.event_badge_models import Event_Badge_Base
from app.models.response_models import Resp_Body_Base, mk_resp


router = APIRouter()


@router.post('/event/badge', response_model=Resp_Body_Base)
async def post_event_badge_obj(
        obj: Event_Badge_Base,
        x_account_id: str = Header(...),
        return_obj: Optional[bool] = True,
        by_alias: Optional[bool] = True,
        exclude_unset: Optional[bool] = True,
        response: Response = Response,
        ):
    log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    obj_type = 'event_badge'
    obj_data_dict = obj.dict(by_alias=False, exclude_unset=True)
    result = post_obj_template(
        obj_type = obj_type,
        data = obj_data_dict,
        id_random_length = 0, # Added 2023-04-13; need to let SQL take care of this
        return_obj = True,
        by_alias = True,
        exclude_unset = True,
        )
    return result


@router.patch('/event/badge/{obj_id}', response_model=Resp_Body_Base)
async def patch_event_badge_obj(
        obj: Event_Badge_Base,
        obj_id: str = Path(min_length=11, max_length=22),
        x_account_id: Optional[str] = Header(..., ),
        return_obj: Optional[bool] = True,
        by_alias: Optional[bool] = True,
        exclude_unset: Optional[bool] = True,
        response: Response = Response,
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    # time.sleep(2)

    obj_type = 'event_badge'
    obj_data_dict = obj.dict(by_alias=False, exclude_unset=True)
    obj_data_dict['id'] = redis_lookup_id_random(record_id_random=obj_id, table_name=obj_type)
    obj_data_dict['id_random'] = obj_id
    result = patch_obj_template(
        obj_type = obj_type,
        data = obj_data_dict,
        obj_id = obj_id,
        return_obj = True,
        by_alias = True,
        exclude_unset = True,
        )
    return result


@router.get('/event/badge/list', response_model=Resp_Body_Base)
async def get_event_badge_obj_li(
        for_obj_type: str = Query(None, min_length=2, max_length=50),
        for_obj_id: str = Query(None, min_length=1, max_length=22),
        group: str = Query(None, min_length=2, max_length=50),
        x_account_id: str = Header(...),
        by_alias: bool = True,
        exclude_unset: bool = True,
        response: Response = Response,
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    obj_type = 'event_badge'

    if for_obj_type == 'event_exhibit' and for_obj_id:
        #base_name = obj_type_li[obj_type]['base_name']
        base_name = Event_Badge_Base

        for_obj_id_random = for_obj_id
        for_obj_id = redis_lookup_id_random(record_id_random=for_obj_id_random, table_name=for_obj_type)

        data = {}
        data['for_obj_type'] = for_obj_type
        data['for_obj_id'] = for_obj_id
        data['for_obj_id_random'] = for_obj_id_random
        data['group'] = group

        if group:
            sql = """
                SELECT *
                FROM `event_badge` AS event_badge
                WHERE event_badge.for_type = :for_obj_type AND event_badge.for_id = :for_obj_id
                    AND event_badge.group = :group
                """
        else:
            sql = """
                SELECT *
                FROM `event_badge` AS event_badge
                WHERE event_badge.for_type = :for_obj_type AND event_badge.for_id = :for_obj_id
                """

        if sql_result := sql_select(data=data, sql=sql, as_list=True):
            resp_data_li = []
            for record in sql_result:
                resp_data = base_name(**record).dict(by_alias=by_alias, exclude_unset=exclude_unset)
                resp_data_li.append(resp_data)

            return mk_resp(data=resp_data_li, response=response)
        else:
            log.debug(sql_result)
            return mk_resp(data=None, status_code=404, response=response)

    result = get_obj_li_template(
        obj_type=obj_type,
        for_obj_type=for_obj_type,
        for_obj_id=for_obj_id,
        by_alias=True,
        exclude_unset=True,
        )
    return result


# ### BEGIN ### API Event Badge ### search_event_badge_obj_li() ###
# Updated 2022-02-15
@router.get('/event/{event_id}/badge/search', response_model=Resp_Body_Base)
async def search_event_badge_obj_li(
        event_id: str = Path(min_length=11, max_length=22),
        event_badge_id: str = Query(None, min_length=3, max_length=22),
        event_person_id: str = Query(None, min_length=3, max_length=22),

        external_id: str = Query(None, max_length=100), # NOTE: Not currently used! 2022-02-15

        badge_type_code: str = Query(None, min_length=2, max_length=25),

        query_str: str = Query('', max_length=50),

        given_name: str = Query('', max_length=25),
        family_name: str = Query('', max_length=25),
        email: str = Query('', max_length=50),

        badge_only: bool = False, # NOTE: Use the v_event_badge_only view

        hidden: str = 'not_hidden', # hidden, not_hidden, all

        commons: Common_Route_Params = Depends(common_route_params),
        ):
    log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    # account_id = commons.x_account_id

    if event_id := redis_lookup_id_random(record_id_random=event_id, table_name='event'): pass
    else: return mk_resp(data=None, status_code=404, response=commons.response)

    if event_badge_id or event_person_id or external_id:
        log.info(f'Searching: Event ID: {event_id}; Event Badge ID: {event_badge_id}; Event Person ID: {event_person_id}; External ID: {external_id}')
    elif query_str:
        log.info(f'Searching: Event ID: {event_id}; Query String: {query_str}')
    else:
        log.info(f'Searching: Event ID: {event_id}; Event Badge ID: {event_badge_id}; Event Person ID: {event_person_id}; External ID: {external_id}; Give Name: {given_name}; Family Name: {family_name}; Email: {email}')

    data = {}
    # data['account_id'] = account_id
    data['event_id'] = event_id

    # Adding the % symbol here because it turns certain combinations into special characters
    if event_badge_id and len(event_badge_id) > 2:
        log.info(f'Using Event Badge ID: {event_badge_id}')
        data['event_badge_id_random'] = f'%{event_badge_id}%'
    elif event_person_id and len(event_person_id) > 2:
        log.info(f'Using Event Person ID: {event_person_id}')
        data['event_person_id_random'] = f'%{event_person_id}%'
    elif external_id and len(external_id) > 2:
        log.info(f'Using External ID: {external_id}')
        data['external_id'] = f'%{external_id}%'
    elif query_str and len(query_str) > 1:
        log.info(f'Using Query String: {query_str}')

        # Strip (left right) whitespace then commas then semicolons
        query_str = query_str.strip().strip(',').strip(';')
         # Replace commas, semicolons, and then spaces with %
        query_str_like = query_str.replace(',', ' ').replace(';', ' ').replace('  ', '%').replace(' ', '%')
        # data['query_str'] = f'%{query_str}%'
        log.debug(query_str_like)
        data['query_str'] = f'%{query_str_like}%'

        query_li = query_str.split()
        data['query_li'] = query_li
    elif len(given_name) > 1 or len(family_name) > 1 or len(email) > 1:
        log.info(f'Using other data fields')
        if given_name: data['given_name'] = '%'+given_name+'%'
        else: data['given_name'] = '%'
        if family_name: data['family_name'] = '%'+family_name+'%'
        else: data['family_name'] = '%'
        if email: data['email'] = '%'+email+'%'
        else: data['email'] = '%'
    else:
        return mk_resp(data=None, status_code=404, status_message=f'Need more characters for query.', response=commons.response)

    if badge_type_code:
        data['badge_type_code'] = [badge_type_code]
        sql_badge_type_code = 'AND event_badge.badge_type_code IN :badge_type_code'
    else:
        # sql_badge_type_code = 'AND event_badge.badge_type_code IS NOT NULL'
        sql_badge_type_code = 'AND 1=1'

    log.debug(data)

    if hidden in ['hidden', 'not_hidden', 'all']:
        if hidden == 'hidden':
            data['hide'] = True
            sql_hidden = f'AND `event_badge`.hide = :hide'
        elif hidden == 'not_hidden':
            data['hide'] = False
            sql_hidden = f'AND `event_badge`.hide = :hide'
        elif hidden == 'all':
            sql_hidden = ''

    sql_enabled, data['enable'] = sql_enable_part(table_name='event_badge', enabled=commons.enabled) # Reasonably safe return str and bool
    sql_limit = sql_limit_offset_part(limit=commons.limit, offset=commons.offset) # Reasonably safe return str

    if event_badge_id and len(event_badge_id) > 2:
        sql = f"""
            SELECT *
            FROM `v_event_badge` AS event_badge
            WHERE event_badge.event_id = :event_id
                AND event_badge.event_badge_id_random LIKE :event_badge_id_random
                {sql_hidden}
                {sql_enabled}
            ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            {sql_limit};
            """
    elif event_person_id and len(event_person_id) > 2:
        sql = f"""
            SELECT *
            FROM `v_event_badge` AS event_badge
            WHERE event_badge.event_id = :event_id
                AND event_badge.event_person_id_random LIKE :event_person_id_random
                {sql_hidden}
                {sql_enabled}
            ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            {sql_limit};
            """
    elif external_id and len(external_id) > 2:
        sql = f"""
            SELECT *
            FROM `v_event_badge` AS event_badge
            WHERE event_badge.event_id = :event_id
                AND event_badge.external_id LIKE :external_id
                {sql_hidden}
                {sql_enabled}
            ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            {sql_limit};
            """
    elif query_str and len(query_str) > 1:
        if badge_only:
            sql = f"""
                SELECT *
                FROM `v_event_badge_only` AS event_badge
                WHERE event_badge.event_id = :event_id
                    {sql_badge_type_code}
                    AND event_badge.query_str LIKE :query_str
                    {sql_hidden}
                    {sql_enabled}
                ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
                {sql_limit};
                """
        else:
            # sql = f"""
            #     SELECT *
            #     FROM `v_event_badge` AS event_badge
            #     WHERE event_badge.event_id = :event_id
            #         AND (
            #             event_badge.given_name LIKE :query_str
            #             OR event_badge.family_name LIKE :query_str
            #             OR event_badge.email LIKE :query_str
            #             )
            #         {sql_enabled}
            #     ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            #     {sql_limit};
            #     """
            # sql = f"""
            #     SELECT *
            #     FROM `v_event_badge` AS event_badge
            #     WHERE event_badge.event_id = :event_id
            #         AND (
            #             event_badge.given_name IN (:query_li)
            #             OR event_badge.family_name IN (:query_li)
            #             OR event_badge.email IN (:query_li)
            #             )
            #         {sql_enabled}
            #     ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            #     {sql_limit};
            #     """
            sql = f"""
                SELECT *
                FROM `v_event_badge` AS event_badge
                WHERE event_badge.event_id = :event_id
                    {sql_badge_type_code}
                    AND event_badge.query_str LIKE :query_str
                    {sql_hidden}
                    {sql_enabled}
                ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
                {sql_limit};
                """
    else:
        if badge_only:
            sql = f"""
                SELECT *
                FROM `v_event_badge_only` AS event_badge
                WHERE event_badge.event_id = :event_id
                    {sql_badge_type_code}
                    AND (event_badge.given_name LIKE :given_name
                    AND event_badge.family_name LIKE :family_name)
                    AND event_badge.email LIKE :email
                    {sql_hidden}
                    {sql_enabled}
                ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
                {sql_limit};
                """
        else:
            # sql = f"""
            #     SELECT *
            #     FROM `v_event_badge` AS event_badge
            #     WHERE event_badge.account_id = :account_id
            #         AND (event_badge.external_id LIKE :external_id OR event_badge.external_id IS NULL)
            #         AND (event_badge.given_name LIKE :given_name
            #         AND event_badge.family_name LIKE :family_name)
            #         AND event_badge.email LIKE :email
            #         {sql_enabled}
            #     ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
            #     {sql_limit};
            #     """
            sql = f"""
                SELECT *
                FROM `v_event_badge` AS event_badge
                WHERE event_badge.event_id = :event_id
                    {sql_badge_type_code}
                    AND (event_badge.given_name LIKE :given_name
                    AND event_badge.family_name LIKE :family_name)
                    AND event_badge.email LIKE :email
                    {sql_hidden}
                    {sql_enabled}
                ORDER BY event_badge.given_name ASC, event_badge.family_name ASC, event_badge.created_on DESC, event_badge.updated_on DESC
                {sql_limit};
                """
    log.debug(sql)

    if sql_result := sql_select(data=data, sql=sql, as_list=True, rm_id_random=False, log_lvl=logging.INFO): # rm_id_random=True
        resp_data_li = []
        for record in sql_result:
            resp_data = Event_Badge_Base(**record).dict(by_alias=commons.by_alias, exclude_unset=commons.exclude_unset)
            resp_data_li.append(resp_data)
        return mk_resp(data=resp_data_li, response=commons.response)
    elif isinstance(sql_result, list) or sql_result is None: # Empty list or None
        log.info('No results')
        return mk_resp(data=None, status_code=404, response=commons.response) # Not Found
    else:
        log.warning('Likely bad request')
        return mk_resp(data=False, status_code=400, response=commons.response) # Bad Request
# ### END ### API Event Badge ### search_event_badge_obj_li() ###


# ### BEGIN ### API Event Badge ### event_badge_obj_email_review() ###
# Updated 2022-04-16
@router.get('/event/badge/{event_badge_id}/email_review', response_model=Resp_Body_Base)
async def event_badge_obj_email_review(
        event_badge_id: str = Path(min_length=11, max_length=22),
        root_url: Optional[str] = Query(None, min_length=10, max_length=100), # Absolute min = 7
        # to_email: str = Query(..., min_length=5, max_length=100),
        # to_name: str = Query(..., min_length=2, max_length=75),

        commons: Common_Route_Params = Depends(common_route_params),
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    if email_event_badge_review_url(event_badge_id=event_badge_id, root_url=root_url):
        return mk_resp(data=True, response=commons.response)
    else:
        return mk_resp(data=False, status_code=400, response=commons.response) # Bad Request
# ### END ### API Event Badge ### event_badge_obj_email_review() ###


# ### BEGIN ### API Event Badge ### get_event_badge_obj() ###
# Updated 2022-02-15
@router.get('/event/badge/{event_badge_id}', response_model=Resp_Body_Base)
async def get_event_badge_obj(
        event_badge_id: str = Path(min_length=11, max_length=22),
        badge_only: bool = False,
        inc_event_badge_template: bool = True,

        commons: Common_Route_Params = Depends(common_route_params),
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    # time.sleep(2)

    if event_badge_id := redis_lookup_id_random(record_id_random=event_badge_id, table_name='event_badge'): pass
    else: return mk_resp(data=None, status_code=404, response=commons.response)

    if event_badge_obj := load_event_badge_obj(
            event_badge_id = event_badge_id,
            badge_only = badge_only,
            inc_event_badge_template = inc_event_badge_template,

            # limit = commons.limit,
            # by_alias = commons.by_alias,
            # exclude_unset = commons.exclude_unset,
            # model_as_dict = model_as_dict,
            # enabled = commons.enabled,
            ):
        # event_badge_dict = event_badge_obj.dict(by_alias=commons.by_alias, exclude_unset=commons.exclude_unset)
        # return mk_resp(data=event_badge_dict, response=commons.response)
        response_data = event_badge_obj
    else:
        # NOTE: This is not really the best way to do this. This is to help get badges that are not tied to an event_person record.
        if event_badge_obj := load_event_badge_obj(
                event_badge_id = event_badge_id,
                badge_only = True,
                inc_event_badge_template = inc_event_badge_template,
                ):
            response_data = event_badge_obj
        else:
            return mk_resp(data=False, status_code=400, response=commons.response) # Bad Request

    return mk_resp(data=response_data, response=commons.response)
# ### END ### API Event Badge ### get_event_badge_obj() ###


# ### BEGIN ### API Event Badge ### get_account_obj_event_badge_list() ###
# Updated 2021-07-28
@router.get('/account/{account_id}/event/badge/list', response_model=Resp_Body_Base)
async def get_account_obj_event_badge_list(
        account_id: str = Path(min_length=11, max_length=22),
        limit: int = 500, # For now this covers any included objects or object lists
        enabled: str = 'enabled', # For now this covers any included objects or object lists
        inc_event_badge: bool = False,
        x_account_id: str = Header(...),
        by_alias: Optional[bool] = True,
        exclude_unset: Optional[bool] = True,
        response: Response = Response,
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    if account_id := redis_lookup_id_random(record_id_random=account_id, table_name='account'): pass
    else: return mk_resp(data=None, status_code=404, response=response)

    response_data = None
    # log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL

    # Updated 2021-07-28
    # from app.methods.event_badge_methods import get_event_badge_rec_list, load_event_badge_obj
    if event_badge_rec_list_result := get_event_badge_rec_list(
            account_id = account_id,
            limit = limit,
            enabled = enabled,
            ):
        event_badge_result_list = []
        for event_badge_rec in event_badge_rec_list_result:
            if load_event_badge_result := load_event_badge_obj(
                    event_badge_id = event_badge_rec.get('event_badge_id', None),
                    limit = limit,
                    by_alias = by_alias,
                    exclude_unset = exclude_unset,
                    # model_as_dict = model_as_dict,
                    enabled = enabled,
                    inc_event_badge = inc_event_badge,
                    ):
                event_badge_result_list.append(load_event_badge_result)
            else:
                event_badge_result_list.append(None)
        response_data = event_badge_result_list
    else:
        return mk_resp(data=False, status_code=400, response=response) # Bad Request

    return mk_resp(data=response_data, response=response)
# ### END ### API Event Badge ### get_account_obj_event_badge_list() ###


# ### BEGIN ### API Event Badge ### get_event_obj_event_badge_list() ###
# Updated 2021-07-28
@router.get('/event/{event_id}/event/badge/list', response_model=Resp_Body_Base)
async def get_event_obj_event_badge_list(
        event_id: str = Path(min_length=11, max_length=22),
        badge_only: bool = False,
        badge_type_code: str = None,
        printed: str = 'not_printed', # not_printed, printed, all

        inc_event_badge_template: bool = False,
        inc_event_person: bool = False,

        commons: Common_Route_Params = Depends(common_route_params),
        ):
    log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    if event_id := redis_lookup_id_random(record_id_random=event_id, table_name='event'): pass
    else: return mk_resp(data=None, status_code=404, response=commons.response)

    response_data = None
    # log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL

    # Updated 2021-07-28
    if event_badge_rec_list_result := get_event_badge_rec_list(
            event_id = event_id,
            badge_only = badge_only,
            badge_type_code = badge_type_code,
            printed = printed,
            enabled = commons.enabled,
            limit = commons.limit,
            offset = commons.offset,
            ):
        event_badge_result_list = []
        for event_badge_rec in event_badge_rec_list_result:
            if load_event_badge_result := load_event_badge_obj(
                    event_badge_id = event_badge_rec.get('event_badge_id', None),
                    badge_only = badge_only,
                    inc_event_badge_template = inc_event_badge_template,
                    ):
                event_badge_result_list.append(load_event_badge_result)
            else:
                event_badge_result_list.append(None)
        response_data = event_badge_result_list
    else:
        return mk_resp(data=False, status_code=400, response=commons.response) # Bad Request

    return mk_resp(data=response_data, response=commons.response)
# ### END ### API Event Badge ### get_event_obj_event_badge_list() ###


@router.delete('/event/badge/{obj_id}', response_model=Resp_Body_Base)
async def delete_event_badge_obj(
        obj_id: str = Path(min_length=11, max_length=22),
        x_account_id: str = Header(...),
        response: Response = Response,
        ):
    log.setLevel(logging.WARNING) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
    log.debug(locals())

    obj_type = 'event_badge'
    result = delete_obj_template(
        obj_type=obj_type,
        obj_id=obj_id,
        )
    return result