"""
module for db manipulation functions
"""

import os
import sys
import datetime
import pandas as pd

ROOT_PATH = str(os.environ.get("ROOT_DIR_GESTAO_MIDIA"))
sys.path.append(ROOT_PATH)

from app.utils.db.db_connection import (  # pylint: disable=wrong-import-position
    engine,
    alchemy_exec,
    text,
)


def get_wp_blog(blog_id):
    """
    iterate through all blogs related to media database
    """

    sql = """
        SELECT blog_id, path FROM wp_blogs WHERE blog_id = :blog_id;
    """

    try:
        with engine.begin() as conn:
            return conn.execute(text(sql), {"blog_id": blog_id}).fetchall()

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err

def get_all_wp_blogs():
    """
    iterate through all blogs related to media database
    """

    sql = """
        SELECT blog_id, path FROM wp_blogs WHERE blog_id > 1 ORDER BY blog_id;
    """

    try:
        with engine.begin() as conn:
            return conn.execute(text(sql)).fetchall()

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err


def get_no_galery_messages(blog_id: int):
    """
    get all messages with attachment error from last 2 days
    Args:
        blog_id: int = current blog id
    Returns:
        messages_list: list(string) = a list of messages received from one hour ago until now
    """
    date = datetime.datetime.now().date() - datetime.timedelta(days=1.0)
    sql = f"""
        SELECT pm.post_id, pm.midia, pm.galeria_de_midias
        FROM wp_{blog_id}_posts p
        LEFT JOIN (
            SELECT
                meta.post_id,
                MAX(CASE WHEN meta.meta_key = 'midia' THEN meta.meta_value END) AS midia,
                MAX(CASE WHEN meta.meta_key = 'galeria_de_midias' THEN meta.meta_value END) AS galeria_de_midias
                from wp_{blog_id}_postmeta meta
                WHERE meta.meta_key IN ('midia', 'galeria_de_midias')
                GROUP BY meta.post_id
                ORDER BY meta.post_id
        ) pm
        ON p.ID = pm.post_id
        WHERE
            p.post_date >= :date
            AND p.post_type = "mensagem"
            AND pm.midia <> ""
            AND (galeria_de_midias LIKE '%midia%' OR galeria_de_midias IS NULL OR galeria_de_midias = '')
    """

    try:
        params = {"date": date}
        with engine.begin() as conn:
            df_mensagens = pd.read_sql(sql=text(sql), con=conn, params=params)
            return df_mensagens

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err

def get_no_galery_specific_message(blog_id: int, msg_id: int):
    """
    get specific message with attachment error
    Args:
        blog_id: int = current blog id
    Returns:
        messages_list: list(string) = a list of messages received from one hour ago until now
    """
    sql = f"""
        SELECT pm.post_id, pm.midia, pm.galeria_de_midias
        FROM wp_{blog_id}_posts p
        LEFT JOIN (
            SELECT
                meta.post_id,
                MAX(CASE WHEN meta.meta_key = 'midia' THEN meta.meta_value END) AS midia,
                MAX(CASE WHEN meta.meta_key = 'galeria_de_midias' THEN meta.meta_value END) AS galeria_de_midias
                from wp_{blog_id}_postmeta meta
                WHERE meta.meta_key IN ('midia', 'galeria_de_midias')
                GROUP BY meta.post_id
                ORDER BY meta.post_id
        ) pm
        ON p.ID = pm.post_id
        WHERE
            p.ID = :msg_id
            AND p.post_type = "mensagem"
            AND pm.midia <> ""
            AND (galeria_de_midias LIKE '%midia%' OR galeria_de_midias IS NULL OR galeria_de_midias = '')
    """

    try:
        params = {"msg_id": msg_id}
        with engine.begin() as conn:
            df_mensagens = pd.read_sql(sql=text(sql), con=conn, params=params)
            return df_mensagens

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err


def get_ids_from_filenames(blog_id: int, file_names_arr: list[str]):
    """
    gets ids from attachments based on file names
    Args:
        file_names_arr: list(string) = a list of file names
    Results:
        attachment_ids: list(int) = a list of ids
    """
    if not file_names_arr:
        return []

    yesterday = datetime.datetime.now().date() - datetime.timedelta(days=2.0)

    placeholders = "', '".join(file_names_arr)
    sql = f"""
        SELECT id, post_title, post_name
        FROM wp_{blog_id}_posts
        WHERE post_title IN ('{placeholders}')
        AND post_name REGEXP '^[a-f0-9]{{32}}-[a-z0-9]{{3,4}}$'
        AND post_type = 'attachment'
        AND post_date >= {yesterday};
    """
    try:
        with engine.connect() as conn:
            result = conn.execute(text(sql)).fetchall()
            return [str(row[0]) for row in result]

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err


def galery_insert_attachment_ids(
    blog_id: int, message_id: int, serialized_ids_array: str
):
    """
    Inserts ids into message attachment galery
    Args:
        blog_id: int
        message_id: int
        ids_serialized_array: str
    Results:
        attachment_ids: list(int) = a list of ids
    """

    sql = f"""
        UPDATE wp_{blog_id}_postmeta SET meta_value = '{serialized_ids_array}' WHERE meta_key = 'galeria_de_midias' AND post_id = {message_id};
    """
    try:
        with engine.connect() as conn:
            conn.execute(text(sql))
            conn.commit()

    except alchemy_exec.InterfaceError as err:
        raise RuntimeError(f"Erro de conexão: {err}") from err

    except alchemy_exec.DatabaseError as err:
        raise RuntimeError(f"Erro no banco de dados: {err}") from err

    except alchemy_exec.SQLAlchemyError as err:
        raise RuntimeError(f"Erro SQLAlchemy: {err}") from err

    except Exception as err:
        raise RuntimeError(f"Erro inesperado: {err}") from err