package JDBC;

import pojo.ParkingRecord;
import pojo.ParkingSpot;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ParkingSpotJDBC {
    public ParkingSpotJDBC() {
        super();
    }

    public Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/parkinglot", "root", "root");
    }

    public void setSpotStatus(int spot_id) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("UPDATE parkingspotinformation " +
                "SET occupancy_status=1 " +
                "WHERE parking_spot_id=?");
        ps.setInt(1, spot_id);
        ps.executeUpdate();
        conn.close();
    }

    public void resetSpotStatus(int spot_id) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("UPDATE parkingspotinformation " +
                "SET occupancy_status=0 " +
                "WHERE parking_spot_id=?");
        ps.setInt(1, spot_id);
        ps.executeUpdate();
        conn.close();
    }

    public List<ParkingSpot> selectAllSpots() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM parkingspotinformation");
        List<ParkingSpot> spots = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            ParkingSpot spot = new ParkingSpot(
                    rs.getInt("parking_spot_id"),
                    rs.getString("spot_type"),
                    rs.getInt("occupancy_status"),
                    rs.getString("location"),
                    rs.getDouble("fee_standard")
            );
            spots.add(spot);
        }
        conn.close();
        return spots;
    }

    public ParkingSpot getSpot(int spot_id) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT *\n" +
                "FROM parkingspotinformation\n" +
                "WHERE parking_spot_id = ?\n"
        );
        ps.setInt(1, spot_id);
        ResultSet rs = ps.executeQuery();
        ParkingSpot pr = null;
        while(rs.next()) {
            pr = new ParkingSpot(
                    rs.getInt("parking_spot_id"),
                    rs.getString("spot_type"),
                    rs.getByte("occupancy_status"),
                    rs.getString("location"),
                    rs.getDouble("fee_standard")

            );
        }
        conn.close();
        return pr;
    }
}



