#!/usr/bin/python3
# -*- coding: utf-8 -*-

import os
import sys
import io
import sqlite3
import cgitb
import html
import urllib.parse

cgitb.enable()
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")
print("Content-Type: text/html; charset=utf-8\n")

# -----------------------------
# 設定
# -----------------------------
TEMPLATE_HEADER = "temp/header.txt"
TEMPLATE_FOOTER = "temp/footer.txt"

DB1_PATH = os.path.join("data", "finance.db")     # DB①：前年＋予算
DB2_PATH = os.path.join("data", "thisyear.db")    # DB②：本年

TABLE_ZEN   = "zen_finance"       # DB①：前年
TABLE_YOSAN = "yosan_budget"      # DB①：予算
TABLE_HON   = "hon_finance"       # DB②：本年

months = ["4","5","6","7","8","9","10","11","12","1","2","3"]
labels = [f"{m}月" for m in months]

# -----------------------------
# テンプレ読み込み
# -----------------------------
def read_template(path):
    try:
        with open(path, encoding="utf-8") as f:
            return f.read()
    except:
        return f"<!-- {path} が読み込めませんでした -->"

header = read_template(TEMPLATE_HEADER)
footer = read_template(TEMPLATE_FOOTER)

# -----------------------------
# 便利関数
# -----------------------------
def h(s):
    return html.escape(str(s) if s is not None else "")

def to_int(v, default=0):
    try:
        s = str(v).strip().replace(",", "")
        if s == "":
            return default
        return int(float(s))
    except:
        return default

def yen(n):
    try:
        return f"{int(n):,}"
    except:
        return "0"

def percent(numer, denom):
    if denom == 0:
        return "－"
    return f"{(numer / denom * 100):.1f}%"

def diff_yen(a, b):
    d = int(a) - int(b)
    sign = "+" if d > 0 else ""
    return f"{sign}{d:,}"

def table_exists(conn, table):
    cur = conn.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table,))
    return cur.fetchone() is not None

def get_columns(conn, table):
    cur = conn.cursor()
    cur.execute(f"PRAGMA table_info({table})")
    return [r[1] for r in cur.fetchall()]

def row_has_meaningful_data(row, prefixes=None):
    if row is None:
        return False
    try:
        keys = row.keys()
    except:
        return False

    # 年度だけ入っている場合も有効データとして扱う
    if "nendo" in keys:
        nendo = row["nendo"]
        if nendo is not None and str(nendo).strip() != "":
            return True

    if not prefixes:
        prefixes = []

    for k in keys:
        if k in ("id", "datetime", "nendo"):
            continue
        if prefixes and not any(str(k).startswith(p) for p in prefixes):
            continue
        v = row[k]
        if v is None:
            continue
        sv = str(v).strip().replace(",", "")
        if sv == "":
            continue
        try:
            if float(sv) != 0:
                return True
        except:
            return True
    return False

def fetch_latest_row(dbpath, table, order_candidates=("datetime", "id"), prefixes=None):
    if not os.path.exists(dbpath):
        return None
    conn = sqlite3.connect(dbpath)
    conn.row_factory = sqlite3.Row
    try:
        if not table_exists(conn, table):
            return None
        cols = get_columns(conn, table)
        order_col = None
        for c in order_candidates:
            if c in cols:
                order_col = c
                break
        if order_col is None:
            order_col = cols[0]
        cur = conn.cursor()
        cur.execute(f"SELECT * FROM {table} ORDER BY {order_col} DESC")
        rows = cur.fetchall()
        for row in rows:
            if row_has_meaningful_data(row, prefixes):
                return row
        return rows[0] if rows else None
    except:
        return None
    finally:
        conn.close()
def series_from_row(row, prefix):
    if row is None:
        return [0]*12
    keys = row.keys()
    out = []
    for m in months:
        k = f"{prefix}{m}gatu"
        out.append(to_int(row[k], 0) if k in keys else 0)
    return out

def last_filled_index(*series_list):
    last = -1
    for arr in series_list:
        for i, v in enumerate(arr):
            if to_int(v, 0) != 0:
                last = max(last, i)
    return last

def idx_from_month_str(mstr):
    if mstr in months:
        return months.index(mstr)
    return None

def safe_sum_upto(arr, idx):
    if idx is None or idx < 0:
        return 0
    return sum(arr[:idx+1])

def safe_avg_upto(arr, idx):
    if idx is None or idx < 0:
        return 0
    part = arr[:idx+1]
    if not part:
        return 0
    return int(sum(part) / len(part))

def safe_val(arr, idx):
    if idx is None or idx < 0 or idx >= len(arr):
        return 0
    return arr[idx]

# -----------------------------
# DB読み込み
# -----------------------------
zen_row   = fetch_latest_row(DB1_PATH, TABLE_ZEN, prefixes=("zu","zk","ze","zg"))
yosan_row = fetch_latest_row(DB1_PATH, TABLE_YOSAN, prefixes=("yu","yk","ye","yg"))
hon_row   = fetch_latest_row(DB2_PATH, TABLE_HON, prefixes=("hu","hk","he","hg"))

zen_zu = series_from_row(zen_row, "zu")  # 前年 売上
zen_zk = series_from_row(zen_row, "zk")  # 前年 経費
zen_zg = series_from_row(zen_row, "zg")  # 前年 現金

yos_yu = series_from_row(yosan_row, "yu")  # 予算 売上
yos_yk = series_from_row(yosan_row, "yk")  # 予算 経費
yos_yg = series_from_row(yosan_row, "yg")  # 予算 現金

hon_hu = series_from_row(hon_row, "hu")  # 本年 売上
hon_hk = series_from_row(hon_row, "hk")  # 本年 経費
hon_hg = series_from_row(hon_row, "hg")  # 本年 現金

zen_profit = [zen_zu[i] - zen_zk[i] for i in range(12)]
yos_profit = [yos_yu[i] - yos_yk[i] for i in range(12)]
hon_profit = [hon_hu[i] - hon_hk[i] for i in range(12)]

zen_date = zen_row["datetime"] if (zen_row and "datetime" in zen_row.keys()) else "（不明）"
hon_date = hon_row["datetime"] if (hon_row and "datetime" in hon_row.keys()) else "（不明）"

# -----------------------------
# 対象月（select指定 or 最終入力月）
# -----------------------------
qs = os.environ.get("QUERY_STRING", "")
params = urllib.parse.parse_qs(qs)
req_month = params.get("month", [""])[0].strip()  # "11" など

idx_req = idx_from_month_str(req_month) if req_month else None
idx_last = last_filled_index(hon_hu, hon_hk, hon_hg)

idx_target = idx_req if idx_req is not None else idx_last
if idx_target is None:
    idx_target = -1

target_month = months[idx_target] if idx_target >= 0 else "－"
target_label = f"{target_month}月" if idx_target >= 0 else "（未入力）"

# -----------------------------
# 上段：通期累計（12か月合計）
# ※現金は「平均」（4月〜対象月の平均）で表示したい要望
#   → “通期”カードでも「平均」は対象月までの平均にする（同時期比較が意味がある）
# -----------------------------
hon_sales_total = sum(hon_hu)
zen_sales_total = sum(zen_zu)
yos_sales_total = sum(yos_yu)

hon_cost_total  = sum(hon_hk)
zen_cost_total  = sum(zen_zk)
yos_cost_total  = sum(yos_yk)

hon_profit_total = sum(hon_profit)
zen_profit_total = sum(zen_profit)
yos_profit_total = sum(yos_profit)

# 現金残高平均（4月〜対象月）
hon_cash_avg = safe_avg_upto(hon_hg, idx_target)
zen_cash_avg = safe_avg_upto(zen_zg, idx_target)
yos_cash_avg = safe_avg_upto(yos_yg, idx_target)

# -----------------------------
# 下段：今月（対象月）の単月比較
# -----------------------------
hon_sales_m = safe_val(hon_hu, idx_target)
zen_sales_m = safe_val(zen_zu, idx_target)
yos_sales_m = safe_val(yos_yu, idx_target)

hon_cost_m = safe_val(hon_hk, idx_target)
zen_cost_m = safe_val(zen_zk, idx_target)
yos_cost_m = safe_val(yos_yk, idx_target)

hon_profit_m = safe_val(hon_profit, idx_target)
zen_profit_m = safe_val(zen_profit, idx_target)
yos_profit_m = safe_val(yos_profit, idx_target)

hon_cash_m = safe_val(hon_hg, idx_target)
zen_cash_m = safe_val(zen_zg, idx_target)
yos_cash_m = safe_val(yos_yg, idx_target)

# -----------------------------
# HTML
# -----------------------------
print(header)

print(f"""
<style>
  .card-mini {{
    background:#fff; border:1px solid #eee; border-radius:12px;
    padding:14px; margin-bottom:12px;
  }}
  .muted {{ color:#666; font-size:0.92rem; }}
  .kpi-title {{ font-size:0.95rem; color:#666; }}
  .kpi-val {{ font-size:1.4rem; font-weight:700; }}
  .badge-soft {{
    display:inline-block; padding:4px 10px; border-radius:999px;
    background:#f3f4f6; color:#111; font-size:0.85rem;
  }}
  select.form-select {{
    max-width: 220px;
    display: inline-block;
  }}
</style>

<div class="container py-5">
  <h1 class="mb-2">経営ダッシュボード</h1>
  <div class="muted mb-3">
    前年/予算：{h(zen_date)}　｜　本年：{h(hon_date)}
  </div>

  <!-- 月選択（GETで再読み込み） -->
  <form method="get" action="dashboard.py" class="mb-3">
    <span class="badge-soft">対象月：{h(target_label)}</span>
    <label class="ms-2 muted">月を指定：</label>
    <select name="month" class="form-select ms-2" onchange="this.form.submit()">
      <option value="">（未指定：最終入力月）</option>
""")

for m in months:
    sel = "selected" if (req_month == m) else ""
    print(f"""      <option value="{m}" {sel}>{m}月</option>""")

print(f"""
    </select>
    <noscript><button class="btn btn-sm btn-primary ms-2" type="submit">表示</button></noscript>
  </form>

  <!-- 上段：通期累計 -->
  <div class="row mt-2">
    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 売上累計（通期）</div>
        <div class="kpi-val">{yen(hon_sales_total)} 円</div>
        <div class="muted">前年比：{percent(hon_sales_total, zen_sales_total)} ／ 予算比：{percent(hon_sales_total, yos_sales_total)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 経費累計（通期）</div>
        <div class="kpi-val">{yen(hon_cost_total)} 円</div>
        <div class="muted">前年比：{percent(hon_cost_total, zen_cost_total)} ／ 予算比：{percent(hon_cost_total, yos_cost_total)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 純利益累計（通期）</div>
        <div class="kpi-val">{yen(hon_profit_total)} 円</div>
        <div class="muted">前年比：{percent(hon_profit_total, zen_profit_total)} ／ 予算比：{percent(hon_profit_total, yos_profit_total)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 現金残高平均（4月〜{h(target_month)}月）</div>
        <div class="kpi-val">{yen(hon_cash_avg)} 円</div>
        <div class="muted">前年同時期：{yen(zen_cash_avg)} ／ 予算同時期：{yen(yos_cash_avg)}</div>
        <div class="muted">前年比：{percent(hon_cash_avg, zen_cash_avg)} ／ 予算比：{percent(hon_cash_avg, yos_cash_avg)}</div>
      </div>
    </div>
  </div>

  <hr class="my-4">

  <!-- 下段：今月（対象月）の単月比較 -->
  <h2 class="mb-2">◆ 今月比較（{h(target_label)}）</h2>
  <div class="muted mb-3">
    未指定の場合は「本年で入力のある最終月」を自動採用します。
  </div>

  <div class="row">
    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 今月売上（{h(target_label)}）</div>
        <div class="kpi-val">{yen(hon_sales_m)} 円</div>
        <div class="muted">前年：{yen(zen_sales_m)}（差：{diff_yen(hon_sales_m, zen_sales_m)}）</div>
        <div class="muted">予算：{yen(yos_sales_m)}（差：{diff_yen(hon_sales_m, yos_sales_m)}）</div>
        <div class="muted">前年比：{percent(hon_sales_m, zen_sales_m)} ／ 予算比：{percent(hon_sales_m, yos_sales_m)}</div>
      </div>
    </div>

    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 今月経費（{h(target_label)}）</div>
        <div class="kpi-val">{yen(hon_cost_m)} 円</div>
        <div class="muted">前年：{yen(zen_cost_m)}（差：{diff_yen(hon_cost_m, zen_cost_m)}）</div>
        <div class="muted">予算：{yen(yos_cost_m)}（差：{diff_yen(hon_cost_m, yos_cost_m)}）</div>
        <div class="muted">前年比：{percent(hon_cost_m, zen_cost_m)} ／ 予算比：{percent(hon_cost_m, yos_cost_m)}</div>
      </div>
    </div>

    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 今月純利益（{h(target_label)}）</div>
        <div class="kpi-val">{yen(hon_profit_m)} 円</div>
        <div class="muted">前年：{yen(zen_profit_m)}（差：{diff_yen(hon_profit_m, zen_profit_m)}）</div>
        <div class="muted">予算：{yen(yos_profit_m)}（差：{diff_yen(hon_profit_m, yos_profit_m)}）</div>
        <div class="muted">前年比：{percent(hon_profit_m, zen_profit_m)} ／ 予算比：{percent(hon_profit_m, yos_profit_m)}</div>
      </div>
    </div>

    <div class="col-md-3">
      <div class="card-mini">
        <div class="kpi-title">本年 今月現金残高（{h(target_label)}）</div>
        <div class="kpi-val">{yen(hon_cash_m)} 円</div>
        <div class="muted">前年同月：{yen(zen_cash_m)} ／ 予算同月：{yen(yos_cash_m)}</div>
        <div class="muted">前年比：{percent(hon_cash_m, zen_cash_m)} ／ 予算比：{percent(hon_cash_m, yos_cash_m)}</div>
      </div>
    </div>
  </div>

  <div class="mt-4">
    <a href="index.html" class="btn btn-secondary">戻る</a>
    
    <a href="monthly.py" class="btn btn-primary mt-3">月別テーブル＆グラフ（詳細）</a>

    <a href="breakeven.py" class="btn btn-outline-primary mt-3">損益分岐点（月指定）</a>

  </div>
</div>
""")

print(footer)
