package gc.mda.signal_batch.domain.ship.repository;

import gc.mda.signal_batch.domain.ship.dto.ShipImageDto;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.util.List;

@Slf4j
@Repository
public class ShipImageRepository {

    private final JdbcTemplate queryJdbcTemplate;

    public ShipImageRepository(@Qualifier("queryJdbcTemplate") JdbcTemplate queryJdbcTemplate) {
        this.queryJdbcTemplate = queryJdbcTemplate;
    }

    private static final String FIND_BY_IMO_SQL = """
        SELECT pic_id, copyright, dateofphoto
        FROM signal.t_snp_ship_img
        WHERE lrno = ?
        ORDER BY dateofphoto DESC NULLS LAST, pic_id DESC
        """;

    private static final RowMapper<ShipImageRawData> ROW_MAPPER = (rs, rowNum) -> {
        ShipImageRawData data = new ShipImageRawData();
        data.picId = rs.getInt("pic_id");
        data.copyright = rs.getString("copyright");
        Date dateOfPhoto = rs.getDate("dateofphoto");
        data.dateOfPhoto = dateOfPhoto != null ? dateOfPhoto.toLocalDate() : null;
        return data;
    };

    /**
     * IMO(lrno)로 이미지 정보 조회
     *
     * @param imo IMO 번호
     * @return 이미지 raw 데이터 목록
     */
    public List<ShipImageRawData> findByImo(Integer imo) {
        return queryJdbcTemplate.query(FIND_BY_IMO_SQL, ROW_MAPPER, imo);
    }

    /**
     * Repository 내부에서 사용하는 Raw 데이터 클래스
     */
    public static class ShipImageRawData {
        public Integer picId;
        public String copyright;
        public java.time.LocalDate dateOfPhoto;
    }
}
