package ru.rsreu.koklyukov.filesharing.datalayer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import ru.rsreu.koklyukov.filesharing.File;
import ru.rsreu.koklyukov.filesharing.User;
import ru.rsreu.koklyukov.filesharing.UserGroups;

public class OracleDAO {

    private static Connection con;
    private static Statement st;

    private OracleDAO() {
    }

    private static void Connect() {
        Locale.setDefault(Locale.ENGLISH);
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "UserJavaProject";
        String password = "root";
        try {
            con = DriverManager.getConnection(url, user, password);
            //System.out.println("Connected!!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void Disconnect() {
        try {
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void addUser(User user) {

        Connect();
        try {
            st = con.createStatement();
            String query = String.format("INSERT INTO USERS (ID, UserGroup, NAME, PASSWORD) VALUES(%d, '%s', '%s', '%s')",
                    user.getID(), user.getGroup(), user.getName(), user.getPassword());
            st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Disconnect();
        }
    }

    public static void editUser(User user) {
        Connect();
        try {
            st = con.createStatement();
            String query = String.format("UPDATE USERS SET UserGroup = '%s', NAME = '%s', PASSWORD = '%s' WHERE ID = %d",
                    user.getGroup(), user.getName(), user.getPassword(), user.getID());
            st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Disconnect();
        }
    }

    public static void deleteUser(User user) {
        Connect();
        try {
            st = con.createStatement();
            String query = String.format("DELETE FROM USERS WHERE ID = %d",
                    user.getID());
            st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Disconnect();
        }
    }

    public static List<User> getUsers() {
        Connect();
        List<User> listUsers = new ArrayList<User>();
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM USERS");
            while(rs.next()) {
                int ID = rs.getInt("ID");
                String name = rs.getString("NAME");
                String password = rs.getString("PASSWORD");
                String groupString = rs.getString("UserGroup");
                User user = new User(name, password);
                user.setID(ID);
                UserGroups userGroup = UserGroups.getGroup(groupString);
                user.setGroup(userGroup);
                listUsers.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            Disconnect();
        }
        return listUsers;
    }

    public static void addFile(File file) {

        Connect();
        try {
            st = con.createStatement();
            String query = String.format("INSERT INTO FILES (ID, NAME, DESCRIPTION, FileSize, UserUploaded, DownloadsCount)" +
                    "VALUES(%d, '%s', '%s', %d, %d, %d)",
                    file.getID(), file.getName(), file.getDescription(), file.getSize(),
                    file.getUserUploaded(), file.getDownloadsCount());
            st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Disconnect();
        }
    }
}