using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.ItemGroup
{
    public static class ItemGroupQB
    {
        public const string GET_ITEMGROUP = @"
SELECT
    IG.ITEMGROUPID AS ItemGroupId,
    IG.ITEMGROUPCODE AS ItemGroupCode,
    IG.ITEMGROUPNAME AS ItemGroupName,
    IG.LEVELS AS ItemGroupLevels,
    IG.ISSELECTED AS ItemGroupIsSelected,
    IG.REMARKS AS ItemGroupRemarks,

    IG.PARENTGROUPID AS ParentId,

    PG.ITEMGROUPCODE AS ParentCode,
    PG.ITEMGROUPNAME AS ParentName,

    IG.PARENTGROUPIDS AS ParentIds,
    IG.SORTORDER AS ItemGroupSortOrder,
    IG.STATUS AS ItemGroupStatus,
    IG.VERSION AS ItemGroupVersion,
    IG.CREATEDBYID AS ItemGroupCreatedById,
    IG.CREATEDON AS ItemGroupCreatedOn,
    IG.MODIFIEDBYID AS ItemGroupModifiedById,
    IG.MODIFIEDON AS ItemGroupModifiedOn,
    IG.SOURCETYPE AS ItemGroupSourceType,
    IG.TENANTID AS BranchTenantId
FROM MITEMGROUP IG
LEFT JOIN MITEMGROUP PG
    ON IG.PARENTGROUPID = PG.ITEMGROUPID
WHERE IG.ITEMGROUPID = @ItemGroupId;
";
        public const string SAVE_ITEMGROUP = @"
INSERT INTO MITEMGROUP (
    ITEMGROUPID,
    ITEMGROUPCODE,
    ITEMGROUPNAME,
    LEVELS,
    ISSELECTED,
    REMARKS,
    PARENTGROUPID,
    PARENTGROUPIDS,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES (
    @ItemGroupId,
    @ItemGroupCode,
    @ItemGroupName,
    @ItemGroupLevels,
    @ItemGroupIsSelected,
    @ItemGroupRemarks,
    @ParentId,
    @ParentIds,
    @ItemGroupSortOrder,
    @ItemGroupStatus,
    @ItemGroupVersion,
    @ItemGroupCreatedById,
    @ItemGroupCreatedOn,
    @ItemGroupModifiedById,
    @ItemGroupModifiedOn,
    @ItemGroupSourceType,
    @BranchTenantId
);
";
        public const string UPDATE_ITEMGROUP = @"
UPDATE MITEMGROUP SET
    ITEMGROUPCODE = @ItemGroupCode,
    ITEMGROUPNAME = @ItemGroupName,
    LEVELS = @ItemGroupLevels,
    ISSELECTED = @ItemGroupIsSelected,
    REMARKS = @ItemGroupRemarks,
    PARENTGROUPID = @ParentId,
    PARENTGROUPIDS = @ParentIds,
    SORTORDER = @ItemGroupSortOrder,
    STATUS = @ItemGroupStatus,
    VERSION = @ItemGroupVersion,
    MODIFIEDBYID = @ItemGroupModifiedById,
    MODIFIEDON = @ItemGroupModifiedOn,
    SOURCETYPE = @ItemGroupSourceType,
    TENANTID = @BranchTenantId
WHERE ITEMGROUPID = @ItemGroupId;
";
        public const string DELETE_ITEMGROUP = @"
DELETE FROM MITEMGROUP 
WHERE ITEMGROUPID = @ItemGroupId;
";

        public const string GET_SELECTLIST_ITEM_GROUP = @"
SELECT
    ITEMGROUPID AS Id,
    ITEMGROUPCODE AS Code,
    ITEMGROUPNAME AS Name
FROM MITEMGROUP
WHERE STATUS = 1;
";

        // Returns flat list including ParentId — used by BLL to build the tree
        public const string GET_ITEMGROUP_FLAT_LIST = @"
SELECT
    ITEMGROUPID AS Id,
    ITEMGROUPCODE AS Code,
    ITEMGROUPNAME AS Name,
    PARENTGROUPID AS ParentId
FROM MITEMGROUP
WHERE STATUS = 1
ORDER BY ITEMGROUPNAME;
";
    }
}