namespace MaintenanceDAL.Query.TypeComponent
{
    // Confirmed against the real MTYPECOMPONENT/MTYPECOMPONENTDETAIL DDL via the legacy NHibernate
    // mappings (HibernateMapFile/TypeComponent.hbm.xml, TypeComponentDetail.hbm.xml). Note the PK
    // column is COMPONENTID/COMPONENTDETAILID, not TYPECOMPONENTID as the C# property names suggest.
    public static class TypeComponentQB
    {
        public const string GET_TYPECOMPONENT = @"SELECT
                TC.COMPONENTID          AS TypeComponentId,
                TC.NATURE               AS TypeComponentNature,
                TC.ASSETTYPEID          AS AssetTypeId,
                TC.RESOURCETYPEID       AS ResourceTypeId,
                TC.MACHINETYPEID        AS MachineTypeId,
                TC.CATEGORYID           AS CategoryId,
                TC.SUBCATEGORYID        AS SubCategoryId,
                TC.ITEMID               AS ItemId,
                TC.REMARKS              AS TypeComponentRemarks,
                TC.SOURCETYPE           AS TypeComponentSourceType,
                TC.SORTORDER            AS TypeComponentSortOrder,
                TC.STATUS               AS TypeComponentStatus,
                TC.VERSION              AS TypeComponentVersion,
                TC.CREATEDBYID          AS TypeComponentCreatedById,
                TC.CREATEDON            AS TypeComponentCreatedOn,
                TC.MODIFIEDBYID         AS TypeComponentModifiedById,
                TC.MODIFIEDON           AS TypeComponentModifiedOn,
                TC.TENANTID             AS TenantId,

                D.COMPONENTDETAILID     AS TypeComponentDetailId,
                D.COMPONENTID           AS ComponentId,
                D.SLNO                  AS TypeComponentDetailSlNo,
                D.DETAILNATURE          AS TypeComponentDetailDetailNature,
                D.DETAILASSETTYPEID     AS DetailAssetTypeId,
                D.DETAILRESOURCETYPEID  AS DetailResourceTypeId,
                D.DETAILMACHINETYPEID   AS DetailMachineTypeId,
                D.DETAILCATEGORYID      AS DetailCategoryId,
                D.DETAILSUBCATEGORYID   AS DetailSubCategoryId,
                D.DETAILITEMID          AS DetailItemId,
                D.QUANTITY              AS TypeComponentDetailQuantity,
                D.USAGETYPE             AS TypeComponentDetailUsageType,
                D.PARTYID               AS PartyId,
                D.REMARKS               AS TypeComponentDetailRemarks
            FROM MTYPECOMPONENT TC
            LEFT JOIN MTYPECOMPONENTDETAIL D ON D.COMPONENTID = TC.COMPONENTID
            WHERE TC.COMPONENTID = @TypeComponentId
            AND   TC.TENANTID = @TenantId;";

        // No natural Code/Name on this entity (it's an AssetType+Item combination, not a coded
        // master) — list by owning AssetType instead of a generic select-list, mirroring
        // ActivityMaterial's GetActivityMaterialListByActivity shape.
        public const string GET_TYPECOMPONENT_LIST_BY_ASSETTYPE = @"SELECT
                COMPONENTID AS Id,
                ASSETTYPEID AS AssetTypeId,
                ITEMID      AS ItemId
            FROM MTYPECOMPONENT
            WHERE ASSETTYPEID = @AssetTypeId
            AND   STATUS <> 2
            AND   TENANTID = @TenantId;";

        public const string SAVE_TYPECOMPONENT = @"INSERT INTO MTYPECOMPONENT (
                COMPONENTID, NATURE, ASSETTYPEID, RESOURCETYPEID, MACHINETYPEID, CATEGORYID, SUBCATEGORYID, ITEMID,
                REMARKS, SOURCETYPE, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES (
                @TypeComponentId, @TypeComponentNature, @AssetTypeId, @ResourceTypeId, @MachineTypeId, @CategoryId, @SubCategoryId, @ItemId,
                @TypeComponentRemarks, @TypeComponentSourceType, @TypeComponentSortOrder, @TypeComponentStatus, @TypeComponentVersion,
                @TypeComponentCreatedById, @TypeComponentCreatedOn, @TypeComponentModifiedById, @TypeComponentModifiedOn, @TenantId);";

        public const string UPDATE_TYPECOMPONENT = @"UPDATE MTYPECOMPONENT SET
                NATURE = @TypeComponentNature, ASSETTYPEID = @AssetTypeId, RESOURCETYPEID = @ResourceTypeId,
                MACHINETYPEID = @MachineTypeId, CATEGORYID = @CategoryId, SUBCATEGORYID = @SubCategoryId, ITEMID = @ItemId,
                REMARKS = @TypeComponentRemarks, SOURCETYPE = @TypeComponentSourceType, SORTORDER = @TypeComponentSortOrder,
                STATUS = @TypeComponentStatus, VERSION = VERSION + 1,
                MODIFIEDBYID = @TypeComponentModifiedById, MODIFIEDON = @TypeComponentModifiedOn
            WHERE COMPONENTID = @TypeComponentId
            AND   TENANTID = @TenantId;";

        public const string DELETE_TYPECOMPONENT = @"DELETE FROM MTYPECOMPONENT
            WHERE COMPONENTID = @TypeComponentId
            AND   TENANTID = @TenantId;";

        public const string DELETE_TYPECOMPONENTDETAIL_BY_COMPONENT = @"DELETE FROM MTYPECOMPONENTDETAIL
            WHERE COMPONENTID = @ComponentId;";

        public const string SAVE_TYPECOMPONENTDETAIL = @"INSERT INTO MTYPECOMPONENTDETAIL (
                COMPONENTDETAILID, COMPONENTID, SLNO, DETAILNATURE, DETAILASSETTYPEID, DETAILRESOURCETYPEID,
                DETAILMACHINETYPEID, DETAILCATEGORYID, DETAILSUBCATEGORYID, DETAILITEMID, QUANTITY, USAGETYPE, PARTYID, REMARKS)
            VALUES (
                @TypeComponentDetailId, @ComponentId, @TypeComponentDetailSlNo, @TypeComponentDetailDetailNature,
                @DetailAssetTypeId, @DetailResourceTypeId, @DetailMachineTypeId, @DetailCategoryId, @DetailSubCategoryId,
                @DetailItemId, @TypeComponentDetailQuantity, @TypeComponentDetailUsageType, @PartyId, @TypeComponentDetailRemarks);";
    }
}