package com.javaoo.store.dao;

import java.sql.*;
import java.util.*;
import com.javaoo.store.*;

public class InventoryDAO {
    private Connection connection;
    private Statement  statement;

    private final String driverClass      = "org.apache.derby.jdbc.EmbeddedDriver";
    private final String databaseURL      = "jdbc:derby:c:\\sample;create=true";
    private final String databaseUser     = "";
    private final String databasePassword = "";

    public InventoryDAO() {
    	try {
    	    Class.forName(driverClass);
    	    connection = DriverManager.getConnection( databaseURL, databaseUser, databasePassword );
    	    statement  = connection.createStatement();

    	} catch( ClassNotFoundException|SQLException e ) {
    	    e.printStackTrace();
    	}
    }

    public void createBookTable() {
        try {
            statement.execute( "DROP TABLE Books" );
            System.out.println( "Dropped Books table" );
        } catch (SQLException e) {
            // If table doesn't exist then don't bother.
        }

    	try {
    	    statement.execute(
                "CREATE TABLE Books ( "             +
                    "Title CHAR(20) NOT NULL, "     +
                    "Price DECIMAL(10,2), "         +
                    "Quantity INT,"                 +
                    "Author CHAR(20),"              +
                    "Publisher CHAR(20),"           +
                    "Category CHAR(20) )"
    	    );
    	    System.out.println( "Created Books table" );
    	} catch (SQLException e) {
    	    e.printStackTrace();
    	}
    }

    public void createCDTable() {
        try {
            statement.execute( "DROP TABLE CDs" );
            System.out.println( "Dropped CDs table" );
        } catch (SQLException e) {
            // If table doesn't exist then don't bother.
        }

    	try {
    	    statement.execute(
    	        "CREATE TABLE CDs ( " 		         +
                    "Title CHAR(20) NOT NULL, "      +
                    "Price DECIMAL(10,2), " 	     +
                    "Quantity INT," 		         +
                    "Artist CHAR(20) NOT NULL,"      +
                    "ReleaseDate DATE )"
    	    );
    	    System.out.println( "Created CDs table" );
    	} catch (SQLException e) {
    	    e.printStackTrace();
    	}
    }

    public void loadBookTable() {
        try {
            statement.execute(
                "INSERT INTO Books ( Title, Price, Quantity, Author, Publisher, Category )" +
                "VALUES ( 'Godzilla On Holiday', 24.95, 3, 'Hiro Grinder', 'Rising Sun Press', 'NON-FICTION' )"
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void loadCDTable() {
        try {
            statement.execute(
                "INSERT INTO CDs ( Title, Price, Quantity, Artist, ReleaseDate )" +
                "VALUES ( 'Robot Ensemble', 14.95, 2, 'Cuclulon', '2001-07-06' )"
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Book> getBooks() {
        List<Book> books = new LinkedList<>();
        try( ResultSet results = statement.executeQuery( "SELECT * FROM Books" ) ) {
            while( results.next() ) {
                String title     = results.getString( 1 );
                double price     = results.getDouble( 2 );
                int    quant     = results.getInt( 3 );
                String author    = results.getString( 4 );
                String publisher = results.getString( 5 );
                String category  = results.getString( 6 );
                books.add( new Book( title, price, quant, author, publisher, category ) );
            }
        } catch( SQLException e ) {
            e.printStackTrace();
        }
        return books;
    }
}