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

import os, sys, io, sqlite3, cgitb, 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")     # 前年
DB2_PATH = os.path.join("data", "thisyear.db")    # 本年
TABLE_ZEN = "zen_finance"
TABLE_HON = "hon_finance"

months = ["4","5","6","7","8","9","10","11","12","1","2","3"]

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 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

# 前年（参照）
zen_row = fetch_latest_row(DB1_PATH, TABLE_ZEN, prefixes=("zu","zk","ze","zg"))
zen_zu = series_from_row(zen_row, "zu")  # 売上
zen_zk = series_from_row(zen_row, "zk")  # 経費
zen_ze = series_from_row(zen_row, "ze")  # 経営利益(参考)
zen_zg = series_from_row(zen_row, "zg")  # 現金残高

# 本年（フォームに反映）
hon_row = fetch_latest_row(DB2_PATH, TABLE_HON, prefixes=("hu","hk","he","hg"))
hon_hu = series_from_row(hon_row, "hu")
hon_hk = series_from_row(hon_row, "hk")
hon_he = series_from_row(hon_row, "he")
hon_hg = series_from_row(hon_row, "hg")

# 年度（本年テーブルに nendo があれば表示。なければ空）
hon_nendo = ""
if hon_row is not None and "nendo" in hon_row.keys():
    hon_nendo = hon_row["nendo"] or ""

print(header)

print("""
<div class="container py-4">
  <h1 class="mb-3">本年実績 入力（前年参照つき）</h1>
  <p class="text-muted">前年はDBから表示（参照）。本年は「現在入力済みの値」をフォームに反映しているので、修正して送信すれば上書きできます。</p>

  <form method="post" action="hon_save.py" class="mb-5">
    <div class="mb-3">
      <label class="form-label">年度（例：2026 / 令和8）</label>
      <input class="form-control" style="max-width:260px;" type="text" name="nendo" value="%s">
    </div>
""" % h(hon_nendo))

def make_row(title_left, prefix_zen, prefix_hon, zen_arr, hon_arr):
    # 1行：前年(参照)＋本年(入力)
    tds = []
    for i, m in enumerate(months):
        tds.append(f"""
          <td>
            <div class="small text-muted">前年</div>
            <div>{zen_arr[i]:,}</div>
            <div class="small text-muted mt-2">本年</div>
            <input type="text" name="{prefix_hon}{m}gatu" value="{hon_arr[i]}" class="form-control form-control-sm">
          </td>
        """)
    return f"""
      <h4 class="mt-4">{h(title_left)}</h4>
      <div class="table-responsive">
      <table class="table table-bordered align-middle">
        <thead class="table-dark">
          <tr><th style="min-width:90px;">月</th>{"".join([f"<th>{mm}月</th>" for mm in months])}</tr>
        </thead>
        <tbody>
          <tr>
            <th>入力</th>
            {"" .join(tds)}
          </tr>
        </tbody>
      </table>
      </div>
    """

print(make_row("売上（前年参照＋本年入力）", "zu", "hu", zen_zu, hon_hu))
print(make_row("経費（前年参照＋本年入力）", "zk", "hk", zen_zk, hon_hk))
print(make_row("経営利益（参考：前年参照＋本年入力）", "ze", "he", zen_ze, hon_he))
print(make_row("現金残高（前年参照＋本年入力）※合計しない", "zg", "hg", zen_zg, hon_hg))

print("""
    <div class="mt-4">
      <button type="submit" class="btn btn-primary btn-lg">保存（上書き）</button>
      <a href="index.html" class="btn btn-secondary btn-lg ms-2">戻る</a>
    </div>
  </form>
</div>
""")

print(footer)
