from finance_watcher_dataclasses.import_dataclasses import AccountRow
from typing import List, Optional
from finance_watcher_dataclasses.query_dataclasses import TransactionToInsert
from datetime import datetime
from helpers.helper_functions import get_string_from_date
from finance_watcher_dataclasses.enums import (
    BusinessTypeEnum, AccountTypeEnum as AccountTypeEnum
)

MAX_RESULTS_PER_QUERY = 50


def get_ensure_account_type_query(
    name: str,
    is_income: bool,
    is_expense: bool
) -> str:
    return f"""
    INSERT INTO account_type(name, is_income, is_expense)
    SELECT '{name}', {'True' if is_income else 'False'}, {'True' if is_expense else 'False'}
    WHERE NOT EXISTS (
        SELECT name FROM account_type WHERE name = '{name}'
    );
    SELECT 
        id, name, is_income, is_expense, created_date
    FROM account_type WHERE name = '{name}';
    """

def get_ensure_account_query(
    name: str,
    account_type_id: int,
    dashboard_user_id: int
) -> str:
    return f"""
    INSERT INTO account(name, account_type_id)
    SELECT '{name}', {account_type_id}
    WHERE NOT EXISTS (
        SELECT name FROM account 
        WHERE name = '{name}' AND dashboard_user_id = {dashboard_user_id}
    );
    SELECT
        id, name, account_type_id, created_date, updated_date, dashboard_user_id
    FROM account WHERE name = '{name}';
    """

def get_insert_default_business_types() -> str:
    enums_to_insert = ",".join([f"('{enum.value}')" for enum in BusinessTypeEnum])
    return f"""
    INSERT INTO business_type(name)
    VALUES{enums_to_insert}
    RETURNING id
    """

def get_ensure_business_query(
    name: str, business_type_id: str
) -> str:
    return f"""
    INSERT INTO business(name, business_type_id)
    SELECT '{name}', {business_type_id}
    WHERE 
        NOT EXISTS (
            SELECT name, business_type_id FROM business WHERE name = '{name}' AND business_type_id = {business_type_id}
        );
    SELECT * from business WHERE name = '{name}' AND business_type_id = {business_type_id}
    """

def get_insert_transaction(
    amount: int,
    business_id: int,
    is_expense: bool,
    created_date: datetime
) -> str:
    return f"""
    INSERT INTO transaction(amount, business_id, is_expense, created_date)
    VALUES({amount}, {business_id}, {is_expense}, '{created_date}')
    RETURNING id
    """

def get_insert_multiple_transactions(
    transactions: List[TransactionToInsert]
):
    transaction_values: List[str] = []
    for transaction in transactions:
        transaction_values.append(
            f'{transaction.amount}, {transaction.business_id}, {transaction.created_date}'
        )

    return f"""
    WITH rows AS (
        INSERT INTO transaction(amount, business_id, created_date)
        VALUES {','.join(transaction_values)}
        RETURNING 1
    )
    SELECT COUNT(*) FROM rows
    """

def get_accounts_and_latest_total_queries(user_id: int) -> str:
    return f"""
    WITH latest_created_date AS (
        select at.created_date from account_total at
        JOIN account a ON a.id = at.account_id
        WHERE a.dashboard_user_id = {user_id}
        ORDER BY at.created_date desc limit 1
    ) 
    SELECT a.name, a.id, at.total from account_total at
    INNER JOIN account a ON a.id = at.account_id
    WHERE 
        a.dashboard_user_id = {user_id} AND
        at.created_date = (SELECT created_date from latest_created_date)
    """

def get_insert_account_totals_query(
    account_rows: List[AccountRow]
)-> str:
    values: List[str] = []

    for row in account_rows:
        for date, amount in row.date_to_amount.items():
            values.append(
                f"({row.account_id}, {amount}, '{date}')"
            )

    return f"""
    INSERT INTO account_total(account_id, total, created_date)
    VALUES {",".join(values)}
    RETURNING total
    """

def get_latest_net_worth_query() -> str:
    return """
    WITH latest_created_date AS (
        select created_date from account_total 
        order by created_date desc limit 1
    )
    SELECT 
        ROUD(SUM(account_total.total)::numeric, 2) as net_worth FROM account_total 
    INNER JOIN account ON account.id = account_total.account_id
    WHERE account_total.created_date = (SELECT created_date from latest_created_date)
    """

def get_net_worth_by_years_query(year: Optional[str] = None) -> str:
    query  = """
    SELECT 
        ROUND(sum(account_total.total)::numeric,2) as total,
        account_total.created_date from account_total 
    INNER JOIN account ON account.id = account_total.account_id"""
    if year and year.isdigit():
        query += f"WHERE account_total.created_date >= '{year}-01-01'::date AND account_total.created_date < ('{year}-01-01'::date + '1 year'::interval)"

    query += """
    GROUP BY account_total.created_date
    ORDER by account_total.created_date desc"""

    return query

def get_create_budget_query(
    name: str,
    amount: int,
    dashboard_user_id: int,
    business_type_id: int
) -> str:
    return f"""
    INSERT INTO budget(name, amount, dashboard_user_id, business_type_id)
    VALUES('{name}', {amount}, {dashboard_user_id}, {business_type_id})

    RETURNING id, name, amount, dashboard_user_id, business_type_id, created_date, updated_date
    """

def get_update_budget_query(
    budget_id: int,
    name: Optional[str],
    amount: Optional[int],
    business_type_id: Optional[int]
) -> str:
    set_query: List[str] = []
    if name:
        set_query.append(f"name = '{name}'")
    if amount:
        set_query.append(f'amount = {amount}')
    if business_type_id:
        set_query.append(f'business_type_id = {business_type_id}')
    
    set_query.append(f"updated_date = '{get_string_from_date(datetime.now().date())}'")

    return f"""
    UPDATE budget
    SET {','.join(set_query)}
    WHERE id = {budget_id}
    RETURNING id
    """

def get_delete_budget_query(user_id: int, name: str) -> str:
    return f"""
    DELETE FROM budget
    WHERE dashboard_user_id = {user_id} AND name= '{name}'

    RETURNING {user_id}
    """

def get_insert_default_account_types_query() -> str:
    account_type_values =[]
    for enum in AccountTypeEnum:
        account_type_values.append(
            f"('{enum.name}',{AccountTypeEnum.is_income(enum)},{AccountTypeEnum.is_expense(enum)})"
        )
    return f"""
    INSERT INTO account_type(name, is_income, is_expense)
    VALUES {','.join(account_type_values)}
    RETURNING id
    """

def get_insert_transactions_query(transactions_to_insert: dict) -> str:
    values_to_insert = ','.join([
        f"({transaction['dashboard_user_id']},{transaction['amount']},{transaction['business_id']}, true, '{transaction['created_date']}')"
        for transaction in transactions_to_insert
    ])
    return f"""
    INSERT INTO transaction(dashboard_user_id, amount, business_id, is_expense, created_date)
    VALUES {values_to_insert}
    RETURNING *
    """

def get_insert_business_query(name: str, business_type_id: int) -> str:
    return f"""
    INSERT INTO business(name, business_type_id)
    VALUES ('{name}', {business_type_id})
    """

def get_insert_businesses_query(names_business_type_id: dict) -> str:
    dict_values = ','.join([
        f"('{name}', {business_type_id})"
        for name, business_type_id in names_business_type_id.items()
    ])
    return f"""
    INSERT INTO business(name, business_type_id)
    VALUES {dict_values}
    RETURNING *
    """

def get_insert_account_query(name: str, account_type_id: int, dashboard_user_id: int) -> str:
    return f"""
    INSERT INTO account(name, account_type_id, dashboard_user_id)
    VALUES ('{name}', {account_type_id}, {dashboard_user_id})
    RETURNING *
    """

def get_account_type_query(name: str) -> str:
    return f"""
    SELECT * from account_type
    WHERE name = '{name}'
    """

def get_business_type_query(name: str) -> str:
    return f"""
    SELECT * FROM business_type
    WHERE name = '{name}' 
    """

def get_insert_dashboard_user(name: str, email: str, password: str) -> str:
    return f"""
    INSERT INTO dashboard_user(name, email, password)
    VALUES ('{name}', '{email}', '{password}')
    RETURNING *
    """

def get_dashboard_user_query(user_id: int) -> str:
    return f"""
    SELECT * from dashboard_user
    WHERE id = {user_id}
    """

def get_transactions_query(
    user_id: int, 
    page_number: int, 
    business_type_id: Optional[int],
    start_date: Optional[datetime],
    end_date: Optional[datetime]
) -> str:
    bussiness_type_query = "" if business_type_id is None else f"""
    AND b.business_type_id = {business_type_id}
    """
    date_query = "" if start_date is None or end_date is None else f"""
    AND t.created_date <= '{end_date.date()}' AND t.created_date >= '{start_date.date()}'
    """
    query = f"""
    SELECT
        t.id,
        t.dashboard_user_id,
        t.amount,
        t.business_id,
        t.is_expense,
        t.created_date
    FROM transaction t
    JOIN business b ON b.id = t.business_id
    JOIN business_type bt ON bt.id = b.business_type_id
    WHERE t.id = {user_id}
    {bussiness_type_query} {date_query}
    ORDER BY created_date
    OFFSET ({(page_number-1) * MAX_RESULTS_PER_QUERY}) ROWS
    FETCH NEXT {MAX_RESULTS_PER_QUERY} ROWS ONLY
    """
    return query

def get_account_connection_query(
    user_id: int,
    account_ids: List[int]
) -> str:
    account_ids_str = [str(account_id) for account_id in account_ids]
    return f"""
    SELECT id
    FROM account
    WHERE 
        id IN ({','.join(account_ids_str)})
        AND account.dashboard_user_id = {user_id}
    """


def get_budget_query(dashboard_user_id: int, page: int) -> str:
    return f"""
    SELECT
        id,
        name,
        amount,
        dashboard_user_id,
        business_type_id,
        created_date,
        updated_date
    FROM budget
    WHERE dashboard_user_id = {dashboard_user_id}
    OFFSET ({(page-1) * MAX_RESULTS_PER_QUERY}) ROWS
    FETCH NEXT {MAX_RESULTS_PER_QUERY} ROWS ONLY
    """

def get_update_user_password_query(user_id: int, encrypted_password: str) -> str:
    return f"""
    UPDATE dashboard_user
    SET password = '{encrypted_password}', updated_date = '{get_string_from_date(datetime.now().date())}'
    WHERE id = {user_id}
    RETURNING id
    """

def get_user_by_email_query(email: str) -> str:
    return f"""
    SELECT
        id,
        name,
        email,
        created_date
    FROM dashboard_user
    WHERE email = '{email}'
    """

def get_check_user_query(dashboard_user_id: int, password: str) -> str:
    return f"""
    WITH user_check AS (
        SELECT 
            CASE WHEN password = '{password}' THEN 'TRUE'
            ELSE 'FALSE'
        END AS result
        FROM dashboard_user
        WHERE id = {dashboard_user_id}
    )
    UPDATE dashboard_user
    SET last_login = NOW()
    WHERE id = {dashboard_user_id} and (SELECT result FROM user_check) = 'TRUE'
    RETURNING id
    """