import re
import mysql.connector
from collections import defaultdict
from datetime import datetime

CONFIG_PATH = "/var/www/html/projects/aerion/global_dbconfig.php"

def extract_config(path):
    with open(path, "r") as f:
        content = f.read()

    def get_value(key):
        pattern = rf"\$GLOBALS\['{key}'\]\s*=\s*\"(.*?)\";"
        match = re.search(pattern, content)
        return match.group(1) if match else None

    return {
        "host": get_value("ser_sh"),
        "user": get_value("UN"),
        "password": get_value("pswd"),
        "database": get_value("DB"),
    }

read_cfg = extract_config(CONFIG_PATH)

write_cfg = {
    "host": "dev1.atguru.work",
    "user": "hemanta",
    "password": "Hemnt@01",
    "database": "hemanta"
}
conn_read = mysql.connector.connect(**read_cfg)
cursor_read = conn_read.cursor()

conn_write = mysql.connector.connect(**write_cfg)
cursor_write = conn_write.cursor()

cursor_read.execute("""
    SELECT
        for_stat_st_dt_fmt2,
        for_stat_e_dt_fmt2
    FROM aerion.vw_dev_est_reqs_status_forcast
""")

rows = cursor_read.fetchall()
def parse_date(d):
    if not d or d == '':
        return None
    try:
        return datetime.strptime(d, "%Y-%m-%d").date()
    except:
        return None

# AGGREGATE
new_count = defaultdict(int)
close_count = defaultdict(int)
date_set = set()

for start, end in rows:
    s = parse_date(start)
    e = parse_date(end)

    if s:
        new_count[s] += 1
        date_set.add(s)

    if e:
        close_count[e] += 1
        date_set.add(e)

sorted_dates = sorted(date_set)
results = []
running_total = 0

for d in sorted_dates:
    new = new_count[d]
    close = close_count[d]

    running_total += new - close

    results.append((
        d,
        d,
        new,
        close,
        running_total,
        f"{new * 100:.2f}%",
        f"{close * 100:.2f}%",
        f"{(new - close) * 100:.2f}%",
        "Total",
        "Total"
    ))

try:
    cursor_write.execute("TRUNCATE TABLE dev_attr_est_reqs_stat_forcast")

    cursor_write.executemany("""
        INSERT INTO dev_attr_est_reqs_stat_forcast (
            dev_est_reqs_stat_forcast_dt,
            qry_dt,
            `new`,
            `close`,
            total,
            new_pcnt,
            close_pcnt,
            change_pcnt,
            category,
            section1
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """, results)

    conn_write.commit()
    print(f"[INFO] Inserted {len(results)} rows")

except Exception as e:
    conn_write.rollback()
    print("[ERROR]", e)

cursor_read.close()
conn_read.close()
cursor_write.close()
conn_write.close()