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

namespace SwDAL.Query.DdlScript;

// Required index: IX_MSWDDLSCRIPT_TENANTID ON SW.MSWDDLSCRIPT (TENANTID, STATUS, BRANCHID)
public class DdlScriptQB : IQueryBuilder<DdlScriptListQuery, DdlScriptListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["objectname"]   = "s.OBJECTNAME",
            ["sequence"]     = "s.SEQUENCE",
            ["scriptstatus"] = "s.SCRIPTSTATUS"
        };

    // ── IQueryBuilder implementation ──────────────────────────────────────
    public (string Sql, DynamicParameters Params) Build(DdlScriptListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "s.SEQUENCE ASC, s.DDLSCRIPTID ASC");
        return BuildDdlScriptList(query.Criteria, dialect, orderBy);
    }

    // ── CRUD SQL constants ────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT s.DDLSCRIPTID                         AS DdlScriptId,
               s.DBMODELID                           AS DbModelId,
               m.DBMODELNAME                         AS DbModelName,
               s.BRANCHID                            AS BranchId,
               bs.BRANCHCODE                         AS BranchCode,
               bs.BRANCHNAME                         AS BranchName,
               s.OBJECTGROUPID                       AS ObjectGroupId,
               ob.GROUPCODE                          AS ObjectGroupCode,
               ob.GROUPNAME                          AS ObjectGroupName,
               s.OBJECTTYPE                          AS ObjectType,
               s.SCHEMANAME                          AS SchemaName,
               s.OBJECTNAME                          AS ObjectName,
               s.SQLSCRIPT                           AS SqlScript,
               s.ROLLBACKSQL                         AS RollbackSql,
               s.CHECKSUM                            AS Checksum,
               s.SCRIPTSTATUS                        AS ScriptStatus,
               CAST(s.APPROVALREQUIRED AS BIT)       AS ApprovalRequired,
               s.APPROVEDBYID                        AS ApprovedById,
               s.APPROVEDON                          AS ApprovedOn,
               s.SEQUENCE                            AS Sequence,
               s.VERSION,
               s.STATUS,
               s.SORTORDER                           AS SortOrder,
               s.CREATEDBYID                         AS CreatedById,
               s.CREATEDON                           AS CreatedOn,
               s.MODIFIEDBYID                        AS ModifiedById,   
               s.MODIFIEDON                          AS ModifiedOn,
               s.SOURCETYPE                          AS SourceType,
               s.TENANTID                            AS TenantId,
               s.CHANGEREQUESTID                     AS ChangeRequestId
        FROM   SW.MSWDDLSCRIPT s
        JOIN   SW.MSWDBMODEL m
               ON  m.DBMODELID = s.DBMODELID
        JOIN   SW.MSWSCRIPTBRANCH bs
               ON  bs.BRANCHID = s.BRANCHID
        JOIN   SW.MSWOBJECTGROUP ob
               ON ob.OBJECTGROUPID = s.OBJECTGROUPID
        WHERE  s.DDLSCRIPTID = @DdlScriptId";

    public const string GET_BY_BRANCH = @"
        SELECT s.DDLSCRIPTID                         AS DdlScriptId,
               s.DBMODELID                           AS DbModelId,
               m.DBMODELNAME                         AS DbModelName,
               s.BRANCHID                            AS BranchId,
               bs.BRANCHCODE                         AS BranchCode,
               bs.BRANCHNAME                         AS BranchName,
               s.OBJECTGROUPID                       AS ObjectGroupId,
               ob.GROUPCODE                          AS ObjectGroupCode,
               ob.GROUPNAME                          AS ObjectGroupName,
               s.OBJECTTYPE                          AS ObjectType,
               s.SCHEMANAME                          AS SchemaName,
               s.OBJECTNAME                          AS ObjectName,
               s.SQLSCRIPT                           AS SqlScript,
               s.ROLLBACKSQL                         AS RollbackSql,
               s.CHECKSUM                            AS Checksum,
               s.SCRIPTSTATUS                        AS ScriptStatus,
               CAST(s.APPROVALREQUIRED AS BIT)       AS ApprovalRequired,
               s.APPROVEDBYID                        AS ApprovedById,
               s.APPROVEDON                          AS ApprovedOn,
               s.SEQUENCE                            AS Sequence,
               s.VERSION,
               s.STATUS,
               s.SORTORDER                           AS SortOrder,
               s.CREATEDBYID                         AS CreatedById,
               s.CREATEDON                           AS CreatedOn,
               s.MODIFIEDBYID                        AS ModifiedById,
               s.MODIFIEDON                          AS ModifiedOn,
               s.SOURCETYPE                          AS SourceType,
               s.TENANTID                            AS TenantId,
               s.CHANGEREQUESTID                     AS ChangeRequestId
        FROM   SW.MSWDDLSCRIPT s
        JOIN   SW.MSWDBMODEL m
               ON  m.DBMODELID = s.DBMODELID
        JOIN   SW.MSWSCRIPTBRANCH bs
               ON  bs.BRANCHID = s.BRANCHID
        JOIN   SW.MSWOBJECTGROUP ob
               ON ob.OBJECTGROUPID = s.OBJECTGROUPID
        WHERE  s.BRANCHID  = @BranchId
          AND  s.TENANTID  = @TenantId
        ORDER BY s.SEQUENCE ASC, s.DDLSCRIPTID ASC";

    public const string INSERT = @"
        INSERT INTO SW.MSWDDLSCRIPT
            (DDLSCRIPTID, DBMODELID, BRANCHID, OBJECTGROUPID, OBJECTTYPE, SCHEMANAME,
             OBJECTNAME, SQLSCRIPT, ROLLBACKSQL, CHECKSUM, SCRIPTSTATUS, APPROVALREQUIRED,
             APPROVEDBYID, APPROVEDON, SEQUENCE,
             VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID,CHANGEREQUESTID)
        VALUES
            (@DdlScriptId, @DbModelId, @BranchId, @ObjectGroupId, @ObjectType, @SchemaName,
             @ObjectName, @SqlScript, @RollbackSql, @Checksum, @ScriptStatus, @ApprovalRequired,
             @ApprovedById, @ApprovedOn, @Sequence,
             @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId,@ChangeRequestId)";

    public const string UPDATE = @"
        UPDATE SW.MSWDDLSCRIPT SET
            OBJECTTYPE       = @ObjectType,
            SCHEMANAME       = @SchemaName,
            OBJECTNAME       = @ObjectName,
            SQLSCRIPT        = @SqlScript,
            ROLLBACKSQL      = @RollbackSql,
            CHECKSUM         = @Checksum,
            SEQUENCE         = @Sequence,
            SCRIPTSTATUS     = 0,
            APPROVEDBYID     = -1,
            STATUS           = @Status,
            SORTORDER        = @SortOrder,
            MODIFIEDBYID     = @ModifiedById,
            MODIFIEDON       = @ModifiedOn,
            CHANGEREQUESTID  =@ChangeRequestId
        WHERE DDLSCRIPTID = @DdlScriptId AND TENANTID = @TenantId";

    public const string UPDATE_STATUS = @"
        UPDATE SW.MSWDDLSCRIPT SET
            SCRIPTSTATUS = @ScriptStatus,
            APPROVEDBYID = @ApprovedById,
            APPROVEDON   = @ApprovedOn,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE DDLSCRIPTID          = @DdlScriptId
          AND SCRIPTSTATUS         = @RequiredCurrentStatus";

    public const string DELETE = @"
        DELETE FROM SW.MSWDDLSCRIPT
        WHERE DDLSCRIPTID  = @DdlScriptId
          AND TENANTID     = @TenantId
          AND SCRIPTSTATUS = 0";

    public const string CHECK_IN_EXECUTIONS = @"
        SELECT COUNT(1)
        FROM   SW.LSWDDLEXECUTION
        WHERE  DDLSCRIPTID = @DdlScriptId";

    // Tracker §37 Decision 2 — how many of a ClientProvisioning CR's own linked scripts have
    // already had an execution attempt logged (success or failure — a script's own eventual
    // success/failure detail already lives in LSWDDLEXECUTION.EXECSTATUS, this is purely a
    // "how far through the batch are we" count). DISTINCT since ExecuteDdlCr logs one row per
    // script per run — never re-run for an already-Executed CR, but defensive regardless.
    public const string COUNT_EXECUTIONS_FOR_SCRIPTS = @"
        SELECT COUNT(DISTINCT DDLSCRIPTID)
        FROM   SW.LSWDDLEXECUTION
        WHERE  CLIENTDBID = @ClientDbId
          AND  DDLSCRIPTID IN @DdlScriptIds";

    public const string GET_SELECT_LIST = @"SELECT
                                            DDLSCRIPTID AS DdlScriptId,
                                            OBJECTNAME
                                        FROM SW.MSWDDLSCRIPT
                                        WHERE STATUS = 1
                                        ORDER BY OBJECTNAME
                                        OFFSET @firstnumber ROWS
                                        FETCH NEXT @maxresult ROWS ONLY;";

    public const string LOG_INSERT = @"
        INSERT INTO SW.LSWDDLEXECUTION
            (DDLSCRIPTID, CLIENTDBID, EXECSTATUS, ERRORMESSAGE, EXECUTEDON, CREATEDBYID, CREATEDON, TENANTID)
        VALUES
            (@DdlScriptId, @ClientDbId, @ExecStatus, @ErrorMessage, @ExecutedOn, @CreatedById, @CreatedOn, @TenantId)";

    // ── List builder ──────────────────────────────────────────────────────

    public static (string Sql, DynamicParameters Params) BuildDdlScriptList(
        DdlScriptListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("script", "s");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx, c.BranchId,     "script", "BRANCHID"),
            SqlClauses.ExactInt(ctx, c.DbModelId,    "script", "DBMODELID"),
            SqlClauses.ExactByte(ctx, c.ScriptStatus, "script", "SCRIPTSTATUS"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   s.DDLSCRIPTID  AS DdlScriptId,
                   s.OBJECTNAME   AS ObjectName,
                   s.SCHEMANAME   AS SchemaName,
                   s.SCRIPTSTATUS AS ScriptStatus,
                   s.BRANCHID     AS BranchId,
                   s.TENANTID     AS TenantId
            FROM   SW.MSWDDLSCRIPT s
            {whereSql}
            {pagingSql}";

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