package varausjarjestelma;

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.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Scanner;
import org.springframework.stereotype.Component;

@Component
public class Tekstikayttoliittyma {

    public void kaynnista(Scanner lukija) {
        lisaaTaulut();
        while (true) {
            System.out.println("Komennot: ");
            System.out.println(" x - lopeta");
            System.out.println(" 1 - lisaa huone");
            System.out.println(" 2 - listaa huoneet");
            System.out.println(" 3 - hae huoneita");
            System.out.println(" 4 - lisaa varaus");
            System.out.println(" 5 - listaa varaukset");
            System.out.println(" 6 - tilastoja");
            System.out.println("");

            String komento = lukija.nextLine();
            if (komento.equals("x")) {
                break;
            }

            if (komento.equals("1")) {
                lisaaHuone(lukija);
            } else if (komento.equals("2")) {
                listaaHuoneet();
            } else if (komento.equals("3")) {
                haeHuoneita(lukija);
            } else if (komento.equals("4")) {
                lisaaVaraus(lukija);
            } else if (komento.equals("5")) {
                listaaVaraukset();
            } else if (komento.equals("6")) {
                tilastoja(lukija);
            }
        }
    }

    private static void lisaaHuone(Scanner s) {
        System.out.println("Lisätään huone");
        System.out.println("");

        System.out.println("Minkä tyyppinen huone on?");
        String tyyppi = s.nextLine();
        System.out.println("Mikä huoneen numeroksi asetetaan?");
        int numero = Integer.valueOf(s.nextLine());
        System.out.println("Kuinka monta euroa huone maksaa yöltä?");
        int hinta = Integer.valueOf(s.nextLine());

        lisaaHuoneita(numero, tyyppi, hinta);


    }

    private static void listaaHuoneet() {
        System.out.println("Listataan huoneet");
        System.out.println("");

        // esimerkkitulostus -- tässä oletetaan, että huoneita on 4
        // tulostuksessa tulostetaan huoneen tyyppi, huoneen numero sekä hinta
//        System.out.println("Excelsior, 604, 119 euroa");
//        System.out.println("Excelsior, 605, 119 euroa");
//        System.out.println("Superior, 705, 159 euroa");
//        System.out.println("Commodore, 128, 229 euroa");

        tulostaHuoneet();
    }

    private static void haeHuoneita(Scanner s) {
        System.out.println("Haetaan huoneita");
        System.out.println("");

        System.out.println("Milloin varaus alkaisi (yyyy-MM-dd)?");;
        LocalDateTime alku = LocalDateTime.parse(s.nextLine() + " " + "16:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Milloin varaus loppuisi (yyyy-MM-dd)?");
        LocalDateTime loppu = LocalDateTime.parse(s.nextLine() + " " + "10:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Minkä tyyppinen huone? (tyhjä = ei rajausta)");
        String tyyppi = s.nextLine();
        System.out.println("Minkä hintainen korkeintaan? (tyhjä = ei rajausta)");
        String maksimihinta = s.nextLine();

//        // esimerkkitulostus -- tässä oletetaan, että vapaita huoneita löytyy 2
//        System.out.println("Vapaat huoneet: ");
//        System.out.println("Excelsior, 604, 119 euroa");
//        System.out.println("Excelsior, 605, 119 euroa");
//
//        // vaihtoehtoisesti, mikäli yhtäkään huonetta ei ole vapaana, ohjelma
//        // tulostaa
//        System.out.println("Ei vapaita huoneita.");

        huoneidenHakeminen(alku, loppu, tyyppi, maksimihinta);

    }

    private static void lisaaVaraus(Scanner s) {
        System.out.println("Haetaan huoneita");
        System.out.println("");

        System.out.println("Milloin varaus alkaisi (yyyy-MM-dd)?");;
        LocalDateTime alku = LocalDateTime.parse(s.nextLine() + " " + "16:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Milloin varaus loppuisi (yyyy-MM-dd)?");
        LocalDateTime loppu = LocalDateTime.parse(s.nextLine() + " " + "10:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Minkä tyyppinen huone? (tyhjä = ei rajausta)");
        String tyyppi = s.nextLine();
        System.out.println("Minkä hintainen korkeintaan? (tyhjä = ei rajausta)");
        String maksimihinta = s.nextLine();

        int vapaatHuoneet = huoneidenHakeminen(alku, loppu, tyyppi, maksimihinta);
        //System.out.println("Vapaat huoneet " + vapaatHuoneet);

        // mikäli huoneita ei ole vapaana, ohjelma tulostaa seuraavan viestin
        // ja varauksen lisääminen loppuu
        if (vapaatHuoneet == 0){
            //System.out.println("Ei vapaita huoneita.");
            return;
        }


        // muulloin, ohjelma kertoo vapaiden huoneiden lukumäärän. Tässä
        // oletetaan että vapaita huoneita on 2.
        System.out.println("Huoneita vapaana: 2");
        System.out.println("");

        // tämän jälkeen kysytään varattavien huoneiden lukumäärää
        // luvuksi tulee hyväksyä vain sopiva luku, esimerkissä 3 ei esim
        // kävisi, sillä vapaita huoneita vain 2
        int huoneita = -1;
        while (true) {
            System.out.println("Montako huonetta varataan?");
            huoneita = Integer.valueOf(s.nextLine());
            if (huoneita > 0 && huoneita <= vapaatHuoneet) {
                break;
            }

            System.out.println("Epäkelpo huoneiden lukumäärä.");
        }

        // tämän jälkeen kysytään lisävarusteet
        List<String> lisavarusteet = new ArrayList<>();
        while (true) {
            System.out.println("Syötä lisävaruste, tyhjä lopettaa");
            String lisavaruste = s.nextLine();
            if (lisavaruste.isEmpty()) {
                break;
            }

            lisavarusteet.add(lisavaruste);
        }

        // ja lopuksi varaajan tiedot
        System.out.println("Syötä varaajan nimi:");
        String nimi = s.nextLine();
        System.out.println("Syötä varaajan puhelinnumero:");
        String puhelinnumero = s.nextLine();
        System.out.println("Syötä varaajan sähköpostiosoite:");
        String sahkoposti = s.nextLine();

        // kun kaikki tiedot on kerätty, ohjelma lisää varauksen tietokantaan
        // -- varaukseen tulee lisätä kalleimmat vapaat huoneet!

        lisaaHuonevaraus(alku, loppu, tyyppi, maksimihinta, huoneita, lisavarusteet, nimi, puhelinnumero, sahkoposti);
    }

    private static void listaaVaraukset() {
        System.out.println("Listataan varaukset");
        System.out.println("");

        // alla olevassa esimerkissä oletetaan, että tietokannassa on
        // kolme varausta
        System.out.println("Essi Esimerkki, essi@esimerkki.net, 2019-02-14, 2019-02-15, 1 päivä, 2 lisävarustetta, 1 huone. Huoneet:");
        System.out.println("\tCommodore, 128, 229 euroa");
        System.out.println("\tYhteensä: 229 euroa");
        System.out.println("");
        System.out.println("Anssi Asiakas, anssi@asiakas.net, 2019-02-14, 2019-02-15, 1 päivä, 0 lisävarustetta, 1 huone. Huoneet:");
        System.out.println("\tSuperior, 705, 159 euroa");
        System.out.println("\tYhteensä: 159 euroa");
        System.out.println("");
        System.out.println("Anssi Asiakas, anssi@asiakas.net, 2020-03-18, 2020-03-21, 3 päivää, 6 lisävarustetta, 2 huonetta. Huoneet:");
        System.out.println("\tSuperior, 705, 159 euroa");
        System.out.println("\tCommodore, 128, 229 euroa");
        System.out.println("\tYhteensä: 1164 euroa");

    }

    private static void tilastoja(Scanner lukija) {
        System.out.println("Mitä tilastoja tulostetaan?");
        System.out.println("");

        // tilastoja pyydettäessä käyttäjältä kysytään tilasto
        System.out.println(" 1 - Suosituimmat lisävarusteet");
        System.out.println(" 2 - Parhaat asiakkaat");
        System.out.println(" 3 - Varausprosentti huoneittain");
        System.out.println(" 4 - Varausprosentti huonetyypeittäin");

        System.out.println("Syötä komento: ");
        int komento = Integer.valueOf(lukija.nextLine());

        if (komento == 1) {
            suosituimmatLisavarusteet();
        } else if (komento == 2) {
            parhaatAsiakkaat();
        } else if (komento == 3) {
            varausprosenttiHuoneittain(lukija);
        } else if (komento == 4) {
            varausprosenttiHuonetyypeittain(lukija);
        }
    }

    private static void suosituimmatLisavarusteet() {
        System.out.println("Tulostetaan suosituimmat lisävarusteet");
        System.out.println("");

        // alla oletetaan, että lisävarusteita on vain muutama
        // mikäli tietokannassa niitä on enemmän, tulostetaan 10 suosituinta
        System.out.println("Teekannu, 2 varausta");
        System.out.println("Kahvinkeitin, 2 varausta");
        System.out.println("Silitysrauta, 1 varaus");
    }

    private static void parhaatAsiakkaat() {
        System.out.println("Tulostetaan parhaat asiakkaat");
        System.out.println("");

        // alla oletetaan, että asiakkaita on vain 2
        // mikäli tietokannassa niitä on enemmän, tulostetaan asiakkaita korkeintaan 10
        System.out.println("Anssi Asiakas, anssi@asiakas.net, +358441231234, 1323 euroa");
        System.out.println("Essi Esimerkki, essi@esimerkki.net, +358443214321, 229 euroa");
    }

    private static void varausprosenttiHuoneittain(Scanner lukija) {
        System.out.println("Tulostetaan varausprosentti huoneittain");
        System.out.println("");

        System.out.println("Mistä lähtien tarkastellaan?");
        LocalDateTime alku = LocalDateTime.parse(lukija.nextLine() + "-01 " + "16:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Mihin asti tarkastellaan?");
        LocalDateTime loppu = LocalDateTime.parse(lukija.nextLine() + "-01 " + "10:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));

        // alla esimerkkitulostus
        System.out.println("Tulostetaan varausprosentti huoneittain");
        System.out.println("Excelsior, 604, 119 euroa, 0.0%");
        System.out.println("Excelsior, 605, 119 euroa, 0.0%");
        System.out.println("Superior, 705, 159 euroa, 22.8%");
        System.out.println("Commodore, 128, 229 euroa, 62.8%");
    }

    private static void varausprosenttiHuonetyypeittain(Scanner lukija) {
        System.out.println("Tulostetaan varausprosentti huonetyypeittäin");
        System.out.println("");

        System.out.println("Mistä lähtien tarkastellaan?");
        LocalDateTime alku = LocalDateTime.parse(lukija.nextLine() + "-01 " + "16:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
        System.out.println("Mihin asti tarkastellaan?");
        LocalDateTime loppu = LocalDateTime.parse(lukija.nextLine() + "-01 " + "10:00", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));

        // alla esimerkkitulostus
        System.out.println("Tulostetaan varausprosentti huonetyypeittän");
        System.out.println("Excelsior, 0.0%");
        System.out.println("Superior, 22.8%");
        System.out.println("Commodore, 62.8%");
    }

    // OMIA TAULUJA TÄSTÄ ETEENPÄIN


    public static void lisaaTaulut() {
        try {
        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

        //Hotellihuone
        PreparedStatement statementHuone = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Hotellihuone(numero INTEGER UNIQUE NOT NULL, tyyppi VARCHAR(100) NOT NULL, hinta INTEGER NOT NULL, PRIMARY KEY(numero))");
        statementHuone.executeUpdate();

        // Asiakas
        PreparedStatement statementAsiakas = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Asiakas(id INTEGER AUTO_INCREMENT UNIQUE NOT NULL, nimi VARCHAR(100) NOT NULL, puhelinnumero VARCHAR(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY(id))");
        statementAsiakas.executeUpdate();

        //Varaus
        PreparedStatement statementVaraus = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Varaus(id INTEGER AUTO_INCREMENT UNIQUE NOT NULL, alkupvm DATE NOT NULL, loppupvm DATE NOT NULL, asiakas_id INTEGER, FOREIGN KEY (asiakas_id) REFERENCES Asiakas(id), PRIMARY KEY(id))");
        statementVaraus.executeUpdate();

        //Lisavaruste
        PreparedStatement statementLisavaruste = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Lisavaruste(id INTEGER AUTO_INCREMENT UNIQUE NOT NULL, nimi VARCHAR(100) NOT NULL)");
        statementLisavaruste.executeUpdate();

        // LIITOSTAULUT

        //VarausHotellihuone
        PreparedStatement statementVarausHotellihuone = connection.prepareStatement("CREATE TABLE IF NOT EXISTS VarausHotellihuone(varaus_id INTEGER NOT NULL, huonenumero_id INTEGER NOT NULL)");
        statementVarausHotellihuone.executeUpdate();

        // Lisätään tauluun FK:t
        PreparedStatement statementVarausHotellihuone_LisaaVarausFK= connection.prepareStatement("ALTER TABLE VarausHotellihuone ADD FOREIGN KEY (varaus_id) REFERENCES Varaus(id)");
        statementVarausHotellihuone_LisaaVarausFK.executeUpdate();

        // Lisätään tauluun FK:t
        PreparedStatement statementVarausHotellihuone_LisaaHuoneFK = connection.prepareStatement("ALTER TABLE VarausHotellihuone ADD FOREIGN KEY (huonenumero_id) REFERENCES Hotellihuone(numero)");
        statementVarausHotellihuone_LisaaHuoneFK.executeUpdate();

        //VarausLisavaruste
        PreparedStatement VarausLisavaruste = connection.prepareStatement("CREATE TABLE IF NOT EXISTS VarausLisavaruste(varaus_id INTEGER NOT NULL, lisavaruste_id INTEGER NOT NULL)");
        VarausLisavaruste.executeUpdate();

        // Lisätään tauluun FK:t
        PreparedStatement VarausLisavaruste_LisaaVarusteFK = connection.prepareStatement("ALTER TABLE VarausLisavaruste ADD FOREIGN KEY (varaus_id) REFERENCES Varaus(id)");
        VarausLisavaruste_LisaaVarusteFK.executeUpdate();

        // Lisätään tauluun FK:t
        PreparedStatement VarausLisavaruste_LisaaLisavarusteFK = connection.prepareStatement("ALTER TABLE VarausLisavaruste ADD FOREIGN KEY (lisavaruste_id) REFERENCES Lisavaruste(id)");
        VarausLisavaruste_LisaaLisavarusteFK.executeUpdate();

        //Suljetaan tietokantataulut
        statementAsiakas.close();
        statementVaraus.close();
        statementHuone.close();
        statementLisavaruste.close();

        //Suljetaan liitostaulut
        statementVarausHotellihuone.close();
        statementVarausHotellihuone_LisaaVarausFK.close();
        statementVarausHotellihuone_LisaaHuoneFK.close();
        VarausLisavaruste.close();
        VarausLisavaruste_LisaaVarusteFK.close();
        VarausLisavaruste_LisaaLisavarusteFK.close();

        //Suljetaan yhteys
        connection.close();

        }

    catch (SQLException e) {}
    }

    public static void lisaaHuoneita(int numero, String tyyppi, int hinta) {
        try {

        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

        PreparedStatement statement = connection.prepareStatement("INSERT INTO Hotellihuone (numero, tyyppi, hinta) VALUES (?, ?, ?)");

        statement.setInt(1, numero);
        statement.setString(2,tyyppi);
        statement.setInt(3, hinta);

        statement.executeUpdate();

        statement.close();
        connection.close();
        }

    catch (SQLException e) {}
    }

    public static void tulostaHuoneet() {

    try {
    Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

    //Luetaan kaikki sarakkeen Hotellihuone-taulusta ja printataan ne
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM Hotellihuone;");
    ResultSet result = statement.executeQuery();

    while (result.next()) {
        Integer huoneNumero = result.getInt("numero");
        String huoneTyyppi = result.getString("tyyppi");
        Integer huoneHinta = result.getInt("hinta");

        System.out.println(huoneNumero + ", " + huoneTyyppi + ", " + huoneHinta + " euroa");
    }

    connection.close();
    } catch (SQLException e) {}
    }


    public static int huoneidenHakeminen(LocalDateTime alkupvm, LocalDateTime loppupvm, String tyyppi, String max_hinta) {
       //Muutetaan päivät oikeaan muotoon (Date)

        LocalDate alku = alkupvm.toLocalDate();
        LocalDate loppu = loppupvm.toLocalDate();
        Date alku_paivays = Date.valueOf(alku);
        Date loppu_paivays = Date.valueOf(loppu);


        // Luodaan String-tyyppinen lista, johon listataan vapaat huoneet
        List<String> vapaatHuoneet = new ArrayList<>();

        tyyppi = tyyppi.toLowerCase(); // Saadaan kaikki huonetyypit samaan muotoon kun printataan

        try {
        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

        // tyyppi ja max_hinta on tyhjiä
        if (tyyppi.length() == 0 && max_hinta.length() == 0) {
            //PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone LEFT JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero LEFT JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE Varaus.loppupvm >= alkupvm OR Varaus.loppupvm IS NULL AND Varaus.alkupvm >= loppupvm OR Varaus.alkupvm IS NULL ORDER BY Hotellihuone.hinta DESC;");

            PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone WHERE huonenumero_id NOT IN(SELECT DISCTINCT numero FROM Hotellihuone JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE Varaus.loppupvm >= ? OR Varaus.loppupvm IS NULL AND Varaus.alkupvm >= ? OR Varaus.alkupvm IS NULL OR Varaus.loppupvm IS NULL ORDER BY Hotellihuone.hinta DESC;");

            statement.setDate(1, alku_paivays);
            statement.setDate(2,loppu_paivays);

            ResultSet result = statement.executeQuery();

            while (result.next()) {
                Integer huoneNumero = result.getInt("numero");
                String huoneTyyppi = result.getString("tyyppi");
                Integer huoneHinta = result.getInt("hinta");

                vapaatHuoneet.add(huoneNumero + ", " + huoneTyyppi + ", " + huoneHinta + "€");

                }
        // tyyppi on tyhjä, mutta max_hinta ei ole tyhjä
        } else if (tyyppi.length() == 0 && max_hinta.length() != 0){
            //PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone LEFT JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero LEFT JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE Varaus.loppupvm >= alkupvm OR Varaus.loppupvm IS NULL AND Varaus.alkupvm >= loppupvm OR Varaus.alkupvm IS NULL AND Hotellihuone.hinta <= ? ORDER BY Hotellihuone.hinta DESC;");
            PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone WHERE hinta <= ? AND numero NOT IN ( SELECT DISTINCT numero FROM Hotellihuone JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE (Varaus.loppupvm > ?) AND (Varaus.alkupvm < ?) OR Varaus.alkupvm IS NULL OR Varaus.loppupvm IS NULL);");
            statement.setString(1, max_hinta);
            statement.setDate(2, alku_paivays);
            statement.setDate(3,loppu_paivays);

            ResultSet result = statement.executeQuery();


            while (result.next()) {
                Integer huoneNumero = result.getInt("numero");
                String huoneTyyppi = result.getString("tyyppi");
                Integer huoneHinta = result.getInt("hinta");

                vapaatHuoneet.add(huoneNumero + ", " + huoneTyyppi + ", " + huoneHinta + " euroa");
                }

        // max_hinta on tyhjä, mutta tyyppi ei ole tyhjä
        } else if (tyyppi.length() != 0 && max_hinta.length() == 0){
            //PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone LEFT JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero LEFT JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE Varaus.loppupvm >= alkupvm OR Varaus.loppupvm IS NULL AND Varaus.alkupvm >= loppupvm OR Varaus.alkupvm IS NULL AND Hotellihuone.tyyppi = ? ORDER BY Hotellihuone.hinta DESC;");
            PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Huone WHERE tyyppi = ? AND numero NOT IN (SELECT DISTINCT numero FROM Hotellihuone JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE (Varaus.loppupvm > ?) AND (Varaus.alkupvm < ?) OR Varaus.alkupvm IS NULL OR Varaus.loppupvm IS NULL);");
            statement.setString(1, tyyppi);
            statement.setDate(2, alku_paivays);
            statement.setDate(3,loppu_paivays);

            ResultSet result = statement.executeQuery();


            while (result.next()) {
                Integer huoneNumero = result.getInt("numero");
                String huoneTyyppi = result.getString("tyyppi");
                Integer huoneHinta = result.getInt("hinta");

                vapaatHuoneet.add(huoneNumero + ", " + huoneTyyppi + ", " + huoneHinta + " euroa");

                }

        // kaikki parametrit käytössä
        } else {
            //PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone LEFT JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero LEFT JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE Varaus.loppupvm >= alkupvm OR Varaus.loppupvm IS NULL AND Varaus.alkupvm >= loppupvm OR Varaus.alkupvm IS NULL AND Hotellihuone.tyyppi = ? AND Hotellihuone.hinta <= ? ORDER BY Hotellihuone.hinta DESC;");
            PreparedStatement statement = connection.prepareStatement("SELECT numero, tyyppi, hinta FROM Hotellihuone WHERE tyyppi = ? AND hinta <= ? AND numero NOT IN (SELECT DISTINCT numero FROM Hotellihuone JOIN VarausHotellihuone ON VarausHotellihuone.huonenumero_id = Hotellihuone.numero JOIN Varaus ON Varaus.id = VarausHotellihuone.varaus_id WHERE (Varaus.loppupvm > ?) AND (Varaus.alkupvm < ?) OR Varaus.alkupvm IS NULL OR Varaus.loppupvm IS NULL);");
            statement.setString(1, tyyppi);
            statement.setString(2, max_hinta);
            statement.setDate(3, alku_paivays);
            statement.setDate(4,loppu_paivays);

            ResultSet result = statement.executeQuery();


            while (result.next()) {
                Integer huoneNumero = result.getInt("numero");
                String huoneTyyppi = result.getString("tyyppi");
                Integer huoneHinta = result.getInt("hinta");

                vapaatHuoneet.add(huoneNumero + ", " + huoneTyyppi + ", " + huoneHinta + " euroa");;

                }

        }

        // Tulostetaan listan sisältö
        int vapaidenHuoneidenMaara = 0;
        if(vapaatHuoneet.isEmpty()) {
             System.out.println("Ei vapaita huoneita. ");
        } else {
            System.out.println("Vapaat huoneet: ");
            for (String huone : vapaatHuoneet) {
                System.out.println(huone);
                vapaidenHuoneidenMaara++;
            }
        }
            connection.close();
            return vapaidenHuoneidenMaara;  // Palautetaan kokonaislukumuuttuja vapaatHuoneet, jota voidaan käyttää varauksessa vapaiden huoneiden lukumäärän selvittämiseen
        } catch (SQLException e) {}
        return 0;
    }


    public static void lisaaHuonevaraus(LocalDateTime alkupvm, LocalDateTime loppupvm, String tyyppi, String max_hinta, int huoneita, List <String> lisavarusteet, String nimi, String puhelinumero, String sahkoposti) {

        lisaaAsiakas_aliohjelma(nimi, puhelinumero, sahkoposti);
        int VarausPK = lisaaVaraus_aliohjelma(alkupvm,loppupvm);
        lisaaLisavaruste_aliohjelma(lisavarusteet);




    }

    public static void lisaaAsiakas_aliohjelma(String nimi, String puhelinumero, String sahkoposti) {
        try {

        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

        PreparedStatement statementAsiakas = connection.prepareStatement("INSERT INTO Asiakas (nimi, puhelinnumero, email) VALUES (?, ?, ?)");

        statementAsiakas.setString(1, nimi);
        statementAsiakas.setString(2,puhelinumero);
        statementAsiakas.setString(3,sahkoposti);

        statementAsiakas.executeUpdate();
        statementAsiakas.close();

        connection.close();
        }

    catch (SQLException e) {}
    }

    public static int lisaaVaraus_aliohjelma(LocalDateTime alkupvm, LocalDateTime loppupvm) {
        try {



        LocalDate alku = alkupvm.toLocalDate();
        LocalDate loppu = loppupvm.toLocalDate();
        Date alku_paivays = Date.valueOf(alku);
        Date loppu_paivays = Date.valueOf(loppu);

        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");
        PreparedStatement statementVaraus = connection.prepareStatement("INSERT INTO Varaus (alkupvm, loppupvm) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);

        statementVaraus.setDate(1, alku_paivays);
        statementVaraus.setDate(2,loppu_paivays);

        statementVaraus.executeUpdate();

        int id = -1;
        ResultSet rs = statementVaraus.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getInt(1);
        }

        statementVaraus.close();
        connection.close();

        }
    catch (SQLException e) {}
    }

    public static void lisaaLisavaruste_aliohjelma(List <String> lisavarusteet) {
        try {

        Connection connection = DriverManager.getConnection("jdbc:h2:./hotelliketju", "sa", "");

        PreparedStatement statementLisavaruste = connection.prepareStatement("-- prepataan muuttuja loopin ulkopuolella");

        for (String lisavaruste : lisavarusteet) {
            statementLisavaruste = connection.prepareStatement("INSERT INTO Lisavaruste (nimi) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
            statementLisavaruste.setString(1, lisavaruste);
            statementLisavaruste.executeUpdate();
        }

        statementLisavaruste.close();

        connection.close();

        }
    catch (SQLException e) {}
    }


}