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

class DbMigration {
    // constants {{{
    static final boolean CONFIG_PRINT_DB = true;
    static final String CSV_SEPARATOR = ",";
    static final String DB_URL = "jdbc:postgresql:migration";
    static final int    DB_BATCH_SIZE = 10;
    static final String SQL_GET_USERS = "SELECT * FROM users;";
    static final String SQL_UPSERT_USER =
            """
            INSERT INTO users VALUES (?, ?, ?, ?, ?)
            ON CONFLICT (USER_ID)
            DO UPDATE SET
                MAIL = EXCLUDED.MAIL,
                CAN_READ = EXCLUDED.CAN_READ,
                CAN_WRITE = EXCLUDED.CAN_WRITE,
                CAN_DELETE = EXCLUDED.CAN_DELETE
            ;
            """;

    // }}}

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

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

    // }}}

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

    // }}}

    // 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);
    }

    // }}}

    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 {{{
        ArrayList<String[]> csvUsers;
        ArrayList<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;
        }
        // }}}
        
        // Sparkassen Nutzer aussortieren {{{
        csvUsers.removeIf(row -> row[1].endsWith("@sparkasse.de"));
        // }}}

        // process roles {{{
        // - read roles.csv line by line
        // - get user_id and role from each line
        // - make sure user_id is in the HashMap
        // - if not create default value with no roles
        // - update the role specified in that line
        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("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
            int batchIndex = 0;
            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);

                // batching
                batchIndex += 1;
                if (batchIndex > DB_BATCH_SIZE) {
                    stmt.executeBatch();
                    batchIndex = 0;
                }
            }

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

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