package Øving6;

/**
 * SQL & JDBC
 * med unntakshåndtering
 * @author Amund 30.jan.2012
 */

import java.sql.*;

public class Database{
    private Connection con;

    public Database()throws Exception{
        String dbdriver = "org.apache.derby.jdbc.ClientDriver";
        Class.forName(dbdriver);
        String databasenavn = "jdbc:derby://localhost:1527/ov5_vprg;user=vprg;password=vprg";
        this.con = DriverManager.getConnection(databasenavn);
    }

    public void closeConnection(){
        try{
            con.close();
            System.out.println("Connection Closed."); //for test purposes
        }catch(SQLException sqle){
            Opprydder.skrivMelding(sqle, "closeConnection()");
        }
    }

    public boolean regNewBook(Bok newBook){
        /*
         * Registers 1 copy of a new book, if said book does not excist in db
         */
        try{
            boolean isReg = false;
            String sqlInput = "insert into boktittel(isbn, forfatter, tittel) values('"+newBook.getIsbn()+"', '"+newBook.getForfatter()+"', '"+newBook.getTittel()+"')";
            Statement state = con.createStatement();
            if(state.executeUpdate(sqlInput)!=0){
                String sqlInput2 = "insert into eksemplar(isbn, eks_nr) values('"+newBook.getIsbn()+"', 1)";
                state.executeUpdate(sqlInput2);
                isReg = true;
            }
            state.close();
            return isReg;
        }catch(SQLException sqle){
            Opprydder.skrivMelding(sqle, "regNewBook()");
            return false;
        }
    }

    public int regNewCopy(String isbn){
        int nyttNr = 1; //in case of no copies
        boolean regOK=false;
        int tries = 0;
        do{
            ResultSet res = null;
            Statement state = null;
            try{
                String sqlInput = "select max(eks_nr) as maks from eksemplar where isbn = '"+isbn+"'";
                state = con.createStatement();
                res = state.executeQuery(sqlInput);

                res.next();
                nyttNr = res.getInt("maks")+1;

                sqlInput = "insert into eksemplar(isbn, eks_nr) values('"+isbn+"', "+nyttNr+")";
                if(state.executeUpdate(sqlInput)!=0){
                    regOK=true;
                }
            }catch(SQLException sqle){
                if(tries < 4){
                    tries++;
                }else{
                    Opprydder.skrivMelding(sqle, "regNewCopy()");
                    return 0;
                }
            }finally{
                Opprydder.lukkResSet(res);
                Opprydder.lukkSetning(state);
            }
        }while(!regOK);
        return nyttNr;
    }

    public boolean lendCopy(String isbn, String name, int copyNum){
        boolean isOk=false;
        Statement stm = null;
        try{
            String sqlInput = "update eksemplar set laant_av = '"+name+"' where isbn = '"+isbn+"' and eks_nr = "+copyNum;
            stm = con.createStatement();

            if(stm.executeUpdate(sqlInput)!=0){
                isOk=true;
            }
            stm.close();
            return isOk;
        }catch(SQLException sqlerr){
            Opprydder.skrivMelding(sqlerr, "lendCopy");
            return isOk;
        }
    }

    public static void main(String[] args)throws Exception{
        Database db = new Database();
        String isbn = "1337";
        String tittel = "Pedofile i dagens samfunn";
        String forfatter = "Rune Aasvestad";
        if(db.regNewBook(new Bok(isbn, tittel, forfatter))) System.out.println("Bok registrert");
        System.out.println("Eksemplar registrert som eksemplarnr: "+db.regNewCopy("1337"));
        if(db.lendCopy("1337","Rune Aasvestad",1)) System.out.println("eksemplar nr1 av bok med isbn 1337 utlånt til Rune Aasvestad");
        db.closeConnection();
    }
}