#!/bin/bash
set -e

source ~/cloud-env.sh

mysql \
   --comments -u "${SERVERLESS_CLUSTER_USERNAME}" -p"${SERVERLESS_CLUSTER_PASSWORD}" \
   -h ${SERVERLESS_CLUSTER_HOST} -P ${SERVERLESS_CLUSTER_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt <<EOF
DROP DATABASE IF EXISTS ${SERVERLESS_CLUSTER_DATABASE_NAME};
CREATE DATABASE ${SERVERLESS_CLUSTER_DATABASE_NAME};
USE ${SERVERLESS_CLUSTER_DATABASE_NAME};
CREATE TABLE color (id INT AUTO_INCREMENT, name TEXT, Point INT, PRIMARY KEY (id));
INSERT INTO color (name, Point) VALUES ('red', 1);
INSERT INTO color (name, Point) VALUES ('orange', 1);
INSERT INTO color (name, Point) VALUES ('yellow', 2);
SOURCE create_bookshop_schema_in_current_db.sql;
EOF

python3 -m venv ~/.venv
~/.venv/bin/pip install mysql-connector-python prettytable dotenv

cat > top3books.py <<EOF
import mysql.connector
import datetime
from prettytable import PrettyTable

config = {
  'user': "${SERVERLESS_CLUSTER_USERNAME}",
  'password': "${SERVERLESS_CLUSTER_PASSWORD}",
  'host': "${SERVERLESS_CLUSTER_HOST}",
  'database': "${SERVERLESS_CLUSTER_DATABASE_NAME}",
  'raise_on_warnings': False,
  'port': "${SERVERLESS_CLUSTER_PORT}",
}

def execQuery(query, queryName):
  print(queryName)
  print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))
  # Executing the query
  cursor.execute(query)
  print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))

  # Fetching all rows from the last executed query
  results = cursor.fetchall()

  # Creating a PrettyTable object
  my_table = PrettyTable()

  # Adding field names (column names) to the table
  field_names = [i[0] for i in cursor.description]
  my_table.field_names = field_names

  # Adding rows to the table
  for row in results:
      my_table.add_row(row)

  # Printing the table
  print(my_table)
  print("==========================")

try:
  # Establishing the connection
  conn = mysql.connector.connect(**config)

  # Creating a cursor object
  cursor = conn.cursor()

  # Your SQL query
  queryName= "■■■Query: Top three books within each genre■■■"
  query = """
  SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title
  FROM books b JOIN ratings r ON b.id = r.book_id JOIN
  (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM
    (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM
      (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv
    ) ov
    WHERE rank_in_type < 4
    GROUP BY ov.type
  ) v ON v.type = b.type
  GROUP BY type, top_3_rated_title
  ORDER BY avg_score DESC;
  """
  execQuery(query, queryName)

except mysql.connector.Error as err:
  print(f"Error: {err}")

finally:
  # Closing the connection
  if conn.is_connected():
      cursor.close()
      conn.close()
      print("MySQL connection is closed")
EOF