package db;

import java.sql.*;
import java.util.LinkedList;

public abstract class Db{
    private static String url = "jdbc:postgresql://localhost:5432/p2pbase";
    private static final String user = "p2pbase";
    private static final String pass = "qias0pdm";

    public static LinkedList<File> getFileList(String name) {
        String query = "SELECT MD5,NAME1, SIZE1 FROM FILES WHERE NAME1 LIKE ?";
        LinkedList<File> files = new LinkedList<>();
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,"%"+name+"%");
            ResultSet r = statement.executeQuery();
            while (r.next()){
                files.add(new File(r.getString(1),r.getString(2),r.getDouble(3)));
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return files;
    }

    public static String getIP(String MD5) {
        String query = "SELECT p.IP FROM PEER p JOIN HAS h ON (p.IP = h.IP and p.port = h.port) WHERE h.MD5 = ? ORDER BY p.NoConnections";
        String ip = null;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,MD5);
            ResultSet r = statement.executeQuery();
            while (r.next()){
                ip = r.getString(1);
                break;
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return ip;
    }

    public static boolean addFile(String IP, int port, File file) {
        String query;
        if(!exist(file)){
            query = "INSERT INTO FILES values (?,?,?)";
            try(
                    Connection con = DriverManager.getConnection(url, user, pass);
                    PreparedStatement statement = con.prepareStatement(query);
            ) {
                statement.setString(1,file.getMD5());
                statement.setString(2,file.getName());
                statement.setDouble(3,file.getSize());
                con.setAutoCommit(false);
                int a = statement.executeUpdate();
                if(a > 0) con.commit();
                else con.rollback();
            }catch (SQLException ex){
                ex.printStackTrace();
            }
        }
        if(existOnPeer(IP, port, file)){
            return false;
        }
        query = "INSERT INTO HAS values (?,?,?)";
        boolean inserted = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,IP);
            statement.setInt(2,port);
            statement.setString(3,file.getMD5());
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            inserted = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return inserted;
    }

    public static boolean addPeer(String IP, int port) {
        String query = "INSERT INTO PEER values (?,?,?)";
        boolean inserted = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,IP);
            statement.setInt(2,port);
            statement.setInt(3,0);
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            inserted = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return inserted;
    }

    public static boolean deletePeer(String IP, int port) {
        String query = "DELETE FROM PEER WHERE IP = ? AND PORT = ?";
        return update(IP, port, query);
    }

    public static boolean incrementNoConnections(String IP, int port) {
        String  query = "UPDATE PEER SET NOCONNECTIONS = NOCONNECTIONS + 1 WHERE IP = ? AND PORT = ?";
        return update(IP, port, query);
    }
    public static boolean decrementNoConnections(String IP, int port) {
        String query = "UPDATE PEER SET NOCONNECTIONS = NOCONNECTIONS - 1 WHERE IP = ? AND PORT = ?";
        return update(IP, port, query);
    }

    private static boolean update(String IP, int port, String query){
        boolean updated = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,IP);
            statement.setInt(2,port);
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            updated = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return updated;
    }



    public static boolean deleteFileOnPeer(String IP, int port, String MD5) {
        String query = "DELETE FROM HAS WHERE IP = ? AND PORT = ? AND MD5 = ?";
        boolean deleted = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,IP);
            statement.setInt(2,port);
            statement.setString(3, MD5);
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            deleted = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return deleted;
    }

    private static boolean exist(File file){
        String query = "SELECT * FROM FILES WHERE MD5 = ?";
        boolean finded = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,file.getMD5());
            ResultSet r = statement.executeQuery();
            while (r.next()){
                finded = true;
                break;
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return finded;
    }

    private static boolean existOnPeer(String ip, int port, File file){
        String query = "SELECT * FROM HAS WHERE IP = ? AND PORT = ? AND MD5 = ?";
        boolean finded = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1,ip);
            statement.setInt(2,port);
            statement.setString(3,file.getMD5());
            ResultSet r = statement.executeQuery();
            while (r.next()){
                finded = true;
                break;
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return finded;
    }

    public static void setUrl(String IP) {
        Db.url = "jdbc:postgresql://"+IP+":5432/p2pbase";
    }

    //under this line only testing methods
    public static LinkedList<File> getAllFiles() {
        String query = "SELECT * FROM FILES";
        LinkedList<File> files = new LinkedList<>();
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            ResultSet r = statement.executeQuery();
            while (r.next()){
                files.add(new File(r.getString(1),r.getString(2),r.getDouble(3)));
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return files;
    }

    public static LinkedList<String> getAllFilesOnPeers() {
        String query = "SELECT * FROM HAS";
        LinkedList<String> files = new LinkedList<>();
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            ResultSet r = statement.executeQuery();
            while (r.next()){
                files.add("Peer : "+r.getString(1)+":"+r.getInt(2) + " File: "+r.getString(3));
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return files;
    }

    public static LinkedList<String> getAllPeers() {
        String query = "SELECT * FROM PEER";
        LinkedList<String> peer = new LinkedList<>();
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            ResultSet r = statement.executeQuery();
            while (r.next()){
                peer.add("Peer : "+r.getString(1)+":"+r.getInt(2));
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return peer;
    }

    public static boolean deleteFile(String MD5){
        String query = "DELETE FROM FILES WHERE MD5 = ?";
        boolean deleted = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            statement.setString(1, MD5);
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            deleted = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return deleted;
    }
    public static boolean deleteAllFileOnPeer() {
        String query = "DELETE FROM HAS";
        return delete(query);
    }

    public static boolean deleteAllPeers() {
        String query = "DELETE FROM PEER";
        return delete(query);
    }
    public static boolean deleteAllFiles(){
        String query = "DELETE FROM FILES";
        return delete(query);
    }
    public  static boolean delete(String query){
        boolean deleted = false;
        try(
                Connection con = DriverManager.getConnection(url, user, pass);
                PreparedStatement statement = con.prepareStatement(query);
        ) {
            con.setAutoCommit(false);
            int a = statement.executeUpdate();
            deleted = a > 0;
            if(a > 0) con.commit();
            else con.rollback();
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return deleted;
    }
}