package ebuy;

import java.sql.*;
import java.io.*;
import ebuy.ItemCats;
import ebuy.Items;
import ebuy.Users;

public class eBuyUniversal
{
    private Connection createConnection()
    {
        Connection connection = null;
        try
        {
            String connectionURL;

            connectionURL = "jdbc:mysql://localhost:3306/ebuy";

            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection(connectionURL, "root", "uniboyz");

        }
        catch(Exception e)
        {
            System.out.println("Unable to connect to database.");
        }
        return connection;

    }

    public String getCatList(int parent, int depth)
    {
        String cats = "";
        try
        {
            Connection connection;
            Statement statement;
            ResultSet resultSet;

            connection = createConnection();

            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT * FROM itemcats WHERE parentCat="+parent);

            while(resultSet.next())
            {
                for(int i=0; i<depth; i++)
                    cats += " - ";

                if(parent == 0)
                    cats += "<b>";

                cats += "<a href='category.jsp?catID=" + resultSet.getInt(1) + "'>" + resultSet.getObject(2) + "</a>";

                if(parent == 0)
                    cats += "</b>";

                cats += "<br />";
                cats += getCatList(resultSet.getInt(1),depth+1);
            }
            connection.close();
        }
        catch(Exception e)
        {
            System.out.println("Unable to connect to database.");
        }

        return cats;
    }

    public ItemCats getItemCat(int categoryID)
    {
        ItemCats currentCat = new ItemCats();

        try
        {
            Connection connection;
            Statement statement;
            ResultSet resultSet;

            connection = createConnection();

            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT * FROM itemcats WHERE catID=" + categoryID);

            while(resultSet.next())
            {
                currentCat.setCatID(resultSet.getInt(1));
                currentCat.setCatName(resultSet.getString(2));
                currentCat.setParentCat(resultSet.getInt(3));
            }
            connection.close();
        }
        catch(Exception e)
        {
            System.out.println("Unable to connect to database.");
        }

        return currentCat;
    }

    public boolean createUser(Users newUser)
    {

        try
        {
            Connection connection;
            connection = createConnection();

            String query = "INSERT INTO users (username, password, firstName, lastName, email, phoneNo, DOB, streetNo, streetName, suburb, city, postcode, country) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";

            PreparedStatement preparedStmt = connection.prepareStatement(query);

            preparedStmt.setString(1,newUser.getUserName());
            preparedStmt.setString(2,newUser.getPassword());
            preparedStmt.setString(3,newUser.getFirstName());
            preparedStmt.setString(4,newUser.getLastName());
            preparedStmt.setString(5,newUser.getEmail());
            preparedStmt.setString(6,newUser.getPhoneNo());
            preparedStmt.setString(7,newUser.getDOB());
            preparedStmt.setString(8,newUser.getStreetNo());
            preparedStmt.setString(9,newUser.getStreetName());
            preparedStmt.setString(10,newUser.getSuburb());
            preparedStmt.setString(11,newUser.getCity());
            preparedStmt.setString(12,newUser.getPostcode());
            preparedStmt.setString(13,newUser.getCountry());

            preparedStmt.executeUpdate();

            if(!connection.isClosed())
            {
                System.out.println("Successfully connected to " + "MySQL server using TCP/IP...<br /><br />");
                return true;
            }

            connection.close();
        }
        catch(SQLException e)
        {
            System.out.println("Unable to connect to database.");
            System.out.println("<br />" + e.getMessage());
            System.out.println("<br />" + e.getErrorCode());
            return false;
        }
        return false;

    }

    public boolean createItem(Items newItem)
    {
        try
        {
            Connection connection;
            connection = createConnection();

            String query = "INSERT INTO items (seller, catID, itemName, itemDescription, startTime, duration, currentBidID, currentPrice, reserve) VALUES(?,?,?,?,?,?,?,?,?)";

            PreparedStatement preparedStmt = connection.prepareStatement(query);

            preparedStmt.setInt(1,newItem.getSellerID());
            preparedStmt.setInt(2,newItem.getCategoryID());
            preparedStmt.setString(3,newItem.getItemName());
            preparedStmt.setString(4,newItem.getItemDescription());
            preparedStmt.setLong(5,newItem.getStartTime());
            preparedStmt.setLong(6,newItem.getDuration());
            preparedStmt.setInt(7,newItem.getCurrentBidID());
            preparedStmt.setDouble(8,newItem.getCurrentPrice());
            preparedStmt.setDouble(9,newItem.getReserve());

            preparedStmt.executeUpdate();

            if(!connection.isClosed())
            {
                System.out.println("Successfully connected to " + "MySQL server using TCP/IP...<br /><br />");
                return true;
            }

            connection.close();
        }
        catch(SQLException e)
        {
            System.out.println("Unable to connect to database.");
            System.out.println("<br />" + e.getMessage());
            System.out.println("<br />" + e.getErrorCode());
            return false;
        }
        return false;
    }
}