package CHAP16;

import java.sql.*;
import java.util.Scanner;

public class LAB02_ShoppingMallManagement {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/";
    private static final String DB_USER = "root"; // MySQL 사용자명
    private static final String DB_PASSWORD = "123456789"; // MySQL 비밀번호
    private static final String DATABASE_NAME = "shop_db";    

    // ✅ 데이터베이스 및 테이블 자동 생성
    private static void initializeDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {

            String createDatabase = "CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME;
            stmt.executeUpdate(createDatabase);

            try (Connection dbConn = DriverManager.getConnection(DB_URL + DATABASE_NAME, DB_USER, DB_PASSWORD);
                 Statement dbStmt = dbConn.createStatement()) {

                String createProductsTable = "CREATE TABLE IF NOT EXISTS products ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY, "
                        + "name VARCHAR(100) NOT NULL, "
                        + "description TEXT, "
                        + "price DECIMAL(10,2) NOT NULL, "
                        + "stock INT NOT NULL"
                        + ")";
                dbStmt.executeUpdate(createProductsTable);              

                System.out.println("[✅ MySQL 초기화 완료] 데이터베이스 및 테이블이 준비되었습니다.");
            }
        } catch (SQLException e) {
            System.err.println("MySQL 초기화 오류: " + e.getMessage());
        }
    }

    // ✅ 상품 등록
    private static void addProduct(Scanner scanner) {
        System.out.println("\n[ 상품 등록 ]");
        System.out.print("상품명: ");
        String name = scanner.nextLine();
        System.out.print("설명: ");
        String description = scanner.nextLine();
        System.out.print("가격: ");
        double price = scanner.nextDouble();
        System.out.print("재고 수량: ");
        int stock = scanner.nextInt();
        scanner.nextLine();

        String sql = "INSERT INTO products (name, description, price, stock) VALUES (?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(DB_URL + DATABASE_NAME, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, description);
            pstmt.setDouble(3, price);
            pstmt.setInt(4, stock);
            pstmt.executeUpdate();
            System.out.println("상품 등록 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ✅ 상품 정보 수정
    private static void updateProduct(Scanner scanner) {
        System.out.print("\n[ 수정할 상품 ID 입력 ]: ");
        int productId = scanner.nextInt();
        scanner.nextLine();
        System.out.print("새 가격: ");
        double price = scanner.nextDouble();
        System.out.print("새 재고 수량: ");
        int stock = scanner.nextInt();
        scanner.nextLine();

        String sql = "UPDATE products SET price = ?, stock = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL + DATABASE_NAME, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, price);
            pstmt.setInt(2, stock);
            pstmt.setInt(3, productId);
            pstmt.executeUpdate();
            System.out.println("상품 정보 수정 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ✅ 상품 삭제
    private static void deleteProduct(Scanner scanner) {
        System.out.print("\n[ 삭제할 상품 ID 입력 ]: ");
        int productId = scanner.nextInt();
        scanner.nextLine();

        String sql = "DELETE FROM products WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL + DATABASE_NAME, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            pstmt.executeUpdate();
            System.out.println("상품 삭제 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ✅ 상품 검색
    private static void searchProduct(Scanner scanner) {
        System.out.print("\n[ 검색할 상품명 입력 ]: ");
        String name = scanner.nextLine();

        String sql = "SELECT * FROM products WHERE name LIKE ?";

        try (Connection conn = DriverManager.getConnection(DB_URL + DATABASE_NAME, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + name + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") +
                        ", 이름: " + rs.getString("name") +
                        ", 가격: " + rs.getDouble("price") +
                        ", 재고: " + rs.getInt("stock"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }   

    public static void main(String[] args) {
        initializeDatabase();
        Scanner scanner = new Scanner(System.in);

        while (true) {
            System.out.println("\n===== 쇼핑몰 상품 관리 시스템 =====");
            System.out.println("1. 상품 등록");
            System.out.println("2. 상품 정보 수정");
            System.out.println("3. 상품 삭제");
            System.out.println("4. 상품 검색");
            System.out.println("5. 종료");
            System.out.print("메뉴 선택: ");

            int choice = scanner.nextInt();
            scanner.nextLine(); // 개행 문자 제거

            switch (choice) {
                case 1:
                    addProduct(scanner);
                    break;
                case 2:
                    updateProduct(scanner);
                    break;
                case 3:
                    deleteProduct(scanner);
                    break;
                case 4:
                    searchProduct(scanner);
                    break;
                case 5:                    
                    System.out.println("프로그램 종료.");
                    return;
                default:
                    System.out.println("잘못된 입력입니다. 다시 선택하세요.");
            }
        }
    }
}
