/*
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 */

package vkc.db.helper;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;


public class MyInterbase2Oracle9i {
	
	
	public MyInterbase2Oracle9i(){
	}
	

	/**
	 * 
	 */
	private void convert(String ddlFile) throws IOException {
	    
	    InterbaseSQLParser parser = new InterbaseSQLParser(ddlFile);
	    parser.setRemoveStmtLimiter(false);
	    
	    List stmts = parser.parseSQLFile();
	    
		BufferedOutputStream bos = new BufferedOutputStream( 
		        new FileOutputStream( 
		                new File( parser.getFile().getParentFile().getParentFile().getParentFile(), 
		                        "oracle/migration/vkc_db_oracle_migration_to_1-187.sql" ) ) );
	
		String lastGenStmt = null;
		
		for (Iterator iter = stmts.iterator(); iter.hasNext();) {
            String s = (String) iter.next();
				
			if ( s.toLowerCase().startsWith( "create generator " ) ) {
				lastGenStmt = s;
				continue;
			} else if ( s.toLowerCase().startsWith( "set generator " ) ) {
				if(lastGenStmt!=null) {
					s = lastGenStmt.substring( 0, lastGenStmt.indexOf( ";") ) + " " + s.substring( 0, s.length()-1 );
					String ss [] = s.split( "\\s" );
					s = "create sequence " + ss[2] + " start with " + ( Integer.parseInt( ss[7] ) + 1 ) +  " increment by 1;";
					lastGenStmt = null;
				}else{continue;}
			} else if ( s.toLowerCase().startsWith( "create unique asc " ) ) {
				String ss [] = s.split( "\\s|,|\\)|\\(|;" );
				s = "create unique index " + ss[4] + " on " + ss[6] + "(\n";
				
				for ( int i = 7; i < ss.length; i++ ) {
					if ( ss[i].trim().length() > 0 ) {
						s += ss[i] + " asc,\n";	
					}
				}
				
				s = s.substring( 0, s.length()-2 ) + "\n);";
			} else if ( s.toLowerCase().startsWith( "create asc " ) ) {
				String ss [] = s.split( "\\s|,|\\)|\\(|;" );
				s = "create index " + ss[3] + " on " + ss[5] + "(\n";
				
				for ( int i = 6; i < ss.length; i++ ) {
					if( ss[i].trim().length() > 0 ) {
						s += ss[i] + " asc,\n";
					}
				}
				
				s = s.substring(0, s.length()-2) + "\n);";
			}
			
			if ( s.toLowerCase().startsWith( "create table " ) || s.toLowerCase().startsWith( "create view " ) ) {
				
				//String ss [] = s.split( "\\s" );
				//s = "drop " + ss[1] + " " + ss[2] +"\n/\n" + s;
			}
			
			// Oracle-spezifisches Zeug ersetzen
			s = s.replaceAll( "BIGINT", "INTEGER" );
			s = s.replaceAll( "VARCHAR", "VARCHAR2" );
			s = s.replaceAll( "\n\\s\n", "\n" );
			s = s.substring( 0, s.length()-1 ) + "\n/";
			
			bos.write( s.getBytes() );
			bos.write( '\n' );
			bos.write( '\n' );
			
			
		}
		
		bos.flush();
		bos.close();
	}
	
	public static void main( String[] args ) {
		
	    System.out.println( "start converting interbase to oracle 9i setup sql ..." );
	    MyInterbase2Oracle9i i2o = new MyInterbase2Oracle9i( );
		
		try {
			i2o.convert( "db/dbschema/scripts/interbase/migration/vkc_db_interbase_migration_to_1-187.sql" );
		} catch ( IOException e ) {
			e.printStackTrace();
		}
		
		System.out.println( "...finished" );		
	}

}