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

import os
import sys
import io
import sqlite3
import cgitb
import html
import cgi

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"
TABLE_YOSAN = "yosan_budget"
TABLE_HON   = "hon_finance"

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)

# -----------------------------
# util
# -----------------------------
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(round(n)):,}"
    except:
        return "0"

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

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_input_month_index(series):
    """本年入力済みの最終月index（0..11）。全部0なら0。"""
    idx = 0
    for i, v in enumerate(series):
        if to_int(v, 0) != 0:
            idx = i
    return 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")

# -----------------------------
# パラメータ：月と変動費率
# -----------------------------
form = cgi.FieldStorage()

# month は "4".."3" の文字で受け取る（なければ最終入力月）
default_idx = last_input_month_index(hon_hu)
default_month = months[default_idx]

sel_month = form.getfirst("m", default_month)
if sel_month not in months:
    sel_month = default_month
sel_idx = months.index(sel_month)

# 変動費率（%） 例：30 → 0.30
vr_raw = form.getfirst("vr", "30").strip()
try:
    vr = float(vr_raw)
except:
    vr = 30.0
if vr < 0: vr = 0.0
if vr > 95: vr = 95.0  # 100だと割れないので上限
vr_rate = vr / 100.0

# -----------------------------
# 月次データ
# -----------------------------
S = hon_hu[sel_idx]  # 本年売上
C = hon_hk[sel_idx]  # 本年経費
P = S - C            # 本年純利益（単月）

# 変動費・固定費 推定
var_cost = S * vr_rate
fixed_cost = C - var_cost
if fixed_cost < 0:
    fixed_cost = 0

# 損益分岐点売上
# BE = 固定費 ÷（1-変動費率）
contrib = 1.0 - vr_rate
be_sales = 0
if contrib > 0:
    be_sales = fixed_cost / contrib

gap = S - be_sales  # +なら超えてる、-なら不足

# 前年・予算の「同月」比較用
zenS = zen_zu[sel_idx]
yosS = yos_yu[sel_idx]

zenC = zen_zk[sel_idx]
yosC = yos_yk[sel_idx]

zenP = zenS - zenC
yosP = yosS - yosC

honCash = hon_hg[sel_idx]
zenCash = zen_zg[sel_idx]
yosCash = yos_yg[sel_idx]

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

# 月選択の<option>
opts = []
for m in months:
    sel = "selected" if m == sel_month else ""
    opts.append(f'<option value="{m}" {sel}>{m}月</option>')
opts_html = "\n".join(opts)

status_badge = ""
if gap >= 0:
    status_badge = '<span class="badge bg-success">黒字ライン超え</span>'
else:
    status_badge = '<span class="badge bg-danger">黒字ライン未達</span>'

print(f"""
<style>
  .muted {{ color:#666; font-size:0.92rem; }}
  .card-mini {{
    background:#fff; border:1px solid #eee; border-radius:12px;
    padding:14px; margin-bottom:12px;
  }}
  .kpi {{ font-size:1.35rem; font-weight:800; }}
  .kpi-sub {{ color:#555; font-size:0.95rem; }}
</style>

<div class="container py-5">
  <h1 class="mb-2">損益分岐点（月指定）</h1>
  <div class="muted mb-3">
    ※ 損益分岐点は「変動費率（%）」の仮定が必要です。現実に合わせて調整してください。
  </div>

  <form method="get" class="row g-2 align-items-end mb-4">
    <div class="col-auto">
      <label class="form-label">対象月</label>
      <select name="m" class="form-select">
        {opts_html}
      </select>
    </div>
    <div class="col-auto">
      <label class="form-label">変動費率（%）</label>
      <input name="vr" class="form-control" style="width:110px;" value="{h(vr_raw)}">
      <div class="muted">例：30（=30%）</div>
    </div>
    <div class="col-auto">
      <button class="btn btn-primary">表示</button>
    </div>
    <div class="col-auto">
      <a class="btn btn-secondary" href="dashboard.py">ダッシュボードへ</a>
      <a class="btn btn-outline-secondary" href="monthly.py">月別詳細へ</a>
    </div>
  </form>

  <div class="row">
    <div class="col-md-3">
      <div class="card-mini">
        <div class="muted">本年 {sel_month}月 売上</div>
        <div class="kpi">{yen(S)} 円</div>
        <div class="kpi-sub">前年比：{percent(S, zenS)} / 予算比：{percent(S, yosS)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="muted">本年 {sel_month}月 経費</div>
        <div class="kpi">{yen(C)} 円</div>
        <div class="kpi-sub">前年比：{percent(C, zenC)} / 予算比：{percent(C, yosC)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="muted">本年 {sel_month}月 純利益</div>
        <div class="kpi">{yen(P)} 円</div>
        <div class="kpi-sub">前年比：{percent(P, zenP)} / 予算比：{percent(P, yosP)}</div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="card-mini">
        <div class="muted">本年 {sel_month}月 現金残高</div>
        <div class="kpi">{yen(honCash)} 円</div>
        <div class="kpi-sub">前年比：{percent(honCash, zenCash)} / 予算比：{percent(honCash, yosCash)}</div>
      </div>
    </div>
  </div>

  <hr class="my-4">

  <h2 class="mb-3">損益分岐点（推定） {status_badge}</h2>

  <div class="row">
    <div class="col-md-4">
      <div class="card-mini">
        <div class="muted">変動費（推定）</div>
        <div class="kpi">{yen(var_cost)} 円</div>
        <div class="kpi-sub">売上 × {vr:.1f}%</div>
      </div>
    </div>
    <div class="col-md-4">
      <div class="card-mini">
        <div class="muted">固定費（推定）</div>
        <div class="kpi">{yen(fixed_cost)} 円</div>
        <div class="kpi-sub">経費 − 変動費（マイナスは0扱い）</div>
      </div>
    </div>
    <div class="col-md-4">
      <div class="card-mini">
        <div class="muted">損益分岐点売上（推定）</div>
        <div class="kpi">{yen(be_sales)} 円</div>
        <div class="kpi-sub">固定費 ÷（1 − 変動費率）</div>
      </div>
    </div>
  </div>

  <div class="alert {"alert-success" if gap >= 0 else "alert-danger"}">
    <div style="font-weight:700;">
      {sel_month}月の損益分岐点との差：{yen(gap)} 円
    </div>
    <div class="muted">
      {"損益分岐点を上回っています（この仮定の範囲で黒字）。" if gap >= 0 else "損益分岐点に届いていません（この仮定の範囲で赤字）。"}
    </div>
  </div>

  <canvas id="beChart" height="120"></canvas>

  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <script>
    const ctx = document.getElementById('beChart').getContext('2d');
    new Chart(ctx, {{
      type: 'bar',
      data: {{
        labels: ['本年 {sel_month}月'],
        datasets: [
          {{ label:'売上', data:[{S}], backgroundColor:'rgba(52,152,219,0.35)', borderColor:'#3498db', borderWidth:1 }},
          {{ label:'経費', data:[{C}], backgroundColor:'rgba(231,76,60,0.35)', borderColor:'#e74c3c', borderWidth:1 }},
          {{ label:'損益分岐点売上(推定)', data:[{be_sales}], backgroundColor:'rgba(39,174,96,0.35)', borderColor:'#27ae60', borderWidth:1 }},
        ]
      }},
      options: {{
        responsive:true,
        plugins: {{
          title: {{ display:true, text:'売上・経費・損益分岐点（{sel_month}月）' }},
          legend: {{ position:'top' }}
        }},
        scales: {{
          y: {{ beginAtZero:true }}
        }}
      }}
    }});
  </script>

</div>
""")

print(footer)
