import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.*;

class DbMigration {
    // constants
    static final boolean CONFIG_PRINT_DB = false;
    static final String CSV_SEPARATOR = ",";
    static final String DB_URL = "jdbc:postgresql:users";
    static final String SQL_GET_USERS = "SELECT * FROM users;";
    static final String SQL_UPSERT_USER =
            """
              INSERT INTO users VALUES (?, ?, ?, ?, ?)
              ON CONFLICT (ID)
              DO UPDATE SET
                Mail = EXCLUDED.Mail,
                CanRead = EXCLUDED.CanRead,
                CanWrite = EXCLUDED.CanWrite,
                CanDelete = EXCLUDED.CanDelete
              ;
            """;

    // user
    public record User(int id, String mail, Role role) {
        public String toString() {
            return this.id + " " + this.mail + " " + this.role;
        }
    }

    // role
    public record Role(boolean read, boolean write, boolean delete) {
        static final Role Default = new Role(false, false, false);
    }

    // csv
    public static String[][] LoadCSV(String filename) throws IOException {
        var path = Paths.get(filename);
        return Files.lines(path)
                .skip(1)
                .map(line -> line.split(CSV_SEPARATOR))
                .toArray(String[][]::new);
    }

    // db
    public static void insertUser(PreparedStatement stmt, User user) throws SQLException {
        stmt.setInt(1, user.id);
        stmt.setString(2, user.mail);
        stmt.setBoolean(3, user.role.read);
        stmt.setBoolean(4, user.role.write);
        stmt.setBoolean(5, user.role.delete);

        // batching
        stmt.addBatch();
    }

    static User[] getUsers(Connection conn) throws SQLException {
        var users = new ArrayList<User>();

        try (var st = conn.createStatement();
                var rs = st.executeQuery(SQL_GET_USERS); ) {
            while (rs.next()) {
                var userId = rs.getInt(1);
                var userMail = rs.getString(2);
                var userRole = new Role(rs.getBoolean(3), rs.getBoolean(4), rs.getBoolean(5));
                users.add(new User(userId, userMail, userRole));
            }
        }

        return users.toArray(User[]::new);
    }

    // main
    public static void main(String[] args) {
        // filenames from command line args
        if (args.length != 2) {
            System.out.printf("Usage: java src/DbMigrations.java <users.csv> <roles.csv>\n");
            return;
        }
        String filenameUsers = args[0];
        String filenameRoles = args[1];

        // read csvs
        String[][] csvUsers;
        String[][] csvRoles;
        try {
            csvUsers = LoadCSV(filenameUsers);
            csvRoles = LoadCSV(filenameRoles);
        } catch (IOException e) {
            System.out.printf("Expection occured while trying to load CSV: %s\n", e);
            return;
        }

        // process roles
        var roles = new HashMap<Integer, Role>();
        for (var row : csvRoles) {
            // get id and single role
            var userId = Integer.parseInt(row[0], 10);
            var roleStr = row[1];

            // make sure userId is present
            roles.putIfAbsent(userId, Role.Default);

            // update value by setting one role to true and copying the rest
            roles.computeIfPresent(
                    userId,
                    (id, role) -> {
                        if (roleStr.equals("read")) return new Role(true, role.write, role.delete);
                        if (roleStr.equals("write")) return new Role(role.read, true, role.delete);
                        if (roleStr.equals("delete")) return new Role(role.read, role.write, true);
                        return role;
                    });
        }

        // OPTIONAL: open db connection for reading
        if (CONFIG_PRINT_DB) {
            try (var conn = DriverManager.getConnection(DB_URL)) {

                var users = getUsers(conn);
                System.out.println("DB before update:");
                for (var user : users) {
                    System.out.println(user);
                }

            } catch (SQLException e) {
                System.out.printf("Expection occured while accessing database: %s\n", e);
                return;
            }
        }

        // open db connection for updating
        try (var conn = DriverManager.getConnection(DB_URL);
                var stmt = conn.prepareStatement(SQL_UPSERT_USER); ) {
            // batching
            conn.setAutoCommit(false);

            // update db from csv
            for (var row : csvUsers) {
                var userId = Integer.parseInt(row[0], 10);
                var userMail = row[1];
                var userRoles = roles.get(userId);

                User user = new User(userId, userMail, userRoles);
                insertUser(stmt, user);
            }

            // commit batch
            stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            System.out.printf("Expection occured while accessing database: %s\n", e);
            return;
        }

        System.out.println("Updated successfully");
    }
}