/*
 * Copyright (c) 2024, WSO2 LLC. (http://www.wso2.com).
 *
 * WSO2 LLC. licenses this file to you 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 org.wso2.carbon.is.migration.service.v710.constants;

/**
 * This class contains the SQL constants used in the email management feature.
 */
public class NotificationSQLConstants {

    // sql constants for notification type
    public static final String LIST_NOTIFICATION_TYPES_SQL =
            "SELECT TYPE_KEY " +
                    "FROM IDN_NOTIFICATION_TYPE " +
                    "WHERE CHANNEL = :CHANNEL; " +
                    "AND TENANT_ID = :TENANT_ID;";
    public static final String INSERT_NOTIFICATION_TYPE_SQL =
            "INSERT INTO IDN_NOTIFICATION_TYPE " +
                    "(TYPE_KEY, NAME, CHANNEL, TENANT_ID) " +
                    "VALUES (:TYPE_KEY;, :NAME;, :CHANNEL;, :TENANT_ID;)";
    public static final String GET_NOTIFICATION_TYPE_ID_SQL =
            "SELECT ID " +
                    "FROM IDN_NOTIFICATION_TYPE " +
                    "WHERE TYPE_KEY = :TYPE_KEY; " +
                    "AND CHANNEL = :CHANNEL; " +
                    "AND TENANT_ID = :TENANT_ID;";

    // sql constants for org notification template
    public static final String LIST_ORG_NOTIFICATION_TEMPLATE_KEYS_SQL =
            "SELECT T_ORG.TEMPLATE_KEY AS TEMPLATE_KEY, T_TYPE.TYPE_KEY AS TYPE_KEY " +
                    "FROM IDN_NOTIFICATION_ORG_TEMPLATE T_ORG " +
                    "JOIN IDN_NOTIFICATION_TYPE T_TYPE " +
                    "ON T_ORG.TYPE_ID = T_TYPE.ID " +
                    "WHERE T_TYPE.CHANNEL = :CHANNEL; " +
                    "AND T_TYPE.TENANT_ID = :TENANT_ID; " +
                    "AND T_ORG.TENANT_ID = :TENANT_ID;";

    public static final String INSERT_ORG_NOTIFICATION_TEMPLATE_SQL =
            "INSERT INTO IDN_NOTIFICATION_ORG_TEMPLATE " +
                    "(TEMPLATE_KEY, LOCALE, SUBJECT, BODY, FOOTER, CONTENT_TYPE, TYPE_ID, TENANT_ID) " +
                    "VALUES (:TEMPLATE_KEY;, :LOCALE;, :SUBJECT;, :BODY;, :FOOTER;, :CONTENT_TYPE;, (" +
                    GET_NOTIFICATION_TYPE_ID_SQL + "), :TENANT_ID;)";

    public static final String LIST_ORG_NOTIFICATION_TEMPLATES_SQL =
            "SELECT " +
                    "T_TYPE.TYPE_KEY AS TYPE_KEY, " +
                    "T_TYPE.NAME AS NAME, " +
                    "T_ORG.LOCALE AS LOCALE, " +
                    "T_ORG.SUBJECT AS SUBJECT, " +
                    "T_ORG.BODY AS BODY, " +
                    "T_ORG.FOOTER AS FOOTER, " +
                    "T_ORG.CONTENT_TYPE AS CONTENT_TYPE " +
                    "FROM IDN_NOTIFICATION_ORG_TEMPLATE T_ORG " +
                    "JOIN IDN_NOTIFICATION_TYPE T_TYPE " +
                    "ON T_ORG.TYPE_ID = T_TYPE.ID " +
                    "WHERE T_TYPE.CHANNEL = :CHANNEL; " +
                    "AND T_TYPE.TENANT_ID = :TENANT_ID; " +
                    "AND T_ORG.TENANT_ID = :TENANT_ID;";

    public static final String DELETE_ORG_NOTIFICATION_TEMPLATE_SQL =
            "DELETE FROM IDN_NOTIFICATION_ORG_TEMPLATE WHERE TEMPLATE_KEY = :TEMPLATE_KEY; AND TYPE_ID = (" +
                    GET_NOTIFICATION_TYPE_ID_SQL + ") AND TENANT_ID = :TENANT_ID;";

    // sql constants for app notification template
    public static final String LIST_APP_NOTIFICATION_TEMPLATE_KEYS_SQL =
            "SELECT T_APP.TEMPLATE_KEY AS TEMPLATE_KEY, T_APP.APP_ID AS APP_ID, T_TYPE.TYPE_KEY AS TYPE_KEY " +
                    "FROM IDN_NOTIFICATION_APP_TEMPLATE T_APP " +
                    "JOIN IDN_NOTIFICATION_TYPE T_TYPE " +
                    "ON T_APP.TYPE_ID = T_TYPE.ID " +
                    "WHERE T_TYPE.CHANNEL = :CHANNEL; " +
                    "AND T_TYPE.TENANT_ID = :TENANT_ID; " +
                    "AND T_APP.TENANT_ID = :TENANT_ID;";
    public static final String INSERT_APP_NOTIFICATION_TEMPLATE_SQL =
            "INSERT INTO IDN_NOTIFICATION_APP_TEMPLATE " +
                    "(TEMPLATE_KEY, LOCALE, SUBJECT, BODY, FOOTER, CONTENT_TYPE, TYPE_ID, APP_ID, TENANT_ID) " +
                    "VALUES (:TEMPLATE_KEY;, :LOCALE;, :SUBJECT;, :BODY;, :FOOTER;, :CONTENT_TYPE;, (" +
                    GET_NOTIFICATION_TYPE_ID_SQL + "), :APP_ID;, :TENANT_ID;)";

    /**
     * Grouping of constants related to database table names.
     */
    public static class NotificationTableColumns {

        public static final String ID = "ID";
        public static final String TYPE_KEY = "TYPE_KEY";
        public static final String NAME = "NAME";
        public static final String CHANNEL = "CHANNEL";
        public static final String TENANT_ID = "TENANT_ID";
        public static final String TEMPLATE_KEY = "TEMPLATE_KEY";
        public static final String LOCALE = "LOCALE";
        public static final String SUBJECT = "SUBJECT";
        public static final String BODY = "BODY";
        public static final String FOOTER = "FOOTER";
        public static final String CONTENT_TYPE = "CONTENT_TYPE";
        public static final String TYPE_ID = "TYPE_ID";
        public static final String APP_ID = "APP_ID";
    }
}
