package meerkat.bulletinboard.sqlserver;

import meerkat.protobuf.BulletinBoardApi.*;
import org.apache.commons.dbcp2.BasicDataSource;
import org.sqlite.SQLiteDataSource;

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 SQLiteQueryProvider implements BulletinBoardSQLServer.SQLQueryProvider {

    String dbName;

    public SQLiteQueryProvider(String dbName) {
        this.dbName = dbName;
    }

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

        switch(queryType) {
            case ADD_SIGNATURE:
                return "INSERT OR IGNORE INTO SignatureTable (EntryNum, SignerId, Signature) VALUES (:EntryNum,:SignerId,:Signature)";

            case CONNECT_TAG:
                return "INSERT OR IGNORE INTO MsgTagTable (TagId, EntryNum)"
                    + " SELECT TagTable.TagId, :EntryNum AS EntryNum FROM TagTable WHERE Tag = :Tag";

            case FIND_MSG_ID:
                return "SELECT EntryNum From MsgTable WHERE MsgId = :MsgId";

            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 "SELECT Signature FROM SignatureTable WHERE EntryNum = :EntryNum";

            case INSERT_MSG:
                return "INSERT INTO MsgTable (MsgId, Msg) VALUES(:MsgId,:Msg)";

            case INSERT_NEW_TAG:
                return "INSERT OR IGNORE INTO TagTable(Tag) VALUES (:Tag)";

            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 "INTEGER";

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

            case TAG:
                return "VARCHAR";

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

    }

    @Override
    public DataSource getDataSource() {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.sqlite.JDBC");
        dataSource.setUrl("jdbc:sqlite:" + dbName);

        return dataSource;
    }


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

        list.add("CREATE TABLE IF NOT EXISTS MsgTable (EntryNum INTEGER PRIMARY KEY, MsgId BLOB UNIQUE, Msg BLOB)");

        list.add("CREATE TABLE IF NOT EXISTS TagTable (TagId INTEGER PRIMARY KEY, Tag varchar(50) UNIQUE)");
        list.add("CREATE TABLE IF NOT EXISTS MsgTagTable (EntryNum BLOB, TagId INTEGER,"
                + " FOREIGN KEY (EntryNum) REFERENCES MsgTable(EntryNum) ON DELETE CASCADE,"
                + " FOREIGN KEY (TagId) REFERENCES TagTable(TagId) ON DELETE CASCADE,"
                + " UNIQUE (EntryNum, TagID))");

        list.add("CREATE TABLE IF NOT EXISTS SignatureTable (EntryNum INTEGER, SignerId BLOB, Signature BLOB,"
                + " FOREIGN KEY (EntryNum) REFERENCES MsgTable(EntryNum) ON DELETE CASCADE,"
                + " UNIQUE(SignerId, EntryNum))");

        list.add("CREATE INDEX IF NOT EXISTS SignerIndex ON SignatureTable(SignerId)");

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

        list.add("CREATE TABLE IF NOT EXISTS BatchTable (BatchId INTEGER, EntryNum INTEGER, SerialNum INTEGER, Data BLOB,"
                + " UNIQUE(BatchId, SerialNum),"
                + " 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 MsgTagTable");

        list.add("DROP INDEX IF EXISTS SignerIndex");
        list.add("DROP TABLE IF EXISTS SignatureTable");

        list.add("DROP TABLE IF EXISTS TagTable");
        list.add("DROP TABLE IF EXISTS MsgTable");

        return list;
    }
}
