import java.text.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class Methods_version5 {

/************************************************************************/
    public static void loadJDBCDriverClass() {

        //Load JDBC driver class
        try {
            Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException cnfe) {
            System.err.println("\nAn error has occured while loading the "
                    + "JDBC driver:\n");
            cnfe.printStackTrace();
            System.exit(1);
        }
        System.out.println("\nLoading drivers for JDBC...\n");

    }
//***********************************************************************/
    public static Connection connectPostgreSQLDatabase() {

        //Connect to PostgreSQL database
        Connection c = null;

        try {
            c = DriverManager.getConnection(
                    "jdbc:postgresql://localhost/",
                    "java","java");
        } catch (SQLException connection_failed) {
            System.out.println("Unable to establish a connection to the "
                    + "database.\n");
            System.exit(1);
        }

        return c;
    }
//***********************************************************************/
    public static Statement checkStatement() {

        //Check the connection
        Statement s = null;
        try {
            s = connectPostgreSQLDatabase().createStatement();
        } catch (SQLException se) {
            System.out.println("An exception error occured while creating a"
                    + " statement. Probably becuase the connection is lost.\n");
            se.printStackTrace();
            System.exit(1);
        }

        return s;
    }
//***********************************************************************/
    public static ResultSet runSQL(String query) {

        ResultSet resultat = null;

        try {
            resultat = checkStatement().executeQuery(query);
        } catch (SQLException query_failed) {
            System.out.println("Felaktigt formulerad SQL-sats");
            System.exit(1);
        }

        return resultat;
    }
//***********************************************************************/
    public static void addBorrower() {

        boolean addUser=true;
        boolean loop;
        int index;
        int hasCardNrBeenChecked;
        ResultSet rs = null;
        Scanner in = new Scanner(System.in);
        String databaseCardNr="110";
        String inputCardNr;
        String answer;
        String borrowerName;
        String streetAddress;
        String postalAddress;


        // Add a borrower
        while (addUser==true) {

            loop=true;

            System.out.println("\n\n\n\n\n#####################################"
                    + "#############################\n\nAdd a borrower\n\n#####"
                    + "########################################################"
                    + "#####");

            System.out.print("\nInput a card number: ");
            inputCardNr = in.nextLine();


            //Makes sure thar the chosen card number is unique
            while (loop==true) {
                index = 0;
                rs = null;
                hasCardNrBeenChecked = 0;

                try {
                    rs = Methods_version5.checkStatement().executeQuery(
                            "SELECT librarycardno FROM LTAGARE;");
                } catch (SQLException se) {
                    System.out.println("An exception error has occured while "
                            + "executing the query.\n");
                    se.printStackTrace();
                    System.exit(1);
                }

                try {
                    while (rs.next()) {
                        databaseCardNr = rs.getString(1);
                        if (databaseCardNr.equals(inputCardNr)) {
                            System.out.print("\nThe card number " + inputCardNr
                                    + " already exists.\nPlease input another "
                                    + "card number: ");
                            inputCardNr = in.nextLine();
                            loop = true;
                            hasCardNrBeenChecked++;
                        }
                    }
                } catch (SQLException se) {
                    System.out.println("An exception error has occured.\n");
                    se.printStackTrace();
                    System.exit(1);
                }
                if (hasCardNrBeenChecked==0) {
                    loop = false;
                }
            }


            System.out.print("\nInput a name: ");
            borrowerName = in.nextLine();
            while (borrowerName.equals("")) {
                System.out.print("\nInput a name: " );
                borrowerName = in.nextLine();
            }

            System.out.print("\nInput a street address: ");
            streetAddress = in.nextLine();
            while (streetAddress.equals("")) {
                System.out.print("\nInput a street address: " );
                streetAddress = in.nextLine();
            }

            System.out.print("\nInput a postal address: ");
            postalAddress = in.nextLine();
            while (postalAddress.equals("")) {
                System.out.print("\nInput a postal address: " );
                postalAddress = in.nextLine();
            }

            //Prints SQL statement on screen for manual error check
            System.out.println(
            "\nINSERT INTO Ltagare (librarycardno, "
            +                      "name, "
            +                      "streetaddress, "
            +                      "postaladdress) "
            + "VALUES (" + inputCardNr + ", "
            +        "'" + borrowerName + "', "
            +        "'" + streetAddress + "', "
            +        "'" + postalAddress + "');\n");

            //Inserts the new data into the database
            ResultSet resultat = null;
            try {
                resultat = Methods_version5.checkStatement().executeQuery(
                   "INSERT INTO Ltagare (librarycardno, "
                   +                      "name, "
                   +                      "streetaddress, "
                   +                      "postaladdress) "
                   + "VALUES (" + inputCardNr + ", "
                   +        "'" + borrowerName + "', "
                   +        "'" + streetAddress + "', "
                   +        "'" + postalAddress + "');");
            } catch (SQLException query_failed) {
                ;
                //This doesn't work, all feedback seems to be treated as errors
                //System.out.println("Incorrect SQL statement");
                //System.exit(1);
            }

            //Ask the user if he wants to add another borrower
            System.out.print("\nDo you want to add another borrower? (y/n): ");
            answer = in.nextLine();
            while (!answer.equalsIgnoreCase("y") &&
                   !answer.equalsIgnoreCase("n")) {
                System.out.print("\nDo you want to add another borrower? "
                        + "(y/n): ");
                answer = in.nextLine();
            }
            if (answer.equalsIgnoreCase("y")) {
                addUser = true;
            } else {
                addUser = false;
            }
        }

    }

//***********************************************************************/
    //kontrollerar att kontrolltecknet
    // i ISBN-numret är korrekt
    public static boolean isIsbn(String s) {

        String kSiffra, nio; //kSiffra=kontrolltecknet som en String
        int sum, koll;  // koll = kontrolltecknet som en int

        nio = s.replaceAll("-","");

        //nio innehåller nu 10 tecken, 9 siffror + kontrolltecknet.
        sum = 0;
        for (int i = 1;i<=9;i++)
            sum = sum + i*Integer.parseInt(nio.substring(i-1,i));

        koll = sum%11;  // koll = kontrollvärdet som heltal
        if (koll == 10)
            kSiffra = "X";
        else
            kSiffra = String.valueOf(koll);

        // Kontrollera kontrolltecknet och returnera true el false
        return kSiffra.equals(s.substring(12));

    }
}