using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using SwDAL.DTO.MetadataSync;

namespace SwDAL.Query.MetadataSync;

// Required index: IX_MSWMETADATAVERSION_TENANTID ON SW.MSWMETADATAVERSION (TENANTID, STATUS, DBMODELID)
public class MetadataSyncQB : IQueryBuilder<MetadataSyncListQuery, MetadataSyncListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        { ["versionlabel"] = "mv.VERSIONLABEL" };

    public (string Sql, DynamicParameters Params) Build(MetadataSyncListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "mv.METAVERSIONID DESC");
        return BuildMetadataSyncList(query.Criteria, dialect, orderBy);
    }

    public const string GET_BY_ID = @"
        SELECT mv.METAVERSIONID  AS MetaVersionId,
               mv.DBMODELID      AS DbModelId,
               m.DBMODELNAME     AS DbModelName,
               mv.VERSIONLABEL   AS VersionLabel,
               mv.METASTATUS     AS MetaStatus,
               mv.SNAPSHOTJSON   AS SnapshotJson,
               mv.DESCRIPTION    AS Description,
               mv.VERSION,
               mv.STATUS,
               mv.SORTORDER      AS SortOrder,
               mv.CREATEDBYID    AS CreatedById,
               mv.CREATEDON      AS CreatedOn,
               mv.MODIFIEDBYID   AS ModifiedById,
               mv.MODIFIEDON     AS ModifiedOn,
               mv.SOURCETYPE     AS SourceType,
               mv.TENANTID       AS TenantId
        FROM   SW.MSWMETADATAVERSION mv
        INNER JOIN SW.MSWDBMODEL m ON m.DBMODELID = mv.DBMODELID AND m.TENANTID = mv.TENANTID
        WHERE  mv.METAVERSIONID = @MetaVersionId
          AND  mv.TENANTID      = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWMETADATAVERSION
            (METAVERSIONID, DBMODELID, VERSIONLABEL, METASTATUS, SNAPSHOTJSON, DESCRIPTION,
             VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             SOURCETYPE, TENANTID)
        VALUES
            (@MetaVersionId, @DbModelId, @VersionLabel, @MetaStatus, @SnapshotJson, @Description,
             @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
             @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWMETADATAVERSION SET
            VERSIONLABEL  = @VersionLabel,
            METASTATUS    = @MetaStatus,
            SNAPSHOTJSON  = @SnapshotJson,
            DESCRIPTION   = @Description,
            STATUS        = @Status,
            SORTORDER     = @SortOrder,
            MODIFIEDBYID  = @ModifiedById,
            MODIFIEDON    = @ModifiedOn
        WHERE METAVERSIONID = @MetaVersionId AND TENANTID = @TenantId";

    public const string UPDATE_STATUS = @"
        UPDATE SW.MSWMETADATAVERSION SET
            METASTATUS   = @MetaStatus,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE METAVERSIONID = @MetaVersionId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWMETADATAVERSION
        WHERE METAVERSIONID = @MetaVersionId AND TENANTID = @TenantId
          AND STATUS = 1 AND METASTATUS = 0";

    public const string GET_SELECT_LIST = @"SELECT
                                            METAVERSIONID AS MetaVersionId,
                                            VERSIONLABEL
                                        FROM SW.MSWMETADATAVERSION
                                        ORDER BY VERSIONLABEL
                                        OFFSET @firstnumber ROWS
                                        FETCH NEXT @maxresult ROWS ONLY";

    public const string GET_CHANGES = @"
        SELECT c.METAVERSIONID AS MetaVersionId,
               c.TABLENAME     AS TableName,
               c.COLUMNNAME    AS ColumnName,
               c.CHANGETYPE    AS ChangeType,
               c.OLDDEFINITION AS OldValue,
               c.NEWDEFINITION AS NewValue,
               c.TENANTID      AS TenantId
        FROM   SW.MSWMETADATACHANGE c
        WHERE  c.METAVERSIONID = @MetaVersionId
          AND  c.TENANTID      = @TenantId";

    public const string INSERT_CHANGE = @"
        INSERT INTO SW.MSWMETADATACHANGE
            (METAVERSIONID, TABLENAME, COLUMNNAME, CHANGETYPE, OLDDEFINITION, NEWDEFINITION, TENANTID)
        VALUES
            (@MetaVersionId, @TableName, @ColumnName, @ChangeType, @OldValue, @NewValue, @TenantId)";

    public const string DELETE_CHANGE = @"
        DELETE FROM SW.MSWMETADATACHANGE
        WHERE METAVERSIONID = @MetaVersionId
          AND TABLENAME     = @TableName
          AND COLUMNNAME    = @ColumnName
          AND TENANTID      = @TenantId";

    public const string DELETE_CHANGES_BY_VERSION = @"
        DELETE FROM SW.MSWMETADATACHANGE
        WHERE METAVERSIONID = @MetaVersionId AND TENANTID = @TenantId";

    public static (string Sql, DynamicParameters Params) BuildMetadataSyncList(
        MetadataSyncListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("metaversion", "mv");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx,  c.DbModelId,  "metaversion", "DBMODELID"),
            SqlClauses.ExactByte(ctx, c.MetaStatus, "metaversion", "METASTATUS"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   mv.METAVERSIONID AS MetaVersionId,
                   mv.VERSIONLABEL  AS VersionLabel,
                   mv.METASTATUS    AS MetaStatus,
                   mv.DBMODELID     AS DbModelId,
                   mv.TENANTID      AS TenantId
            FROM   SW.MSWMETADATAVERSION mv
            {whereSql}
            {pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }
}