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

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"),
    }

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

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 net_attr_st_dt
    FROM aerion.net_data
    WHERE net_script_nm = 'dev_attr_est_reqs_stat_forcast'
    LIMIT 1
""")

row = cursor_read.fetchone()
if not row or not row[0]:
    print("[ERROR] Missing affected date in net_data")
    exit(1)

AFFECTED_START_DATE = parse_date(row[0])
if not AFFECTED_START_DATE:
    print("[ERROR] Invalid date format in net_data")
    exit(1)

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()

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

null_start_count = 0
null_end_count = 0

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

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

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

if not date_set:
    print("[ERROR] No valid dates")
    exit(1)

max_date = max(date_set)
next_date = max_date + timedelta(days=1)
date_set.add(next_date)

print(f"[INFO] Recompute range: {AFFECTED_START_DATE} → {next_date}")

cursor_write.execute("""
    SELECT total FROM dev_attr_est_reqs_stat_forcast
    WHERE qry_dt < %s
    ORDER BY qry_dt DESC
    LIMIT 1
""", (AFFECTED_START_DATE,))

row = cursor_write.fetchone()
running_total = row[0] if row else 0

cursor_write.execute("""
    DELETE FROM dev_attr_est_reqs_stat_forcast
    WHERE qry_dt >= %s OR qry_dt IS NULL
""", (AFFECTED_START_DATE,))

print(f"[INFO] Cleared data from {AFFECTED_START_DATE}")

sorted_dates = sorted(date_set)
results = []

for d in sorted_dates:
    if d < AFFECTED_START_DATE:
        continue

    if d == next_date:
        new = null_start_count
        close = null_end_count
        qry_dt = None
        dev_dt = d
    else:
        new = new_count[d]
        close = close_count[d]
        qry_dt = d
        dev_dt = d

    running_total += new - close

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

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] Updated {len(results)} rows")

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