<?php
// A simple PHP endpoint that serves aggregated data
// This is the layer data scientists often consume.

header('Content-Type: application/json');

$pdo = new PDO(
  'mysql:host=localhost;dbname=shop', 'user', 'pass'
);

$stmt = $pdo->prepare("
  SELECT
    DATE(created_at) AS day,
    SUM(total) AS revenue,
    COUNT(*) AS orders
  FROM orders
  WHERE created_at >= :since
  GROUP BY DATE(created_at)
  ORDER BY day
");

$stmt->execute([
  ':since' => date('Y-m-d', strtotime('-30 days'))
]);

echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

# Python — consume the PHP endpoint from an analysis notebook
import requests
import pandas as pd

url = "https://internal.example.com/api/revenue"

resp = requests.get(url, timeout=10)
resp.raise_for_status()

df = pd.DataFrame(resp.json())
df["day"] = pd.to_datetime(df["day"])
df = df.set_index("day").sort_index()

print(df.tail())
print("Monthly revenue:",
      df["revenue"].resample("ME").sum().iloc[-1])

# From here, all the analysis happens in Python —
# PHP is just the data source, not the analytics engine.

<?php
// A PHP endpoint that triggers a Python report
// (PHP glue + Python analytics — a very common pattern)

header('Content-Type: application/json');

$payload = [
  'start' => $_GET['start'] ?? date('Y-m-01'),
  'end'   => $_GET['end']   ?? date('Y-m-d'),
];

// Kick off a Python job (via shell, queue, or API)
$cmd = 'python3 /opt/reports/generate.py '
     . escapeshellarg(json_encode($payload));

$output = shell_exec($cmd);

echo json_encode([
  'status'  => 'queued',
  'payload' => $payload,
  'result'  => $output,
]);

// In production, prefer a queue (Redis, SQS)
// over shell_exec — but the pattern is the same.

Old workflow (desktop era):
1. Download a CSV from email
2. Open in Excel or SPSS
3. Clean manually, add formulas
4. Build charts by hand
5. Save as .xlsx and email to boss
6. Repeat next month from scratch

Pain points:
- Not reproducible
- Not collaborative
- Breaks with large data
- No version history

New workflow (web-first era):
1. Data lives in a warehouse / API
2. Pull data with SQL or Python
3. Clean and analyse with Pandas
4. Version everything in Git
5. Schedule the pipeline (Airflow, cron)
6. Deliver via a live dashboard, not a file
7. Let stakeholders self-serve

Benefits:
- Reproducible
- Collaborative
- Scales with volume
- Every change is auditable

# A modern analysis workflow — Python + Pandas + SQL
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://warehouse")
df = pd.read_sql("""
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      product_category,
      SUM(revenue) AS revenue
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '12 months'
    GROUP BY 1, 2
    ORDER BY 1
""", engine)

# Quick sanity checks (a habit from the old days)
assert df["revenue"].min() >= 0, "Negative revenue detected"
assert df["month"].is_monotonic_increasing, "Months out of order"

# Feature engineering
df["mom_change"] = (
    df.groupby("product_category")["revenue"].pct_change()
)

# Summary for stakeholders
summary = df.groupby("product_category").agg(
    total=("revenue", "sum"),
    months=("month", "nunique"),
    avg_mom=("mom_change", "mean"),
).sort_values("total", ascending=False)

print(summary.head())

# FastAPI — serve your analysis to stakeholders
from fastapi import FastAPI
import pandas as pd
from sqlalchemy import create_engine

app = FastAPI()
engine = create_engine("postgresql://warehouse")

@app.get("/api/monthly-revenue")
def monthly_revenue():
    df = pd.read_sql("""
        SELECT
          DATE_TRUNC('month', created_at) AS month,
          SUM(revenue) AS revenue
        FROM orders
        WHERE created_at >= NOW() - INTERVAL '12 months'
        GROUP BY 1
        ORDER BY 1
    """, engine)

    return df.assign(month=df["month"].astype(str)).to_dict(orient="records")

# Run: uvicorn app:app --reload
# This replaces "emailing a CSV" with a live, always-current endpoint.
# PHP, Node, or a frontend framework can now consume it.