namespace CMSDAL.Query.Site;
public static class SiteQB
{
    public const string GET_SITE = @"
        SELECT
            s.SITEID              AS SiteId,
            s.SITECODE            AS SiteCode,
            s.SITENAME            AS SiteName,
            s.SITEDEFAULTLOCALE   AS SiteDefaultLocale,
            s.DESIGNSYSTEMID      AS DesignSystemId,
            s.DEFAULTNAVIGATIONID AS DefaultNavigationId,
            s.HEADERCOMPONENTCONTENTID AS HeaderComponentContentId,
            s.FOOTERCOMPONENTCONTENTID AS FooterComponentContentId,
            s.SITESTATUS          AS SiteStatus,
            s.STATUS              AS Status,
            s.TENANTID            AS TenantId,
            s.CREATEDBYID         AS CreatedById,
            s.CREATEDON           AS CreatedOn,
            s.MODIFIEDBYID        AS ModifiedById,
            s.MODIFIEDON          AS ModifiedOn
        FROM DBO.MCMSSITE s
        WHERE s.SITEID = @SiteId AND s.TENANTID = @TenantId";

    public const string SAVE_SITE = @"
        INSERT INTO DBO.MCMSSITE
            (SITECODE, SITENAME, SITEDEFAULTLOCALE, DESIGNSYSTEMID, SITESTATUS, STATUS,
             TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
        VALUES
            (@SiteCode, @SiteName, @SiteDefaultLocale, @DesignSystemId, @SiteStatus, @Status,
             @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);
        SELECT SCOPE_IDENTITY();";

    public const string UPDATE_SITE = @"
        UPDATE DBO.MCMSSITE
        SET
            SITECODE = @SiteCode,
            SITENAME = @SiteName,
            SITEDEFAULTLOCALE = @SiteDefaultLocale,
            DESIGNSYSTEMID = @DesignSystemId,
            DEFAULTNAVIGATIONID = @DefaultNavigationId,
            HEADERCOMPONENTCONTENTID = @HeaderComponentContentId,
            FOOTERCOMPONENTCONTENTID = @FooterComponentContentId,
            SITESTATUS = @SiteStatus,
            STATUS = @Status,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON = @ModifiedOn
        WHERE SITEID = @SiteId AND TENANTID = @TenantId";

    public const string DELETE_SITE = @"
        UPDATE DBO.MCMSSITE SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
        WHERE SITEID = @SiteId AND TENANTID = @TenantId";

    public const string GET_SELECTLIST_SITE = @"
        WITH SiteList AS (
            SELECT
                s.SITEID     AS SiteId,
                s.SITECODE   AS SiteCode,
                s.SITENAME   AS SiteName,
                s.SITESTATUS AS SiteStatus,
                ROW_NUMBER() OVER (ORDER BY s.SITEID) AS RowNum
            FROM DBO.MCMSSITE s
            WHERE s.TENANTID = @TenantId AND s.STATUS = 1
        )
        SELECT SiteId, SiteCode, SiteName, SiteStatus
        FROM SiteList
        WHERE
            (@firstnumber = -1 AND @maxresult = -1)
            OR (RowNum BETWEEN @firstnumber AND @maxresult);";

    // Used by the cross-tenant guard pattern elsewhere in CMS — deliberately NOT filtered by
    // caller-claimed TenantId, so BLL can verify a referenced SiteId's true owner.
    public const string GET_SITE_TENANT = @"
        SELECT TENANTID FROM DBO.MCMSSITE WHERE SITEID = @SiteId";
}