namespace EntitlementDAL.QueryBuilders;

/// <summary>SQL constants for MCLIENT (Thread 1 §2.2 narrowed — client-identity creation only).
/// Column list verified live against INFORMATION_SCHEMA.COLUMNS on a real reachable tenant DB
/// (docs/GB5-Schema-Registry.md has zero MCLIENT entries — it's built from QB INSERT extraction,
/// and none existed anywhere in this repo before this file).</summary>
public static class ClientProvisioningQB
{
    public const string CHECK_CLIENT_CODE_EXISTS = @"
        SELECT COUNT(1) FROM MCLIENT WHERE CLIENTCODE = @ClientCode";

    // Read path for MCLIENT.DEPLOYMENTTYPE (tracker §40, Gate A) — no read of this column
    // existed anywhere before this; every prior reference was write-only (this file's own
    // INSERT_MCLIENT, at client-creation time). Returns NULL if the client doesn't exist —
    // the caller must not assume every ClientId has a real MCLIENT row (e.g. sandbox/test rows).
    public const string GET_CLIENT_DEPLOYMENT_TYPE = @"
        SELECT DEPLOYMENTTYPE FROM MCLIENT WHERE CLIENTID = @ClientId";

    // Read path for MCLIENT.JURISDICTIONCODE (tracker §51.12) — resolved at the two points where
    // a real ClientId already exists (Login's individual-EULA gate, InitiatePaymentAsync's
    // "buying" gate). Returns NULL if the client doesn't exist or never had one captured.
    public const string GET_CLIENT_JURISDICTION_CODE = @"
        SELECT JURISDICTIONCODE FROM MCLIENT WHERE CLIENTID = @ClientId";

    // Resolves the real MROLE.ROLEID for a code-driven default (e.g. 'CLIENTADMIN', tracker
    // §31.12) instead of a hardcoded numeric config value — portable across environments, since
    // any install just needs the same ROLECODE seeded, not a re-resolved numeric ID.
    public const string GET_ROLEID_BY_CODE = @"
        SELECT ROLEID FROM MROLE WHERE ROLECODE = @RoleCode";

    // GSPID/MFAAPPLICABLETYPE/MFATYPEID/MFACLIENTACCOUNTID/MFACLIENTSECRETKEY/PASSWORDPOLICYID
    // are NOT NULL columns on the live schema but are unrelated to Entitlement's own scope (GST
    // partner / MFA / password-policy features) — defaulted to this codebase's established
    // "-1/0/empty = NONE" sentinel convention, not independently confirmed against any real
    // GSP/MFA/password-policy configuration. Revisit if a client ever needs these set for real
    // at creation time.
    public const string INSERT_MCLIENT = @"
        INSERT INTO MCLIENT
            (CLIENTID, CLIENTCODE, CLIENTNAME, CLIENTSHORTNAME, SORTORDER, STATUS, VERSION,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE,
             GSPID, MFAAPPLICABLETYPE, MFATYPEID, MFACLIENTACCOUNTID, MFACLIENTSECRETKEY, PASSWORDPOLICYID,
             DEPLOYMENTTYPE, SUBSCRIPTIONSTATUS, TRIALMODE, JURISDICTIONCODE)
        VALUES
            (@ClientId, @ClientCode, @ClientName, @ClientShortName, @SortOrder, @Status, @Version,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType,
             -1, 0, -1, '', '', -1,
             @DeploymentType, @SubscriptionStatus, @TrialMode, @JurisdictionCode)";
}