import java.io.*;
import java.sql.*;


import java.util.Random;

public class Main extends Thread {

    private static volatile boolean acabou;
    private static int n = 0;
    private static long trtot = 0;
    private volatile static int invoiceId = 0;

    private static synchronized void op(long tr) {
        if (acabou)
            return;
        n++;
        trtot += tr;
    }

    private final Connection c;
    private final Statement s;
    private Random r = new Random();

    public Main() throws Exception {
        c = DriverManager.getConnection("jdbc:postgresql://localhost/mydb");
        c.setAutoCommit(false);
        s = c.createStatement();
    }


    public void run() {
        int max = 1024;

        try {
            c.setSavepoint();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        for (int i = 0; i < max; i++) {
            try {
                int maxStock = randRange(25, 50);
                s.executeUpdate("insert into client (id, name, address) values (" + i + ", 'Cliente de numero " + i + "', 'Endereço aleatorio "
                        + i + "')");
                s.executeUpdate("insert into product (id, description, stock, stockMin, stockMax) values (" + i + ", 'Produto de numero" + i + "', " + maxStock + "," + randRange(5, 20) + ","
                        + maxStock + ")");
                c.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        try {
            int invIdInsert = 0;
            while (!acabou) {
                long antes = System.nanoTime();
                switch (r.nextInt(1)) {
                    case 0:

                        int numItems = r.nextInt(5) + 1;
                        int clientId = rand(max);
                        int numeroFatura = 0;

                        synchronized (this) {
                            numeroFatura = invoiceId;
                            invoiceId++;
                        }

                        s.executeUpdate("insert into invoice values (" + numeroFatura + "," + clientId + ")");

                        for (int item = 0; item < numItems; item++) {
                            int productId = rand(max);
                            int invoiceId = rand(max);
                            int supplierId = rand(max);

                            ResultSet rs = s.executeQuery("select stock, stockMin, stockMax from product p where p.id = " + productId);

                            rs.next();

                            int currentStock = rs.getInt(1);
                            int minStock = rs.getInt(2);
                            int maxStock = rs.getInt(3);

                            System.out.println("Currenstock: " + currentStock + "\t minStock: " + minStock + "\t maxStock: " + maxStock);

                        }

                        System.out.println("Inserting!");
                        break;

                    case 1:
                        // List names of products sold to some client
                        int pid = rand(1024);
                        int cid = rand(1024);

                        ResultSet rs = s.executeQuery("select p.description from product p, client c, invoice i" +
                                " where p.id = i.productId and i.clientId = c.id and p.id = " + pid + " " +
                                " and c.id = " + cid);
                        while (rs.next()) {
                            System.out.println(rs.getString(1));
                        }
                        rs.close();
                        break;

                    case 2: // TOP 10, sem materialized view
                        rs = s.executeQuery("select count(*) as quant, productId from invoice group by productId order by" +
                                " quant desc limit 10"); // top 10
                        while (rs.next()) {
                            System.out.println(rs.getInt(1) + ", " + rs.getInt(2));
                        }
                        rs.close();
                        break;

                    case 3: // ORDER
                        rs = s.executeQuery("select id, stock, stockMax from product p where p.stock < p.stockMin");
                        while (rs.next()) {
                            int productId = rs.getInt(1);
                            int currentStock = rs.getInt(2);
                            int stockMax = rs.getInt(4);

                            c.setSavepoint();
                            s.executeUpdate("insert into orderStock(productId, supplierId, itemQuantity) values (" + productId + ","
                                    + randRange(0, 50) + "," + (stockMax - currentStock) + ")");
                            c.commit();
                        }

                    case 4: // DELIVERY
                        rs = s.executeQuery("select id, productId, supplierId, itemQuantity from orderStock order by random() limit 5");

                        while (rs.next()) {
                            int orderId = rs.getInt(1);
                            int productId = rs.getInt(2);
                            int itemQuantity = rs.getInt(4);

                            ResultSet rsStock = s.executeQuery("select stock from product p where productId = " + productId);
                            rsStock.next();
                            int currentStock = rs.getInt(1);

                            s.executeUpdate("update product p set p.stock = " + (currentStock + itemQuantity) + " where p.id = " + productId);
                            s.executeUpdate("delete from orderStock os where os.id = " + orderId);
                        }
                        break;
                }

                long tr = System.nanoTime() - antes;
                op(tr);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int rand(int val) {
        return r.nextInt(val) | r.nextInt(val);
    }

    public static int randRange(int min, int max) {

        if (min >= max) {
            throw new IllegalArgumentException("max must be greater than min");
        }

        return (int) (Math.random() * ((max - min) + 1)) + min;
    }


    public void createTables(boolean useIndex) throws Exception {
        s.executeUpdate("drop table if exists client, product, invoice, invoiceLine, orderStock");
        s.executeUpdate("create table client (id int, name varchar, address varchar, PRIMARY KEY(id))");
        s.executeUpdate("create table product (id int, description varchar, stock int, stockMin int, stockMax int, PRIMARY KEY(id))");
        s.executeUpdate("create table invoice (id SERIAL, clientId int)");
        s.executeUpdate("create table invoiceLine (id SERIAL, invoiceId int, productId int)");
        s.executeUpdate("create table orderStock (id SERIAL, productId int, supplierId int, itemQuantity int)");
        c.commit();
    }

    public static void main(String[] args) throws Exception {

        Main m = new Main();

        File file = new File("/home/mbrito/mei/BDA/dados_lab_2.csv");
        if (!file.exists())
            file.createNewFile();

        FileWriter fileWriter = new FileWriter(file, true);
        BufferedWriter bw = new BufferedWriter(fileWriter);
        bw.write("Threads, Debito, Tempo de Resposta\n");


        for (int numThreads = 1; numThreads <= 1; numThreads *= 2) {
            m.createTables(true);
            System.out.println("Creating table!");
            Main[] t = new Main[numThreads];

            for (int i = 0; i < t.length; i++)
                t[i] = new Main();

            for (int i = 0; i < t.length; i++)
                t[i].start();

            int d = 45;


            Thread.sleep(d * 1000);

            acabou = true;

            for (int i = 0; i < t.length; i++)
                t[i].join();

            double debito = (n / (double) d);
            double tr = (trtot / (1e9 * n));
            System.out.println("debito = " + (n / (double) d));
            System.out.println("tr = " + (trtot / (1e9 * n)));

            bw.write(numThreads + ", " + debito + ", " + tr + "\n");
            System.out.println("Finishing cycle of threads " + numThreads);

            n = 0;
            trtot = 0;
            acabou = false;
        }

        bw.close();
    }

}