/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package telkom;

// Import all necessary packages
import java.sql.*;
import java.util.Scanner;

/**
 *
 * @author Kuba Sejdak i Tomasz Figa
 */
public class DataBase
{
    public DataBase()
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch(ClassNotFoundException e )
        {
            System.err.print("ERROR: Could not load database driver -> ");
            System.err.println(e.getMessage());
            System.exit(1);
        }

        in = new Scanner(System.in);
    }

    public void connect()
    {
        String uri = "jdbc:oracle:thin:@//ikar.elka.pw.edu.pl:1521/elka.elka.pw.edu.pl";
        String user = "jsejdak";
        String password = "jsejdak";

        try
        {
            dbConnection = DriverManager.getConnection(uri, user, password);
        }
        catch(SQLException e)
        {
            System.err.print("ERROR: Could not connect to database -> ");
            System.err.println(e.getMessage());
            System.exit(1);
        }
    }

    public void disconnect()
    {
        try
        {
            if(dbConnection != null)
                dbConnection.close();
        }
        catch(SQLException e)
        {
            System.err.print("ERROR: could not disconnect from database -> ");
            System.err.println(e.getMessage());
        }
    }

    public void addClient()
    {
        System.out.println("---- Dodawanie klientów do bazy: ----\n");

        System.out.println("Podaj imię klienta:");
        String firstName = in.next();
        System.out.println("Podaj nazwisko klienta:");
        String lastName = in.next();
        System.out.println("Podaj adres klienta:");
        String address = in.next();
        System.out.println("Podaj region klienta:");
        String area = in.next();

        try
        {
            Statement s = dbConnection.createStatement();

            s.executeQuery("INSERT INTO klienci VALUES (" +
                           "ID_KLIENTA_SEQ.nextval, " +
                           lastName + "," +
                           firstName + "," +
                           address + "," +
                           area + ")");

            s.close();
        }
        catch(SQLException e)
        {
            System.out.println("ERROR: cannot add client. " + e.getMessage());
        }
    }

    public void selectClient()
    {
        System.out.println();
        System.out.println("Podaj imię i nazwisko klienta:");
        String firstName = in.next();
        String lastName = in.next();
        System.out.println();

        try
        {
            Statement s = dbConnection.createStatement();

            ResultSet rset1 = s.executeQuery("SELECT * FROM klienci WHERE " +
                            "imie = '" + firstName + "' AND nazwisko = '" + lastName +"'");

            if(rset1.next())
            {
                System.out.println("---- Dane klienta: ----");
                System.out.println("Id      : " + rset1.getString(1));
                System.out.println("Imię    : " + rset1.getString(3));
                System.out.println("Nazwisko: " + rset1.getString(2));
                System.out.println("Adres   : " + rset1.getString(4));
                System.out.println("Region  : " + rset1.getString(5));
            }
            else
            {
                rset1.close();
                s.close();
                System.out.println("Brak wyników!");
                return;
            }

            ResultSet rset2 = s.executeQuery("SELECT id_umowy, poczatek, koniec " +
                    "FROM klienci NATURAL JOIN umowy WHERE " +
                    "imie = '" + firstName + "' AND nazwisko = '" + lastName +"'");

            if(rset2.next())
            {
                System.out.println("---- Umowy klienta: ----");
                System.out.println("Id      : " + rset2.getString(1));
                System.out.println("Początek: " + rset2.getString(2));
                System.out.println("Koniec  : " + rset2.getString(3));
                System.out.println("-----------------------\n");
            }
            else
            {
                rset2.close();
                s.close();
                System.out.println("Brak wyników!");
                return;
            }

            rset1.close();
            rset2.close();
            s.close();
        }
        catch(SQLException e)
        {
            System.out.println("ERROR: cannot show client. " + e.getMessage());
        }

        modifyClient();
    }

    private void modifyClient()
    {
        loop:
        while(true)
        {
            System.out.println("Co chcesz zrobić?\n");
            System.out.println("1 - edytuj klienta");
            System.out.println("2 - usuń klienta");
            System.out.println("3 - pokaż dane klienta");
            System.out.println("4 - dodaj umowę");
            System.out.println("0 - powrót");

            int choice = in.nextInt();

            switch(choice)
            {
            case 1:
                break;
            case 2:
                break;
            case 0:
                break loop;
            default:
                System.out.println("Niepoprawny wybór!");
                break;
            } /* switch */
        } /* while */
    }

    private Connection dbConnection = null;
    Scanner in;
}