#!/usr/bin/env python3
"""Belts-fitted-per-generator as a CSV download.

Generated live from gen_service.db on each request, so it never goes stale.
Behind the normal login, reusing the gen_service page key.

Belt count is the sum of `quantity` on belt PART lines -- fan belts are fitted in
sets of six, so this counts individual belts, not replacement jobs. `belt_visits`
is the number of distinct service dates, which is the fairer "how often" measure.
Belt tensioners and fan-belt bolts are excluded; they are not belts.

SC is given twice on purpose: sc_field_usd is service calls with shop repairs
taken out (what the site now reports), sc_incl_shop_usd is Mesa's raw PM/SC flag
which counts a shop teardown as SC (what older figures used).
"""
import cgi
import csv
import io
import sqlite3
import sys
from datetime import datetime

sys.path.insert(0, '/opt/ngon/apps')
from managers.auth_manager import AuthManager, generate_login_page_html

DB = '/opt/ngon/data/gen_service.db'

BELT = ("upper(wrangled_description) LIKE '%BELT%' "
        "AND upper(wrangled_description) NOT LIKE '%TENSIONER%' "
        "AND upper(wrangled_description) NOT LIKE '%BOLT%'")

WORK_TYPE = """CASE WHEN work_type_override != '' THEN work_type_override
                    WHEN visit_type = 'shop'       THEN 'shop'
                    WHEN visit_type = 'parts_only' THEN 'parts_only'
                    WHEN service_type = 'PM'       THEN 'pm_field'
                    ELSE 'sc_field' END"""

form = cgi.FieldStorage()
auth = AuthManager('gen_service')
auth_required, should_exit, headers = auth.require_auth(form)

if should_exit:
    print("Content-Type: application/json")
    if headers:
        print(headers)
    print("")
    print('{"success": false, "error": "Authentication required"}' if auth_required
          else '{"success": true}')
    sys.exit(0)

if auth_required:
    print("Content-Type: text/html\n")
    print(generate_login_page_html("Belts by Generator"))
    sys.exit(0)

conn = sqlite3.connect(f'file:{DB}?mode=ro', uri=True)
conn.row_factory = sqlite3.Row
rows = conn.execute(f"""
    SELECT b.gen_id, b.belts, b.belt_lines, b.belt_visits, b.first_belt, b.last_belt,
           COALESCE(s.sc_field, 0)  sc_field,
           COALESCE(s.sc_raw, 0)    sc_raw,
           COALESCE(s.shop, 0)      shop,
           COALESCE(s.pm_field, 0)  pm_field,
           COALESCE(s.total, 0)     total,
           COALESCE(s.warranty, 0)  warranty
    FROM (SELECT gen_id, SUM(quantity) belts, COUNT(*) belt_lines,
                 COUNT(DISTINCT order_date) belt_visits,
                 MIN(order_date) first_belt, MAX(order_date) last_belt
          FROM service_records WHERE {BELT} AND gen_id GLOB '22*'
          GROUP BY gen_id) b
    LEFT JOIN (SELECT gen_id,
                 SUM(CASE WHEN {WORK_TYPE} = 'sc_field' THEN amount ELSE 0 END) sc_field,
                 SUM(CASE WHEN service_type = 'SC'      THEN amount ELSE 0 END) sc_raw,
                 SUM(CASE WHEN {WORK_TYPE} = 'shop'     THEN amount ELSE 0 END) shop,
                 SUM(CASE WHEN {WORK_TYPE} = 'pm_field' THEN amount ELSE 0 END) pm_field,
                 SUM(amount) total,
                 SUM(warranty_value_imputed) warranty
               FROM service_records GROUP BY gen_id) s ON s.gen_id = b.gen_id
    ORDER BY b.belts DESC, b.gen_id""").fetchall()
conn.close()

buf = io.StringIO()
w = csv.writer(buf)
w.writerow(['gen_id', 'belts', 'belt_lines', 'belt_visits', 'belts_per_visit',
            'first_belt', 'last_belt', 'sc_field_usd', 'sc_incl_shop_usd',
            'shop_usd', 'pm_field_usd', 'total_service_usd', 'warranty_est_usd'])
for r in rows:
    per = round(r['belts'] / r['belt_visits'], 1) if r['belt_visits'] else ''
    w.writerow([r['gen_id'], int(r['belts']), r['belt_lines'], r['belt_visits'], per,
                r['first_belt'], r['last_belt'],
                round(r['sc_field'], 2), round(r['sc_raw'], 2), round(r['shop'], 2),
                round(r['pm_field'], 2), round(r['total'], 2), round(r['warranty'], 2)])

name = f"belts_by_gen_{datetime.now().strftime('%Y%m%d')}.csv"
print("Content-Type: text/csv; charset=utf-8")
print(f"Content-Disposition: attachment; filename={name}")
print("")
print(buf.getvalue())