import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

public class DataBase {
    private static final String EXDB_ADRESS = "jdbc:sqlite:db.db";

    private int mNextGid;
    private int mNextTid;

    public DataBase() {
        super();

        System.out.println("in");

        initTables();
        initNextIdNumber();

    }

    public void insertGallery(LinkedList<Gallery> records) throws SQLException {
        String sql = "INSERT INTO gallerys(name, category, gid) VALUES(?, ?, ?);";
        String sql2 = "INSERT INTO tags(tagName, tid) VALUES(?, ?);";
        String sql3 = "INSERT INTO relation(gid, tid) VALUES(?, ?);";

        Connection conn = getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        PreparedStatement stmt2 = conn.prepareStatement(sql2);
        PreparedStatement stmt3 = conn.prepareStatement(sql3);

        while (!records.isEmpty()) {

            Gallery record = records.poll();
            stmt.setString(1, record.getTitle());
            stmt.setString(2, record.getCategory());
            stmt.setInt(3, mNextGid);
            stmt.execute();

            LinkedList<String> tags = record.getTags();

            String tag = null;
            while ((tag = tags.poll()) != null) {
                // System.out.println("Tags: " + tag.toString());
                String sql4 = "SELECT tid FROM tags WHERE tagName='" + tag + "'";
                ResultSet rs = getResultSet(sql4);
                int tid;
                if (rs.next())
                    tid = rs.getInt(1);
                else
                    tid = mNextGid++;

                stmt2.setString(1, tag);
                stmt2.setInt(2, tid);
                stmt2.execute();

                stmt3.setInt(1, mNextGid);
                stmt3.setInt(2, tid++);
                stmt3.execute();
            }

            mNextGid++;
        }

    }

    private void initNextIdNumber() {
        // TODO Auto-generated method stub

        try {
            String sql = "SELECT MAX(gid) FROM gallerys;";
            ResultSet rs = getResultSet(sql);
            mNextGid = rs.getInt(1);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            mNextGid = 0;
        }
        try {
            String sql = "SELECT MAX(tid) FROM gallerys;";
            ResultSet rs = getResultSet(sql);
            mNextTid = rs.getInt(1);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            mNextTid = 0;
        }

        System.out.println("mNextGid: " + mNextGid);
        System.out.println("mNextTid: " + mNextTid);

    }

    private ResultSet getResultSet(String sql) throws SQLException {
        // System.out.println("Getting Results");
        Connection conn = getConnection();

        ResultSet rs = conn.createStatement().executeQuery(sql);
        return rs;

    }

    private Connection getConnection() {
        try {
            return DriverManager.getConnection(EXDB_ADRESS);
        } catch (SQLException e) {
            System.out.println("DataBase connection fail!!");
            return null;
        }
    }

    private void initTables() {
        creatGalleryTable();
        creatTagsTable();
        creatRelationsTable();
    }

    private void creatGalleryTable() {

        String sql = " CREATE TABLE IF NOT EXISTS gallerys(name TEXT,category TEXT,gid INT);";
        createTable("gallerys", sql);
    }

    private void creatRelationsTable() {

        String sql = " CREATE TABLE IF NOT EXISTS relation(gid INT,tid INT);";
        createTable("nodes", sql);
    }

    private void creatTagsTable() {
        String sql = " CREATE TABLE IF NOT EXISTS tags(tagName TEXT, tid INT);";
        createTable("nodes", sql);

    }

    private void createTable(String name, String sql) {
        try {

            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.execute(sql);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}