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

/**
 * Created by nate on 4/10/16.
 */
public class Game extends Model {
    public int id;
    public String name;
    public float price;

    public static List<Game> getAllGames() {
        List<Game> games = new ArrayList<Game>();
        try {
            PreparedStatement s = c.prepareStatement(
                    "SELECT id, name, price from game"
            );
            ResultSet rs = s.executeQuery();
            Game g;
            while (rs.next()) {         // read the result set
                g = new Game();
                g.id = rs.getInt("id");
                g.name = rs.getString("name");
                g.price = rs.getFloat("price");

                games.add(g);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return games;
    }

    public List<Category> categories() {
        try {
            List<Category> categories = new ArrayList<Category>();
            Category cat;

            PreparedStatement s = c.prepareStatement(
                    "SELECT id, name from game_category join category on id=category_id where game_id=?"
            );
            s.setInt(1, id);

            ResultSet rs = s.executeQuery();
            while (rs.next()) {
                cat = new Category();
                cat.id = rs.getInt("id");
                cat.name = rs.getString("name");

                categories.add(cat);
            }

            return categories;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public float getCriticRating() {
        try {
            PreparedStatement s = c.prepareStatement("SELECT AVG(rating) from CRITIC_REVIEW where game_id=? GROUP BY GAME_ID");
            s.setInt(1, id);

            ResultSet rs = s.executeQuery();
            return rs.getFloat(1);
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public float getUserRating() {
        try {
            PreparedStatement s = c.prepareStatement("SELECT AVG(rating) from USER_REVIEW where game_id=? GROUP BY GAME_ID");
            s.setInt(1, id);

            ResultSet rs = s.executeQuery();
            return rs.getFloat(1);
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public List<Review> reviews() {
        try {
            List<Review> reviews = new ArrayList<>();
            UserReview ur;
            CriticReview cr;
            User u;

            PreparedStatement s = c.prepareStatement(
                    "SELECT rating, text, real_name, profile_name, credit_card, level, phone, user.id" +
                            "from user_review" +
                            "join user on user.id=user_review.user_id" +
                            "where game_id=?"
            );
            s.setInt(1, id);

            ResultSet rs = s.executeQuery();
            while (rs.next()) {
                ur = new UserReview();
                ur.rating = rs.getInt("id");
                ur.text = rs.getString("text");
                ur.game = this;

                u = new User();
                ur.user = u;
                u.realName = rs.getString("real_name");
                u.creditCard = rs.getString("credit_card");
                u.level = rs.getInt("level");
                u.phone = rs.getString("phone");
                u.id = rs.getInt("user.id");

                reviews.add(ur);
            }

            s = c.prepareStatement("SELECT rating, text, company, link from critic_review where game_id=?");
            s.setInt(1, id);

            rs = s.executeQuery();
            while (rs.next()) {
                cr = new CriticReview();

                cr.game = this;
                cr.company = rs.getString("company");
                cr.rating = rs.getFloat("rating");
                cr.link = rs.getString("link");

                reviews.add(cr);
            }

            return reviews;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

@@ -68,47 +68,8 @@
 t;%0A%0A
-/**%0A * Created by nate on 4/10/16.%0A */%0A
 publ
@@ -166,16 +166,209 @@
 price;%0A%0A
+    public Game() %7B%0A%0A    %7D%0A%0A    private Game(ResultSet rs) throws SQLException %7B%0A        id = rs.getInt(%22id%22);%0A        name = rs.getString(%22name%22);%0A        price = rs.getFloat(%22price%22);%0A    %7D%0A%0A
     publ
@@ -445,20 +445,16 @@
 rayList%3C
-Game
 %3E();%0A   
@@ -625,32 +625,33 @@
 executeQuery();%0A
+%0A
             Game
@@ -650,119 +650,297 @@
     
-Game g;%0A            while (rs.next()) %7B  
+while (rs.next()) %7B%0A                 games.add(new Game(rs));%0A            %7D%0A%0A        %7D catch (SQLException e) %7B%0A            e.printStackTrace();%0A        %7D%0A
        
-//
  re
-ad the result set%0A                g = new Game
+turn games;%0A    %7D%0A%0A    public static List%3CGame%3E search(String key) %7B%0A        List%3CGame%3E games = new ArrayList%3C%3E
 ();%0A
+%0A
         
     
@@ -939,69 +939,203 @@
     
-        g.id = rs.getInt(%22id%22);%0A                g.name = rs.g
+key = %22%25%22 + key + %22%25%22;%0A%0A        try %7B%0A            PreparedStatement s = c.prepareStatement(%0A                    %22SELECT id, name, price from game where name like ?%22%0A            );%0A            s.s
 etSt
@@ -1135,33 +1135,34 @@
 s.setString(
-%22name%22
+1, key
 );%0A
+%0A
             
@@ -1165,44 +1165,103 @@
     
-    g.price = rs.getFloat(%22price%22);%0A
+ResultSet rs = s.executeQuery();%0A            while (rs.next()) %7B         // read the result set
 %0A   
@@ -1283,17 +1283,28 @@
 mes.add(
-g
+new Game(rs)
 );%0A     
@@ -1529,24 +1529,16 @@
 rayList%3C
-Category
 %3E();%0A   

Allow searching of games in logic, not in GUI - YET @loomisdf