#!/usr/bin/env catnip
# DuckDB + Catnip : analytics déclaratif et composable
# DuckDB exécute le SQL, Catnip orchestre les scénarios analytiques.

duckdb = import('duckdb')

line = () => { print("-" * 60) }

# Données sources typées
struct Product {
    name: str
    category: str
    price: float
    stock: int

    inventory(self) => { self.price * self.stock }
    to_row(self) => { list(self.name, self.category, self.price, self.stock) }
}

products = list(
    Product("Laptop", "Hardware", 999.99, 15),
    Product("Mouse", "Accessory", 29.99, 150),
    Product("Keyboard", "Accessory", 79.99, 80),
    Product("Monitor", "Hardware", 299.99, 35),
    Product("Dock", "Accessory", 149.99, 40),
)

# Interop : Catnip structs -> DuckDB table
db = duckdb.connect()
db.execute("CREATE TABLE products (name TEXT, category TEXT, price DOUBLE, stock INT)")

for p in products {
    db.execute("INSERT INTO products VALUES (?, ?, ?, ?)", p.to_row())
}

print(f"⇒ {len(products)} produits chargés dans DuckDB")
line()

# KPI : specs déclaratives
struct Metric {
    label: str
    sql: str
    unit: str

    run(self, db): str => {
        value = db.execute(self.sql).fetchone()[0]
        f"  {self.label} : {value}{self.unit}"
    }
}

metrics = list(
    Metric("Produits", "SELECT COUNT(*) FROM products", ""),
    Metric("Prix moyen", "SELECT ROUND(AVG(price), 2) FROM products", " €"),
    Metric("Valeur stock", "SELECT ROUND(SUM(price * stock), 2) FROM products", " €"),
    Metric("Prix max", "SELECT ROUND(MAX(price), 2) FROM products", " €"),
)

print("⇒ KPI globaux")
# Broadcast sur structs : chaque Metric est une feuille, passé entier à la lambda
results = metrics.[(m) => { m.run(db) }]
for r in results { print(r) }
line()

# Ranking composable
ranking_sql = (order_expr, n) => {
    "SELECT name, category, price, stock, ROUND(price * stock, 2) " +
        f"FROM products ORDER BY {order_expr} DESC LIMIT {n}"
}

top_by = (order_expr, n) => {
    rows = db.execute(ranking_sql(order_expr, n)).fetchall()
    out = list()
    for row in rows {
        out.append(f"  {row[0]} | {row[1]} | {row[4]} €")
    }
    out
}

print("⇒ Top 3 par valeur de stock")
for item in top_by("price * stock", 3) { print(item) }
line()

# Segmentation : SQL window + pattern matching Catnip
ranked = db.execute("SELECT name, category, price, RANK() OVER (ORDER BY price DESC) as r FROM products").fetchall()

# Niveau de gamme : ensemble fermé, dérivé du rang et du prix
union Tier {
    premium; mid; entry

    label(self): str => {
        match self {
            Tier.premium => { "premium" }
            Tier.mid     => { "mid" }
            Tier.entry   => { "entry" }
        }
    }
}

struct Segment {
    name: str
    price: float
    rank: int
    tier: Tier

    display(self): str => { f"  #{self.rank} {self.name} ({self.price} €) => {self.tier.label()}" }
}

tier_for = (price, rank): Tier => {
    match True {
        _ if rank <= 2 => { Tier.premium }
        _ if price >= 100 => { Tier.mid }
        _ => { Tier.entry }
    }
}

segments = list()
for row in ranked {
    segments.append(Segment(row[0], row[2], row[3], tier_for(row[2], row[3])))
}

print("⇒ Segmentation sur résultat SQL")
# Broadcast explicite : les structs sont des feuilles, pas de descente récursive
segments.[(s) => { print(s.display()) }]

line()
db.close()