namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTSUBSCRIPTION: UX (CLIENTID) — one row per client
public static class SubscriptionQB
{
    private const string COLUMNS = @"
       SUBSCRIPTIONID     AS SubscriptionId,
       CLIENTID           AS ClientId,
       PLANID             AS PlanId,
       IDMSENGAGEMENTREF  AS IdmsEngagementRef,
       SUBSCRIPTIONSTATUS AS SubscriptionStatus,
       TRIALFLAG          AS TrialFlag,
       TRIALENDDATE       AS TrialEndDate,
       STARTDATE          AS StartDate,
       LICENSEVALIDTILL   AS LicenseValidTill,
       SUPPORTVALIDTILL   AS SupportValidTill,
       HOSTINGVALIDTILL   AS HostingValidTill,
       GRACEPERIODDAYS    AS GracePeriodDays,
       AUTORENEW          AS AutoRenew,
       REMARKS            AS Remarks,
       PAYORDERID         AS PayOrderId,
       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 GET_BY_ID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    public const string GET_BY_CLIENT = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  CLIENTID = @ClientId";

    // Entitlement↔Payment loose coupling (§9.1) — the Dapr subscriber/reconciliation job only
    // ever learns a bare PayOrderId (PAY's own event payload carries no SourceDocId), so this
    // is the join back to "which subscription is this."
    public const string GET_BY_PAYORDERID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  PAYORDERID = @PayOrderId";

    public const string GET_BY_ENGAGEMENT_REF = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  IDMSENGAGEMENTREF = @IdmsEngagementRef
  AND  IDMSENGAGEMENTREF != ''";

    // Filters: @Status (-1 = any), @PlanId (-1 = any), @ClientId (-1 = any) — paged via QueryPagedAsync
    public const string GET_LIST_PAGED = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  (@SubscriptionStatus = -1 OR SUBSCRIPTIONSTATUS = @SubscriptionStatus)
  AND  (@PlanId  = -1 OR PLANID   = @PlanId)
  AND  (@ClientId = -1 OR CLIENTID = @ClientId)
ORDER BY SUBSCRIPTIONID
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTSUBSCRIPTION
    (SUBSCRIPTIONID, CLIENTID, PLANID, IDMSENGAGEMENTREF, SUBSCRIPTIONSTATUS, TRIALFLAG, TRIALENDDATE,
     STARTDATE, LICENSEVALIDTILL, SUPPORTVALIDTILL, HOSTINGVALIDTILL, GRACEPERIODDAYS, AUTORENEW, REMARKS, PAYORDERID,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@SubscriptionId, @ClientId, @PlanId, @IdmsEngagementRef, @SubscriptionStatus, @TrialFlag, @TrialEndDate,
     @StartDate, @LicenseValidTill, @SupportValidTill, @HostingValidTill, @GracePeriodDays, @AutoRenew, @Remarks, @PayOrderId,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE = @"
UPDATE MENTITLEMENTSUBSCRIPTION SET
    PLANID             = @PlanId,
    SUBSCRIPTIONSTATUS = @SubscriptionStatus,
    TRIALFLAG          = @TrialFlag,
    TRIALENDDATE       = @TrialEndDate,
    STARTDATE          = @StartDate,
    LICENSEVALIDTILL   = @LicenseValidTill,
    SUPPORTVALIDTILL   = @SupportValidTill,
    HOSTINGVALIDTILL   = @HostingValidTill,
    GRACEPERIODDAYS    = @GracePeriodDays,
    AUTORENEW          = @AutoRenew,
    REMARKS            = @Remarks,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    public const string UPDATE_PLAN = @"
UPDATE MENTITLEMENTSUBSCRIPTION SET
    PLANID       = @PlanId,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    public const string UPDATE_RENEWAL = @"
UPDATE MENTITLEMENTSUBSCRIPTION SET
    LICENSEVALIDTILL = @LicenseValidTill,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    public const string UPDATE_STATUS = @"
UPDATE MENTITLEMENTSUBSCRIPTION SET
    SUBSCRIPTIONSTATUS = @SubscriptionStatus,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    // Entitlement↔Payment loose coupling (§9.1) — records which PAY TPAYORDER is currently
    // outstanding/most-recent for this subscription. Called right after InitiatePayOrder
    // succeeds, and read back by both the Dapr subscriber and the reconciliation job.
    public const string UPDATE_PAYORDER = @"
UPDATE MENTITLEMENTSUBSCRIPTION SET
    PAYORDERID   = @PayOrderId,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  SUBSCRIPTIONID = @SubscriptionId";

    // Reconciliation job's own scan (§9.1 item 4) — every subscription still Pending with a
    // real outstanding PayOrderId, across all tenants (a background job, not a per-tenant call).
    public const string GET_PENDING_WITH_PAYORDER = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTSUBSCRIPTION
WHERE  SUBSCRIPTIONSTATUS = 0
  AND  PAYORDERID IS NOT NULL";

    // Keeps MCLIENT.SUBSCRIPTIONSTATUS (added in the Thread 0 additive extension, for fast
    // tenant-status filtering) in sync — previously added but never written by any code.
    public const string UPDATE_CLIENT_SUBSCRIPTION_STATUS = @"
UPDATE MCLIENT SET
    SUBSCRIPTIONSTATUS = @SubscriptionStatus
WHERE  CLIENTID = @ClientId";
}