/*Program.cs*/

//
// <<Your Name>>
// U. of Illinois, Chicago
// CS 341, Fall 2018
// Project #06: Netflix database application
//

using System;
using System.Data;
using System.Data.SqlClient;

namespace program
{

  class Program
  {
    //
    // Connection info for ChicagoCrimes database in Azure SQL:
    //
    static string connectionInfo = String.Format(@"
Server=tcp:jhummel2.database.windows.net,1433;Initial Catalog=Netflix;
Persist Security Info=False;User ID=student;Password=cs341!uic;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;
Connection Timeout=30;
");


    static void OutputNumMovies()
    {
      SqlConnection db = null;

      try
      {
        db = new SqlConnection(connectionInfo);
        db.Open();

        string sql = string.Format(@"
SELECT Count(*) As NumMovies
FROM Movies;
");

        System.Console.WriteLine(sql);  // debugging:

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = db;
        cmd.CommandText = sql;

        object result = cmd.ExecuteScalar();

        db.Close();

        int numMovies = System.Convert.ToInt32(result);

   System.Console.WriteLine("Number of movies: {0}", numMovies);
      }
      catch (Exception ex)
      {
        System.Console.WriteLine();
        System.Console.WriteLine("**Error: {0}", ex.Message);
        System.Console.WriteLine();
      }
      finally
      {
   // make sure we close connection no matter what happens:
        if (db != null && db.State != ConnectionState.Closed)
          db.Close();
      }
    }





       static void userInfo(string queryIN){
          SqlConnection db = null;


          try
          {
            db = new SqlConnection(connectionInfo);

            db.Open();
            string sql = queryIN ;
             SqlCommand  cmd =  new  SqlCommand();
             cmd.Connection =  db;
             SqlDataAdapter  adapter  =  new  SqlDataAdapter(cmd);
             DataSet  ds =  new  DataSet();
             cmd.CommandText  =  sql;
             adapter.Fill(ds);
             var  rows  =  ds.Tables["TABLE"].Rows;
             //System.Console.WriteLine(rows.Count);    //debugging
             if (rows.Count == 0){
                 Console.WriteLine("** User not found...");
             }

             foreach  (DataRow  row  in  rows)  {

                int id = System.Convert.ToInt32(row["UserID"]);
                string userName =  System.Convert.ToString(  row["UserName"]  );
                string occu = System.Convert.ToString(row["Occupation"]);
                int numReviews = System.Convert.ToInt32(row["NumReviews"]);
                int oneStar = System.Convert.ToInt32(row["OneStar"]);
                int twoStar = System.Convert.ToInt32(row["TwoStar"]);
                int threeStar = System.Convert.ToInt32(row["ThreeStar"]);
                int fourStar = System.Convert.ToInt32(row["FourStar"]);
                int fiveStar = System.Convert.ToInt32(row["FiveStar"]);

                if(row["AvgRating"] != System.DBNull.Value){
                         float avgRating = System.Convert.ToSingle(row["AvgRating"]);
                Console.WriteLine("{0}\nUser id: {1}\nOccupation: {2}\nAvg rating: {3:0.00000}\nNum Reviews: {4}\n 1 star: {5}\n 2 stars: {6}\n 3 stars: {7}\n 4 stars: {8}\n 5 stars: {9}", userName, id, occu, avgRating, numReviews, oneStar, twoStar, threeStar, fourStar, fiveStar);
                }
                 else {
                     string avgRating = "N/A";
                          Console.WriteLine("{0}\nUser id: {1}\nOccupation: {2}\nAvg rating: {3}\nNum Reviews: {4}\n 1 star: {5}\n 2 stars: {6}\n 3 stars: {7}\n 4 stars: {8}\n 5 stars: {9}", userName, id, occu, avgRating, numReviews, oneStar, twoStar, threeStar, fourStar, fiveStar);
                 }
          }
      }

          catch  (Exception  ex)  {
              System.Console.WriteLine("Error:  {0}",  ex.Message);
          }
          finally  {  //  success  or  failure,  make  sure  connection  is  closed:
              if  (db  !=  null  &&  db.State  !=  ConnectionState.Closed)  db.Close();

          }

      }



      static void userInfoHelper(){
            System.Console.Write("Enter user id or name>>");
            string input = System.Console.ReadLine();
            System.Console.WriteLine();
            string query = "" ;
            int id;


         if(System.Int32.TryParse(input,  out id)){
         query = string.Format(@"
         SELECT UserName, Users.UserID, Occupation, count(Rating) as NumReviews, AVG(Convert(float, Rating)) as AvgRating,
         coalesce(count(case when Rating = 1 then 1 end), 0) as OneStar,
        coalesce(count(case when Rating = 2 then 1 end), 0) as TwoStar,
        coalesce(count(case when Rating = 3 then 1 end), 0) as ThreeStar,
        coalesce(count(case when Rating = 4 then 1 end), 0) as FourStar,
        coalesce(count(case when Rating = 5 then 1 end), 0) as FiveStar
        From Users
        LEFT JOIN Reviews ON Users.UserID = Reviews.UserID
        Where Users.UserID = {0}
        Group By UserName, Users.UserID, Occupation
        ", id);
            }

            else {
               input = input.Replace("'", "''");
               query = string.Format(@"
                SELECT UserName, Users.UserID, Occupation, count(Rating) as NumReviews, AVG(Convert(float, Rating)) as AvgRating,
                coalesce(count(case when Rating = 1 then 1 end), 0) as OneStar,
                coalesce(count(case when Rating = 2 then 1 end), 0) as TwoStar,
                coalesce(count(case when Rating = 3 then 1 end), 0) as ThreeStar,
                coalesce(count(case when Rating = 4 then 1 end), 0) as FourStar,
                coalesce(count(case when Rating = 5 then 1 end), 0) as FiveStar
                From Users
                LEFT JOIN Reviews ON Users.UserID = Reviews.UserID
                Where Users.UserName = '{0}'
                Group By UserName, Users.UserID, Occupation
                ", input);

            }
          userInfo(query);

      }



      static void movieInfo(string queryIN){
          SqlConnection db = null;


          try
          {
            db = new SqlConnection(connectionInfo);

            db.Open();
            string sql = queryIN ;



             SqlCommand  cmd =  new  SqlCommand();
             cmd.Connection =  db;
             SqlDataAdapter  adapter  =  new  SqlDataAdapter(cmd);
             DataSet  ds =  new  DataSet();
             cmd.CommandText  =  sql;
             adapter.Fill(ds);
             var  rows  =  ds.Tables["TABLE"].Rows;
             //System.Console.WriteLine(rows.Count);    //debugging
             if (rows.Count == 0){
                 Console.WriteLine("** Movie not found...");
             }

             foreach  (DataRow  row  in  rows)  {




                    int id = System.Convert.ToInt32(row["MovieID"]);
                    string movieName =  System.Convert.ToString(  row["MovieName"]  );
                    int year = System.Convert.ToInt32(row["MovieYear"]);
                    int numReviews = System.Convert.ToInt32(row["NumReviews"]);
                    if(row["AvgRating"] != System.DBNull.Value){
                         float avgRating = System.Convert.ToSingle(row["AvgRating"]);
                         Console.WriteLine("{0}\n'{1}'\nYear: {2}\nNum Reviews: {3}\nAvg rating: {4:0.00000}\n\n", id, movieName, year, numReviews, avgRating);
                    }
                     else{
                   string avgRating = "N/A";
                  Console.WriteLine("{0}\n'{1}'\nYear: {2}\nNum Reviews: {3}\nAvg rating: {4}\n\n", id, movieName, year, numReviews, avgRating);
                     }
          }
      }

          catch  (Exception  ex)  {
              System.Console.WriteLine("Error:  {0}",  ex.Message);
          }
          finally  {  //  success  or  failure,  make  sure  connection  is  closed:
              if  (db  !=  null  &&  db.State  !=  ConnectionState.Closed)  db.Close();

          }

      }

      static void movieInfoHelper(){

            System.Console.Write("Enter movie id or part of movie name>>");
            string input = System.Console.ReadLine();
            System.Console.WriteLine();
            string query = "" ;
            int id;


            if(System.Int32.TryParse(input,  out id)){
            query = string.Format(@"
            SELECT Movies.MovieID,MovieName, MovieYear, count(Rating) as NumReviews, AVG(Convert(float,Rating)) as AvgRating
            From Movies
            LEFT JOIN Reviews ON Movies.MovieID = Reviews.MovieID
            Where Movies.MovieID = {0}
            Group By MovieName, MovieYear, Movies.MovieID
            Order BY MovieName asc
             ", id);
            }
            else {
               input = input.Replace("'", "''");
               query = string.Format(@"
                SELECT Movies.MovieID,MovieName, MovieYear, count(Rating) as NumReviews, AVG(Convert(float,Rating)) as AvgRating
                From Movies
                LEFT JOIN Reviews ON Movies.MovieID = Reviews.MovieID
                Where Movies.MovieName LIKE'%{0}%'
                Group By MovieName, MovieYear, Movies.MovieID
                Order BY MovieName asc
                 ", input);

            }
          movieInfo(query);

      }




      static void top10Movies()
      {

          SqlConnection db = null;

          try
          {


             db = new SqlConnection(connectionInfo);
             db.Open();
             string sql = string.Format(@"
             SELECT TOP 10  ROW_NUMBER() OVER(ORDER BY (SELECT 10)) Rank, Movies.MovieID, count(Rating) as NumReviews, AVG(Convert(float,Rating)) as AvgRating, MovieName
             From Movies
             Inner JOIN Reviews ON Movies.MovieID = Reviews.MovieID
             Group By Movies.MovieName , Movies.MovieID
             Order by AvgRating desc, Movies.Moviename asc;
             ");


             SqlCommand  cmd =  new  SqlCommand();
             cmd.Connection =  db;
             SqlDataAdapter  adapter  =  new  SqlDataAdapter(cmd);
             DataSet  ds =  new  DataSet();
             cmd.CommandText  =  sql;
             adapter.Fill(ds);
             var  rows  =  ds.Tables["TABLE"].Rows;
             //System.Console.WriteLine(rows.Count);    //rebugging


             Console.WriteLine("Rank\tMovieID\tNumReviews\tAvgRating\tMovieName");

             foreach  (DataRow  row  in  rows)  {

                    int rank = System.Convert.ToInt32(row["Rank"]);
                    int id = System.Convert.ToInt32(row["MovieID"]);
                    int numReviews = System.Convert.ToInt32(row["NumReviews"]);
                    float avgRating = System.Convert.ToSingle(row["AvgRating"]);
                    string movieName =  System.Convert.ToString(  row["MovieName"]  );


                  Console.WriteLine("{0}\t{1}\t{2}\t\t{3:0.00000}\t\t'{4}'", rank, id,numReviews,avgRating,movieName);

          }
      }

          catch  (Exception  ex)  {
              System.Console.WriteLine("Error:  {0}",  ex.Message);
          }
          finally  {  //  success  or  failure,  make  sure  connection  is  closed:
              if  (db  !=  null  &&  db.State  !=  ConnectionState.Closed)  db.Close();
          }
      }


    static string GetUserCommand()
    {
      System.Console.WriteLine();
      System.Console.WriteLine("What would you like?");
      System.Console.WriteLine("m. movie info");
      System.Console.WriteLine("t. top-10 info");
      System.Console.WriteLine("u. user info");
      System.Console.WriteLine("x. exit");
      System.Console.Write(">> ");

      string cmd = System.Console.ReadLine();

      return cmd.ToLower();
    }


    //
    // Main:
    //
    static void Main(string[] args)
    {
      System.Console.WriteLine("** Netflix Database App **");

      string cmd = GetUserCommand();

      while (cmd != "x")
      {
 //if(cmd == "m"){
        //System.Console.WriteLine();
 //  OutputNumMovies();
        //}
        if(cmd== "t"){
           System.Console.WriteLine();
                top10Movies();
        }
        if(cmd == "m"){
            System.Console.WriteLine();
            movieInfoHelper();

        }
        if(cmd == "u"){
            System.Console.WriteLine();
            userInfoHelper();
        }
        cmd = GetUserCommand();
      }

      System.Console.WriteLine();
      System.Console.WriteLine("** Done **");
      System.Console.WriteLine();
    }

  }//class
}//namespace