package pwc.taxtech.atms.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.RowBounds;
import pwc.taxtech.atms.MyMapper;
import pwc.taxtech.atms.dpo.ProjectDisplayDto;
import pwc.taxtech.atms.entity.Project;
import pwc.taxtech.atms.entity.ProjectExample;
import pwc.taxtech.atms.entity.ProjectStatusManage;

import java.util.List;

@Mapper
public interface ProjectMapper extends MyMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    long countByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int deleteByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int deleteByPrimaryKey(String id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int insert(Project record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int insertSelective(Project record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    List<Project> selectByExampleWithRowbounds(ProjectExample example, RowBounds rowBounds);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    List<Project> selectByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    Project selectByPrimaryKey(String id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int updateByExampleSelective(@Param("record") Project record, @Param("example") ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int updateByExample(@Param("record") Project record, @Param("example") ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int updateByPrimaryKeySelective(Project record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table TAX_ADMIN.PROJECT
     *
     * @mbg.generated
     */
    int updateByPrimaryKey(Project record);

    @Select("<script>" +
            "SELECT DISTINCT " +
            "    p.CREATE_TIME AS CreateTime, " +
            "    p.IS_ACTIVE AS IsActive, " +
            "    p.ORGANIZATION_ID AS OrganizationID, " +
            "    p.Name AS Name, " +
            "    p.Code AS Code, " +
            "    p.id AS id, " +
            "    p.INDUSTRY_ID AS IndustryID, " +
            "    p.Year AS Year, " +
            "    p.REGION_ID AS RegionID, " +
            "    region.SHORT_NAME AS RegionName, " +
            "    p.RULE_TYPE AS RuleType, " +
            "    projectServiceType.SERVICE_TYPE_ID AS ServiceTypeID, " +
            "    p.UPDATE_TIME AS UpdateTime, " +
            "    serviceType.Name AS ServiceTypeName, " +
            "    organization.Name AS OrganizationName, " +
            "    industry.Name AS IndustryName, " +
            "    projectServiceType.Template_Group_ID AS TemplateGroupID, " +
            "    templateGroup.Name AS TemplateGroupName, " +
            "    p.CLIENT_CODE AS ClientCode, " +
            "    p.DB_NAME AS DbName, " +
            "    1 AS HaveCreateProject, " +
            "    p.ENTERPRISE_ACCOUNT_SET_ID AS EnterpriseAccountSetID, " +
            "    p.START_PERIOD AS StartPeriod, " +
            "    p.END_PERIOD AS EndPeriod " +
            "FROM " +
            "    PROJECT p " +
            "        JOIN " +
            "    ORGANIZATION org ON p.ORGANIZATION_ID = org.id " +
            "        JOIN " +
            "    PROJECT_SERVICE_TYPE projectServiceType ON p.id = projectServiceType.PROJECT_ID " +
            "        JOIN " +
            "    Organization organization ON p.ORGANIZATION_ID = organization.id " +
            "        JOIN " +
            "    SERVICE_TYPE serviceType ON projectServiceType.SERVICE_TYPE_ID = serviceType.id " +
            "        JOIN " +
            "    Industry industry ON p.INDUSTRY_ID = industry.id " +
            "        JOIN " +
            "    TEMPLATE_GROUP templateGroup ON projectServiceType.TEMPLATE_GROUP_ID = templateGroup.id " +
            "        JOIN " +
            "    Region region ON p.REGION_ID = region.id " +
            "        JOIN " +
            "    ENTERPRISE_ACCOUNT_SET_ORG enterOrg ON p.ENTERPRISE_ACCOUNT_SET_ID = enterOrg.ENTERPRISE_ACCOUNT_SET_ID" +
            "   <where>" +
            "       p.IS_ACTIVE=1 and serviceType.IS_ACTIVE=1 and p.ORGANIZATION_ID = enterOrg.ORGANIZATION_ID" +
            "       <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
//            "       <if test=\"serverId != null and serverId !='' \">AND p.serviceType=#{serverId}</if>" +
            "       <if test=\"projectYear != null\">AND p.Year=#{projectYear}</if>" +
            "   </where>" +
            "</script>")
    List<ProjectDisplayDto> getProjectList(@Param("orgId") String orgId, @Param("serverId") String serviceId,
                                           @Param("projectYear") Integer projectYear);

    @Select("SELECT  " +
            "    p.PERIOD_ID AS periodId, p.STATUS AS status " +
            "FROM " +
            "    PROJECT_STATUS_MANAGE p " +
            "WHERE " +
            "    p.PROJECT_ID = #{projectId} " +
            "ORDER BY PERIOD_ID , STATUS")
    List<ProjectStatusManage> getStatusesByProjectId(String projectId);

    @Select("<script>" +
            "SELECT   " +
            "    SYSDATE AS CreateTime,  " +
            "    1 AS IsActive,  " +
            "    org.id AS OrganizationID,  " +
            "    org.Name AS Name,  " +
            "    org.Code AS Code,  " +
            "    SYS_GUID() AS id,  " +
            "    org.Industry_ID AS IndustryID,  " +
            "    0 AS Year,  " +
            "    org.REGION_ID AS RegionID,  " +
            "    0 AS RuleType,  " +
            "    st.id AS ServiceTypeID,  " +
            "    reg.Short_Name AS RegionName,  " +
            "    SYSDATE AS UpdateTime,  " +
            "    st.Name AS ServiceTypeName,  " +
            "    org.Name AS OrganizationName,  " +
            "    ind.Name AS IndustryName,  " +
            "    ostg.TEMPLATE_GROUP_ID AS TemplateGroupID,  " +
            "    '' AS TemplateGroupName,  " +
            "    org.Client_Code AS ClientCode,  " +
            "    '' AS DbName,  " +
            "    0 AS HaveCreateProject,  " +
            "    ea.Enterprise_Account_Set_ID AS EnterpriseAccountSetID,  " +
            "    1 AS StartPeriod,  " +
            "    12 AS EndPeriod,  " +
            "    ea.EFFECTIVE_DATE AS EffectiveDate,  " +
            "    ea.EXPIRED_DATE AS ExpiredDate  " +
            "FROM  " +
            "    ORGANIZATION org  " +
            "        JOIN  " +
            "    ENTERPRISE_ACCOUNT_SET_ORG ea ON org.id = ea.Organization_ID  " +
            "        JOIN  " +
            "    ORG_SERVICE_TEMPLATE_GROUP ostg ON org.id = ostg.ORGANIZATION_ID  " +
            "        JOIN  " +
            "    SERVICE_TYPE st ON ostg.SERVICE_TYPE_ID = st.id  " +
            "        JOIN  " +
            "    INDUSTRY ind ON org.Industry_ID = ind.id  " +
            "        JOIN  " +
            "    REGION reg ON org.Region_ID = reg.id " +
            "WHERE " +
            "  1=1" +
            "       <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
            "        AND org.Is_Active = 1 " +
            "        AND st.Is_Active = 1 " +
            "       <if test=\"serviceId != null and serviceId !='' \">AND ostg.Service_Type_ID=#{serviceId}</if>" +
            "ORDER BY ea.EFFECTIVE_DATE,org.Code" +
            "</script>")
    List<ProjectDisplayDto> getProjectFromEnterpriseAccountSetOrg(@Param("orgId") String orgId, @Param("serviceId") String serviceId);

    @Select("SELECT  " +
            "    PERIOD_ID AS periodId, STATUS AS status " +
            "FROM " +
            "    PROJECT_STATUS_MANAGE p " +
            "WHERE " +
            "    p.PROJECT_ID = #{projectId} " +
            "ORDER BY PERIOD_ID , STATUS")
    List<ProjectStatusManage> selectProjectAllStatus(String projectId);

    @Select("SELECT max(DB_NAME) as DbName FROM Project WHERE Year=#{year}")
    String maxDbName(Integer year);

    @Select("SELECT " +
            "  TEMPLATE_GROUP_ID " +
            "FROM PROJECT a " +
            "  JOIN ORG_SERVICE_TEMPLATE_GROUP b " +
            "    ON a.ORGANIZATION_ID = b.ORGANIZATION_ID " +
            "WHERE a.ID = #{projectId} " +
            "    AND b.SERVICE_TYPE_ID = #{serviceType}")
    Long getTemplateGroupIdByProject(@Param("projectId") String projectId, @Param("serviceType") Integer serviceType);
}