from __future__ import annotations

from collections import Counter
from datetime import date, datetime
from unicodedata import category

import mysql.connector

# =========================
# Config
# =========================
# Update these to match your MySQL setup 
DB_HOST        = "dev1.atguru.work"
DB_PORT        = 3306
DB_USER        = "eslamayman"
DB_PASSWORD    = "AymanE@1"
DB_NAME        = "eslamayman"
# ===========================================================================
# Update these to match your source Schema and View 
# ===========================================================================
SOURCE_SCHEMA  = "eslamayman"
SOURCE_VIEW    = "vw_dev_est_reqs_status_forcast"
# ===========================================================================
# Update these to match your source column names for start and end dates 
# ===========================================================================
START_DATE_COL = "for_stat_st_dt_fmt2"
END_DATE_COL   = "for_stat_e_dt_fmt2"
# ===========================================================================
# Update these to match your Destination Schema and Table where you want to insert the results 
# ===========================================================================
TARGET_SCHEMA  = "eslamayman"
TARGET_TABLE   = "dev_attr_est_reqs_stat_forcast"
# ===========================================================================
# Update these to match your Destination Table column names for the output
# ===========================================================================

TARGET_ID_COL             = "iddev_afvr_est_reqs_stat_forcast"
TARGET_START_DATE_COL     = "for_stat_st_dt"
TARGET_END_DATE_COL       = "for_stat_e_dt"
TARGET_CATEGORY_COL       = "category"
TARGET_SECTION1_COL       = "section1"
TARGET_NEW_COL            = "new"
TARGET_CLOSE_COL          = "close"
TARGET_TOTAL_COL          = "total"
TARGET_NEW_PERCENT_COL    = "new_percent"
TARGET_CLOSE_PERCENT_COL  = "close_percent"
TARGET_CHANGE_PERCENT_COL = "change_percent"
# ===========================================================================
# Update these if you have already some existing data and want to start from that point
# ===========================================================================
CLEAR_TARGET_TABLE = True
INITIAL_OLD_TOTAL = 0
# total = (new - close) + old_total

# ===========================================================================
# Don't Update these unless you know what you are doing
# ===========================================================================
IGNORE_ZERO_DATE = True

def qident(name: str) -> str:
    return f"`{name}`"


def normalize_to_date(value):
    """
    Convert MySQL value to a python date object using only the date part.
    Handles:
      - datetime
      - date
      - string 'YYYY-MM-DD HH:MM:SS'
      - string 'YYYY-MM-DD'
      - None
      - '0000-00-00 00:00:00'
    """
    if value is None:
        return None

    if isinstance(value, datetime):
        return value.date()

    if isinstance(value, date):
        return value

    s = str(value).strip()
    if not s:
        return None

    if IGNORE_ZERO_DATE and s == "0000-00-00 00:00:00":
        return None

    s = s[:10]  # keep only YYYY-MM-DD

    try:
        return datetime.strptime(s, "%Y-%m-%d").date()
    except ValueError:
        return None


def get_connection():
    return mysql.connector.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
    )


def load_source_dates(cur):
    """
    Read dates from the view, normalize them to date only,
    and return two counters:
      - start_counter
      - end_counter
    """
    source_ref = f"{qident(SOURCE_SCHEMA)}.{qident(SOURCE_VIEW)}"
    start_col = qident(START_DATE_COL)
    end_col = qident(END_DATE_COL)

    sql = f"""
        SELECT {start_col}, {end_col}
        FROM {source_ref}
        WHERE {start_col} IS NOT NULL
           OR {end_col} IS NOT NULL
    """
    cur.execute(sql)
    rows = cur.fetchall()

    start_dates = []
    end_dates = []

    for start_value, end_value in rows:
        s_date = normalize_to_date(start_value)
        e_date = normalize_to_date(end_value)

        if s_date is not None:
            start_dates.append(s_date)

        if e_date is not None:
            end_dates.append(e_date)

    start_counter = Counter(start_dates)
    end_counter = Counter(end_dates)

    return start_counter, end_counter


def get_sorted_all_dates(start_counter: Counter, end_counter: Counter):
    all_dates = set(start_counter.keys()) | set(end_counter.keys())
    return sorted(all_dates)


def build_insert_rows(sorted_dates, start_counter, end_counter):
    """
    For each date:
      new   = count of that date in start column
      close = count of that date in end column
      total = previous_total + new - close
    """
    rows_to_insert = []
    old_total = INITIAL_OLD_TOTAL
    row_number = 1

    for current_date in sorted_dates:
        new_count = start_counter.get(current_date, 0)
        close_count = end_counter.get(current_date, 0)

        total = (new_count - close_count) + old_total

        # As requested by you:
        new_percent = f"{new_count * 100.00:.2f}%"
        close_percent = f"{close_count * 100.00:.2f}%"
        change_percent = f"{(new_count * 100.00) - (close_count * 100.00):.2f}%"

        rows_to_insert.append(
            (
                row_number,           # numbered from 1 to n
                current_date,         # start date
                current_date,         # end date
                "Total",              # category
                "Total",              # section1
                new_count,            # new
                close_count,          # close
                total,                # total
                new_percent,          # new_percent
                close_percent,        # close_percent
                change_percent,       # change_percent
            )
        )

        old_total = total
        row_number += 1

    return rows_to_insert


def insert_rows(cur, rows_to_insert):
    target_ref = f"{qident(TARGET_SCHEMA)}.{qident(TARGET_TABLE)}"

    cols = [
        TARGET_ID_COL,
        TARGET_START_DATE_COL,
        TARGET_END_DATE_COL,
        TARGET_CATEGORY_COL,
        TARGET_SECTION1_COL,
        TARGET_NEW_COL,
        TARGET_CLOSE_COL,
        TARGET_TOTAL_COL,
        TARGET_NEW_PERCENT_COL,
        TARGET_CLOSE_PERCENT_COL,
        TARGET_CHANGE_PERCENT_COL,
    ]

    col_sql = ", ".join(qident(c) for c in cols)
    placeholders = ", ".join(["%s"] * len(cols))

    insert_sql = f"""
        INSERT INTO {target_ref} ({col_sql})
        VALUES ({placeholders})
    """

    cur.executemany(insert_sql, rows_to_insert)


# =========================
# Main
# =========================
def main():
    conn = get_connection()
    cur = conn.cursor()

    try:
        # Read and prepare counts from source view
        start_counter, end_counter = load_source_dates(cur)
        sorted_dates = get_sorted_all_dates(start_counter, end_counter)

        if not sorted_dates:
            print("No valid dates found in the source view.")
            return

        rows_to_insert = build_insert_rows(sorted_dates, start_counter, end_counter)

        # Prepare target table
        if CLEAR_TARGET_TABLE:
            target_ref = f"{qident(TARGET_SCHEMA)}.{qident(TARGET_TABLE)}"
            cur.execute(f"TRUNCATE TABLE {target_ref}")

        # Insert all rows
        insert_rows(cur, rows_to_insert)
        conn.commit()

        print("========== DONE ==========")
        print(f"Inserted rows: {len(rows_to_insert)}")
        print(f"Earliest date : {sorted_dates[0]}")
        print(f"Latest date   : {sorted_dates[-1]}")
        print("==========================")

    except Exception:
        conn.rollback()
        raise
    finally:
        try:
            cur.close()
        except Exception:
            pass
        conn.close()


if __name__ == "__main__":
    main()