#-----------------------------------------------------------------------
# Sobre Docker e SQL.

# Listar os containers.
#$ sudo docker container ls -a
system("sudo docker container ls -a")

# Verificar se o container está rodando.
#$ sudo docker container inspect bb2174b03caa | grep Status
jsonlite::fromJSON(system("sudo docker container inspect bb2174b03caa",
                          intern = TRUE))[["State"]][["Status"]]

# Certificar que o BD está disponível.
#$ sudo docker container restart bb2174b03caa
#$ sudo docker container stop bb2174b03caa
system("sudo docker container restart bb2174b03caa")
system("sudo docker container start bb2174b03caa")

# TODO: Descobrir como rodar SQL (*.SQL) dentro do Emacs.
# https://arjanvandergaag.nl/blog/using-emacs-as-a-database-client.html

# Habilitar o ChatGPT no DBeaver.
# https://dbeaver.com/docs/dbeaver/AI-Smart-Assistance/
# https://www.youtube.com/watch?v=ujXKnUZKrNM
# https://www.youtube.com/watch?v=MOaEpDa5GP0

# Seguir o processo de instalação a seguir.
# https://dbeaver.com/docs/dbeaver/AI-Smart-Assistance/#installing-the-gpt-extension
# IMPORTANT: Precisa de uma API Key da OpenAI.

# NOTE: Dá pra usar o Ollama, Gemini e outros mas aí tem que ser DBeaver
# PRO (Lite, Entrerprise, etc).

#-----------------------------------------------------------------------
# Pacotes.

# library(DBI)
library(dbplyr)
library(tidyverse)

#-----------------------------------------------------------------------
# Conexão com o banco de dados.

con <- DBI::dbConnect(RPostgres::Postgres(),
                      dbname = "postgres",
                      host = "localhost",
                      port = 5432L,
                      user = "postgres",
                      password = "postgres")

# Lista as tabelas disponíveis.
DBI::dbListTables(con)

# Lista os campos de uma tabela.
DBI::dbListFields(con, "actor")

# Lê uma tabela.
DBI::dbReadTable(con, "actor")

# Usar instruções SQL.
res <- DBI::dbSendQuery(con, "SELECT a.first_name, a.last_name FROM actor as a LIMIT 6;")
DBI::dbFetch(res)
DBI::dbClearResult(res)

#-----------------------------------------------------------------------
# Usando o `dbplyr`.

# dbplyr::sql_render(tbl_table)

# Realiza a consulta.
con |>
    tbl("actor") |>
    select(first_name, last_name) |>
    rename(primeiro_nome = first_name, ultimo_nome = last_name) |>
    head()

# Faz consulta usando verbos do `dplyr` e mostra a tradução em SQL.
con |>
    tbl("actor") |>
    select(first_name, last_name) |>
    rename(primeiro_nome = first_name, ultimo_nome = last_name) |>
    head() |>
    # sql_render()
    show_query()

con |>
    tbl("rental") |>
    colnames()

#-----------------------------------------------------------------------
# Fazendo um join.

# Últimos registros.
tb1 <-
    con |>
    tbl("rental") |>
    slice_max(rental_date, n = 5)

# O inventário.
tb2 <-
    con |>
    tbl("inventory") |>
    select(inventory_id, film_id)

# Os filmes.
tb3 <-
    con |>
    tbl("film") |>
    select(film_id, title, release_year)

# Mostra as consultas.
tb1 |>
    show_query()
tb2 |>
    show_query()
tb3 |>
    show_query()

# Faz o join e mostra a consulta.
left_join(tb1, tb2, by = "inventory_id") |>
    left_join(tb3, by = "film_id") |>
    select(rental_date, title, release_year) |>
    show_query()

#-----------------------------------------------------------------------

query <- 'CREATE VIEW last_films AS SELECT "rental_date", "title", "release_year"
FROM (
  SELECT
    "rental_id",
    "rental_date",
    "inventory_id",
    "customer_id",
    "return_date",
    "staff_id",
    "last_update"
  FROM (
    SELECT "rental".*, RANK() OVER (ORDER BY "rental_date" DESC) AS "col01"
    FROM "rental"
  ) AS "q01"
  WHERE ("col01" <= 5)
) AS "...1"
LEFT JOIN "inventory"
  ON ("...1"."inventory_id" = "inventory"."inventory_id")
LEFT JOIN "film"
  ON ("inventory"."film_id" = "film"."film_id")'

# Para criar a VIEW (Dar F5 no BD para ver).
DBI::dbSendQuery(con, query) |>
    DBI::dbFetch()

# Consulta a VIEW.
DBI::dbSendQuery(con, "SELECT * FROM last_films;") |>
    DBI::dbFetch()

# Para apagar a VIEW (Dar F5 no BD para ver).
DBI::dbSendQuery(con, "DROP VIEW last_films;")

#-----------------------------------------------------------------------
# Fecha a conexão.

DBI::dbDisconnect(con)

#-----------------------------------------------------------------------