@Repository
@RequiredArgsConstructor
public class MyProjectRecordsDao {

    private final DefaultDSLContext dslContext;

    @Transactional
    public List<ProjectWithCountDto> fetchAllProjectsWithCount() {
        return dslContext
                .select(
                        PROJECTS.PROJECT_NAME,
                        DSL.count(PROJECT_RECORDS.ID)
                )
                .from(PROJECTS)
                .leftJoin(PROJECT_RECORDS)
                .on(PROJECTS.PROJECT_NAME.eq(PROJECT_RECORDS.PROJECT_NAME))
                .groupBy(PROJECTS.PROJECT_NAME)
                .fetchInto(ProjectWithCountDto.class);
    }

    @Transactional
    public void saveNewRecord(String projectName, String storedData) {

        dslContext.insertInto(PROJECTS)
                .columns(PROJECTS.PROJECT_NAME)
                .values(projectName)
                .onDuplicateKeyIgnore() //ignore insert if the record exists
                .execute();

        dslContext.insertInto(PROJECT_RECORDS)
                .columns(PROJECT_RECORDS.PROJECT_NAME, PROJECT_RECORDS.STORED_DATA)
                .values(projectName, storedData)
                .execute();
    }


}