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

DB_PATH = os.path.join("data", "finance.db")
TABLE = "yosan_budget"

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} が読み込めませんでした -->"

def h(s):
    return html.escape(str(s) if s is not None else "")

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 get_val(row, key, default=""):
    if row is None:
        return default
    try:
        keys = row.keys()
        return row[key] if key in keys and row[key] is not None else default
    except:
        return default

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

row = fetch_latest_row(DB_PATH, TABLE, prefixes=("yu","yk","ye","yg"))
nendo = get_val(row, "nendo", "")
dt = get_val(row, "datetime", "（未入力）")

def month_inputs(prefix):
    # prefix: yu/yk/ye/yg
    tds = []
    for m in months:
        name = f"{prefix}{m}gatu"
        val = get_val(row, name, "")
        tds.append(f'<td><input class="form-control form-control-sm" type="text" name="{name}" value="{h(val)}"></td>')
    return "\n".join(tds)

print(header)
print(f"""
<div class="container py-4">
  <h2 class="mb-1">予算 入力</h2>
  <div class="text-muted small mb-3">前回保存：{h(dt)}　／　DB: {h(DB_PATH)}（{h(TABLE)}）</div>

  <div class="alert alert-secondary py-2">
    前回入力した予算をフォームに反映しています。修正したい月だけ書き換えて送信できます。
  </div>

  <form method="post" action="yosan_save.py">
    <div class="mb-3">
      <label class="form-label">年度（例：2025 / 令和7）</label>
      <input class="form-control" style="max-width:240px;" type="text" name="nendo" value="{h(nendo)}">
    </div>

    <h4 class="mt-4">売上（予算）</h4>
    <div class="table-responsive">
      <table class="table table-bordered align-middle">
        <thead class="table-dark">
          <tr><th>項目</th>{"".join([f"<th>{lab}</th>" for lab in labels])}</tr>
        </thead>
        <tbody>
          <tr><th style="white-space:nowrap;">予算売上</th>
            {month_inputs("yu")}
          </tr>
        </tbody>
      </table>
    </div>

    <h4 class="mt-4">経費（予算）</h4>
    <div class="table-responsive">
      <table class="table table-bordered align-middle">
        <thead class="table-dark">
          <tr><th>項目</th>{"".join([f"<th>{lab}</th>" for lab in labels])}</tr>
        </thead>
        <tbody>
          <tr><th style="white-space:nowrap;">予算経費</th>
            {month_inputs("yk")}
          </tr>
        </tbody>
      </table>
    </div>

    <h4 class="mt-4">経営利益（予算）</h4>
    <div class="table-responsive">
      <table class="table table-bordered align-middle">
        <thead class="table-dark">
          <tr><th>項目</th>{"".join([f"<th>{lab}</th>" for lab in labels])}</tr>
        </thead>
        <tbody>
          <tr><th style="white-space:nowrap;">予算経営利益</th>
            {month_inputs("ye")}
          </tr>
        </tbody>
      </table>
    </div>

    <h4 class="mt-4">現金残高（予算）</h4>
    <div class="table-responsive">
      <table class="table table-bordered align-middle">
        <thead class="table-dark">
          <tr><th>項目</th>{"".join([f"<th>{lab}</th>" for lab in labels])}</tr>
        </thead>
        <tbody>
          <tr><th style="white-space:nowrap;">予算現金残高</th>
            {month_inputs("yg")}
          </tr>
        </tbody>
      </table>
    </div>

    <div class="mt-4 d-flex gap-2">
      <button class="btn btn-primary" type="submit">保存（予算）</button>
      <a class="btn btn-secondary" href="index.html">戻る</a>
    </div>
  </form>
</div>
""")
print(footer)
