from datetime import date

import mysql.connector

from apify_fetch import fetch_linkedin_data
from config import APIFY_TOKEN_ENV, load_env, mysql_config, require_env
from parsers import (
    activity_item_key,
    group_items_by_profile,
    normalize_linkedin_url,
    parse_activity_items,
    parse_profile_items,
    profile_item_key,
)


def get_linkedin_rows(cursor):
    cursor.execute(
        """
        SELECT idprosp_corp, linkedin
        FROM prosp_corp
        WHERE linkedin LIKE 'https://www.linkedin.com/in/%'
        """
    )
    return cursor.fetchall()


def same_values(left, right, keys):
    if not left or not right:
        return False
    return all(left.get(key) == right.get(key) for key in keys)


def latest_profile_details(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT prosp_edu, prosp_exp, prosp_cert, prosp_skills
        FROM prosp_corp_linkedIn
        WHERE idprosp_corp = %s
        ORDER BY idprosp_corp_linkedIn DESC
        LIMIT 1
        """,
        (idprosp_corp,),
    )
    return cursor.fetchone()


def latest_connection_snapshot(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT linked_con, linked_folower, linked_con_tol, linked_con_dt
        FROM prosp_corp_linkedIn_con
        WHERE idprosp_corp = %s
        ORDER BY idprosp_corp_linkedIn_con DESC
        LIMIT 1
        """,
        (idprosp_corp,),
    )
    return cursor.fetchone()


def child_counts(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT
            (SELECT COUNT(*) FROM prosp_corp_linkedIn WHERE idprosp_corp = %s) AS linked_cnt,
            (SELECT COUNT(*) FROM prosp_corp_linkedIn_con WHERE idprosp_corp = %s) AS linked_con_cnt,
            (SELECT COUNT(*) FROM prosp_corp_linkedin_act WHERE idprosp_corp = %s) AS linked_act_cnt
        """,
        (idprosp_corp, idprosp_corp, idprosp_corp),
    )
    return cursor.fetchone()


def latest_activity_date(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT MAX(linked_act_dt) AS linked_act_dt
        FROM prosp_corp_linkedin_act
        WHERE idprosp_corp = %s
        """,
        (idprosp_corp,),
    )
    row = cursor.fetchone()
    return row["linked_act_dt"] if row else None


def average_day_gap(activity_dates):
    activity_dates = sorted(set(activity_date for activity_date in activity_dates if activity_date))
    if len(activity_dates) < 2:
        return None

    total_gap = 0
    for index in range(1, len(activity_dates)):
        total_gap += (activity_dates[index] - activity_dates[index - 1]).days

    return int((total_gap / (len(activity_dates) - 1)) + 0.5)


def activity_average_gap(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT DISTINCT linked_act_dt
        FROM prosp_corp_linkedin_act
        WHERE idprosp_corp = %s
            AND linked_act_dt IS NOT NULL
        ORDER BY linked_act_dt
        """,
        (idprosp_corp,),
    )
    return average_day_gap([row["linked_act_dt"] for row in cursor.fetchall()])


def latest_connection_date(cursor, idprosp_corp):
    cursor.execute(
        """
        SELECT MAX(linked_con_dt) AS linked_con_dt
        FROM prosp_corp_linkedIn_con
        WHERE idprosp_corp = %s
        """,
        (idprosp_corp,),
    )
    row = cursor.fetchone()
    return row["linked_con_dt"] if row else None


def update_main_table(
    cursor,
    idprosp_corp,
    profile_data,
    latest_connection_dt,
    latest_activity_dt,
    linked_act_avg,
    counts,
    run_date,
):
    set_change = (
        counts["linked_cnt"] > 2
        or counts["linked_con_cnt"] > 2
        or counts["linked_act_cnt"] > 2
    )
    linked_chg_sql = ", linked_chg = 'Y'" if set_change else ""

    cursor.execute(
        ("""
        UPDATE prosp_corp
        SET linked_profile = %s,
            linked_vrfd = %s,
            linked_since = %s,
            linked_all_info = %s,
            linked_con = %s,
            linked_folower = %s,
            linked_con_tol = %s,
            linked_con_dt = %s,
            linked_act_dt = %s,
            linked_run_dt = %s,
            linked_cnt = %s,
            linked_con_cnt = %s,
            linked_act_cnt = %s,
            linked_act_avg = %s
            {}
        WHERE idprosp_corp = %s
        """).format(linked_chg_sql),
        (
            profile_data["linked_profile"],
            profile_data["linked_vrfd"],
            profile_data["linked_since"],
            profile_data["linked_all_info"],
            profile_data["linked_con"],
            profile_data["linked_folower"],
            profile_data["linked_con_tol"],
            latest_connection_dt,
            latest_activity_dt,
            run_date,
            counts["linked_cnt"],
            counts["linked_con_cnt"],
            counts["linked_act_cnt"],
            linked_act_avg,
            idprosp_corp,
        ),
    )


def insert_profile_details(cursor, idprosp_corp, profile_data, run_date):
    latest = latest_profile_details(cursor, idprosp_corp)
    current = {
        "prosp_edu": profile_data["prosp_edu"],
        "prosp_exp": profile_data["prosp_exp"],
        "prosp_cert": profile_data["prosp_cert"],
        "prosp_skills": profile_data["prosp_skills"],
    }
    if same_values(latest, current, current.keys()):
        return 0

    cursor.execute(
        """
        INSERT INTO prosp_corp_linkedIn
            (idprosp_corp, prosp_edu, prosp_exp, prosp_cert, prosp_skills, linked_dt)
        VALUES (%s, %s, %s, %s, %s, %s)
        """,
        (
            idprosp_corp,
            profile_data["prosp_edu"],
            profile_data["prosp_exp"],
            profile_data["prosp_cert"],
            profile_data["prosp_skills"],
            run_date,
        ),
    )
    return 1


def insert_connection_snapshot(cursor, idprosp_corp, profile_data, run_date):
    latest = latest_connection_snapshot(cursor, idprosp_corp)
    current = {
        "linked_con": profile_data["linked_con"],
        "linked_folower": profile_data["linked_folower"],
        "linked_con_tol": profile_data["linked_con_tol"],
    }
    if same_values(latest, current, current.keys()):
        return 0

    cursor.execute(
        """
        INSERT INTO prosp_corp_linkedIn_con
            (idprosp_corp, linked_con, linked_folower, linked_con_tol, linked_con_dt)
        VALUES (%s, %s, %s, %s, %s)
        """,
        (
            idprosp_corp,
            profile_data["linked_con"],
            profile_data["linked_folower"],
            profile_data["linked_con_tol"],
            run_date,
        ),
    )
    return 1


def insert_activities(cursor, idprosp_corp, activities):
    if not activities:
        return 0

    cursor.execute(
        """
        SELECT act_type, act_type_info, linked_act_dt
        FROM prosp_corp_linkedin_act
        WHERE idprosp_corp = %s
        """,
        (idprosp_corp,),
    )
    existing = {
        (row["act_type"], row["act_type_info"], row["linked_act_dt"])
        for row in cursor.fetchall()
    }

    new_activities = []
    for activity in activities:
        activity_key = (
            activity["act_type"],
            activity["act_type_info"],
            activity["linked_act_dt"],
        )
        if activity_key in existing:
            continue

        existing.add(activity_key)
        new_activities.append(activity)

    if not new_activities:
        return 0

    cursor.executemany(
        """
        INSERT INTO prosp_corp_linkedin_act
            (idprosp_corp, act_type, act_type_info, linked_act_dt)
        VALUES (%s, %s, %s, %s)
        """,
        [
            (
                idprosp_corp,
                activity["act_type"],
                activity["act_type_info"],
                activity["linked_act_dt"],
            )
            for activity in new_activities
        ],
    )
    return len(new_activities)


def process_row(cursor, idprosp_corp, linkedin_url, profile_items, activity_items, run_date):
    print("Processing {} ({})".format(linkedin_url, idprosp_corp))

    profile_data = parse_profile_items(profile_items)
    if not profile_data:
        raise RuntimeError("No profile data returned")

    activities, _ = parse_activity_items(activity_items)

    profile_inserted = insert_profile_details(cursor, idprosp_corp, profile_data, run_date)
    connection_inserted = insert_connection_snapshot(cursor, idprosp_corp, profile_data, run_date)
    activities_inserted = insert_activities(cursor, idprosp_corp, activities)
    counts = child_counts(cursor, idprosp_corp)
    update_main_table(
        cursor,
        idprosp_corp,
        profile_data,
        latest_connection_date(cursor, idprosp_corp),
        latest_activity_date(cursor, idprosp_corp),
        activity_average_gap(cursor, idprosp_corp),
        counts,
        run_date,
    )
    print(
        "Inserted profile={}, connection={}, activities={}".format(
            profile_inserted,
            connection_inserted,
            activities_inserted,
        )
    )


def main():
    load_env()
    apify_token = require_env(APIFY_TOKEN_ENV)
    run_date = date.today()

    connection = mysql.connector.connect(**mysql_config())
    cursor = connection.cursor(dictionary=True)

    try:
        rows = get_linkedin_rows(cursor)
        print("Found {} LinkedIn profile(s).".format(len(rows)))
        if not rows:
            print("Done. Processed: 0. Failed: 0.")
            return

        linkedin_urls = [row["linkedin"] for row in rows]
        fetched = fetch_linkedin_data(apify_token, linkedin_urls)
        profile_items_by_url = group_items_by_profile(fetched["profile"], profile_item_key)
        activity_items_by_url = group_items_by_profile(fetched["activities"], activity_item_key)

        processed = 0
        failed = 0

        for row in rows:
            linkedin_key = normalize_linkedin_url(row["linkedin"])
            try:
                process_row(
                    cursor,
                    row["idprosp_corp"],
                    row["linkedin"],
                    profile_items_by_url.get(linkedin_key, []),
                    activity_items_by_url.get(linkedin_key, []),
                    run_date,
                )
                connection.commit()
                processed += 1
            except Exception as exc:
                connection.rollback()
                failed += 1
                print("Failed {}: {}".format(row["linkedin"], exc))

        print("Done. Processed: {}. Failed: {}.".format(processed, failed))
    finally:
        cursor.close()
        connection.close()


if __name__ == "__main__":
    main()