namespace PartnerDAL.Query.PartnerBrand
{
    public static class PartnerBrandQB
    {
        // Index: IX_TPARTNERBRAND_PARTNER on TPARTNERBRAND(PARTNERID) UNIQUE
        // NOTE: LOGOFILEID/LOGODARKFILEID/FAVICONFILEID are FKs to MFILE, matching
        // the live schema. These CRUD queries run via the caller's LoginDTO, which
        // for an authenticated admin session resolves to that client's own
        // ProductionDB — the SAME database TPARTNERBRAND and MFILE both live in
        // today, so the join below is safe and correct here (unlike the pre-auth
        // ConnectionName-rooted queries further down, which must never assume that).
        // Dropdown/select-list: one row per partner that already has a brand configured.
        // Runs via the caller's LoginDTO like the other admin CRUD queries above.
        // Index: IX_TPARTNERBRAND_PARTNER on TPARTNERBRAND(PARTNERID) UNIQUE
        public const string GET_SELECTLIST_PARTNER_BRAND = @"
            SELECT * FROM (
                SELECT
                    PB.PARTNERBRANDID                       AS PartnerBrandId,
                    PB.APPNAME                              AS PartnerBrandAppName,
                    P.PARTNERCODE                           AS PartnerCode,
                    P.PARTNERNAME                           AS PartnerName,
                    P.PARTNERSHORTNAME                      AS PartnerShortName
                FROM TPARTNERBRAND PB
                INNER JOIN TPARTNER P ON P.PARTNERID = PB.PARTNERID
                WHERE PB.STATUS = 1
            ) AS T
            WHERE 1 = 1
            {DYNAMIC_WHERE}
            ORDER BY PartnerName";

        public const string GET_PARTNER_BRAND = @"
            SELECT
                PB.PARTNERBRANDID       AS PartnerBrandId,
                PB.PARTNERID            AS PartnerId,
                P.PARTNERNAME           AS PartnerName,
                PB.APPNAME              AS AppName,
                PB.PRIMARYCOLOR         AS PrimaryColor,
                PB.SECONDARYCOLOR       AS SecondaryColor,
                PB.ACCENTCOLOR          AS AccentColor,
                PB.FONTFAMILY           AS FontFamily,
                PB.LOGOFILEID           AS LogoFileId,
                lf.FILELOCATION         AS LogoFileUrl,
                PB.LOGODARKFILEID       AS LogoDarkFileId,
                dlf.FILELOCATION        AS LogoDarkFileUrl,
                PB.FAVICONFILEID        AS FaviconFileId,
                ff.FILELOCATION         AS FaviconFileUrl,
                PB.FOOTERTEXT           AS FooterText,
                PB.SUPPORTEMAIL         AS SupportEmail,
                PB.WEBSITEURL           AS WebsiteUrl,
                PB.STATUS               AS PartnerBrandStatus,
                PB.CREATEDBYID          AS PartnerBrandCreatedById,
                PB.CREATEDON            AS PartnerBrandCreatedOn,
                PB.MODIFIEDBYID         AS PartnerBrandModifiedById,
                PB.MODIFIEDON           AS PartnerBrandModifiedOn
            FROM TPARTNERBRAND PB
            INNER JOIN TPARTNER P  ON P.PARTNERID = PB.PARTNERID
            LEFT JOIN MFILE lf     ON lf.FILEID  = PB.LOGOFILEID
            LEFT JOIN MFILE dlf    ON dlf.FILEID = PB.LOGODARKFILEID
            LEFT JOIN MFILE ff     ON ff.FILEID  = PB.FAVICONFILEID
            WHERE PB.PARTNERBRANDID = @PartnerBrandId";

        public const string SAVE_PARTNER_BRAND = @"
            INSERT INTO TPARTNERBRAND (
                PARTNERID,
                APPNAME,
                PRIMARYCOLOR,
                SECONDARYCOLOR,
                ACCENTCOLOR,
                FONTFAMILY,
                LOGOFILEID,
                LOGODARKFILEID,
                FAVICONFILEID,
                FOOTERTEXT,
                SUPPORTEMAIL,
                WEBSITEURL,
                STATUS,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            ) VALUES (
                @PartnerId,
                @AppName,
                @PrimaryColor,
                @SecondaryColor,
                @AccentColor,
                @FontFamily,
                @LogoFileId,
                @LogoDarkFileId,
                @FaviconFileId,
                @FooterText,
                @SupportEmail,
                @WebsiteUrl,
                @PartnerBrandStatus,
                @PartnerBrandCreatedById,
                @PartnerBrandCreatedOn,
                @PartnerBrandModifiedById,
                @PartnerBrandModifiedOn
            );
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_PARTNER_BRAND = @"
            UPDATE TPARTNERBRAND SET
                APPNAME         = @AppName,
                PRIMARYCOLOR    = @PrimaryColor,
                SECONDARYCOLOR  = @SecondaryColor,
                ACCENTCOLOR     = @AccentColor,
                FONTFAMILY      = @FontFamily,
                LOGOFILEID      = @LogoFileId,
                LOGODARKFILEID  = @LogoDarkFileId,
                FAVICONFILEID   = @FaviconFileId,
                FOOTERTEXT      = @FooterText,
                SUPPORTEMAIL    = @SupportEmail,
                WEBSITEURL      = @WebsiteUrl,
                STATUS          = @PartnerBrandStatus,
                MODIFIEDBYID    = @PartnerBrandModifiedById,
                MODIFIEDON      = @PartnerBrandModifiedOn
            WHERE PARTNERBRANDID = @PartnerBrandId
              AND PARTNERID      = @PartnerId";

        public const string DELETE_PARTNER_BRAND = @"
            UPDATE TPARTNERBRAND SET STATUS = 0, MODIFIEDON = GETUTCDATE()
            WHERE PARTNERBRANDID = @PartnerBrandId";

        // Index: IX_TPARTNERBRAND_PARTNER on TPARTNERBRAND(PARTNERID) UNIQUE
        public const string GET_PARTNER_BRAND_LIST_BY_PARTNER = @"
            SELECT
                PB.PARTNERBRANDID       AS PartnerBrandId,
                PB.PARTNERID            AS PartnerId,
                P.PARTNERNAME           AS PartnerName,
                PB.APPNAME              AS AppName,
                PB.PRIMARYCOLOR         AS PrimaryColor,
                PB.SECONDARYCOLOR       AS SecondaryColor,
                PB.ACCENTCOLOR          AS AccentColor,
                PB.FONTFAMILY           AS FontFamily,
                PB.LOGOFILEID           AS LogoFileId,
                lf.FILELOCATION         AS LogoFileUrl,
                PB.LOGODARKFILEID       AS LogoDarkFileId,
                dlf.FILELOCATION        AS LogoDarkFileUrl,
                PB.FAVICONFILEID        AS FaviconFileId,
                ff.FILELOCATION         AS FaviconFileUrl,
                PB.FOOTERTEXT           AS FooterText,
                PB.SUPPORTEMAIL         AS SupportEmail,
                PB.WEBSITEURL           AS WebsiteUrl,
                PB.STATUS               AS PartnerBrandStatus,
                PB.CREATEDBYID          AS PartnerBrandCreatedById,
                PB.CREATEDON            AS PartnerBrandCreatedOn,
                PB.MODIFIEDBYID         AS PartnerBrandModifiedById,
                PB.MODIFIEDON           AS PartnerBrandModifiedOn
            FROM TPARTNERBRAND PB
            INNER JOIN TPARTNER P  ON P.PARTNERID = PB.PARTNERID
            LEFT JOIN MFILE lf     ON lf.FILEID  = PB.LOGOFILEID
            LEFT JOIN MFILE dlf    ON dlf.FILEID = PB.LOGODARKFILEID
            LEFT JOIN MFILE ff     ON ff.FILEID  = PB.FAVICONFILEID
            WHERE PB.PARTNERID = @PartnerId";

        // Index: IX_TPARTNERPRODUCTBRAND_PRODUCT on TPARTNERPRODUCTBRAND(PARTNERPRODUCTID) UNIQUE
        public const string GET_PARTNER_PRODUCT_BRAND = @"
            SELECT
                PPB.PARTNERPRODUCTBRANDID   AS PartnerProductBrandId,
                PPB.PARTNERPRODUCTID        AS PartnerProductId,
                PP.PRODUCTNAME              AS ProductName,
                PPB.APPNAME                 AS AppName,
                PPB.PRIMARYCOLOR            AS PrimaryColor,
                PPB.SECONDARYCOLOR          AS SecondaryColor,
                PPB.ACCENTCOLOR             AS AccentColor,
                PPB.FONTFAMILY              AS FontFamily,
                PPB.LOGOFILEID              AS LogoFileId,
                lf.FILELOCATION             AS LogoFileUrl,
                PPB.LOGODARKFILEID          AS LogoDarkFileId,
                dlf.FILELOCATION            AS LogoDarkFileUrl,
                PPB.FAVICONFILEID           AS FaviconFileId,
                ff.FILELOCATION             AS FaviconFileUrl,
                PPB.FOOTERTEXT              AS FooterText,
                PPB.STATUS                  AS PartnerProductBrandStatus,
                PPB.CREATEDBYID             AS PartnerProductBrandCreatedById,
                PPB.CREATEDON               AS PartnerProductBrandCreatedOn,
                PPB.MODIFIEDBYID            AS PartnerProductBrandModifiedById,
                PPB.MODIFIEDON              AS PartnerProductBrandModifiedOn
            FROM TPARTNERPRODUCTBRAND PPB
            INNER JOIN TPARTNERPRODUCT PP ON PP.PARTNERPRODUCTID = PPB.PARTNERPRODUCTID
            LEFT JOIN MFILE lf            ON lf.FILEID  = PPB.LOGOFILEID
            LEFT JOIN MFILE dlf           ON dlf.FILEID = PPB.LOGODARKFILEID
            LEFT JOIN MFILE ff            ON ff.FILEID  = PPB.FAVICONFILEID
            WHERE PPB.PARTNERPRODUCTID = @PartnerProductId";

        public const string SAVE_PARTNER_PRODUCT_BRAND = @"
            INSERT INTO TPARTNERPRODUCTBRAND (
                PARTNERPRODUCTID,
                APPNAME,
                PRIMARYCOLOR,
                SECONDARYCOLOR,
                ACCENTCOLOR,
                FONTFAMILY,
                LOGOFILEID,
                LOGODARKFILEID,
                FAVICONFILEID,
                FOOTERTEXT,
                STATUS,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            ) VALUES (
                @PartnerProductId,
                @AppName,
                @PrimaryColor,
                @SecondaryColor,
                @AccentColor,
                @FontFamily,
                @LogoFileId,
                @LogoDarkFileId,
                @FaviconFileId,
                @FooterText,
                @PartnerProductBrandStatus,
                @PartnerProductBrandCreatedById,
                @PartnerProductBrandCreatedOn,
                @PartnerProductBrandModifiedById,
                @PartnerProductBrandModifiedOn
            );
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_PARTNER_PRODUCT_BRAND = @"
            UPDATE TPARTNERPRODUCTBRAND SET
                APPNAME         = @AppName,
                PRIMARYCOLOR    = @PrimaryColor,
                SECONDARYCOLOR  = @SecondaryColor,
                ACCENTCOLOR     = @AccentColor,
                FONTFAMILY      = @FontFamily,
                LOGOFILEID      = @LogoFileId,
                LOGODARKFILEID  = @LogoDarkFileId,
                FAVICONFILEID   = @FaviconFileId,
                FOOTERTEXT      = @FooterText,
                STATUS          = @PartnerProductBrandStatus,
                MODIFIEDBYID    = @PartnerProductBrandModifiedById,
                MODIFIEDON      = @PartnerProductBrandModifiedOn
            WHERE PARTNERPRODUCTBRANDID = @PartnerProductBrandId
              AND PARTNERPRODUCTID      = @PartnerProductId";

        // Hop 1 for the post-login path — GB5System only, resolves which
        // PartnerProductId a ClientId is linked to. Hop 2 is the SAME
        // GET_TENANT_LOCAL_PUBLIC_BRAND below, run via the caller's own
        // (already-resolved, post-login) LoginDTO — which already points at
        // this client's own ProductionDB, where TPARTNERBRAND/MFILE actually live.
        //
        // MCLIENTDETAILS has confirmed duplicate rows per CLIENTID (data-quality
        // issue, not caused by this column) — SELECT DISTINCT collapses harmless
        // duplicates while still surfacing a real conflict (more than one distinct
        // PARTNERPRODUCTID for the same client) as more than one row, on purpose.
        // Index: IX_MCLIENTDETAILS_PARTNERPRODUCTID on MCLIENTDETAILS(PARTNERPRODUCTID)
        public const string GET_ROUTING_BY_CLIENT_ID = @"
            SELECT DISTINCT
                CD.CLIENTID                     AS ClientId,
                ISNULL(CD.PARTNERPRODUCTID, -1) AS PartnerProductId
            FROM MCLIENTDETAILS CD
            WHERE CD.CLIENTID = @ClientId";

        // ── Two-hop resolution rooted at ConnectionName ──────────────────────────
        // Reflects where the data actually lives today: TPARTNER/TPARTNERBRAND/MFILE
        // are per-tenant, inside each client's own ProductionDB — not centralized.
        // Hop 1 (below) only resolves ROUTING from GB5System: which PartnerProductId
        // a client is linked to. Hop 2 (GET_TENANT_LOCAL_PUBLIC_BRAND) then queries
        // that SAME ProductionDB — resolved via ConnectionName, no cross-database
        // join, no second lookup — for the actual brand + logo files.
        //
        // MCLIENTDETAILS is keyed by (CLIENTID, CLIENTSITEID) — one row per client
        // SITE, not per client — so a client with N sites fans out to N rows here.
        // SELECT DISTINCT collapses that back to one row per (ClientId, PartnerProductId)
        // combination; if sites ever legitimately disagreed on PartnerProductId, this
        // still surfaces as >1 row, which the caller's ambiguity check correctly rejects.
        //
        // Caveat this implies: if the same partner ever spans more than one client,
        // each client's ProductionDB needs its OWN synced copy of that partner's
        // TPARTNERBRAND/TPARTNERPRODUCTBRAND rows (extend ProvisionPartnerSync to
        // cover them) — otherwise every client but the one holding the "real" row
        // resolves to the platform defaults below.
        // MCLIENTDETAILS.PARTNERPRODUCTID is the single source of truth for this
        // assignment — there is no separate override column on MSERVERCONFIG (an earlier
        // draft of this query assumed one; it was never actually added to the live schema
        // and has been removed here to match reality, with no ALTER TABLE required).
        // MCLIENTDETAILS is keyed by (CLIENTID, CLIENTSITEID), so the join includes
        // CLIENTSITEID to find the exact site row this ConnectionName's config points at,
        // instead of fanning out to every site under the same CLIENTID.
        // Index: existing lookup on MSERVERCONFIG(CONNECTIONNAME) (see ConnectionQB),
        //        IX_MCLIENTDETAILS_PARTNERPRODUCTID on MCLIENTDETAILS(PARTNERPRODUCTID)
        // MSERVERCONFIG.PARTNERPRODUCTID overrides MCLIENTDETAILS.PARTNERPRODUCTID when set
        // (i.e. not -1) — same precedence as ConnectionQB.GET_PARTNERPRODUCTID_BY_CONNECTION_NAME
        // (also used pre-login, in the same VersionCheck/branding boot sequence as this query).
        // Previously read only MCLIENTDETAILS, so the two pre-login calls could disagree on
        // which partner's branding to show for the same ConnectionName.
        public const string GET_ROUTING_BY_CONNECTION_NAME = @"
            SELECT DISTINCT
                SC.CLIENTID      AS ClientId,
                SC.DATABASENAME  AS DatabaseName,
                SC.CONNECTIONNAME AS ConnectionName,
                CASE WHEN SC.PARTNERPRODUCTID <> -1 THEN SC.PARTNERPRODUCTID ELSE ISNULL(CD.PARTNERPRODUCTID, -1) END AS PartnerProductId
            FROM MSERVERCONFIG SC
            LEFT JOIN MCLIENTDETAILS CD
                   ON CD.CLIENTID     = SC.CLIENTID
                  AND CD.CLIENTSITEID = SC.CLIENTSITEID
            WHERE SC.CONNECTIONNAME = @ConnectionName
              AND SC.STATUS = 1";

        // Override lookup — runs tenant-local (same ConnectionName overload as Hop 2).
        // Lets a caller preview a SPECIFIC partner's brand for this ConnectionName
        // without touching MCLIENTDETAILS.PARTNERPRODUCTID at all. Purely a request-time
        // override — the stored assignment is untouched, and every other call without
        // this parameter continues to resolve through the normal Hop 1 routing.
        public const string GET_PARTNERPRODUCTID_BY_PARTNER_ID = @"
            SELECT TOP 1 PARTNERPRODUCTID
            FROM TPARTNERPRODUCT
            WHERE PARTNERID = @PartnerId
              AND STATUS = 1";

        // Hop 2 — runs INSIDE the tenant's own ProductionDB (via IQueryExecutor's
        // QueryAsync<T>(string ConnectionName, ...) overload — the same one
        // VersionDAL.VersionCheck already uses pre-auth). TPARTNERBRAND/MFILE are
        // co-located here, so the MFILE join is safe and correct in this context —
        // unlike a query rooted in GB5System, which must never join MFILE.
        public const string GET_TENANT_LOCAL_PUBLIC_BRAND = @"
            SELECT
                COALESCE(PPB.APPNAME,        PB.APPNAME,        'GoodBooks')  AS AppName,
                COALESCE(PPB.PRIMARYCOLOR,   PB.PRIMARYCOLOR,   '#003399')    AS PrimaryColor,
                COALESCE(PPB.SECONDARYCOLOR, PB.SECONDARYCOLOR, '#0055CC')    AS SecondaryColor,
                COALESCE(PPB.ACCENTCOLOR,    PB.ACCENTCOLOR,    '#FF6600')    AS AccentColor,
                COALESCE(PPB.FONTFAMILY,     PB.FONTFAMILY,     'Inter')      AS FontFamily,
                COALESCE(PPB.FOOTERTEXT,     PB.FOOTERTEXT,     NULL)         AS FooterText,
                PB.SUPPORTEMAIL                                                AS SupportEmail,
                PP.PARTNERPRODUCTID                                            AS PartnerProductId,
                PP.PRODUCTNAME                                                 AS ProductName,
                P.PARTNERID                                                    AS PartnerId,
                P.PARTNERCODE                                                  AS PartnerCode,
                REPLACE(COALESCE(ppbr_lf.FILELOCATION,  pbr_lf.FILELOCATION,  ''), '{BaseURI}', @BaseUri) AS LogoFileUrl,
                REPLACE(COALESCE(ppbr_dlf.FILELOCATION, pbr_dlf.FILELOCATION, ''), '{BaseURI}', @BaseUri) AS LogoDarkFileUrl,
                REPLACE(COALESCE(ppbr_ff.FILELOCATION,  pbr_ff.FILELOCATION,  ''), '{BaseURI}', @BaseUri) AS FaviconFileUrl
            FROM TPARTNERPRODUCT PP
            INNER JOIN TPARTNER P              ON P.PARTNERID = PP.PARTNERID
            LEFT JOIN TPARTNERBRAND PB         ON PB.PARTNERID = PP.PARTNERID
            LEFT JOIN TPARTNERPRODUCTBRAND PPB ON PPB.PARTNERPRODUCTID = PP.PARTNERPRODUCTID
            LEFT JOIN MFILE pbr_lf              ON pbr_lf.FILEID   = PB.LOGOFILEID
            LEFT JOIN MFILE pbr_dlf             ON pbr_dlf.FILEID  = PB.LOGODARKFILEID
            LEFT JOIN MFILE pbr_ff              ON pbr_ff.FILEID   = PB.FAVICONFILEID
            LEFT JOIN MFILE ppbr_lf             ON ppbr_lf.FILEID  = PPB.LOGOFILEID
            LEFT JOIN MFILE ppbr_dlf            ON ppbr_dlf.FILEID = PPB.LOGODARKFILEID
            LEFT JOIN MFILE ppbr_ff             ON ppbr_ff.FILEID  = PPB.FAVICONFILEID
            WHERE PP.PARTNERPRODUCTID = @PartnerProductId
              AND PP.STATUS = 1";
    }
}