package com.exam.fob;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;


public class Main3 {

    public static final String selectMessagesSQL = "SELECT message FROM messages WHERE user_id = ?";

    public static final String selectOrdersSQL = "SELECT o.id AS 'order id', o.description, o.created, i.name, i.price " +
            " FROM users u" +
            " JOIN orders o ON u.id = o.user_id" +
            " JOIN items_orders io ON o.id = io.order_id" +
            " JOIN items i ON io.item_id = i.id" +
            " WHERE u.id = ?";

    public static void main(String[] args) {

        int id;
        System.out.print("Id użytkownika: ");
        try(Scanner sc = new Scanner(System.in)) {
            id = sc.nextInt();
        }

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.137.237:3306/exam_2?useSSL=false", "test", "test")) {

            System.out.println("Wiadomości użytkownika o ID: " + id);
            PreparedStatement st = conn.prepareStatement(selectMessagesSQL);
            st.setInt(1, id);
            ResultSet rs = st.executeQuery();
            while(rs.next()) {
                System.out.println(rs.getString("message"));
            }

            st = conn.prepareStatement(selectOrdersSQL);
            st.setInt(1, id);
            rs = st.executeQuery();
            while(rs.next()) {
                int orderId  = rs.getInt("order id");
                String desc = rs.getString("description");
                Date date = rs.getDate("created");
                String itemName = rs.getString("name");
                float price = rs.getFloat("price");
                System.out.printf("Id zamówienia; '%d', opis zamówienia: '%s' dnia '%s', przemiot: '%s' w cenie '%.2f' ", orderId, desc, date.toString(), itemName, price);
                System.out.println();
            }

            st.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}