from loguru import logger
import pandas as pd
import datetime
from jinja2 import Template
from djagitit import db

from config import Params, Tables


def get_marketshare_date():
    logger.info("Getting marketshare max date")
    try:
        rdb = db.ReportingDB()
        sql = '''
        select max(report_date) as marketshare_date
        from spotify_reports.prorata_sony_weekly_v2
        '''
        marketshare_date = rdb.query(sql)['marketshare_date'].values[0]
        logger.info(f"Marketshare max date: {marketshare_date}")
        return marketshare_date
    except Exception as e:
        logger.exception(f"Failed to get marketshare date: {e}")
        raise e


def check_data_availability(partner, max_date):
    logger.info(f"Checking data availability for {partner} at {max_date}")
    try:
        rdb = db.ReportingDB()
        sql = Template(
            open(f'../sql/availability_{partner}.sql').read()
        ).render(max_date=max_date)
        result = rdb.query(sql)
        has_data = result['has_data'].values[0]
        if has_data:
            logger.success(f"Data available for {partner} for week ending on {max_date}")
        else:
            logger.warning(f"Data not available yet for {partner} for week ending on {max_date}")
        return has_data
    except Exception as e:
        logger.exception(f"Failed to check data availability for {partner}: {e}")
        return False


def delete_marketshare_weekly_partner(partner, marketshare_date, max_date):
    logger.info(f"Deleting marketshare data for {partner} from {marketshare_date} to {max_date}")
    rdb = db.ReportingDB()
    partner_names_clause = get_partner_names_clause(partner)
    sql = f'''
    delete from {Tables.marketshare_weekly}
    where report_date >= '{marketshare_date}'
        and report_date <= '{max_date}'
        and partner_name in {partner_names_clause}
    '''
    status = rdb.execute(sql)
    if status:
        logger.success(f"Successfully deleted marketshare data for {partner} from {marketshare_date} to {max_date}")
    else:
        logger.error(f"Failed to delete marketshare data for {partner} from {marketshare_date} to {max_date}")
    return status


def insert_marketshare_weekly(partner, marketshare_date, max_date):
    logger.info(f"Inserting marketshare data for {partner}")
    rdb = db.ReportingDB()
    insert_sql = f'''
    insert into {Tables.marketshare_weekly}
    '''
    query_sql = Template(
        open(f'../sql/marketshare_{partner}.sql').read()
        ).render(
            marketshare_date=marketshare_date,
            max_date=max_date
            )
    status = rdb.execute(f'{insert_sql}{query_sql}')
    if status:
        logger.success(f"Successfully inserted marketshare data for {partner}")
    else:
        logger.error(f"Failed to insert marketshare data for {partner}")
    return status


def get_partner_names_clause(partner):
    if partner.lower() == 'amazon':
        return "('Amazon Streaming', 'Prime Music', 'Amazon Free')"
    return f"('{partner.capitalize()}')"


def check_marketshare_exists(partner, marketshare_date):
    logger.info(f"Checking if marketshare data exists for {partner} at {marketshare_date}")
    try:
        rdb = db.ReportingDB()
        partner_names_clause = get_partner_names_clause(partner)
        sql = f'''
        select count(*) > 0 as exists_flag
        from {Tables.marketshare_weekly}
        where report_date = '{marketshare_date}'
            and partner_name in {partner_names_clause}
        '''
        result = rdb.query(sql)
        exists = result['exists_flag'].values[0]
        return bool(exists)
    except Exception as e:
        logger.exception(f"Failed to check marketshare existence for {partner}: {e}")
        return False


def feed_marketshare_weekly(marketshare_date=None, max_date=None, force=False, partner=None):
    if marketshare_date is None:
        marketshare_date = get_marketshare_date()
        max_date = marketshare_date + datetime.timedelta(days=6)
    if max_date is None:
        max_date = get_marketshare_date() + datetime.timedelta(days=6)

    partners = [partner] if partner else Params.partners
    logger.info(f"Processing marketshare data for partners from {marketshare_date} to {max_date}")
    results = {}
    for p in partners:
        if not force and check_marketshare_exists(p, marketshare_date):
            logger.info(f"Skipping {p} - data already exists for {marketshare_date}")
            results[p] = None
            continue
        if check_data_availability(p, max_date):
            status = delete_marketshare_weekly_partner(p, marketshare_date, max_date)
            if status:
                status = insert_marketshare_weekly(p, marketshare_date, max_date)
            results[p] = status
        else:
            logger.warning(f"Skipping {p} - data not available for {max_date}")
            results[p] = None
    return results