/*
 *       Copyright 2017 Netflix, Inc.
 *          Licensed under the Apache License, Version 2.0 (the "License");
 *          you may not use this file except in compliance with the License.
 *          You may obtain a copy of the License at
 *              http://www.apache.org/licenses/LICENSE-2.0
 *          Unless required by applicable law or agreed to in writing, software
 *          distributed under the License is distributed on an "AS IS" BASIS,
 *          WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *          See the License for the specific language governing permissions and
 *          limitations under the License.
 */

package com.netflix.metacat.metadata.mysql;

import com.google.common.base.Joiner;
import com.google.common.base.Preconditions;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.netflix.metacat.common.QualifiedName;
import com.netflix.metacat.common.json.MetacatJson;
import com.netflix.metacat.common.server.model.Lookup;
import com.netflix.metacat.common.server.model.TagItem;
import com.netflix.metacat.common.server.properties.Config;
import com.netflix.metacat.common.server.usermetadata.LookupService;
import com.netflix.metacat.common.server.usermetadata.TagService;
import com.netflix.metacat.common.server.usermetadata.UserMetadataService;
import com.netflix.metacat.common.server.usermetadata.UserMetadataServiceException;
import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Nullable;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Tag service implementation.
 *
 * @author amajumdar
 * @author zhenl
 */
@Slf4j
@SuppressFBWarnings
@Transactional("metadataTxManager")
public class MySqlTagService implements TagService {
    /**
     * Lookup name for tag.
     */
    private static final String LOOKUP_NAME_TAG = "tag";
    private static final String NAME_TAGS = "tags";
    private static final String QUERY_LIST =
        "select distinct i.name from tag_item i, tag_item_tags t where i.id=t.tag_item_id"
            + " and (1=? or t.tags_string %s ) and (1=? or i.name like ?) and (1=? or i.name rlike ?)";

    private static final String QUERY_SEARCH =
        "select distinct i.name from tag_item i, tag_item_tags t where i.id=t.tag_item_id"
            + " and (1=? or t.tags_string %s ) and (1=? or i.name like ?)";
    private static final String SQL_GET_TAG_ITEM =
        "select id, name, created_by createdBy, last_updated_by lastUpdatedBy, date_created dateCreated,"
            + " last_updated lastUpdated from tag_item where name=?";
    private static final String SQL_INSERT_TAG_ITEM =
        "insert into tag_item( name, version, created_by, last_updated_by, date_created, last_updated)"
            + " values (?,0, ?,?,now(),now())";
    private static final String SQL_UPDATE_TAG_ITEM =
        "update tag_item set name=?, last_updated=now() where name=?";
    private static final String SQL_INSERT_TAG_ITEM_TAGS =
        "insert into tag_item_tags( tag_item_id, tags_string) values (?,?)";
    private static final String SQL_DELETE_TAG_ITEM =
        "delete from tag_item where name=?";
    private static final String SQL_DELETE_TAG_ITEM_TAGS_BY_NAME =
        "delete from tag_item_tags where tag_item_id=(select id from tag_item where name=?)";
    private static final String SQL_DELETE_TAG_ITEM_TAGS_BY_NAME_TAGS =
        "delete from tag_item_tags where tag_item_id=(select id from tag_item where name=?) and tags_string in (%s)";
    private static final String SQL_DELETE_TAG_ITEM_TAGS =
        "delete from tag_item_tags where tag_item_id=? and tags_string in (%s)";
    private static final String SQL_GET_TAG_ITEM_TAGS =
        "select tags_string value from tag_item_tags where tag_item_id=?";
    //    private static final String SQL_GET_LOOKUP_VALUES_BY_NAME =
//        "select lv.tags_string value from tag_item l, tag_item_tags lv where l.id=lv.tag_item_id and l.name=?";
    private final Config config;
    private final LookupService lookupService;
    private final MetacatJson metacatJson;
    private final UserMetadataService userMetadataService;
    private JdbcTemplate jdbcTemplate;

    /**
     * Constructor.
     *
     * @param config              config
     * @param jdbcTemplate        JDBC template
     * @param lookupService       lookup service
     * @param metacatJson         json util
     * @param userMetadataService user metadata service
     */
    public MySqlTagService(
        final Config config,
        final JdbcTemplate jdbcTemplate,
        final LookupService lookupService,
        final MetacatJson metacatJson,
        final UserMetadataService userMetadataService
    ) {
        this.config = Preconditions.checkNotNull(config, "config is required");
        this.jdbcTemplate = jdbcTemplate;
        this.lookupService = Preconditions.checkNotNull(lookupService, "lookupService is required");
        this.metacatJson = Preconditions.checkNotNull(metacatJson, "metacatJson is required");
        this.userMetadataService = Preconditions.checkNotNull(userMetadataService, "userMetadataService is required");
    }

    private Lookup addTags(final Set<String> tags) {
        try {
            return lookupService.addValues(LOOKUP_NAME_TAG, tags);
        } catch (Exception e) {
            final String message = String.format("Failed adding the tags %s", tags);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }

    }

    /**
     * Get the tag item.
     *
     * @param name name
     * @return tag item
     */
    public TagItem get(final QualifiedName name) {
        return get(name.toString());
    }

    /**
     * Returns the TagItem for the given <code>name</code>.
     *
     * @param name tag name
     * @return TagItem
     */
    @Transactional(readOnly = true)
    public TagItem get(final String name) {
        try {
            return jdbcTemplate.queryForObject(
                SQL_GET_TAG_ITEM,
                new Object[]{name}, new int[]{Types.VARCHAR},
                (rs, rowNum) -> {
                    final TagItem tagItem = new TagItem();
                    tagItem.setId(rs.getLong("id"));
                    tagItem.setName(rs.getString("name"));
                    tagItem.setCreatedBy(rs.getString("createdBy"));
                    tagItem.setLastUpdated(rs.getDate("lastUpdated"));
                    tagItem.setLastUpdatedBy(rs.getString("lastUpdatedBy"));
                    tagItem.setDateCreated(rs.getDate("dateCreated"));
                    tagItem.setValues(getValues(rs.getLong("id")));
                    return tagItem;

                });
        } catch (EmptyResultDataAccessException e) {
            return null;
        } catch (Exception e) {
            final String message = String.format("Failed to get the tag for name %s", name);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }

    }

    /**
     * Returns the list of tags of the tag item id.
     *
     * @param tagItemId tag item id
     * @return list of tags
     */
    private Set<String> getValues(final Long tagItemId) {
        try {
            return MySqlServiceUtil.getValues(jdbcTemplate, SQL_GET_TAG_ITEM_TAGS, tagItemId);
        } catch (EmptyResultDataAccessException e) {
            return Sets.newHashSet();
        } catch (Exception e) {
            final String message = String.format("Failed to get the tags for id %s", tagItemId);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
    }

    /**
     * findOrCreateTagItemByName.
     *
     * @param name name to find or create
     * @return Tag Item
     * @throws SQLException sql exception
     */
    private TagItem findOrCreateTagItemByName(final String name) throws SQLException {
        TagItem result = get(name);
        if (result == null) {
            final KeyHolder holder = new GeneratedKeyHolder();
            jdbcTemplate.update(connection -> {
                final PreparedStatement ps = connection.prepareStatement(SQL_INSERT_TAG_ITEM,
                    Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, name);
                ps.setString(2, config.getTagServiceUserAdmin());
                ps.setString(3, config.getTagServiceUserAdmin());
                return ps;
            }, holder);
            final Long id = holder.getKey().longValue();
            result = new TagItem();
            result.setName(name);
            result.setId(id);
        }
        return result;
    }

    @Override
    public void renameTableTags(final QualifiedName name, final String newTableName) {
        try {
            final QualifiedName newName = QualifiedName.ofTable(name.getCatalogName(), name.getDatabaseName(),
                newTableName);
            if (get(newName) != null) {
                delete(newName, true);
            }
            jdbcTemplate.update(SQL_UPDATE_TAG_ITEM, new String[]{newName.toString(), name.toString()},
                new int[]{Types.VARCHAR, Types.VARCHAR});
        } catch (Exception e) {
            final String message = String.format("Failed to rename item name %s", name);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
    }

    @Override
    public void delete(final QualifiedName name, final boolean updateUserMetadata) {
        try {
            jdbcTemplate
                .update(SQL_DELETE_TAG_ITEM_TAGS_BY_NAME, new SqlParameterValue(Types.VARCHAR, name.toString()));
            jdbcTemplate.update(SQL_DELETE_TAG_ITEM, new SqlParameterValue(Types.VARCHAR, name.toString()));
            if (updateUserMetadata) {
                // Set the tags in user metadata
                final Map<String, Set<String>> data = Maps.newHashMap();
                data.put(NAME_TAGS, Sets.newHashSet());
                userMetadataService
                    .saveDefinitionMetadata(name, "admin", Optional.of(metacatJson.toJsonObject(data)),
                        true);
            }
        } catch (Exception e) {
            final String message = String.format("Failed to delete all tags for name %s", name);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
    }

    /**
     * remove.
     *
     * @param name               qualifiedName
     * @param tags               tags
     * @param updateUserMetadata flag to update user metadata
     */
    public void remove(final QualifiedName name, final Set<String> tags, final boolean updateUserMetadata) {
        try {
            jdbcTemplate.update(String.format(SQL_DELETE_TAG_ITEM_TAGS_BY_NAME_TAGS,
                "'" + Joiner.on("','").skipNulls().join(tags) + "'"),
                new SqlParameterValue(Types.VARCHAR, name.toString()));
            if (updateUserMetadata) {
                final TagItem tagItem = get(name);
                tagItem.getValues().removeAll(tags);
                final Map<String, Set<String>> data = Maps.newHashMap();
                data.put(NAME_TAGS, tagItem.getValues());
                userMetadataService
                    .saveDefinitionMetadata(name, "admin", Optional.of(metacatJson.toJsonObject(data)),
                        true);
            }
        } catch (Exception e) {
            final String message = String.format("Failed to remove tags for name %s", name);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
    }

    /**
     * Returns the list of tags.
     *
     * @return list of tag names
     */
    @Override
    @Transactional(readOnly = true)
    public Set<String> getTags() {
        return lookupService.getValues(LOOKUP_NAME_TAG);
    }

    /**
     * Returns the list of <code>QualifiedName</code> of items that are tagged by the
     * given <code>includeTags</code> and do not contain the given <code>excludeTags</code>.
     *
     * @param includeTags  include items that contain tags
     * @param excludeTags  include items that do not contain tags
     * @param sourceName   catalog/source name
     * @param databaseName database name
     * @param tableName    table name
     * @param type         metacat data category
     * @return list of qualified names of the items
     */
    @Override
    @Transactional(readOnly = true)
    public List<QualifiedName> list(
        @Nullable final Set<String> includeTags,
        @Nullable final Set<String> excludeTags,
        @Nullable final String sourceName,
        @Nullable final String databaseName,
        @Nullable final String tableName,
        @Nullable final QualifiedName.Type type
    ) {
        Set<String> includedNames = Sets.newHashSet();
        final Set<String> excludedNames = Sets.newHashSet();
        final String wildCardName =
            QualifiedName.qualifiedNameToWildCardQueryString(sourceName, databaseName, tableName);
        //Includes
        final Set<String> localIncludes = includeTags != null ? includeTags : Sets.newHashSet();
        try {
            StringBuilder query = new StringBuilder(
                String.format(QUERY_LIST, "in ('" + Joiner.on("','").skipNulls().join(localIncludes) + "')"
                ));
            final Object[] params = {
                localIncludes.size() == 0 ? 1 : 0,
                wildCardName == null ? 1 : 0,
                wildCardName,
                type == null ? 1 : 0,
                type == null ? ".*" : type.getRegexValue(),
            };
            includedNames.addAll(jdbcTemplate.query(query.toString(),
                params,
                new int[]{Types.INTEGER,
                    Types.INTEGER,
                    Types.VARCHAR,
                    Types.INTEGER,
                    Types.VARCHAR,
                },
                (rs, rowNum) -> rs.getString("name")));
            if (excludeTags != null && !excludeTags.isEmpty()) {
                //Excludes
                query = new StringBuilder(
                    String.format(QUERY_LIST, "in ('" + Joiner.on("','").skipNulls().join(excludeTags) + "')"));
                final Object[] eParams = {
                    excludeTags.size() == 0 ? 1 : 0,
                    wildCardName == null ? 1 : 0,
                    wildCardName,
                    type == null ? 1 : 0,
                    type == null ? ".*" : type.getRegexValue(),
                };
                excludedNames.addAll(jdbcTemplate.query(query.toString(),
                    eParams,
                    new int[]{Types.INTEGER,
                        Types.INTEGER,
                        Types.VARCHAR,
                        Types.INTEGER,
                        Types.VARCHAR,
                    },
                    (rs, rowNum) -> rs.getString("name")));
            }
        } catch (Exception e) {
            final String message = String.format("Failed getting the list of qualified names for tags %s", includeTags);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
        if (excludeTags != null && !excludeTags.isEmpty()) {
            includedNames = Sets.difference(includedNames, excludedNames);
        }
        return includedNames.stream().map(s -> QualifiedName.fromString(s, false)).collect(Collectors.toList());
    }

    /**
     * Returns the list of <code>QualifiedName</code> of items that have tags containing the given tag text.
     *
     * @param tag          partial text of a tag
     * @param sourceName   source/catalog name
     * @param databaseName database name
     * @param tableName    table name
     * @return list of qualified names of the items
     */
    @Override
    @Transactional(readOnly = true)
    public List<QualifiedName> search(
        @Nullable final String tag,
        @Nullable final String sourceName,
        @Nullable final String databaseName,
        @Nullable final String tableName
    ) {
        final Set<String> result = Sets.newHashSet();
        try {
            final String wildCardName =
                QualifiedName.qualifiedNameToWildCardQueryString(sourceName, databaseName, tableName);
            //Includes
            final String query = String.format(QUERY_SEARCH, "like ?");
            final Object[] params = {tag == null ? 1 : 0, tag + "%", wildCardName == null ? 1 : 0, wildCardName};
            result.addAll(jdbcTemplate.query(query, params,
                new int[]{Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.VARCHAR},
                (rs, rowNum) -> rs.getString("name")));
        } catch (Exception e) {
            final String message = String.format("Failed getting the list of qualified names for tag %s", tag);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
        return result.stream().map(QualifiedName::fromString).collect(Collectors.toList());
    }

    /**
     * Tags the given table with the given <code>tags</code>.
     *
     * @param name resource name
     * @param tags list of tags
     * @return return the complete list of tags associated with the table
     */
    @Override
    public Set<String> setTags(final QualifiedName name, final Set<String> tags,
                               final boolean updateUserMetadata) {
        addTags(tags);
        try {
            final TagItem tagItem = findOrCreateTagItemByName(name.toString());
            final Set<String> inserts;
            Set<String> deletes = Sets.newHashSet();
            Set<String> values = tagItem.getValues();
            if (values == null || values.isEmpty()) {
                inserts = tags;
            } else {
                inserts = Sets.difference(tags, values).immutableCopy();
                deletes = Sets.difference(values, tags).immutableCopy();
            }
            values = tags;
            if (!inserts.isEmpty()) {
                insertTagItemTags(tagItem.getId(), inserts);
            }
            if (!deletes.isEmpty()) {
                removeTagItemTags(tagItem.getId(), deletes);
            }
            if (updateUserMetadata) {
                // Set the tags in user metadata
                final Map<String, Set<String>> data = Maps.newHashMap();
                data.put(NAME_TAGS, values);
                userMetadataService
                    .saveDefinitionMetadata(name, "admin", Optional.of(metacatJson.toJsonObject(data)),
                        true);
            }
        } catch (Exception e) {
            final String message = String.format("Failed to remove tags for name %s", name);
            log.error(message, e);
            throw new UserMetadataServiceException(message, e);
        }
        return tags;
    }

    private void removeTagItemTags(final Long id, final Set<String> tags) {
        jdbcTemplate
            .update(String.format(SQL_DELETE_TAG_ITEM_TAGS, "'" + Joiner.on("','").skipNulls().join(tags) + "'"),
                new SqlParameterValue(Types.BIGINT, id));
    }

    private void insertTagItemTags(final Long id, final Set<String> tags) {
        jdbcTemplate.batchUpdate(SQL_INSERT_TAG_ITEM_TAGS, tags.stream().map(tag -> new Object[]{id, tag})
            .collect(Collectors.toList()), new int[]{Types.BIGINT, Types.VARCHAR});
    }

    /**
     * Removes the tags from the given resource.
     *
     * @param name      qualified name
     * @param deleteAll if true, will delete all tags associated with the given table
     * @param tags      list of tags to be removed for the given table
     */
    @Override
    public void removeTags(final QualifiedName name, final Boolean deleteAll,
                           final Set<String> tags, final boolean updateUserMetadata) {
        if (deleteAll != null && deleteAll) {
            delete(name, updateUserMetadata);
        } else {
            remove(name, tags, updateUserMetadata);
        }
    }
}