import duckdb
from duckdb import connect

# In-memory database for blazing-fast queries
con = connect(":memory:")

# Three tables: revenue, costs, budget
con.execute("""
CREATE TABLE revenue AS
SELECT date, product, amount, region
FROM read_csv_auto('revenue_2024.csv')
WHERE date >= '2024-01-01';

CREATE TABLE costs AS
SELECT date, category, amount, dept
FROM read_csv_auto('costs_2024.csv')
WHERE date >= '2024-01-01';

CREATE TABLE budget AS
SELECT dept, year, quarter, allocated
FROM read_csv_auto('budget_2024.csv');
""")

monthly_revenue = con.execute("""
SELECT
    date_trunc('month', date) AS month,
    product,
    SUM(amount) AS total_revenue,
    LAG(SUM(amount)) OVER (
        PARTITION BY product
        ORDER BY date_trunc('month', date)
    ) AS prev_month_revenue,
    ROUND(
        100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (
            PARTITION BY product ORDER BY date_trunc('month', date)
        )) / NULLIF(LAG(SUM(amount)) OVER (
            PARTITION BY product ORDER BY date_trunc('month', date)
        ), 0),
        2
    ) AS mom_pct
FROM revenue
GROUP BY month, product
ORDER BY month, product
""").df()

budget_actual = con.execute("""
SELECT
    b.dept,
    b.year,
    b.quarter,
    b.allocated AS budget,
    COALESCE(c.spent, 0) AS actual_spent,
    ROUND(
        100.0 * COALESCE(c.spent, 0) / b.allocated, 1
    ) AS spend_pct,
    CASE
        WHEN COALESCE(c.spent, 0) / b.allocated > 0.9 THEN '🔴 Over Budget'
        WHEN COALESCE(c.spent, 0) / b.allocated > 0.8 THEN '🟡 Near Limit'
        ELSE '🟢 Normal'
    END AS status
FROM budget b
LEFT JOIN (
    SELECT dept, year, quarter, SUM(amount) AS spent
    FROM costs
    GROUP BY dept, year, quarter
) c USING (dept, year, quarter)
ORDER BY spend_pct DESC
""").df()

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

# Export both tables
monthly_revenue.to_excel('monthly_report.xlsx', sheet_name='Revenue Trend', index=False)
budget_actual.to_excel('monthly_report.xlsx', sheet_name='Budget Execution', index=False)

# Format headers
wb = load_workbook('monthly_report.xlsx')
for sheet_name in ['Revenue Trend', 'Budget Execution']:
    ws = wb[sheet_name]
    ws.freeze_panes = 'A2'  # Freeze header row
    for cell in ws[1]:
        cell.font = Font(bold=True, color='FFFFFF')
        cell.fill = PatternFill('solid', fgColor='4472C4')  # Dark blue
        cell.alignment = Alignment(horizontal='center')
wb.save('monthly_report.xlsx')

import zipfile
from datetime import datetime

filename = f"financial_report_{datetime.now().strftime('%Y%m%d')}.zip"
with zipfile.ZipFile(filename, 'w') as z:
    z.write('monthly_report.xlsx')
    z.writestr('README.txt', f'''
Financial Report Generated: {datetime.now()}
Data Range: 2024 Q1-Q3
Tool: DuckDB + Python
Note: Re-run gen_report.py to regenerate
''')

print(f"✅ Report generated: {filename}")

# Auto-generate report on the 1st of every month at 2 AM
0 2 1 * * cd /path/to/project && python3 gen_report.py

from fastapi import FastAPI
from fastapi.responses import FileResponse
import subprocess
from datetime import datetime

app = FastAPI()

@app.get("/report")
def get_report():
    subprocess.run(["python3", "gen_report.py"], check=True)
    return FileResponse(
        f"financial_report_{datetime.now().strftime('%Y%m%d')}.zip",
        media_type='application/zip'
    )

@app.get("/revenue/trend")
def get_revenue_trend():
    con = connect(":memory:")
    con.execute("""
    CREATE TABLE revenue AS
    SELECT * FROM read_csv_auto('revenue_2024.csv');
    """)
    result = con.execute("""
    SELECT date_trunc('month', date) AS month,
           product, SUM(amount) AS total
    FROM revenue GROUP BY month, product
    ORDER BY month
    """).df().to_dict('records')
    return result

# First run: build tables from CSV and persist to .duckdb file
con = duckdb.connect("finance.db")
con.execute("CREATE TABLE revenue AS SELECT * FROM read_csv_auto('revenue_2024.csv')")
con.execute("CREATE TABLE costs AS SELECT * FROM read_csv_auto('costs_2024.csv')")
con.execute("CREATE TABLE budget AS SELECT * FROM read_csv_auto('budget_2024.csv')")
con.close()

# Subsequent runs: open the database file directly, instant response
con = duckdb.connect("finance.db")
# Query logic is identical — data is already on disk

con.execute("""
-- Only import new revenue records for this month
INSERT INTO revenue
SELECT * FROM read_csv_auto('revenue_2024_08.csv')
WHERE date >= '2024-08-01'
  AND date NOT IN (SELECT date FROM revenue);
""")