tools/import_excel.py

import os
import sys
import glob
from datetime import date, timedelta, datetime

sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))
from app import app
from models import KpiWeeklyData, EmployeeKpiValue, User
from extensions import db


# Column mapping: category -> [(metric_name, col_index), ...]
# Same structure as _seed_kpi_weekly_data in routes/main.py
METRICS_DEF = {
    'Revenue': [('Plan Total revenue', 1), ('Fact Total revenue', 2), ('LE% execution', 3),
                 ('AVG, Check in transaction', 4), ('Страница Экосистемы', 5)],
    'Visits': [('# UNIQUE CLIENTS', 6), ('PLAN UNIQUE CLIENTS', 7), ('% execution', 8), ('#LAS', 9), ('AVG visits', 10)],
    'KITs': [('#Plan Kits', 11), ('#KITs sold', 12), ('% execution', 13), ('#Kit LAS', 14), ('Reg Rate kits', 15), ('ADS KIT', 16)],
    'HTS': [('Plan HTS packs', 18), ('Fact HTS packs', 19), ('LE% execution', 20), ('% registred Heats', 21), ('%, carton from HTS', 24)],
    'Deg Set / Lending': [('% Deg Set + sticks', 27), ('% LAS Lending', 28), ('#Open Lendings', 29),
                          ('PLAN Open Lending LAS', 30), ('#Open Lending LAS', 31), ('% LAS Lending 1-30', 32), ('% Lending 1-30', 33)],
    'Education': [('Digital screen (science)', 34), ('Digital screen (superiority)', 35),
                  ('Digital screen (taste)', 36), ('#CO tests', 37), ('Сред. закрытых категорий', 38),
                  ('% успешных визитов', 39), ('% успешных (начинающие)', 40), ('% успешных (лояльные)', 41)],
    'Upgrade / Q-club': [('% Restart', 57), ('% Retention', 58), ('% Q-Club Engaged', 59), ('% Wallet', 60), ('Contactability', 61)],
    'QOS': [('%Random (общий)', 68), ('Блок % Q-Club', 69), ('Блок % Dual/4.0', 70), ('Brand Building %', 71), ('Discover', 72)],
}

IMPORT_DIR = os.path.join(os.path.dirname(__file__), '..', 'import_excel')
PROCESSED_DIR = os.path.join(IMPORT_DIR, '_processed')


def log(msg):
    ts = datetime.now().strftime('%H:%M:%S')
    print(f'[{ts}] {msg}')


def parse_val(raw):
    if raw is None:
        return None
    s = str(raw).strip().replace('\xa0', '').replace(',', '.').replace('%', '').replace(' ', '')
    if not s:
        return None
    try:
        return float(s)
    except ValueError:
        return None


def get_date_from_row(sheet, data_row_idx):
    """Look upwards from data_row_idx for a row containing a date."""
    for i in range(data_row_idx - 1, -1, -1):
        val = str(sheet.cell(row=i + 1, column=1).value or '').strip()
        parts = val.replace('.', ' ').split()
        nums = [p for p in parts if p.isdigit()]
        if len(nums) >= 3:
            try:
                d, m, y = int(nums[0]), int(nums[1]), int(nums[2])
                y = y + 2000 if y < 100 else y
                return date(y, m, d)
            except Exception:
                pass
    return None


def get_week_monday(d):
    return d - timedelta(days=d.weekday())


def process_sheet(sheet):
    """Process a single sheet. Returns list of (category, metric_name, week_start, value) tuples."""
    rows = list(sheet.iter_rows(values_only=True))
    results = []
    found_dates = {}  # row_index -> date

    # First pass: find all Moscow data rows and their dates
    data_rows = []
    for i, row in enumerate(rows):
        if not row or not row[0]:
            continue
        cell0 = str(row[0]).strip()
        if 'Moscow' in cell0:
            wd = get_date_from_row(sheet, i)
            if wd:
                week_start = get_week_monday(wd)
                data_rows.append((i, week_start, row))

    if not data_rows:
        log(f'  No Moscow rows found in sheet')
        return results

    log(f'  Found {len(data_rows)} data row(s)')

    for row_idx, week_start, row in data_rows:
        for cat_name, mlist in METRICS_DEF.items():
            for mname, col_idx in mlist:
                if col_idx < len(row):
                    raw = row[col_idx]
                    val = parse_val(raw)
                    if val is not None:
                        results.append((cat_name, mname, week_start, val))

    return results


def save_plans(entries):
    """Save plan entries to KpiWeeklyData."""
    count = 0
    for cat_name, mname, week_start, val in entries:
        is_plan = any(kw in mname for kw in ['Plan', 'PLAN', '#Plan'])
        if not is_plan:
            continue
        existing = KpiWeeklyData.query.filter_by(
            category=cat_name, metric_name=mname, week_start=week_start
        ).first()
        if existing:
            existing.value = str(val)
        else:
            rec = KpiWeeklyData(category=cat_name, metric_name=mname, week_start=week_start, value=str(val))
            db.session.add(rec)
        count += 1
    db.session.commit()
    return count


def save_facts(entries):
    """Save fact entries to EmployeeKpiValue for all active employees."""
    employees = User.query.filter(User.role.in_(['employee', 'senior_expert']), User.is_active == True).all()
    if not employees:
        log('  No active employees found')
        return 0

    count = 0
    for cat_name, mname, week_start, val in entries:
        is_plan = any(kw in mname for kw in ['Plan', 'PLAN', '#Plan'])
        if is_plan:
            continue
        # % execution metrics: store as fact value per employee
        # Flat metrics: distribute evenly among employees
        for emp in employees:
            existing = EmployeeKpiValue.query.filter_by(
                user_id=emp.id,
                category=cat_name,
                metric_name=mname,
                week_start=week_start
            ).first()
            if existing:
                existing.fact_value = str(val)
                existing.updated_at = datetime.utcnow()
            else:
                rec = EmployeeKpiValue(
                    user_id=emp.id,
                    category=cat_name,
                    metric_name=mname,
                    week_start=week_start,
                    fact_value=str(val),
                )
                db.session.add(rec)
            count += 1
    db.session.commit()
    return count


def process_all():
    with app.app_context():
        log(f'Import directory: {IMPORT_DIR}')
        os.makedirs(PROCESSED_DIR, exist_ok=True)

        xlsx_files = glob.glob(os.path.join(IMPORT_DIR, '*.xlsx'))
        xls_files = glob.glob(os.path.join(IMPORT_DIR, '*.xls'))

        all_files = sorted(xlsx_files + xls_files)
        if not all_files:
            log(f'No Excel files found in {IMPORT_DIR}')
            log('Place .xlsx files in the import_excel folder and run again.')
            return

        log(f'Found {len(all_files)} Excel file(s)')

        for fpath in all_files:
            fname = os.path.basename(fpath)
            log(f'Processing: {fname}')
            try:
                import openpyxl
                wb = openpyxl.load_workbook(fpath, data_only=True)
                log(f'  Sheets: {wb.sheetnames}')

                all_entries = []
                for sheet_name in wb.sheetnames:
                    ws = wb[sheet_name]
                    log(f'  Sheet: {sheet_name} ({ws.max_row}r x {ws.max_column}c)')
                    entries = process_sheet(ws)
                    log(f'    Extracted {len(entries)} values')
                    all_entries.extend(entries)

                if not all_entries:
                    log(f'  No data extracted, skipping')
                    wb.close()
                    continue

                plans = [e for e in all_entries if any(kw in e[1] for kw in ['Plan', 'PLAN', '#Plan'])]
                facts = [e for e in all_entries if e not in plans]

                saved_plans = save_plans(plans)
                saved_facts = save_facts(facts)
                log(f'  Saved: {saved_plans} plans, {saved_facts} fact values')

                # Move processed file
                dest = os.path.join(PROCESSED_DIR, fname)
                os.replace(fpath, dest)
                log(f'  Moved to _processed/')

                wb.close()

            except Exception as e:
                log(f'  ERROR: {e}')

        log('Import complete!')


if __name__ == '__main__':
    process_all()