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

namespace SwDAL.Query.UpgradePackage;

// Required index: IX_MSWUPGRADEPACKAGE_TENANTID ON SW.MSWUPGRADEPACKAGE (TENANTID, STATUS, DBMODELID)
public class UpgradePackageQB : IQueryBuilder<UpgradePackageListQuery, UpgradePackageListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        { ["packagename"] = "p.PACKAGENAME", ["packagecode"] = "p.PACKAGECODE" };

    public (string Sql, DynamicParameters Params) Build(UpgradePackageListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "p.PACKAGEID DESC");
        return BuildUpgradePackageList(query.Criteria, dialect, orderBy);
    }

    public const string GET_BY_ID = @"
        SELECT p.PACKAGEID      AS PackageId,
               p.DBMODELID      AS DbModelId,
               m.DBMODELNAME    AS DbModelName,
               p.PACKAGENAME    AS PackageName,
               p.PACKAGECODE    AS PackageCode,
               p.PKGSTATUS      AS PkgStatus,
               p.DESCRIPTION    AS Description,
               p.SEQUENCEINCHAIN     AS SequenceInChain,
               p.SUPERSEDESPACKAGEID AS SupersedesPackageId,
               p.SCOPETYPE           AS ScopeType,
               p.SCOPEVALUE          AS ScopeValue,
               p.RELEASEVERSION      AS ReleaseVersion,
               p.ROLLOUTTYPE         AS RolloutType,
               p.ROLLOUTPERCENT      AS RolloutPercent,
               p.PACKAGECONTENTTYPE  AS PackageContentType,
               p.REQUIREDFEATURECODE AS RequiredFeatureCode,
               p.VERSION,
               p.STATUS,
               p.SORTORDER      AS SortOrder,
               p.CREATEDBYID    AS CreatedById,
               p.CREATEDON      AS CreatedOn,
               p.MODIFIEDBYID   AS ModifiedById,
               p.MODIFIEDON     AS ModifiedOn,
               p.SOURCETYPE     AS SourceType,
               p.TENANTID       AS TenantId
        FROM   SW.MSWUPGRADEPACKAGE p
        INNER JOIN SW.MSWDBMODEL m ON m.DBMODELID = p.DBMODELID
        WHERE  p.PACKAGEID = @PackageId";

    // Resolves one DbModel's real provisioning chain: every Released, Active Universal
    // package plus any Released, Active overlay package whose SCOPETYPE/SCOPEVALUE matches
    // the caller's filter — restricted, per chain, to the package nobody supersedes (the
    // NOT EXISTS clause), i.e. always the latest version. A null filter parameter can never
    // equal a bound SCOPEVALUE, so omitted filter dimensions simply exclude that scope's
    // overlay packages, not error.
    public const string GET_PROVISIONING_CHAIN = @"
        SELECT p.PACKAGEID       AS PackageId,
               p.PACKAGECODE     AS PackageCode,
               p.PACKAGENAME     AS PackageName,
               p.SEQUENCEINCHAIN AS SequenceInChain,
               p.SCOPETYPE       AS ScopeType,
               p.ROLLOUTTYPE     AS RolloutType,
               p.ROLLOUTPERCENT  AS RolloutPercent,
               p.PACKAGECONTENTTYPE  AS PackageContentType,
               p.REQUIREDFEATURECODE AS RequiredFeatureCode
        FROM   SW.MSWUPGRADEPACKAGE p
        WHERE  p.DBMODELID = @DbModelId
          AND  p.STATUS    = 1
          AND  p.PKGSTATUS = 2
          AND  NOT EXISTS (
                   SELECT 1 FROM SW.MSWUPGRADEPACKAGE newer
                   WHERE  newer.SUPERSEDESPACKAGEID = p.PACKAGEID
                     AND  newer.STATUS    = 1
                     AND  newer.PKGSTATUS = 2
               )
          AND (
                   p.SCOPETYPE = 0
                OR (p.SCOPETYPE = 1 AND p.SCOPEVALUE = @PartnerProductCode)
                OR (p.SCOPETYPE = 2 AND p.SCOPEVALUE = @IndustryCode)
                OR (p.SCOPETYPE = 3 AND p.SCOPEVALUE = @CountryCode)
                OR (p.SCOPETYPE = 4 AND p.SCOPEVALUE = @ClientCode)
              )
        ORDER BY p.SCOPETYPE, p.SEQUENCEINCHAIN, p.PACKAGEID";

    // Whole-DbModel lineage fetch for the "walk SupersedesPackageId to nearest ReleaseVersion"
    // resolution (SwBLL.Provisioning.ProvisioningBLL.GetCurrentVersion). Deliberately NO
    // STATUS/PKGSTATUS/scope filter — an ancestor package may since have been Deprecated or
    // gone Inactive but its ReleaseVersion must still resolve if it's the nearest one; filtering
    // it out here would silently break the walk.
    // TENANTID allows an exact match OR the shared platform sentinel (-1) — same bug class as
    // GET_DDL_LINES below (its own comment documents the original discovery): a baseline
    // UpgradePackage row is commonly a SHARED platform template (TENANTID = -1, one package
    // referenced by many clients' provisioning runs), so requiring an exact tenant match meant
    // GetLineage could never resolve a shared template package's RELEASEVERSION for any tenant
    // other than the template's own authoring tenant — reproduced live on GB5DEMO, where a
    // fully-applied, versioned package was resolved by GET_MOST_RECENTLY_APPLIED_PACKAGE but
    // then silently dropped out of the lineage walk here, reporting "unversioned" incorrectly.
    public const string GET_LINEAGE = @"
        SELECT p.PACKAGEID           AS PackageId,
               p.PACKAGECODE         AS PackageCode,
               p.RELEASEVERSION      AS ReleaseVersion,
               p.SUPERSEDESPACKAGEID AS SupersedesPackageId
        FROM   SW.MSWUPGRADEPACKAGE p
        WHERE  p.DBMODELID = @DbModelId AND (p.TENANTID = @TenantId OR p.TENANTID = -1)";

    // TENANTID allows an exact match OR the shared platform sentinel (-1) — confirmed live
    // (tracker §31.14), same bug class as ClientDatabaseQB's fix: a baseline UpgradePackage's
    // own DDL links are commonly SHARED platform rows (TENANTID = -1, one package referenced by
    // many clients' provisioning runs), so requiring an exact tenant match meant no real
    // tenant-scoped caller could ever retrieve a shared baseline package's DDL lines at all.
    public const string GET_DDL_LINES = @"
        SELECT d.PACKAGEDDLID  AS PackageDdlId,
               d.PACKAGEID     AS PackageId,
               d.DDLSCRIPTID   AS DdlScriptId,
               s.OBJECTNAME    AS ObjectName,
               d.SEQUENCE      AS Sequence,
               d.TENANTID      AS TenantId
        FROM   SW.MSWUPGRADEPACKAGEDDL d
        INNER JOIN SW.MSWDDLSCRIPT s ON s.DDLSCRIPTID = d.DDLSCRIPTID AND (s.TENANTID = d.TENANTID OR s.TENANTID = -1)
        WHERE  d.PACKAGEID = @PackageId
          AND  (d.TENANTID = @TenantId OR d.TENANTID = -1)
        ORDER BY d.SEQUENCE, d.PACKAGEDDLID";

    public const string GET_META_LINES = @"
        SELECT mm.PACKAGEMETAID  AS PackageMetaId,
               mm.PACKAGEID      AS PackageId,
               mm.METAVERSIONID  AS MetaVersionId,
               mv.VERSIONLABEL   AS VersionLabel,
               mm.TENANTID       AS TenantId
        FROM   SW.MSWUPGRADEPACKAGEMETA mm
        INNER JOIN SW.MSWMETADATAVERSION mv ON mv.METAVERSIONID = mm.METAVERSIONID AND mv.TENANTID = mm.TENANTID
        WHERE  mm.PACKAGEID = @PackageId
          AND  mm.TENANTID  = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWUPGRADEPACKAGE
            (PACKAGEID, DBMODELID, PACKAGENAME, PACKAGECODE, PKGSTATUS, DESCRIPTION,
             SEQUENCEINCHAIN, SUPERSEDESPACKAGEID, SCOPETYPE, SCOPEVALUE, RELEASEVERSION,
             ROLLOUTTYPE, ROLLOUTPERCENT, PACKAGECONTENTTYPE, REQUIREDFEATURECODE,
             VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             SOURCETYPE, TENANTID)
        VALUES
            (@PackageId, @DbModelId, @PackageName, @PackageCode, @PkgStatus, @Description,
             @SequenceInChain, @SupersedesPackageId, @ScopeType, @ScopeValue, @ReleaseVersion,
             @RolloutType, @RolloutPercent, @PackageContentType, @RequiredFeatureCode,
             @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
             @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWUPGRADEPACKAGE SET
            PACKAGENAME          = @PackageName,
            PACKAGECODE          = @PackageCode,
            PKGSTATUS            = @PkgStatus,
            DESCRIPTION          = @Description,
            SEQUENCEINCHAIN      = @SequenceInChain,
            SUPERSEDESPACKAGEID  = @SupersedesPackageId,
            SCOPETYPE            = @ScopeType,
            SCOPEVALUE           = @ScopeValue,
            RELEASEVERSION       = @ReleaseVersion,
            ROLLOUTTYPE          = @RolloutType,
            ROLLOUTPERCENT       = @RolloutPercent,
            PACKAGECONTENTTYPE   = @PackageContentType,
            REQUIREDFEATURECODE  = @RequiredFeatureCode,
            STATUS               = @Status,
            SORTORDER            = @SortOrder,
            MODIFIEDBYID         = @ModifiedById,
            MODIFIEDON           = @ModifiedOn
        WHERE PACKAGEID = @PackageId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWUPGRADEPACKAGE
        WHERE PACKAGEID = @PackageId AND TENANTID = @TenantId
          AND STATUS = 1 AND PKGSTATUS = 0";

    public const string INSERT_DDL_LINE = @"
        INSERT INTO SW.MSWUPGRADEPACKAGEDDL
            (PACKAGEDDLID, PACKAGEID, DDLSCRIPTID, SEQUENCE, TENANTID)
        VALUES
            (@PackageDdlId, @PackageId, @DdlScriptId, @Sequence, @TenantId)";

    public const string DELETE_DDL_LINES = @"
        DELETE FROM SW.MSWUPGRADEPACKAGEDDL
        WHERE PACKAGEID = @PackageId AND TENANTID = @TenantId";

    public const string INSERT_META_LINE = @"
        INSERT INTO SW.MSWUPGRADEPACKAGEMETA
            (PACKAGEMETAID, PACKAGEID, METAVERSIONID, TENANTID)
        VALUES
            (@PackageMetaId, @PackageId, @MetaVersionId, @TenantId)";

    public const string DELETE_META_LINES = @"
        DELETE FROM SW.MSWUPGRADEPACKAGEMETA
        WHERE PACKAGEID = @PackageId AND TENANTID = @TenantId";

    public const string GET_SELECT_LIST = @"SELECT
                                            PACKAGEID   AS PackageId,
                                            PACKAGENAME AS PackageName
                                        FROM SW.MSWUPGRADEPACKAGE
                                        WHERE STATUS = 1
                                        ORDER BY PACKAGENAME
                                        OFFSET @firstnumber ROWS
                                        FETCH NEXT @maxresult ROWS ONLY";

    public static (string Sql, DynamicParameters Params) BuildUpgradePackageList(
        UpgradePackageListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("upgradepackage", "p");

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

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   p.PACKAGEID    AS PackageId,
                   p.PACKAGENAME  AS PackageName,
                   p.PACKAGECODE  AS PackageCode,
                   p.PKGSTATUS    AS PkgStatus,
                   p.DBMODELID    AS DbModelId,
                   p.TENANTID     AS TenantId
            FROM   SW.MSWUPGRADEPACKAGE p
            {whereSql}
            {pagingSql}";

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