package vmm.train;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;

public class Main {
    public static void main(String[] args) {

        String jdbcURL = "jdbc:h2:~/test";
        String dbUser = "sa";
        String dbPassword = "";

        String excelFilePath = "file.xlsx";
        String createTablesSQL = """
                 DROP TABLE IF EXISTS Движение_товаров;
                 DROP TABLE IF EXISTS Товар;
                 DROP TABLE IF EXISTS Магазин;

                 CREATE TABLE Магазин (
                     ID VARCHAR(255) PRIMARY KEY,
                     Район VARCHAR(255),
                     Адрес VARCHAR(255)
                 );
                                \s
                 CREATE TABLE Товар (
                     Артикул INT PRIMARY KEY,
                     Отдел VARCHAR(255),
                     Наименование VARCHAR(255),
                     Единица_измерения VARCHAR(255),
                     Количество_в_упаковке INT,
                     Производитель VARCHAR(255)
                 );
                                \s
                 CREATE TABLE Движение_товаров (
                     ID_операции INT PRIMARY KEY,
                     Дата DATE,
                     ID_магазина VARCHAR(255),
                     Артикул INT,
                     Количество_упаковок INT,
                     Тип_операции VARCHAR(255),
                     Цена INT,
                     FOREIGN KEY (ID_магазина) REFERENCES Магазин(ID),
                     FOREIGN KEY (Артикул) REFERENCES Товар(Артикул)
                 );
                \s""";

        try {
            Connection connection = DriverManager.getConnection(jdbcURL, dbUser, dbPassword);
            Statement statement = connection.createStatement();
            statement.execute(createTablesSQL);

            try (FileInputStream fis = new FileInputStream(excelFilePath);
                 Workbook workbook = new XSSFWorkbook(fis) {
                 })
            {
                Sheet goods = workbook.getSheetAt(1);
                Sheet shops = workbook.getSheetAt(2);
                Sheet goodsMovements = workbook.getSheetAt(0);

                String insertSQL = "INSERT INTO Товар " +
                        "(Артикул, Отдел, Наименование, Единица_измерения, Количество_в_упаковке, Производитель) " +
                        "VALUES (?, ?, ?, ?, ?, ?)";
                PreparedStatement preparedStatementGoods = connection.prepareStatement(insertSQL);

                String insertSQL1 = "INSERT INTO Магазин " +
                        "(ID, Район, Адрес) " +
                        "VALUES (?, ?, ?)";
                PreparedStatement preparedStatementShops = connection.prepareStatement(insertSQL1);

                String insertSQL2 = "INSERT INTO Движение_товаров " +
                        "(ID_операции, Дата, ID_магазина, Артикул, Количество_упаковок, Тип_операции, Цена) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)";
                PreparedStatement preparedStatementMovements = connection.prepareStatement(insertSQL2);

                for (int i = 1; i <= goods.getLastRowNum(); i++) {
                    Row row = goods.getRow(i);
                    if (row != null) {
                        int артикул = (int) row.getCell(0).getNumericCellValue();
                        String отдел = row.getCell(1).getStringCellValue();
                        String наименование = row.getCell(2).getStringCellValue();
                        String единицаИзмерения = row.getCell(3).getStringCellValue();
                        int количествоВУпаковке = (int) row.getCell(4).getNumericCellValue();
                        String производитель = row.getCell(5).getStringCellValue();
                        preparedStatementGoods.setInt(1, артикул);
                        preparedStatementGoods.setString(2, отдел);
                        preparedStatementGoods.setString(3, наименование);
                        preparedStatementGoods.setString(4, единицаИзмерения);
                        preparedStatementGoods.setInt(5, количествоВУпаковке);
                        preparedStatementGoods.setString(6, производитель);
                        preparedStatementGoods.executeUpdate();
                    }
                }

                for (int i = 1; i <= shops.getLastRowNum(); i++) {
                    Row row = shops.getRow(i);
                    if (row != null) {
                        String id = row.getCell(0).getStringCellValue();
                        String район = row.getCell(1).getStringCellValue();
                        String адрес = row.getCell(2).getStringCellValue();
                        preparedStatementShops.setString(1, id);
                        preparedStatementShops.setString(2, район);
                        preparedStatementShops.setString(3, адрес);
                        preparedStatementShops.executeUpdate();
                    }
                }

                for (int i = 1; i <= goodsMovements.getLastRowNum(); i++) {
                    Row row = goodsMovements.getRow(i);
                    if (row != null) {
                        int id_операции = (int) row.getCell(0).getNumericCellValue();
                        java.util.Date utilDate = row.getCell(1).getDateCellValue();
                        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                        String id_магазина = row.getCell(2).getStringCellValue();
                        int артикул = (int) row.getCell(3).getNumericCellValue();
                        int количество = (int) row.getCell(4).getNumericCellValue();
                        String тип_операции = row.getCell(5).getStringCellValue();
                        int цена = (int) row.getCell(6).getNumericCellValue();
                        preparedStatementMovements.setInt(1, id_операции);
                        preparedStatementMovements.setDate(2, sqlDate);
                        preparedStatementMovements.setString(3, id_магазина);
                        preparedStatementMovements.setInt(4, артикул);
                        preparedStatementMovements.setInt(5, количество);
                        preparedStatementMovements.setString(6, тип_операции);
                        preparedStatementMovements.setInt(7, цена);
                        preparedStatementMovements.executeUpdate();
                    }
                }

                statement.close();
                connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e);
        }

        String query = "SELECT SUM(Количество_упаковок * Цена) AS общая_стоимость " +
                "FROM Магазин, Движение_товаров " +
                "WHERE Магазин.район = ? " +
                "AND Движение_товаров.id_магазина IN (?, ?, ?, ?) " +
                "AND Движение_товаров.дата BETWEEN ? AND ?";

        try (Connection conn = DriverManager.getConnection(jdbcURL, dbUser, dbPassword);
             PreparedStatement preparedStatement = conn.prepareStatement(query))
        {
            preparedStatement.setString(1, "Октябрьский");
            preparedStatement.setString(2, "M3");
            preparedStatement.setString(3, "M9");
            preparedStatement.setString(4, "M11");
            preparedStatement.setString(5, "M14");
            preparedStatement.setDate(6, java.sql.Date.valueOf("2021-06-06"));
            preparedStatement.setDate(7, java.sql.Date.valueOf("2023-12-31"));
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                int общаяСтоимость = rs.getInt("общая_стоимость");
                System.out.println("Общая стоимость: " + общаяСтоимость + " рублей");
            }
        } catch (Exception e) {
            e.getMessage();
        }
    }

}