```{r}
library(reticulate)#abrindo o pacote reticulate
```

```{r}
virtualenv_create("env_me315", python = "C:/Users/Gustavo/AppData/Local/Programs/Python/Python314/python.exe")#criando um ambiente virtual pra usar python
```

```{r}
use_virtualenv("env_me315", required = TRUE) #manda usar o ambiente virtual criado
```

```{python}
#!pip install polars
#!pip install pandas
```

```{python}
#Instalando os pacotes necessarios
import polars as pl
import sqlite3
import os
import gc
import pandas as pd
```


```{python}
# Leitura dos 3 arquivos TSV (com gzip)
basics = pl.read_csv("title.basics0.tsv.gz", separator="\t", null_values="\\N")
ratings = pl.read_csv("title.ratings.tsv.gz", separator="\t", null_values="\\N")
principals = pl.read_csv("title.principals0.tsv.gz", separator="\t", null_values="\\N")

```

```{python}
#conferindo se ja criei para quando rodar denovo nao criar duas vezes ou corromper
if os.path.exists("movies.sqlite3"):
  os.remove("movies.sqlite3")
```


```{python}
#abrindo a conexao 
conn = sqlite3.connect("movies.sqlite3")
cursor = conn.cursor()
```

```{python}
cursor.execute('''
CREATE TABLE IF NOT EXISTS basics (
    tconst TEXT PRIMARY KEY,
    titleType TEXT,
    primaryTitle TEXT,
    originalTitle TEXT,
    isAdult INTEGER,
    startYear INTEGER,
    endYear INTEGER,
    runtimeMinutes INTEGER,
    genres TEXT
)
''')
# Cria a tabela 'basics' no banco de dados, caso ela ainda não exista.
# Define as colunas principais relacionadas aos títulos (filmes, séries, etc.).
# 'tconst' é a chave primária que identifica unicamente cada título.
# Tipos de dados: TEXT para textos e INTEGER/REAL para números.

cursor.execute('''
CREATE TABLE IF NOT EXISTS ratings (
    tconst TEXT PRIMARY KEY,
    averageRating REAL,
    numVotes INTEGER,
    FOREIGN KEY (tconst) REFERENCES basics (tconst)
)
''')
# Cria a tabela 'ratings' para armazenar as avaliações dos títulos.
# 'tconst' é novamente a chave primária e também chave estrangeira referenciando 'basics'.
# 'averageRating' guarda a nota média do título.
# 'numVotes' guarda o número total de votos recebidos.
# O FOREIGN KEY assegura integridade referencial com a tabela 'basics'.

cursor.execute('''
CREATE TABLE IF NOT EXISTS principals (
    tconst TEXT,
    nconst TEXT,
    category TEXT,
    job TEXT,
    characters TEXT,
    FOREIGN KEY (tconst) REFERENCES basics (tconst)
)
''')
# Cria a tabela 'principals' que relaciona títulos com pessoas (atores, diretores, etc.).
# 'tconst' identifica o título e 'nconst' identifica a pessoa.
# 'category' descreve o papel (ex: actor, director).
# 'job' e 'characters' guardam funções e personagens associados.
# Define 'tconst' como chave estrangeira referenciando 'basics' para manter consistência.


```

```{python}
# --- configurações de performance SQLite (executar uma vez antes dos inserts) ---
cursor.execute("PRAGMA synchronous = OFF;")
cursor.execute("PRAGMA journal_mode = WAL;")
cursor.execute("PRAGMA temp_store = MEMORY;")
# opcional: aumentar cache
cursor.execute("PRAGMA cache_size = -200000;")  # tamanho do cache em KB negativo = KB* -1 = memória

# --- definir colunas exatamente como suas tabelas SQLite foram criadas ---
cols_basics = ["tconst","titleType","primaryTitle","originalTitle","isAdult","startYear","endYear","runtimeMinutes","genres"]
cols_ratings = ["tconst","averageRating","numVotes"]
cols_principals = ["tconst","nconst","category","job","characters"]  # só as 5 que a tabela tem

# função utilitária para inserir em chunks a partir de um polars DataFrame
def insert_polars_into_sqlite(df, table_name, cols, chunk_size=100_000):
    placeholders = ",".join("?" for _ in cols)
    sql = f"INSERT OR REPLACE INTO {table_name} ({','.join(cols)}) VALUES ({placeholders})"
    n = df.height
    # percorrer por fatias; polars slicing é eficiente
    for off in range(0, n, chunk_size):
        chunk = df.slice(off, min(chunk_size, n - off)).select(cols)  # garante ordem e colunas
        dicts = chunk.to_dicts()  # lista de dicionários (sem precisar de pyarrow)
        # converter dicts para tuplas na ordem de cols
        rows = [tuple(d.get(c, None) for c in cols) for d in dicts]
        if rows:
            cursor.executemany(sql, rows)
    conn.commit()

# --- executar para cada tabela ---
insert_polars_into_sqlite(basics, "basics", cols_basics, chunk_size=100000)
insert_polars_into_sqlite(ratings, "ratings", cols_ratings, chunk_size=100000)
insert_polars_into_sqlite(principals, "principals", cols_principals, chunk_size=100000)

# --- (opcional) restaurar pragmas padrão depois se quiser ---
cursor.execute("PRAGMA synchronous = FULL;")
cursor.execute("PRAGMA journal_mode = DELETE;")

# confirmação rápida
print("Inserções concluídas. Contagens:")
for t in ["basics","ratings","principals"]:
    cursor.execute(f"SELECT COUNT(*) FROM {t};")
    print(t, cursor.fetchone()[0])

# fechar conexão quando terminar
# conn.close()

```

##pergunta 2 

```{python}

cursor.execute("""
SELECT 
    b.primaryTitle,
    r.averageRating,
    r.numVotes
FROM ratings r
JOIN basics b
    ON r.tconst = b.tconst
ORDER BY r.averageRating DESC, r.numVotes DESC
LIMIT 5;
""")
# Executa uma consulta SQL que seleciona os 5 títulos com as melhores avaliações.
# Faz um JOIN entre as tabelas 'ratings' (r) e 'basics' (b) usando o campo 'tconst' como ligação.
# Seleciona as colunas 'primaryTitle' (nome do título), 'averageRating' (nota média) e 'numVotes' (número de votos).
# Ordena os resultados primeiro pela nota média em ordem decrescente, e depois pelo número de votos (também decrescente).
# O comando LIMIT 5 retorna apenas os 5 primeiros resultados.

top5 = cursor.fetchall()
# Recupera todos os resultados da consulta SQL e armazena na variável 'top5' como uma lista de tuplas.

for row in top5:
    print(row)
# Percorre cada linha dos resultados e imprime os dados (título, nota média e número de votos) no console.


```

##pergunta 3

```{python}
# query para encontrar o gênero mais frequente entre filmes com nota > 8
query = """
WITH high_rating AS (
    SELECT b.genres
    FROM basics b
    JOIN ratings r ON b.tconst = r.tconst
    WHERE r.averageRating > 8
),
exploded AS (
    SELECT TRIM(value) AS genre
    FROM high_rating,
         json_each('["' || REPLACE(genres, ',', '","') || '"]')
)
SELECT genre, COUNT(*) AS freq
FROM exploded
GROUP BY genre
ORDER BY freq DESC
LIMIT 1;
"""

cursor.execute(query)
result = cursor.fetchone()
print("Gênero mais frequente (nota > 8):", result)

# Explicação linha a linha:
# A variável `query` armazena um comando SQL multilinha.
# O bloco WITH define duas CTEs (subconsultas temporárias):
# 1. high_rating → seleciona os gêneros dos filmes com média acima de 8.
# 2. exploded → transforma a string de gêneros (como “Action,Drama”) em várias linhas usando json_each.
# Depois, a query principal faz uma contagem (COUNT) dos gêneros e os ordena do mais frequente para o menos.
# O comando `cursor.execute(query)` envia a query ao banco SQLite.
# `cursor.fetchone()` captura apenas o primeiro resultado (o gênero mais frequente).
# Por fim, o `print` exibe o resultado no console.

```

```{python}
query = """
SELECT p.nconst, COUNT(*) AS num_films
FROM principals p
JOIN ratings r ON p.tconst = r.tconst
WHERE r.averageRating > 7.5
  AND p.category IN ('actor', 'actress')
GROUP BY p.nconst
ORDER BY num_films DESC
LIMIT 3;
"""

cursor.execute(query)
top_actors = cursor.fetchall()

print("3 atores/atrizes que mais participaram de filmes com nota > 7.5:")
for nconst, count in top_actors:
    print(nconst, count)

# Explicação linha a linha:
# A variável `query` contém um comando SQL que busca os atores/atrizes mais recorrentes em filmes com nota > 7.5.
# A instrução SELECT escolhe o identificador do ator/atriz (`p.nconst`) e conta quantos filmes (`COUNT(*)`) ele/ela participou.
# O JOIN conecta a tabela `principals` (p) com `ratings` (r) através da coluna `tconst`, que identifica o título.
# A cláusula WHERE filtra apenas filmes com média acima de 7.5 e categorias "actor" ou "actress".
# GROUP BY agrupa os resultados por pessoa (nconst) para fazer a contagem de filmes.
# ORDER BY organiza os resultados pela quantidade de filmes em ordem decrescente.
# LIMIT 3 retorna apenas os 3 primeiros resultados (os mais atuantes).
# `cursor.execute(query)` envia a query para o banco SQLite.
# `cursor.fetchall()` recupera todos os resultados retornados pela consulta.
# O `print` inicial exibe uma mensagem descritiva.
# O loop `for` percorre os resultados e imprime o código da pessoa (`nconst`) e o número de filmes.

```

```{r}
# Captura o horário atual
horario_geracao <- Sys.time()

# Mostrar no console
horario_geracao

# Ou inserir em um HTML (exemplo com cat)
cat("<p>Relatório gerado em:", horario_geracao, "</p>")
```



