"""Скрипт для загрузки xlsx-файлов из data/new_data в DuckDB напрямую (без Streamlit)."""
import os
import duckdb
import pandas as pd

DB_PATH = "data/orders.duckdb"
DATA_DIR = "data/new_data"

SCHEMA = """
CREATE TABLE IF NOT EXISTS orders (
    date        DATE        NOT NULL,
    hour        SMALLINT    NOT NULL,
    tt_id       VARCHAR     NOT NULL,
    region      VARCHAR,
    service_id  INTEGER     NOT NULL DEFAULT -1,
    cnt_parcel  INTEGER     NOT NULL DEFAULT 0,
    cnt_courier INTEGER     NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_order ON orders (date, hour, tt_id, service_id);
"""


def load_xlsx(path: str) -> pd.DataFrame:
    df = pd.read_excel(path)
    df = df.rename(columns={
        "shopNo":     "tt_id",
        "id_service": "service_id",
        "region_tt":  "region",
    })
    df["tt_id"]       = df["tt_id"].astype(str)
    df["date"]        = pd.to_datetime(df["date"]).dt.date
    df["hour"]        = df["hour"].astype(int)
    df["service_id"]  = pd.to_numeric(df["service_id"], errors="coerce").fillna(-1).astype(int)
    df["cnt_parcel"]  = pd.to_numeric(df["cnt_parcel"], errors="coerce").fillna(0).astype(int)
    df["cnt_courier"] = pd.to_numeric(df["cnt_courier"], errors="coerce").fillna(0).astype(int)
    df["region"]      = df["region"].astype(str).str.strip()
    return df[["date", "hour", "tt_id", "region", "service_id", "cnt_parcel", "cnt_courier"]]


os.makedirs("data", exist_ok=True)
con = duckdb.connect(DB_PATH)
for stmt in SCHEMA.strip().split(";"):
    s = stmt.strip()
    if s:
        con.execute(s)

files = sorted([f for f in os.listdir(DATA_DIR) if f.endswith(".xlsx")])
print(f"Найдено файлов: {len(files)}")

total_inserted = 0
for fname in files:
    path = os.path.join(DATA_DIR, fname)
    print(f"\nЗагружаю: {fname}...", flush=True)
    df = load_xlsx(path)
    before = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
    con.register("_tmp", df)
    con.execute("""
        INSERT OR IGNORE INTO orders
            (date, hour, tt_id, region, service_id, cnt_parcel, cnt_courier)
        SELECT date, hour, tt_id, region, service_id, cnt_parcel, cnt_courier
        FROM _tmp
    """)
    con.unregister("_tmp")
    after = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
    inserted = after - before
    total_inserted += inserted
    print(f"  Строк в файле: {len(df):,} | Вставлено: {inserted:,}")

print(f"\n{'='*40}")
print(f"Итого вставлено: {total_inserted:,}")
print(f"Всего в БД: {con.execute('SELECT COUNT(*) FROM orders').fetchone()[0]:,}")
print("\nСтатистика по ТТ:")
stats = con.execute("""
    SELECT region, COUNT(DISTINCT tt_id) as tts, SUM(cnt_parcel) as orders, MIN(date), MAX(date)
    FROM orders GROUP BY region ORDER BY region
""").df()
print(stats.to_string())
con.close()
print("\nГотово!")