package kursovaya.model;

import kursovaya.Main;

import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;

public class DBWorker {
    private static final String URL = "jdbc:mysql://localhost:3306/shop?useSSL=false"; //адрес БД
    private static final String NAME = "root"; //логин БД
    private static final String PASSWORD = "root"; //пароль БД

    private Connection connection; //соединение с БД
    private Statement statement; //запрос

    private Main mainApp; //экземпляр главного класса

    /*
    * Конструтор
    * Создание соединения и запроса
    */
    public DBWorker() {
        try {
            connection = DriverManager.getConnection(URL, NAME, PASSWORD);
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    * Геттер для соединения
    */
    public Connection getConnection() {
        return connection;
    }

    public ArrayList<Category> readCategories() {

        ArrayList<Category> categories = new ArrayList<>();

        String queryForClient = "select * from category";

        try {
            Statement statement = connection.createStatement();
            ResultSet resultSetForCategories = statement.executeQuery(queryForClient);

            while(resultSetForCategories.next()) {
                Category category = new Category();

                category.setId(resultSetForCategories.getInt(1));
                category.setCategory(resultSetForCategories.getString(2));

                categories.add(category);
            }

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

        return categories;
    }

    public void deleteClient(Client c) {
        String delete = "delete from clients where id="+c.getId();

        try(Statement statement = connection.createStatement()) {
            statement.execute(delete);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteProduct(Product p) {
        String delete = "delete from products where id="+p.getId();

        try(Statement statement = connection.createStatement()) {
            statement.execute(delete);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /*
    * Чтение клиентов из БД
    */
    public ArrayList<Client> readClients() {

        ArrayList<Client> clients = new ArrayList<>();

        String queryForClient = "select * from clients";

        try {
            Statement statement = connection.createStatement();
            ResultSet resultSetForClients = statement.executeQuery(queryForClient);

            while(resultSetForClients.next()) {
                Client client = new Client();

                client.setId(resultSetForClients.getInt(1));
                client.setName(resultSetForClients.getString(2));
                client.setBank(resultSetForClients.getInt(3));
                client.setLogin(resultSetForClients.getString(4));
                client.setPassword(resultSetForClients.getString(5));

                clients.add(client);
            }

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

        return clients;
    }

    /*
    * Чтение продуктов из БД
    */
    public ArrayList<Product> readProducts() {
        ArrayList<Product> products = new ArrayList<>();

        String queryForProduct = "select * from products";

        try {
            ResultSet resultSetForProducts = statement.executeQuery(queryForProduct);

            while(resultSetForProducts.next()) {
                Product product = new Product();

                product.setId(resultSetForProducts.getInt(1));
                product.setName(resultSetForProducts.getString(2));
                product.setPrice(resultSetForProducts.getInt(3));
                product.setQuantity(resultSetForProducts.getInt(4));
                product.setCategory(resultSetForProducts.getInt(5));

                products.add(product);
            }

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

        return products;
    }

    /*
    * Запись клиентов в БД
    */

    public void writeClients() {
        for(Client c : mainApp.getObsClients()) {
            if(c.getId() == 0) {
                String insert = "insert into clients(name, bank, login, pass) values(?, ?, ?, ?)";
                try(PreparedStatement statement = connection.prepareStatement(insert)) {
                    statement.setString(1, c.getName());
                    statement.setInt(2, c.getBank());
                    statement.setString(3, c.getLogin());
                    statement.setString(4, c.getPassword());

                    statement.execute();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                String update = "update clients set name='"+c.getName()+"', bank="+c.getBank()+
                        ", login='"+c.getLogin()+"', pass='"+c.getPassword()+"' where id="+c.getId();
                try(Statement statement = connection.createStatement()) {
                    statement.execute(update);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /*
    * Запись продуктов в БД
    */

    public void writeProducts() {
        for(Product p : mainApp.getObsProducts()) {
            if(p.getId() == 0) {
                String insert = "insert into products(name, price, quantity, category) values(?, ?, ?, ?)";
                try(PreparedStatement statement = connection.prepareStatement(insert)) {
                    statement.setString(1, p.getName());
                    statement.setInt(2, p.getPrice());
                    statement.setInt(3, p.getQuantity());
                    statement.setInt(4, p.getCategory());

                    statement.execute();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                String update = "update products set name='"+p.getName()+"', price="+p.getPrice()
                        +", quantity="+p.getQuantity()+",category="+p.getCategory()+" where id="+p.getId();
                try(Statement statement = connection.createStatement()) {
                    statement.execute(update);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public ArrayList<Report> readReports() {
        ArrayList<Report> reports = new ArrayList<>();

        String queryForProduct = "select * from reports";

        try {
            ResultSet resultSetForProducts = statement.executeQuery(queryForProduct);

            while(resultSetForProducts.next()) {
                Report report = new Report();

                report.setId(resultSetForProducts.getInt(1));
                report.setReport(resultSetForProducts.getString(2));
                report.setDate(resultSetForProducts.getDate(3).toLocalDate());

                reports.add(report);
            }

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

        return reports;
    }

    public void writeReports() {
        for(Report r : mainApp.getObsReports()) {
            if(r.getId() == 0) {
                String insert = "insert into clients(report, date) values(?, ?)";
                try(PreparedStatement statement = connection.prepareStatement(insert)) {
                    statement.setString(1, r.getReport());
                    statement.setDate(2, Date.valueOf(r.getDate()));

                    statement.execute();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                String update = "update clients set report='"+r.getReport()+"', date='"+r.getDate()+"' where id="+r.getId();
                try(Statement statement = connection.createStatement()) {
                    statement.execute(update);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public ArrayList<Report> readReportsWithDate(LocalDate date1, LocalDate date2) {
        ArrayList<Report> reports = new ArrayList<>();

        String queryForProduct = "select * from reports where date < " + Date.valueOf(date1) + " and date > " + Date.valueOf(date2);

        try {
            ResultSet resultSetForProducts = statement.executeQuery(queryForProduct);

            while(resultSetForProducts.next()) {
                Report report = new Report();

                report.setId(resultSetForProducts.getInt(1));
                report.setReport(resultSetForProducts.getString(2));
                report.setDate(resultSetForProducts.getDate(3).toLocalDate());

                reports.add(report);
            }

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

        return reports;
    }

    public void writeReport(Report r) {
        if(r.getId() == 0) {
            String insert = "insert into clients(report, date) values(?, ?)";
            try(PreparedStatement statement = connection.prepareStatement(insert)) {
                statement.setString(1, r.getReport());
                statement.setDate(2, Date.valueOf(r.getDate()));

                statement.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            String update = "update clients set report='"+r.getReport()+"', date='"+r.getDate()+"' where id="+r.getId();
            try(Statement statement = connection.createStatement()) {
                statement.execute(update);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /*
    * Обратная связь для переменной главного класса
    */
    public void setMainApp(Main mainApp) {
        this.mainApp = mainApp;
    }
}