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

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

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

def percent(numer, denom):
    # denomが0やマイナスの場合でも、表示はできるようにする
    try:
        denom_i = int(denom)
        numer_i = int(numer)
    except:
        return "－"

    if denom_i == 0:
        return "－"
    try:
        return f"{(numer_i / denom_i * 100):.1f}%"
    except:
        return "－"

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):
    """prefix + {m}gatu の12ヶ月配列を返す（ない場合0）"""
    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 is_all_zero(arr):
    return all((to_int(x, 0) == 0) for x in arr)

# -----------------------------
# 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")
yos_yu = series_from_row(yosan_row, "yu")
hon_hu = series_from_row(hon_row, "hu")

# 経費
zen_zk = series_from_row(zen_row, "zk")
yos_yk = series_from_row(yosan_row, "yk")
hon_hk = series_from_row(hon_row, "hk")

# 現金残高（単月の値）
zen_zg = series_from_row(zen_row, "zg")
yos_yg = series_from_row(yosan_row, "yg")
hon_hg = series_from_row(hon_row, "hg")

# 経営利益（入力値を優先）
# - 前年：zen_input.py系に合わせて "ze"
# - 本年：hon_input.py系に合わせて "he"
# - 予算：命名規則上は "ye" が自然（無ければ売上-経費で補完）
zen_profit = series_from_row(zen_row, "ze")
hon_profit = series_from_row(hon_row, "he")
yos_profit = series_from_row(yosan_row, "ye")

# 予算に利益欄が無い場合、売上-経費で補完（保険）
if is_all_zero(yos_profit):
    yos_profit = [yos_yu[i] - yos_yk[i] for i in range(12)]

# もし前年/本年の利益欄も無い場合に備えた保険（基本は使われない想定）
if is_all_zero(zen_profit):
    zen_profit = [zen_zu[i] - zen_zk[i] for i in range(12)]
if is_all_zero(hon_profit):
    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 "（不明）"

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

print(f"""
<style>
  .muted {{ color:#666; font-size:0.92rem; }}
  .sec-title {{ font-size:1.2rem; font-weight:700; margin:0 0 10px; }}
</style>

<div class="container py-5">
  <h1 class="mb-2">月別 詳細（前年 / 予算 / 本年）</h1>
  <div class="muted mb-3">
    前年/予算：{h(zen_date)}　｜　本年：{h(hon_date)}
  </div>

  <div class="mb-4">
    <a class="btn btn-secondary" href="dashboard.py">ダッシュボードへ戻る</a>
  </div>

  <hr class="my-4">

  <h2 class="sec-title">① 売上（月別）</h2>
  <div class="table-responsive">
    <table class="table table-bordered table-striped">
      <thead class="table-dark">
        <tr>
          <th>月</th>
          <th>前年</th><th>予算</th><th>本年</th>
          <th>前年比</th><th>予算比</th>
        </tr>
      </thead>
      <tbody>
""")

for i, m in enumerate(months):
    print(f"""
        <tr>
          <td>{m}月</td>
          <td>{yen(zen_zu[i])}</td>
          <td>{yen(yos_yu[i])}</td>
          <td>{yen(hon_hu[i])}</td>
          <td>{percent(hon_hu[i], zen_zu[i])}</td>
          <td>{percent(hon_hu[i], yos_yu[i])}</td>
        </tr>
    """)

print(f"""
      </tbody>
    </table>
  </div>
  <canvas id="chartSales" height="120"></canvas>

  <hr class="my-4">

  <h2 class="sec-title">② 経費（月別）</h2>
  <div class="table-responsive">
    <table class="table table-bordered table-striped">
      <thead class="table-dark">
        <tr>
          <th>月</th>
          <th>前年</th><th>予算</th><th>本年</th>
          <th>前年比</th><th>予算比</th>
        </tr>
      </thead>
      <tbody>
""")

for i, m in enumerate(months):
    print(f"""
        <tr>
          <td>{m}月</td>
          <td>{yen(zen_zk[i])}</td>
          <td>{yen(yos_yk[i])}</td>
          <td>{yen(hon_hk[i])}</td>
          <td>{percent(hon_hk[i], zen_zk[i])}</td>
          <td>{percent(hon_hk[i], yos_yk[i])}</td>
        </tr>
    """)

print(f"""
      </tbody>
    </table>
  </div>
  <canvas id="chartCost" height="120"></canvas>

  <hr class="my-4">

  <h2 class="sec-title">③ 経営利益（月別：入力値）</h2>
  <div class="muted mb-2">※本年は <code>he*</code>（入力値）、前年は <code>ze*</code>（入力値）。予算は <code>ye*</code> が無い場合、売上−経費で補完。</div>
  <div class="table-responsive">
    <table class="table table-bordered table-striped">
      <thead class="table-dark">
        <tr>
          <th>月</th>
          <th>前年</th><th>予算</th><th>本年</th>
          <th>前年比</th><th>予算比</th>
        </tr>
      </thead>
      <tbody>
""")

for i, m in enumerate(months):
    print(f"""
        <tr>
          <td>{m}月</td>
          <td>{yen(zen_profit[i])}</td>
          <td>{yen(yos_profit[i])}</td>
          <td>{yen(hon_profit[i])}</td>
          <td>{percent(hon_profit[i], zen_profit[i])}</td>
          <td>{percent(hon_profit[i], yos_profit[i])}</td>
        </tr>
    """)

print(f"""
      </tbody>
    </table>
  </div>
  <canvas id="chartProfit" height="120"></canvas>

  <hr class="my-4">

  <h2 class="sec-title">④ 現金残高（月別）</h2>
  <div class="muted mb-2">※現金は「単月の残高値」として比較します。</div>
  <div class="table-responsive">
    <table class="table table-bordered table-striped">
      <thead class="table-dark">
        <tr>
          <th>月</th>
          <th>前年</th><th>予算</th><th>本年</th>
          <th>前年比</th><th>予算比</th>
        </tr>
      </thead>
      <tbody>
""")

for i, m in enumerate(months):
    print(f"""
        <tr>
          <td>{m}月</td>
          <td>{yen(zen_zg[i])}</td>
          <td>{yen(yos_yg[i])}</td>
          <td>{yen(hon_hg[i])}</td>
          <td>{percent(hon_hg[i], zen_zg[i])}</td>
          <td>{percent(hon_hg[i], yos_yg[i])}</td>
        </tr>
    """)

print(f"""
      </tbody>
    </table>
  </div>
  <canvas id="chartCash" height="120"></canvas>

  <hr class="my-4">

  <a class="btn btn-secondary" href="dashboard.py">ダッシュボードへ戻る</a>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
  const labels = {labels};

  function mkLineChart(canvasId, title, aLabel, aData, bLabel, bData, cLabel, cData) {{
    const el = document.getElementById(canvasId);
    if(!el) return;
    const ctx = el.getContext('2d');
    new Chart(ctx, {{
      type: 'line',
      data: {{
        labels: labels,
        datasets: [
          {{ label: aLabel, data: aData, borderColor:'#27ae60', backgroundColor:'rgba(39,174,96,0.10)', fill:false, tension:0.25 }},
          {{ label: bLabel, data: bData, borderColor:'#e74c3c', backgroundColor:'rgba(231,76,60,0.10)', fill:false, tension:0.25 }},
          {{ label: cLabel, data: cData, borderColor:'#3498db', backgroundColor:'rgba(52,152,219,0.10)', fill:false, tension:0.25 }},
        ]
      }},
      options: {{
        responsive: true,
        plugins: {{
          title: {{ display:true, text:title }},
          legend: {{ position:'top' }}
        }},
        scales: {{
          y: {{ beginAtZero: true }}
        }}
      }}
    }});
  }}

  mkLineChart('chartSales',  '売上（月別推移）',       '前年', {zen_zu},     '予算', {yos_yu},     '本年', {hon_hu});
  mkLineChart('chartCost',   '経費（月別推移）',       '前年', {zen_zk},     '予算', {yos_yk},     '本年', {hon_hk});
  mkLineChart('chartProfit', '経営利益（月別推移）',   '前年', {zen_profit}, '予算', {yos_profit}, '本年', {hon_profit});
  mkLineChart('chartCash',   '現金残高（月別推移）',   '前年', {zen_zg},     '予算', {yos_yg},     '本年', {hon_hg});
</script>
""")

print(footer)
