package com.kapitsa;
//Created by Luladik on 2/20/2016.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBManager {

    private static final String URL = "jdbc:mysql://localhost:3306/food?autoReconnect=true&useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    private static Connection getDBConnection() {
        Connection dbConnection;
        try {
            Class.forName(DRIVER);
            System.out.println("Driver connected");
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        try {
            dbConnection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("Successfully Connected");
            return dbConnection;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    public static void init() throws SQLException {
        String createDb = "CREATE DATABASE IF NOT EXISTS shopmanager";
        String createShops = "CREATE TABLE IF NOT EXISTS Shops (" +
                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                                "Title VARCHAR(30) NOT NULL" +
                                ");";
        String createCategories = "CREATE TABLE IF NOT EXISTS Categories (" +
                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                                "shop_id INT UNSIGNED NOT NULL," +
                                "Title VARCHAR(20) NOT NULL," +
                                "Description VARCHAR(40) NOT NULL," +
                                "FOREIGN KEY(shop_id) REFERENCES Shops(id)" +
                                ");";
        String createItems = "CREATE TABLE IF NOT EXISTS Items (" +
                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                                "category_id INT UNSIGNED NOT NULL," +
                                "Title VARCHAR(30) NOT NULL," +
                                "Price DECIMAL(20) NOT NULL," +
                                "Status ENUM('Available', 'Absent', 'Expected')," +
                                "FOREIGN KEY(category_id) REFERENCES Categories(id)" +
                                ");";

        try(final Statement statement = getDBConnection().createStatement()) {
            statement.execute(createDb);
            statement.execute("USE shopmanager;");
            statement.execute(createShops);
            statement.execute(createCategories);
            statement.execute(createItems);
            System.out.println("Tables created successfully");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Error when creating tables");
        }
    }
}