package sql;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;

class Category {
    private int catId;
    private String catName;
    private ArrayList<Category> children = new ArrayList<Category>();

    Category(int id, String name) {
        catId = id;
        catName = name;
    }

    public boolean add(Category cat, int parent) {
        if (parent == catId) {
            children.add(cat);
            return true;
        } else {
            for (Category child : children) {
                if (child.add(cat, parent)) return true;
            }
            return false;
        }

    }

    private String spaces(int parent) {
        char[] chars = new char[parent*4];
        Arrays.fill(chars, ' ');
        return new String(chars);
    }

    public void print(int ancestors) {
        if (catId != 0) {
            if (ancestors == 0)
                System.out.println(spaces(ancestors) + catName);
            else
                System.out.println(spaces(ancestors) + "|-- " + catName);
        }

        for (Category child : children) {
            child.print(ancestors + 1);
        }
    }

    public boolean printFiltered(int search) {
        /*
        if (search == catId) {
            return true;
        } else {
            for (Category child : children) {
                if (child.add(cat, parent)) return true;
            }
            return false;
        }
        */
    }

}

public class SqlTask {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/task";
    static final String USER = "root";
    static final String PASS = "";

    public static void main(String[] args) {
        Category root = new Category(0, "cat 0");
        Connection conn = null;
        Statement stmt = null;
        int id;
        int parent;
        String name;
        String sql;

        int search = 3;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            System.out.println("Creating statement...");
            stmt = conn.createStatement();

            sql = "SELECT id, name, parent FROM category ORDER BY name ASC";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()) {
                Category cat = null;
                id  = rs.getInt("id");
                name = rs.getString("name");
                parent = rs.getInt("parent");
                cat = new Category(id, name);

                root.add(cat, parent);
            }

            root.print(-1);

            rs.close();
            stmt.close();
            conn.close();
        } catch(SQLException se) {
            se.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt!=null)
                    stmt.close();
            } catch(SQLException se2) {
            }
            try {
                if (conn!=null)
                    conn.close();
            } catch(SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("Task finished!");
    }

}