using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DevadminDAL.Query.Replicate
{
    public static class ReplicateQB
    {
        public const string GET_REPLICATE = @"SELECT
                                            REPLICATE.REPLICATEID                  AS ReplicateId,
                                            REPLICATE.DESCRIPTION                  AS ReplicateDescription,
                                            REPLICATE.FROMSERVERID                 AS ReplicateFromServerId,
                                            REPLICATE.FROMDATABASEID              AS ReplicateFromDataBaseId,
                                            REPLICATE.TOSERVERID                   AS ReplicateToServerId,
                                            REPLICATE.TODATABASEID                AS ReplicateToDataBaseId,
                                            REPLICATE.SCHEDULERID                  AS SchedulerId,
                                            REPLICATE.DATASETID                    AS DatasetId,
                                            REPLICATE.ISOVERWRITE                 AS ReplicateIsOverWrite,
                                            REPLICATE.LASTINSERTEDTILL            AS ReplicateLastInsertedTill,
                                            REPLICATE.LASTUPDATEDTILL             AS ReplicateLastUpdatedTill,
                                            REPLICATE.LASTDELETEDTILL             AS ReplicateLastDeletedTill,
                                            REPLICATE.CREATEDBYID                 AS ReplicateCreatedById,
                                            REPLICATE.CREATEDON                   AS ReplicateCreatedOn,
                                            REPLICATE.MODIFIEDBYID                AS ReplicateModifiedById,
                                            REPLICATE.MODIFIEDON                  AS ReplicateModifiedOn,
                                            REPLICATE.SORTORDER                   AS ReplicateSortOrder,
                                            REPLICATE.STATUS                      AS ReplicateStatus,
                                            REPLICATE.VERSION                     AS ReplicateVersion,
                                            REPLICATE.SOURCETYPE                  AS ReplicateSourceType,
                                            REPLICATE.QUERYCONDITION              AS ReplicateQueryCondition

                                        FROM TREPLICATE REPLICATE
                                        WHERE
                                        REPLICATE.REPLICATEID = @replicateid;";

        // ── List/CRUD (tracker §41 — DevAdmin CRUD gap) ──────────────────────
        // Column list mirrors GET_REPLICATE exactly, same table, no filtering beyond STATUS — no
        // TENANTID/CLIENTID column is referenced anywhere in the pre-existing GET_REPLICATE
        // query above, so none is assumed here either (never infer a column that isn't already
        // confirmed via an existing query in this codebase).
        public const string GET_REPLICATE_LIST = @"SELECT
                                            REPLICATE.REPLICATEID                  AS ReplicateId,
                                            REPLICATE.DESCRIPTION                  AS ReplicateDescription,
                                            REPLICATE.FROMSERVERID                 AS ReplicateFromServerId,
                                            REPLICATE.FROMDATABASEID               AS ReplicateFromDataBaseId,
                                            REPLICATE.TOSERVERID                   AS ReplicateToServerId,
                                            REPLICATE.TODATABASEID                 AS ReplicateToDataBaseId,
                                            REPLICATE.SCHEDULERID                  AS SchedulerId,
                                            REPLICATE.DATASETID                    AS DatasetId,
                                            REPLICATE.ISOVERWRITE                  AS ReplicateIsOverWrite,
                                            REPLICATE.LASTINSERTEDTILL             AS ReplicateLastInsertedTill,
                                            REPLICATE.LASTUPDATEDTILL              AS ReplicateLastUpdatedTill,
                                            REPLICATE.LASTDELETEDTILL              AS ReplicateLastDeletedTill,
                                            REPLICATE.SORTORDER                    AS ReplicateSortOrder,
                                            REPLICATE.STATUS                       AS ReplicateStatus
                                        FROM TREPLICATE REPLICATE
                                        WHERE REPLICATE.STATUS = 1
                                        ORDER BY REPLICATE.SORTORDER, REPLICATE.REPLICATEID DESC";

        // TREPLICATEID is a real SQL Server IDENTITY column (this table's own naming/vintage
        // matches TPARTNER/TPARTNERPRODUCT's confirmed-IDENTITY precedent, not the MAUTONUMBER
        // convention modern M-tables use) — insert omits REPLICATEID and returns the generated
        // identity via SCOPE_IDENTITY(), per this codebase's own "Insert returning new identity"
        // convention (no CancellationToken parameter on ExecuteIdentityAsync).
        public const string INSERT_REPLICATE = @"
            INSERT INTO TREPLICATE
                (DESCRIPTION, FROMSERVERID, FROMDATABASEID, TOSERVERID, TODATABASEID, SCHEDULERID,
                 DATASETID, ISOVERWRITE, LASTINSERTEDTILL, LASTUPDATEDTILL, LASTDELETEDTILL,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION,
                 SOURCETYPE, QUERYCONDITION)
            VALUES
                (@ReplicateDescription, @ReplicateFromServerId, @ReplicateFromDataBaseId,
                 @ReplicateToServerId, @ReplicateToDataBaseId, @SchedulerId, @DatasetId,
                 @ReplicateIsOverWrite, @ReplicateLastInsertedTill, @ReplicateLastUpdatedTill,
                 @ReplicateLastDeletedTill, @ReplicateCreatedById, @ReplicateCreatedOn,
                 @ReplicateModifiedById, @ReplicateModifiedOn, @ReplicateSortOrder, @ReplicateStatus,
                 @ReplicateVersion, @ReplicateSourceType, @ReplicateQueryCondition);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

        public const string UPDATE_REPLICATE = @"
            UPDATE TREPLICATE SET
                DESCRIPTION       = @ReplicateDescription,
                FROMSERVERID      = @ReplicateFromServerId,
                FROMDATABASEID    = @ReplicateFromDataBaseId,
                TOSERVERID        = @ReplicateToServerId,
                TODATABASEID      = @ReplicateToDataBaseId,
                SCHEDULERID       = @SchedulerId,
                DATASETID         = @DatasetId,
                ISOVERWRITE       = @ReplicateIsOverWrite,
                LASTINSERTEDTILL  = @ReplicateLastInsertedTill,
                LASTUPDATEDTILL   = @ReplicateLastUpdatedTill,
                LASTDELETEDTILL   = @ReplicateLastDeletedTill,
                MODIFIEDBYID      = @ReplicateModifiedById,
                MODIFIEDON        = @ReplicateModifiedOn,
                SORTORDER         = @ReplicateSortOrder,
                STATUS            = @ReplicateStatus,
                QUERYCONDITION    = @ReplicateQueryCondition
            WHERE REPLICATEID = @ReplicateId";

        // Soft delete — matches this table's own STATUS-flag convention (GET_REPLICATE_LIST
        // already filters STATUS=1); no other module's Replicate row should ever hard-delete a
        // sync-job definition that may have real watermark history worth keeping for audit.
        public const string DELETE_REPLICATE = @"
            UPDATE TREPLICATE SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE REPLICATEID = @ReplicateId";
    }
}