package databaseapp;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBUtils
{
    // LOCAL OR LIVE

    private static boolean live = !true;
    private static String username;
    private static String password;
    private static String url;


    static
    {
        if(live)
        {
            username = "root";
            password = "password";
            url = "jdbc:mysql://localhost/";
        } else
        {
            username = "root";
            password = "password";
            url = "jdbc:mysql://127.0.0.1/";
        }
    }

    public static Connection getConnection(String DB)
    {
        Connection conn = null;

        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url + DB, username, password);
            System.out.println("CONNECTION: " + (url + DB) + " ESTABLISHED");
        } catch(Exception ex)
        {
            System.out.println(ex.getMessage());
            System.err.println("COULD NOT CONNECT: " + url);
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

        return conn;
    }

    public static String[] getDatabaseNames()
    {
        Connection conn = getConnection("");

        if(conn != null)
        {
            return getDatabaseNames(conn);
        } else
        {
            try
            {
                conn.close();
                System.out.println("CONNECTION TERMINATED");
            } catch(SQLException ex)
            {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
            return null;
        }
    }

    public static String insertInto(String table, Object[] values)
    {
        StringBuffer valueStr = new StringBuffer("(");
        for(Object value : values)
        {
            valueStr.append("'" + value.toString() + "',");
        }
        valueStr.deleteCharAt(valueStr.length() - 1);
        valueStr.append(")");

        return "INSERT INTO " + table + " VALUES " + valueStr.toString();
    }

    private static String[] getDatabaseNames(Connection conn)
    {
        List<String> list = new LinkedList<String>();

        try
        {
            DatabaseMetaData dbmd = conn.getMetaData();

            ResultSet ctlgs = dbmd.getCatalogs();

            while(ctlgs.next())
            {
                list.add(ctlgs.getString(1));
            }
        } catch(SQLException ex)
        {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }

        String[] names = new String[list.size()];
        for(int lp = 0; lp < names.length; lp++)
        {
            names[lp] = list.get(lp);
        }

        return names;
    }

    static String getQueryTable(Connection conn, String query)
    {
        List[] list = null;

        try
        {
            Statement s = conn.createStatement();
            s.executeQuery(query);
            ResultSet rs = s.getResultSet();
            int count = 0;

            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            list = new LinkedList[colCount];
            // ADD COLUMN NAMES
            for(int c = 1; c <= colCount; c++)
            {
                list[c - 1] = new LinkedList();
                //System.out.println("colName = " + rsmd.getColumnName(c));
                list[c - 1].add(rsmd.getColumnName(c));
            }

            while(rs.next())
            {
                count++;

                for(int c = 1; c <= colCount; c++)
                {
                    String item = rs.getString(rsmd.getColumnName(c));
                    list[c - 1].add(item);
                }
            }

            // CLOSE STATEMENT
            rs.close();
            s.close();
        } catch(SQLException ex)
        {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch(Exception ex)
        {
            return null;
        }

        // CREATE HTML QUERY-TABLE NOW
        String table = "<TABLE border=\"1\" bgcolor=\"yellow\">";
        for(int y = 0; y < list[0].size(); y++)
        {
            table += "<TR>";
            for(int x = 1; x <= list.length; x++)
            {
                table += "<TD>";
                if(y == 0)
                {
                    table += "<B>";
                }
                table += (list[x - 1].get(y) == null ? "" : list[x - 1].get(y));
                if(y == 0)
                {
                    table += "</B>";
                }
                table += "</TD>";
            }
            table += "</TR>";
        }
        table += "</TABLE>";

        return "<B>QUERY RESULT:</B> <BR>" + table;
    }

    static void queryUpdate(Connection conn, String query)
    {
        try
        {
            Statement s = conn.createStatement();
            s.executeUpdate(query);
            System.out.println("QUERY UPDATE EXECUTED: " + query);
        } catch(Exception ex)
        {
            System.out.println("QUERY UPDATE NOT EXECUTED: " + query);
        }
    }

    // THIS TAKES A STRING FOR DB NAME, AND SHOWS ITS TABLES
    public static String[] getTableNames(String DB)
    {
        // FIRST MAKE THE CONNECTION
        Connection conn = getConnection(DB);

        return getTableNames(conn);
    }

    // ASSUMES A CONNECTION IS PRESENT TO A SPECIFIC DB
    public static String[] getTableNames(Connection conn)
    {
        List<String> list = new LinkedList<String>();

        try
        {
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet dmbRS = dbmd.getTables(
                    null, null, "%", new String[]
                    {
                        "TABLE"
                    });

            while(dmbRS.next())
            {
                //System.out.println("db = " + dmbRS.getString(3));
                list.add(dmbRS.getString(3));
            }
        } catch(SQLException e)
        {
        }

        String[] names = new String[list.size()];
        for(int lp = 0; lp < list.size(); lp++)
        {
            names[lp] = list.get(lp);
        //System.out.println("TABLE NAME " + lp + " = " + names[lp]);
        }

        return names;
    }

    // PASS IN A DB AND ONE OF ITS TABLES
    public static String showTable(String DB, String TBL, boolean displayTableName)
    {
        // MAKE CONNECTION FIRST
        Connection conn = getConnection(DB);

        return showTable(conn, TBL, displayTableName);
    }


    // ASSUMES A CONNECTION WITH SOME DB EXISTS TO BE PASSED IN
    public static String showTable(Connection conn, String TBL, boolean displayTableName)
    {
        List[] list = null;

        try
        {
            Statement s = conn.createStatement();
            s.executeQuery("SELECT * FROM " + TBL);
            ResultSet rs = s.getResultSet();
            int count = 0;

            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            list = new LinkedList[colCount];
            // ADD COLUMN NAMES
            for(int c = 1; c <= colCount; c++)
            {
                list[c - 1] = new LinkedList();
                //System.out.println("colName = " + rsmd.getColumnName(c));
                list[c - 1].add(rsmd.getColumnName(c));
            }

            while(rs.next())
            {
                //int idVal = rs.getInt("id");
                //String nameVal = rs.getString("name");
                //String catVal = rs.getString("category");
                // System.out.println(
                //    "*   id = " + idVal + ", name = " + nameVal + ", category = " + catVal);
                ++count;

                for(int c = 1; c <= colCount; c++)
                {
                    String item = rs.getString(rsmd.getColumnName(c));
                    list[c - 1].add(item);
                }
            }

            // CLOSE STATEMENT
            rs.close();
            s.close();

        } catch(Exception e)
        {
            return null;
        }

        // CREATE HTML TABLE NOW
        String table = "<TABLE border=\"1\" bgcolor=\"yellow\">";
        for(int y = 0; y < list[0].size(); y++)
        {
            table += "<TR>";
            for(int x = 1; x <= list.length; x++)
            {
                table += "<TD>";
                if(y == 0)
                {
                    table += "<B>";
                }
                table += (list[x - 1].get(y) == null ? "" : list[x - 1].get(y));
                if(y == 0)
                {
                    table += "</B>";
                }
                table += "</TD>";
            }
            table += "</TR>";
        }
        table += "</TABLE>";

        return displayTableName ? "<B>TABLE:</B> " + TBL + "<BR>" : "" +
                table;
    }

    public static String tabulate(String[] strings, String colour,
            boolean firstBold)
    {
        String table = "<TABLE border=\"1\" bgcolor=\"" + colour + "\">";
        for(int y = 0; y < strings.length; y++)
        {
            table += "<TR><TD>";

            if(y == 0 && firstBold)
                table += "<B>";
            table += (strings[y] == null ? "" : strings[y]);
            if(y == 0 && firstBold)
                table += "</B>";

            table += "</TD></TR>";
        }
        table += "</TABLE>";

        return table;
    }
}