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 ();
         }
     }
}