package org.ohdsi.rabbitInAHat;

import org.ohdsi.rabbitInAHat.dataModel.*;

import java.io.*;
import java.util.List;

/**
 * Created 04-01-17.
 * Generates separate SQL files for each table to table mapping in the given directory.
 */
public class SQLGenerator {
    ETL etl;
    String outputDirectory;

    public SQLGenerator(ETL etl, String directory) {
        this.etl = etl;
        this.outputDirectory = directory;
    }

    public void generate() {
        // Generate a sql file for each source to target table mapping
        for (ItemToItemMap tableToTableMap : etl.getTableToTableMapping().getSourceToTargetMaps()) {
            writeSqlFile(tableToTableMap);
        }
    }

    private void writeSqlFile(ItemToItemMap tableToTableMap){
        Table sourceTable = (Table) tableToTableMap.getSourceItem();
        Table targetTable = (Table) tableToTableMap.getTargetItem();
        List<ItemToItemMap> mappings = etl.getFieldToFieldMapping(sourceTable, targetTable).getSourceToTargetMaps();

        // Create new sql file in the selected directory
        File outFile = new File( outputDirectory, sourceTable.getName() + "_to_" + targetTable.getName() + ".sql");
        System.out.println( "Writing to: " + outFile.getAbsoluteFile() );

        int n_mappings = mappings.size();

        try (FileOutputStream fout = new FileOutputStream(outFile);
             OutputStreamWriter fwr = new OutputStreamWriter(fout, "UTF-8");
             Writer out = new BufferedWriter(fwr)) {

            // Table specific comments
            out.write(createBlockComment(sourceTable.getComment()));
            out.write(createBlockComment(targetTable.getComment()));
            out.write(createBlockComment(tableToTableMap.getComment()));
            out.write(createBlockComment(tableToTableMap.getLogic()));
            out.write('\n');

            // To
            // TODO: handle duplicate target field names (source fields need to be combined)
            out.write("INSERT INTO " + targetTable.getName() + "\n");
            out.write("(\n");
            for (int i=0;i<n_mappings;i++) {
                ItemToItemMap mapping = mappings.get(i);
                Field target = targetTable.getFieldByName(mapping.getTargetItem().getName());
                out.write('\t');
                out.write(target.getName());

                // Do not print comma if last is reached
                if (i != n_mappings-1) {
                    out.write(",");
                }

                out.write(createInLineComment(target.getComment()));

                out.write("\n");
            }

            // From
            out.write(")\nSELECT\n");
            for (int i=0;i<n_mappings;i++) {
                ItemToItemMap mapping = mappings.get(i);
                Field source = sourceTable.getFieldByName(mapping.getSourceItem().getName());
                out.write('\t');
                out.write(source.getName());

                // Do not print comma if last is reached
                if (i != n_mappings-1) {
                    out.write(",");
                }

                out.write(createInLineComment(source.getComment()));
                out.write(createInLineComment(mapping.getComment()));
                out.write(createInLineComment(mapping.getLogic()));

                out.write("\n");
            }
            out.write("FROM " + sourceTable.getName() + ";");

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

    private static String createInLineComment(String input) {
        if (input.trim().equals(""))
            return "";

        return " -- " + input.replaceAll("\\s"," ");
    }

    private static String createBlockComment(String input) {
        if (input.trim().equals(""))
            return "";

        return String.format("/*%n%s%n*/", input.trim());
    }
}

@@ -105,19 +105,67 @@
 va.util.
-Lis
+HashSet;%0Aimport java.util.List;%0Aimport java.util.Se
 t;%0A%0A/**%0A
@@ -573,16 +573,45 @@
 mapping%0A
+        Integer counter = 0;%0A
         
@@ -746,33 +746,155 @@
 leMap);%0A        
-%7D
+    counter++;%0A        %7D%0A        System.out.println(counter.toString() + %22 sql files were generated%22); // TODO report in UI
 %0A    %7D%0A%0A    priv
@@ -1489,24 +1489,78 @@
 ngs.size();%0A
+        Set%3CField%3E targetFieldsSeen = new HashSet%3C%3E();
 %0A        try
@@ -2120,101 +2120,8 @@
  To%0A
-            // TODO: handle duplicate target field names (source fields need to be combined)%0A
     
@@ -2404,32 +2404,33 @@
 m().getName());%0A
+%0A
                 
@@ -2637,32 +2637,276 @@
              %7D%0A%0A
+                // Warning if this target field has already been used%0A                if (!targetFieldsSeen.add(target)) %7B%0A                    out.write(createInLineComment(%22!#WARNING!# THIS TARGET FIELD WAS ALREADY USED%22));%0A                %7D%0A%0A
                 
@@ -3053,16 +3053,43 @@
 ite(%22)%5Cn
+%22);%0A            out.write(%22
 SELECT%5Cn
@@ -3357,32 +3357,131 @@
 urce.getName());
+%0A                out.write(%22%5CtAS%5Ct%22);%0A                out.write(mapping.getTargetItem().getName());
 %0A%0A              
@@ -3610,32 +3610,82 @@
               %7D%0A
+                // TODO: add provenance of comment
 %0A               
@@ -3977,20 +3977,44 @@
 etName()
- + %22
+);%0A            out.write(%22%5Cn
 ;%22);%0A%0A  
@@ -4392,32 +4392,131 @@
     return %22%22;%0A%0A
+        // Let new sentences span multiple lines%0A        input = input.replaceAll(%22%5C%5C.%5C%5Cs%22,%22%5Cn%22);%0A%0A
         return S

Handle many to one target mappings with a warning