package homework4;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class hw4
{
    //  Database credentials
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/sakila";

    public static void main(String[] args) throws ClassNotFoundException
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            conn = DriverManager.getConnection(DB_URL,"root","");

            //STEP 4: Execute a query
            stmt = conn.createStatement();

            //Query to get a list of all actor names and the number of movies they starred in
            String sql = "SELECT actor.first_name, actor.last_name, actor.actor_id, count(*) as movie_count\r\n" +
                    "FROM film, film_actor, actor\r\n" +
                    "WHERE film.film_id = film_actor.film_id\r\n" +
                    "AND film_actor.actor_id = actor.actor_id\r\n" +
                    "AND film.film_id = film_actor.film_id\r\n" +
                    "GROUP BY film_actor.actor_id";
            ResultSet rs = stmt.executeQuery(sql);

            int max_movie_count = 0;

            while(rs.next()) //Find highest movie count
            {
                int movie_count = rs.getInt("movie_count");
                if(movie_count > max_movie_count)
                    max_movie_count = movie_count;
            }
            rs.first();
            List<String> max_actor_ids = new ArrayList<String>();
            List<String> max_actor_names = new ArrayList<String>();

            while(rs.next()) //Find actor(s) with the highest movie count
            {
                String actor_id = rs.getString("actor_id");
                String actor_name = rs.getString("first_name") + " " + rs.getString("last_name");
                int movie_count = Integer.parseInt(rs.getString("movie_count"));
                if(movie_count == max_movie_count)
                {
                    max_actor_ids.add(actor_id);
                    max_actor_names.add(actor_name);
                }
            }

            for(int i = 0; i < max_actor_ids.size(); ++i) //Find top 3 most revenue earning movies for each
            {                                               //actor that had starred in the most movies
                String actor_id = max_actor_ids.get(i);
                String actor_name  = max_actor_names.get(i);
                stmt = conn.createStatement();
                //Query to find the top 3 revenue earning movies for an actor
                sql = "SELECT film.title, rental_rate*count(*) as rental_value, rental_rate, count(*)\r\n" +
                        "FROM film, inventory, rental\r\n" +
                        "WHERE film.film_id IN (\r\n" +
                        "   SELECT film.film_id\r\n" +
                        "   FROM film, film_actor\r\n" +
                        "   WHERE film.film_id = film_actor.film_id\r\n" +
                        "    AND film_actor.actor_id =" + actor_id + "\r\n" + // <-- using the ID from the previous query
                        "   )\r\n" +
                        "AND film.film_id = inventory.film_id\r\n" +
                        "AND inventory.inventory_id = rental.inventory_id\r\n" +
                        "GROUP BY film.film_id\r\n" +
                        "ORDER BY rental_value desc\r\n" +
                        "LIMIT 3\r\n";
                rs =  stmt.executeQuery(sql);

                //Outputting actor information
                System.out.println("Actor who starred in the most movies: " + actor_name + "(actor ID " + actor_id + ")");
                System.out.println("    acted in " + max_movie_count + " movies, top three revenue earning movies are:");
                int j  = 0;
                while(rs.next()) //Print the top 3 revenue earning movies
                {
                    ++j;
                    String film_title = rs.getString("title");
                    String rental_value = rs.getString("rental_value");
                    System.out.println("    Movie #" + j + ": " + film_title + ", $" + rental_value);
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        }
        catch(SQLException se2)
        {
            se2.printStackTrace();
        }
    }
}