"""Year-end closing: the income and expense balances of a financial year are moved to retained earnings.

One entry per branch, dated the last day of the year (type 'closing'). The profit and loss report leaves closing
entries out, so the year's result stays visible; the balance sheet and later years see the closed balances.
Closing again after late entries closes only what is left; reopening deletes the closing entries.
"""
from datetime import date, timedelta

from .db import placeholders, q, x
from .i18n import t
from .ledger import PL_ROOTS, ROOT, AccountingError, audit, check_lock, company, create_move, now


def year_end_for(fy_start):
    from .recurring import add_months
    return (add_months(date.fromisoformat(fy_start), 12) - timedelta(days=1)).isoformat()


def fiscal_years():
    """[(start, end)] from the first entry up to the current year, latest first."""
    month = company()["fy_start_month"] or 1
    first = q("SELECT MIN(date) FROM moves WHERE state = 'posted'", one=True)[0] or date.today().isoformat()
    y = int(first[:4]) - (1 if int(first[5:7]) < month else 0)
    out = []
    while True:
        start = f"{y:04d}-{month:02d}-01"
        if start > date.today().isoformat():
            break
        out.append((start, year_end_for(start)))
        y += 1
    return list(reversed(out))


def pl_balances(branch_id, year_end):
    """{account_id: balance (debit-positive)} of income and expense accounts up to the year end, closings included,
    i.e. what is still open."""
    out = {}
    for r in q("SELECT ml.account_id, a.kind, SUM(ml.debit - ml.credit) AS bal FROM move_lines ml "
               "JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
               "WHERE m.state = 'posted' AND m.date <= ? AND ml.branch_id = ? GROUP BY ml.account_id, a.kind",
               (year_end, branch_id)):
        if ROOT[r["kind"]] in PL_ROOTS and r["bal"]:
            out[r["account_id"]] = (r["bal"], r["kind"])
    return out


def preview(fy_start, branch_ids):
    """Per branch: what closing the year would post, and what stops it."""
    year_end = year_end_for(fy_start)
    rows = []
    for b in q(f"SELECT * FROM branches WHERE id IN ({placeholders(branch_ids)}) ORDER BY code", list(branch_ids)):
        open_ = pl_balances(b["id"], year_end)
        income = -sum(v for v, k in open_.values() if ROOT[k] == "income")
        expense = sum(v for v, k in open_.values() if ROOT[k] == "expense")
        closed = q("SELECT COALESCE(SUM(profit), 0), COUNT(*) FROM fiscal_closings WHERE year_end = ? AND branch_id = ?",
                   (year_end, b["id"]), one=True)
        drafts = q("SELECT COUNT(*) FROM moves WHERE state = 'draft' AND branch_id = ? AND date BETWEEN ? AND ?",
                   (b["id"], fy_start, year_end), one=True)[0]
        rows.append({"branch": b, "income": income, "expense": expense, "profit": income - expense,
                     "accounts": len(open_), "closed_profit": closed[0], "closings": closed[1], "drafts": drafts})
    return year_end, rows


def close_year(fy_start, branch_ids, user_id=None):
    """Post the closing entries; returns [(branch code, profit, move_id)]."""
    year_end = year_end_for(fy_start)
    if year_end >= date.today().isoformat():
        raise AccountingError(t("A financial year can be closed once it has ended."))
    c = company()
    re_acc = c["retained_earnings_account_id"]
    if not re_acc:
        raise AccountingError(t("Set the retained earnings account in Company settings."))
    check_lock(year_end)
    done = []
    for b in q(f"SELECT * FROM branches WHERE id IN ({placeholders(branch_ids)}) ORDER BY code", list(branch_ids)):
        drafts = q("SELECT COUNT(*) FROM moves WHERE state = 'draft' AND branch_id = ? AND date BETWEEN ? AND ?",
                   (b["id"], fy_start, year_end), one=True)[0]
        if drafts:
            raise AccountingError(f"{b['code']}: {drafts} {t('draft entries are dated in this year. Post or delete them first.')}")
        open_ = pl_balances(b["id"], year_end)
        if not open_:
            continue
        label = f"{t('Year-end closing')} {fy_start[:4] if fy_start[5:7] == '01' else fy_start[:7] + ' – ' + year_end[:7]}"
        lines = [{"account_id": acc, "label": label, "debit": max(-bal, 0), "credit": max(bal, 0)}
                 for acc, (bal, _) in sorted(open_.items())]
        profit = -sum(bal for bal, _ in open_.values())  # credit-positive: income above expenses
        lines.append({"account_id": re_acc, "label": label, "debit": max(-profit, 0), "credit": max(profit, 0)})
        lines = [ln for ln in lines if ln["debit"] or ln["credit"]]
        journal = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'general' AND active = 1 "
                    "ORDER BY code = 'MISC' DESC, id LIMIT 1", (b["id"],), one=True)
        if not journal:
            raise AccountingError(f"{b['code']}: {t('The branch needs a miscellaneous (general) journal for stock entries.')}")
        mid = create_move(b["id"], journal["id"], year_end, lines, user_id, ref=label,
                          narration=t("Income and expenses of the year closed to retained earnings"),
                          move_type="closing", post=True)
        x("INSERT INTO fiscal_closings (year_end, branch_id, move_id, profit, created_by, created_at) VALUES (?,?,?,?,?,?)",
          (year_end, b["id"], mid, profit, user_id, now()))
        audit(user_id, "year-end close", "move", mid, f"{b['code']} {year_end}")
        done.append((b["code"], profit, mid))
    return done


def reopen_year(fy_start, branch_ids, user_id=None):
    """Delete the closing entries of a year (to correct it), unless the year is locked."""
    year_end = year_end_for(fy_start)
    check_lock(year_end)
    rows = q(f"SELECT * FROM fiscal_closings WHERE year_end = ? AND branch_id IN ({placeholders(branch_ids)})",
             [year_end] + list(branch_ids))
    for r in rows:
        x("DELETE FROM fiscal_closings WHERE id = ?", (r["id"],))
        x("DELETE FROM moves WHERE id = ?", (r["move_id"],))
        audit(user_id, "year-end reopen", "move", r["move_id"], year_end)
    return len(rows)
