#!/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"

DEFAULT_START_MONTH = 4

# -----------------------------
# settings.py から開始月
# -----------------------------
def safe_get_start_month():
    try:
        from settings import get_start_month
        m = int(get_start_month(DEFAULT_START_MONTH))
        if 1 <= m <= 12:
            return m
    except:
        pass
    return DEFAULT_START_MONTH

def rotated_months(start_month: int):
    a = list(range(start_month, 13))
    b = list(range(1, start_month))
    return [str(x) for x in (a + b)]

start_month = safe_get_start_month()
months = rotated_months(start_month)
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):
    try:
        numer = float(numer)
        denom = float(denom)
    except:
        return "－"
    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

# -----------------------------
# 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 "（不明）"

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

print(f"""
<style>
  .muted {{ color:#666; font-size:0.92rem; }}
  .section-title {{ display:flex; align-items:flex-end; justify-content:space-between; gap:12px; }}
  .tbl-note {{ font-size:0.9rem; color:#666; }}
</style>

<div class="container py-5">
  <div class="section-title">
    <div>
      <h1 class="mb-1">月別詳細（テーブル＆グラフ）</h1>
      <div class="muted">開始月：{start_month}月 ／ 表示順：{h(" → ".join(labels))}</div>
      <div class="muted">前年/予算：{h(zen_date)}　｜　本年：{h(hon_date)}</div>
    </div>
    <div class="d-flex flex-wrap gap-2">
      <a class="btn btn-outline-secondary" href="dashboard.py">ダッシュボードへ</a>
      <a class="btn btn-outline-secondary" href="settings_page.py">開始月設定</a>
      <a class="btn btn-outline-secondary" href="index.html">ホームへ</a>
    </div>
  </div>

  <hr class="my-4">

  <!-- 売上 -->
  <h2>◆ 売上（前年／予算／本年 + 比率）</h2>
  <div class="table-responsive">
    <table class="table table-bordered table-striped align-middle">
      <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("""
      </tbody>
    </table>
  </div>

  <hr class="my-4">

  <!-- 経費 -->
  <h2>◆ 経費（前年／予算／本年 + 比率）</h2>
  <div class="table-responsive">
    <table class="table table-bordered table-striped align-middle">
      <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("""
      </tbody>
    </table>
  </div>

  <hr class="my-4">

  <!-- 純利益 -->
  <h2>◆ 純利益（売上−経費：前年／予算／本年 + 比率）</h2>
  <div class="table-responsive">
    <table class="table table-bordered table-striped align-middle">
      <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("""
      </tbody>
    </table>
  </div>

  <hr class="my-4">

  <!-- 現金残高 -->
  <h2>◆ 現金残高（前年／予算／本年 + 比率）</h2>
  <div class="tbl-note mb-2">
    ※ 現金残高は「その月の残高」を比較します（合計ではありません）。
  </div>
  <div class="table-responsive">
    <table class="table table-bordered table-striped align-middle">
      <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>

  <hr class="my-4">

  <!-- グラフ -->
  <h2>◆ 折れ線グラフ（月別推移）</h2>
  <div class="muted mb-3">前年（緑）／予算（赤）／本年（青）</div>

  <canvas id="chartSales" height="120"></canvas>
  <canvas id="chartCost"  height="120" class="mt-4"></canvas>
  <canvas id="chartProfit" height="120" class="mt-4"></canvas>
  <canvas id="chartCash"  height="120" class="mt-4"></canvas>

  <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 ctx = document.getElementById(canvasId).getContext('2d');
      new Chart(ctx, {{
        type: 'line',
        data: {{
          labels: labels,
          datasets: [
            {{
              label: aLabel,
              data: aData,
              borderColor: '#27ae60',
              backgroundColor: 'rgba(39,174,96,0.12)',
              fill: false,
              tension: 0.25
            }},
            {{
              label: bLabel,
              data: bData,
              borderColor: '#e74c3c',
              backgroundColor: 'rgba(231,76,60,0.12)',
              fill: false,
              tension: 0.25
            }},
            {{
              label: cLabel,
              data: cData,
              borderColor: '#3498db',
              backgroundColor: 'rgba(52,152,219,0.12)',
              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>

  <div class="mt-4 d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="dashboard.py">ダッシュボードへ</a>
    <a class="btn btn-outline-secondary" href="settings_page.py">開始月設定</a>
    <a class="btn btn-outline-secondary" href="index.html">ホームへ</a>
  </div>
</div>
""")

print(footer)
