import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * Notes for PA
 * 
 * Use Semi-haive evaluation and logarithmic convergence
 * Use information from table "transfer" 
 * - transfer (src: string, tft: string, timestamp: date, amount: float)
 * Do NOT use recursive SQL queries
 * 
 */
public class FTM {
    public static void main(String[] args){
        String connURL = args[0]; // jdbc:postgresql://localhost/pa2
        String userName = args[1]; // postgres
        String password = args[2];
        try{
            Connection conn = DriverManager.getConnection(connURL, userName, password);
            java.sql.Statement state = conn.createStatement();
            // drop influence table
            state.execute("DROP TABLE IF EXISTS influence");
            // create infleunce table
            state.execute("CREATE TABLE influence (who varchar(255), whom varchar(255), FOREIGN KEY(who) REFERENCES customer(name), FOREIGN KEY(whom) REFERENCES customer(name))");
            state.execute("DROP TABLE IF EXISTS funds");
            state.execute("DROP TABLE IF EXISTS delta");
            state.execute("DROP TABLE IF EXISTS t_old");
            // create funds table and populate
            state.execute("CREATE TABLE funds(src varchar(255), tgt varchar(255))");
            state.execute("INSERT INTO funds (SELECT c1.name, c2.name from customer as c1, customer as c2, depositor as d1, depositor as d2, transfer as t WHERE c1.name = d1.cname and c2.name = d2.cname and d1.ano = t.src and d2.ano = t.tgt)");

            // initiate delta table and influence table
            state.execute("CREATE TABLE delta(src varchar(255), tgt varchar(255))");
            state.execute("INSERT INTO delta (SELECT src, tgt from funds)"); // copy 'G' = funds
            state.execute("INSERT INTO influence (SELECT src, tgt from funds)");
            
            // initial check if delta is empty
            java.sql.PreparedStatement empty_check = conn.prepareStatement("SELECT COUNT(*) AS cnt FROM delta");
            ResultSet rs = empty_check.executeQuery();
            rs.next();
            // System.out.println(rs.next());
            Boolean isEmpty = rs.getInt("cnt") == 0;
            // int count = 0;
            // System.out.println("count: " + rs.getInt("cnt"));
            state.execute("CREATE TABLE t_old(src varchar(255), tgt varchar(255))");
            while(!isEmpty){
                // System.out.println("iter: " + count);
                state.execute("INSERT INTO t_old (SELECT * FROM influence)");

                state.execute("INSERT INTO influence ((SELECT * FROM influence) UNION (SELECT x.src, y.whom FROM delta AS x, influence AS y WHERE x.tgt = y.who) UNION (SELECT x.who, y.tgt FROM influence AS x, delta AS y WHERE x.whom = y.src))");

                // repopulate delta
                state.execute("DELETE FROM ONLY delta");
                state.execute("INSERT INTO delta ((SELECT * FROM influence) EXCEPT (SELECT * FROM t_old))");

                // empty out t_old
                state.execute("DELETE FROM ONLY t_old");
                // check if delta is empty
                rs = empty_check.executeQuery();
                rs.next();
                isEmpty = rs.getInt("cnt") == 0;

                // count++;
            }
            // drop extra tables 
            state.execute("DROP TABLE IF EXISTS funds");
            state.execute("DROP TABLE IF EXISTS delta");
            state.execute("DROP TABLE IF EXISTS t_old");
        } catch(Exception e){
            e.printStackTrace();
        }
    }
}