package com.coursedb.dbtask5;


import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) {
        MyConection myConection = new MyConection("jdbc:mysql://localhost:3306/", "chinook", "root", "root");
        MyConection myNewConection = new MyConection("jdbc:mysql://localhost:3306/", "newchinook", "root", "root");
        try {
            Statement statement = myConection.getConnection().createStatement();
            Statement newStatement = myNewConection.getConnection().createStatement();
            DatabaseMetaData metaData = myConection.getConnection().getMetaData();
            ResultSet resultSet = metaData.getTables(myConection.getDatabase(), "", "%", null);
            while (resultSet.next()){
                String table = resultSet.getString(3);
                newStatement.execute("create table" + myNewConection.getDatabase() + "." + table + "like" + myConection.getDatabase() + "." + table + ";" );
                newStatement.execute("insert into" +  myNewConection.getDatabase() + "." +  table + "select * from" + myConection.getDatabase() + "." + table + ";" );
            }
            resultSet.close();
            resultSet = metaData.getTables(myConection.getDatabase(), "", "%", null);
            while (resultSet.next()){
                String table = resultSet.getString(3);
                ResultSet keys = metaData.getExportedKeys(myConection.getConnection().getCatalog(),null, table);
                int n = 0;
                String foreignKeyTable = "";
                String foreignKeyColumn = "";
                String primaryKeyTable = "";
                String primaryKeyColumn = "";
                while (keys.next()){
                    int foreignKeySequence = keys.getInt("key_seq");
                    if (foreignKeySequence > n){
                        if (foreignKeyColumn != "") foreignKeyColumn += ",";
                        if (primaryKeyColumn != "") primaryKeyColumn += ",";
                        foreignKeyTable = keys.getString("foreignKeyColumn_name");
                        foreignKeyColumn += keys.getString("foreignKeyColumn_name");
                        primaryKeyColumn += keys.getString("primaryKeyColumn_name");
                        primaryKeyTable = keys.getString("primaryKeyTable_name");
                        n = foreignKeySequence;
                    }
                    else{
                        String execute = "alter table" + foreignKeyTable + "add foreign key (" + foreignKeyColumn + ")references" + primaryKeyTable + "(" + primaryKeyColumn + ");";
                        newStatement.execute(execute);
                        n = 1;
                        foreignKeyColumn = keys.getString("foreignKeyColumn_name");
                        primaryKeyColumn = keys.getString("primaryKeyColumn_name");
                        primaryKeyTable = keys.getString("primaryKeyTable_name");
                        foreignKeyTable = keys.getString("foreignKeyTable_name");
                    }
                }
                if(n > 0){
                    String execute = "alter table " + foreignKeyTable + " add foreign key (" + foreignKeyColumn + ") references " + primaryKeyTable + "(" + primaryKeyColumn + ");";
                    newStatement.execute(execute);
                }
                keys.close();
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            myConection.getConnection().close();
            myNewConection.getConnection().close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}