namespace PartnerDAL.Query.ClientDomain
{
    public static class ClientDomainQB
    {
        // Index: IX_TCLIENTDOMAIN_CLIENTID on (CLIENTID) INCLUDE (DOMAINNAME, DATABASENAME, ISVERIFIED, STATUS)
        public const string GET_CLIENT_DOMAINS = @"
            SELECT DOMAINID             AS DomainId,
                   CLIENTID             AS ClientId,
                   DATABASENAME         AS DatabaseName,
                   DOMAINNAME           AS DomainName,
                   EXPECTEDCNAMETARGET  AS ExpectedCnameTarget,
                   ISVERIFIED           AS IsVerified,
                   SSLSTATUS            AS SslStatus,
                   CERTEXPIRYDATE       AS CertExpiryDate,
                   STATUS               AS Status,
                   CREATEDBYID          AS CreatedById,
                   CREATEDON            AS CreatedOn,
                   MODIFIEDBYID         AS ModifiedById,
                   MODIFIEDON           AS ModifiedOn
            FROM   TCLIENTDOMAIN
            WHERE  CLIENTID = @ClientId
              AND  STATUS   = 1
            ORDER BY CREATEDON DESC;";

        public const string GET_CLIENT_DOMAIN_BY_ID = @"
            SELECT DOMAINID             AS DomainId,
                   CLIENTID             AS ClientId,
                   DATABASENAME         AS DatabaseName,
                   DOMAINNAME           AS DomainName,
                   EXPECTEDCNAMETARGET  AS ExpectedCnameTarget,
                   ISVERIFIED           AS IsVerified,
                   SSLSTATUS            AS SslStatus,
                   CERTEXPIRYDATE       AS CertExpiryDate,
                   STATUS               AS Status
            FROM   TCLIENTDOMAIN
            WHERE  DOMAINID = @DomainId
              AND  STATUS   = 1;";

        // Index: IX_TCLIENTDOMAIN_DOMAIN_ACTIVE on (DOMAINNAME, STATUS) INCLUDE (DOMAINID, CLIENTID, DATABASENAME)
        public const string GET_DOMAIN_TENANT = @"
                SELECT DOMAINID             AS DomainId,
                       CLIENTID             AS ClientId,
                       DATABASENAME         AS DatabaseName,
                       DOMAINNAME           AS DomainName,
                       EXPECTEDCNAMETARGET  AS ExpectedCnameTarget,
                       ISVERIFIED           AS IsVerified,
                       SSLSTATUS            AS SslStatus
                FROM   TCLIENTDOMAIN
                WHERE  DOMAINNAME = @DomainName
                  AND  STATUS     = 1;";

        public const string GET_ALL_ACTIVE_DOMAINS = @"
            SELECT DOMAINID             AS DomainId,
                   CLIENTID             AS ClientId,
                   DATABASENAME         AS DatabaseName,
                   DOMAINNAME           AS DomainName,
                   EXPECTEDCNAMETARGET  AS ExpectedCnameTarget,
                   ISVERIFIED           AS IsVerified,
                   SSLSTATUS            AS SslStatus
            FROM   TCLIENTDOMAIN
            WHERE  STATUS = 1;";

        public const string SAVE_CLIENT_DOMAIN = @"
            INSERT INTO TCLIENTDOMAIN
                (CLIENTID, DATABASENAME, DOMAINNAME, EXPECTEDCNAMETARGET, ISVERIFIED, SSLSTATUS, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@ClientId, @DatabaseName, @DomainName, @ExpectedCnameTarget, 0, 'PENDING', 1,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_CLIENT_DOMAIN = @"
            UPDATE TCLIENTDOMAIN
            SET    DATABASENAME         = @DatabaseName,
                   DOMAINNAME           = @DomainName,
                   EXPECTEDCNAMETARGET  = @ExpectedCnameTarget,
                   MODIFIEDBYID         = @ModifiedById,
                   MODIFIEDON           = @ModifiedOn
            WHERE  DOMAINID = @DomainId;";

        public const string DELETE_CLIENT_DOMAIN = @"
            UPDATE TCLIENTDOMAIN
            SET    STATUS       = 0,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETUTCDATE()
            WHERE  DOMAINID = @DomainId;";

        public const string VERIFY_CLIENT_DOMAIN = @"
            UPDATE TCLIENTDOMAIN
            SET    ISVERIFIED   = 1,
                   SSLSTATUS    = 'ACTIVE',
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETUTCDATE()
            WHERE  DOMAINID = @DomainId;";

        // ---------------------------------------------------------------
        // Platform-wide settings (e.g. Domains.DefaultCnameTarget)
        // Table: TPLATFORMSETTINGS (SETTINGKEY PK, SETTINGVALUE, MODIFIEDBYID, MODIFIEDON)
        // ---------------------------------------------------------------

        public const string GET_PLATFORM_SETTING = @"
            SELECT SETTINGVALUE
            FROM   TPLATFORMSETTINGS
            WHERE  SETTINGKEY = @SettingKey;";

        public const string GET_ALL_PLATFORM_SETTINGS = @"
            SELECT SETTINGKEY   AS SettingKey,
                   SETTINGVALUE AS SettingValue,
                   MODIFIEDBYID AS ModifiedById,
                   MODIFIEDON   AS ModifiedOn
            FROM   TPLATFORMSETTINGS
            ORDER BY SETTINGKEY;";

        // Upsert: insert if key doesn't exist, update if it does
        public const string SAVE_PLATFORM_SETTING = @"
            MERGE TPLATFORMSETTINGS AS target
            USING (SELECT @SettingKey AS SettingKey) AS src
                ON target.SETTINGKEY = src.SettingKey
            WHEN MATCHED THEN
                UPDATE SET SETTINGVALUE = @SettingValue,
                           MODIFIEDBYID = @ModifiedById,
                           MODIFIEDON   = GETUTCDATE()
            WHEN NOT MATCHED THEN
                INSERT (SETTINGKEY, SETTINGVALUE, MODIFIEDBYID, MODIFIEDON)
                VALUES (@SettingKey, @SettingValue, @ModifiedById, GETUTCDATE());";
    }
}