"""
SQL Queries for Data Intelligence Report
OPTIMIZED: All queries use base tables (vanzari, vanzari_detalii) with Oracle hints
           for index usage (IDX_VANZARI_NR) instead of views (fact_vfacturi2, fact_vfacturi_detalii).
           Other views: vstoc, vrul are still used where appropriate.

IMPORTANT: Price calculation considers pret_cu_tva flag:
- If pret_cu_tva = 1: price includes VAT, must divide by (1 + proc_tvav/100)
- If pret_cu_tva = 0: price is already without VAT
Formula: CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END
"""

# =============================================================================
# 1. MARJA PER CLIENT (OPTIMIZAT - folosește tabele de bază + indexuri)
# =============================================================================
MARJA_PER_CLIENT = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    f.id_part,
    p.denumire AS client,
    p.cod_fiscal,
    COUNT(DISTINCT f.id_vanzare) AS nr_facturi,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * d.pret_achizitie), 2) AS cost_total,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
WHERE f.sters = 0
  AND f.tip > 0
  AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
GROUP BY f.id_part, p.denumire, p.cod_fiscal
ORDER BY marja_bruta DESC
"""

# =============================================================================
# 2. CLIENȚI CU MARJĂ MICĂ (sub prag) - OPTIMIZAT
# =============================================================================
CLIENTI_MARJA_MICA = """
SELECT * FROM (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        p.denumire AS client,
        p.cod_fiscal,
        ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
        ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
        ROUND(
            CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
            THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
            ELSE 0 END
        , 2) AS procent_marja
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
    GROUP BY f.id_part, p.denumire, p.cod_fiscal
    HAVING SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > :min_sales
)
WHERE procent_marja < :margin_threshold
ORDER BY vanzari_fara_tva DESC
"""

# =============================================================================
# 3. MARJA PER CATEGORIE (Grupă + Subgrupă) - OPTIMIZAT
# =============================================================================
MARJA_PER_CATEGORIE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    NVL(sg.grupa, 'NECLASIFICAT') AS grupa,
    NVL(sg.subgrupa, 'NECLASIFICAT') AS subgrupa,
    COUNT(DISTINCT f.id_vanzare) AS nr_facturi,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * d.pret_achizitie), 2) AS cost_total,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_articole art ON d.id_articol = art.id_articol
LEFT JOIN vgest_art_sbgr sg ON art.id_subgrupa = sg.id_subgrupa
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
GROUP BY sg.id_grupa, sg.grupa, art.id_subgrupa, sg.subgrupa
ORDER BY vanzari_fara_tva DESC
"""

# =============================================================================
# 4. PRODUCȚIE PROPRIE vs MARFĂ REVÂNDUTĂ - OPTIMIZAT
# =============================================================================
PRODUCTIE_VS_REVANZARE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    CASE
        WHEN d.cont IN ('341', '345') THEN 'Producție proprie'
        WHEN d.cont = '301' THEN 'Materii prime'
        ELSE 'Marfă revândută'
    END AS tip_produs,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * d.pret_achizitie), 2) AS cost_total,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
GROUP BY CASE
    WHEN d.cont IN ('341', '345') THEN 'Producție proprie'
    WHEN d.cont = '301' THEN 'Materii prime'
    ELSE 'Marfă revândută'
END
ORDER BY vanzari_fara_tva DESC
"""

# =============================================================================
# 5. DISPERSIA PREȚURILOR PER PRODUS
# =============================================================================
DISPERSIE_PRETURI = """
WITH preturi_detalii AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        d.id_articol,
        a.denumire,
        g.subgrupa,
        f.id_part,
        p.denumire as client,
        CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS pret_fara_tva,
        d.cantitate,
        MIN(CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) OVER (PARTITION BY d.id_articol) AS pret_min_global
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    LEFT JOIN nom_articole a ON d.id_articol = a.id_articol
    LEFT JOIN gest_art_sbgr g ON a.id_subgrupa = g.id_subgrupa
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
      AND d.pret > 0
)
SELECT
    denumire AS produs,
    NVL(subgrupa, 'NECLASIFICAT') AS subgrupa,
    COUNT(DISTINCT id_part) AS nr_clienti,
    COUNT(*) AS nr_tranzactii,
    ROUND(MIN(pret_fara_tva), 2) AS pret_minim,
    ROUND(MAX(pret_fara_tva), 2) AS pret_maxim,
    ROUND(AVG(pret_fara_tva), 2) AS pret_mediu,
    ROUND((MAX(pret_fara_tva) - MIN(pret_fara_tva)) * 100.0 / NULLIF(AVG(pret_fara_tva), 0), 2) AS variatie_procent,
    SUM(CASE WHEN pret_fara_tva = pret_min_global THEN 1 ELSE 0 END) AS nr_la_pret_min,
    MAX(CASE WHEN pret_fara_tva = pret_min_global THEN client END) AS client_pret_min
FROM preturi_detalii
GROUP BY id_articol, denumire, subgrupa
HAVING COUNT(DISTINCT id_part) >= 3
   AND MAX(pret_fara_tva) > MIN(pret_fara_tva) * 1.2
ORDER BY variatie_procent DESC
FETCH FIRST 50 ROWS ONLY
"""

# =============================================================================
# 6. CLIENȚI CARE CUMPĂRĂ SUB MEDIE
# =============================================================================
# OPTIMIZED: Single base CTE scans once, derives both aggregations from it
CLIENTI_SUB_MEDIE = """
WITH base_prices AS (
    SELECT
        d.id_articol,
        f.id_part,
        p.denumire AS client,
        CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS pret_fara_tva,
        d.cantitate
    FROM VANZARI f
    JOIN VANZARI_DETALII d ON d.id_vanzare = f.id_vanzare
    JOIN NOM_PARTENERI p ON f.id_part = p.id_part
    WHERE f.sters = 0 AND d.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
      AND d.pret > 0
),
preturi_medii AS (
    SELECT id_articol, AVG(pret_fara_tva) AS pret_mediu
    FROM base_prices
    GROUP BY id_articol
),
preturi_client AS (
    SELECT id_articol, id_part, client, AVG(pret_fara_tva) AS pret_client, SUM(cantitate) AS cantitate_totala
    FROM base_prices
    GROUP BY id_articol, id_part, client
)
SELECT
    a.denumire AS produs,
    pc.client,
    ROUND(pc.pret_client, 2) AS pret_platit,
    ROUND(pm.pret_mediu, 2) AS pret_mediu,
    ROUND((pm.pret_mediu - pc.pret_client) * 100.0 / pm.pret_mediu, 2) AS discount_vs_medie,
    pc.cantitate_totala
FROM preturi_client pc
JOIN preturi_medii pm ON pm.id_articol = pc.id_articol
JOIN NOM_ARTICOLE a ON a.id_articol = pc.id_articol
WHERE pc.pret_client < pm.pret_mediu * 0.85
ORDER BY discount_vs_medie DESC
FETCH FIRST 100 ROWS ONLY
"""

# =============================================================================
# 7. VÂNZĂRI SUB COST (ALERTĂ CRITICĂ) - OPTIMIZAT
# =============================================================================
VANZARI_SUB_COST = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    f.data_act,
    f.serie_act || ' ' || f.numar_act AS factura,
    p.denumire AS client,
    NVL2(d.id_articol, art.denumire, d.explicatie) AS produs,
    d.cantitate,
    ROUND(CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END, 2) AS pret_vanzare,
    ROUND(d.pret_achizitie, 2) AS cost,
    ROUND((CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie) * d.cantitate, 2) AS pierdere
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
LEFT JOIN nom_articole art ON d.id_articol = art.id_articol
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
  AND d.pret_achizitie > 0
  AND d.pret > 0
  AND CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END < d.pret_achizitie
ORDER BY pierdere ASC
FETCH FIRST 100 ROWS ONLY
"""

# =============================================================================
# 8. TRENDING CLIENȚI YoY
# =============================================================================
TRENDING_CLIENTI = """
WITH vanzari_perioade AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        p.denumire AS client,
        SUM(CASE WHEN f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
            THEN d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END ELSE 0 END) AS vanzari_an_curent,
        SUM(CASE WHEN f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
                 AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -12)
            THEN d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END ELSE 0 END) AS vanzari_an_trecut
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
    GROUP BY f.id_part, p.denumire
)
SELECT
    client,
    ROUND(vanzari_an_curent, 2) AS vanzari_12_luni,
    ROUND(vanzari_an_trecut, 2) AS vanzari_an_anterior,
    ROUND(
        CASE WHEN vanzari_an_trecut > 0
        THEN (vanzari_an_curent - vanzari_an_trecut) * 100.0 / vanzari_an_trecut
        ELSE NULL END
    , 2) AS variatie_procent,
    CASE
        WHEN vanzari_an_trecut = 0 AND vanzari_an_curent > 0 THEN 'CLIENT NOU'
        WHEN vanzari_an_curent = 0 AND vanzari_an_trecut > 0 THEN 'PIERDUT'
        WHEN vanzari_an_curent > vanzari_an_trecut * 1.2 THEN 'CREȘTERE'
        WHEN vanzari_an_curent < vanzari_an_trecut * 0.8 THEN 'SCĂDERE'
        ELSE 'STABIL'
    END AS trend
FROM vanzari_perioade
WHERE vanzari_an_curent > 0 OR vanzari_an_trecut > 0
ORDER BY variatie_procent DESC NULLS LAST
"""

# =============================================================================
# 9. CONCENTRARE CLIENȚI (TOP 30 + REST)
# =============================================================================
CONCENTRARE_CLIENTI = """
WITH total_vanzari AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS total
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
),
vanzari_client AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        p.denumire AS client,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari,
        ROW_NUMBER() OVER (ORDER BY SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) DESC) AS rn
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
    GROUP BY f.id_part, p.denumire
),
top_clienti AS (
    SELECT
        vc.client,
        ROUND(vc.vanzari, 2) AS vanzari,
        ROUND(vc.vanzari * 100.0 / tv.total, 2) AS procent_din_total,
        vc.rn
    FROM vanzari_client vc
    CROSS JOIN total_vanzari tv
    WHERE vc.rn <= 30
),
rest_clienti AS (
    SELECT
        'ALȚI CLIENȚI' AS client,
        ROUND(SUM(vc.vanzari), 2) AS vanzari,
        ROUND(SUM(vc.vanzari) * 100.0 / tv.total, 2) AS procent_din_total,
        31 AS rn
    FROM vanzari_client vc
    CROSS JOIN total_vanzari tv
    WHERE vc.rn > 30
    GROUP BY tv.total
    HAVING SUM(vc.vanzari) > 0
)
SELECT
    client,
    vanzari,
    procent_din_total,
    SUM(procent_din_total) OVER (ORDER BY rn) AS procent_cumulat
FROM (
    SELECT * FROM top_clienti
    UNION ALL
    SELECT * FROM rest_clienti
)
ORDER BY rn
"""

# =============================================================================
# 10. VÂNZĂRI LUNARE (SEZONALITATE)
# =============================================================================
# OPTIMIZED: Use base tables instead of views (removes 29 unnecessary JOINs)
VANZARI_LUNARE = """
SELECT
    TO_CHAR(f.data_act, 'YYYY-MM') AS luna,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    COUNT(DISTINCT f.id_vanzare) AS nr_facturi,
    COUNT(DISTINCT f.id_part) AS nr_clienti
FROM VANZARI f
JOIN VANZARI_DETALII d ON d.id_vanzare = f.id_vanzare
WHERE f.sters = 0 AND d.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
GROUP BY TO_CHAR(f.data_act, 'YYYY-MM')
ORDER BY luna
"""

# =============================================================================
# 11. STOC CURENT PER GESTIUNE
# =============================================================================
STOC_CURENT = """
SELECT
    s.nume_gestiune,
    CASE WHEN g.nr_pag = 7 THEN 'Marfă preț vânzare' ELSE 'Marfă preț achiziție' END AS tip_gestiune,
    s.grupa,
    s.subgrupa,
    COUNT(DISTINCT s.id_articol) AS nr_articole,
    ROUND(SUM((s.cants + s.cant - s.cante) * s.pret), 2) AS valoare_stoc_achizitie,
    CASE
        WHEN g.nr_pag = 7 THEN ROUND(SUM((s.cants + s.cant - s.cante) * s.pretv), 2)
        ELSE NULL
    END AS valoare_stoc_vanzare
FROM vstoc s
JOIN nom_gestiuni g ON s.id_gestiune = g.id_gestiune
WHERE s.an = EXTRACT(YEAR FROM SYSDATE)
  AND s.luna = EXTRACT(MONTH FROM SYSDATE)
  AND (s.cants + s.cant - s.cante) > 0
GROUP BY s.id_gestiune, s.nume_gestiune, g.nr_pag, s.id_grupa, s.grupa, s.id_subgrupa, s.subgrupa
ORDER BY valoare_stoc_achizitie DESC
"""

# =============================================================================
# 12. STOC LENT (fără mișcare > 90 zile)
# =============================================================================
STOC_LENT = """
SELECT
    s.denumire AS produs,
    s.nume_gestiune,
    (s.cants + s.cant - s.cante) AS cantitate,
    ROUND((s.cants + s.cant - s.cante) * s.pret, 2) AS valoare,
    s.dataout AS ultima_iesire,
    ROUND(SYSDATE - NVL(s.dataout, s.datain)) AS zile_fara_miscare
FROM vstoc s
WHERE s.an = EXTRACT(YEAR FROM SYSDATE)
  AND s.luna = EXTRACT(MONTH FROM SYSDATE)
  AND (s.cants + s.cant - s.cante) > 0
  AND (s.dataout IS NULL OR s.dataout < SYSDATE - 90)
  AND (s.cants + s.cant - s.cante) * s.pret > 100
ORDER BY zile_fara_miscare DESC NULLS FIRST
FETCH FIRST 100 ROWS ONLY
"""

# =============================================================================
# 13. ROTAȚIE STOCURI
# =============================================================================
ROTATIE_STOCURI = """
WITH vanzari_articole AS (
    SELECT
        r.id_articol,
        SUM(ABS(r.cante) * r.pret) AS valoare_vanzari
    FROM vrul r
    JOIN vanzari v ON r.id_fact = v.id_fact
    WHERE r.id_tip_rulaj = 0
      AND r.cante <> 0
      AND r.dataact >= ADD_MONTHS(TRUNC(SYSDATE), -12)
    GROUP BY r.id_articol
),
stoc_curent AS (
    SELECT
        s.id_articol,
        s.denumire,
        s.nume_gestiune,
        SUM((s.cants + s.cant - s.cante) * s.pret) AS valoare_stoc
    FROM vstoc s
    WHERE s.an = EXTRACT(YEAR FROM SYSDATE)
      AND s.luna = EXTRACT(MONTH FROM SYSDATE)
      AND (s.cants + s.cant - s.cante) > 0
    GROUP BY s.id_articol, s.denumire, s.nume_gestiune
)
SELECT
    sc.denumire AS produs,
    sc.nume_gestiune,
    ROUND(sc.valoare_stoc, 2) AS valoare_stoc,
    ROUND(NVL(va.valoare_vanzari, 0), 2) AS vanzari_12_luni,
    ROUND(
        CASE WHEN sc.valoare_stoc > 0
        THEN NVL(va.valoare_vanzari, 0) / sc.valoare_stoc
        ELSE 0 END
    , 2) AS rotatie,
    ROUND(
        CASE WHEN NVL(va.valoare_vanzari, 0) > 0
        THEN sc.valoare_stoc * 365 / va.valoare_vanzari
        ELSE 9999 END
    , 0) AS zile_stoc
FROM stoc_curent sc
LEFT JOIN vanzari_articole va ON va.id_articol = sc.id_articol
WHERE sc.valoare_stoc > 500
ORDER BY rotatie ASC NULLS FIRST
FETCH FIRST 100 ROWS ONLY
"""

# =============================================================================
# 14. ANALIZĂ PRĂJITORIE - MATERII PRIME vs OUTPUT
# =============================================================================
ANALIZA_PRAJITORIE = """
SELECT
    TO_CHAR(r.dataact, 'YYYY-MM') AS luna,
    CASE
        WHEN r.cont = '301' THEN 'Materii prime'
        WHEN r.cont = '341' THEN 'Semifabricate'
        WHEN r.cont = '345' THEN 'Produse finite'
        ELSE 'Altele'
    END AS tip,
    -- Intrari: cantitate > 0 AND cante = 0
    ROUND(SUM(CASE WHEN r.cant > 0 AND NVL(r.cante, 0) = 0 THEN r.cant ELSE 0 END), 2) AS cantitate_intrari,
    ROUND(SUM(CASE WHEN r.cant > 0 AND NVL(r.cante, 0) = 0 THEN r.cant * NVL(r.pret, 0) ELSE 0 END), 2) AS valoare_intrari,
    -- Iesiri: cant = 0 AND cante > 0
    ROUND(SUM(CASE WHEN NVL(r.cant, 0) = 0 AND r.cante > 0 THEN r.cante ELSE 0 END), 2) AS cantitate_iesiri,
    ROUND(SUM(CASE WHEN NVL(r.cant, 0) = 0 AND r.cante > 0 THEN r.cante * NVL(r.pret, 0) ELSE 0 END), 2) AS valoare_iesiri,
    -- Transformari: cant > 0 AND cante > 0 (intrare si iesire simultan)
    ROUND(SUM(CASE WHEN r.cant > 0 AND r.cante > 0 THEN r.cant ELSE 0 END), 2) AS cantitate_transformari_in,
    ROUND(SUM(CASE WHEN r.cant > 0 AND r.cante > 0 THEN r.cante ELSE 0 END), 2) AS cantitate_transformari_out,
    -- Sold net
    ROUND(SUM(NVL(r.cant, 0) - NVL(r.cante, 0)), 2) AS sold_net_cantitate,
    ROUND(SUM((NVL(r.cant, 0) - NVL(r.cante, 0)) * NVL(r.pret, 0)), 2) AS sold_net_valoare
FROM vrul r
WHERE r.cont IN ('301', '341', '345')
  AND r.dataact >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
GROUP BY TO_CHAR(r.dataact, 'YYYY-MM'),
         CASE WHEN r.cont = '301' THEN 'Materii prime'
              WHEN r.cont = '341' THEN 'Semifabricate'
              WHEN r.cont = '345' THEN 'Produse finite'
              ELSE 'Altele' END
ORDER BY luna, tip
"""

# =============================================================================
# 15. SUMAR EXECUTIV - KPIs
# =============================================================================
# OPTIMIZED: Single CTE scans base tables once instead of 6 separate view scans (29 JOINs each)
SUMAR_EXECUTIV = """
WITH base_data AS (
    SELECT
        f.id_vanzare,
        f.id_part,
        d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS vanzare_fara_tva,
        d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie) AS marja
    FROM VANZARI f
    JOIN VANZARI_DETALII d ON d.id_vanzare = f.id_vanzare
    WHERE f.sters = 0 AND d.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
),
aggregated AS (
    SELECT
        SUM(vanzare_fara_tva) AS total_vanzari,
        SUM(marja) AS total_marja,
        COUNT(DISTINCT id_vanzare) AS nr_facturi,
        COUNT(DISTINCT id_part) AS nr_clienti
    FROM base_data
)
SELECT
    'Vânzări totale (fără TVA)' AS indicator,
    TO_CHAR(ROUND(total_vanzari, 0), '999,999,999') AS valoare,
    'RON' AS um
FROM aggregated

UNION ALL

SELECT
    'Marja brută totală' AS indicator,
    TO_CHAR(ROUND(total_marja, 0), '999,999,999') AS valoare,
    'RON' AS um
FROM aggregated

UNION ALL

SELECT
    'Procent marjă medie' AS indicator,
    TO_CHAR(ROUND(total_marja * 100.0 / NULLIF(total_vanzari, 0), 2), '990.99') AS valoare,
    '%' AS um
FROM aggregated

UNION ALL

SELECT
    'Număr facturi' AS indicator,
    TO_CHAR(nr_facturi, '999,999') AS valoare,
    'buc' AS um
FROM aggregated

UNION ALL

SELECT
    'Număr clienți activi' AS indicator,
    TO_CHAR(nr_clienti, '999,999') AS valoare,
    'buc' AS um
FROM aggregated

UNION ALL

SELECT
    'Valoare stoc total' AS indicator,
    TO_CHAR(ROUND(SUM(s.cant * s.pret), 0), '999,999,999') AS valoare,
    'RON' AS um
FROM vstoc s
WHERE s.cant > 0
"""

# =============================================================================
# 16. TOP PRODUSE DUPĂ VÂNZĂRI
# =============================================================================
TOP_PRODUSE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    NVL2(d.id_articol, a.denumire, d.explicatie) AS produs,
    NVL(g.subgrupa, 'NECLASIFICAT') AS subgrupa,
    a.um,
    ROUND(SUM(d.cantitate), 2) AS cantitate_vanduta,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS valoare_vanzari,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_articole a ON d.id_articol = a.id_articol
LEFT JOIN gest_art_sbgr g ON a.id_subgrupa = g.id_subgrupa
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
GROUP BY d.id_articol, NVL2(d.id_articol, a.denumire, d.explicatie), g.subgrupa, a.um
ORDER BY valoare_vanzari DESC
FETCH FIRST 50 ROWS ONLY
"""

# =============================================================================
# 17. MARJA PER GESTIUNE (doar articole gestionabile) - OPTIMIZAT
# =============================================================================
MARJA_PER_GESTIUNE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    g.nume_gestiune,
    COUNT(DISTINCT f.id_vanzare) AS nr_facturi,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_gestiuni g ON d.id_gestiune = g.id_gestiune
JOIN nom_articole a ON d.id_articol = a.id_articol
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
  AND NVL(a.in_stoc, 1) = 1
GROUP BY d.id_gestiune, g.nume_gestiune
ORDER BY vanzari_fara_tva DESC
"""

# =============================================================================
# 18. ARTICOLE NEGESTIONABILE (servicii, etc.) - OPTIMIZAT
# =============================================================================
ARTICOLE_NEGESTIONABILE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    NVL(art.denumire, d.explicatie) AS denumire,
    NVL(sg.subgrupa, 'NECLASIFICAT') AS subgrupa,
    art.um,
    COUNT(DISTINCT f.id_vanzare) AS nr_facturi,
    ROUND(SUM(d.cantitate), 2) AS cantitate_vanduta,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari_fara_tva,
    ROUND(SUM(d.cantitate * d.pret_achizitie), 2) AS cost_total,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja_bruta,
    ROUND(
        CASE WHEN SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 0
        THEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 / SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END)
        ELSE 0 END
    , 2) AS procent_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_articole art ON d.id_articol = art.id_articol
LEFT JOIN vgest_art_sbgr sg ON art.id_subgrupa = sg.id_subgrupa
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
  AND NVL(art.in_stoc, 0) = 0
GROUP BY d.id_articol, art.denumire, d.explicatie, sg.subgrupa, art.um
ORDER BY vanzari_fara_tva DESC
"""

# =============================================================================
# 19. SOLDURI CLIENTI (Creanțe din cont 4111)
# =============================================================================
SOLDURI_CLIENTI = """
SELECT
    p.denumire AS client,
    p.cod_fiscal,
    -- Sold direct: pozitiv = creanta, negativ = avans/storno/retur
    ROUND(SUM(b.solddeb - b.soldcred), 2) AS sold_curent,
    CASE
        WHEN SUM(b.solddeb - b.soldcred) > 0 THEN 'Creanta'
        WHEN SUM(b.solddeb - b.soldcred) < 0 THEN 'Avans/Storno'
        ELSE 'Sold zero'
    END AS tip_sold
FROM vbalanta_parteneri b
JOIN vnom_parteneri p ON b.id_part = p.id_part
WHERE b.cont LIKE '4111%'
  AND b.an = EXTRACT(YEAR FROM SYSDATE)
  AND b.luna = EXTRACT(MONTH FROM SYSDATE)
GROUP BY p.id_part, p.denumire, p.cod_fiscal
HAVING SUM(b.solddeb - b.soldcred) <> 0
ORDER BY sold_curent DESC
"""

# =============================================================================
# 20. SOLDURI FURNIZORI (Datorii din cont 401)
# =============================================================================
SOLDURI_FURNIZORI = """
SELECT
    p.denumire AS furnizor,
    p.cod_fiscal,
    -- Sold direct: pozitiv = datorie, negativ = avans/storno
    ROUND(SUM(b.soldcred - b.solddeb), 2) AS sold_curent,
    CASE
        WHEN SUM(b.soldcred - b.solddeb) > 0 THEN 'Datorie'
        WHEN SUM(b.soldcred - b.solddeb) < 0 THEN 'Avans/Storno'
        ELSE 'Sold zero'
    END AS tip_sold
FROM vbalanta_parteneri b
JOIN vnom_parteneri p ON b.id_part = p.id_part
WHERE b.cont LIKE '401%'
  AND b.an = EXTRACT(YEAR FROM SYSDATE)
  AND b.luna = EXTRACT(MONTH FROM SYSDATE)
GROUP BY p.id_part, p.denumire, p.cod_fiscal
HAVING SUM(b.soldcred - b.solddeb) <> 0
ORDER BY sold_curent DESC
"""

# =============================================================================
# 21. AGING CREANTE (Buckets pentru creanțe - din vireg_parteneri)
# =============================================================================
# FIX: Filter by last closed calendar period to avoid duplicate invoices
AGING_CREANTE = """
WITH ultima_luna AS (
    SELECT anul, luna FROM calendar ORDER BY anul DESC, luna DESC FETCH FIRST 1 ROW ONLY
),
solduri_clienti AS (
    SELECT
        r.id_part,
        r.nume AS client,
        r.serie_act || ' ' || r.nract AS nr_factura,
        r.dataact AS data_factura,
        r.datascad,
        (r.precdeb + r.debit) AS valoare_factura,
        (r.preccred + r.credit) AS valoare_achitata,
        (r.precdeb + r.debit) - (r.preccred + r.credit) AS sold_ramas,
        CASE
            WHEN r.datascad IS NULL THEN 0
            WHEN r.datascad >= TRUNC(SYSDATE) THEN 0
            ELSE ROUND(TRUNC(SYSDATE) - r.datascad)
        END AS zile_restante
    FROM vireg_parteneri r, ultima_luna ul
    WHERE (r.cont LIKE '4111%' OR r.cont LIKE '461%')
      AND (r.precdeb + r.debit) - (r.preccred + r.credit) <> 0
      AND r.an = ul.anul AND r.luna = ul.luna
)
SELECT
    client,
    ROUND(SUM(CASE WHEN zile_restante <= 0 THEN sold_ramas ELSE 0 END), 2) AS neajuns_scadenta,
    ROUND(SUM(CASE WHEN zile_restante > 0 AND zile_restante <= 30 THEN sold_ramas ELSE 0 END), 2) AS zile_1_30,
    ROUND(SUM(CASE WHEN zile_restante > 30 AND zile_restante <= 60 THEN sold_ramas ELSE 0 END), 2) AS zile_31_60,
    ROUND(SUM(CASE WHEN zile_restante > 60 AND zile_restante <= 90 THEN sold_ramas ELSE 0 END), 2) AS zile_61_90,
    ROUND(SUM(CASE WHEN zile_restante > 90 THEN sold_ramas ELSE 0 END), 2) AS peste_90_zile,
    ROUND(SUM(sold_ramas), 2) AS total_sold
FROM solduri_clienti
GROUP BY id_part, client
HAVING SUM(sold_ramas) <> 0
ORDER BY total_sold DESC
"""

# =============================================================================
# 22. FACTURI RESTANTE CLIENTI (Depășite scadența - din vireg_parteneri)
# =============================================================================
# FIX: Filter by last closed calendar period to avoid duplicate invoices
FACTURI_RESTANTE = """
WITH ultima_luna AS (
    SELECT anul, luna FROM calendar ORDER BY anul DESC, luna DESC FETCH FIRST 1 ROW ONLY
)
SELECT
    r.nume AS client,
    r.serie_act || ' ' || r.nract AS nr_factura,
    r.dataact AS data_factura,
    r.datascad AS data_scadenta,
    ROUND(TRUNC(SYSDATE) - r.datascad) AS zile_intarziere,
    ROUND((r.precdeb + r.debit) - (r.preccred + r.credit), 2) AS suma_restanta
FROM vireg_parteneri r, ultima_luna ul
WHERE (r.cont LIKE '4111%' OR r.cont LIKE '461%')
  AND (r.precdeb + r.debit) - (r.preccred + r.credit) <> 0
  AND r.datascad IS NOT NULL
  AND r.datascad < TRUNC(SYSDATE)
  AND r.an = ul.anul AND r.luna = ul.luna
ORDER BY zile_intarziere DESC
FETCH FIRST 100 ROWS ONLY
"""


# =============================================================================
# 22b. AGING DATORII FURNIZORI (din vireg_parteneri)
# =============================================================================
# FIX: Filter by last closed calendar period to avoid duplicate invoices
AGING_DATORII = """
WITH ultima_luna AS (
    SELECT anul, luna FROM calendar ORDER BY anul DESC, luna DESC FETCH FIRST 1 ROW ONLY
),
solduri_furnizori AS (
    SELECT
        r.id_part,
        r.nume AS furnizor,
        r.serie_act || ' ' || r.nract AS nr_factura,
        r.dataact AS data_factura,
        r.datascad,
        (r.preccred + r.credit) AS valoare_factura,
        (r.precdeb + r.debit) AS valoare_achitata,
        (r.preccred + r.credit) - (r.precdeb + r.debit) AS sold_ramas,
        CASE
            WHEN r.datascad IS NULL THEN 0
            WHEN r.datascad >= TRUNC(SYSDATE) THEN 0
            ELSE ROUND(TRUNC(SYSDATE) - r.datascad)
        END AS zile_restante
    FROM vireg_parteneri r, ultima_luna ul
    WHERE (r.cont LIKE '401%' OR r.cont LIKE '404%' OR r.cont LIKE '462%')
      AND (r.preccred + r.credit) - (r.precdeb + r.debit) <> 0
      AND r.an = ul.anul AND r.luna = ul.luna
)
SELECT
    furnizor,
    ROUND(SUM(CASE WHEN zile_restante <= 0 THEN sold_ramas ELSE 0 END), 2) AS neajuns_scadenta,
    ROUND(SUM(CASE WHEN zile_restante > 0 AND zile_restante <= 30 THEN sold_ramas ELSE 0 END), 2) AS zile_1_30,
    ROUND(SUM(CASE WHEN zile_restante > 30 AND zile_restante <= 60 THEN sold_ramas ELSE 0 END), 2) AS zile_31_60,
    ROUND(SUM(CASE WHEN zile_restante > 60 AND zile_restante <= 90 THEN sold_ramas ELSE 0 END), 2) AS zile_61_90,
    ROUND(SUM(CASE WHEN zile_restante > 90 THEN sold_ramas ELSE 0 END), 2) AS peste_90_zile,
    ROUND(SUM(sold_ramas), 2) AS total_sold
FROM solduri_furnizori
GROUP BY id_part, furnizor
HAVING SUM(sold_ramas) <> 0
ORDER BY total_sold DESC
"""

# =============================================================================
# 22c. FACTURI RESTANTE FURNIZORI (Depășite scadența)
# =============================================================================
# FIX: Filter by last closed calendar period to avoid duplicate invoices
FACTURI_RESTANTE_FURNIZORI = """
WITH ultima_luna AS (
    SELECT anul, luna FROM calendar ORDER BY anul DESC, luna DESC FETCH FIRST 1 ROW ONLY
)
SELECT
    r.nume AS furnizor,
    r.serie_act || ' ' || r.nract AS nr_factura,
    r.dataact AS data_factura,
    r.datascad AS data_scadenta,
    ROUND(TRUNC(SYSDATE) - r.datascad) AS zile_intarziere,
    ROUND((r.preccred + r.credit) - (r.precdeb + r.debit), 2) AS suma_restanta
FROM vireg_parteneri r, ultima_luna ul
WHERE (r.cont LIKE '401%' OR r.cont LIKE '404%' OR r.cont LIKE '462%')
  AND (r.preccred + r.credit) - (r.precdeb + r.debit) <> 0
  AND r.datascad IS NOT NULL
  AND r.datascad < TRUNC(SYSDATE)
  AND r.an = ul.anul AND r.luna = ul.luna
ORDER BY zile_intarziere DESC
FETCH FIRST 100 ROWS ONLY
"""
# =============================================================================
# 23. DSO / DPO (Zile medii încasare/plată)
# Folosește jurnalele de TVA pentru vânzări/achiziții fără TVA
# =============================================================================
DSO_DPO = """
WITH vanzari_12_luni AS (
    -- Vânzări fără TVA din ultimele 12 luni (jurnal vânzări vjv2025)
    -- Filtrare: an/luna în interval + dataireg în anul/luna respectivă
    SELECT SUM(
        -- Baze impozabile cu TVA (ro*b)
        NVL(ro24b, 0) + NVL(ro21b, 0) + NVL(ro20b, 0) + NVL(ro19b, 0) +
        NVL(ro11b, 0) + NVL(ro9b, 0) + NVL(ro5b, 0) +
        -- Baze neimpozabile (ro*nb)
        NVL(ro24nb, 0) + NVL(ro21nb, 0) + NVL(ro20nb, 0) + NVL(ro19nb, 0) +
        NVL(ro11nb, 0) + NVL(ro9nb, 0) + NVL(ro5nb, 0) +
        -- Operațiuni fără TVA
        NVL(roti, 0) + NVL(cescdd1, 0) + NVL(cescdd2, 0) + NVL(ceoptr, 0) +
        NVL(cesvdd, 0) + NVL(cesvfdd, 0) + NVL(cesvfs, 0) +
        NVL(wrscdd, 0) + NVL(fodd, 0) + NVL(fofdd, 0) + NVL(wrscfdd, 0) + NVL(wrn, 0)
    ) AS total_vanzari
    FROM vjv2025
    WHERE (an * 12 + luna) BETWEEN
          (EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) * 12 + EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -12)))
          AND (EXTRACT(YEAR FROM SYSDATE) * 12 + EXTRACT(MONTH FROM SYSDATE))
      AND EXTRACT(YEAR FROM dataireg) = an
      AND EXTRACT(MONTH FROM dataireg) = luna
),
sold_clienti AS (
    -- Sold creanțe clienți (cont 4111)
    SELECT SUM(CASE WHEN b.solddeb > b.soldcred THEN b.solddeb - b.soldcred ELSE 0 END) AS total_creante
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '4111%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
achizitii_12_luni AS (
    -- Achiziții fără TVA din ultimele 12 luni (jurnal cumpărări vjc2025)
    SELECT SUM(
        -- Baze impozabile domestice (ro*b)
        NVL(ro24b, 0) + NVL(ro21b, 0) + NVL(ro20b, 0) + NVL(ro19b, 0) +
        NVL(ro11b, 0) + NVL(ro09b, 0) + NVL(ro05b, 0) +
        -- Baze neimpozabile (ro*nb)
        NVL(ro24nb, 0) + NVL(ro21nb, 0) + NVL(ro20nb, 0) + NVL(ro19nb, 0) +
        NVL(ro11nb, 0) + NVL(ro9nb, 0) + NVL(ro5nb, 0) +
        -- Furnizori (fo*b)
        NVL(fo24b, 0) + NVL(fo21b, 0) + NVL(fo20b, 0) + NVL(fo19b, 0) +
        NVL(fo11b, 0) + NVL(fo09b, 0) +
        -- Taxare inversă (ti*b)
        NVL(ti24b, 0) + NVL(ti21b, 0) + NVL(ti20b, 0) + NVL(ti19b, 0) +
        NVL(ti11b, 0) + NVL(ti09b, 0) +
        -- Achiziții intracomunitare (ce*b)
        NVL(ceb, 0) + NVL(cebb, 0) + NVL(cesb, 0) +
        -- Cross-border (xx*tib)
        NVL(xx19tib, 0) + NVL(xx21tib, 0) + NVL(xx11tib, 0) + NVL(xx9tib, 0) +
        -- Fără TVA
        NVL(cen, 0)
    ) AS total_achizitii
    FROM vjc2025
    WHERE (an * 12 + luna) BETWEEN
          (EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) * 12 + EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -12)))
          AND (EXTRACT(YEAR FROM SYSDATE) * 12 + EXTRACT(MONTH FROM SYSDATE))
      AND EXTRACT(YEAR FROM dataireg) = an
      AND EXTRACT(MONTH FROM dataireg) = luna
),
sold_furnizori AS (
    -- Sold datorii furnizori (cont 401)
    SELECT SUM(CASE WHEN b.soldcred > b.solddeb THEN b.soldcred - b.solddeb ELSE 0 END) AS total_datorii
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '401%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
)
SELECT
    'DSO (Zile incasare clienti)' AS indicator,
    ROUND(NVL(sc.total_creante, 0) * 365 / NULLIF(v.total_vanzari, 0), 0) AS zile,
    '(Creante clienti x 365) / Vanzari anuale (cont 4111 x 365 / jurnal vanzari vjv2025)' AS formula,
    CASE
        WHEN NVL(sc.total_creante, 0) * 365 / NULLIF(v.total_vanzari, 0) > 60 THEN 'ALERTA'
        WHEN NVL(sc.total_creante, 0) * 365 / NULLIF(v.total_vanzari, 0) > 45 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM vanzari_12_luni v, sold_clienti sc
UNION ALL
SELECT
    'DPO (Zile plata furnizori)' AS indicator,
    ROUND(NVL(sf.total_datorii, 0) * 365 / NULLIF(a.total_achizitii, 0), 0) AS zile,
    '(Datorii furnizori x 365) / Achizitii anuale (cont 401 x 365 / jurnal cumparari vjc2025)' AS formula,
    CASE
        WHEN NVL(sf.total_datorii, 0) * 365 / NULLIF(a.total_achizitii, 0) < 15 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM achizitii_12_luni a, sold_furnizori sf
"""

# =============================================================================
# 24. POZITIA CASH (Conturi 5121, 5311)
# =============================================================================
POZITIA_CASH = """
SELECT
    b.cont,
    CASE
        WHEN b.cont LIKE '5121%' THEN 'Conturi la banci in lei'
        WHEN b.cont LIKE '5124%' THEN 'Conturi la banci in valuta'
        WHEN b.cont LIKE '5311%' THEN 'Casa in lei'
        WHEN b.cont LIKE '5314%' THEN 'Casa in valuta'
        ELSE 'Alte disponibilitati'
    END AS descriere,
    ROUND(SUM(b.solddeb - b.soldcred), 2) AS sold_curent,
    CASE
        WHEN b.cont LIKE '512%' OR b.cont LIKE '531%' THEN 'RON'
        WHEN b.cont LIKE '5124%' OR b.cont LIKE '5314%' THEN 'VALUTA'
        ELSE 'RON'
    END AS valuta
FROM vbal b
WHERE (b.cont LIKE '512%' OR b.cont LIKE '531%')
  AND b.an = EXTRACT(YEAR FROM SYSDATE)
  AND b.luna = EXTRACT(MONTH FROM SYSDATE)
GROUP BY b.cont
HAVING SUM(b.solddeb - b.soldcred) <> 0
ORDER BY b.cont
"""

# =============================================================================
# 25. CICLU CONVERSIE CASH (DIO + DSO - DPO)
# Folosește vbal clasa 3xx pentru stoc, jurnale TVA pentru vânzări/achiziții
# =============================================================================
CICLU_CONVERSIE_CASH = """
WITH metrici AS (
    SELECT
        -- Stoc curent din balanța de verificare, clasa 3xx
        (SELECT SUM(b.solddeb - b.soldcred)
         FROM vbal b
         WHERE b.cont LIKE '3%'
           AND b.an = EXTRACT(YEAR FROM SYSDATE)
           AND b.luna = EXTRACT(MONTH FROM SYSDATE)) AS stoc_curent,
        -- COGS din facturi (preț achiziție articole vândute)
        (SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
                SUM(d.cantitate * d.pret_achizitie)
         FROM vanzari f
         JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
         WHERE f.sters = 0
           AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
           AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)) AS cogs_12_luni,
        -- Creanțe clienți
        (SELECT SUM(CASE WHEN b.solddeb > b.soldcred THEN b.solddeb - b.soldcred ELSE 0 END)
         FROM vbalanta_parteneri b
         WHERE b.cont LIKE '4111%'
           AND b.an = EXTRACT(YEAR FROM SYSDATE)
           AND b.luna = EXTRACT(MONTH FROM SYSDATE)) AS creante,
        -- Vânzări 12 luni din jurnal TVA vânzări (vjv2025)
        (SELECT SUM(
            -- Baze impozabile (ro*b)
            NVL(ro24b, 0) + NVL(ro21b, 0) + NVL(ro20b, 0) + NVL(ro19b, 0) +
            NVL(ro11b, 0) + NVL(ro9b, 0) + NVL(ro5b, 0) +
            -- Baze neimpozabile (ro*nb)
            NVL(ro24nb, 0) + NVL(ro21nb, 0) + NVL(ro20nb, 0) + NVL(ro19nb, 0) +
            NVL(ro11nb, 0) + NVL(ro9nb, 0) + NVL(ro5nb, 0) +
            -- Operațiuni fără TVA
            NVL(roti, 0) + NVL(cescdd1, 0) + NVL(cescdd2, 0) + NVL(ceoptr, 0) +
            NVL(cesvdd, 0) + NVL(cesvfdd, 0) + NVL(cesvfs, 0) +
            NVL(wrscdd, 0) + NVL(fodd, 0) + NVL(fofdd, 0) + NVL(wrscfdd, 0) + NVL(wrn, 0))
         FROM vjv2025
         WHERE (an * 12 + luna) BETWEEN
               (EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) * 12 + EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -12)))
               AND (EXTRACT(YEAR FROM SYSDATE) * 12 + EXTRACT(MONTH FROM SYSDATE))
           AND EXTRACT(YEAR FROM dataireg) = an
           AND EXTRACT(MONTH FROM dataireg) = luna) AS vanzari_12_luni,
        -- Datorii furnizori
        (SELECT SUM(CASE WHEN b.soldcred > b.solddeb THEN b.soldcred - b.solddeb ELSE 0 END)
         FROM vbalanta_parteneri b
         WHERE b.cont LIKE '401%'
           AND b.an = EXTRACT(YEAR FROM SYSDATE)
           AND b.luna = EXTRACT(MONTH FROM SYSDATE)) AS datorii_furnizori,
        -- Achiziții 12 luni din jurnal TVA cumpărări (vjc2025)
        (SELECT SUM(
            -- Baze impozabile domestice (ro*b)
            NVL(ro24b, 0) + NVL(ro21b, 0) + NVL(ro20b, 0) + NVL(ro19b, 0) +
            NVL(ro11b, 0) + NVL(ro09b, 0) + NVL(ro05b, 0) +
            -- Baze neimpozabile (ro*nb)
            NVL(ro24nb, 0) + NVL(ro21nb, 0) + NVL(ro20nb, 0) + NVL(ro19nb, 0) +
            NVL(ro11nb, 0) + NVL(ro9nb, 0) + NVL(ro5nb, 0) +
            -- Furnizori (fo*b)
            NVL(fo24b, 0) + NVL(fo21b, 0) + NVL(fo20b, 0) + NVL(fo19b, 0) +
            NVL(fo11b, 0) + NVL(fo09b, 0) +
            -- Taxare inversă (ti*b)
            NVL(ti24b, 0) + NVL(ti21b, 0) + NVL(ti20b, 0) + NVL(ti19b, 0) +
            NVL(ti11b, 0) + NVL(ti09b, 0) +
            -- Achiziții intracomunitare (ce*b)
            NVL(ceb, 0) + NVL(cebb, 0) + NVL(cesb, 0) +
            -- Cross-border (xx*tib)
            NVL(xx19tib, 0) + NVL(xx21tib, 0) + NVL(xx11tib, 0) + NVL(xx9tib, 0) +
            -- Fără TVA
            NVL(cen, 0))
         FROM vjc2025
         WHERE (an * 12 + luna) BETWEEN
               (EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) * 12 + EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -12)))
               AND (EXTRACT(YEAR FROM SYSDATE) * 12 + EXTRACT(MONTH FROM SYSDATE))
           AND EXTRACT(YEAR FROM dataireg) = an
           AND EXTRACT(MONTH FROM dataireg) = luna) AS achizitii_12_luni
    FROM dual
)
SELECT
    'DIO (Zile stoc)' AS indicator,
    ROUND(NVL(m.stoc_curent, 0) * 365 / NULLIF(m.cogs_12_luni, 0), 0) AS zile,
    '(Stoc x 365) / Cost marfuri vandute (clasa 3xx x 365 / cost achizitie 12 luni)' AS formula,
    'Zile medii pentru transformarea stocului in vanzare' AS explicatie
FROM metrici m
UNION ALL
SELECT
    'DSO (Zile incasare)' AS indicator,
    ROUND(NVL(m.creante, 0) * 365 / NULLIF(m.vanzari_12_luni, 0), 0) AS zile,
    '(Creante x 365) / Vanzari anuale (cont 4111 x 365 / jurnal vanzari vjv2025)' AS formula,
    'Zile medii pentru incasarea creantelor' AS explicatie
FROM metrici m
UNION ALL
SELECT
    'DPO (Zile plata)' AS indicator,
    ROUND(NVL(m.datorii_furnizori, 0) * 365 / NULLIF(m.achizitii_12_luni, 0), 0) AS zile,
    '(Datorii furnizori x 365) / Achizitii anuale (cont 401 x 365 / jurnal cumparari vjc2025)' AS formula,
    'Zile medii pentru plata furnizorilor' AS explicatie
FROM metrici m
UNION ALL
SELECT
    'CCC (Ciclu conversie cash)' AS indicator,
    ROUND(
        NVL(m.stoc_curent, 0) * 365 / NULLIF(m.cogs_12_luni, 0) +
        NVL(m.creante, 0) * 365 / NULLIF(m.vanzari_12_luni, 0) -
        NVL(m.datorii_furnizori, 0) * 365 / NULLIF(m.achizitii_12_luni, 0)
    , 0) AS zile,
    'DIO + DSO - DPO (zile de la plata furnizor pana la incasare client)' AS formula,
    'DIO + DSO - DPO = zile de la plata furnizor pana la incasare client' AS explicatie
FROM metrici m
"""

# =============================================================================
# 26. INDICATORI AGREGATI VENITURI (Revenue mix per linie de business) - OPTIMIZAT
# =============================================================================
INDICATORI_AGREGATI_VENITURI = """
WITH vanzari_detaliate AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        CASE
            WHEN d.cont IN ('341', '345') THEN 'Productie proprie'
            WHEN d.cont = '301' THEN 'Materii prime'
            ELSE 'Marfa revanduta'
        END AS linie_business,
        d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS vanzare,
        d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie) AS marja
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
),
total AS (
    SELECT SUM(vanzare) AS total_vanzari, SUM(marja) AS total_marja
    FROM vanzari_detaliate
)
SELECT
    v.linie_business,
    ROUND(SUM(v.vanzare), 2) AS vanzari_ron,
    ROUND(SUM(v.vanzare) * 100 / t.total_vanzari, 2) AS procent_venituri,
    ROUND(SUM(v.marja), 2) AS marja_ron,
    ROUND(SUM(v.marja) * 100 / NULLIF(SUM(v.vanzare), 0), 2) AS procent_marja,
    ROUND(SUM(v.marja) * 100 / NULLIF(t.total_marja, 0), 2) AS contributie_profit
FROM vanzari_detaliate v
CROSS JOIN total t
GROUP BY v.linie_business, t.total_vanzari, t.total_marja
ORDER BY vanzari_ron DESC
"""

# =============================================================================
# 27. SEZONALITATE LUNARA (Analiza 24 luni)
# =============================================================================
SEZONALITATE_LUNARA = """
WITH vanzari_lunare AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        EXTRACT(MONTH FROM f.data_act) AS nr_luna,
        TO_CHAR(f.data_act, 'Month', 'NLS_DATE_LANGUAGE=ROMANIAN') AS luna,
        EXTRACT(YEAR FROM f.data_act) AS an,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
    GROUP BY EXTRACT(MONTH FROM f.data_act), TO_CHAR(f.data_act, 'Month', 'NLS_DATE_LANGUAGE=ROMANIAN'), EXTRACT(YEAR FROM f.data_act)
),
statistici_luna AS (
    SELECT
        nr_luna,
        TRIM(luna) AS luna,
        ROUND(AVG(vanzari), 2) AS medie_vanzari,
        ROUND(MIN(vanzari), 2) AS min_vanzari,
        ROUND(MAX(vanzari), 2) AS max_vanzari,
        ROUND(STDDEV(vanzari), 2) AS deviere_std
    FROM vanzari_lunare
    GROUP BY nr_luna, TRIM(luna)
),
media_globala AS (
    SELECT AVG(vanzari) AS medie_globala FROM vanzari_lunare
)
SELECT
    s.luna,
    s.medie_vanzari,
    s.min_vanzari,
    s.max_vanzari,
    ROUND((s.medie_vanzari - m.medie_globala) * 100 / m.medie_globala, 2) AS deviere_procent,
    CASE
        WHEN s.medie_vanzari > m.medie_globala * 1.15 THEN 'LUNA PUTERNICA'
        WHEN s.medie_vanzari < m.medie_globala * 0.85 THEN 'LUNA SLABA'
        ELSE 'NORMAL'
    END AS clasificare
FROM statistici_luna s
CROSS JOIN media_globala m
ORDER BY s.nr_luna
"""

# =============================================================================
# 28. PORTOFOLIU CLIENTI (Sănătatea portofoliului)
# =============================================================================
PORTOFOLIU_CLIENTI = """
WITH clienti_activi_3_luni AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */ COUNT(DISTINCT f.id_part) AS cnt
    FROM vanzari f
    WHERE f.sters = 0 AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -3)
),
clienti_activi_12_luni AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */ COUNT(DISTINCT f.id_part) AS cnt
    FROM vanzari f
    WHERE f.sters = 0 AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
),
clienti_noi AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */ COUNT(DISTINCT f.id_part) AS cnt
    FROM vanzari f
    WHERE f.sters = 0 AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
      AND f.id_part NOT IN (
          SELECT /*+ INDEX(f2 IDX_VANZARI_NR) */ DISTINCT f2.id_part
          FROM vanzari f2
          WHERE f2.sters = 0 AND f2.tip > 0 AND f2.tip NOT IN (7, 8, 9, 24)
            AND f2.data_act < ADD_MONTHS(TRUNC(SYSDATE), -12)
      )
),
clienti_pierduti AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */ COUNT(DISTINCT f.id_part) AS cnt
    FROM vanzari f
    WHERE f.sters = 0 AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -6)
      AND f.id_part NOT IN (
          SELECT /*+ INDEX(f2 IDX_VANZARI_NR) */ DISTINCT f2.id_part
          FROM vanzari f2
          WHERE f2.sters = 0 AND f2.tip > 0 AND f2.tip NOT IN (7, 8, 9, 24)
            AND f2.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -6)
      )
),
clienti_inactivi AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */ COUNT(DISTINCT f.id_part) AS cnt
    FROM vanzari f
    WHERE f.sters = 0 AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -6)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -3)
      AND f.id_part NOT IN (
          SELECT /*+ INDEX(f2 IDX_VANZARI_NR) */ DISTINCT f2.id_part
          FROM vanzari f2
          WHERE f2.sters = 0 AND f2.tip > 0 AND f2.tip NOT IN (7, 8, 9, 24)
            AND f2.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -3)
      )
)
SELECT 'Clienti activi (ultimele 3 luni)' AS indicator, cnt AS valoare, 'Au cumparat recent' AS explicatie FROM clienti_activi_3_luni
UNION ALL
SELECT 'Clienti activi (ultimele 12 luni)' AS indicator, cnt AS valoare, 'Au cumparat in ultimul an' AS explicatie FROM clienti_activi_12_luni
UNION ALL
SELECT 'Clienti noi (ultimele 12 luni)' AS indicator, cnt AS valoare, 'Prima achizitie in ultimul an' AS explicatie FROM clienti_noi
UNION ALL
SELECT 'Clienti pierduti (>6 luni inactivi)' AS indicator, cnt AS valoare, 'Nu au mai cumparat de 6+ luni' AS explicatie FROM clienti_pierduti
UNION ALL
SELECT 'Clienti inactivi (3-6 luni)' AS indicator, cnt AS valoare, 'Risc de pierdere' AS explicatie FROM clienti_inactivi
"""

# =============================================================================
# 29. FRECVENTA CLIENTI (Frecvența comenzilor și evoluție YoY)
# =============================================================================
FRECVENTA_CLIENTI = """
WITH frecventa_curenta AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        p.denumire AS client,
        COUNT(DISTINCT f.id_vanzare) AS comenzi_12_luni,
        ROUND(COUNT(DISTINCT f.id_vanzare) / 12.0, 2) AS comenzi_pe_luna,
        ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS valoare_12_luni
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
    GROUP BY f.id_part, p.denumire
),
frecventa_anterioara AS (
    SELECT /*+ INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        COUNT(DISTINCT f.id_vanzare) AS comenzi_an_anterior
    FROM vanzari f
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -12)
    GROUP BY f.id_part
)
SELECT
    fc.client,
    fc.comenzi_12_luni,
    fc.comenzi_pe_luna,
    fc.valoare_12_luni,
    ROUND(fc.valoare_12_luni / NULLIF(fc.comenzi_12_luni, 0), 2) AS valoare_medie_comanda,
    ROUND(
        CASE WHEN fa.comenzi_an_anterior > 0
        THEN (fc.comenzi_12_luni - fa.comenzi_an_anterior) * 100.0 / fa.comenzi_an_anterior
        ELSE NULL END
    , 2) AS evolutie_frecventa_yoy
FROM frecventa_curenta fc
LEFT JOIN frecventa_anterioara fa ON fa.id_part = fc.id_part
WHERE fc.valoare_12_luni > 5000
ORDER BY fc.valoare_12_luni DESC
"""

# =============================================================================
# 30. CONCENTRARE RISC (Risc de concentrare)
# =============================================================================
CONCENTRARE_RISC = """
WITH total_vanzari AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS total
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
),
vanzari_client AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari,
        ROW_NUMBER() OVER (ORDER BY SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) DESC) AS rn
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
    GROUP BY f.id_part
)
SELECT
    'Top 1 client' AS indicator,
    ROUND(SUM(CASE WHEN vc.rn <= 1 THEN vc.vanzari ELSE 0 END) * 100 / tv.total, 2) AS procent,
    CASE
        WHEN SUM(CASE WHEN vc.rn <= 1 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 25 THEN 'RISC MARE'
        WHEN SUM(CASE WHEN vc.rn <= 1 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 15 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM vanzari_client vc
CROSS JOIN total_vanzari tv
GROUP BY tv.total
UNION ALL
SELECT
    'Top 5 clienti' AS indicator,
    ROUND(SUM(CASE WHEN vc.rn <= 5 THEN vc.vanzari ELSE 0 END) * 100 / tv.total, 2) AS procent,
    CASE
        WHEN SUM(CASE WHEN vc.rn <= 5 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 60 THEN 'RISC MARE'
        WHEN SUM(CASE WHEN vc.rn <= 5 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 40 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM vanzari_client vc
CROSS JOIN total_vanzari tv
GROUP BY tv.total
UNION ALL
SELECT
    'Top 10 clienti' AS indicator,
    ROUND(SUM(CASE WHEN vc.rn <= 10 THEN vc.vanzari ELSE 0 END) * 100 / tv.total, 2) AS procent,
    CASE
        WHEN SUM(CASE WHEN vc.rn <= 10 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 80 THEN 'RISC MARE'
        WHEN SUM(CASE WHEN vc.rn <= 10 THEN vc.vanzari ELSE 0 END) * 100 / tv.total > 60 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM vanzari_client vc
CROSS JOIN total_vanzari tv
GROUP BY tv.total
"""

# =============================================================================
# 31. CLIENTI RANKING PROFIT (Ranking după profit, nu venituri)
# =============================================================================
CLIENTI_RANKING_PROFIT = """
WITH vanzari_client AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        p.denumire AS client,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari_fara_tva,
        SUM(d.cantitate * d.pret_achizitie) AS cost_total,
        SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) AS profit_brut
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -:months)
    GROUP BY f.id_part, p.denumire
)
SELECT
    client,
    ROUND(vanzari_fara_tva, 2) AS vanzari_fara_tva,
    ROUND(cost_total, 2) AS cost_total,
    ROUND(profit_brut, 2) AS profit_brut,
    ROUND(profit_brut * 100 / NULLIF(vanzari_fara_tva, 0), 2) AS procent_marja,
    RANK() OVER (ORDER BY profit_brut DESC) AS rang_profit,
    RANK() OVER (ORDER BY vanzari_fara_tva DESC) AS rang_vanzari
FROM vanzari_client
WHERE vanzari_fara_tva > 1000
ORDER BY profit_brut DESC
"""

# =============================================================================
# 32. MARJA CLIENT CATEGORIE (Marjă per categorie per client) - OPTIMIZAT
# =============================================================================
MARJA_CLIENT_CATEGORIE = """
SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
    p.denumire AS client,
    NVL(sg.grupa, 'NECLASIFICAT') AS categoria,
    ROUND(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 2) AS vanzari,
    ROUND(SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)), 2) AS marja,
    ROUND(
        SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 /
        NULLIF(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 0)
    , 2) AS procent_marja,
    CASE
        WHEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) < 0 THEN 'PIERDERE'
        WHEN SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) * 100.0 /
             NULLIF(SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END), 0) < 15 THEN 'MARJA MICA'
        ELSE 'OK'
    END AS status_marja
FROM vanzari f
JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
LEFT JOIN nom_parteneri p ON f.id_part = p.id_part
LEFT JOIN nom_articole art ON d.id_articol = art.id_articol
LEFT JOIN vgest_art_sbgr sg ON art.id_subgrupa = sg.id_subgrupa
WHERE f.sters = 0
  AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
  AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -:months)
GROUP BY f.id_part, p.denumire, sg.id_grupa, sg.grupa
HAVING SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) > 1000
ORDER BY p.denumire, vanzari DESC
"""

# =============================================================================
# 33. EVOLUTIE DISCOUNT (Discount creep - evoluția prețului în timp)
# =============================================================================
EVOLUTIE_DISCOUNT = """
WITH preturi_vechi AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        d.id_articol,
        a.denumire,
        AVG(CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS pret_mediu_vechi
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    LEFT JOIN nom_articole a ON d.id_articol = a.id_articol
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -6)
      AND d.pret > 0
    GROUP BY d.id_articol, a.denumire
    HAVING COUNT(*) >= 5
),
preturi_noi AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        d.id_articol,
        AVG(CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS pret_mediu_nou
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -6)
      AND d.pret > 0
    GROUP BY d.id_articol
    HAVING COUNT(*) >= 5
)
SELECT
    pv.denumire AS produs,
    ROUND(pv.pret_mediu_vechi, 2) AS pret_initial,
    ROUND(pn.pret_mediu_nou, 2) AS pret_actual,
    ROUND((pn.pret_mediu_nou - pv.pret_mediu_vechi) * 100 / pv.pret_mediu_vechi, 2) AS variatie_pret_procent
FROM preturi_vechi pv
JOIN preturi_noi pn ON pn.id_articol = pv.id_articol
WHERE ABS(pn.pret_mediu_nou - pv.pret_mediu_vechi) / pv.pret_mediu_vechi > 0.05
ORDER BY variatie_pret_procent ASC
FETCH FIRST 50 ROWS ONLY
"""

# =============================================================================
# 34. INDICATORI GENERALI DE BUSINESS
# =============================================================================
INDICATORI_GENERALI = """
WITH
-- Capital propriu (conturi 10x, 11x, 12x, 117)
capitaluri AS (
    SELECT SUM(b.soldcred - b.solddeb) AS capital_propriu
    FROM vbal b
    WHERE (b.cont LIKE '101%' OR b.cont LIKE '104%' OR b.cont LIKE '105%'
           OR b.cont LIKE '106%' OR b.cont LIKE '117%' OR b.cont LIKE '121%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Datorii totale (16x, 40x, 42x, 44x, 46x)
datorii AS (
    SELECT SUM(b.soldcred - b.solddeb) AS datorii_totale
    FROM vbal b
    WHERE (b.cont LIKE '16%' OR b.cont LIKE '40%' OR b.cont LIKE '42%'
           OR b.cont LIKE '44%' OR b.cont LIKE '46%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Total activ (sold debitor toate conturile de activ)
activ AS (
    SELECT SUM(b.solddeb - b.soldcred) AS total_activ
    FROM vbal b
    WHERE (b.cont LIKE '1%' OR b.cont LIKE '2%' OR b.cont LIKE '3%'
           OR b.cont LIKE '4%' OR b.cont LIKE '5%')
      AND b.cont NOT LIKE '16%' AND b.cont NOT LIKE '40%' AND b.cont NOT LIKE '42%'
      AND b.cont NOT LIKE '44%' AND b.cont NOT LIKE '46%' AND b.cont NOT LIKE '47%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Vanzari si profit din ultimele 12 luni
vanzari_calc AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS total_vanzari,
        SUM(d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie)) AS profit_brut
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
)
SELECT
    'Grad indatorare' AS indicator,
    ROUND(NVL(dt.datorii_totale, 0) / NULLIF(cp.capital_propriu, 0), 2) AS valoare,
    CASE
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(cp.capital_propriu, 0) > 2 THEN 'ALERTA'
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(cp.capital_propriu, 0) > 1 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    'Datorii / Capital Propriu (cont 16x,40x,42x,44x,46x / cont 10x,11x,12x,117)' AS formula,
    '< 1 = bine, > 2 = risc' AS interpretare,
    CASE
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(cp.capital_propriu, 0) > 2 THEN 'Reduceti datoriile sau cresteti capitalul propriu'
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(cp.capital_propriu, 0) > 1 THEN 'Monitorizati nivelul datoriilor'
        ELSE 'Situatie financiara solida'
    END AS recomandare
FROM capitaluri cp, datorii dt
UNION ALL
SELECT
    'Grad autonomie financiara' AS indicator,
    ROUND(NVL(cp.capital_propriu, 0) / NULLIF(ac.total_activ, 0), 2) AS valoare,
    CASE
        WHEN NVL(cp.capital_propriu, 0) / NULLIF(ac.total_activ, 0) < 0.3 THEN 'ALERTA'
        WHEN NVL(cp.capital_propriu, 0) / NULLIF(ac.total_activ, 0) < 0.5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    'Capital Propriu / Total Active (cont 10x,11x,12x,117 / Active clasa 1-5)' AS formula,
    '> 0.5 = bine, < 0.3 = risc' AS interpretare,
    CASE
        WHEN NVL(cp.capital_propriu, 0) / NULLIF(ac.total_activ, 0) < 0.3 THEN 'Dependenta prea mare de creditori'
        WHEN NVL(cp.capital_propriu, 0) / NULLIF(ac.total_activ, 0) < 0.5 THEN 'Cresteti capitalul propriu'
        ELSE 'Autonomie financiara buna'
    END AS recomandare
FROM capitaluri cp, activ ac
UNION ALL
SELECT
    'Rata datoriilor' AS indicator,
    ROUND(NVL(dt.datorii_totale, 0) / NULLIF(ac.total_activ, 0), 2) AS valoare,
    CASE
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(ac.total_activ, 0) > 0.7 THEN 'ALERTA'
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(ac.total_activ, 0) > 0.5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    'Datorii / Total Active (cont 16x,40x,42x,44x,46x / Active clasa 1-5)' AS formula,
    '< 0.5 = bine, > 0.7 = risc' AS interpretare,
    CASE
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(ac.total_activ, 0) > 0.7 THEN 'Risc de insolventa - reduceti datoriile'
        WHEN NVL(dt.datorii_totale, 0) / NULLIF(ac.total_activ, 0) > 0.5 THEN 'Atentie la capacitatea de plata'
        ELSE 'Nivel acceptabil al datoriilor'
    END AS recomandare
FROM datorii dt, activ ac
UNION ALL
SELECT
    'Marja neta (%)' AS indicator,
    ROUND(NVL(v.profit_brut, 0) * 100 / NULLIF(v.total_vanzari, 0), 2) AS valoare,
    CASE
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(v.total_vanzari, 0) < 3 THEN 'ALERTA'
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(v.total_vanzari, 0) < 5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    '(Profit Brut / Vanzari) x 100 (din facturi ultimele 12 luni)' AS formula,
    '> 5% = bine, < 3% = risc' AS interpretare,
    CASE
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(v.total_vanzari, 0) < 3 THEN 'Marja periculoasa - revizuiti preturile si costurile'
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(v.total_vanzari, 0) < 5 THEN 'Optimizati costurile sau cresteti preturile'
        ELSE 'Marja acceptabila'
    END AS recomandare
FROM vanzari_calc v
UNION ALL
SELECT
    'ROA - Rentabilitatea activelor (%)' AS indicator,
    ROUND(NVL(v.profit_brut, 0) * 100 / NULLIF(ac.total_activ, 0), 2) AS valoare,
    CASE
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(ac.total_activ, 0) < 2 THEN 'ALERTA'
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(ac.total_activ, 0) < 5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    '(Profit Brut / Total Active) x 100 (facturi 12 luni / balanta)' AS formula,
    '> 5% = bine, < 2% = slab' AS interpretare,
    CASE
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(ac.total_activ, 0) < 2 THEN 'Activele nu genereaza profit suficient - optimizati utilizarea'
        WHEN NVL(v.profit_brut, 0) * 100 / NULLIF(ac.total_activ, 0) < 5 THEN 'Cresteti eficienta utilizarii activelor'
        ELSE 'Activele sunt utilizate eficient'
    END AS recomandare
FROM vanzari_calc v, activ ac
UNION ALL
SELECT
    'Rotatia activelor' AS indicator,
    ROUND(NVL(v.total_vanzari, 0) / NULLIF(ac.total_activ, 0), 2) AS valoare,
    CASE
        WHEN NVL(v.total_vanzari, 0) / NULLIF(ac.total_activ, 0) < 0.5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    'Vanzari / Total Active (facturi 12 luni / balanta)' AS formula,
    '> 1 = eficient' AS interpretare,
    CASE
        WHEN NVL(v.total_vanzari, 0) / NULLIF(ac.total_activ, 0) < 0.5 THEN 'Active subutilizate - cresteti vanzarile'
        ELSE 'Activele genereaza vanzari eficient'
    END AS recomandare
FROM vanzari_calc v, activ ac
"""

# =============================================================================
# 35. INDICATORI LICHIDITATE
# =============================================================================
INDICATORI_LICHIDITATE = """
WITH
-- Cash (conturi 5121, 5124, 5311, 5314)
cash AS (
    SELECT SUM(b.solddeb - b.soldcred) AS cash_total
    FROM vbal b
    WHERE (b.cont LIKE '512%' OR b.cont LIKE '531%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Creante (cont 4111)
creante AS (
    SELECT SUM(CASE WHEN b.solddeb > b.soldcred THEN b.solddeb - b.soldcred ELSE 0 END) AS creante_total
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '4111%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Stocuri
stocuri AS (
    SELECT SUM((s.cants + s.cant - s.cante) * s.pret) AS stoc_total
    FROM vstoc s
    WHERE s.an = EXTRACT(YEAR FROM SYSDATE)
      AND s.luna = EXTRACT(MONTH FROM SYSDATE)
      AND (s.cants + s.cant - s.cante) > 0
),
-- Datorii curente (401, 404, 462)
datorii_curente AS (
    SELECT SUM(CASE WHEN b.soldcred > b.solddeb THEN b.soldcred - b.solddeb ELSE 0 END) AS datorii_total
    FROM vbalanta_parteneri b
    WHERE (b.cont LIKE '401%' OR b.cont LIKE '404%' OR b.cont LIKE '462%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
)
SELECT
    'Lichiditate curenta' AS indicator,
    ROUND((NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) / NULLIF(dc.datorii_total, 0), 2) AS valoare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.0 THEN 'ALERTA'
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.5 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    '(Cash + Creante + Stoc) / Datorii curente (cont 512x,531x + 4111 + vstoc / cont 401x,404x,462x)' AS formula,
    '>= 1.5 ideal, >= 1.0 acceptabil' AS interpretare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.0 THEN 'Risc de lichiditate - nu puteti acoperi datoriile curente'
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.5 THEN 'Mentineti rezerve suplimentare'
        ELSE 'Lichiditate buna'
    END AS recomandare
FROM cash c, creante cr, stocuri st, datorii_curente dc
UNION ALL
SELECT
    'Lichiditate rapida (Quick Ratio)' AS indicator,
    ROUND((NVL(c.cash_total, 0) + NVL(cr.creante_total, 0)) / NULLIF(dc.datorii_total, 0), 2) AS valoare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0)) / NULLIF(dc.datorii_total, 0) < 0.7 THEN 'ALERTA'
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.0 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    '(Cash + Creante) / Datorii curente - fara stocuri (cont 512x,531x + 4111 / cont 401x,404x,462x)' AS formula,
    '>= 1.0 ideal, >= 0.7 acceptabil' AS interpretare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0)) / NULLIF(dc.datorii_total, 0) < 0.7 THEN 'Dependenta de stocuri pentru plata datoriilor'
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0)) / NULLIF(dc.datorii_total, 0) < 1.0 THEN 'Accelerati incasarile'
        ELSE 'Lichiditate rapida buna'
    END AS recomandare
FROM cash c, creante cr, datorii_curente dc
UNION ALL
SELECT
    'Rata de cash (Cash Ratio)' AS indicator,
    ROUND(NVL(c.cash_total, 0) / NULLIF(dc.datorii_total, 0), 2) AS valoare,
    CASE
        WHEN NVL(c.cash_total, 0) / NULLIF(dc.datorii_total, 0) < 0.1 THEN 'ALERTA'
        WHEN NVL(c.cash_total, 0) / NULLIF(dc.datorii_total, 0) < 0.2 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status,
    'Cash / Datorii curente (cont 512x,531x / cont 401x,404x,462x)' AS formula,
    '>= 0.2 ideal, >= 0.1 minim' AS interpretare,
    CASE
        WHEN NVL(c.cash_total, 0) / NULLIF(dc.datorii_total, 0) < 0.1 THEN 'Cash insuficient - risc la plati urgente'
        WHEN NVL(c.cash_total, 0) / NULLIF(dc.datorii_total, 0) < 0.2 THEN 'Cresteti rezervele de cash'
        ELSE 'Rezerve de cash suficiente'
    END AS recomandare
FROM cash c, datorii_curente dc
UNION ALL
SELECT
    'Fond de rulment (RON)' AS indicator,
    ROUND((NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) - NVL(dc.datorii_total, 0), 0) AS valoare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) - NVL(dc.datorii_total, 0) < 0 THEN 'ALERTA'
        ELSE 'OK'
    END AS status,
    'Active curente - Datorii curente (Cash + Creante + Stoc - Datorii furnizori)' AS formula,
    'Pozitiv = OK, Negativ = probleme' AS interpretare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(cr.creante_total, 0) + NVL(st.stoc_total, 0)) - NVL(dc.datorii_total, 0) < 0 THEN 'Fond de rulment negativ - necesita finantare'
        ELSE 'Capital de lucru pozitiv'
    END AS recomandare
FROM cash c, creante cr, stocuri st, datorii_curente dc
"""

# =============================================================================
# 36. CLASIFICARE DATORII PE TERMENE
# =============================================================================
CLASIFICARE_DATORII = """
WITH datorii_scadenta AS (
    SELECT
        r.id_part,
        r.nume AS furnizor,
        r.datascad,
        (r.preccred + r.credit) - (r.precdeb + r.debit) AS sold_ramas,
        CASE
            WHEN r.datascad IS NULL THEN 'TERMEN_NEDEFINIT'
            WHEN r.datascad <= TRUNC(SYSDATE) + 30 THEN 'TERMEN_SCURT'
            WHEN r.datascad <= TRUNC(SYSDATE) + 90 THEN 'TERMEN_MEDIU'
            ELSE 'TERMEN_LUNG'
        END AS clasificare
    FROM vireg_parteneri r
    WHERE (r.cont LIKE '401%' OR r.cont LIKE '404%' OR r.cont LIKE '462%')
      AND (r.preccred + r.credit) - (r.precdeb + r.debit) <> 0
)
SELECT
    'Datorii termen scurt (<= 30 zile)' AS categorie,
    ROUND(SUM(CASE WHEN clasificare = 'TERMEN_SCURT' THEN sold_ramas ELSE 0 END), 0) AS valoare,
    COUNT(CASE WHEN clasificare = 'TERMEN_SCURT' THEN 1 END) AS nr_facturi,
    'Prioritate INALTA - de platit urgent' AS observatie
FROM datorii_scadenta
UNION ALL
SELECT
    'Datorii termen mediu (31-90 zile)' AS categorie,
    ROUND(SUM(CASE WHEN clasificare = 'TERMEN_MEDIU' THEN sold_ramas ELSE 0 END), 0) AS valoare,
    COUNT(CASE WHEN clasificare = 'TERMEN_MEDIU' THEN 1 END) AS nr_facturi,
    'Prioritate MEDIE - planificati plata' AS observatie
FROM datorii_scadenta
UNION ALL
SELECT
    'Datorii termen lung (> 90 zile)' AS categorie,
    ROUND(SUM(CASE WHEN clasificare = 'TERMEN_LUNG' THEN sold_ramas ELSE 0 END), 0) AS valoare,
    COUNT(CASE WHEN clasificare = 'TERMEN_LUNG' THEN 1 END) AS nr_facturi,
    'Prioritate SCAZUTA' AS observatie
FROM datorii_scadenta
UNION ALL
SELECT
    'Datorii fara scadenta definita' AS categorie,
    ROUND(SUM(CASE WHEN clasificare = 'TERMEN_NEDEFINIT' THEN sold_ramas ELSE 0 END), 0) AS valoare,
    COUNT(CASE WHEN clasificare = 'TERMEN_NEDEFINIT' THEN 1 END) AS nr_facturi,
    'Verificati datele de scadenta' AS observatie
FROM datorii_scadenta
UNION ALL
SELECT
    'TOTAL DATORII' AS categorie,
    ROUND(SUM(sold_ramas), 0) AS valoare,
    COUNT(*) AS nr_facturi,
    'Suma totala de platit' AS observatie
FROM datorii_scadenta
"""

# =============================================================================
# 37. GRAD ACOPERIRE DATORII (Cash + Incasari vs Datorii scadente)
# =============================================================================
GRAD_ACOPERIRE_DATORII = """
WITH
cash_curent AS (
    SELECT SUM(b.solddeb - b.soldcred) AS cash_total
    FROM vbal b
    WHERE (b.cont LIKE '512%' OR b.cont LIKE '531%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
incasari_asteptate AS (
    SELECT
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 30 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END), 0) AS incasari_30,
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 60 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END), 0) AS incasari_60,
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 90 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END), 0) AS incasari_90
    FROM vireg_parteneri r
    WHERE (r.cont LIKE '4111%' OR r.cont LIKE '461%')
      AND (r.precdeb + r.debit) - (r.preccred + r.credit) <> 0
),
plati_scadente AS (
    SELECT
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 30 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END), 0) AS plati_30,
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 60 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END), 0) AS plati_60,
        ROUND(SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 90 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END), 0) AS plati_90
    FROM vireg_parteneri r
    WHERE (r.cont LIKE '401%' OR r.cont LIKE '404%' OR r.cont LIKE '462%')
      AND (r.preccred + r.credit) - (r.precdeb + r.debit) <> 0
)
SELECT
    'Cash disponibil' AS indicator,
    NVL(c.cash_total, 0) AS valoare,
    '-' AS acoperire,
    'Disponibil imediat' AS explicatie
FROM cash_curent c
UNION ALL
SELECT
    'Incasari asteptate 30 zile' AS indicator,
    NVL(i.incasari_30, 0) AS valoare,
    '-' AS acoperire,
    'Facturi clienti scadente in 30 zile' AS explicatie
FROM incasari_asteptate i
UNION ALL
SELECT
    'Plati scadente 30 zile' AS indicator,
    NVL(p.plati_30, 0) AS valoare,
    '-' AS acoperire,
    'Facturi furnizori de platit in 30 zile' AS explicatie
FROM plati_scadente p
UNION ALL
SELECT
    'GRAD ACOPERIRE 30 ZILE' AS indicator,
    ROUND((NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) / NULLIF(p.plati_30, 0), 2) AS valoare,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) / NULLIF(p.plati_30, 0) < 1 THEN 'DEFICIT'
        WHEN (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) / NULLIF(p.plati_30, 0) < 1.2 THEN 'ATENTIE'
        ELSE 'OK'
    END AS acoperire,
    CASE
        WHEN (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) / NULLIF(p.plati_30, 0) < 1 THEN 'NECESITA FINANTARE'
        ELSE 'Acoperiti platile'
    END AS explicatie
FROM cash_curent c, incasari_asteptate i, plati_scadente p
UNION ALL
SELECT
    'NECESAR FINANTARE 30 ZILE' AS indicator,
    CASE
        WHEN NVL(p.plati_30, 0) - (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) > 0
        THEN NVL(p.plati_30, 0) - (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0))
        ELSE 0
    END AS valoare,
    CASE
        WHEN NVL(p.plati_30, 0) - (NVL(c.cash_total, 0) + NVL(i.incasari_30, 0)) > 0 THEN 'NECESAR'
        ELSE 'NU E NECESAR'
    END AS acoperire,
    'Suma de obtinut pentru acoperirea deficitului' AS explicatie
FROM cash_curent c, incasari_asteptate i, plati_scadente p
"""

# =============================================================================
# 38. PROIECTIE LICHIDITATE 30/60/90 ZILE
# =============================================================================
PROIECTIE_LICHIDITATE = """
WITH
cash_curent AS (
    SELECT SUM(b.solddeb - b.soldcred) AS cash_total
    FROM vbal b
    WHERE (b.cont LIKE '512%' OR b.cont LIKE '531%')
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
incasari_programate AS (
    SELECT
        SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 30 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END) AS incasari_30,
        SUM(CASE WHEN r.datascad > TRUNC(SYSDATE) + 30 AND r.datascad <= TRUNC(SYSDATE) + 60 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END) AS incasari_31_60,
        SUM(CASE WHEN r.datascad > TRUNC(SYSDATE) + 60 AND r.datascad <= TRUNC(SYSDATE) + 90 THEN (r.precdeb + r.debit) - (r.preccred + r.credit) ELSE 0 END) AS incasari_61_90
    FROM vireg_parteneri r
    WHERE (r.cont LIKE '4111%' OR r.cont LIKE '461%')
      AND (r.precdeb + r.debit) - (r.preccred + r.credit) <> 0
),
plati_programate AS (
    SELECT
        SUM(CASE WHEN r.datascad <= TRUNC(SYSDATE) + 30 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END) AS plati_30,
        SUM(CASE WHEN r.datascad > TRUNC(SYSDATE) + 30 AND r.datascad <= TRUNC(SYSDATE) + 60 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END) AS plati_31_60,
        SUM(CASE WHEN r.datascad > TRUNC(SYSDATE) + 60 AND r.datascad <= TRUNC(SYSDATE) + 90 THEN (r.preccred + r.credit) - (r.precdeb + r.debit) ELSE 0 END) AS plati_61_90
    FROM vireg_parteneri r
    WHERE (r.cont LIKE '401%' OR r.cont LIKE '404%' OR r.cont LIKE '462%')
      AND (r.preccred + r.credit) - (r.precdeb + r.debit) <> 0
)
SELECT
    'Sold initial (azi)' AS perioada,
    ROUND(NVL(c.cash_total, 0), 0) AS sold_proiectat,
    0 AS incasari,
    0 AS plati,
    0 AS flux_net,
    'OK' AS status
FROM cash_curent c
UNION ALL
SELECT
    'Proiectie 30 zile' AS perioada,
    ROUND(NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) - NVL(p.plati_30, 0), 0) AS sold_proiectat,
    ROUND(NVL(i.incasari_30, 0), 0) AS incasari,
    ROUND(NVL(p.plati_30, 0), 0) AS plati,
    ROUND(NVL(i.incasari_30, 0) - NVL(p.plati_30, 0), 0) AS flux_net,
    CASE
        WHEN NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) - NVL(p.plati_30, 0) < 0 THEN 'ALERTA'
        WHEN NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) - NVL(p.plati_30, 0) < NVL(c.cash_total, 0) * 0.2 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM cash_curent c, incasari_programate i, plati_programate p
UNION ALL
SELECT
    'Proiectie 60 zile' AS perioada,
    ROUND(NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) + NVL(i.incasari_31_60, 0) - NVL(p.plati_30, 0) - NVL(p.plati_31_60, 0), 0) AS sold_proiectat,
    ROUND(NVL(i.incasari_31_60, 0), 0) AS incasari,
    ROUND(NVL(p.plati_31_60, 0), 0) AS plati,
    ROUND(NVL(i.incasari_31_60, 0) - NVL(p.plati_31_60, 0), 0) AS flux_net,
    CASE
        WHEN NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) + NVL(i.incasari_31_60, 0) - NVL(p.plati_30, 0) - NVL(p.plati_31_60, 0) < 0 THEN 'ALERTA'
        ELSE 'OK'
    END AS status
FROM cash_curent c, incasari_programate i, plati_programate p
UNION ALL
SELECT
    'Proiectie 90 zile' AS perioada,
    ROUND(NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) + NVL(i.incasari_31_60, 0) + NVL(i.incasari_61_90, 0) - NVL(p.plati_30, 0) - NVL(p.plati_31_60, 0) - NVL(p.plati_61_90, 0), 0) AS sold_proiectat,
    ROUND(NVL(i.incasari_61_90, 0), 0) AS incasari,
    ROUND(NVL(p.plati_61_90, 0), 0) AS plati,
    ROUND(NVL(i.incasari_61_90, 0) - NVL(p.plati_61_90, 0), 0) AS flux_net,
    CASE
        WHEN NVL(c.cash_total, 0) + NVL(i.incasari_30, 0) + NVL(i.incasari_31_60, 0) + NVL(i.incasari_61_90, 0) - NVL(p.plati_30, 0) - NVL(p.plati_31_60, 0) - NVL(p.plati_61_90, 0) < 0 THEN 'ALERTA'
        ELSE 'OK'
    END AS status
FROM cash_curent c, incasari_programate i, plati_programate p
"""

# =============================================================================
# 39. SUMAR EXECUTIV YoY (cu comparatie an anterior)
# =============================================================================
# OPTIMIZED: Single 24-month scan with CASE-based period partitioning instead of 2 separate view scans
SUMAR_EXECUTIV_YOY = """
WITH base_data AS (
    SELECT
        f.id_vanzare,
        f.id_part,
        CASE WHEN f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12) THEN 'CURENT' ELSE 'ANTERIOR' END AS perioada,
        d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS vanzare,
        d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie) AS marja
    FROM VANZARI f
    JOIN VANZARI_DETALII d ON d.id_vanzare = f.id_vanzare
    WHERE f.sters = 0 AND d.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
),
aggregated AS (
    SELECT
        SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) AS vanzari_curente,
        SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END) AS vanzari_anterioare,
        SUM(CASE WHEN perioada = 'CURENT' THEN marja ELSE 0 END) AS marja_curenta,
        SUM(CASE WHEN perioada = 'ANTERIOR' THEN marja ELSE 0 END) AS marja_anterioara,
        COUNT(DISTINCT CASE WHEN perioada = 'CURENT' THEN id_vanzare END) AS facturi_curente,
        COUNT(DISTINCT CASE WHEN perioada = 'ANTERIOR' THEN id_vanzare END) AS facturi_anterioare,
        COUNT(DISTINCT CASE WHEN perioada = 'CURENT' THEN id_part END) AS clienti_curenti,
        COUNT(DISTINCT CASE WHEN perioada = 'ANTERIOR' THEN id_part END) AS clienti_anteriori
    FROM base_data
)
SELECT
    'Vanzari totale (RON)' AS indicator,
    ROUND(vanzari_curente, 0) AS valoare_curenta,
    ROUND(vanzari_anterioare, 0) AS valoare_anterioara,
    ROUND(vanzari_curente - vanzari_anterioare, 0) AS variatie_absoluta,
    ROUND((vanzari_curente - vanzari_anterioare) * 100 / NULLIF(vanzari_anterioare, 0), 2) AS variatie_procent,
    CASE
        WHEN vanzari_curente > vanzari_anterioare * 1.05 THEN 'CRESTERE'
        WHEN vanzari_curente < vanzari_anterioare * 0.95 THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM aggregated
UNION ALL
SELECT
    'Marja bruta (RON)' AS indicator,
    ROUND(marja_curenta, 0) AS valoare_curenta,
    ROUND(marja_anterioara, 0) AS valoare_anterioara,
    ROUND(marja_curenta - marja_anterioara, 0) AS variatie_absoluta,
    ROUND((marja_curenta - marja_anterioara) * 100 / NULLIF(marja_anterioara, 0), 2) AS variatie_procent,
    CASE
        WHEN marja_curenta > marja_anterioara * 1.05 THEN 'CRESTERE'
        WHEN marja_curenta < marja_anterioara * 0.95 THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM aggregated
UNION ALL
SELECT
    'Procent marja (%)' AS indicator,
    ROUND(marja_curenta * 100 / NULLIF(vanzari_curente, 0), 2) AS valoare_curenta,
    ROUND(marja_anterioara * 100 / NULLIF(vanzari_anterioare, 0), 2) AS valoare_anterioara,
    ROUND(marja_curenta * 100 / NULLIF(vanzari_curente, 0) - marja_anterioara * 100 / NULLIF(vanzari_anterioare, 0), 2) AS variatie_absoluta,
    NULL AS variatie_procent,
    CASE
        WHEN marja_curenta * 100 / NULLIF(vanzari_curente, 0) > marja_anterioara * 100 / NULLIF(vanzari_anterioare, 0) + 1 THEN 'CRESTERE'
        WHEN marja_curenta * 100 / NULLIF(vanzari_curente, 0) < marja_anterioara * 100 / NULLIF(vanzari_anterioare, 0) - 1 THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM aggregated
UNION ALL
SELECT
    'Numar facturi' AS indicator,
    facturi_curente AS valoare_curenta,
    facturi_anterioare AS valoare_anterioara,
    facturi_curente - facturi_anterioare AS variatie_absoluta,
    ROUND((facturi_curente - facturi_anterioare) * 100 / NULLIF(facturi_anterioare, 0), 2) AS variatie_procent,
    CASE
        WHEN facturi_curente > facturi_anterioare * 1.05 THEN 'CRESTERE'
        WHEN facturi_curente < facturi_anterioare * 0.95 THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM aggregated
UNION ALL
SELECT
    'Numar clienti activi' AS indicator,
    clienti_curenti AS valoare_curenta,
    clienti_anteriori AS valoare_anterioara,
    clienti_curenti - clienti_anteriori AS variatie_absoluta,
    ROUND((clienti_curenti - clienti_anteriori) * 100 / NULLIF(clienti_anteriori, 0), 2) AS variatie_procent,
    CASE
        WHEN clienti_curenti > clienti_anteriori THEN 'CRESTERE'
        WHEN clienti_curenti < clienti_anteriori THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM aggregated
"""

# =============================================================================
# 40. DSO DPO YoY (comparatie an anterior)
# =============================================================================
DSO_DPO_YOY = """
WITH
-- Metrici curente
vanzari_curente AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS total_vanzari
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
),
sold_clienti_curent AS (
    SELECT SUM(CASE WHEN b.solddeb > b.soldcred THEN b.solddeb - b.soldcred ELSE 0 END) AS total_creante
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '4111%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
achizitii_curente AS (
    SELECT SUM(ABS(r.cant * r.pret)) AS total_achizitii
    FROM vrul r
    WHERE r.id_tip_rulaj = 1 AND r.cant > 0
      AND r.dataact >= ADD_MONTHS(TRUNC(SYSDATE), -12)
),
sold_furnizori_curent AS (
    SELECT SUM(CASE WHEN b.soldcred > b.solddeb THEN b.soldcred - b.solddeb ELSE 0 END) AS total_datorii
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '401%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE)
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
-- Metrici anterioare (aproximare - vanzari an anterior)
vanzari_anterioare AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS total_vanzari
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -12)
),
achizitii_anterioare AS (
    SELECT SUM(ABS(r.cant * r.pret)) AS total_achizitii
    FROM vrul r
    WHERE r.id_tip_rulaj = 1 AND r.cant > 0
      AND r.dataact >= ADD_MONTHS(TRUNC(SYSDATE), -24)
      AND r.dataact < ADD_MONTHS(TRUNC(SYSDATE), -12)
),
-- Solduri anterioare (aproximare - luna curenta anul trecut)
sold_clienti_anterior AS (
    SELECT SUM(CASE WHEN b.solddeb > b.soldcred THEN b.solddeb - b.soldcred ELSE 0 END) AS total_creante
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '4111%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE) - 1
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
),
sold_furnizori_anterior AS (
    SELECT SUM(CASE WHEN b.soldcred > b.solddeb THEN b.soldcred - b.solddeb ELSE 0 END) AS total_datorii
    FROM vbalanta_parteneri b
    WHERE b.cont LIKE '401%'
      AND b.an = EXTRACT(YEAR FROM SYSDATE) - 1
      AND b.luna = EXTRACT(MONTH FROM SYSDATE)
)
SELECT
    'DSO (Zile incasare)' AS indicator,
    ROUND(NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0), 0) AS valoare_curenta,
    ROUND(NVL(sca.total_creante, 0) * 365 / NULLIF(va.total_vanzari, 0), 0) AS valoare_anterioara,
    ROUND(NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0) - NVL(sca.total_creante, 0) * 365 / NULLIF(va.total_vanzari, 0), 0) AS variatie_zile,
    CASE
        WHEN NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0) < NVL(sca.total_creante, 0) * 365 / NULLIF(va.total_vanzari, 0) THEN 'IMBUNATATIRE'
        WHEN NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0) > NVL(sca.total_creante, 0) * 365 / NULLIF(va.total_vanzari, 0) + 5 THEN 'DETERIORARE'
        ELSE 'STABIL'
    END AS trend,
    CASE
        WHEN NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0) > 60 THEN 'ALERTA'
        WHEN NVL(scc.total_creante, 0) * 365 / NULLIF(vc.total_vanzari, 0) > 45 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM vanzari_curente vc, sold_clienti_curent scc, vanzari_anterioare va, sold_clienti_anterior sca
UNION ALL
SELECT
    'DPO (Zile plata)' AS indicator,
    ROUND(NVL(sfc.total_datorii, 0) * 365 / NULLIF(ac.total_achizitii, 0), 0) AS valoare_curenta,
    ROUND(NVL(sfa.total_datorii, 0) * 365 / NULLIF(aa.total_achizitii, 0), 0) AS valoare_anterioara,
    ROUND(NVL(sfc.total_datorii, 0) * 365 / NULLIF(ac.total_achizitii, 0) - NVL(sfa.total_datorii, 0) * 365 / NULLIF(aa.total_achizitii, 0), 0) AS variatie_zile,
    CASE
        WHEN NVL(sfc.total_datorii, 0) * 365 / NULLIF(ac.total_achizitii, 0) > NVL(sfa.total_datorii, 0) * 365 / NULLIF(aa.total_achizitii, 0) THEN 'IMBUNATATIRE'
        WHEN NVL(sfc.total_datorii, 0) * 365 / NULLIF(ac.total_achizitii, 0) < NVL(sfa.total_datorii, 0) * 365 / NULLIF(aa.total_achizitii, 0) - 5 THEN 'DETERIORARE'
        ELSE 'STABIL'
    END AS trend,
    CASE
        WHEN NVL(sfc.total_datorii, 0) * 365 / NULLIF(ac.total_achizitii, 0) < 15 THEN 'ATENTIE'
        ELSE 'OK'
    END AS status
FROM achizitii_curente ac, sold_furnizori_curent sfc, achizitii_anterioare aa, sold_furnizori_anterior sfa
"""

# =============================================================================
# 41. CONCENTRARE RISC YoY (comparatie an anterior)
# =============================================================================
CONCENTRARE_RISC_YOY = """
WITH
-- Single scan for current year: compute total + per-client with ranking
vanzari_curent AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12)
    GROUP BY f.id_part
),
ranked_curent AS (
    SELECT vanzari, ROW_NUMBER() OVER (ORDER BY vanzari DESC) AS rn
    FROM vanzari_curent
),
metrics_curent AS (
    SELECT
        SUM(vanzari) AS total,
        SUM(CASE WHEN rn <= 1 THEN vanzari ELSE 0 END) AS top1,
        SUM(CASE WHEN rn <= 5 THEN vanzari ELSE 0 END) AS top5,
        SUM(CASE WHEN rn <= 10 THEN vanzari ELSE 0 END) AS top10
    FROM ranked_curent
),
-- Single scan for previous year
vanzari_anterior AS (
    SELECT /*+ LEADING(f d) USE_NL(d) INDEX(f IDX_VANZARI_NR) */
        f.id_part,
        SUM(d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END) AS vanzari
    FROM vanzari f
    JOIN vanzari_detalii d ON d.id_vanzare = f.id_vanzare AND d.sters = 0
    WHERE f.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
      AND f.data_act < ADD_MONTHS(TRUNC(SYSDATE), -12)
    GROUP BY f.id_part
),
ranked_anterior AS (
    SELECT vanzari, ROW_NUMBER() OVER (ORDER BY vanzari DESC) AS rn
    FROM vanzari_anterior
),
-- Raw metrics for anterior (may have NULL if no data)
metrics_anterior_raw AS (
    SELECT
        SUM(vanzari) AS total,
        SUM(CASE WHEN rn <= 1 THEN vanzari ELSE 0 END) AS top1,
        SUM(CASE WHEN rn <= 5 THEN vanzari ELSE 0 END) AS top5,
        SUM(CASE WHEN rn <= 10 THEN vanzari ELSE 0 END) AS top10
    FROM ranked_anterior
),
-- Fallback to 0 for NULL values (when no anterior data exists)
metrics_anterior AS (
    SELECT
        NVL(total, 0) AS total,
        NVL(top1, 0) AS top1,
        NVL(top5, 0) AS top5,
        NVL(top10, 0) AS top10
    FROM metrics_anterior_raw
),
-- Final metrics: just 1 row each, no cartesian product
combined AS (
    SELECT
        ROUND(mc.top1 * 100.0 / NULLIF(mc.total, 0), 2) AS pct_curent_1,
        CASE WHEN ma.total = 0 THEN NULL ELSE ROUND(ma.top1 * 100.0 / ma.total, 2) END AS pct_anterior_1,
        ROUND(mc.top5 * 100.0 / NULLIF(mc.total, 0), 2) AS pct_curent_5,
        CASE WHEN ma.total = 0 THEN NULL ELSE ROUND(ma.top5 * 100.0 / ma.total, 2) END AS pct_anterior_5,
        ROUND(mc.top10 * 100.0 / NULLIF(mc.total, 0), 2) AS pct_curent_10,
        CASE WHEN ma.total = 0 THEN NULL ELSE ROUND(ma.top10 * 100.0 / ma.total, 2) END AS pct_anterior_10,
        CASE WHEN ma.total > 0 THEN 1 ELSE 0 END AS has_anterior
    FROM metrics_curent mc
    CROSS JOIN metrics_anterior ma
)
SELECT
    'Top 1 client' AS indicator,
    pct_curent_1 AS procent_curent,
    pct_anterior_1 AS procent_anterior,
    CASE WHEN has_anterior = 1 THEN ROUND(pct_curent_1 - pct_anterior_1, 2) ELSE NULL END AS variatie,
    CASE
        WHEN has_anterior = 0 THEN 'FARA DATE YOY'
        WHEN pct_curent_1 < pct_anterior_1 THEN 'DIVERSIFICARE'
        WHEN pct_curent_1 > pct_anterior_1 + 5 THEN 'CONCENTRARE'
        ELSE 'STABIL'
    END AS trend
FROM combined
UNION ALL
SELECT
    'Top 5 clienti' AS indicator,
    pct_curent_5 AS procent_curent,
    pct_anterior_5 AS procent_anterior,
    CASE WHEN has_anterior = 1 THEN ROUND(pct_curent_5 - pct_anterior_5, 2) ELSE NULL END AS variatie,
    CASE
        WHEN has_anterior = 0 THEN 'FARA DATE YOY'
        WHEN pct_curent_5 < pct_anterior_5 THEN 'DIVERSIFICARE'
        WHEN pct_curent_5 > pct_anterior_5 + 5 THEN 'CONCENTRARE'
        ELSE 'STABIL'
    END AS trend
FROM combined
UNION ALL
SELECT
    'Top 10 clienti' AS indicator,
    pct_curent_10 AS procent_curent,
    pct_anterior_10 AS procent_anterior,
    CASE WHEN has_anterior = 1 THEN ROUND(pct_curent_10 - pct_anterior_10, 2) ELSE NULL END AS variatie,
    CASE
        WHEN has_anterior = 0 THEN 'FARA DATE YOY'
        WHEN pct_curent_10 < pct_anterior_10 THEN 'DIVERSIFICARE'
        WHEN pct_curent_10 > pct_anterior_10 + 5 THEN 'CONCENTRARE'
        ELSE 'STABIL'
    END AS trend
FROM combined
"""

# =============================================================================
# 42. INDICATORI AGREGATI VENITURI YoY
# =============================================================================
# OPTIMIZED: Single 24-month scan with CASE-based period partitioning, no FULL OUTER JOIN needed
INDICATORI_AGREGATI_VENITURI_YOY = """
WITH base_data AS (
    SELECT
        CASE
            WHEN d.cont IN ('341', '345') THEN 'Productie proprie'
            WHEN d.cont = '301' THEN 'Materii prime'
            ELSE 'Marfa revanduta'
        END AS linie_business,
        CASE WHEN f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -12) THEN 'CURENT' ELSE 'ANTERIOR' END AS perioada,
        d.cantitate * CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END AS vanzare,
        d.cantitate * (CASE WHEN d.pret_cu_tva = 1 THEN d.pret / (1 + d.proc_tvav/100) ELSE d.pret END - d.pret_achizitie) AS marja
    FROM VANZARI f
    JOIN VANZARI_DETALII d ON d.id_vanzare = f.id_vanzare
    WHERE f.sters = 0 AND d.sters = 0
      AND f.tip > 0 AND f.tip NOT IN (7, 8, 9, 24)
      AND f.data_act >= ADD_MONTHS(TRUNC(SYSDATE), -24)
)
SELECT
    linie_business,
    ROUND(SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END), 0) AS vanzari_curente,
    ROUND(SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END), 0) AS vanzari_anterioare,
    ROUND(SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) - SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END), 0) AS variatie_vanzari,
    ROUND((SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) - SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END)) * 100 / NULLIF(SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END), 0), 2) AS variatie_procent,
    ROUND(SUM(CASE WHEN perioada = 'CURENT' THEN marja ELSE 0 END) * 100 / NULLIF(SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END), 0), 2) AS marja_curenta_pct,
    ROUND(SUM(CASE WHEN perioada = 'ANTERIOR' THEN marja ELSE 0 END) * 100 / NULLIF(SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END), 0), 2) AS marja_anterioara_pct,
    CASE
        WHEN SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) > SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END) * 1.05 THEN 'CRESTERE'
        WHEN SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) < SUM(CASE WHEN perioada = 'ANTERIOR' THEN vanzare ELSE 0 END) * 0.95 THEN 'SCADERE'
        ELSE 'STABIL'
    END AS trend
FROM base_data
GROUP BY linie_business
ORDER BY SUM(CASE WHEN perioada = 'CURENT' THEN vanzare ELSE 0 END) DESC
"""

# Dictionary mapping query names to queries and their parameters
QUERIES = {
    'sumar_executiv': {
        'sql': SUMAR_EXECUTIV,
        'params': {},
        'title': 'Sumar Executiv - KPIs',
        'description': 'Indicatori cheie de performanță pentru ultimele 12 luni'
    },
    'marja_per_client': {
        'sql': MARJA_PER_CLIENT,
        'params': {'months': 12},
        'title': 'Marja per Client',
        'description': 'Analiza profitabilității per client'
    },
    'clienti_marja_mica': {
        'sql': CLIENTI_MARJA_MICA,
        'params': {'months': 12, 'min_sales': 5000, 'margin_threshold': 15},
        'title': '⚠️ ALERTĂ: Clienți cu Marjă Mică',
        'description': 'Clienți cu marjă sub 15% și vânzări peste 5000 RON'
    },
    'vanzari_sub_cost': {
        'sql': VANZARI_SUB_COST,
        'params': {'months': 12},
        'title': '🚨 ALERTĂ CRITICĂ: Vânzări sub Cost',
        'description': 'Produse vândute sub prețul de achiziție'
    },
    'marja_per_categorie': {
        'sql': MARJA_PER_CATEGORIE,
        'params': {'months': 12},
        'title': 'Marja per Categorie',
        'description': 'Profitabilitate per grupă și subgrupă de produse'
    },
    'productie_vs_revanzare': {
        'sql': PRODUCTIE_VS_REVANZARE,
        'params': {'months': 12},
        'title': 'Producție Proprie vs Revânzare',
        'description': 'Comparație marjă: producție proprie vs marfă cumpărată'
    },
    'top_produse': {
        'sql': TOP_PRODUSE,
        'params': {'months': 12},
        'title': 'Top 50 Produse',
        'description': 'Cele mai vândute produse după valoare'
    },
    'marja_per_gestiune': {
        'sql': MARJA_PER_GESTIUNE,
        'params': {'months': 12},
        'title': 'Marja per Gestiune',
        'description': 'Profitabilitate per gestiune/depozit (doar articole gestionabile)'
    },
    'articole_negestionabile': {
        'sql': ARTICOLE_NEGESTIONABILE,
        'params': {'months': 12},
        'title': 'Articole Negestionabile',
        'description': 'Vânzări articole care nu se țin pe stoc (servicii, etc.)'
    },
    'dispersie_preturi': {
        'sql': DISPERSIE_PRETURI,
        'params': {'months': 12},
        'title': 'Dispersia Prețurilor',
        'description': 'Produse vândute la prețuri foarte diferite între clienți'
    },
    'clienti_sub_medie': {
        'sql': CLIENTI_SUB_MEDIE,
        'params': {'months': 12},
        'title': 'Clienți cu Prețuri sub Medie',
        'description': 'Clienți care cumpără cu >15% sub prețul mediu'
    },
    'trending_clienti': {
        'sql': TRENDING_CLIENTI,
        'params': {},
        'title': 'Trending Clienți YoY',
        'description': 'Evoluția vânzărilor per client: an curent vs an trecut'
    },
    'concentrare_clienti': {
        'sql': CONCENTRARE_CLIENTI,
        'params': {'months': 12},
        'title': 'Concentrare Clienți (Top 30)',
        'description': 'Dependența de clienții mari (top 30 + restul)'
    },
    'vanzari_lunare': {
        'sql': VANZARI_LUNARE,
        'params': {},
        'title': 'Vânzări Lunare (24 luni)',
        'description': 'Evoluția vânzărilor și marjei pe luni'
    },
    'stoc_curent': {
        'sql': STOC_CURENT,
        'params': {},
        'title': 'Stoc Curent per Gestiune',
        'description': 'Valoarea stocului actual'
    },
    'stoc_lent': {
        'sql': STOC_LENT,
        'params': {},
        'title': '⚠️ Stoc Lent (>90 zile)',
        'description': 'Produse fără mișcare de peste 90 de zile'
    },
    'rotatie_stocuri': {
        'sql': ROTATIE_STOCURI,
        'params': {},
        'title': 'Rotație Stocuri',
        'description': 'Viteza de rotație a stocurilor'
    },
    'analiza_prajitorie': {
        'sql': ANALIZA_PRAJITORIE,
        'params': {'months': 12},
        'title': 'Analiză Prăjitorie',
        'description': 'Fluxul materiilor prime și producției'
    },
    # =========================================================================
    # NEW: Financial Queries
    # =========================================================================
    'solduri_clienti': {
        'sql': SOLDURI_CLIENTI,
        'params': {},
        'title': 'Solduri Clienți (Creanțe)',
        'description': 'Creanțe din cont 4111 - bani de încasat de la clienți'
    },
    'solduri_furnizori': {
        'sql': SOLDURI_FURNIZORI,
        'params': {},
        'title': 'Solduri Furnizori (Datorii)',
        'description': 'Datorii din cont 401 - bani de plătit către furnizori'
    },
    'aging_creante': {
        'sql': AGING_CREANTE,
        'params': {},
        'title': 'Aging Creanțe',
        'description': 'Vechimea creanțelor pe intervale: <30, 31-60, 61-90, >90 zile'
    },
    'facturi_restante': {
        'sql': FACTURI_RESTANTE,
        'params': {},
        'title': '⚠️ Facturi Restante Clienți',
        'description': 'Facturi clienți depășite ca scadență - necesită urmărire'
    },
    'aging_datorii': {
        'sql': AGING_DATORII,
        'params': {},
        'title': 'Aging Datorii Furnizori',
        'description': 'Vechimea datoriilor către furnizori pe intervale: <30, 31-60, 61-90, >90 zile'
    },
    'facturi_restante_furnizori': {
        'sql': FACTURI_RESTANTE_FURNIZORI,
        'params': {},
        'title': '⚠️ Facturi Restante Furnizori',
        'description': 'Facturi furnizori depășite ca scadență - de plătit urgent'
    },
    'dso_dpo': {
        'sql': DSO_DPO,
        'params': {},
        'title': 'DSO / DPO',
        'description': 'Zile medii încasare clienți (DSO) și plată furnizori (DPO)'
    },
    'pozitia_cash': {
        'sql': POZITIA_CASH,
        'params': {},
        'title': 'Poziția Cash',
        'description': 'Disponibilități în bancă și casă'
    },
    'ciclu_conversie_cash': {
        'sql': CICLU_CONVERSIE_CASH,
        'params': {},
        'title': 'Ciclu Conversie Cash',
        'description': 'CCC = DIO + DSO - DPO (zile de la plată furnizor la încasare client)'
    },
    # =========================================================================
    # NEW: Aggregated Indicators
    # =========================================================================
    'indicatori_agregati_venituri': {
        'sql': INDICATORI_AGREGATI_VENITURI,
        'params': {'months': 12},
        'title': 'Indicatori Agregați Venituri',
        'description': 'Revenue mix per linie de business cu contribuție la profit'
    },
    'sezonalitate_lunara': {
        'sql': SEZONALITATE_LUNARA,
        'params': {},
        'title': 'Sezonalitate Lunară',
        'description': 'Analiza sezonalității pe 24 luni istoric'
    },
    'portofoliu_clienti': {
        'sql': PORTOFOLIU_CLIENTI,
        'params': {},
        'title': 'Portofoliu Clienți',
        'description': 'Sănătatea portofoliului: activi, noi, pierduți, inactivi'
    },
    'frecventa_clienti': {
        'sql': FRECVENTA_CLIENTI,
        'params': {},
        'title': 'Frecvența Clienți',
        'description': 'Frecvența comenzilor și evoluție YoY'
    },
    'concentrare_risc': {
        'sql': CONCENTRARE_RISC,
        'params': {'months': 12},
        'title': 'Concentrare Risc',
        'description': 'Risc concentrare: Top 1/5/10 clienți ca % din total'
    },
    # =========================================================================
    # NEW: Improved Margin Analysis
    # =========================================================================
    'clienti_ranking_profit': {
        'sql': CLIENTI_RANKING_PROFIT,
        'params': {'months': 12},
        'title': 'Clienți Ranking Profit',
        'description': 'Ranking clienți după PROFIT (nu venituri)'
    },
    'marja_client_categorie': {
        'sql': MARJA_CLIENT_CATEGORIE,
        'params': {'months': 12},
        'title': 'Marjă Client/Categorie',
        'description': 'Marjă per categorie PER CLIENT (același client poate fi profitabil/neprofitabil pe categorii diferite)'
    },
    'evolutie_discount': {
        'sql': EVOLUTIE_DISCOUNT,
        'params': {},
        'title': 'Evoluție Discount',
        'description': 'Discount creep - evoluția prețului în timp (alerte scăderi)'
    },
    # =========================================================================
    # NEW: Indicatori Generali și Lichiditate (PLAN_INDICATORI_LICHIDITATE_YOY)
    # =========================================================================
    'indicatori_generali': {
        'sql': INDICATORI_GENERALI,
        'params': {},
        'title': 'Indicatori Generali de Business',
        'description': 'Grad îndatorare, autonomie financiară, ROA, marjă netă'
    },
    'indicatori_lichiditate': {
        'sql': INDICATORI_LICHIDITATE,
        'params': {},
        'title': 'Indicatori de Lichiditate',
        'description': 'Lichiditate curentă, rapidă, cash ratio, fond de rulment'
    },
    'clasificare_datorii': {
        'sql': CLASIFICARE_DATORII,
        'params': {},
        'title': 'Clasificare Datorii pe Termene',
        'description': 'Datorii termen scurt (<30z), mediu (31-90z), lung (>90z)'
    },
    'grad_acoperire_datorii': {
        'sql': GRAD_ACOPERIRE_DATORII,
        'params': {},
        'title': 'Grad Acoperire Datorii',
        'description': 'Cash + încasări așteptate vs datorii scadente, necesar finanțare'
    },
    'proiectie_lichiditate': {
        'sql': PROIECTIE_LICHIDITATE,
        'params': {},
        'title': 'Proiecție Lichiditate 30/60/90 zile',
        'description': 'Sold cash proiectat pe orizonturi de 30, 60 și 90 zile'
    },
    # =========================================================================
    # NEW: Comparații YoY
    # =========================================================================
    'sumar_executiv_yoy': {
        'sql': SUMAR_EXECUTIV_YOY,
        'params': {},
        'title': 'Sumar Executiv YoY',
        'description': 'KPI-uri cu comparație an curent vs an anterior'
    },
    'dso_dpo_yoy': {
        'sql': DSO_DPO_YOY,
        'params': {},
        'title': 'DSO/DPO YoY',
        'description': 'Evoluție zile încasare/plată vs an anterior'
    },
    'concentrare_risc_yoy': {
        'sql': CONCENTRARE_RISC_YOY,
        'params': {},
        'title': 'Concentrare Risc YoY',
        'description': 'Evoluție concentrare Top 1/5/10 clienți vs an anterior'
    },
    'indicatori_agregati_venituri_yoy': {
        'sql': INDICATORI_AGREGATI_VENITURI_YOY,
        'params': {},
        'title': 'Indicatori Agregați Venituri YoY',
        'description': 'Linii de business comparate cu anul anterior'
    },
}