🗄️ Database SQL + Python per AI e Data Science

# 1. SETUP AMBIENTE DATABASE PER AI/DATA SCIENCE
print("🗄️ SETUP DATABASE PER APPLICAZIONI AI")
print("="*60)

# Installazione librerie (eseguire in terminale prima)
print("Per Google Colab, esegui questi comandi in una cella separata:")
print("!pip install sqlalchemy psycopg2-binary pymysql pandas numpy scikit-learn")
print("!apt-get install sqlite3")

# Import librerie essenziali per database e AI
import pandas as pd
import numpy as np
import sqlite3
from sqlalchemy import create_engine, text, MetaData, Table, Column, Integer, String, Float, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta
import json
import warnings
warnings.filterwarnings('ignore')

# Import librerie AI/ML
from sklearn.preprocessing import StandardScaler, LabelEncoder
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, confusion_matrix
import joblib

print("✅ Librerie importate con successo!")
print("\n🧰 TOOLBOX DATABASE + AI:")
print("  • SQLite3: Database embedded per sviluppo")
print("  • SQLAlchemy: ORM per database relazionali")
print("  • Pandas: Manipolazione dati da/in database")
print("  • Scikit-learn: Modelli ML su dati estratti")
print("  • SQL: Linguaggio per query complesse")

# Configurazione visualizzazione
plt.style.use('seaborn-v0_8-darkgrid')
sns.set_palette("husl")
plt.rcParams['figure.figsize'] = (14, 8)

print("\n🔧 Ambiente pronto per integrazione Database + Python + AI!")

# 2. CREAZIONE DATABASE SQLITE COMPLESSO PER AI
print("🏗️ CREAZIONE DATABASE RELAZIONALE COMPLESSO")
print("="*60)

# Crea connessione SQLite in memoria (o su file)
conn = sqlite3.connect(':memory:')  # Usa 'ai_database.db' per salvare su file
cursor = conn.cursor()

print("📊 Creazione schema database per fintech (rischio creditizio)...")

# 2.1 Creazione tabelle con relazioni complesse
tables_sql = [
    # Tabella Clienti (entità principale)
    '''CREATE TABLE IF NOT EXISTS clienti (
        cliente_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nome TEXT NOT NULL,
        cognome TEXT NOT NULL,
        eta INTEGER CHECK(eta >= 18 AND eta <= 100),
        citta TEXT,
        stipendio_annuale DECIMAL(10,2),
        occupazione TEXT,
        data_registrazione DATE,
        credit_score INTEGER CHECK(credit_score >= 300 AND credit_score <= 850)
    )''',
    
    # Tabella Conti Bancari
    '''CREATE TABLE IF NOT EXISTS conti_bancari (
        conto_id INTEGER PRIMARY KEY AUTOINCREMENT,
        cliente_id INTEGER,
        tipo_conto TEXT CHECK(tipo_conto IN ('corrente', 'risparmio', 'investimento')),
        saldo DECIMAL(12,2),
        data_apertura DATE,
        FOREIGN KEY (cliente_id) REFERENCES clienti(cliente_id)
    )''',
    
    # Tabella Transazioni (dettagliato per ML)
    '''CREATE TABLE IF NOT EXISTS transazioni (
        transazione_id INTEGER PRIMARY KEY AUTOINCREMENT,
        conto_id INTEGER,
        data_transazione DATETIME,
        importo DECIMAL(10,2),
        categoria TEXT CHECK(categoria IN ('stipendio', 'bollette', 'shopping', 
                                          'ristorante', 'trasporti', 'intrattenimento',
                                          'investimenti', 'prelievo', 'bonifico')),
        tipo TEXT CHECK(tipo IN ('entrata', 'uscita')),
        merchant TEXT,
        rischio_frode BOOLEAN DEFAULT 0,
        FOREIGN KEY (conto_id) REFERENCES conti_bancari(conto_id)
    )''',
    
    # Tabella Prestiti (target per modelli predittivi)
    '''CREATE TABLE IF NOT EXISTS prestiti (
        prestito_id INTEGER PRIMARY KEY AUTOINCREMENT,
        cliente_id INTEGER,
        importo_richiesto DECIMAL(12,2),
        importo_concesso DECIMAL(12,2),
        durata_mesi INTEGER,
        tasso_interesse DECIMAL(5,3),
        data_richiesta DATE,
        data_approvazione DATE,
        stato TEXT CHECK(stato IN ('approvato', 'rifiutato', 'in_verifica')),
        motivo_rifiuto TEXT,
        rischio_default DECIMAL(5,4), -- Probabilità di default (0-1)
        FOREIGN KEY (cliente_id) REFERENCES clienti(cliente_id)
    )''',
    
    # Tabella Storico Pagamenti (time series per ML)
    '''CREATE TABLE IF NOT EXISTS storico_pagamenti (
        pagamento_id INTEGER PRIMARY KEY AUTOINCREMENT,
        prestito_id INTEGER,
        data_scadenza DATE,
        data_pagamento DATE,
        importo_dovuto DECIMAL(10,2),
        importo_pagato DECIMAL(10,2),
        giorni_ritardo INTEGER,
        FOREIGN KEY (prestito_id) REFERENCES prestiti(prestito_id)
    )'''
]

# Esegui creazione tabelle
for i, table_sql in enumerate(tables_sql):
    cursor.execute(table_sql)
    print(f"✅ Tabella {i+1} creata")

# 2.2 Inserimento dati sintetici per training AI
print(f"\n📥 INSERIMENTO DATI SINTETICI PER TRAINING AI...")

# Genera dati sintetici realistici
np.random.seed(42)
n_clienti = 5000

# Inserimento clienti
clienti_data = []
for i in range(1, n_clienti + 1):
    eta = np.random.randint(22, 70)
    stipendio = np.random.lognormal(10.5, 0.3) * 1000
    credit_score = np.random.randint(300, 850)
    
    clienti_data.append((
        f"Nome{i}", f"Cognome{i}", eta,
        np.random.choice(['Milano', 'Roma', 'Torino', 'Napoli', 'Firenze']),
        round(stipendio, 2),
        np.random.choice(['Impiegato', 'Libero Professionista', 'Manager', 'Operaio', 'Commerciante']),
        f"202{np.random.randint(0,4)}-{np.random.randint(1,13):02d}-{np.random.randint(1,28):02d}",
        credit_score
    ))

cursor.executemany('''INSERT INTO clienti 
    (nome, cognome, eta, citta, stipendio_annuale, occupazione, data_registrazione, credit_score)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)''', clienti_data)

print(f"✅ {n_clienti} clienti inseriti")

# Inserimento conti bancari (2-3 conti per cliente)
conti_data = []
for cliente_id in range(1, n_clienti + 1):
    n_conti = np.random.randint(1, 4)
    for _ in range(n_conti):
        saldo = np.random.exponential(5000) + 100
        conti_data.append((
            cliente_id,
            np.random.choice(['corrente', 'risparmio', 'investimento']),
            round(saldo, 2),
            f"202{np.random.randint(0,4)}-{np.random.randint(1,13):02d}-{np.random.randint(1,28):02d}"
        ))

cursor.executemany('''INSERT INTO conti_bancari 
    (cliente_id, tipo_conto, saldo, data_apertura) VALUES (?, ?, ?, ?)''', conti_data)

print(f"✅ {len(conti_data)} conti bancari inseriti")

# Inserimento transazioni (10-50 per conto)
print(f"📊 Generazione transazioni (simulazione realistica)...")
transazioni_data = []
conto_ids = [row[0] for row in cursor.execute("SELECT conto_id FROM conti_bancari").fetchall()]

for conto_id in conto_ids:
    n_transazioni = np.random.randint(10, 51)
    for _ in range(n_transazioni):
        # Data casuale negli ultimi 2 anni
        giorni_fa = np.random.randint(0, 730)
        data = datetime.now() - timedelta(days=giorni_fa)
        
        importo = abs(np.random.normal(0, 100))
        if importo < 5: importo = 5
        
        categoria = np.random.choice(['stipendio', 'bollette', 'shopping', 'ristorante', 
                                     'trasporti', 'intrattenimento', 'investimenti', 'prelievo'])
        
        tipo = 'entrata' if categoria == 'stipendio' or np.random.random() < 0.3 else 'uscita'
        
        # Simula rischio frode (1% delle transazioni)
        rischio_frode = 1 if np.random.random() < 0.01 else 0
        
        transazioni_data.append((
            conto_id,
            data.strftime('%Y-%m-%d %H:%M:%S'),
            round(importo, 2),
            categoria,
            tipo,
            f"Merchant{np.random.randint(1, 1000)}",
            rischio_frode
        ))

# Inserimento in batch per performance
batch_size = 1000
for i in range(0, len(transazioni_data), batch_size):
    batch = transazioni_data[i:i+batch_size]
    cursor.executemany('''INSERT INTO transazioni 
        (conto_id, data_transazione, importo, categoria, tipo, merchant, rischio_frode)
        VALUES (?, ?, ?, ?, ?, ?, ?)''', batch)

print(f"✅ {len(transazioni_data)} transazioni inserite")

# Inserimento prestiti (30% dei clienti)
print(f"💰 Generazione dati prestiti per modello predittivo...")
prestiti_data = []
clienti_con_prestito = np.random.choice(range(1, n_clienti + 1), 
                                        size=int(n_clienti * 0.3), 
                                        replace=False)

for cliente_id in clienti_con_prestito:
    # Recupera credit score del cliente
    credit_score = cursor.execute(
        "SELECT credit_score FROM clienti WHERE cliente_id = ?", 
        (cliente_id,)
    ).fetchone()[0]
    
    # Determina approvazione basata su credit score
    probabilita_approvazione = (credit_score - 300) / 550  # Normalizza 300-850 a 0-1
    
    if np.random.random() < probabilita_approvazione:
        stato = 'approvato'
        motivo_rifiuto = None
        
        # Importo concesso (minore di richiesto se credit score basso)
        importo_richiesto = np.random.lognormal(9, 0.5)
        importo_concesso = importo_richiesto * (0.5 + probabilita_approvazione * 0.5)
        
        # Calcola rischio default basato su vari fattori
        rischio_default = max(0, min(1, 
            (850 - credit_score) / 550 * 0.7 +  # 70% peso credit score
            np.random.beta(2, 5) * 0.3           # 30% casualità
        ))
    else:
        stato = 'rifiutato'
        motivo_rifiuto = np.random.choice(['credit_score_basso', 'reddito_insufficiente', 
                                          'storico_negativo', 'troppi_debiti'])
        importo_richiesto = np.random.lognormal(9, 0.5)
        importo_concesso = 0
        rischio_default = 0
    
    prestiti_data.append((
        cliente_id,
        round(importo_richiesto, 2),
        round(importo_concesso, 2),
        np.random.choice([12, 24, 36, 48, 60]),
        round(np.random.uniform(2.5, 9.5), 3),
        f"202{np.random.randint(0,4)}-{np.random.randint(1,13):02d}-01",
        f"202{np.random.randint(0,4)}-{np.random.randint(1,13):02d}-{np.random.randint(1,15):02d}" if stato == 'approvato' else None,
        stato,
        motivo_rifiuto,
        round(rischio_default, 4)
    ))

cursor.executemany('''INSERT INTO prestiti 
    (cliente_id, importo_richiesto, importo_concesso, durata_mesi, tasso_interesse, 
     data_richiesta, data_approvazione, stato, motivo_rifiuto, rischio_default)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', prestiti_data)

print(f"✅ {len(prestiti_data)} prestiti inseriti")

# 2.3 Commit e verifica database
conn.commit()

# Query di verifica
print(f"\n📈 VERIFICA DATABASE CREATO:")
tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
print(f"Tabelle nel database: {[table[0] for table in tables]}")

for table in tables:
    count = cursor.execute(f"SELECT COUNT(*) FROM {table[0]}").fetchone()[0]
    print(f"  • {table[0]}: {count:,} record")

print(f"\n🎉 Database SQLite creato con successo!")
print(f"   Contiene dati realistici per training modelli AI di rischio creditizio")
print(f"   Totale record: {sum([cursor.execute(f'SELECT COUNT(*) FROM {table[0]}').fetchone()[0] for table in tables]):,}")

# 3. SQL AVANZATO PER FEATURE ENGINEERING AI
print("🎯 FEATURE ENGINEERING CON SQL PER MODELLI ML")
print("="*60)

print("In produzione, il 80% del lavoro ML è preparazione dati!")
print("SQL avanzato può fare feature engineering direttamente nel database.")

# 3.1 Query base per esplorazione dati
print(f"\n🔍 ESPLORAZIONE DATABASE CON SQL:")

# Query 1: Statistiche base clienti
query1 = """
SELECT 
    COUNT(*) as totale_clienti,
    ROUND(AVG(eta), 1) as eta_media,
    ROUND(AVG(stipendio_annuale), 2) as stipendio_medio,
    ROUND(AVG(credit_score), 1) as credit_score_medio,
    MIN(credit_score) as credit_score_min,
    MAX(credit_score) as credit_score_max
FROM clienti;
"""

df_base = pd.read_sql_query(query1, conn)
print("📊 Statistiche base clienti:")
print(df_base.to_string(index=False))

# Query 2: Distribuzione occupazioni
query2 = """
SELECT 
    occupazione,
    COUNT(*) as numero_clienti,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM clienti), 2) as percentuale,
    ROUND(AVG(credit_score), 1) as credit_score_medio,
    ROUND(AVG(stipendio_annuale), 2) as stipendio_medio
FROM clienti
GROUP BY occupazione
ORDER BY numero_clienti DESC;
"""

df_occupazioni = pd.read_sql_query(query2, conn)
print(f"\n👔 Distribuzione per occupazione:")
print(df_occupazioni.to_string(index=False))

# 3.2 Feature Engineering complesso con SQL
print(f"\n⚙️  FEATURE ENGINEERING AVANZATO PER MODELLO RISCHIO CREDITIZIO:")

# Query complessa che unisce multiple tabelle e calcola features
query_features = """
WITH transazioni_agg AS (
    -- Features dalle transazioni
    SELECT 
        c.cliente_id,
        COUNT(t.transazione_id) as totale_transazioni,
        SUM(CASE WHEN t.tipo = 'uscita' THEN t.importo ELSE 0 END) as totale_uscite,
        SUM(CASE WHEN t.tipo = 'entrata' THEN t.importo ELSE 0 END) as totale_entrate,
        AVG(CASE WHEN t.tipo = 'uscita' THEN t.importo ELSE NULL END) as media_uscite,
        AVG(CASE WHEN t.tipo = 'entrata' THEN t.importo ELSE NULL END) as media_entrate,
        SUM(CASE WHEN t.rischio_frode = 1 THEN 1 ELSE 0 END) as transazioni_sospette,
        COUNT(DISTINCT t.categoria) as categorie_spesa_diverse
    FROM clienti c
    JOIN conti_bancari cb ON c.cliente_id = cb.cliente_id
    JOIN transazioni t ON cb.conto_id = t.conto_id
    WHERE t.data_transazione >= DATE('now', '-365 days')  -- Ultimi 12 mesi
    GROUP BY c.cliente_id
),
conti_agg AS (
    -- Features dai conti bancari
    SELECT 
        cliente_id,
        COUNT(conto_id) as numero_conti,
        SUM(saldo) as saldo_totale,
        AVG(saldo) as saldo_medio,
        MAX(saldo) as saldo_massimo,
        MIN(saldo) as saldo_minimo,
        SUM(CASE WHEN tipo_conto = 'corrente' THEN 1 ELSE 0 END) as conti_correnti,
        SUM(CASE WHEN tipo_conto = 'risparmio' THEN 1 ELSE 0 END) as conti_risparmio
    FROM conti_bancari
    GROUP BY cliente_id
),
prestiti_agg AS (
    -- Features storiche prestiti
    SELECT 
        cliente_id,
        COUNT(prestito_id) as prestiti_totali,
        SUM(CASE WHEN stato = 'approvato' THEN 1 ELSE 0 END) as prestiti_approvati,
        SUM(CASE WHEN stato = 'rifiutato' THEN 1 ELSE 0 END) as prestiti_rifiutati,
        AVG(importo_richiesto) as importo_medio_richiesto,
        AVG(rischio_default) as rischio_default_medio,
        MAX(data_richiesta) as ultima_richiesta
    FROM prestiti
    GROUP BY cliente_id
)
-- Unione di tutte le features
SELECT 
    c.cliente_id,
    c.eta,
    c.stipendio_annuale,
    c.credit_score,
    c.occupazione,
    c.citta,
    
    -- Features transazioni
    COALESCE(ta.totale_transazioni, 0) as transazioni_ultimo_anno,
    COALESCE(ta.totale_uscite, 0) as totale_uscite,
    COALESCE(ta.totale_entrate, 0) as totale_entrate,
    COALESCE(ta.media_uscite, 0) as media_uscite,
    COALESCE(ta.media_entrate, 0) as media_entrate,
    COALESCE(ta.transazioni_sospette, 0) as transazioni_sospette,
    COALESCE(ta.categorie_spesa_diverse, 0) as categorie_spesa_diverse,
    
    -- Features conti
    COALESCE(ca.numero_conti, 0) as numero_conti,
    COALESCE(ca.saldo_totale, 0) as saldo_totale,
    COALESCE(ca.saldo_medio, 0) as saldo_medio,
    COALESCE(ca.saldo_massimo, 0) as saldo_massimo,
    COALESCE(ca.saldo_minimo, 0) as saldo_minimo,
    COALESCE(ca.conti_correnti, 0) as conti_correnti,
    COALESCE(ca.conti_risparmio, 0) as conti_risparmio,
    
    -- Features prestiti
    COALESCE(pa.prestiti_totali, 0) as prestiti_totali,
    COALESCE(pa.prestiti_approvati, 0) as prestiti_approvati,
    COALESCE(pa.prestiti_rifiutati, 0) as prestiti_rifiutati,
    COALESCE(pa.importo_medio_richiesto, 0) as importo_medio_richiesto,
    COALESCE(pa.rischio_default_medio, 0) as rischio_default_medio,
    
    -- Calcolo features derivate
    CASE 
        WHEN COALESCE(ta.totale_entrate, 0) > 0 
        THEN ROUND(COALESCE(ta.totale_uscite, 0) / ta.totale_entrate, 4)
        ELSE 0 
    END as rapporto_uscite_entrate,
    
    CASE 
        WHEN c.stipendio_annuale > 0 
        THEN ROUND(COALESCE(ta.totale_uscite, 0) / c.stipendio_annuale, 4)
        ELSE 0 
    END as rapporto_uscite_stipendio,
    
    CASE 
        WHEN COALESCE(ca.saldo_totale, 0) > 0 
        THEN ROUND(COALESCE(ta.totale_uscite, 0) / ca.saldo_totale, 4)
        ELSE 0 
    END as rapporto_uscite_saldo
    
FROM clienti c
LEFT JOIN transazioni_agg ta ON c.cliente_id = ta.cliente_id
LEFT JOIN conti_agg ca ON c.cliente_id = ca.cliente_id
LEFT JOIN prestiti_agg pa ON c.cliente_id = pa.cliente_id
WHERE c.eta BETWEEN 18 AND 100  -- Filtro dati errati
"""

# Esegui query complessa
print("⏳ Esecuzione query complessa di feature engineering...")
df_features = pd.read_sql_query(query_features, conn)

print(f"✅ Dataset features estratto con successo!")
print(f"   • Righe: {df_features.shape[0]:,}")
print(f"   • Colonne: {df_features.shape[1]}")
print(f"   • Dimensioni: {df_features.memory_usage(deep=True).sum() / 1024 / 1024:.2f} MB")

# Mostra prime righe
print(f"\n📋 ANTEPRIMA DATASET FEATURES:")
print(df_features.head().to_string())

# 3.3 Statistiche dataset
print(f"\n📊 STATISTICHE FEATURES PRINCIPALI:")
stats_cols = ['eta', 'stipendio_annuale', 'credit_score', 'saldo_totale', 
              'transazioni_ultimo_anno', 'rapporto_uscite_entrate']

print(df_features[stats_cols].describe().round(2).to_string())

# 3.4 Query per target variable (per modello supervisionato)
print(f"\n🎯 PREPARAZIONE TARGET VARIABLE PER MODELLO ML:")

query_target = """
SELECT 
    p.cliente_id,
    CASE 
        WHEN p.stato = 'approvato' AND p.rischio_default > 0.5 THEN 1
        WHEN p.stato = 'rifiutato' THEN 1
        ELSE 0 
    END as rischio_alto,
    p.rischio_default,
    p.stato,
    p.importo_richiesto
FROM prestiti p
WHERE p.data_richiesta >= DATE('now', '-365 days')
"""

df_target = pd.read_sql_query(query_target, conn)

# Unisci features con target
df_ml = pd.merge(df_features, df_target, on='cliente_id', how='left')
df_ml['rischio_alto'] = df_ml['rischio_alto'].fillna(0).astype(int)

print(f"✅ Dataset ML pronto!")
print(f"   • Campioni totali: {df_ml.shape[0]:,}")
print(f"   • Features: {df_ml.shape[1] - 2}")  # Escludi cliente_id e rischio_alto
print(f"   • Distribuzione target (rischio_alto):")
print(df_ml['rischio_alto'].value_counts().to_string())
print(f"   • Percentuale rischio alto: {df_ml['rischio_alto'].mean()*100:.1f}%")

# Salva dataset per ML
df_ml.to_csv('dataset_ml_rischio_credito.csv', index=False)
print(f"\n💾 Dataset salvato come 'dataset_ml_rischio_credito.csv'")

print("\n✅ Feature engineering con SQL completato!")
print("   Abbiamo estratto e trasformato dati direttamente dal database")

# 4. MODELLO MACHINE LEARNING SU DATI DATABASE
print("🤖 ADDESTRAMENTO MODELLO ML PER RISCHIO CREDITIZIO")
print("="*60)

print("Utilizziamo i dati estratti dal database per addestrare un modello predittivo")
print("che predice il rischio di default sui prestiti.")

# 4.1 Preparazione dati per ML
print(f"\n🔧 PREPARAZIONE DATI PER MODELLO ML...")

# Seleziona features e target
features = [
    'eta', 'stipendio_annuale', 'credit_score', 
    'transazioni_ultimo_anno', 'totale_uscite', 'totale_entrate',
    'media_uscite', 'media_entrate', 'transazioni_sospette',
    'categorie_spesa_diverse', 'numero_conti', 'saldo_totale',
    'saldo_medio', 'saldo_massimo', 'saldo_minimo',
    'conti_correnti', 'conti_risparmio', 'prestiti_totali',
    'prestiti_approvati', 'prestiti_rifiutati', 'importo_medio_richiesto',
    'rapporto_uscite_entrate', 'rapporto_uscite_stipendio', 'rapporto_uscite_saldo'
]

# Features categoriche (da encodare)
cat_features = ['occupazione', 'citta']

# Target
target = 'rischio_alto'

# Dataset completo
X = df_ml[features + cat_features].copy()
y = df_ml[target].copy()

print(f"   • Numero di features: {len(features) + len(cat_features)}")
print(f"   • Numero di samples: {len(X):,}")
print(f"   • Distribuzione target: {y.value_counts().to_dict()}")

# 4.2 Preprocessing
print(f"\n🔄 PREPROCESSING DATI...")

# Encoding features categoriche
label_encoders = {}
for col in cat_features:
    le = LabelEncoder()
    X[col] = le.fit_transform(X[col].astype(str))
    label_encoders[col] = le
    print(f"   • Encoded '{col}' con {len(le.classes_)} categorie")

# Gestione valori NaN (dovuti a LEFT JOIN)
X = X.fillna(0)

# Standardizzazione features numeriche
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

print(f"   • Features scaled: {X_scaled.shape}")

# 4.3 Split dataset
print(f"\n🎯 SPLIT DATASET TRAINING/TEST...")

X_train, X_test, y_train, y_test = train_test_split(
    X_scaled, y, 
    test_size=0.3, 
    random_state=42,
    stratify=y  # Mantieni distribuzione target
)

print(f"   • Training set: {X_train.shape[0]:,} samples")
print(f"   • Test set: {X_test.shape[0]:,} samples")
print(f"   • Training positive: {y_train.mean()*100:.1f}%")
print(f"   • Test positive: {y_test.mean()*100:.1f}%")

# 4.4 Addestramento modello
print(f"\n🚀 ADDESTRAMENTO MODELLO RANDOM FOREST...")

model_rf = RandomForestClassifier(
    n_estimators=100,
    max_depth=10,
    min_samples_split=5,
    min_samples_leaf=2,
    random_state=42,
    n_jobs=-1,
    class_weight='balanced'  # Importante per dataset sbilanciato
)

model_rf.fit(X_train, y_train)

print(f"✅ Modello addestrato con successo!")
print(f"   • Numero alberi: {model_rf.n_estimators}")
print(f"   • Profondità massima: {model_rf.max_depth}")

# 4.5 Valutazione modello
print(f"\n📊 VALUTAZIONE PERFORMANCE MODELLO...")

# Predizioni
y_pred = model_rf.predict(X_test)
y_pred_proba = model_rf.predict_proba(X_test)[:, 1]

# Metriche
from sklearn.metrics import accuracy_score, precision_score, recall_score, f1_score, roc_auc_score

accuracy = accuracy_score(y_test, y_pred)
precision = precision_score(y_test, y_pred)
recall = recall_score(y_test, y_pred)
f1 = f1_score(y_test, y_pred)
roc_auc = roc_auc_score(y_test, y_pred_proba)

print(f"🎯 METRICHE DI PERFORMANCE:")
print(f"   • Accuracy: {accuracy:.4f} ({accuracy*100:.1f}%)")
print(f"   • Precision: {precision:.4f} (dei predetti positivi, quanti sono veri positivi)")
print(f"   • Recall: {recall:.4f} (dei veri positivi, quanti abbiamo trovato)")
print(f"   • F1-Score: {f1:.4f} (media armonica precision/recall)")
print(f"   • ROC-AUC: {roc_auc:.4f} (capacità discriminativa)")

# Report di classificazione dettagliato
print(f"\n📄 REPORT CLASSIFICAZIONE DETTAGLIATO:")
print(classification_report(y_test, y_pred, target_names=['Rischio Basso', 'Rischio Alto']))

# 4.6 Feature importance
print(f"\n🏆 FEATURE IMPORTANCE (quali features influenzano di più il modello):")

feature_importance = pd.DataFrame({
    'feature': X.columns,
    'importance': model_rf.feature_importances_
}).sort_values('importance', ascending=False)

print(feature_importance.head(15).to_string(index=False))

# Visualizzazione feature importance
plt.figure(figsize=(12, 8))
top_features = feature_importance.head(15)
plt.barh(top_features['feature'], top_features['importance'])
plt.xlabel('Importance')
plt.title('Top 15 Feature Importance - Modello Rischio Creditizio')
plt.gca().invert_yaxis()
plt.grid(axis='x', alpha=0.3)
plt.tight_layout()
plt.show()

# 4.7 Matrice confusione
print(f"\n🎨 VISUALIZZAZIONE MATRICE CONFUSIONE...")

from sklearn.metrics import ConfusionMatrixDisplay

fig, ax = plt.subplots(1, 2, figsize=(15, 6))

# Matrice confusione
ConfusionMatrixDisplay.from_estimator(model_rf, X_test, y_test, 
                                       display_labels=['Basso', 'Alto'],
                                       cmap='Blues', ax=ax[0])
ax[0].set_title('Matrice Confusione')

# Curva ROC
from sklearn.metrics import RocCurveDisplay
RocCurveDisplay.from_estimator(model_rf, X_test, y_test, ax=ax[1])
ax[1].plot([0, 1], [0, 1], 'k--', label='Random Classifier')
ax[1].set_title('Curva ROC')
ax[1].legend()

plt.tight_layout()
plt.show()

# 4.8 Salvataggio modello e preprocessing
print(f"\n💾 SALVATAGGIO MODELLO E PIPELINE...")

# Salva modello
joblib.dump(model_rf, 'modello_rischio_credito.pkl')
print(f"✅ Modello salvato come 'modello_rischio_credito.pkl'")

# Salva scaler
joblib.dump(scaler, 'scaler_rischio_credito.pkl')
print(f"✅ Scaler salvato come 'scaler_rischio_credito.pkl'")

# Salva label encoders
joblib.dump(label_encoders, 'label_encoders_rischio_credito.pkl')
print(f"✅ Label encoders salvati")

# Salva lista features
feature_info = {
    'features': features,
    'cat_features': cat_features,
    'target': target,
    'feature_importance': feature_importance.to_dict()
}

with open('feature_info.json', 'w') as f:
    json.dump(feature_info, f, indent=2)

print(f"✅ Informazioni features salvate come 'feature_info.json'")

print(f"\n🎉 MODELLO ML ADDESTRATO CON SUCCESSO!")
print(f"   Performance: {accuracy*100:.1f}% accuracy")
print(f"   Il modello può ora predire il rischio di default basandosi sui dati del database")

# 5. PIPELINE PRODUZIONE: DATABASE → ML → DATABASE
print("🏭 PIPELINE PRODUZIONE COMPLETA DATABASE-ML")
print("="*60)

print("Simulazione sistema reale che:")
print("1. Legge nuovi dati dal database")
print("2. Applica preprocessing e feature engineering")
print("3. Esegue predizioni con modello ML")
print("4. Salva risultati nel database per utilizzo applicazioni")

# 5.1 Caricamento modello e preprocessing salvati
print(f"\n📦 CARICAMENTO MODELLO E COMPONENTI SALVATI...")

try:
    model = joblib.load('modello_rischio_credito.pkl')
    scaler = joblib.load('scaler_rischio_credito.pkl')
    label_encoders = joblib.load('label_encoders_rischio_credito.pkl')
    
    with open('feature_info.json', 'r') as f:
        feature_info = json.load(f)
    
    print(f"✅ Modello e componenti caricati con successo!")
    print(f"   • Modello: RandomForest con {model.n_estimators} alberi")
    print(f"   • Features: {len(feature_info['features'])} numeriche, {len(feature_info['cat_features'])} categoriche")
    
except Exception as e:
    print(f"❌ Errore caricamento: {e}")
    print("⚠️  Utilizzeremo il modello appena addestrato")
    model = model_rf

# 5.2 Funzione per estrarre e preparare nuovi dati
print(f"\n🔍 FUNZIONE PER ESTRAZIONE NUOVI DATI DAL DATABASE...")

def estrai_dati_cliente(cliente_id, conn):
    """
    Estrae dati per un singolo cliente dal database
    e applica lo stesso feature engineering usato in training
    """
    
    # Query per estrarre dati cliente specifico
    query_cliente = f"""
    WITH transazioni_cliente AS (
        SELECT 
            c.cliente_id,
            COUNT(t.transazione_id) as totale_transazioni,
            SUM(CASE WHEN t.tipo = 'uscita' THEN t.importo ELSE 0 END) as totale_uscite,
            SUM(CASE WHEN t.tipo = 'entrata' THEN t.importo ELSE 0 END) as totale_entrate,
            AVG(CASE WHEN t.tipo = 'uscita' THEN t.importo ELSE NULL END) as media_uscite,
            AVG(CASE WHEN t.tipo = 'entrata' THEN t.importo ELSE NULL END) as media_entrate,
            SUM(CASE WHEN t.rischio_frode = 1 THEN 1 ELSE 0 END) as transazioni_sospette,
            COUNT(DISTINCT t.categoria) as categorie_spesa_diverse
        FROM clienti c
        JOIN conti_bancari cb ON c.cliente_id = cb.cliente_id
        JOIN transazioni t ON cb.conto_id = t.conto_id
        WHERE c.cliente_id = {cliente_id}
          AND t.data_transazione >= DATE('now', '-365 days')
        GROUP BY c.cliente_id
    ),
    conti_cliente AS (
        SELECT 
            cliente_id,
            COUNT(conto_id) as numero_conti,
            SUM(saldo) as saldo_totale,
            AVG(saldo) as saldo_medio,
            MAX(saldo) as saldo_massimo,
            MIN(saldo) as saldo_minimo,
            SUM(CASE WHEN tipo_conto = 'corrente' THEN 1 ELSE 0 END) as conti_correnti,
            SUM(CASE WHEN tipo_conto = 'risparmio' THEN 1 ELSE 0 END) as conti_risparmio
        FROM conti_bancari
        WHERE cliente_id = {cliente_id}
        GROUP BY cliente_id
    ),
    prestiti_cliente AS (
        SELECT 
            cliente_id,
            COUNT(prestito_id) as prestiti_totali,
            SUM(CASE WHEN stato = 'approvato' THEN 1 ELSE 0 END) as prestiti_approvati,
            SUM(CASE WHEN stato = 'rifiutato' THEN 1 ELSE 0 END) as prestiti_rifiutati,
            AVG(importo_richiesto) as importo_medio_richiesto,
            AVG(rischio_default) as rischio_default_medio
        FROM prestiti
        WHERE cliente_id = {cliente_id}
        GROUP BY cliente_id
    )
    SELECT 
        c.cliente_id,
        c.eta,
        c.stipendio_annuale,
        c.credit_score,
        c.occupazione,
        c.citta,
        
        -- Features transazioni
        COALESCE(tc.totale_transazioni, 0) as transazioni_ultimo_anno,
        COALESCE(tc.totale_uscite, 0) as totale_uscite,
        COALESCE(tc.totale_entrate, 0) as totale_entrate,
        COALESCE(tc.media_uscite, 0) as media_uscite,
        COALESCE(tc.media_entrate, 0) as media_entrate,
        COALESCE(tc.transazioni_sospette, 0) as transazioni_sospette,
        COALESCE(tc.categorie_spesa_diverse, 0) as categorie_spesa_diverse,
        
        -- Features conti
        COALESCE(cc.numero_conti, 0) as numero_conti,
        COALESCE(cc.saldo_totale, 0) as saldo_totale,
        COALESCE(cc.saldo_medio, 0) as saldo_medio,
        COALESCE(cc.saldo_massimo, 0) as saldo_massimo,
        COALESCE(cc.saldo_minimo, 0) as saldo_minimo,
        COALESCE(cc.conti_correnti, 0) as conti_correnti,
        COALESCE(cc.conti_risparmio, 0) as conti_risparmio,
        
        -- Features prestiti
        COALESCE(pc.prestiti_totali, 0) as prestiti_totali,
        COALESCE(pc.prestiti_approvati, 0) as prestiti_approvati,
        COALESCE(pc.prestiti_rifiutati, 0) as prestiti_rifiutati,
        COALESCE(pc.importo_medio_richiesto, 0) as importo_medio_richiesto,
        COALESCE(pc.rischio_default_medio, 0) as rischio_default_medio,
        
        -- Features derivate
        CASE 
            WHEN COALESCE(tc.totale_entrate, 0) > 0 
            THEN ROUND(COALESCE(tc.totale_uscite, 0) / tc.totale_entrate, 4)
            ELSE 0 
        END as rapporto_uscite_entrate,
        
        CASE 
            WHEN c.stipendio_annuale > 0 
            THEN ROUND(COALESCE(tc.totale_uscite, 0) / c.stipendio_annuale, 4)
            ELSE 0 
        END as rapporto_uscite_stipendio,
        
        CASE 
            WHEN COALESCE(cc.saldo_totale, 0) > 0 
            THEN ROUND(COALESCE(tc.totale_uscite, 0) / cc.saldo_totale, 4)
            ELSE 0 
        END as rapporto_uscite_saldo
        
    FROM clienti c
    LEFT JOIN transazioni_cliente tc ON c.cliente_id = tc.cliente_id
    LEFT JOIN conti_cliente cc ON c.cliente_id = cc.cliente_id
    LEFT JOIN prestiti_cliente pc ON c.cliente_id = pc.cliente_id
    WHERE c.cliente_id = {cliente_id}
    """
    
    df_cliente = pd.read_sql_query(query_cliente, conn)
    return df_cliente

# 5.3 Funzione per predire rischio cliente
def predici_rischio_cliente(cliente_id, conn, model, scaler, label_encoders, feature_info):
    """
    Pipeline completa: estrae dati, preprocessa, predice
    """
    # 1. Estrai dati cliente
    df_cliente = estrai_dati_cliente(cliente_id, conn)
    
    if df_cliente.empty:
        return {"errore": f"Cliente {cliente_id} non trovato"}
    
    # 2. Prepara features nello stesso ordine del training
    features = feature_info['features']
    cat_features = feature_info['cat_features']
    
    X = df_cliente[features + cat_features].copy()
    
    # 3. Encoding features categoriche
    for col in cat_features:
        if col in label_encoders:
            # Per nuovi valori non visti in training, usa "unknown"
            X[col] = X[col].apply(lambda x: x if str(x) in label_encoders[col].classes_ else 'unknown')
            X[col] = label_encoders[col].transform(X[col].astype(str))
        else:
            X[col] = 0
    
    # 4. Gestione NaN
    X = X.fillna(0)
    
    # 5. Standardizzazione
    X_scaled = scaler.transform(X)
    
    # 6. Predizione
    probabilita_rischio = model.predict_proba(X_scaled)[0, 1]  # Probabilità classe positiva
    predizione = 1 if probabilita_rischio > 0.5 else 0
    
    # 7. Interpretazione
    if probabilita_rischio > 0.7:
        livello_rischio = "ALTO"
        raccomandazione = "Richiedi documentazione aggiuntiva"
    elif probabilita_rischio > 0.3:
        livello_rischio = "MEDIO"
        raccomandazione = "Verifica approfondita consigliata"
    else:
        livello_rischio = "BASSO"
        raccomandazione = "Prestito approvabile"
    
    return {
        'cliente_id': cliente_id,
        'predizione': predizione,
        'probabilita_rischio': float(probabilita_rischio),
        'livello_rischio': livello_rischio,
        'raccomandazione': raccomandazione,
        'data_predizione': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    }

# 5.4 Test pipeline su clienti campione
print(f"\n🧪 TEST PIPELINE SU CLIENTI CAMPIONE...")

# Seleziona alcuni clienti casuali per test
clienti_test = np.random.choice(range(1, 101), 5, replace=False)

print("\n🔮 PREDIZIONI RISCHIO PER CLIENTI CAMPIONE:")
print("-" * 80)

for cliente_id in clienti_test:
    risultato = predici_rischio_cliente(
        cliente_id, conn, model, scaler, label_encoders, feature_info
    )
    
    if 'errore' in risultato:
        print(f"Cliente {cliente_id}: {risultato['errore']}")
    else:
        print(f"\n👤 Cliente ID: {risultato['cliente_id']}")
        print(f"   • Probabilità rischio: {risultato['probabilita_rischio']:.3f}")
        print(f"   • Livello rischio: {risultato['livello_rischio']}")
        print(f"   • Raccomandazione: {risultato['raccomandazione']}")
        print(f"   • Data predizione: {risultato['data_predizione']}")

# 5.5 Creazione tabella per salvare predizioni
print(f"\n💾 CREAZIONE TABELLA DATABASE PER SALVARE PREDIZIONI...")

create_predizioni_table = """
CREATE TABLE IF NOT EXISTS predizioni_rischio (
    predizione_id INTEGER PRIMARY KEY AUTOINCREMENT,
    cliente_id INTEGER,
    probabilita_rischio DECIMAL(5,4),
    livello_rischio TEXT CHECK(livello_rischio IN ('BASSO', 'MEDIO', 'ALTO')),
    raccomandazione TEXT,
    data_predizione DATETIME,
    modello_utilizzato TEXT DEFAULT 'random_forest_v1',
    FOREIGN KEY (cliente_id) REFERENCES clienti(cliente_id)
)
"""

cursor.execute(create_predizioni_table)
print("✅ Tabella 'predizioni_rischio' creata")

# 5.6 Funzione per salvare predizioni nel database
def salva_predizione_db(risultato_predizione, cursor):
    """
    Salva risultato predizione nel database
    """
    query_insert = """
    INSERT INTO predizioni_rischio 
    (cliente_id, probabilita_rischio, livello_rischio, raccomandazione, data_predizione)
    VALUES (?, ?, ?, ?, ?)
    """
    
    cursor.execute(query_insert, (
        risultato_predizione['cliente_id'],
        risultato_predizione['probabilita_rischio'],
        risultato_predizione['livello_rischio'],
        risultato_predizione['raccomandazione'],
        risultato_predizione['data_predizione']
    ))
    
    cursor.connection.commit()
    return cursor.lastrowid

# 5.7 Batch prediction e salvataggio
print(f"\n📦 BATCH PREDICTION PER 50 CLIENTI E SALVATAGGIO NEL DATABASE...")

clienti_batch = np.random.choice(range(1, 1001), 50, replace=False)
predizioni_salvate = []

for i, cliente_id in enumerate(clienti_batch):
    if i % 10 == 0:
        print(f"   Processati {i}/50 clienti...")
    
    risultato = predici_rischio_cliente(
        cliente_id, conn, model, scaler, label_encoders, feature_info
    )
    
    if 'errore' not in risultato:
        predizione_id = salva_predizione_db(risultato, cursor)
        risultato['predizione_id'] = predizione_id
        predizioni_salvate.append(risultato)

print(f"✅ {len(predizioni_salvate)} predizioni salvate nel database")

# 5.8 Query per verificare predizioni salvate
print(f"\n📋 VERIFICA PREDIZIONI SALVATE NEL DATABASE...")

query_verifica = """
SELECT 
    livello_rischio,
    COUNT(*) as numero_predizioni,
    ROUND(AVG(probabilita_rischio), 3) as probabilita_media
FROM predizioni_rischio
GROUP BY livello_rischio
ORDER BY numero_predizioni DESC
"""

df_predizioni = pd.read_sql_query(query_verifica, conn)
print(df_predizioni.to_string(index=False))

# 5.9 Query per clienti ad alto rischio con dettagli
print(f"\n🚨 CLIENTI AD ALTO RISCHIO (per follow-up):")

query_alto_rischio = """
SELECT 
    p.cliente_id,
    c.nome || ' ' || c.cognome as cliente,
    c.credit_score,
    ROUND(c.stipendio_annuale, 2) as stipendio,
    p.probabilita_rischio,
    p.livello_rischio,
    p.raccomandazione,
    p.data_predizione
FROM predizioni_rischio p
JOIN clienti c ON p.cliente_id = c.cliente_id
WHERE p.livello_rischio = 'ALTO'
ORDER BY p.probabilita_rischio DESC
LIMIT 10
"""

df_alto_rischio = pd.read_sql_query(query_alto_rischio, conn)
print(df_alto_rischio.to_string(index=False))

# 5.10 Visualizzazione distribuzione predizioni
print(f"\n📊 VISUALIZZAZIONE DISTRIBUZIONE PREDIZIONI...")

fig, axes = plt.subplots(1, 2, figsize=(15, 6))

# Distribuzione livelli rischio
distribuzione = df_predizioni.set_index('livello_rischio')['numero_predizioni']
colors = {'BASSO': 'green', 'MEDIO': 'orange', 'ALTO': 'red'}
bar_colors = [colors.get(x, 'gray') for x in distribuzione.index]

axes[0].bar(distribuzione.index, distribuzione.values, color=bar_colors)
axes[0].set_title('Distribuzione Livelli Rischio Predetti')
axes[0].set_ylabel('Numero Clienti')
axes[0].grid(axis='y', alpha=0.3)

# Probabilità media per livello rischio
axes[1].bar(df_predizioni['livello_rischio'], df_predizioni['probabilita_media'], 
           color=bar_colors)
axes[1].set_title('Probabilità Media per Livello Rischio')
axes[1].set_ylabel('Probabilità Rischio')
axes[1].set_ylim(0, 1)
axes[1].grid(axis='y', alpha=0.3)

plt.tight_layout()
plt.show()

print(f"\n🎉 PIPELINE PRODUZIONE COMPLETATA!")
print(f"   Sistema funzionante: Database → Feature Engineering → ML → Database")
print(f"   Predizioni salvate e pronte per utilizzo da applicazioni business")

# 6. DATABASE PRODUCTION: POSTGRESQL & MYSQL
print("🏢 CONNESSIONI DATABASE PRODUZIONE E BEST PRACTICES")
print("="*60)

print("In ambiente di produzione si usano database server come PostgreSQL o MySQL")
print("Implementiamo connessioni sicure, connection pooling, e query ottimizzate.")

# 6.1 Configurazione connessione PostgreSQL (esempio)
print(f"\n🔗 CONNESSIONE POSTGRESQL (ESEMPIO PRODUZIONE):")

postgresql_config = {
    'host': 'localhost',           # O indirizzo server remoto
    'port': 5432,                  # Porta default PostgreSQL
    'database': 'ai_database',     # Nome database
    'user': 'ai_user',             # Utente dedicato
    'password': 'secure_password', # Password (mai hardcodare in produzione!)
    'sslmode': 'require'           # Connessione sicura
}

# Template per connessione PostgreSQL
postgresql_connection_template = """
# Con SQLAlchemy (raccomandato per produzione)
from sqlalchemy import create_engine
import psycopg2

# Stringa connessione
connection_string = (
    f"postgresql+psycopg2://{postgresql_config['user']}:"
    f"{postgresql_config['password']}@{postgresql_config['host']}:"
    f"{postgresql_config['port']}/{postgresql_config['database']}"
)

# Crea engine con connection pooling
engine = create_engine(
    connection_string,
    pool_size=10,           # Numero massimo connessioni nel pool
    max_overflow=20,        # Connessioni aggiuntive se necessario
    pool_timeout=30,        # Timeout per ottenere connessione
    pool_recycle=3600       # Ricicla connessioni ogni ora
)

# Utilizzo
try:
    conn = engine.connect()
    df = pd.read_sql_query("SELECT * FROM clienti LIMIT 10", conn)
    conn.close()
except Exception as e:
    print(f"Errore connessione: {e}")
"""

print(postgresql_connection_template)

# 6.2 Configurazione connessione MySQL
print(f"\n🔗 CONNESSIONE MYSQL (ESEMPIO PRODUZIONE):")

mysql_config = {
    'host': 'localhost',
    'port': 3306,
    'database': 'ai_database',
    'user': 'ai_user',
    'password': 'secure_password',
    'charset': 'utf8mb4'
}

mysql_connection_template = """
# Con SQLAlchemy per MySQL
from sqlalchemy import create_engine
import pymysql

# Stringa connessione MySQL
connection_string = (
    f"mysql+pymysql://{mysql_config['user']}:"
    f"{mysql_config['password']}@{mysql_config['host']}:"
    f"{mysql_config['port']}/{mysql_config['database']}"
    f"?charset={mysql_config['charset']}"
)

engine = create_engine(
    connection_string,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True  # Importante per MySQL
)

# Best practice: context manager per connessioni
with engine.connect() as conn:
    df = pd.read_sql_query("SELECT * FROM clienti", conn)
    # Le transazioni sono automaticamente gestite
"""

print(mysql_connection_template)

# 6.3 Best Practices per Database in Produzione
print(f"\n🏆 BEST PRACTICES DATABASE PER AI IN PRODUZIONE:")

best_practices = """
1. 🔐 SICUREZZA:
   • Mai hardcodare password in codice
   • Usa variabili d'ambiente o secret manager
   • Connessioni SSL/TLS obbligatorie
   • Utenti con permessi minimi necessari

2. 📊 PERFORMANCE:
   • Index sulle colonne usate in WHERE, JOIN, ORDER BY
   • Partitioning per tabelle grandi (>10M righe)
   • Query ottimizzate (EXPLAIN ANALYZE)
   • Connection pooling per ridurre overhead

3. 🧹 MANUTENZIONE:
   • Backup automatizzati
   • Monitoraggio spazio disco
   • Logging query lente
   • Pulizia dati storici

4. 🔄 VERSIONING:
   • Migrazioni database versionate
   • Rollback plan per ogni cambiamento
   • Test su ambiente staging prima di produzione

5. 📈 SCALABILITÀ:
   • Read replicas per carico query
   • Sharding per dataset molto grandi
   • Caching layer (Redis) per query frequenti
"""

print(best_practices)

# 6.4 Ottimizzazione Query per Big Data
print(f"\n⚡ OTTIMIZZAZIONE QUERY PER BIG DATA AI:")

optimization_tips = """
🔍 QUERY OPTIMIZATION TECHNIQUES:

1. INDEX STRATEGY:
   • CREATE INDEX idx_cliente_credit ON clienti(credit_score);
   • CREATE INDEX idx_transazioni_data ON transazioni(data_transazione);
   • Composite indexes per query frequenti

2. PARTITIONING (PostgreSQL/MySQL):
   -- Partiziona per data (per query temporali)
   CREATE TABLE transazioni_partitioned (
       ... 
   ) PARTITION BY RANGE (DATE(data_transazione));

3. MATERIALIZED VIEWS per features frequenti:
   CREATE MATERIALIZED VIEW features_clienti AS
   SELECT cliente_id, ... -- query complessa
   REFRESH MATERIALIZED VIEW CONCURRENTLY features_clienti;

4. QUERY OPTIMIZATION:
   • Usa EXPLAIN ANALYZE per debug performance
   • Evita SELECT * (seleziona solo colonne necessarie)
   • Usa LIMIT per query esplorative
   • Batch inserts invece di singoli INSERT

5. CONNECTION MANAGEMENT:
   • Connection pooling
   • Timeout configurabili
   • Retry logic per connessioni transient failures
"""

print(optimization_tips)

# 6.5 Esempio Query Ottimizzata per ML Pipeline
print(f"\n🎯 QUERY OTTIMIZZATA PER ML PIPELINE PRODUZIONE:")

optimized_query_example = """
-- Query ottimizzata con index e filtri precisi
-- Assumi index: clienti(credit_score), transazioni(data_transazione, conto_id)

EXPLAIN ANALYZE  -- Per analisi performance
SELECT 
    c.cliente_id,
    c.credit_score,
    c.stipendio_annuale,
    
    -- Subquery aggregata efficiente
    (
        SELECT COUNT(*) 
        FROM transazioni t
        JOIN conti_bancari cb ON t.conto_id = cb.conto_id
        WHERE cb.cliente_id = c.cliente_id
          AND t.data_transazione >= CURRENT_DATE - INTERVAL '1 year'
          AND t.tipo = 'uscita'
    ) as uscite_ultimo_anno,
    
    -- Window function invece di self-join
    AVG(t.importo) OVER (
        PARTITION BY c.cliente_id
        ORDER BY t.data_transazione
        ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
    ) as media_mobile_30gg
    
FROM clienti c
LEFT JOIN conti_bancari cb ON c.cliente_id = cb.cliente_id
LEFT JOIN transazioni t ON cb.conto_id = t.conto_id
WHERE c.credit_score BETWEEN 300 AND 850
  AND c.data_registrazione >= '2020-01-01'
  AND t.data_transazione >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY c.cliente_id, c.credit_score, c.stipendio_annuale
HAVING COUNT(t.transazione_id) > 10  -- Solo clienti attivi
ORDER BY c.credit_score DESC
LIMIT 1000;  -- Batch size gestibile
"""

print(optimized_query_example)

# 6.6 Monitoring e Logging
print(f"\n📊 MONITORING E LOGGING PER DATABASE AI:")

monitoring_code = """
# Setup logging per query database
import logging
from sqlalchemy import event
from sqlalchemy.engine import Engine
import time

# Configura logging
logging.basicConfig()
logger = logging.getLogger("sqlalchemy.engine")
logger.setLevel(logging.INFO)

# Log query lente (>1 secondo)
@event.listens_for(Engine, "before_cursor_execute")
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault('query_start_time', []).append(time.time())

@event.listens_for(Engine, "after_cursor_execute")
def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    total = time.time() - conn.info['query_start_time'].pop(-1)
    if total > 1.0:  # Query lenta > 1 secondo
        logger.warning(
            f"⚠️  Query lenta ({total:.2f}s): {statement[:200]}... "
            f"Params: {parameters}"
        )

# Monitoraggio performance
def monitor_database_performance(engine):
    '''Monitora metriche database'''
    with engine.connect() as conn:
        # Query attive
        active_queries = pd.read_sql_query("""
            SELECT pid, query, state, age(clock_timestamp(), query_start) as duration
            FROM pg_stat_activity 
            WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%'
            ORDER BY duration DESC
        """, conn)
        
        # Utilizzo spazio
        disk_usage = pd.read_sql_query("""
            SELECT schemaname, tablename, 
                   pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
            FROM pg_tables 
            ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
            LIMIT 10
        """, conn)
        
    return active_queries, disk_usage
"""

print(monitoring_code)

# 6.7 Backup e Recovery
print(f"\n💾 BACKUP E RECOVERY STRATEGY:")

backup_strategy = """
# Script backup automatizzato
import subprocess
from datetime import datetime
import boto3  # Per backup su cloud

def backup_database(config, backup_dir='./backups'):
    '''Esegue backup database'''
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_file = f"{backup_dir}/backup_{timestamp}.sql"
    
    # Comando pg_dump per PostgreSQL
    cmd = [
        'pg_dump',
        '-h', config['host'],
        '-p', str(config['port']),
        '-U', config['user'],
        '-d', config['database'],
        '-f', backup_file,
        '-F', 'c'  # Formato custom (compresso)
    ]
    
    # Set password via environment
    env = {'PGPASSWORD': config['password']}
    
    try:
        subprocess.run(cmd, env=env, check=True)
        print(f"✅ Backup completato: {backup_file}")
        
        # Upload to cloud (optional)
        # upload_to_s3(backup_file, 'ai-database-backups')
        
        return backup_file
    except subprocess.CalledProcessError as e:
        print(f"❌ Errore backup: {e}")
        return None

# Schedule backup giornaliero
# Usa cron (Linux) o Task Scheduler (Windows)
# 0 2 * * * python /path/to/backup_script.py  # Ogni giorno alle 2 AM
"""

print(backup_strategy)

# 6.8 Conclusione e Prossimi Passi
print(f"\n🎉 ESERCITAZIONE COMPLETATA!")
print("="*60)
print(f"🏆 COMPETENZE ACQUISITE:")
print(f"  1. ✅ Connessione Python a database SQL (SQLite, PostgreSQL, MySQL)")
print(f"  2. ✅ Query SQL avanzate per feature engineering AI")
print(f"  3. ✅ Estrarre e preparare dati per modelli ML direttamente dal DB")
print(f"  4. ✅ Pipeline produzione: Database → ML → Database")
print(f"  5. ✅ Ottimizzazione performance per big data")
print(f"  6. ✅ Best practices sicurezza e manutenzione")

print(f"\n🚀 ARCHITETTURA PRODUZIONE IMPLEMENTATA:")
print("""
    ┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
    │                 │    │                 │    │                 │
    │   DATABASE      │───▶│   PYTHON ML     │───▶│   RISULTATI     │
    │   PostgreSQL    │    │   Pipeline      │    │   nel DB        │
    │                 │◀───│                 │    │                 │
    └─────────────────┘    └─────────────────┘    └─────────────────┘
         Raw Data           Feature Engineering        Predictions
                              Model Training           Business Use
""")

print(f"\n📚 PROSSIMI ARGOMENTI AVANZATI:")
print("  • Real-time streaming con Apache Kafka + Database")
print("  • Vector databases per AI (ChromaDB, Pinecone)")
print("  • Data lakes (AWS S3, Delta Lake) per big data AI")
print("  • MLOps: CI/CD per modelli ML")
print("  • Feature stores per ML features management")

print(f"\n💡 CONSIGLI FINALI:")
print("  • Inizia sempre con SQLite per prototipazione")
print("  • Migra a PostgreSQL quando necessario")
print("  • Documenta tutte le query e trasformazioni")
print("  • Testa performance con dataset reali")
print("  • Monitora e ottimizza continuamente")

print(f"\n🎯 ORA SEI PRONTO PER:")
print("  • Lavorare come Data Engineer in progetti AI reali")
print("  • Costruire pipeline dati end-to-end")
print("  • Ottimizzare database per applicazioni ML")
print("  • Gestire dataset di produzione per modelli AI")

print(f"\n🌟 RICORDA: In AI, i dati sono il nuovo petrolio!")
print("   E tu sai come estrarlo, raffinarlo e trasformarlo in valore!")