package jp.ac.osaka_u.ist.sel.similarity.database;

import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DatabaseCreationDAO extends AbstractDAO {

    public DatabaseCreationDAO(TransactionManager transactionManager) throws SQLException {
        super(transactionManager);
    }

    public boolean createDomainInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.DOMAIN_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.NAME + " " + ColumnName.getColumnType(ColumnName.NAME) + " NOT NULL)";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.DOMAIN_INFO, ColumnName.ID);
            return createIndex;
        }
        return false;
    }
    
    public boolean createFileInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.FILE_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.PATH + " " + ColumnName.getColumnType(ColumnName.PATH) + " NOT NULL, "
        + ColumnName.EXT + " " + ColumnName.getColumnType(ColumnName.EXT) + " NOT NULL, "
        + ColumnName.FILE_SIZE + " " + ColumnName.getColumnType(ColumnName.FILE_SIZE) + " NOT NULL, "
        + ColumnName.DOMAIN_ID + " " + ColumnName.getColumnType(ColumnName.DOMAIN_ID) + " NOT NULL, "
        //+ ColumnName.MODIFIED + " " + ColumnName.getColumnType(ColumnName.MODIFIED) + " NOT NULL, "
        + "FOREIGN KEY (" + ColumnName.DOMAIN_ID + ") REFERENCES " + TableType.DOMAIN_INFO + "(" + ColumnName.ID + "))";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.FILE_INFO, ColumnName.ID);
            return createIndex;
        }
        return false;
    }

    public boolean createTokenInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.TOKEN_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.PATH + " " + ColumnName.getColumnType(ColumnName.PATH) + " NOT NULL, "
        + ColumnName.NUM_OF_TOK + " " + ColumnName.getColumnType(ColumnName.NUM_OF_TOK) + " NOT NULL, "
        + ColumnName.FILE_SIZE + " " + ColumnName.getColumnType(ColumnName.FILE_SIZE) + " NOT NULL, "
        + "FOREIGN KEY (" + ColumnName.ID + ") REFERENCES " + TableType.FILE_INFO + "(" + ColumnName.ID + "))";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.TOKEN_INFO, ColumnName.FILE_SIZE);
            return createIndex;
        }
        return false;
    }

    public boolean createHashInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.HASH_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.MD5_HASH + " " + ColumnName.getColumnType(ColumnName.MD5_HASH) + " NOT NULL, "
        + "FOREIGN KEY (" + ColumnName.ID + ") REFERENCES " + TableType.FILE_INFO + "(" + ColumnName.ID + "))";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.HASH_INFO, ColumnName.MD5_HASH);
            return createIndex;
        }
        return false;
    }

    public boolean createCloneInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.CLONE_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.GROUP_ID + " " + ColumnName.getColumnType(ColumnName.GROUP_ID) + " NOT NULL, "
        + "FOREIGN KEY (" + ColumnName.ID + ") REFERENCES " + TableType.FILE_INFO + "(" + ColumnName.ID + "))";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.CLONE_INFO, ColumnName.GROUP_ID);
            return createIndex;
        }
        return false;
    }
    
    public boolean createFileManageInfo() throws SQLException {
        // create sql
        String sql = "CREATE TABLE " + TableType.FILE_MANAGE_INFO + " ("
        + ColumnName.ID + " " + ColumnName.getColumnType(ColumnName.ID) + " NOT NULL PRIMARY KEY, "
        + ColumnName.MODIFIED + " " + ColumnName.getColumnType(ColumnName.MODIFIED) + " NOT NULL, "
        + "FOREIGN KEY (" + ColumnName.ID + ") REFERENCES " + TableType.FILE_INFO + "(" + ColumnName.ID + "))";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            boolean createIndex = createIndex(TableType.FILE_MANAGE_INFO, ColumnName.MODIFIED);
            return createIndex;
        }
        return false;
    }
    
    private boolean createIndex(TableType table, ColumnName column) throws SQLException {
        // create sql
        String sql = "CREATE INDEX IDX_" + table + "_" + column + " ON " + table + "(" + column + ")";
        // prepare the statement
        PreparedStatement pstmt = prepareStatement(sql);
        // execute query
        if (executeUpdate(pstmt) == 0) {
            return true;
        }
        return false;
    }
    
}
