"""
Savetrack — Savings & Dues Collection Platform
For Co-operative Societies, Social Associations & Community Development Associations.

RECORD-KEEPING ONLY: this app never handles or moves money. Members pay directly
into their organisation's designated bank account and record the transfer here;
an admin verifies and approves before it reflects in the member's ledger.
"""

import os
import sqlite3
from datetime import datetime
from functools import wraps

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

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.environ.get("SAVETRACK_DB", os.path.join(BASE_DIR, "data.db"))

app = Flask(__name__)
app.secret_key = os.environ.get("SAVETRACK_SECRET", "change-this-secret-key-in-production")


@app.template_filter("number_format")
def number_format(value):
    """Format a number as 12,345.00 (thousands separators, 2 decimals)."""
    try:
        return f"{float(value):,.2f}"
    except (TypeError, ValueError):
        return str(value)


# ---------------------------------------------------------------- database --
def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA foreign_keys = ON")
    return g.db


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


def init_db():
    db = sqlite3.connect(DB_PATH)
    db.executescript(
        """
        CREATE TABLE IF NOT EXISTS organisations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            org_type TEXT NOT NULL,
            bank_name TEXT DEFAULT '',
            account_number TEXT DEFAULT '',
            account_name TEXT DEFAULT '',
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            org_id INTEGER NOT NULL REFERENCES organisations(id),
            full_name TEXT NOT NULL,
            phone TEXT DEFAULT '',
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL CHECK (role IN ('admin','member')),
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS dues (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            org_id INTEGER NOT NULL REFERENCES organisations(id),
            member_id INTEGER NOT NULL REFERENCES users(id),
            title TEXT NOT NULL,
            amount REAL NOT NULL,
            due_date TEXT DEFAULT '',
            status TEXT NOT NULL DEFAULT 'open',
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS payments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            org_id INTEGER NOT NULL REFERENCES organisations(id),
            member_id INTEGER NOT NULL REFERENCES users(id),
            amount REAL NOT NULL,
            payment_date TEXT NOT NULL,
            reference TEXT DEFAULT '',
            note TEXT DEFAULT '',
            status TEXT NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending','approved','rejected')),
            decided_by INTEGER REFERENCES users(id),
            decided_at TEXT,
            decided_note TEXT DEFAULT '',
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            org_id INTEGER NOT NULL,
            actor TEXT NOT NULL,
            action TEXT NOT NULL,
            detail TEXT DEFAULT '',
            created_at TEXT NOT NULL
        );
        """
    )
    # seed demo data once
    if db.execute("SELECT COUNT(*) FROM organisations").fetchone()[0] == 0:
        now = datetime.utcnow().isoformat()
        db.execute(
            "INSERT INTO organisations (name, org_type, bank_name, account_number, account_name, created_at)"
            " VALUES (?,?,?,?,?,?)",
            ("Sunrise Co-operative Society", "Co-operative Society",
             "First Bank of Nigeria", "2034567890", "Sunrise Co-operative Society Savings", now),
        )
        db.execute(
            "INSERT INTO users (org_id, full_name, phone, email, password_hash, role, created_at)"
            " VALUES (1, 'Admin User', '08000000001', 'admin@demo.com', ?, 'admin', ?)",
            (generate_password_hash("admin123"), now),
        )
        db.execute(
            "INSERT INTO users (org_id, full_name, phone, email, password_hash, role, created_at)"
            " VALUES (1, 'Ngozi Okafor', '08000000002', 'member@demo.com', ?, 'member', ?)",
            (generate_password_hash("member123"), now),
        )
        db.execute(
            "INSERT INTO users (org_id, full_name, phone, email, password_hash, role, created_at)"
            " VALUES (1, 'Tunde Bello', '08000000003', 'tunde@demo.com', ?, 'member', ?)",
            (generate_password_hash("member123"), now),
        )
        db.executemany(
            "INSERT INTO dues (org_id, member_id, title, amount, due_date, status, created_at)"
            " VALUES (1, ?, ?, ?, ?, 'open', ?)",
            [
                (2, "Monthly Savings – October", 10000, "2026-10-31", now),
                (2, "Development Levy", 5000, "2026-11-15", now),
                (3, "Monthly Savings – October", 10000, "2026-10-31", now),
            ],
        )
        db.execute(
            "INSERT INTO payments (org_id, member_id, amount, payment_date, reference, note,"
            " status, created_at) VALUES (1, 2, 10000, '2026-09-30', 'TRF-9021',"
            " 'September savings transfer', 'approved', ?)",
            (now,),
        )
        db.execute(
            "INSERT INTO payments (org_id, member_id, amount, payment_date, reference, note,"
            " status, created_at) VALUES (1, 2, 10000, '2026-10-25', 'TRF-1044',"
            " 'October savings transfer', 'pending', ?)",
            (now,),
        )
        db.execute(
            "INSERT INTO payments (org_id, member_id, amount, payment_date, reference, note,"
            " status, created_at) VALUES (1, 3, 4000, '2026-10-20', 'TRF-1031',"
            " 'Part payment – October', 'pending', ?)",
            (now,),
        )
        db.execute(
            "INSERT INTO audit_log (org_id, actor, action, detail, created_at)"
            " VALUES (1, 'System', 'seed', 'Demo organisation seeded', ?)",
            (now,),
        )
    db.commit()
    db.close()


def audit(action, detail=""):
    db = get_db()
    db.execute(
        "INSERT INTO audit_log (org_id, actor, action, detail, created_at) VALUES (?,?,?,?,?)",
        (session["org_id"], session.get("full_name", "?"), action, detail,
         datetime.utcnow().isoformat()),
    )
    db.commit()


# ------------------------------------------------------------------ guards --
def login_required(fn):
    @wraps(fn)
    def wrapper(*a, **kw):
        if "user_id" not in session:
            return redirect(url_for("login"))
        return fn(*a, **kw)
    return wrapper


def role_required(role):
    def deco(fn):
        @wraps(fn)
        def wrapper(*a, **kw):
            if "user_id" not in session:
                return redirect(url_for("login"))
            if session["role"] != role:
                return redirect(url_for(f"{session['role']}_dashboard"))
            return fn(*a, **kw)
        return wrapper
    return deco


# -------------------------------------------------------------------- auth --
@app.route("/")
def index():
    if "user_id" in session:
        return redirect(url_for(f"{session['role']}_dashboard"))
    return redirect(url_for("login"))


@app.route("/register", methods=["GET", "POST"])
def register():
    """Create a new organisation + its first admin account."""
    if request.method == "POST":
        org_name = request.form["org_name"].strip()
        org_type = request.form["org_type"]
        name = request.form["full_name"].strip()
        email = request.form["email"].strip().lower()
        phone = request.form.get("phone", "").strip()
        password = request.form["password"]
        if not (org_name and name and email and len(password) >= 6):
            flash("All fields are required; password must be at least 6 characters.", "error")
            return render_template("register.html")
        db = get_db()
        try:
            now = datetime.utcnow().isoformat()
            cur = db.execute(
                "INSERT INTO organisations (name, org_type, created_at) VALUES (?,?,?)",
                (org_name, org_type, now),
            )
            org_id = cur.lastrowid
            db.execute(
                "INSERT INTO users (org_id, full_name, phone, email, password_hash, role, created_at)"
                " VALUES (?,?,?,?,?,?,?)",
                (org_id, name, phone, email, generate_password_hash(password), "admin", now),
            )
            db.commit()
        except sqlite3.IntegrityError:
            flash("That email is already registered.", "error")
            return render_template("register.html")
        flash("Organisation created. Please log in.", "success")
        return redirect(url_for("login"))
    return render_template("register.html")


@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        email = request.form["email"].strip().lower()
        password = request.form["password"]
        db = get_db()
        user = db.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()
        if user and check_password_hash(user["password_hash"], password):
            session.update(user_id=user["id"], org_id=user["org_id"], role=user["role"],
                           full_name=user["full_name"])
            return redirect(url_for(f"{user['role']}_dashboard"))
        flash("Invalid email or password.", "error")
    return render_template("login.html")


@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("login"))


def current_org():
    return get_db().execute(
        "SELECT * FROM organisations WHERE id = ?", (session["org_id"],)).fetchone()


# ------------------------------------------------------------------- admin --
@role_required("admin")
def admin_dashboard():
    db = get_db()
    org = current_org()
    requests_pending = db.execute(
        """SELECT p.*, u.full_name FROM payments p JOIN users u ON u.id = p.member_id
           WHERE p.org_id=? AND p.status='pending' ORDER BY p.created_at DESC""",
        (session["org_id"],)).fetchall()
    decisions = db.execute(
        """SELECT p.*, u.full_name FROM payments p JOIN users u ON u.id = p.member_id
           WHERE p.org_id=? AND p.status!='pending' ORDER BY p.decided_at DESC LIMIT 25""",
        (session["org_id"],)).fetchall()
    members = db.execute(
        """SELECT u.*, COALESCE(SUM(CASE WHEN p.status='approved' THEN p.amount END),0) AS savings,
                  COALESCE(SUM(CASE WHEN p.status='pending' THEN p.amount END),0) AS pending_amt
           FROM users u LEFT JOIN payments p ON p.member_id = u.id
           WHERE u.org_id=? AND u.role='member' GROUP BY u.id ORDER BY u.full_name""",
        (session["org_id"],)).fetchall()
    dues = db.execute(
        """SELECT d.*, u.full_name FROM dues d JOIN users u ON u.id = d.member_id
           WHERE d.org_id=? ORDER BY d.created_at DESC LIMIT 50""",
        (session["org_id"],)).fetchall()
    logs = db.execute(
        "SELECT * FROM audit_log WHERE org_id=? ORDER BY id DESC LIMIT 30",
        (session["org_id"],)).fetchall()
    return render_template("admin.html", org=org, requests=requests_pending,
                           decisions=decisions, members=members, dues=dues, logs=logs)


@app.post("/admin/members/add")
def admin_add_member():
    db = get_db()
    name = request.form["full_name"].strip()
    email = request.form["email"].strip().lower()
    phone = request.form.get("phone", "").strip()
    password = request.form.get("password", "member123") or "member123"
    if not (name and email):
        flash("Member name and email are required.", "error")
        return redirect(url_for("admin_dashboard"))
    try:
        db.execute(
            "INSERT INTO users (org_id, full_name, phone, email, password_hash, role, created_at)"
            " VALUES (?,?,?,?,?,?,?)",
            (session["org_id"], name, phone, email,
             generate_password_hash(password), "member", datetime.utcnow().isoformat()),
        )
        db.commit()
    except sqlite3.IntegrityError:
        flash("That email is already in use.", "error")
        return redirect(url_for("admin_dashboard"))
    audit("member_added", f"{name} ({email})")
    flash(f"Member {name} added. Default password: {password}", "success")
    return redirect(url_for("admin_dashboard"))


@app.post("/admin/dues/add")
def admin_add_due():
    db = get_db()
    title = request.form["title"].strip()
    amount = float(request.form["amount"])
    due_date = request.form.get("due_date", "")
    target = request.form.get("member_id", "all")
    now = datetime.utcnow().isoformat()
    if target == "all":
        members = db.execute(
            "SELECT id FROM users WHERE org_id=? AND role='member'",
            (session["org_id"],)).fetchall()
        db.executemany(
            "INSERT INTO dues (org_id, member_id, title, amount, due_date, created_at)"
            " VALUES (?,?,?,?,?,?)",
            [(session["org_id"], m["id"], title, amount, due_date, now) for m in members])
        audit("due_assigned", f"'{title}' ₦{amount:,.2f} to ALL members")
    else:
        db.execute(
            "INSERT INTO dues (org_id, member_id, title, amount, due_date, created_at)"
            " VALUES (?,?,?,?,?,?)",
            (session["org_id"], int(target), title, amount, due_date, now))
        audit("due_assigned", f"'{title}' ₦{amount:,.2f} to member id {target}")
    db.commit()
    flash("Due assigned.", "success")
    return redirect(url_for("admin_dashboard"))


@app.post("/admin/payments/<int:pid>/<decision>")
def admin_decide(pid, decision):
    if decision not in ("approved", "rejected"):
        flash("Invalid action.", "error")
        return redirect(url_for("admin_dashboard"))
    db = get_db()
    pay = db.execute("SELECT * FROM payments WHERE id=? AND org_id=? AND status='pending'",
                     (pid, session["org_id"])).fetchone()
    if not pay:
        flash("Request not found (or already decided).", "error")
        return redirect(url_for("admin_dashboard"))
    note = request.form.get("decided_note", "").strip()
    db.execute(
        "UPDATE payments SET status=?, decided_by=?, decided_at=?, decided_note=? WHERE id=?",
        (decision, session["user_id"], datetime.utcnow().isoformat(), note, pid))
    db.commit()
    audit(f"payment_{decision}",
          f"₦{pay['amount']:,.2f} ref {pay['reference']} by member id {pay['member_id']}")
    flash(f"Payment #{pid} {decision}.", "success")
    return redirect(url_for("admin_dashboard"))


@app.post("/admin/settings")
def admin_settings():
    db = get_db()
    db.execute(
        "UPDATE organisations SET name=?, org_type=?, bank_name=?, account_number=?, account_name=?"
        " WHERE id=?",
        (request.form["org_name"].strip(), request.form["org_type"],
         request.form.get("bank_name", "").strip(),
         request.form.get("account_number", "").strip(),
         request.form.get("account_name", "").strip(),
         session["org_id"]))
    db.commit()
    audit("org_settings_updated", "Designated account details changed")
    flash("Organisation settings saved.", "success")
    return redirect(url_for("admin_dashboard"))


# ------------------------------------------------------------------ member --
@role_required("member")
def member_dashboard():
    db = get_db()
    uid = session["user_id"]
    org = current_org()
    totals = db.execute(
        """SELECT COALESCE(SUM(CASE WHEN status='approved' THEN amount END),0) AS approved,
                  COALESCE(SUM(CASE WHEN status='pending' THEN amount END),0) AS pending
           FROM payments WHERE member_id=?""", (uid,)).fetchone()
    my_payments = db.execute(
        """SELECT p.*, a.full_name AS decided_by_name FROM payments p
           LEFT JOIN users a ON a.id = p.decided_by
           WHERE p.member_id=? ORDER BY p.created_at DESC""", (uid,)).fetchall()
    my_dues = db.execute(
        "SELECT * FROM dues WHERE member_id=? AND status='open' ORDER BY due_date",
        (uid,)).fetchall()
    return render_template("member.html", org=org, totals=totals,
                           payments=my_payments, dues=my_dues)


@app.post("/member/pay")
def member_pay():
    db = get_db()
    try:
        amount = float(request.form["amount"])
        if amount <= 0:
            raise ValueError
    except (ValueError, KeyError):
        flash("Enter a valid amount.", "error")
        return redirect(url_for("member_dashboard"))
    db.execute(
        "INSERT INTO payments (org_id, member_id, amount, payment_date, reference, note, created_at)"
        " VALUES (?,?,?,?,?,?,?)",
        (session["org_id"], session["user_id"], amount,
         request.form["payment_date"], request.form.get("reference", "").strip(),
         request.form.get("note", "").strip(), datetime.utcnow().isoformat()))
    db.commit()
    audit("payment_submitted", f"₦{amount:,.2f} ref {request.form.get('reference','')}")
    flash("Payment submitted and awaiting admin approval.", "success")
    return redirect(url_for("member_dashboard"))


app.add_url_rule("/admin", "admin_dashboard", view_func=admin_dashboard,
                 methods=["GET"], strict_slashes=False)
app.add_url_rule("/member", "member_dashboard", view_func=member_dashboard,
                 methods=["GET"], strict_slashes=False)

init_db()

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