package meerkat.bulletinboard.sqlserver;

import meerkat.bulletinboard.sqlserver.BulletinBoardSQLServer.SQLQueryProvider;
import meerkat.protobuf.BulletinBoardApi.FilterType;
import org.apache.commons.dbcp2.BasicDataSource;

import javax.sql.DataSource;
import java.text.MessageFormat;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by Arbel Deutsch Peled on 09-Dec-15.
 */

public class MySQLQueryProvider implements SQLQueryProvider {

    private String dbAddress;
    private int dbPort;
    private String dbName;
    private String username;
    private String password;

    public MySQLQueryProvider(String dbAddress, int dbPort, String dbName, String username, String password) {
        this.dbAddress = dbAddress;
        this.dbPort = dbPort;
        this.dbName = dbName;
        this.username = username;
        this.password = password;
    }

    @Override
    public String getSQLString(QueryType queryType) throws IllegalArgumentException{

        switch(queryType) {

            case ADD_SIGNATURE:
                return MessageFormat.format(
                        "INSERT IGNORE INTO SignatureTable (EntryNum, SignerId, Signature) VALUES (:{0}, :{1}, :{2})",
                        QueryType.ADD_SIGNATURE.getParamName(0),
                        QueryType.ADD_SIGNATURE.getParamName(1),
                        QueryType.ADD_SIGNATURE.getParamName(2));

            case CONNECT_TAG:
                return MessageFormat.format(
                        "INSERT IGNORE INTO MsgTagTable (TagId, EntryNum)"
                        + " SELECT TagTable.TagId, :{0} AS EntryNum FROM TagTable WHERE Tag = :{1}",
                        QueryType.CONNECT_TAG.getParamName(0),
                        QueryType.CONNECT_TAG.getParamName(1));

            case FIND_MSG_ID:
                return MessageFormat.format(
                        "SELECT EntryNum From MsgTable WHERE MsgId = :{0}",
                        QueryType.FIND_MSG_ID.getParamName(0));

            case FIND_TAG_ID:
                return MessageFormat.format(
                        "SELECT TagId FROM TagTable WHERE Tag = :{0}",
                        QueryType.FIND_TAG_ID.getParamName(0));

            case GET_MESSAGES:
                return "SELECT MsgTable.EntryNum, MsgTable.Msg FROM MsgTable";

            case COUNT_MESSAGES:
                return "SELECT COUNT(MsgTable.EntryNum) FROM MsgTable";

            case GET_MESSAGE_STUBS:
                return "SELECT MsgTable.EntryNum, MsgTable.MsgId, MsgTable.ExactTime FROM MsgTable";

            case GET_SIGNATURES:
                return MessageFormat.format(
                        "SELECT Signature FROM SignatureTable WHERE EntryNum = :{0}",
                        QueryType.GET_SIGNATURES.getParamName(0));

            case INSERT_MSG:
                return MessageFormat.format(
                        "INSERT INTO MsgTable (MsgId, ExactTime, Msg) VALUES(:{0}, :{1}, :{2})",
                        QueryType.INSERT_MSG.getParamName(0),
                        QueryType.INSERT_MSG.getParamName(1),
                        QueryType.INSERT_MSG.getParamName(2));

            case DELETE_MSG_BY_ENTRY:
                return MessageFormat.format(
                        "DELETE IGNORE FROM MsgTable WHERE EntryNum = :{0}",
                        QueryType.DELETE_MSG_BY_ENTRY.getParamName(0));

            case DELETE_MSG_BY_ID:
                return MessageFormat.format(
                        "DELETE IGNORE FROM MsgTable WHERE MsgId = :{0}",
                        QueryType.DELETE_MSG_BY_ID.getParamName(0));

            case INSERT_NEW_TAG:
                return MessageFormat.format(
                        "INSERT IGNORE INTO TagTable(Tag) VALUES (:{0})",
                        QueryType.INSERT_NEW_TAG.getParamName(0));

            case GET_LAST_MESSAGE_ENTRY:
                return "SELECT MAX(MsgTable.EntryNum) FROM MsgTable";

            case GET_BATCH_MESSAGE_DATA_BY_MSG_ID:
                return MessageFormat.format(
                        "SELECT Data FROM BatchTable"
                                + " INNER JOIN MsgTable ON MsgTable.EntryNum = BatchTable.EntryNum"
                                + " WHERE MsgTable.MsgId = :{0} AND BatchTable.SerialNum >= :{1}"
                                + " ORDER BY BatchTable.SerialNum ASC",
                        QueryType.GET_BATCH_MESSAGE_DATA_BY_MSG_ID.getParamName(0),
                        QueryType.GET_BATCH_MESSAGE_DATA_BY_MSG_ID.getParamName(1));

            case GET_BATCH_MESSAGE_DATA_BY_BATCH_ID:
                return MessageFormat.format(
                        "SELECT Data FROM BatchTable"
                                + " WHERE BatchId = :{0} AND SerialNum >= :{1}"
                                + " ORDER BY BatchTable.SerialNum ASC",
                        QueryType.GET_BATCH_MESSAGE_DATA_BY_BATCH_ID.getParamName(0),
                        QueryType.GET_BATCH_MESSAGE_DATA_BY_BATCH_ID.getParamName(1));

            case INSERT_BATCH_DATA:
                return MessageFormat.format(
                        "INSERT INTO BatchTable (BatchId, SerialNum, Data) VALUES (:{0}, :{1}, :{2})",
                        QueryType.INSERT_BATCH_DATA.getParamName(0),
                        QueryType.INSERT_BATCH_DATA.getParamName(1),
                        QueryType.INSERT_BATCH_DATA.getParamName(2));

            case CHECK_BATCH_LENGTH:
                return MessageFormat.format(
                        "SELECT COUNT(Data) AS BatchLength FROM BatchTable WHERE BatchId = :{0}",
                        QueryType.CHECK_BATCH_LENGTH.getParamName(0));

            case CHECK_BATCH_OPEN:
                return MessageFormat.format(
                        "SELECT COUNT(BatchId) AS batchCount FROM BatchTagTable WHERE  BatchId = :{0}",
                        QueryType.CHECK_BATCH_OPEN.getParamName(0));

            case STORE_BATCH_TAGS:
                return MessageFormat.format(
                        "INSERT INTO BatchTagTable (Tags) VALUES (:{0})",
                        QueryType.STORE_BATCH_TAGS.getParamName(0));

            case GET_BATCH_TAGS:
                return MessageFormat.format(
                        "SELECT Tags FROM BatchTagTable WHERE BatchId = :{0}",
                        QueryType.GET_BATCH_TAGS.getParamName(0));

            case ADD_ENTRY_NUM_TO_BATCH:
                return MessageFormat.format(
                        "UPDATE BatchTable SET EntryNum = :{1} WHERE BatchId = :{0}",
                        QueryType.ADD_ENTRY_NUM_TO_BATCH.getParamName(0),
                        QueryType.ADD_ENTRY_NUM_TO_BATCH.getParamName(1));

           default:
               throw new IllegalArgumentException("Cannot serve a query of type " + queryType);
        }

    }

    @Override
    public String getCondition(FilterType filterType, int serialNum) throws IllegalArgumentException {

        String serialString = Integer.toString(serialNum);

        switch(filterType) {
            case EXACT_ENTRY:
                return "MsgTable.EntryNum = :EntryNum" + serialString;
            case MAX_ENTRY:
                return "MsgTable.EntryNum <= :EntryNum" + serialString;
            case MIN_ENTRY:
                return "MsgTable.EntryNum >= :EntryNum" + serialString;
            case MAX_MESSAGES:
                return "LIMIT :Limit" + serialString;
            case MSG_ID:
                return "MsgTable.MsgId = :MsgId" + serialString;
            case SIGNER_ID:
                return "EXISTS (SELECT 1 FROM SignatureTable"
                        + " WHERE SignatureTable.SignerId = :SignerId" + serialString + " AND SignatureTable.EntryNum = MsgTable.EntryNum)";
            case TAG:
                return "EXISTS (SELECT 1 FROM TagTable"
                        + " INNER JOIN MsgTagTable ON TagTable.TagId = MsgTagTable.TagId"
                        + " WHERE TagTable.Tag = :Tag" + serialString + " AND MsgTagTable.EntryNum = MsgTable.EntryNum)";

            case BEFORE_TIME:
                return "MsgTable.ExactTime <= :TimeStamp";

            case AFTER_TIME:
                return "MsgTable.ExactTime >= :TimeStamp";

            default:
                throw new IllegalArgumentException("Cannot serve a filter of type " + filterType);
        }

    }

    @Override
    public String getConditionParamTypeName(FilterType filterType) throws IllegalArgumentException {

        switch(filterType) {
            case EXACT_ENTRY:   // Go through
            case MAX_ENTRY:     // Go through
            case MIN_ENTRY:     // Go through
            case MAX_MESSAGES:
                return "INT";

            case MSG_ID:        // Go through
            case SIGNER_ID:
                return "TINYBLOB";

            case TAG:
                return "VARCHAR";

            case AFTER_TIME:    // Go through
            case BEFORE_TIME:
                return "TIMESTAMP";

            default:
                throw new IllegalArgumentException("Cannot serve a filter of type " + filterType);
        }

    }

    @Override
    public DataSource getDataSource() {

        BasicDataSource dataSource = new BasicDataSource();

        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://" + dbAddress + ":" + dbPort + "/" + dbName);

        dataSource.setUsername(username);
        dataSource.setPassword(password);

        return dataSource;

    }

    @Override
    public List<String> getSchemaCreationCommands() {
        List<String> list = new LinkedList<String>();

        list.add("CREATE TABLE IF NOT EXISTS MsgTable (EntryNum INT NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                + " MsgId TINYBLOB, ExactTime TIMESTAMP, Msg BLOB, UNIQUE(MsgId(50)))");

        list.add("CREATE TABLE IF NOT EXISTS TagTable (TagId INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Tag VARCHAR(50), UNIQUE(Tag))");

        list.add("CREATE TABLE IF NOT EXISTS MsgTagTable (EntryNum INT, TagId INT,"
                + " CONSTRAINT FOREIGN KEY (EntryNum) REFERENCES MsgTable(EntryNum) ON DELETE CASCADE,"
                + " CONSTRAINT FOREIGN KEY (TagId) REFERENCES TagTable(TagId) ON DELETE CASCADE,"
                + " CONSTRAINT UNIQUE (EntryNum, TagID))");

        list.add("CREATE TABLE IF NOT EXISTS SignatureTable (EntryNum INT, SignerId TINYBLOB, Signature TINYBLOB,"
                + " INDEX(SignerId(32)), CONSTRAINT Unique_Signature UNIQUE(SignerId(32), EntryNum),"
                + " CONSTRAINT FOREIGN KEY (EntryNum) REFERENCES MsgTable(EntryNum) ON DELETE CASCADE)");

        list.add("CREATE TABLE IF NOT EXISTS BatchTagTable (BatchId INT AUTO_INCREMENT PRIMARY KEY, Tags BLOB)");

        list.add("CREATE TABLE IF NOT EXISTS BatchTable (BatchId INT, EntryNum INT, SerialNum INT, Data BLOB,"
                + " CONSTRAINT UNIQUE(BatchId, SerialNum),"
                + " CONSTRAINT FOREIGN KEY (BatchId) REFERENCES BatchTagTable(BatchId) ON DELETE CASCADE)");



        return list;
    }

    @Override
    public List<String> getSchemaDeletionCommands() {
        List<String> list = new LinkedList<String>();

        list.add("DROP TABLE IF EXISTS BatchTable");
        list.add("DROP TABLE IF EXISTS BatchTagTable");
        list.add("DROP TABLE IF EXISTS MsgTagTable");
        list.add("DROP TABLE IF EXISTS SignatureTable");
        list.add("DROP TABLE IF EXISTS TagTable");
        list.add("DROP TABLE IF EXISTS MsgTable");

        return list;
    }
}