package database.migration;


import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.util.ArrayList;

import tool.regEx.DataNormalizer;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author alexis
 */
public class CSV_to_SQL_insert {
    String file;
    String table;
    boolean writeToFile;
    
    public CSV_to_SQL_insert(String textFile, String tableName, boolean _writeToFile){
        file = textFile;
        table = tableName;
        writeToFile = _writeToFile;
    }
    
    public static boolean isInQuote(String str, int index){
        int count = 0;

        for(int i = 0; i < index; i++)
        {
            if(str.charAt(i) == '\"')
            {
                count++;
            }
        }
        
        return (count%2 == 0?false:true);
    }
    
    public void CSV_to_Array() throws FileNotFoundException, IOException{
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file),Charset.forName("ISO-8859-1")));
        BufferedWriter out = null;
        if(writeToFile)
        	out = new BufferedWriter(new FileWriter("inserts_"+table+".txt"));
        String csv;
        
        while (!(csv = DataNormalizer.normalize(br.readLine())).equals("")) 
        {
            int lastComma = 0;
            ArrayList<String> array = new ArrayList<String>();

            while(csv.length() >= lastComma)
            {
                String value;
                int nextComma = csv.indexOf(";",lastComma);
                if(nextComma >= 0)
                {
                  value = csv.substring(lastComma,csv.indexOf(";",lastComma));
                  lastComma = csv.indexOf(";",lastComma)+1;
                }
                else
                {
                  value = csv.substring(lastComma);
                  lastComma = csv.length()+1;
                  if(isInQuote(csv, csv.length()))
                  {
                      do
                      {
                          csv = DataNormalizer.normalize(br.readLine());
                          value += "\n"+csv;
                      }while(!csv.contains("\""));
                  }
                }
                
                if(value == null  || value.equals("NULL")  || value.equals("null"))
                    value = "";

                if(value.startsWith("\"") && value.charAt(1) != '"')
                {
                    value = value.substring(1);
                }
                if(value.endsWith("\"") && value.charAt(value.length()-2) != '"')
                {
                    value = value.substring(0,value.length()-1);
                }
                array.add(value);
            }
            if(writeToFile)
            	out.write(createInsertFromArray(array.toArray(new String[array.size()]))+"\n");
            else
            	System.out.println(createInsertFromArray(array.toArray(new String[array.size()])));
        }
        br.close();
        if(writeToFile)
        	out.close();
    }
    
    public String createInsertFromArray(String[] array)
    {
        String insert = "INSERT INTO "+table+" VALUES(";
        
        if(table.equals("client"))
        {
            int[] index = {0,1,2,3,4,5,6,7,8,9,12};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("chambre"))
        {
            int[] index = {0,1,3,4,5,6,7};
            //speficique a 1
            if(!array[1].equals(""))
                array[1] = array[1].substring(0,1);
            //fin    
            //speficique a 
            if(array[4].equals("AVANT"))
                array[4] = "1";
            else
                array[4] = "0";
            //fin
            insert += createInsertValue(index,array);
        }
        else if(table.equals("depots"))
        {
            int[] index = {0,1,2};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("encaissement"))
        {
            int[] index = {0,1,2,3};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("facture"))
        {
            int[] index = {0,1,2,3,4,5,6,7,8,9};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("fermeture"))
        {
            int[] index = {0,1,2,3};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("categorie"))
        {
            int[] index = {0,1};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("methode_encaissement"))
        {
            int[] index = {0,1};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("statut"))
        {
            int[] index = {0,1};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("permanente"))
        {
            int[] index = {0,1,9,10,11,12,13};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("stock"))
        {
            int[] index = {0,1,2,3,4,5};
            insert += createInsertValue(index,array);
        }
        else if(table.equals("ventes"))
        {
            int[] index = {0,1,2,3,4,5,6};
            insert += createInsertValue(index,array);
        }
        
        insert += ");";
        
        return insert;
    }
    
    public String createInsertValue(int[] columnIndex, String[] array){
        String values = "\"";
        
        for(int i = 0; i < columnIndex.length-1; i++)
        {
            values += array[columnIndex[i]] + "\", \"";
        }
        values += array[columnIndex[columnIndex.length-1]] + "\"";
        
        return values;
    }
    
    public static void main(String args[]) {
        CSV_to_SQL_insert converter = new CSV_to_SQL_insert("/home/alexis/Bureau/ecole/PROJET/snapshot 16 oct/Table - Chambres.txt","chambre",true);
        
        try{
            converter.CSV_to_Array();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}