namespace PartnerDAL.Query.PartnerProduct
{
    public static class PartnerProductQB
    {
        // Index: IX_TPARTNERPRODUCT_PARTNER on TPARTNERPRODUCT(PARTNERID)
        // Index: IX_TPARTNERPRODUCT_CODE on TPARTNERPRODUCT(PARTNERID, PRODUCTCODE) UNIQUE
        public const string GET_PARTNER_PRODUCT = @"
            SELECT
                PP.PARTNERPRODUCTID         AS PartnerProductId,
                PP.PARTNERID                AS PartnerId,
                P.PARTNERNAME               AS PartnerName,
                P.PARTNERCODE               AS PartnerCode,
                PP.PRODUCTCODE              AS ProductCode,
                PP.PRODUCTNAME              AS ProductName,
                PP.PRODUCTSEGMENT           AS ProductSegment,
                PP.PRODUCTDESCRIPTION       AS ProductDescription,
                PP.STATUS                   AS PartnerProductStatus,
                PP.CREATEDBYID              AS PartnerProductCreatedById,
                PP.CREATEDON                AS PartnerProductCreatedOn,
                PP.MODIFIEDBYID             AS PartnerProductModifiedById,
                PP.MODIFIEDON               AS PartnerProductModifiedOn
            FROM TPARTNERPRODUCT PP
            INNER JOIN TPARTNER P ON P.PARTNERID = PP.PARTNERID
            WHERE PP.PARTNERPRODUCTID = @PartnerProductId";

        public const string GET_PARTNER_PRODUCT_LIST_BY_PARTNER = @"
            SELECT
                PP.PARTNERPRODUCTID         AS PartnerProductId,
                PP.PARTNERID                AS PartnerId,
                P.PARTNERNAME               AS PartnerName,
                PP.PRODUCTCODE              AS ProductCode,
                PP.PRODUCTNAME              AS ProductName,
                PP.PRODUCTSEGMENT           AS ProductSegment,
                PP.STATUS                   AS PartnerProductStatus,
                PP.CREATEDBYID              AS PartnerProductCreatedById,
                PP.CREATEDON                AS PartnerProductCreatedOn,
                PP.MODIFIEDBYID             AS PartnerProductModifiedById,
                PP.MODIFIEDON               AS PartnerProductModifiedOn
            FROM TPARTNERPRODUCT PP
            INNER JOIN TPARTNER P ON P.PARTNERID = PP.PARTNERID
            WHERE PP.PARTNERID = @PartnerId
              AND PP.STATUS = 1
            ORDER BY PP.PRODUCTNAME";

        // PartnerId is no longer a fixed query parameter — it's exposed as a plain
        // output column so the caller filters on it via CriteriaDTO (SectionCriteriaList),
        // the same dynamic-WHERE mechanism as every other filter on this select list.
        public const string GET_SELECTLIST_PARTNER_PRODUCT = @"
            SELECT * FROM (
                SELECT
                    PARTNERPRODUCTID    AS Id,
                    PARTNERID           AS PartnerId,
                    PRODUCTCODE         AS Code,
                    PRODUCTNAME         AS Name,
                    PRODUCTSEGMENT      AS ShortName
                FROM TPARTNERPRODUCT
                WHERE STATUS = 1
            ) AS T
            WHERE 1 = 1
            {DYNAMIC_WHERE}
            ORDER BY Name";

        public const string SAVE_PARTNER_PRODUCT = @"
            INSERT INTO TPARTNERPRODUCT (
                PARTNERID,
                PRODUCTCODE,
                PRODUCTNAME,
                PRODUCTSEGMENT,
                PRODUCTDESCRIPTION,
                STATUS,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            ) VALUES (
                @PartnerId,
                @ProductCode,
                @ProductName,
                @ProductSegment,
                @ProductDescription,
                @PartnerProductStatus,
                @PartnerProductCreatedById,
                @PartnerProductCreatedOn,
                @PartnerProductModifiedById,
                @PartnerProductModifiedOn
            );
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_PARTNER_PRODUCT = @"
            UPDATE TPARTNERPRODUCT SET
                PRODUCTCODE         = @ProductCode,
                PRODUCTNAME         = @ProductName,
                PRODUCTSEGMENT      = @ProductSegment,
                PRODUCTDESCRIPTION  = @ProductDescription,
                STATUS              = @PartnerProductStatus,
                MODIFIEDBYID        = @PartnerProductModifiedById,
                MODIFIEDON          = @PartnerProductModifiedOn
            WHERE PARTNERPRODUCTID = @PartnerProductId";

        public const string DELETE_PARTNER_PRODUCT = @"
            UPDATE TPARTNERPRODUCT SET STATUS = 0, MODIFIEDON = GETUTCDATE()
            WHERE PARTNERPRODUCTID = @PartnerProductId";
    }
}