"""Investor Portal - Flask + SQLite.

Run (development):   python app.py
Run (production):    gunicorn -w 2 -b 0.0.0.0:8000 app:app
"""
import calendar
import csv
import io
import os
import re
import secrets
import sqlite3
import uuid
from datetime import date, datetime, timedelta
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from functools import wraps
from pathlib import Path

from flask import (Flask, Response, abort, flash, g, redirect, render_template,
                   request, send_from_directory, session, url_for)
from werkzeug.security import check_password_hash, generate_password_hash

BASE = Path(__file__).resolve().parent
DATA = Path(os.environ.get("DATA_DIR", BASE / "instance"))
UPLOADS = DATA / "agreements"
DB_PATH = DATA / "portal.db"
DATA.mkdir(parents=True, exist_ok=True)
UPLOADS.mkdir(parents=True, exist_ok=True)

app = Flask(__name__)
_key_file = DATA / "secret.key"
if not _key_file.exists():
    _key_file.write_text(secrets.token_hex(32))
    try:
        os.chmod(_key_file, 0o600)
    except OSError:
        pass
app.config.update(
    SECRET_KEY=os.environ.get("SECRET_KEY") or _key_file.read_text().strip(),
    MAX_CONTENT_LENGTH=10 * 1024 * 1024,
    SESSION_COOKIE_HTTPONLY=True,
    SESSION_COOKIE_SAMESITE="Lax",
    SESSION_COOKIE_SECURE=os.environ.get("HTTPS", "0") == "1",
    PERMANENT_SESSION_LIFETIME=timedelta(hours=8),
)
if os.environ.get("BEHIND_PROXY") == "1":
    from werkzeug.middleware.proxy_fix import ProxyFix
    app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1)
COMPANY = os.environ.get("COMPANY_NAME", "Investor Portal")
MODES = ["Bank Transfer", "UPI", "Cash", "Cheque", "Other"]
ALLOWED_EXT = {"pdf", "jpg", "jpeg", "png"}


# --------------------------------------------------------------------------
# Database
# --------------------------------------------------------------------------
SCHEMA = """
CREATE TABLE IF NOT EXISTS profit_plans(
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  rate INTEGER NOT NULL,          -- paise of profit ...
  per_amount INTEGER NOT NULL,    -- ... per this many paise invested, per month
  active INTEGER NOT NULL DEFAULT 1,
  notes TEXT
);
CREATE TABLE IF NOT EXISTS investors(
  id INTEGER PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  phone TEXT, email TEXT, address TEXT, pan TEXT,
  bank_name TEXT, bank_account TEXT, bank_ifsc TEXT, nominee TEXT,
  principal INTEGER NOT NULL,     -- paise
  start_date TEXT NOT NULL,
  plan_id INTEGER NOT NULL REFERENCES profit_plans(id),
  status TEXT NOT NULL DEFAULT 'active',
  notes TEXT,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS users(
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL CHECK(role IN ('admin','investor')),
  investor_id INTEGER REFERENCES investors(id),
  active INTEGER NOT NULL DEFAULT 1,
  must_change INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS investor_documents(
  id INTEGER PRIMARY KEY,
  investor_id INTEGER NOT NULL REFERENCES investors(id) ON DELETE CASCADE,
  label TEXT NOT NULL,
  stored_name TEXT NOT NULL,
  original_name TEXT NOT NULL,
  uploaded_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS heads(
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('income','expense')),
  active INTEGER NOT NULL DEFAULT 1,
  system INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS transactions(
  id INTEGER PRIMARY KEY,
  voucher_no TEXT,
  txn_date TEXT NOT NULL,
  kind TEXT NOT NULL CHECK(kind IN ('receipt','payment')),
  head_id INTEGER NOT NULL REFERENCES heads(id),
  amount INTEGER NOT NULL,
  mode TEXT NOT NULL,
  party TEXT, reference TEXT, narration TEXT,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS payouts(
  id INTEGER PRIMARY KEY,
  investor_id INTEGER NOT NULL REFERENCES investors(id),
  period_from TEXT NOT NULL,
  period_to TEXT NOT NULL,
  due_date TEXT NOT NULL,
  principal INTEGER NOT NULL,
  rate INTEGER NOT NULL,
  per_amount INTEGER NOT NULL,
  months TEXT NOT NULL,
  amount INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid')),
  paid_date TEXT, paid_amount INTEGER, mode TEXT, reference TEXT, remarks TEXT,
  txn_id INTEGER REFERENCES transactions(id)
);
CREATE INDEX IF NOT EXISTS ix_payouts_inv ON payouts(investor_id, due_date);
CREATE INDEX IF NOT EXISTS ix_txn_date ON transactions(txn_date);
"""
PAYOUT_HEAD = "Investor Profit Payout"


def db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH, timeout=15)
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA foreign_keys=ON")
    return g.db


@app.teardown_appcontext
def close_db(_e=None):
    d = g.pop("db", None)
    if d is not None:
        d.close()


def q(sql, args=()):
    return db().execute(sql, args).fetchall()


def one(sql, args=()):
    return db().execute(sql, args).fetchone()


def ex(sql, args=(), commit=True):
    cur = db().execute(sql, args)
    if commit:
        db().commit()
    return cur


def init_db():
    con = sqlite3.connect(DB_PATH)
    con.executescript(SCHEMA)
    now = datetime.now().isoformat(timespec="seconds")
    if not con.execute("SELECT 1 FROM heads").fetchone():
        con.execute("INSERT INTO heads(name,type,system) VALUES(?,?,1)", (PAYOUT_HEAD, "expense"))
        for n, t in [("Office Rent", "expense"), ("Salaries", "expense"),
                     ("Electricity & Internet", "expense"), ("Stationery & Misc", "expense"),
                     ("Business Income", "income"), ("Interest Income", "income"),
                     ("Other Income", "income")]:
            con.execute("INSERT INTO heads(name,type) VALUES(?,?)", (n, t))
    if not con.execute("SELECT 1 FROM profit_plans").fetchone():
        con.execute("INSERT INTO profit_plans(name,rate,per_amount,notes) VALUES(?,?,?,?)",
                    ("Standard - INR 400 per INR 1,00,000 per month", 40000, 10000000, "Sample plan, edit as needed"))
    if not con.execute("SELECT 1 FROM users WHERE role='admin'").fetchone():
        pw = os.environ.get("ADMIN_PASSWORD") or secrets.token_urlsafe(9)
        con.execute("INSERT INTO users(username,password_hash,role,must_change,created_at) VALUES(?,?,?,?,?)",
                    ("admin", generate_password_hash(pw), "admin", 0 if os.environ.get("ADMIN_PASSWORD") else 1, now))
        print("=" * 60)
        print(" First run: admin account created")
        print(f"   username: admin\n   password: {pw}")
        print(" (change it after the first login)")
        print("=" * 60)
    con.commit()
    con.close()


# --------------------------------------------------------------------------
# Helpers: money, dates, formatting
# --------------------------------------------------------------------------
class Money(int):
    """Paise amount; rendered as INR in templates, as 2-decimals in CSV."""


def to_paise(s):
    try:
        d = Decimal(str(s).replace(",", "").strip())
    except InvalidOperation:
        raise ValueError("Enter a valid amount")
    if d < 0:
        raise ValueError("Amount cannot be negative")
    return int((d * 100).quantize(Decimal(1), ROUND_HALF_UP))


def rs(p):
    return f"{int(p or 0) / 100:.2f}"


def inr(p):
    p = int(p or 0)
    neg, p = p < 0, abs(p)
    r, pa = divmod(p, 100)
    s = str(r)
    if len(s) > 3:
        s = re.sub(r"(\d)(?=(\d\d)+$)", r"\1,", s[:-3]) + "," + s[-3:]
    return ("-" if neg else "") + "₹" + s + "." + f"{pa:02d}"


def dfmt(s):
    if not s:
        return ""
    return date.fromisoformat(s).strftime("%d %b %Y")


def pct(rate, per):
    return f"{Decimal(rate) / Decimal(per) * 100:.3f}".rstrip("0").rstrip(".") + "%"


def parse_date(s):
    s = (s or "").strip()
    for fmt in ("%Y-%m-%d", "%d-%m-%Y", "%d/%m/%Y"):
        try:
            return datetime.strptime(s, fmt).date()
        except ValueError:
            pass
    raise ValueError(f"Invalid date: {s or '(blank)'}")


def add_months(d, n):
    y = d.year + (d.month - 1 + n) // 12
    m = (d.month - 1 + n) % 12 + 1
    return date(y, m, min(d.day, calendar.monthrange(y, m)[1]))


def months_between(a, b):
    """Months from a (inclusive) to b (exclusive): whole months + leftover days/30."""
    n = 0
    while add_months(a, n + 1) <= b:
        n += 1
    rem = (b - add_months(a, n)).days
    return (Decimal(n) + Decimal(rem) / Decimal(30)).quantize(Decimal("0.0001"))


def calc_profit(principal, rate, per, months):
    return int((Decimal(principal) * Decimal(rate) / Decimal(per) * Decimal(months))
               .quantize(Decimal(1), ROUND_HALF_UP))


def fy_start(today=None):
    t = today or date.today()
    return date(t.year if t.month >= 4 else t.year - 1, 4, 1)


app.jinja_env.filters["inr"] = inr
app.jinja_env.filters["dfmt"] = dfmt
app.jinja_env.filters["pct"] = lambda p: pct(p["rate"], p["per_amount"])
app.jinja_env.tests["money"] = lambda v: isinstance(v, Money)
app.jinja_env.globals.update(company=COMPANY, today=lambda: date.today().isoformat(), MODES=MODES)


def csrf_token():
    if "_csrf" not in session:
        session["_csrf"] = secrets.token_hex(16)
    return session["_csrf"]


app.jinja_env.globals["csrf_token"] = csrf_token


def csv_response(name, headers, rows):
    def clean(v):
        if isinstance(v, Money):
            return rs(v)
        if isinstance(v, str) and v[:1] in ("=", "+", "-", "@"):
            return "'" + v
        return v
    out = io.StringIO()
    w = csv.writer(out)
    w.writerow(headers)
    for r in rows:
        w.writerow([clean(c) for c in r])
    return Response("﻿" + out.getvalue(), mimetype="text/csv",
                    headers={"Content-Disposition": f"attachment; filename={name}.csv"})


# --------------------------------------------------------------------------
# Auth
# --------------------------------------------------------------------------
_fails = {}


@app.before_request
def before():
    g.user = None
    uid = session.get("uid")
    if uid:
        g.user = one("SELECT * FROM users WHERE id=? AND active=1", (uid,))
        if not g.user:
            session.clear()
    if request.method == "POST":
        tok = session.get("_csrf")
        if not tok or not secrets.compare_digest(tok, request.form.get("_csrf", "")):
            abort(400)
    if (g.user and g.user["must_change"]
            and request.endpoint not in ("change_password", "logout", "static", "service_worker", "manifest")):
        return redirect(url_for("change_password"))


@app.after_request
def secure_headers(resp):
    resp.headers.setdefault("X-Content-Type-Options", "nosniff")
    resp.headers.setdefault("X-Frame-Options", "DENY")
    resp.headers.setdefault("Referrer-Policy", "same-origin")
    # Never let browsers or proxies keep copies of account pages, documents or reports.
    if request.endpoint not in ("static", "service_worker", "manifest"):
        resp.headers["Cache-Control"] = "no-store"
    return resp


# ---------------- PWA ----------------
@app.route("/sw.js")
def service_worker():
    resp = send_from_directory(app.static_folder, "sw.js", mimetype="application/javascript")
    resp.headers["Cache-Control"] = "no-cache"
    resp.headers["Service-Worker-Allowed"] = "/"
    return resp


@app.route("/manifest.webmanifest")
def manifest():
    import json
    icons = [{"src": "/static/icons/icon-192.png", "sizes": "192x192", "type": "image/png", "purpose": "any"},
             {"src": "/static/icons/icon-512.png", "sizes": "512x512", "type": "image/png", "purpose": "any"},
             {"src": "/static/icons/icon-512.png", "sizes": "512x512", "type": "image/png", "purpose": "maskable"}]
    body = {"id": "/", "name": COMPANY, "short_name": COMPANY[:12], "description": "Investor profit and accounts portal",
            "start_url": "/", "scope": "/", "display": "standalone", "orientation": "any",
            "background_color": "#f2f4f5", "theme_color": "#0e4a40", "icons": icons}
    resp = Response(json.dumps(body), mimetype="application/manifest+json")
    resp.headers["Cache-Control"] = "no-cache"
    return resp


def login_required(role=None):
    def deco(f):
        @wraps(f)
        def w(*a, **k):
            if not g.user:
                return redirect(url_for("login", next=request.path))
            if role and g.user["role"] != role:
                abort(403)
            return f(*a, **k)
        return w
    return deco


def safe_next(n):
    return n if n and n.startswith("/") and not n.startswith("//") else None


@app.route("/")
def index():
    if not g.user:
        return redirect(url_for("login"))
    return redirect(url_for("admin_dashboard" if g.user["role"] == "admin" else "me_home"))


@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        u = request.form.get("username", "").strip().lower()
        key = (request.remote_addr, u)
        cnt, until = _fails.get(key, (0, None))
        if until and datetime.now() < until:
            flash("Too many failed attempts. Try again in a few minutes.", "error")
            return render_template("login.html"), 429
        row = one("SELECT * FROM users WHERE lower(username)=? AND active=1", (u,))
        if row and check_password_hash(row["password_hash"], request.form.get("password", "")):
            _fails.pop(key, None)
            session.clear()
            session["uid"] = row["id"]
            session.permanent = True
            return redirect(safe_next(request.args.get("next")) or url_for("index"))
        cnt += 1
        _fails[key] = (cnt, datetime.now() + timedelta(minutes=5) if cnt >= 5 else None)
        flash("Wrong username or password.", "error")
    return render_template("login.html")


@app.route("/logout", methods=["POST"])
def logout():
    session.clear()
    return redirect(url_for("login"))


@app.route("/account/password", methods=["GET", "POST"])
@login_required()
def change_password():
    if request.method == "POST":
        f = request.form
        if not check_password_hash(g.user["password_hash"], f.get("current", "")):
            flash("Current password is wrong.", "error")
        elif len(f.get("new", "")) < 8:
            flash("New password must be at least 8 characters.", "error")
        elif f.get("new") != f.get("confirm"):
            flash("New passwords do not match.", "error")
        else:
            ex("UPDATE users SET password_hash=?, must_change=0 WHERE id=?",
               (generate_password_hash(f["new"]), g.user["id"]))
            flash("Password changed.", "ok")
            return redirect(url_for("index"))
    return render_template("change_password.html")


@app.errorhandler(400)
@app.errorhandler(403)
@app.errorhandler(404)
@app.errorhandler(413)
def err(e):
    msg = {400: "Bad request or expired form. Reload the page and try again.",
           403: "You do not have access to this page.",
           404: "Page not found.",
           413: "File too large. Maximum size is 10 MB."}[e.code]
    return render_template("error.html", code=e.code, msg=msg), e.code


# --------------------------------------------------------------------------
# Admin: dashboard
# --------------------------------------------------------------------------
@app.route("/admin")
@login_required("admin")
def admin_dashboard():
    t = date.today()
    ms = t.replace(day=1).isoformat()
    inv = q("SELECT i.principal, p.rate, p.per_amount FROM investors i JOIN profit_plans p ON p.id=i.plan_id WHERE i.status='active'")
    monthly = sum(calc_profit(r["principal"], r["rate"], r["per_amount"], 1) for r in inv)
    s = {
        "investors": len(inv),
        "principal": sum(r["principal"] for r in inv),
        "monthly": monthly,
        "overdue": one("SELECT COUNT(*) c, COALESCE(SUM(amount),0) s FROM payouts WHERE status='pending' AND due_date<?", (t.isoformat(),)),
        "upcoming": one("SELECT COUNT(*) c, COALESCE(SUM(amount),0) s FROM payouts WHERE status='pending' AND due_date BETWEEN ? AND ?",
                        (t.isoformat(), (t + timedelta(days=30)).isoformat())),
        "paid_month": one("SELECT COALESCE(SUM(paid_amount),0) s FROM payouts WHERE status='paid' AND paid_date>=?", (ms,))["s"],
        "income": one("SELECT COALESCE(SUM(amount),0) s FROM transactions WHERE kind='receipt' AND txn_date>=?", (ms,))["s"],
        "expense": one("SELECT COALESCE(SUM(amount),0) s FROM transactions WHERE kind='payment' AND txn_date>=?", (ms,))["s"],
    }
    due = q("""SELECT p.*, i.name, i.code FROM payouts p JOIN investors i ON i.id=p.investor_id
               WHERE p.status='pending' AND p.due_date<=? ORDER BY p.due_date LIMIT 10""",
            ((t + timedelta(days=30)).isoformat(),))
    return render_template("admin/dashboard.html", s=s, due=due)


# --------------------------------------------------------------------------
# Admin: profit plans (master set-up)
# --------------------------------------------------------------------------
@app.route("/admin/plans")
@login_required("admin")
def plans():
    rows = q("""SELECT p.*, (SELECT COUNT(*) FROM investors i WHERE i.plan_id=p.id) used
                FROM profit_plans p ORDER BY active DESC, name""")
    return render_template("admin/plans.html", rows=rows)


def plan_form(plan=None):
    if request.method == "POST":
        f = request.form
        try:
            name = f["name"].strip()
            rate, per = to_paise(f["rate"]), to_paise(f["per_amount"])
            if not name:
                raise ValueError("Plan name is required")
            if rate <= 0 or per <= 0:
                raise ValueError("Profit amount and investment amount must be greater than zero")
            vals = (name, rate, per, 1 if f.get("active") else 0, f.get("notes", "").strip())
            if plan:
                ex("UPDATE profit_plans SET name=?,rate=?,per_amount=?,active=?,notes=? WHERE id=?", vals + (plan["id"],))
                flash("Plan updated. Existing schedule rows keep their old rate until you recalculate them on the investor page.", "ok")
            else:
                ex("INSERT INTO profit_plans(name,rate,per_amount,active,notes) VALUES(?,?,?,?,?)", vals)
                flash("Plan added.", "ok")
            return redirect(url_for("plans"))
        except sqlite3.IntegrityError:
            flash("A plan with this name already exists.", "error")
        except ValueError as e:
            flash(str(e), "error")
    return render_template("admin/plan_form.html", plan=plan)


@app.route("/admin/plans/new", methods=["GET", "POST"])
@login_required("admin")
def plan_new():
    return plan_form()


@app.route("/admin/plans/<int:pid>/edit", methods=["GET", "POST"])
@login_required("admin")
def plan_edit(pid):
    plan = one("SELECT * FROM profit_plans WHERE id=?", (pid,)) or abort(404)
    return plan_form(plan)


# --------------------------------------------------------------------------
# Admin: investors
# --------------------------------------------------------------------------
@app.route("/admin/investors")
@login_required("admin")
def investors():
    term = request.args.get("q", "").strip()
    status = request.args.get("status", "active")
    sql = """SELECT i.*, p.rate, p.per_amount, p.name plan_name,
               (SELECT COALESCE(SUM(paid_amount),0) FROM payouts x WHERE x.investor_id=i.id AND x.status='paid') received,
               (SELECT COUNT(*) FROM investor_documents d WHERE d.investor_id=i.id) docs
             FROM investors i JOIN profit_plans p ON p.id=i.plan_id WHERE 1=1"""
    args = []
    if status in ("active", "inactive", "closed"):
        sql += " AND i.status=?"
        args.append(status)
    if term:
        sql += " AND (i.name LIKE ? OR i.code LIKE ? OR i.phone LIKE ?)"
        args += [f"%{term}%"] * 3
    rows = q(sql + " ORDER BY i.name", args)
    return render_template("admin/investors.html", rows=rows, term=term, status=status)


def save_document(inv_id, file, label):
    name = file.filename or ""
    ext = name.rsplit(".", 1)[-1].lower() if "." in name else ""
    if ext not in ALLOWED_EXT:
        raise ValueError("Attach a PDF, JPG or PNG file")
    head = file.stream.read(8)
    file.stream.seek(0)
    ok = ((ext == "pdf" and head.startswith(b"%PDF")) or
          (ext in ("jpg", "jpeg") and head.startswith(b"\xff\xd8")) or
          (ext == "png" and head.startswith(b"\x89PNG")))
    if not ok:
        raise ValueError("File content does not match its type")
    stored = uuid.uuid4().hex + "." + ext
    file.save(UPLOADS / stored)
    ex("INSERT INTO investor_documents(investor_id,label,stored_name,original_name,uploaded_at) VALUES(?,?,?,?,?)",
       (inv_id, label or "Agreement", stored, os.path.basename(name)[:150], datetime.now().isoformat(timespec="seconds")))


def read_investor_form(f):
    d = {k: f.get(k, "").strip() for k in
         ("code", "name", "phone", "email", "address", "pan", "bank_name", "bank_account", "bank_ifsc", "nominee", "notes")}
    if not d["name"]:
        raise ValueError("Investor name is required")
    d["principal"] = to_paise(f.get("principal", ""))
    if d["principal"] <= 0:
        raise ValueError("Invested amount must be greater than zero")
    d["start_date"] = parse_date(f.get("start_date")).isoformat()
    try:
        d["plan_id"] = int(f.get("plan_id", ""))
    except ValueError:
        raise ValueError("Choose a profit plan")
    if not one("SELECT 1 FROM profit_plans WHERE id=?", (d["plan_id"],)):
        raise ValueError("Choose a profit plan")
    d["status"] = f.get("status", "active")
    if d["status"] not in ("active", "inactive", "closed"):
        d["status"] = "active"
    if d["pan"]:
        d["pan"] = d["pan"].upper()
    return d


def next_code():
    r = one("SELECT COALESCE(MAX(id),0)+1 n FROM investors")["n"]
    return f"INV-{r:04d}"


@app.route("/admin/investors/new", methods=["GET", "POST"])
@login_required("admin")
def investor_new():
    plans_ = q("SELECT * FROM profit_plans WHERE active=1 ORDER BY name")
    if request.method == "POST":
        f = request.form
        try:
            d = read_investor_form(f)
            d["code"] = d["code"] or next_code()
            username = (f.get("username", "").strip() or d["code"]).lower()
            pw = f.get("password", "")
            if pw and len(pw) < 8:
                raise ValueError("Password must be at least 8 characters")
            generated = not pw
            pw = pw or secrets.token_urlsafe(8)
            file = request.files.get("agreement")
            if file and file.filename:
                ext = file.filename.rsplit(".", 1)[-1].lower() if "." in file.filename else ""
                if ext not in ALLOWED_EXT:
                    raise ValueError("Attach a PDF, JPG or PNG file")
            now = datetime.now().isoformat(timespec="seconds")
            cols = ",".join(d)
            cur = ex(f"INSERT INTO investors({cols},created_at) VALUES({','.join('?' * len(d))},?)",
                     list(d.values()) + [now], commit=False)
            iid = cur.lastrowid
            ex("INSERT INTO users(username,password_hash,role,investor_id,must_change,created_at) VALUES(?,?,?,?,?,?)",
               (username, generate_password_hash(pw), "investor", iid, 1, now), commit=False)
            if file and file.filename:
                save_document(iid, file, f.get("doc_label", "Agreement"))
            db().commit()
            flash(f"Investor created. Login: {username} / {pw}. Share it with the investor now; it will not be shown again."
                  if generated else f"Investor created. Login username: {username}", "ok")
            return redirect(url_for("investor_detail", iid=iid))
        except sqlite3.IntegrityError:
            db().rollback()
            flash("Investor code or login username already exists.", "error")
        except ValueError as e:
            db().rollback()
            flash(str(e), "error")
    return render_template("admin/investor_form.html", inv=None, plans=plans_, form=request.form, code=next_code())


@app.route("/admin/investors/<int:iid>/edit", methods=["GET", "POST"])
@login_required("admin")
def investor_edit(iid):
    inv = one("SELECT * FROM investors WHERE id=?", (iid,)) or abort(404)
    plans_ = q("SELECT * FROM profit_plans WHERE active=1 OR id=? ORDER BY name", (inv["plan_id"],))
    if request.method == "POST":
        try:
            d = read_investor_form(request.form)
            d["code"] = d["code"] or inv["code"]
            sets = ",".join(f"{k}=?" for k in d)
            ex(f"UPDATE investors SET {sets} WHERE id=?", list(d.values()) + [iid])
            flash("Profile updated. Pending schedule rows keep their earlier figures. Use 'Recalculate pending' if the amount or plan changed.", "ok")
            return redirect(url_for("investor_detail", iid=iid))
        except sqlite3.IntegrityError:
            flash("Investor code already exists.", "error")
        except ValueError as e:
            flash(str(e), "error")
    return render_template("admin/investor_form.html", inv=inv, plans=plans_, form=request.form, code=inv["code"])


@app.route("/admin/investors/<int:iid>")
@login_required("admin")
def investor_detail(iid):
    inv = one("""SELECT i.*, p.rate, p.per_amount, p.name plan_name FROM investors i
                 JOIN profit_plans p ON p.id=i.plan_id WHERE i.id=?""", (iid,)) or abort(404)
    docs = q("SELECT * FROM investor_documents WHERE investor_id=? ORDER BY id DESC", (iid,))
    pays = q("SELECT * FROM payouts WHERE investor_id=? ORDER BY period_from", (iid,))
    user = one("SELECT * FROM users WHERE investor_id=?", (iid,))
    last = one("SELECT MAX(period_to) m FROM payouts WHERE investor_id=?", (iid,))["m"]
    nxt = (date.fromisoformat(last) + timedelta(1)).isoformat() if last else inv["start_date"]
    paid = sum(p["paid_amount"] for p in pays if p["status"] == "paid")
    pend = sum(p["amount"] for p in pays if p["status"] == "pending")
    return render_template("admin/investor_detail.html", inv=inv, docs=docs, pays=pays, user=user,
                           next_from=nxt, paid=paid, pend=pend,
                           monthly=calc_profit(inv["principal"], inv["rate"], inv["per_amount"], 1))


@app.route("/admin/investors/<int:iid>/docs", methods=["POST"])
@login_required("admin")
def investor_docs_add(iid):
    one("SELECT 1 FROM investors WHERE id=?", (iid,)) or abort(404)
    file = request.files.get("file")
    try:
        if not file or not file.filename:
            raise ValueError("Choose a file to attach")
        save_document(iid, file, request.form.get("label", "").strip())
        flash("Document attached.", "ok")
    except ValueError as e:
        flash(str(e), "error")
    return redirect(url_for("investor_detail", iid=iid))


@app.route("/admin/docs/<int:did>/delete", methods=["POST"])
@login_required("admin")
def doc_delete(did):
    d = one("SELECT * FROM investor_documents WHERE id=?", (did,)) or abort(404)
    ex("DELETE FROM investor_documents WHERE id=?", (did,))
    try:
        (UPLOADS / d["stored_name"]).unlink()
    except OSError:
        pass
    flash("Document removed.", "ok")
    return redirect(url_for("investor_detail", iid=d["investor_id"]))


@app.route("/docs/<int:did>")
@login_required()
def doc_download(did):
    d = one("SELECT * FROM investor_documents WHERE id=?", (did,)) or abort(404)
    if g.user["role"] != "admin" and g.user["investor_id"] != d["investor_id"]:
        abort(404)
    return send_from_directory(UPLOADS, d["stored_name"], download_name=d["original_name"])


@app.route("/admin/investors/<int:iid>/reset-password", methods=["POST"])
@login_required("admin")
def investor_reset_pw(iid):
    user = one("SELECT * FROM users WHERE investor_id=?", (iid,)) or abort(404)
    pw = request.form.get("password", "").strip()
    if pw and len(pw) < 8:
        flash("Password must be at least 8 characters.", "error")
    else:
        gen = not pw
        pw = pw or secrets.token_urlsafe(8)
        ex("UPDATE users SET password_hash=?, must_change=1, active=1 WHERE id=?",
           (generate_password_hash(pw), user["id"]))
        flash(f"Password reset. New password: {pw}. The investor must change it at next login." if gen
              else "Password reset. The investor must change it at next login.", "ok")
    return redirect(url_for("investor_detail", iid=iid))


# ---------------- Profit schedule generation ----------------
def insert_payout(inv, plan, a, b, due):
    months = months_between(a, b + timedelta(1))
    ex("""INSERT INTO payouts(investor_id,period_from,period_to,due_date,principal,rate,per_amount,months,amount)
          VALUES(?,?,?,?,?,?,?,?,?)""",
       (inv["id"], a.isoformat(), b.isoformat(), due.isoformat(), inv["principal"], plan["rate"],
        plan["per_amount"], str(months), calc_profit(inv["principal"], plan["rate"], plan["per_amount"], months)),
       commit=False)


@app.route("/admin/investors/<int:iid>/schedule", methods=["POST"])
@login_required("admin")
def investor_schedule(iid):
    inv = one("SELECT * FROM investors WHERE id=?", (iid,)) or abort(404)
    plan = one("SELECT * FROM profit_plans WHERE id=?", (inv["plan_id"],))
    f = request.form
    try:
        if inv["status"] != "active":
            raise ValueError("Only active investors can have a schedule generated")
        mode = f.get("mode")
        if f.get("replace") == "1":
            ex("DELETE FROM payouts WHERE investor_id=? AND status='pending'", (iid,), commit=False)
        last = one("SELECT MAX(period_to) m FROM payouts WHERE investor_id=?", (iid,))["m"]
        cursor = date.fromisoformat(last) + timedelta(1) if last else date.fromisoformat(inv["start_date"])
        rows = []
        if mode == "custom":
            ds = sorted({parse_date(x) for x in re.split(r"[,\s;]+", f.get("dates", "").strip()) if x})
            if not ds:
                raise ValueError("Enter at least one payout date")
            prev = cursor
            for d in ds:
                if d < prev:
                    raise ValueError(f"{dfmt(d.isoformat())} falls before {dfmt(prev.isoformat())}, the day after the last scheduled period")
                rows.append((prev, d, d))
                prev = d + timedelta(1)
        elif mode in ("monthly", "every_n"):
            n = 1 if mode == "monthly" else int(f.get("n", "0") or 0)
            count = int(f.get("count", "0") or 0)
            offset = int(f.get("offset", "0") or 0)
            if not 1 <= n <= 60 or not 1 <= count <= 120 or not 0 <= offset <= 60:
                raise ValueError("Check the interval, number of payouts and due-day offset")
            for k in range(count):
                a = add_months(cursor, k * n)
                b = add_months(cursor, (k + 1) * n) - timedelta(1)
                rows.append((a, b, b + timedelta(offset)))
        else:
            raise ValueError("Choose a schedule type")
        for a, b, due in rows:
            insert_payout(inv, plan, a, b, due)
        db().commit()
        flash(f"{len(rows)} payout(s) scheduled.", "ok")
    except ValueError as e:
        db().rollback()
        flash(str(e), "error")
    return redirect(url_for("investor_detail", iid=iid))


@app.route("/admin/investors/<int:iid>/recalc", methods=["POST"])
@login_required("admin")
def investor_recalc(iid):
    inv = one("SELECT * FROM investors WHERE id=?", (iid,)) or abort(404)
    plan = one("SELECT * FROM profit_plans WHERE id=?", (inv["plan_id"],))
    n = 0
    for p in q("SELECT * FROM payouts WHERE investor_id=? AND status='pending'", (iid,)):
        amt = calc_profit(inv["principal"], plan["rate"], plan["per_amount"], p["months"])
        ex("UPDATE payouts SET principal=?,rate=?,per_amount=?,amount=? WHERE id=?",
           (inv["principal"], plan["rate"], plan["per_amount"], amt, p["id"]), commit=False)
        n += 1
    db().commit()
    flash(f"{n} pending payout(s) recalculated with the current amount and plan.", "ok")
    return redirect(url_for("investor_detail", iid=iid))


# --------------------------------------------------------------------------
# Admin: payouts
# --------------------------------------------------------------------------
@app.route("/admin/payouts")
@login_required("admin")
def payouts():
    status = request.args.get("status", "pending")
    inv_id = request.args.get("investor_id", "")
    dfrom, dto = request.args.get("from", ""), request.args.get("to", "")
    sql = "SELECT p.*, i.name, i.code FROM payouts p JOIN investors i ON i.id=p.investor_id WHERE 1=1"
    args = []
    if status == "overdue":
        sql += " AND p.status='pending' AND p.due_date<?"
        args.append(date.today().isoformat())
    elif status in ("pending", "paid"):
        sql += " AND p.status=?"
        args.append(status)
    if inv_id.isdigit():
        sql += " AND p.investor_id=?"
        args.append(int(inv_id))
    if dfrom:
        sql += " AND p.due_date>=?"
        args.append(dfrom)
    if dto:
        sql += " AND p.due_date<=?"
        args.append(dto)
    rows = q(sql + " ORDER BY p.due_date, i.name LIMIT 500", args)
    return render_template("admin/payouts.html", rows=rows, status=status, inv_id=inv_id, dfrom=dfrom, dto=dto,
                           investors=q("SELECT id,code,name FROM investors ORDER BY name"),
                           total=sum(r["amount"] for r in rows))


@app.route("/admin/payouts/<int:pid>")
@login_required("admin")
def payout_view(pid):
    p = one("""SELECT p.*, i.name, i.code, i.bank_name, i.bank_account, i.bank_ifsc
               FROM payouts p JOIN investors i ON i.id=p.investor_id WHERE p.id=?""", (pid,)) or abort(404)
    return render_template("admin/payout.html", p=p)


def mark_paid(pid, paid_date, amount, mode, ref, remarks, post):
    p = one("SELECT p.*, i.name FROM payouts p JOIN investors i ON i.id=p.investor_id WHERE p.id=?", (pid,))
    if not p or p["status"] != "pending":
        raise ValueError("Payout is not pending")
    txn_id = None
    if post:
        head = one("SELECT id FROM heads WHERE name=?", (PAYOUT_HEAD,))
        cur = ex("""INSERT INTO transactions(txn_date,kind,head_id,amount,mode,party,reference,narration,created_at)
                    VALUES(?,?,?,?,?,?,?,?,?)""",
                 (paid_date, "payment", head["id"], amount, mode, p["name"], ref,
                  f"Profit {dfmt(p['period_from'])} to {dfmt(p['period_to'])}", datetime.now().isoformat(timespec="seconds")),
                 commit=False)
        txn_id = cur.lastrowid
        ex("UPDATE transactions SET voucher_no=? WHERE id=?", (f"PAY-{txn_id:05d}", txn_id), commit=False)
    ex("""UPDATE payouts SET status='paid',paid_date=?,paid_amount=?,mode=?,reference=?,remarks=?,txn_id=? WHERE id=?""",
       (paid_date, amount, mode, ref, remarks, txn_id, pid), commit=False)


@app.route("/admin/payouts/<int:pid>/pay", methods=["POST"])
@login_required("admin")
def payout_pay(pid):
    f = request.form
    try:
        pd = parse_date(f.get("paid_date"))
        if pd > date.today():
            raise ValueError("Paid date cannot be in the future")
        amt = to_paise(f.get("paid_amount", ""))
        if amt <= 0:
            raise ValueError("Paid amount must be greater than zero")
        mark_paid(pid, pd.isoformat(), amt, f.get("mode") if f.get("mode") in MODES else "Bank Transfer",
                  f.get("reference", "").strip(), f.get("remarks", "").strip(), f.get("post") == "1")
        db().commit()
        flash("Marked as disbursed.", "ok")
    except ValueError as e:
        db().rollback()
        flash(str(e), "error")
    return redirect(url_for("payout_view", pid=pid))


@app.route("/admin/payouts/bulk-pay", methods=["POST"])
@login_required("admin")
def payouts_bulk_pay():
    f = request.form
    ids = [int(x) for x in f.getlist("ids") if x.isdigit()]
    try:
        if not ids:
            raise ValueError("Select at least one payout")
        pd = parse_date(f.get("paid_date"))
        if pd > date.today():
            raise ValueError("Paid date cannot be in the future")
        mode = f.get("mode") if f.get("mode") in MODES else "Bank Transfer"
        for pid in ids:
            amt = one("SELECT amount FROM payouts WHERE id=?", (pid,))
            if amt:
                mark_paid(pid, pd.isoformat(), amt["amount"], mode, f.get("reference", "").strip(), "", f.get("post") == "1")
        db().commit()
        flash(f"{len(ids)} payout(s) marked as disbursed.", "ok")
    except ValueError as e:
        db().rollback()
        flash(str(e), "error")
    return redirect(request.referrer or url_for("payouts"))


@app.route("/admin/payouts/<int:pid>/unpay", methods=["POST"])
@login_required("admin")
def payout_unpay(pid):
    p = one("SELECT * FROM payouts WHERE id=? AND status='paid'", (pid,)) or abort(404)
    ex("UPDATE payouts SET status='pending',paid_date=NULL,paid_amount=NULL,mode=NULL,reference=NULL,remarks=NULL,txn_id=NULL WHERE id=?",
       (pid,), commit=False)
    if p["txn_id"]:
        ex("DELETE FROM transactions WHERE id=?", (p["txn_id"],), commit=False)
    db().commit()
    flash("Disbursement reversed. The linked payment entry was removed from accounts.", "ok")
    return redirect(url_for("payout_view", pid=pid))


@app.route("/admin/payouts/<int:pid>/edit", methods=["POST"])
@login_required("admin")
def payout_edit(pid):
    one("SELECT 1 FROM payouts WHERE id=? AND status='pending'", (pid,)) or abort(404)
    try:
        due = parse_date(request.form.get("due_date")).isoformat()
        amt = to_paise(request.form.get("amount", ""))
        if amt <= 0:
            raise ValueError("Amount must be greater than zero")
        ex("UPDATE payouts SET due_date=?, amount=? WHERE id=?", (due, amt, pid))
        flash("Payout updated.", "ok")
    except ValueError as e:
        flash(str(e), "error")
    return redirect(url_for("payout_view", pid=pid))


@app.route("/admin/payouts/<int:pid>/delete", methods=["POST"])
@login_required("admin")
def payout_delete(pid):
    p = one("SELECT * FROM payouts WHERE id=? AND status='pending'", (pid,)) or abort(404)
    ex("DELETE FROM payouts WHERE id=?", (pid,))
    flash("Pending payout deleted.", "ok")
    return redirect(url_for("investor_detail", iid=p["investor_id"]))


# --------------------------------------------------------------------------
# Admin: accounts
# --------------------------------------------------------------------------
@app.route("/admin/heads", methods=["GET", "POST"])
@login_required("admin")
def heads():
    if request.method == "POST":
        name, typ = request.form.get("name", "").strip(), request.form.get("type")
        if not name or typ not in ("income", "expense"):
            flash("Enter a head name and choose income or expense.", "error")
        else:
            try:
                ex("INSERT INTO heads(name,type) VALUES(?,?)", (name, typ))
                flash("Head added.", "ok")
            except sqlite3.IntegrityError:
                flash("A head with this name already exists.", "error")
        return redirect(url_for("heads"))
    rows = q("""SELECT h.*, (SELECT COUNT(*) FROM transactions t WHERE t.head_id=h.id) used
                FROM heads h ORDER BY type DESC, name""")
    return render_template("admin/heads.html", rows=rows)


@app.route("/admin/heads/<int:hid>/toggle", methods=["POST"])
@login_required("admin")
def head_toggle(hid):
    h = one("SELECT * FROM heads WHERE id=?", (hid,)) or abort(404)
    if h["system"]:
        flash("This head is used for investor payouts and cannot be switched off.", "error")
    else:
        ex("UPDATE heads SET active=1-active WHERE id=?", (hid,))
    return redirect(url_for("heads"))


@app.route("/admin/transactions")
@login_required("admin")
def transactions():
    a = request.args
    dfrom = a.get("from") or fy_start().isoformat()
    dto = a.get("to") or date.today().isoformat()
    sql = """SELECT t.*, h.name head FROM transactions t JOIN heads h ON h.id=t.head_id
             WHERE t.txn_date BETWEEN ? AND ?"""
    args = [dfrom, dto]
    for col, key in (("t.kind", "kind"), ("t.head_id", "head_id"), ("t.mode", "mode")):
        if a.get(key):
            sql += f" AND {col}=?"
            args.append(a[key])
    rows = q(sql + " ORDER BY t.txn_date DESC, t.id DESC LIMIT 500", args)
    rec = sum(r["amount"] for r in rows if r["kind"] == "receipt")
    pay = sum(r["amount"] for r in rows if r["kind"] == "payment")
    return render_template("admin/transactions.html", rows=rows, dfrom=dfrom, dto=dto, a=a, rec=rec, pay=pay,
                           heads=q("SELECT * FROM heads ORDER BY type DESC, name"))


def txn_form(txn=None):
    heads_ = q("SELECT * FROM heads WHERE active=1 OR id=? ORDER BY type DESC, name", (txn["head_id"] if txn else 0,))
    if request.method == "POST":
        f = request.form
        try:
            kind = f.get("kind")
            if kind not in ("receipt", "payment"):
                raise ValueError("Choose receipt or payment")
            h = one("SELECT * FROM heads WHERE id=?", (f.get("head_id", "0"),))
            if not h or h["type"] != ("income" if kind == "receipt" else "expense"):
                raise ValueError("Receipts need an income head and payments need an expense head")
            d = parse_date(f.get("txn_date")).isoformat()
            amt = to_paise(f.get("amount", ""))
            if amt <= 0:
                raise ValueError("Amount must be greater than zero")
            mode = f.get("mode") if f.get("mode") in MODES else "Cash"
            vals = (d, kind, h["id"], amt, mode, f.get("party", "").strip(), f.get("reference", "").strip(),
                    f.get("narration", "").strip())
            if txn:
                ex("UPDATE transactions SET txn_date=?,kind=?,head_id=?,amount=?,mode=?,party=?,reference=?,narration=? WHERE id=?",
                   vals + (txn["id"],))
            else:
                cur = ex("""INSERT INTO transactions(txn_date,kind,head_id,amount,mode,party,reference,narration,created_at)
                            VALUES(?,?,?,?,?,?,?,?,?)""", vals + (datetime.now().isoformat(timespec="seconds"),), commit=False)
                ex("UPDATE transactions SET voucher_no=? WHERE id=?",
                   (("RCP-" if kind == "receipt" else "PAY-") + f"{cur.lastrowid:05d}", cur.lastrowid))
            flash("Entry saved.", "ok")
            return redirect(url_for("transactions"))
        except ValueError as e:
            flash(str(e), "error")
    return render_template("admin/transaction_form.html", t=txn, heads=heads_, form=request.form)


@app.route("/admin/transactions/new", methods=["GET", "POST"])
@login_required("admin")
def txn_new():
    return txn_form()


@app.route("/admin/transactions/<int:tid>/edit", methods=["GET", "POST"])
@login_required("admin")
def txn_edit(tid):
    t = one("SELECT * FROM transactions WHERE id=?", (tid,)) or abort(404)
    if one("SELECT 1 FROM payouts WHERE txn_id=?", (tid,)):
        flash("This entry was created by an investor payout. Reverse the payout to change it.", "error")
        return redirect(url_for("transactions"))
    return txn_form(t)


@app.route("/admin/transactions/<int:tid>/delete", methods=["POST"])
@login_required("admin")
def txn_delete(tid):
    one("SELECT 1 FROM transactions WHERE id=?", (tid,)) or abort(404)
    if one("SELECT 1 FROM payouts WHERE txn_id=?", (tid,)):
        flash("This entry was created by an investor payout. Reverse the payout to remove it.", "error")
    else:
        ex("DELETE FROM transactions WHERE id=?", (tid,))
        flash("Entry deleted.", "ok")
    return redirect(url_for("transactions"))


# ---------------- Reports ----------------
REPORTS = {"income-expense": "Income & expense statement", "daybook": "Day book / cash book",
           "payouts": "Investor payout summary"}


@app.route("/admin/reports")
@login_required("admin")
def reports():
    return render_template("admin/reports.html", reports=REPORTS)


@app.route("/admin/reports/<kind>")
@login_required("admin")
def report(kind):
    if kind not in REPORTS:
        abort(404)
    a = request.args
    try:
        dfrom = parse_date(a.get("from") or fy_start().isoformat()).isoformat()
        dto = parse_date(a.get("to") or date.today().isoformat()).isoformat()
    except ValueError:
        abort(400)
    cols, rows, foot, note = [], [], None, ""
    if kind == "income-expense":
        data = q("""SELECT h.name, h.type, SUM(t.amount) total, COUNT(*) n FROM transactions t
                    JOIN heads h ON h.id=t.head_id WHERE t.txn_date BETWEEN ? AND ?
                    GROUP BY h.id ORDER BY h.type='income' DESC, total DESC""", (dfrom, dto))
        cols = ["Head", "Type", "Entries", "Amount"]
        inc = sum(r["total"] for r in data if r["type"] == "income")
        exp = sum(r["total"] for r in data if r["type"] == "expense")
        rows = [[r["name"], r["type"].title(), r["n"], Money(r["total"])] for r in data]
        rows += [["Total income", "", "", Money(inc)], ["Total expense", "", "", Money(exp)],
                 ["Surplus / (deficit)", "", "", Money(inc - exp)]]
        foot = 3
        note = "Investor profit payouts appear as an expense when you tick 'Post to accounts' while marking them disbursed."
    elif kind == "daybook":
        where, args = "", []
        if a.get("head_id"):
            where += " AND t.head_id=?"
            args.append(a["head_id"])
        if a.get("mode"):
            where += " AND t.mode=?"
            args.append(a["mode"])
        op = one("SELECT COALESCE(SUM(CASE t.kind WHEN 'receipt' THEN amount ELSE -amount END),0) v FROM transactions t "
                 "WHERE t.txn_date<?" + where, [dfrom] + args)["v"]
        data = q("""SELECT t.*, h.name head FROM transactions t JOIN heads h ON h.id=t.head_id
                    WHERE t.txn_date BETWEEN ? AND ?""" + where + " ORDER BY t.txn_date, t.id", [dfrom, dto] + args)
        cols = ["Date", "Voucher", "Head", "Party", "Narration", "Mode", "Receipt", "Payment", "Balance"]
        bal = op
        rows = [["Opening balance", "", "", "", "", "", "", "", Money(bal)]]
        for r in data:
            bal += r["amount"] if r["kind"] == "receipt" else -r["amount"]
            rows.append([dfmt(r["txn_date"]), r["voucher_no"] or "", r["head"], r["party"] or "", r["narration"] or "",
                         r["mode"], Money(r["amount"]) if r["kind"] == "receipt" else "",
                         Money(r["amount"]) if r["kind"] == "payment" else "", Money(bal)])
        rows.append(["Closing balance", "", "", "", "", "",
                     Money(sum(r["amount"] for r in data if r["kind"] == "receipt")),
                     Money(sum(r["amount"] for r in data if r["kind"] == "payment")), Money(bal)])
        foot = 1
        note = "Filter by head to get a head ledger, or by mode (Cash, Bank Transfer...) to get a cash book or bank book."
    else:
        data = q("""SELECT i.code, i.name, COUNT(*) n, SUM(p.amount) due,
                      COALESCE(SUM(CASE WHEN p.status='paid' THEN p.paid_amount END),0) paid,
                      COALESCE(SUM(CASE WHEN p.status='pending' THEN p.amount END),0) pending
                    FROM payouts p JOIN investors i ON i.id=p.investor_id
                    WHERE p.due_date BETWEEN ? AND ? GROUP BY i.id ORDER BY i.name""", (dfrom, dto))
        cols = ["Code", "Investor", "Payouts", "Scheduled", "Disbursed", "Pending"]
        rows = [[r["code"], r["name"], r["n"], Money(r["due"]), Money(r["paid"]), Money(r["pending"])] for r in data]
        rows.append(["Total", "", sum(r["n"] for r in data), Money(sum(r["due"] for r in data)),
                     Money(sum(r["paid"] for r in data)), Money(sum(r["pending"] for r in data))])
        foot = 1
        note = "Payouts are counted by due date."
    if a.get("format") == "csv":
        return csv_response(f"{kind}_{dfrom}_{dto}", cols, rows)
    return render_template("admin/report.html", kind=kind, title=REPORTS[kind], cols=cols, rows=rows, foot=foot,
                           note=note, dfrom=dfrom, dto=dto, a=a, heads=q("SELECT * FROM heads ORDER BY type DESC, name"))


# --------------------------------------------------------------------------
# Investor portal (own data only)
# --------------------------------------------------------------------------
def me():
    if not g.user["investor_id"]:
        abort(403)
    return one("""SELECT i.*, p.rate, p.per_amount, p.name plan_name FROM investors i
                  JOIN profit_plans p ON p.id=i.plan_id WHERE i.id=?""", (g.user["investor_id"],)) or abort(404)


@app.route("/me")
@login_required("investor")
def me_home():
    inv = me()
    iid = inv["id"]
    recv = one("SELECT COALESCE(SUM(paid_amount),0) s, COUNT(*) c FROM payouts WHERE investor_id=? AND status='paid'", (iid,))
    nxt = one("SELECT * FROM payouts WHERE investor_id=? AND status='pending' ORDER BY due_date LIMIT 1", (iid,))
    pend = one("SELECT COALESCE(SUM(amount),0) s, COUNT(*) c FROM payouts WHERE investor_id=? AND status='pending'", (iid,))
    recent = q("SELECT * FROM payouts WHERE investor_id=? AND status='paid' ORDER BY paid_date DESC, id DESC LIMIT 5", (iid,))
    return render_template("investor/home.html", inv=inv, recv=recv, nxt=nxt, pend=pend, recent=recent,
                           monthly=calc_profit(inv["principal"], inv["rate"], inv["per_amount"], 1))


@app.route("/me/profile")
@login_required("investor")
def me_profile():
    inv = me()
    docs = q("SELECT * FROM investor_documents WHERE investor_id=? ORDER BY id DESC", (inv["id"],))
    return render_template("investor/profile.html", inv=inv, docs=docs)


@app.route("/me/schedule")
@login_required("investor")
def me_schedule():
    inv = me()
    rows = q("SELECT * FROM payouts WHERE investor_id=? AND status='pending' ORDER BY due_date", (inv["id"],))
    return render_template("investor/schedule.html", inv=inv, rows=rows, today=date.today().isoformat(),
                           total=sum(r["amount"] for r in rows))


@app.route("/me/history")
@login_required("investor")
def me_history():
    inv = me()
    rows = q("SELECT * FROM payouts WHERE investor_id=? AND status='paid' ORDER BY paid_date DESC, id DESC", (inv["id"],))
    if request.args.get("format") == "csv":
        return csv_response("my_received_profit", ["Period from", "Period to", "Paid on", "Amount", "Mode", "Reference"],
                            [[r["period_from"], r["period_to"], r["paid_date"], Money(r["paid_amount"]), r["mode"],
                              r["reference"] or ""] for r in rows])
    return render_template("investor/history.html", inv=inv, rows=rows, total=sum(r["paid_amount"] for r in rows))


init_db()

if __name__ == "__main__":
    app.run(host=os.environ.get("HOST", "127.0.0.1"), port=int(os.environ.get("PORT", "5000")), debug=False)
