from sqlalchemy import text
from sqlalchemy.engine import Engine

# Per-account starting balance: the opening_balance of each account's earliest
# confirmed statement (if any). Used to seed the cumulative sums below so the
# Grafana balance charts show the real account balance, not just the running
# sum of imported transactions (which would start at zero and ignore the
# balance that existed before the first imported statement).
_ACCOUNT_BASE = """
    SELECT DISTINCT ON (account_id) account_id, opening_balance
    FROM statements
    WHERE status = 'confirmed' AND opening_balance IS NOT NULL
    ORDER BY account_id, period_start NULLS LAST, id
"""

VIEWS: dict[str, str] = {
    "v_balance_history": f"""
        SELECT t.booking_date AS day, a.name AS account,
               COALESCE(b.opening_balance, 0)
                 + SUM(SUM(t.amount)) OVER (PARTITION BY a.id
                                            ORDER BY t.booking_date) AS balance
        FROM transactions t JOIN accounts a ON a.id = t.account_id
        LEFT JOIN ({_ACCOUNT_BASE}) b ON b.account_id = a.id
        WHERE t.status = 'confirmed'
        GROUP BY a.id, a.name, t.booking_date, b.opening_balance""",
    "v_balance_total": f"""
        SELECT booking_date AS day,
               (SELECT COALESCE(SUM(opening_balance), 0)
                FROM ({_ACCOUNT_BASE}) s)
                 + SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
        FROM transactions WHERE status = 'confirmed' GROUP BY booking_date""",
    "v_monthly_by_category": """
        SELECT date_trunc('month', t.booking_date) AS month,
               COALESCE(c.name, 'unkategorisiert') AS category,
               SUM(t.amount) AS total
        FROM transactions t LEFT JOIN categories c ON c.id = t.category_id
        WHERE t.status = 'confirmed' GROUP BY 1, 2""",
    "v_projection": """
        SELECT s.name AS scenario, p.day, p.balance
        FROM projection_points p JOIN scenarios s ON s.id = p.scenario_id""",
}


def create_views(engine: Engine) -> None:
    if engine.dialect.name != "postgresql":
        return
    with engine.begin() as conn:
        for name, body in VIEWS.items():
            # CREATE OR REPLACE VIEW fails if the output column set changed
            # (name/type/order). Fall back to DROP + CREATE in that case so a
            # redeploy always applies the current definition.
            try:
                with conn.begin_nested():
                    conn.execute(
                        text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
            except Exception:
                conn.execute(text(f"DROP VIEW IF EXISTS {name} CASCADE"))
                conn.execute(text(f"CREATE VIEW {name} AS {body}"))