Lab5

import java.awt.event.{ActionEvent, ActionListener}
import javax.swing.{JButton, JFrame, JPanel, SwingUtilities}
import java.awt.Dimension
import javax.swing.{JFrame, JLabel,JTextField, JPanel, SwingUtilities}
import java.sql.{Connection, DriverManager, ResultSet}
import java.awt.Color
import javax.swing.JTextArea
import javax.swing.JScrollPane
object ButtonModule  {
  val frame = new JFrame("My Application")
  frame.setSize(720, 400)
  frame.setLayout(null)
  frame.getContentPane().setBackground(Color.PINK)

  def main(args: Array[String]): Unit = {
       SwingUtilities.invokeLater(() => {

         val label1=new JLabel("Title")
         val label2=new JLabel("Price")
         val label3=new JLabel("Count")
         val label4=new JLabel("Debug")

         label1.setBounds(75,70,100,20)
         label2.setBounds(235,70,70,20)
         label3.setBounds(410,70,100,20)
         label4.setBounds(150, 150, 100, 20)

         label1.setForeground(Color.DARK_GRAY)
         label2.setForeground(Color.ORANGE)
         label3.setForeground(Color.WHITE)
         label4.setForeground(Color.RED)

         val button1 = new JButton("Insert")
         val button2 = new JButton("Select")
         val button3 = new JButton("Get max profit")
         val button4 = new JButton("Get not less")

         val button5 = new JButton("Task #1")
         val button6 = new JButton("Task #2")

         button1.setBackground(Color.GREEN)
         button2.setBackground(Color.RED)
         button3.setBackground(Color.YELLOW)
         button4.setBackground(Color.CYAN)

         button1.setForeground(Color.CYAN)
         button2.setForeground(Color.YELLOW)
         button3.setForeground(Color.RED)
         button4.setForeground(Color.GREEN)

         val textField = new JTextField(200)
         val textField2 = new JTextField(20)
         val textField3 = new JTextField(20)

         val textArea=new JTextArea()
         val scrollPane = new JScrollPane(textArea)

         val debugTextArea = new JTextArea()
         val debugScrollPane = new JScrollPane(debugTextArea)

         debugTextArea.setBackground(Color.LIGHT_GRAY)
         textArea.setBackground(Color.LIGHT_GRAY)

         button1.setBounds(20,30,130,20)
         button2.setBounds(190,30,130,20)
         button3.setBounds(360,30,130,20)
         button4.setBounds(530,30,130,20)

         button5.setBounds(530,250,130,20)
         button6.setBounds(530,300,130,20)

         textField.setBounds(20,100,130,20)
         textField2.setBounds(190,100,130,20)
         textField3.setBounds(360,100,130,20)
         scrollPane.setBounds(500,100,180,120)
         debugScrollPane.setBounds(65, 180, 210, 150)

         frame.add(button1)
         frame.add(button2)
         frame.add(button3)
         frame.add(button4)
         frame.add(button5)
         frame.add(button6)
         frame.add(label1)
         frame.add(textField)
         frame.add(label2)
         frame.add(textField2)
         frame.add(label3)
         frame.add(textField3)
         frame.add(scrollPane)
         frame.add(label4)
         frame.add(debugScrollPane)

      button1.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {
          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

          try {
           val stmt = conn.createStatement()
           val rs = stmt.execute("INSERT INTO products VALUES ('" + textField.getText + "'," + textField2.getText + "," + textField3.getText + ")")

           textField.setText("")
           textField2.setText("")
           textField3.setText("")

           debugTextArea.append("Added record\n")
          }
          finally {
            conn.close()
          }
        }

      })


      button2.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {

          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

          try {
             val stmt = conn.createStatement()
             val prod_name= textField.getText().toString().trim()
             val rs = stmt.executeQuery("SELECT * FROM products WHERE name = '"+prod_name+"'")
             while (rs.next()) {

               val name = rs.getString("name")
               val price = rs.getInt("price")
               val count = rs.getInt("count")

               textField2.setText(""+price)
               textField3.setText(""+count)
               }
          }
          finally {
            conn.close()
          }
        }

      })


      button3.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {

          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

           try {

             val stmt = conn.createStatement()
             val prod_name= textField.getText().toString().trim()
             val rs = stmt.executeQuery("SELECT * FROM products")
             var max_profit = 0
             var max_product = ""
             var max_price = 0
             var max_count = 0
             while (rs.next()) {

               val name = rs.getString("name")
               val price = rs.getInt("price")
               val count = rs.getInt("count")

               val current_profit = price*count
               if (current_profit > max_profit){
                 max_profit = current_profit
                 max_product = name
                 max_price = price
                 max_count = count
               }


             }

             debugTextArea.append(max_product + "\n")
             debugTextArea.append(max_price + "\n")
             debugTextArea.append(max_profit + "\n")

              //val stmt = conn.createStatement()
              //val prod_name= textField.getText().toString().trim()
              //val rs = stmt.executeQuery("SELECT name, MAX(price * `count`) AS max_total_cost " +
              //                      "FROM products " +
              //                      "GROUP BY name " +
              //                      "ORDER BY max_total_cost DESC "
              //                      )


              //if (rs.next()) {
              //  val name = rs.getString("name")
              //  val maxTotalCost = rs.getInt("max_total_cost")

              //  debugTextArea.append(s"Name: $name\n")
              //  debugTextArea.append(s"Max profit: $maxTotalCost")
              //}


             }
             finally {
               conn.close()
             }
        }

      })


      button4.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {

          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

          val LessCount=500
          try {
             val stmt = conn.createStatement()
             val prod_name = textField.getText().toString().trim()
             val query =
              s"""
                 |SELECT name, price, count
                 |FROM products
                 |WHERE price >= $LessCount
                 |""".stripMargin

             val rs = stmt.executeQuery(query)
             textArea.append("Name       Price       Count\n")

             while (rs.next()) {
               val name = rs.getString("name")
               val price = rs.getInt("price")
               val count = rs.getInt("count")

               textArea.append(name + "   "+ price+"     " + count + "\n")

             }
          }
          finally {
            conn.close()
          }
        }

      })

      //Вывести категории товаров, которых меньше определённого количества
      button5.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {

          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

          val minCount = 10
          try {
             val stmt = conn.createStatement()


             val rs = stmt.executeQuery(s"SELECT * FROM products GROUP BY `count` HAVING `count` <= $minCount")

             debugTextArea.setText("")

             while (rs.next()) {
               val name = rs.getString("name")
               val price = rs.getInt("price")
               val count = rs.getInt("count")

               debugTextArea.append(name + "   "+ price+"     " + count + "\n")

             }
          }
          finally {
            conn.close()
          }
        }

      })

      //Вывести товары, которое содержат букву e
      button6.addActionListener(new ActionListener {
        override def actionPerformed(e: ActionEvent): Unit = {

          val url = "jdbc:mysql://localhost:3306/shop"
          val username = "root"
          val password = "adminadmin"

          Class.forName("com.mysql.jdbc.Driver")
          val conn = DriverManager.getConnection(url, username, password)

          try {
             val stmt = conn.createStatement()

             val rs = stmt.executeQuery("SELECT * FROM products WHERE LOCATE('e', name) > 0")

             debugTextArea.setText("")

             while (rs.next()) {
               val name = rs.getString("name")
               val price = rs.getInt("price")
               val count = rs.getInt("count")

               debugTextArea.append(name + "   "+ price+"     " + count + "\n")

             }
          }
          finally {
            conn.close()
          }
        }

      })


      frame.setBackground(Color.BLUE)
      frame.setLocationRelativeTo(null)
      frame.setVisible(true)

      })
  }
}