package com.example.demo.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import com.example.demo.models.Ispit;

public class IspitRepo implements IspitRepoInterface {

    @Override
    public double dohvatiProsekOcenaZaStudenta(String indeks) {
        try (Connection conn = DB.source().getConnection();
        PreparedStatement stm = conn.prepareStatement("select avg(ocena) as prosek from ispiti where student = ?");) {
            stm.setString(1, indeks);
            ResultSet rs = stm.executeQuery();
            if(rs.next()){
                return rs.getDouble("prosek");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Ispit> dohvatiIspiteUIntervalu(LocalDate datumOd, LocalDate datumDo) {
        try (Connection conn = DB.source().getConnection();
        PreparedStatement stm = conn.prepareStatement("select * from ispiti where datum >= ? and datum <= ?");) {
            stm.setDate(1, java.sql.Date.valueOf(datumOd));
            stm.setDate(2, java.sql.Date.valueOf(datumDo));
            ResultSet rs = stm.executeQuery();

            List<Ispit> ispiti = new ArrayList<>();

            while(rs.next()){
                Ispit i = new Ispit(
                    rs.getInt("idI"), rs.getString("sifra"), 
                    rs.getString("student"), rs.getDate("datum").toLocalDate(), rs.getInt("ocena"));
                ispiti.add(i);
            }
            return ispiti;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Ispit> dodavanjeIspitaZaStudenta(Ispit i) {
        try (Connection conn = DB.source().getConnection();
        //proveravamo da li ima ocena za taj predmet i tog studenta
        PreparedStatement stm = conn.prepareStatement("select * from ispiti where sifra = ?  and student = ?");) {
            stm.setString(1, i.getSifra());
            stm.setString(2, i.getStudent());
            ResultSet rs = stm.executeQuery();

            if(rs.next()){
                //radimo update ako vec ima ocena
                try(PreparedStatement stm2 = conn.prepareStatement("update ispiti set datum = ? , ocena = ? where idI = ?")){
                    stm2.setDate(1, java.sql.Date.valueOf(i.getDatum()));
                    stm2.setInt(2, i.getOcena());
                    stm2.setInt(3, rs.getInt("idI"));
                    stm2.executeUpdate();
                }
            }
            else{
                //radimo insert ako nema
                try(PreparedStatement stm2 = conn.prepareStatement("insert into ispiti (sifra, student, datum, ocena) values (?,?,?,?)")){
                    stm2.setString(1, i.getSifra());
                    stm2.setString(2, i.getStudent());
                    stm2.setDate(3, java.sql.Date.valueOf(i.getDatum()));
                    stm2.setInt(4, i.getOcena());
                    
                    stm2.executeUpdate();
                }
            }

            //dohvatamo sve ispite za studenta
            try(PreparedStatement stm3 = conn.prepareStatement("select * from ispiti where student = ?")) {
                stm3.setString(1, i.getStudent());
                ResultSet rs2 = stm3.executeQuery();

                List<Ispit> ispiti = new ArrayList<>();

                while(rs2.next()){
                    Ispit ispit = new Ispit(
                        rs2.getInt("idI"), rs2.getString("sifra"), 
                        rs2.getString("student"), rs2.getDate("datum").toLocalDate(), rs2.getInt("ocena"));
                    ispiti.add(ispit);
                }
                return ispiti;
            } 
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null; 
    }
        
}

