package CHAPTER15;

import javax.swing.*;
import java.awt.*;
import java.sql.*;

public class REAL_HospitalReservation extends JFrame {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/hospital_db";
    private static final String DB_USER = "root"; 
    private static final String DB_PASSWORD = "123456789"; 

    private JTextField nameField, ageField, genderField, addressField, phoneField, emailField;
    private JTextField doctorNameField, specialtyField, doctorPhoneField, doctorEmailField;
    // 예약관리 탭 관련 필드 (예약 ID 추가)
    private JTextField appointmentIdField, patientIdField, doctorIdField, appointmentDateField;
    
    public REAL_HospitalReservation() {
        setTitle("병원 예약 시스템");
        setSize(600, 400);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);

        initializeDatabase();

        JTabbedPane tabbedPane = new JTabbedPane();
        tabbedPane.addTab("환자 관리", createPatientPanel());
        tabbedPane.addTab("의사 관리", createDoctorPanel());
        tabbedPane.addTab("예약 관리", createAppointmentPanel());

        add(tabbedPane);
    }
    
    // 1. 환자 관리 패널
    private JPanel createPatientPanel() {
        JPanel panel = new JPanel(new GridLayout(7, 2));
        panel.add(new JLabel("이름:"));
        nameField = new JTextField();
        panel.add(nameField);

        panel.add(new JLabel("나이:"));
        ageField = new JTextField();
        panel.add(ageField);

        panel.add(new JLabel("성별(남/여):"));
        genderField = new JTextField();
        panel.add(genderField);

        panel.add(new JLabel("주소:"));
        addressField = new JTextField();
        panel.add(addressField);

        panel.add(new JLabel("전화번호:"));
        phoneField = new JTextField();
        panel.add(phoneField);

        panel.add(new JLabel("이메일:"));
        emailField = new JTextField();
        panel.add(emailField);

        JButton addButton = new JButton("환자 등록");
        addButton.addActionListener(e -> addPatient());
        JButton clsButton = new JButton("초기화");
        clsButton.addActionListener(e -> clsPatient());
        panel.add(addButton);
        panel.add(clsButton);

        return panel;
    }
    
    // 2. 의사 관리 패널
    private JPanel createDoctorPanel() {
        JPanel panel = new JPanel(new GridLayout(5, 2));
        panel.add(new JLabel("이름:"));
        doctorNameField = new JTextField();
        panel.add(doctorNameField);

        panel.add(new JLabel("전문 과목:"));
        specialtyField = new JTextField();
        panel.add(specialtyField);

        panel.add(new JLabel("전화번호:"));
        doctorPhoneField = new JTextField();
        panel.add(doctorPhoneField);

        panel.add(new JLabel("이메일:"));
        doctorEmailField = new JTextField();
        panel.add(doctorEmailField);

        JButton addButton = new JButton("의사 등록");
        addButton.addActionListener(e -> addDoctor());
        panel.add(addButton);

        JButton clsButton = new JButton("초기화");
        clsButton.addActionListener(e -> clsDoctor());
        panel.add(clsButton);

        return panel;
    }
    
    // 3. 예약 관리 패널 (예약 ID, 환자 ID, 의사 ID, 예약 날짜 입력)
    //     버튼: 예약 등록, 예약 취소, 예약 변경, 초기화
    private JPanel createAppointmentPanel() {
        JPanel panel = new JPanel(new GridLayout(6, 2));
        
        panel.add(new JLabel("예약 ID:"));
        appointmentIdField = new JTextField();
        panel.add(appointmentIdField);
        
        panel.add(new JLabel("환자 ID:"));
        patientIdField = new JTextField();
        panel.add(patientIdField);
        
        panel.add(new JLabel("의사 ID:"));
        doctorIdField = new JTextField();
        panel.add(doctorIdField);
        
        panel.add(new JLabel("예약 날짜(YYYY-MM-DD HH:MM:SS):"));
        appointmentDateField = new JTextField();
        panel.add(appointmentDateField);
        
        JButton bookButton = new JButton("예약 등록");
        bookButton.addActionListener(e -> addAppointment());
        JButton cancelButton = new JButton("예약 취소");
        cancelButton.addActionListener(e -> cancelAppointment());
        panel.add(bookButton);
        panel.add(cancelButton);
        
        JButton updateButton = new JButton("예약 변경");
        updateButton.addActionListener(e -> updateAppointment());
        JButton clsButton = new JButton("초기화");
        clsButton.addActionListener(e -> clsAppointment());
        panel.add(updateButton);
        panel.add(clsButton);
        
        return panel;
    }
    
    // 예약 등록
    private void addAppointment() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 신규 예약 등록 시 예약ID는 auto-generated되므로 사용하지 않음
            int patientId = Integer.parseInt(patientIdField.getText());
            int doctorId = Integer.parseInt(doctorIdField.getText());
            String appointmentDate = appointmentDateField.getText();

            // 환자 및 의사 존재 여부 확인
            if (!isPatientExists(conn, patientId)) {
                JOptionPane.showMessageDialog(this, "환자 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            if (!isDoctorExists(conn, doctorId)) {
                JOptionPane.showMessageDialog(this, "의사 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // 중복 예약 방지 (같은 의사, 같은 시간)
            if (isAppointmentDuplicate(conn, doctorId, appointmentDate)) {
                JOptionPane.showMessageDialog(this, "이미 해당 시간에 예약이 존재합니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // 예약 등록
            String sql = "INSERT INTO appointments (patient_id, doctor_id, appointment_date) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, patientId);
                pstmt.setInt(2, doctorId);
                pstmt.setString(3, appointmentDate);
                pstmt.executeUpdate();
                JOptionPane.showMessageDialog(this, "예약 등록 완료!");
            }
        } catch (SQLException | NumberFormatException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "입력 오류: 올바른 값을 입력하세요.", "오류", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    // 예약 취소 기능
    private void cancelAppointment() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            int appointmentId = Integer.parseInt(appointmentIdField.getText());
            if (!isAppointmentExists(conn, appointmentId)) {
                JOptionPane.showMessageDialog(this, "예약 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            String sql = "UPDATE appointments SET status = '취소됨' WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, appointmentId);
                int updated = pstmt.executeUpdate();
                if (updated > 0) {
                    JOptionPane.showMessageDialog(this, "예약 취소 완료!");
                } else {
                    JOptionPane.showMessageDialog(this, "예약 취소에 실패했습니다.", "오류", JOptionPane.ERROR_MESSAGE);
                }
            }
        } catch (SQLException | NumberFormatException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "입력 오류: 올바른 값을 입력하세요.", "오류", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    // 예약 변경 기능
    private void updateAppointment() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            int appointmentId = Integer.parseInt(appointmentIdField.getText());
            int patientId = Integer.parseInt(patientIdField.getText());
            int doctorId = Integer.parseInt(doctorIdField.getText());
            String appointmentDate = appointmentDateField.getText();

            // 예약 존재 여부 확인
            if (!isAppointmentExists(conn, appointmentId)) {
                JOptionPane.showMessageDialog(this, "예약 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // 환자 및 의사 존재 여부 확인
            if (!isPatientExists(conn, patientId)) {
                JOptionPane.showMessageDialog(this, "환자 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            if (!isDoctorExists(conn, doctorId)) {
                JOptionPane.showMessageDialog(this, "의사 ID가 존재하지 않습니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // 변경하려는 예약시간에 동일 의사의 다른 예약이 있는지 확인 (현재 예약은 제외)
            if (isAppointmentDuplicateForUpdate(conn, appointmentId, doctorId, appointmentDate)) {
                JOptionPane.showMessageDialog(this, "이미 해당 시간에 예약이 존재합니다!", "오류", JOptionPane.ERROR_MESSAGE);
                return;
            }
            String sql = "UPDATE appointments SET patient_id = ?, doctor_id = ?, appointment_date = ?, status = '예약됨' WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, patientId);
                pstmt.setInt(2, doctorId);
                pstmt.setString(3, appointmentDate);
                pstmt.setInt(4, appointmentId);
                int updated = pstmt.executeUpdate();
                if (updated > 0) {
                    JOptionPane.showMessageDialog(this, "예약 변경 완료!");
                } else {
                    JOptionPane.showMessageDialog(this, "예약 변경에 실패했습니다.", "오류", JOptionPane.ERROR_MESSAGE);
                }
            }
        } catch (SQLException | NumberFormatException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "입력 오류: 올바른 값을 입력하세요.", "오류", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    // 환자 존재 여부 확인 메서드
    private boolean isPatientExists(Connection conn, int patientId) throws SQLException {
        String sql = "SELECT id FROM patients WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, patientId);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        }
    }
    
    // 의사 존재 여부 확인 메서드
    private boolean isDoctorExists(Connection conn, int doctorId) throws SQLException {
        String sql = "SELECT id FROM doctors WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, doctorId);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        }
    }
    
    // 예약 중복 여부 확인 (등록 시: 같은 의사, 같은 시간)
    private boolean isAppointmentDuplicate(Connection conn, int doctorId, String appointmentDate) throws SQLException {
        String sql = "SELECT id FROM appointments WHERE doctor_id = ? AND appointment_date = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, doctorId);
            pstmt.setString(2, appointmentDate);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        }
    }
    
    // 예약 존재 여부 확인 (예약 ID로)
    private boolean isAppointmentExists(Connection conn, int appointmentId) throws SQLException {
        String sql = "SELECT id FROM appointments WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, appointmentId);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        }
    }
    
    // 예약 변경 시, 현재 예약(ID 제외)과 동일한 의사와 예약시간의 예약이 존재하는지 확인
    private boolean isAppointmentDuplicateForUpdate(Connection conn, int appointmentId, int doctorId, String appointmentDate) throws SQLException {
        String sql = "SELECT id FROM appointments WHERE doctor_id = ? AND appointment_date = ? AND id <> ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, doctorId);
            pstmt.setString(2, appointmentDate);
            pstmt.setInt(3, appointmentId);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        }
    }
    
    // 데이터베이스 초기화 (테이블 자동 생성)
    private void initializeDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {

            String createPatientsTable = "CREATE TABLE IF NOT EXISTS patients (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY, " +
                    "name VARCHAR(100) NOT NULL, " +
                    "age INT NOT NULL, " +
                    "gender ENUM('남', '여') NOT NULL, " +
                    "address TEXT NOT NULL, " +
                    "phone VARCHAR(20) NOT NULL UNIQUE, " +
                    "email VARCHAR(100) UNIQUE)";

            String createDoctorsTable = "CREATE TABLE IF NOT EXISTS doctors (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY, " +
                    "name VARCHAR(100) NOT NULL, " +
                    "specialty VARCHAR(100) NOT NULL, " +
                    "phone VARCHAR(20) NOT NULL UNIQUE, " +
                    "email VARCHAR(100) UNIQUE)";

            String createAppointmentsTable = "CREATE TABLE IF NOT EXISTS appointments (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY, " +
                    "patient_id INT NOT NULL, " +
                    "doctor_id INT NOT NULL, " +
                    "appointment_date DATETIME NOT NULL, " +
                    "status ENUM('예약됨', '취소됨', '완료') DEFAULT '예약됨', " +
                    "FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE, " +
                    "FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE)";

            stmt.executeUpdate(createPatientsTable);
            stmt.executeUpdate(createDoctorsTable);
            stmt.executeUpdate(createAppointmentsTable);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 환자 등록
    private void addPatient() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(
                     "INSERT INTO patients (name, age, gender, address, phone, email) VALUES (?, ?, ?, ?, ?, ?)")) {
            pstmt.setString(1, nameField.getText());
            pstmt.setInt(2, Integer.parseInt(ageField.getText()));
            pstmt.setString(3, genderField.getText());
            pstmt.setString(4, addressField.getText());
            pstmt.setString(5, phoneField.getText());
            pstmt.setString(6, emailField.getText());
            pstmt.executeUpdate();
            JOptionPane.showMessageDialog(this, "환자 등록 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 의사 등록
    private void addDoctor() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(
                     "INSERT INTO doctors (name, specialty, phone, email) VALUES (?, ?, ?, ?)")) {
            pstmt.setString(1, doctorNameField.getText());
            pstmt.setString(2, specialtyField.getText());
            pstmt.setString(3, doctorPhoneField.getText());
            pstmt.setString(4, doctorEmailField.getText());
            pstmt.executeUpdate();
            JOptionPane.showMessageDialog(this, "의사 등록 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 환자 입력 필드 초기화 메서드
    private void clsPatient() {
        nameField.setText("");
        ageField.setText("");
        genderField.setText("");
        addressField.setText("");
        phoneField.setText("");
        emailField.setText("");
    }
    
    // 의사 입력 필드 초기화 메서드
    private void clsDoctor() {
        doctorNameField.setText("");
        specialtyField.setText("");
        doctorPhoneField.setText("");
        doctorEmailField.setText("");
    }
    
    // 예약 입력 필드 초기화 메서드
    private void clsAppointment() {
        appointmentIdField.setText("");
        patientIdField.setText("");
        doctorIdField.setText("");
        appointmentDateField.setText("");
    }
    
    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> new REAL_HospitalReservation().setVisible(true));
    }}
