index.qmd

---
output: html
jupyter: python3
execute: 
  echo: false
  warning: false
---

```{python}
#| label: imports
#| output: false

import pandas as pd
from utils.db_connection import query_to_dataframe
pd.set_option('display.max_colwidth', None)
```

## Domeniul de Competență

```{python}
#| label: comp-areas
#| output: true

areas_df = query_to_dataframe("""
    SELECT 
        ca.nume as "Arie",
        ca.descriere as "Descriere"
    FROM digcomp.comp_arie ca
    ORDER BY ca.id
""")
try:
    display(areas_df.style.hide(axis='index'))
except:
    # Fallback for even older pandas versions
    display(areas_df)
    print("Note: First column shown is just the index, not part of your data")
```

## Competența

```{python}
#| label: descriptors
#| output: true

descriptors_df = query_to_dataframe("""
    SELECT 
        ca.nume as "Arie",
        cd.scaff_cod as "Cod",
        cd.nume as "Nume",
        cd.descriere as "Descriere"
    FROM digcomp.comp_arie ca
    LEFT JOIN digcomp.comp_descriptor cd ON ca.id = cd.comp_arie_id
    ORDER BY ca.id, cd.scaff_cod
""")

# Group and display by area
for arie in descriptors_df["Arie"].unique():
    area_descriptors = descriptors_df[descriptors_df["Arie"] == arie]
    try:
        display(area_descriptors[["Cod", "Nume", "Descriere"]].style.hide(axis='index'))
    except:
        # Fallback if .hide() doesn't work
        from IPython.display import HTML
        display(HTML(area_descriptors[["Cod", "Nume", "Descriere"]].to_html(index=False)))
    print("\n")  # Add spacing between areas
```

## Structura Competențelor

```{python}
#| label: competency-structure
#| output: true

structure_df = query_to_dataframe("""
SELECT 
    cd.scaff_cod as "Cod",
    cd.nume as "Competenta",
    cn.granular_nivel as "Nivel Granular de Performanță",
    cnc.nume as "Nivel de Performanță",
    pc.nivel as "Proces Cognitiv",
    cn.complexitate as "Complexitate",
    cn.autonomie as "Autonomie",
    COUNT(ce.id) as "Numar Exemple"
FROM digcomp.comp_descriptor cd
JOIN digcomp.comp_nivel_descriptor cnd ON cd.id = cnd.comp_descriptor_id
JOIN digcomp.comp_nivel cn ON cnd.comp_nivel_id = cn.id
JOIN digcomp.comp_nivel_categorie cnc ON cn.comp_nivel_categorie_id = cnc.id
JOIN digcomp.proces_cognitiv pc ON cn.proces_cognitiv_id = pc.id
LEFT JOIN digcomp.comp_exemplu ce ON cd.id = ce.comp_descriptor_id
WHERE cd.scaff_cod = 'd.1'
GROUP BY 
    cd.scaff_cod,
    cd.nume,
    cn.granular_nivel,
    cnc.nume,
    pc.nivel,
    cn.complexitate,
    cn.autonomie
ORDER BY cn.granular_nivel;
""")
try:
    display(structure_df.style.hide(axis='index'))
except:
    display(structure_df)
```

## Nivelul de perfomanță

```{python}
#| label: prof-levels
#| output: true

levels_df = query_to_dataframe("""
    SELECT 
        cn.granular_nivel as "Nivel Granular de Perfromanță",
        cnc.nume as "Nivel de Perfromanță",
        cn.complexitate as "Complexitate",
        cn.autonomie as "Autonomie",
        pc.nivel as "Proces Cognitiv"
    FROM digcomp.comp_nivel cn
    JOIN digcomp.comp_nivel_categorie cnc ON cn.comp_nivel_categorie_id = cnc.id
    JOIN digcomp.proces_cognitiv pc ON cn.proces_cognitiv_id = pc.id
    ORDER BY cn.granular_nivel
""")
try:
    display(levels_df.style.hide(axis='index'))
except:
    # Fallback for even older pandas versions
    display(levels_df)
    print("Note: First column shown is just the index, not part of the data")
```

## Rezultate de învățare pe nivel de competență

```{python}
#| label: learning-outcomes
#| output: true

outcomes_df = query_to_dataframe("""
WITH nivel_info AS (
    SELECT cn.id, cn.granular_nivel, cn.complexitate, cn.autonomie,
           cnc.nume as categorie,
           pc.nivel as proces_cognitiv
    FROM digcomp.comp_nivel cn
    JOIN digcomp.comp_nivel_categorie cnc ON cn.comp_nivel_categorie_id = cnc.id
    JOIN digcomp.proces_cognitiv pc ON cn.proces_cognitiv_id = pc.id
    WHERE cn.granular_nivel = 1
)
SELECT 
    'General' as "Tip Rezultat",
    rig.descriere as "Descriere Sarcină",
    ni.categorie as "Nivel Performanță",
    ni.complexitate as "Complexitate",
    ni.autonomie as "Autonomie",
    ni.proces_cognitiv as "Proces Cognitiv"
FROM nivel_info ni
JOIN digcomp.rezultat_invatare_general rig ON rig.comp_nivel_id = ni.id
UNION ALL
SELECT 
    'Specific' as "Tip Rezultat",
    ris.descriere as "Descriere",
    ni.categorie as "Nivel Performanță",
    ni.complexitate as "Complexitate",
    ni.autonomie as "Autonomie",
    ni.proces_cognitiv as "Proces Cognitiv"
FROM nivel_info ni
JOIN digcomp.rezultat_invatare_specific ris ON ris.comp_nivel_id = ni.id
ORDER BY "Tip Rezultat";
""")
try:
    display(outcomes_df.style.hide(axis='index'))
except:
    display(outcomes_df)
```