namespace PartnerDAL.Query.ClientDetail
{
    public static class ClientDetailQB
    {
        // Index: IX_MCLIENTDETAILS_PARTNERPRODUCTID on MCLIENTDETAILS(PARTNERPRODUCTID) INCLUDE (CLIENTID)
        public const string GET_CLIENT_DETAIL_LIST = @"
            SELECT CLIENTID          AS ClientId,
                   CLIENTSITEID      AS ClientSiteId,
                   PARTNERPRODUCTID  AS PartnerProductId
            FROM   MCLIENTDETAILS
            WHERE  CLIENTID = @ClientId
            ORDER BY CLIENTSITEID";

        public const string GET_CLIENT_DETAIL_BY_SITE = @"
            SELECT CLIENTID          AS ClientId,
                   CLIENTSITEID      AS ClientSiteId,
                   PARTNERPRODUCTID  AS PartnerProductId
            FROM   MCLIENTDETAILS
            WHERE  CLIENTID     = @ClientId
              AND  CLIENTSITEID = @ClientSiteId";

        // MCLIENTDETAILS rows themselves belong to core client management (one row per
        // CLIENTID/CLIENTSITEID, created elsewhere) — this module only ever UPDATEs the
        // PARTNERPRODUCTID column on an existing site row, never INSERTs/DELETEs the row.
        public const string ASSIGN_PARTNER_PRODUCT = @"
            UPDATE MCLIENTDETAILS
            SET    PARTNERPRODUCTID = @PartnerProductId
            WHERE  CLIENTID         = @ClientId
              AND  CLIENTSITEID     = @ClientSiteId";

        // No core-framework endpoint creates MCLIENTDETAILS rows (confirmed — every other
        // reference to this table across the codebase is SELECT/UPDATE/DDL only). The 7
        // NOT NULL CRM-assignment columns below (SalesInChargeId, etc.) have nothing to do
        // with white-labeling and Partner has no legitimate way to know their real values —
        // they're set to -1 (this schema's "not set" sentinel throughout) so the row is
        // valid; whatever core-framework process actually owns client onboarding is expected
        // to fill in the real values later. PARTNERPRODUCTID defaults to -1 (unassigned) —
        // callers use AssignPartnerProduct afterward to set the real value.
        public const string CREATE_CLIENT_DETAIL = @"
            IF NOT EXISTS (SELECT 1 FROM MCLIENTDETAILS WHERE CLIENTID = @ClientId AND CLIENTSITEID = @ClientSiteId)
            INSERT INTO MCLIENTDETAILS
                (CLIENTID, CLIENTSITEID, PARTNERPRODUCTID,
                 SALESINCHARGEID, ACCOUNTINCHARGEID, IMPLEMENTATIONINCHARGEID, SUPPORTINCHARGEID,
                 PURCHASECONTACTID, ACCOUNTCONTACTID, SUPPORTCONTACTID)
            VALUES
                (@ClientId, @ClientSiteId, -1,
                 -1, -1, -1, -1,
                 -1, -1, -1);
            SELECT @@ROWCOUNT;";

        // ---------------------------------------------------------------------------
        // MSERVERCONFIG — registering a new ConnectionName against an EXISTING database.
        // Not the same thing as core provisioning's OnboardClient, which creates a brand
        // NEW physical database per client and auto-derives ConnectionName/DatabaseName
        // internally (confirmed: OnboardClientRequestDTO has no such fields). This path
        // is for the opposite, narrower case — several named connections sharing one
        // already-provisioned database — with no schema change to MSERVERCONFIG itself.
        // ---------------------------------------------------------------------------

        // CONNECTIONNAME has no UNIQUE constraint in the live schema (confirmed via
        // sys.indexes — only a non-unique index exists), so this module enforces
        // uniqueness itself before ever inserting.
        public const string CONNECTION_NAME_EXISTS = @"
            SELECT COUNT(1) FROM MSERVERCONFIG WHERE CONNECTIONNAME = @ConnectionName";

        // Confirms the target database is one an existing, working connection already
        // points at — refuses to register a ConnectionName against a name nothing else
        // uses, which would silently create an unreachable/untested routing entry.
        // Also surfaces SERVICESERVERID so the new row can self-heal onto the same IIS/
        // service-server assignment as its sibling connections (CREATE_SERVER_CONFIG used
        // to omit this column entirely, leaving it NULL — the exact cause of
        // VersionDAL.VersionUrlIdentifier's "Service server is not correctly assigned"
        // failure for every connection ever registered through this endpoint).
        public const string DATABASE_NAME_IN_USE = @"
            SELECT TOP 1 SERVERID, SERVICESERVERID
            FROM   MSERVERCONFIG
            WHERE  DATABASENAME = @DatabaseName AND STATUS = 1";

        // Fail-fast guard for the self-heal above: confirms the SERVICESERVERID being
        // propagated onto the new row actually points at a real, active MSERVER row —
        // so a corrupted/never-fixed sibling row can't silently propagate the same bad
        // assignment forward onto every connection registered after it.
        public const string SERVICE_SERVER_IS_ACTIVE = @"
            SELECT COUNT(1) FROM MSERVER WHERE SERVERID = @ServiceServerId AND STATUS = 1";

        // SERVERCONFIGID is confirmed NOT an identity column on the live schema (unlike
        // what SwDAL's INSERT_MSERVERCONFIG assumes) — every insert must supply its own
        // id. Extends the existing negative-number convention safely downward from
        // whatever the current lowest id is, guaranteeing no collision.
        public const string GET_NEXT_SERVER_CONFIG_ID = @"
            SELECT ISNULL(MIN(SERVERCONFIGID), 0) - 1 FROM MSERVERCONFIG";

        public const string CREATE_SERVER_CONFIG = @"
            INSERT INTO MSERVERCONFIG
                (SERVERCONFIGID, CONNECTIONNAME, SERVERID, DATABASETYPE, DATABASENAME,
                 CLIENTID, CLIENTSITEID, STATUS, VERSION, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, OFFSET, MAXVALUE, SERVICESERVERID)
            VALUES
                (@ServerConfigId, @ConnectionName, @ServerId, 0, @DatabaseName,
                 @ClientId, @ClientSiteId, 1, 1, 1,
                 @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), 0, 0, @ServiceServerId);";
    }
}