namespace EntitlementDAL.QueryBuilders;

// Thread 3 (Demo/DemoDB, tracker §49). Required indexes:
//   MDEMOPOOLINSTANCE  IX (DEMOVARIANTID, POOLSTATE)
//   TDEMOSESSION       IX (SESSIONSTATUS, EXPIRESON)
//   LDEMOSESSIONEVENT  IX (DEMOSESSIONID)
public static class DemoQB
{
    // ── MDEMOVARIANT ────────────────────────────────────────────────────────

    private const string VARIANT_COLUMNS = @"
       DEMOVARIANTID    AS DemoVariantId,
       VARIANTCODE      AS VariantCode,
       VARIANTNAME      AS VariantName,
       PARTNERPRODUCTID AS PartnerProductId,
       PLANID           AS PlanId,
       INDUSTRYCODE     AS IndustryCode,
       GEOGRAPHYCODE    AS GeographyCode,
       PERIODTAG        AS PeriodTag,
       FEATUREID        AS FeatureId,
       AVAILABLEDEMOROLECODES AS AvailableDemoRoleCodes,
       DBMODELID        AS DbModelId,
       SEEDPACKAGEID    AS SeedPackageId,
       MUTABLETABLELIST AS MutableTableList,
       POOLTARGETSIZE   AS PoolTargetSize,
       VERSION          AS Version,
       STATUS           AS Status,
       SORTORDER        AS SortOrder,
       CREATEDBYID      AS CreatedById,
       CREATEDON        AS CreatedOn,
       MODIFIEDBYID     AS ModifiedById,
       MODIFIEDON       AS ModifiedOn,
       SOURCETYPE       AS SourceType,
       TENANTID         AS TenantId";

    public const string VARIANT_GET_BY_ID = "SELECT" + VARIANT_COLUMNS + @"
FROM   MDEMOVARIANT
WHERE  DEMOVARIANTID = @DemoVariantId";

    public const string VARIANT_GET_LIST = "SELECT" + VARIANT_COLUMNS + @"
FROM   MDEMOVARIANT
WHERE  STATUS = 1
ORDER BY VARIANTCODE";

    public const string VARIANT_INSERT = @"
INSERT INTO MDEMOVARIANT
    (DEMOVARIANTID, VARIANTCODE, VARIANTNAME, PARTNERPRODUCTID, PLANID, INDUSTRYCODE, GEOGRAPHYCODE, PERIODTAG,
     FEATUREID, AVAILABLEDEMOROLECODES, DBMODELID, SEEDPACKAGEID, MUTABLETABLELIST, POOLTARGETSIZE,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@DemoVariantId, @VariantCode, @VariantName, @PartnerProductId, @PlanId, @IndustryCode, @GeographyCode, @PeriodTag,
     @FeatureId, @AvailableDemoRoleCodes, @DbModelId, @SeedPackageId, @MutableTableList, @PoolTargetSize,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    // Editable fields only — DemoVariantId/CreatedById/CreatedOn/SourceType/TenantId never change
    // once a variant exists, same convention as every other entity's own Update statement in this
    // engagement (e.g. UpgradePackageQB's immutable-once-Released fields are simply omitted here).
    public const string VARIANT_UPDATE = @"
UPDATE MDEMOVARIANT SET
    VARIANTNAME            = @VariantName,
    PARTNERPRODUCTID       = @PartnerProductId,
    PLANID                 = @PlanId,
    INDUSTRYCODE           = @IndustryCode,
    GEOGRAPHYCODE          = @GeographyCode,
    PERIODTAG              = @PeriodTag,
    FEATUREID              = @FeatureId,
    AVAILABLEDEMOROLECODES = @AvailableDemoRoleCodes,
    DBMODELID              = @DbModelId,
    SEEDPACKAGEID          = @SeedPackageId,
    MUTABLETABLELIST       = @MutableTableList,
    POOLTARGETSIZE         = @PoolTargetSize,
    MODIFIEDBYID           = @ModifiedById,
    MODIFIEDON             = @ModifiedOn
WHERE  DEMOVARIANTID = @DemoVariantId";

    // ── MDEMOPOOLINSTANCE ───────────────────────────────────────────────────

    private const string POOL_INSTANCE_COLUMNS = @"
       DEMOPOOLINSTANCEID AS DemoPoolInstanceId,
       DEMOVARIANTID      AS DemoVariantId,
       SWCLIENTDATABASEID AS SwClientDatabaseId,
       LOCALCLIENTID      AS LocalClientId,
       POOLSTATE          AS PoolState,
       VERSION            AS Version,
       STATUS             AS Status,
       SORTORDER          AS SortOrder,
       CREATEDBYID        AS CreatedById,
       CREATEDON          AS CreatedOn,
       MODIFIEDBYID       AS ModifiedById,
       MODIFIEDON         AS ModifiedOn,
       SOURCETYPE         AS SourceType,
       TENANTID           AS TenantId";

    public const string POOL_INSTANCE_GET_BY_ID = "SELECT" + POOL_INSTANCE_COLUMNS + @"
FROM   MDEMOPOOLINSTANCE
WHERE  DEMOPOOLINSTANCEID = @DemoPoolInstanceId";

    public const string POOL_INSTANCE_GET_BY_VARIANT = "SELECT" + POOL_INSTANCE_COLUMNS + @"
FROM   MDEMOPOOLINSTANCE
WHERE  DEMOVARIANTID = @DemoVariantId AND STATUS = 1
ORDER BY DEMOPOOLINSTANCEID";

    // One available instance for this variant, deterministic ordering (oldest-registered first).
    // Real cross-request atomicity comes from the caller wrapping checkout in a transaction and
    // re-checking PoolState before committing — mirrored on CheckOutPooledSessionAsync, same
    // discipline every other checkout-shaped BLL method in this engagement already follows.
    public const string POOL_INSTANCE_GET_ONE_AVAILABLE = "SELECT TOP 1" + POOL_INSTANCE_COLUMNS + @"
FROM   MDEMOPOOLINSTANCE
WHERE  DEMOVARIANTID = @DemoVariantId AND POOLSTATE = 0 AND STATUS = 1
ORDER BY DEMOPOOLINSTANCEID";

    public const string POOL_INSTANCE_INSERT = @"
INSERT INTO MDEMOPOOLINSTANCE
    (DEMOPOOLINSTANCEID, DEMOVARIANTID, SWCLIENTDATABASEID, LOCALCLIENTID, POOLSTATE,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@DemoPoolInstanceId, @DemoVariantId, @SwClientDatabaseId, @LocalClientId, @PoolState,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    // Editable fields only — SwClientDatabaseId/LocalClientId, i.e. correcting which physical
    // database this row points at. Deliberately does NOT touch DemoVariantId (a pool instance
    // isn't reassigned between variants — delete/re-register instead) or PoolState (that has its
    // own dedicated POOL_INSTANCE_UPDATE_STATE/POOL_INSTANCE_TRY_CLAIM methods already).
    public const string POOL_INSTANCE_UPDATE = @"
UPDATE MDEMOPOOLINSTANCE SET
    SWCLIENTDATABASEID = @SwClientDatabaseId,
    LOCALCLIENTID       = @LocalClientId,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn
WHERE  DEMOPOOLINSTANCEID = @DemoPoolInstanceId";

    public const string POOL_INSTANCE_UPDATE_STATE = @"
UPDATE MDEMOPOOLINSTANCE SET
    POOLSTATE    = @PoolState,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  DEMOPOOLINSTANCEID = @DemoPoolInstanceId";

    // Optimistic guard used by checkout — only actually claims the instance if it's still
    // Available at the moment of the UPDATE, closing the race window between the SELECT above
    // and this write without needing a hold-open row lock.
    public const string POOL_INSTANCE_TRY_CLAIM = @"
UPDATE MDEMOPOOLINSTANCE SET
    POOLSTATE    = 1, -- CheckedOut
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  DEMOPOOLINSTANCEID = @DemoPoolInstanceId AND POOLSTATE = 0";

    // ── TDEMOSESSION ────────────────────────────────────────────────────────

    private const string SESSION_COLUMNS = @"
       DEMOSESSIONID         AS DemoSessionId,
       DEMOVARIANTID         AS DemoVariantId,
       DEMOMODE              AS DemoMode,
       DEMOPOOLINSTANCEID    AS DemoPoolInstanceId,
       CLIENTID              AS ClientId,
       POOLEDDEMOUSERID      AS PooledDemoUserId,
       LEADID                AS LeadId,
       OWNERUSERID           AS OwnerUserId,
       DEMOROLECODE          AS DemoRoleCode,
       STARTEDON             AS StartedOn,
       EXPIRESON             AS ExpiresOn,
       EXTENSIONREQUESTEDON  AS ExtensionRequestedOn,
       EXTENSIONAPPROVEDBYID AS ExtensionApprovedById,
       EXTENSIONAPPROVEDON   AS ExtensionApprovedOn,
       EXTENSIONCOMMENT      AS ExtensionComment,
       SESSIONSTATUS         AS SessionStatus,
       VERSION               AS Version,
       STATUS                AS Status,
       SORTORDER             AS SortOrder,
       CREATEDBYID           AS CreatedById,
       CREATEDON             AS CreatedOn,
       MODIFIEDBYID          AS ModifiedById,
       MODIFIEDON            AS ModifiedOn,
       SOURCETYPE            AS SourceType,
       TENANTID              AS TenantId";

    public const string SESSION_GET_BY_ID = "SELECT" + SESSION_COLUMNS + @"
FROM   TDEMOSESSION
WHERE  DEMOSESSIONID = @DemoSessionId";

    public const string SESSION_GET_LIST_BY_OWNER = "SELECT" + SESSION_COLUMNS + @"
FROM   TDEMOSESSION
WHERE  OWNERUSERID = @OwnerUserId AND STATUS = 1
ORDER BY STARTEDON DESC";

    // Non-terminal sessions whose ExpiresOn has already passed a warning threshold but no
    // warning has been issued yet for the CURRENT expiry (a session extended after a warning
    // reverts to Active/Extended, so it's eligible to warn again for its new ExpiresOn).
    public const string SESSION_GET_APPROACHING_EXPIRY = "SELECT" + SESSION_COLUMNS + @"
FROM   TDEMOSESSION
WHERE  SESSIONSTATUS IN (0, 2) -- Active, Extended
  AND  EXPIRESON <= @WarningThreshold";

    public const string SESSION_GET_EXPIRED = "SELECT" + SESSION_COLUMNS + @"
FROM   TDEMOSESSION
WHERE  SESSIONSTATUS IN (0, 2, 3) -- Active, Extended, WarningIssued (a PendingApproval session
                                   -- is deliberately NOT swept here — see Decision 5's own open
                                   -- ""approve before or after expiry"" question, not resolved yet;
                                   -- for now a pending request simply blocks auto-expiry)
  AND  EXPIRESON <= @Now";

    public const string SESSION_INSERT = @"
INSERT INTO TDEMOSESSION
    (DEMOSESSIONID, DEMOVARIANTID, DEMOMODE, DEMOPOOLINSTANCEID, CLIENTID, POOLEDDEMOUSERID, LEADID,
     OWNERUSERID, DEMOROLECODE, STARTEDON, EXPIRESON, SESSIONSTATUS,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@DemoSessionId, @DemoVariantId, @DemoMode, @DemoPoolInstanceId, @ClientId, @PooledDemoUserId, @LeadId,
     @OwnerUserId, @DemoRoleCode, @StartedOn, @ExpiresOn, @SessionStatus,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string SESSION_REQUEST_EXTENSION = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS        = 1, -- PendingApproval
    EXTENSIONREQUESTEDON = @ExtensionRequestedOn,
    EXTENSIONCOMMENT     = @ExtensionComment,
    MODIFIEDBYID         = @ModifiedById,
    MODIFIEDON           = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId
  AND  SESSIONSTATUS IN (0, 2, 3)"; // Active, Extended, WarningIssued only

    public const string SESSION_APPROVE_EXTENSION = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS         = 2, -- Extended
    EXPIRESON             = @ExpiresOn,
    EXTENSIONAPPROVEDBYID = @ExtensionApprovedById,
    EXTENSIONAPPROVEDON   = @ExtensionApprovedOn,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId AND SESSIONSTATUS = 1"; // must still be PendingApproval

    public const string SESSION_REJECT_EXTENSION = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS         = 0, -- back to Active — the original ExpiresOn is untouched
    EXTENSIONAPPROVEDBYID = @ExtensionApprovedById,
    EXTENSIONAPPROVEDON   = @ExtensionApprovedOn,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId AND SESSIONSTATUS = 1";

    public const string SESSION_MARK_WARNING_ISSUED = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS = 3, -- WarningIssued
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId AND SESSIONSTATUS IN (0, 2)";

    public const string SESSION_MARK_EXPIRED = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS = 4, -- Expired
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId";

    public const string SESSION_MARK_TORN_DOWN = @"
UPDATE TDEMOSESSION SET
    SESSIONSTATUS = 5, -- TornDown
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn
WHERE  DEMOSESSIONID = @DemoSessionId";

    // ── LDEMOSESSIONEVENT ───────────────────────────────────────────────────

    public const string EVENT_INSERT = @"
INSERT INTO LDEMOSESSIONEVENT
    (DEMOSESSIONEVENTID, DEMOSESSIONID, EVENTTYPE, EVENTON, EVENTCOMMENT, CREATEDBYID, CREATEDON, TENANTID)
VALUES
    (@DemoSessionEventId, @DemoSessionId, @EventType, @EventOn, @EventComment, @CreatedById, @CreatedOn, @TenantId)";

    public const string EVENT_GET_FOR_SESSION = @"
SELECT DEMOSESSIONEVENTID AS DemoSessionEventId,
       DEMOSESSIONID      AS DemoSessionId,
       EVENTTYPE          AS EventType,
       EVENTON            AS EventOn,
       EVENTCOMMENT       AS EventComment,
       CREATEDBYID        AS CreatedById,
       CREATEDON          AS CreatedOn,
       TENANTID           AS TenantId
FROM   LDEMOSESSIONEVENT
WHERE  DEMOSESSIONID = @DemoSessionId
ORDER BY EVENTON";

    // ── MENTITLEMENTDEMOPREFERENCE (tracker §49 Decision 9) ────────────────────

    private const string PREFERENCE_COLUMNS = @"
       CLIENTID                   AS ClientId,
       PREFERREDPARTNERPRODUCTID  AS PreferredPartnerProductId,
       PREFERREDPLANID            AS PreferredPlanId,
       PREFERREDINDUSTRYCODE      AS PreferredIndustryCode,
       PREFERREDGEOGRAPHYCODE     AS PreferredGeographyCode,
       PREFERREDFEATUREID         AS PreferredFeatureId,
       REQUESTEDON                AS RequestedOn,
       VERSION                    AS Version,
       STATUS                     AS Status,
       SORTORDER                  AS SortOrder,
       CREATEDBYID                AS CreatedById,
       CREATEDON                  AS CreatedOn,
       MODIFIEDBYID               AS ModifiedById,
       MODIFIEDON                 AS ModifiedOn,
       SOURCETYPE                 AS SourceType,
       TENANTID                   AS TenantId";

    public const string PREFERENCE_GET_BY_CLIENT = "SELECT" + PREFERENCE_COLUMNS + @"
FROM   MENTITLEMENTDEMOPREFERENCE
WHERE  CLIENTID = @ClientId";

    public const string PREFERENCE_INSERT = @"
INSERT INTO MENTITLEMENTDEMOPREFERENCE
    (CLIENTID, PREFERREDPARTNERPRODUCTID, PREFERREDPLANID, PREFERREDINDUSTRYCODE, PREFERREDGEOGRAPHYCODE,
     PREFERREDFEATUREID, REQUESTEDON, VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@ClientId, @PreferredPartnerProductId, @PreferredPlanId, @PreferredIndustryCode, @PreferredGeographyCode,
     @PreferredFeatureId, @RequestedOn, @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";
}