package opg1;

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

public class Main {

    static PreparedStatement new_andetProduct;
    static PreparedStatement new_toej;
    static PreparedStatement new_drink;
    static PreparedStatement new_container;

    static PreparedStatement seProduktAndet;
    static PreparedStatement seProduktToej;
    static PreparedStatement seProduktDrink;
    static PreparedStatement seProduktContainer;
    static PreparedStatement sePrisliste;
    static PreparedStatement seKundeListe;
    static ArrayList<PreparedStatement> seArr;
    static ArrayList<String> seArrString;

    static PreparedStatement seSalg;

    static PreparedStatement get_samlet_salg;
    static PreparedStatement get_samlet_salg_drink;
    static PreparedStatement get_samlet_salg_andet;
    static PreparedStatement get_samlet_salg_clothes;

    static Connection minConnection = null;

    static Scanner reader;

    public static void main(String[] args) {

        ///////////////////////////////////////////////////////////////////////// CONNECT
        reader = new Scanner(System.in); // keyboard input

        System.out.println(
                "input connection string. Hvis input er mindre en 10 char lang bruger programmet denne default string: ");
        String connectionString = "jdbc:sqlserver://LAPTOP-4MGF8SQ7\\HAPS;databaseName=master;user=sa;password=password;";
        System.out.println("\n " + connectionString + " \ninput:");

        String input = reader.next();
        if (input.length() >= 10) {
            connectionString = input;
        }

        try {
            minConnection = DriverManager.getConnection(connectionString);
            System.out.println("connection succes!");
        } catch (Exception e) {
            System.out.println("kunne ikke connecte til server - fejl:  " + e.getMessage());
            System.exit(0);
        }

        ///////////////////////////////////////////////////////////////////////// MENU

        init();

        Boolean done = false;
        int inputNum = 0;

        while (!done) {
            System.out.println("Vælg et tal, indtast og tryk enter:\n");
            System.out.println("0: nyt andet produkt");
            System.out.println("1: nyt drink");
            System.out.println("2: nyt container");
            System.out.println("3: nyt tøj\n");
            System.out.println("4: se andetprodukter, tøj, drinks, container, prisliste og customer table");

            System.out.println("5: se salg tabel \n");
            System.out.println("6: se salg ekslusiv pant for dato og/eller produkt katagori\n");

            System.out.println("input:");
            inputNum = reader.nextInt();

            // Skip the newline
            reader.nextLine();

            System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");

            if (inputNum >= 0 & inputNum <= 3) {
                lavProdukt(inputNum);
            } else if (inputNum == 4) {
                seeTables();
            } else if (inputNum == 5) {
                seeSalg();
            } else if (inputNum == 6) {
                seeTotalSalg();
            } else {
                System.out.println("KUNNE IKKE FORSTÅ TAL ");
            }

        }
    }

    public static void init() {

        // TODO put tøj ind for at få fler produktgrupper

        try {
            new_andetProduct = minConnection.prepareStatement("exec nyt_PRODUCT ?,?,?,?");
            new_drink = minConnection.prepareStatement("exec nyt_DRINK ?,?,?,?,?,?");
            new_container = minConnection.prepareStatement("exec nyt_CONTAINER ?,?,?");
            new_toej = minConnection.prepareStatement("exec nyt_CLOTHING ?,?,?,?,?");

            seProduktAndet = minConnection.prepareStatement("exec get_PRODUCT");
            seProduktToej = minConnection.prepareStatement("exec get_CLOTHING");
            seProduktDrink = minConnection.prepareStatement("exec get_DRINK");
            seProduktContainer = minConnection.prepareStatement("exec get_CONTAINER");
            sePrisliste = minConnection.prepareStatement("exec get_PRISLISTE");
            seKundeListe = minConnection.prepareStatement("exec get_KUNDELISTE");

            seSalg = minConnection.prepareStatement("exec get_SALG");

            get_samlet_salg = minConnection.prepareStatement("exec get_samlet_salg ?");
            get_samlet_salg_drink = minConnection.prepareStatement("exec get_samlet_salg_drink ?");
            get_samlet_salg_andet = minConnection.prepareStatement("exec get_samlet_salg_andet ?");
            get_samlet_salg_clothes = minConnection.prepareStatement("exec get_samlet_salg_clothes ?");

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("error i init. Kunne ikke initialisere preparedstatement " + e.toString());
            e.printStackTrace();
        }

        seArr = new ArrayList<PreparedStatement>();
        seArrString = new ArrayList<String>();

        seArr.add(seProduktAndet);
        seArr.add(seProduktToej);
        seArr.add(seProduktDrink);
        seArr.add(seProduktContainer);
        seArr.add(sePrisliste);
        seArr.add(seKundeListe);

        seArrString.add("Andre produkter (Produktkatagori): ");
        seArrString.add("Toej (Produktkatagori):");
        seArrString.add("Drink produkte (Produktkatagori): ");
        seArrString.add("Container for drinks: ");
        seArrString.add("Prisliste: ");
        seArrString.add("kundeliste: ");

        /*
         * delete_andetProduct=; opdater_andetProduct=;
         *
         * seProduktDrink=; seProduktAndet=;
         *
         * seSamletDatoSalg=; seSamletProduktGruppeDatoSalg=;
         */

    }

    /**
     * Prints out tables: Produktandet ProduktDrink Containers Prisliste
     * KundeListe
     */
    public static void seeTables() {

        try {

            for (int i = 0; i < seArr.size(); i++) {
                System.out.println(
                        "\n\n##############################################################################\n\n");
                System.out.println(seArrString.get(i) + "\n");
                PreparedStatement stt = seArr.get(i);
                System.out.println("--------------------------------------------------------------------------------");
                stt.execute();
                ResultSet res = stt.getResultSet();
                ResultSetMetaData rsmd = res.getMetaData();
                String format = "%-35s";

                for (int x = 1; x <= rsmd.getColumnCount(); x++) {
                    System.out.printf(format, rsmd.getColumnName(x));
                }
                System.out.printf("%s", "\n");
                while (res.next()) {
                    for (int x = 1; x <= rsmd.getColumnCount(); x++) {
                        System.out.printf(format, res.getString(x));
                    }
                    System.out.println("");
                }
            }

        } catch (Exception e) {
            System.out.println("fejl " + e.toString());
        }

    }

    public static void lavProdukt(int num) {

        ArrayList<String> info = new ArrayList<String>();
        info.add("nyt andet produkt\n" + "argumenter: barpris double,storepris double,otherpris double,navnstr"
                + "\n eksempel:  53.50,50,0,sparegris");
        info.add("nyt drink\n"
                + "argumenter: barpris double,storepris double,otherpris double,navnstr,percent double, int containerfremmednøgle"
                + "\n eksempel:  40,0,0,alm øl,4.7,1");
        info.add("nyt container\n" + "argumenter: navnstr,double liter, double pant" + "\n eksempel:  ølhorn,0.86,10");
        info.add("nyt toej\n" + "argumenter: barpris double,storepris double,otherpris double,navnstr,størrelsestring"
                + "\n eksempel:  150,150,150,tshirt m.logo,XL");

        System.out.println("input argumenter for det nye produkt og seperer hver argument med et komma uden mellemrum");
        System.out.println(info.get(num));

        String[] a = inputArgumenter();
        System.out.println("numre af argumenter: " + a.length);
        try {
            if (num == 0) { // andet product

                new_andetProduct.setDouble(1, Double.parseDouble(a[0]));
                new_andetProduct.setDouble(2, Double.parseDouble(a[1]));
                new_andetProduct.setDouble(3, Double.parseDouble(a[2]));
                new_andetProduct.setString(4, a[3]);
                new_andetProduct.execute();
            }
            if (num == 1) { // drink
                new_drink.setDouble(1, Double.parseDouble(a[0]));
                new_drink.setDouble(2, Double.parseDouble(a[1]));
                new_drink.setDouble(3, Double.parseDouble(a[2]));
                new_drink.setString(4, a[3]);
                new_drink.setDouble(5, Double.parseDouble(a[4]));
                new_drink.setInt(6, Integer.parseInt(a[5]));
                new_drink.execute();
            }
            if (num == 2) { // container
                new_container.setString(1, a[0]);
                new_container.setDouble(2, Double.parseDouble(a[1]));
                new_container.setDouble(3, Double.parseDouble(a[2]));
                new_container.execute();
            }
            if (num == 3) { // tøj
                new_toej.setDouble(1, Double.parseDouble(a[0]));
                new_toej.setDouble(2, Double.parseDouble(a[1]));
                new_toej.setDouble(3, Double.parseDouble(a[2]));
                new_toej.setString(4, a[3]);
                new_toej.setString(5, a[4]);
                new_toej.execute();
            }
        } catch (NumberFormatException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static String[] inputArgumenter() {
        System.out.println("input:");
        String input = reader.nextLine();
        return input.split(",");
    }

    public static void seeSalg() {
        System.out.println("\n--------------------------------------------------------------------------------");
        System.out.println(
                "\nSALDSDATO ER FORKERT HER!!\n DET MÅ VÆRE EN BUG http://stackoverflow.com/questions/11296606/dates-consistently-two-days-off ");
        try {
            seSalg.execute();
            ResultSet res = seSalg.getResultSet();
            ResultSetMetaData rsmd = res.getMetaData();
            String format = "%-35s";

            for (int x = 1; x <= rsmd.getColumnCount(); x++) {
                System.out.printf(format, rsmd.getColumnName(x));
            }
            System.out.printf("%s", "\n");
            while (res.next()) {

                System.out.printf(format, res.getString(1));
                System.out.printf(format, res.getDate(2)); // SALGSDATO ER
                                                            // FORKERT HER
                System.out.printf(format, res.getString(3)); // DET MÅ VÆRE EN
                                                                // BUG
                                                                // http://stackoverflow.com/questions/11296606/dates-consistently-two-days-off
                System.out.printf(format, res.getString(4));
                System.out.printf(format, res.getString(5));
                System.out.printf(format, res.getString(6));
                System.out.printf(format, res.getString(7));
                System.out.printf(format, res.getString(8));
                System.out.printf(format, res.getString(9));
                System.out.printf(format, res.getString(10));
                System.out.printf(format, res.getString(11));
                System.out.println("");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void seeTotalSalg() {
        System.out.println("\n indtast dato \n eksempel: '2016-12-24'  (uden quotationmarks)");

        System.out.println("input:");
        String input = reader.nextLine();

        try {
            Date d = Date.valueOf(input);
            System.out.println(d.toString());

            get_samlet_salg_drink.setDate(1, d);
            get_samlet_salg_andet.setDate(1, d);
            get_samlet_salg_clothes.setDate(1, d);

            get_samlet_salg.setDate(1, d);
            get_samlet_salg.execute();
            ResultSet res1 = get_samlet_salg.getResultSet();
            res1.next();
            System.out.print("Samlet salg for dato: ");
            System.out.println(res1.getDouble(1));

            get_samlet_salg_drink.execute();
            ResultSet res2 = get_samlet_salg_drink.getResultSet();

            get_samlet_salg_andet.execute();
            ResultSet res3 = get_samlet_salg_andet.getResultSet();

            get_samlet_salg_clothes.execute();
            ResultSet res4 = get_samlet_salg_clothes.getResultSet();

            res2.next();
            res3.next();
            res4.next();
            System.out.print("Samlet salg for dato: ");
            System.out.println(res1.getDouble(1));
            System.out.print("Samlet salg indenfor drink for dato: ");
            System.out.println(res2.getDouble(1));
            System.out.print("Samlet salg indenfor andet for dato: ");
            System.out.println(res3.getDouble(1));
            System.out.print("Samlet salg inden for clothes for dato: ");
            System.out.println(res4.getDouble(1));

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}