package com.company;

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

public class Model {
    private Connection connection;

    public boolean connect() {
        String DBURL = "jdbc:mysql://sql.wiki07.nazwa.pl:3306/wiki07";
        String DBUSER = "wiki07";
        String DBPASS = "Porygon2!";
        String DBDRIVER = "com.mysql.jdbc.Driver";
        try {
            Class.forName(DBDRIVER).newInstance();
            connection = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
            e.printStackTrace();
        }
        return connection != null;
    }

    public void disconnect() {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int get_user(String username, String password) {
        try {
            String query = "SELECT user_id FROM account WHERE login = \""
                    + username + "\" AND password = \"" + password + "\" LIMIT 0, 1";

            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(query);

            int user_id;
            if(!result.next())
                user_id = 0;
            else
                user_id = result.getInt("user_id");

            statement.close();
            return user_id;
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public List<String[]> get_finished_visits(int user_id) {
        List<String[]> list = new ArrayList<String[]>();

        try {
            String query = "SELECT visit.start_date, visit.end_date, visit.description, personal_data.first_name, "
                    + "personal_data.last_name FROM visit JOIN client ON visit.client_id JOIN account ON client.user_id "
                    + "JOIN employee ON visit.employee_id JOIN personal_data ON employee.personal_id "
                    + "WHERE client.user_id = " + user_id + " AND visit.status = \"zakończona\"";

            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(query);

            while(result.next()) {
                String[] str = new String[5];
                str[0] = result.getString("start_date");
                str[1] = result.getString("end_date");
                str[2] = result.getString("description");
                str[3] = result.getString("first_name");
                str[4] = result.getString("last_name");
                list.add(str);
            }

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public List<String[]> get_active_visits(int user_id) {
        List<String[]> list = new ArrayList<String[]>();

        try {
            String query = "SELECT visit.start_date, visit.end_date, visit.description, personal_data.first_name, "
                    + "personal_data.last_name FROM visit JOIN client ON visit.client_id JOIN account ON client.user_id "
                    + "JOIN employee ON visit.employee_id JOIN personal_data ON employee.personal_id "
                    + "WHERE client.user_id = " + user_id + " AND visit.status = \"aktywna\"";

            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(query);

            while(result.next()) {
                String[] str = new String[5];
                str[0] = result.getString("start_date");
                str[1] = result.getString("end_date");
                str[2] = result.getString("description");
                str[3] = result.getString("first_name");
                str[4] = result.getString("last_name");
                list.add(str);
            }

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}