/*
 * Scone - The Web Enhancement Framework
 * Copyright (C) 2009 Harald Weinreich, Volkert Buchmann, Frank Wollenweber, Torsten Haß
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
 package scone.netobjects;


import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.StringTokenizer;
import java.util.Vector;

import scone.util.ErrorLog;


/**
 * represents the table definitions of the database
 *
 * restrictions for the sql definition file:
 * <ul>
 * <li>Table and column definitions must start in a new line.</li>
 * <li>Column definitions must be no more than one line.</li>
 * <li>There must be exactly one space between CREATE and TABLE or PRIMARY and KEY.</li>
 * <li>'#' and ';' will be interpreted as the beginning of a comment, even if they are qouted!</li>
 * <li>Default values may not contain '"' or '\'' or ';' or '#' or " primary " or "auto_increment"</li>
 * <li>Default values must be the last item in a column definition. They must be literal.</li>
 * <li>Rows in a table definition starting with "index " or ")" will be ignored.</li>
 * <li>There must be a PRIMARY KEY defined. If the primary key is composed from multiple columns, use a 
 *     PRIMARY KEY (col_name,...) statement after the columns have been defined.</li>
 * <li>If there is an AUTO_INCREMENT column it should be the PRIMARY KEY. A secondary key can be
 *     defined using the KEY (col_name,...) statement after the columns have been defined. A secondary key
 may only be defined if an AUTO_INCREMENT PRIMARY KEY has been defined.</li>
 * <li>At most one composed key may be defined.</li>
 * <li>Column names are case-sensitive!</li>
 * </ul>
 *
 * @author Volkert Buchmann
 */
 
public class Schema {

    public final static String fileName = "setup/sconedb.sql";

    // column types
    public static final int STRING = 0;
    public static final int NUMBER = 1;
    public static final int BLOB = 2;
   
    // key types
    public static final int GENERATED = 0;
    public static final int KEY = 1;
    public static final int FIELD = 2;

    // this table holds the definitions for each table
    Hashtable tables = new Hashtable();
	
    static Schema definitions;
	
    /**
     * initializes a DBTableAdapter
     * fills the internal definition tables of the adapter with the values read from the sql definition
     */
    public static void initialize(DBTableAdapter adapter) {
        Table table = null;
        Table dbTable = null;
		
        if (definitions == null) {
            definitions = new Schema();
        }
        table = (Table) definitions.tables.get(adapter.tableName.toLowerCase());
		
        // copmare to the definition in the database
        if (adapter.useDb) {
            try {
                if (!getDefinitionFromDb(adapter.tableName, adapter.con).compareTo(table)) {
                    System.out.println("\n-> Scone: ERROR! --------------------------------------------------");
                    System.out.println(" The schema in setup/sconedb.sql does not correspond with");
                    System.out.println(" the schema found in the database! Database updated required!");
                    adapter.dontUseDb();
                }
            } catch (Exception x) {
                ErrorLog.log("this", "Database.initialize()", "Could not read schema from database", x);
                System.out.println("\n-> Scone: ERROR! --------------------------------------------------");
                System.out.println(" Could not read schema from database!");
                adapter.dontUseDb();
            }
        }

        // initialize the adapter for the table			
        Column col = null;

        adapter.init(table.getSize());
        for (int i = 0; i < table.getSize(); i++) {
            col = table.get(i);
            adapter.addField(col.name, col.type, col.defValue, col.key);
        }
    }
	
    public static void main(String[] args) {
        Schema db = new Schema();
    }
	
    public Schema() {
        parseSqlSource();
    }
	
    /**
     * reads a table definition from the database
     */
    static Table getDefinitionFromDb(String tableName, Connection con) {
        Table table = new Table(tableName);

        try {
            synchronized (con) {
                Statement stmt = con.createStatement();
                ResultSet results = stmt.executeQuery("show columns from " + tableName);      
                Column col = null;

                while (results.next()) {
                    col = new Column();
                    col.name = results.getString("Field");
                    col.type = convertType(results.getString("Type"));
                    col.defValue = results.getString("Default");
                    if (col.defValue == null) {
                        if (col.type == NUMBER) {
                            col.defValue = "0";
                        } else {
                            col.defValue = "";
                        }
                    }
                    if (results.getString("Key").toLowerCase().startsWith("mul")) {
                        col.key = KEY;
                    }
                    if (results.getString("Key").toLowerCase().startsWith("pri")) {
                        col.key = KEY;
                        if (results.getString("Extra").toLowerCase().indexOf("auto_increment")
                                >= 0) {
                            col.key = GENERATED;
                        }
                    }
						
                    table.add(col);
                }
            }
        } catch (Exception x) {
            System.out.println("fatal exception while creating DBTAbleAdapter for " + tableName);
            x.printStackTrace();
            System.exit(0);
        }
        // ErrorLog.println("sql: \n"+table.toString());
        return table;
    }
	
    /**
     * converts the sql column type into a netobject field type
     * exists the application if an unknown column type is found.
     */
    public static int convertType(String type) {
        type = type.toLowerCase();
        if (type.startsWith("tinyint") || type.startsWith("smallint")
                || type.startsWith("mediumint") || type.startsWith("int")
                || type.startsWith("bigint")) {
            return NUMBER;
        }
        if (type.startsWith("text") || type.startsWith("char")
                || type.startsWith("varchar")) {
            return STRING;
        }
        if (type.startsWith("blob") || type.startsWith("mediumblob")) {
            return BLOB;
        }
		   
        System.out.println("-----unknown column type: " + type + " -----------------------");
        System.exit(0);
        return -1;
    }
	
    /**
     * returns the count of subject in line
     * count("abcbcde","c")==2
     */
    public int count(String line, String subject) {
        int c = 0;
        int pos = 0;

        while ((pos = line.indexOf(subject, pos)) >= 0) {
            c++;
            pos++;
            if (pos >= line.length()) {
                break;
            }
        }
        return c;
    }
	
    /**
     * parses the sql file 
     */
    public void parseSqlSource() {
        try {
            String line = null;
            String dummy = "";
            StringTokenizer st = null;
            String tableName = null;
            Column col = null;
            Table table = null;

            // open the sql definition file
            BufferedReader file = new BufferedReader(new InputStreamReader(new FileInputStream(fileName))); 
	      
            // read out the whole definition!
            while (true) {
	      	
                // skip lines until table definition
                while ((line = file.readLine()) != null
                        && !line.trim().toLowerCase().startsWith("create table ")) {
                    
                }
                // if there is no table definition left, we are done
                if (line == null) {
                    return;
                }
	
                if (line.indexOf("#") >= 0) {
                    line = line.substring(0, line.indexOf("#"));
                }
                if (line.indexOf(";") >= 0) {
                    line = line.substring(0, line.indexOf(";"));
                }
                line = line.trim();
		      
                // find out the tablename
                st = new StringTokenizer(line);
                while (st.hasMoreTokens()) {
                    line = st.nextToken();
                    if (!line.equals("") && !line.toLowerCase().equals("create")
                            && !line.toLowerCase().equals("table")) {
                        tableName = line.toLowerCase();
                        break;
                    }
                }
		         
                // ensure that it does not terminate with '('
                if (tableName.charAt(tableName.length() - 1) == '(') {
                    tableName = tableName.substring(0, tableName.length() - 1);
                }
					
                // System.out.println("tablename: "+tableName);
				
                int pCount = count(line, "(") - count(line, ")");
					
                // create a new table object
                table = new Table(tableName);
                tables.put(tableName, table);
				
                // extract column definitions
                while ((line = file.readLine()) != null && pCount > 0) {
		      	
                    // this saves some trouble...
                    if (line.length() == 0) {
                        continue;
                    }
                    if (line.indexOf("#") >= 0) {
                        line = line.substring(0, line.indexOf("#"));
                    }
                    if (line.indexOf(";") >= 0) {
                        line = line.substring(0, line.indexOf(";"));
                    }
                    line = line.trim();
                    if (line.endsWith(",")) {
                        line = line.substring(0, line.length() - 1).trim();
                    }
                    // pCount==0 if the table definition has ended	
                    pCount = pCount + count(line, "(") - count(line, ")");
                    if (line.length() == 0
                            || line.toLowerCase().startsWith("index ")
                            || line.toLowerCase().startsWith("fulltext ")
                            || line.startsWith(")")) { 
                        continue;
                    }
                    // get rid of the last ')' that ends the table definition
                    if (pCount == 0) {
                        line = line.substring(0, line.lastIndexOf(")")).trim();
                    }
	      			
                    // does this line define a composed key?
                    if (line.toLowerCase().startsWith("primary key ")
                            || line.toLowerCase().startsWith("key ")
                            || line.toLowerCase().startsWith("primary key(")
                            || line.toLowerCase().startsWith("key(")) {
                        // get the column list
                        line = line.substring(line.indexOf("(") + 1, line.indexOf(")")).trim();
                        // extract the columns from the list
                        // we don't use String.split() yet for compatibility reasons
                        st = new StringTokenizer(line, ",");
                        while (st.hasMoreTokens()) {
                            dummy = st.nextToken().trim();
                            if (dummy.length() != 0) {
                                // now the dirty work: update the column entry
                                for (int i = 0; i < table.getSize(); i++) {
                                    if (table.get(i).name.equalsIgnoreCase(dummy)) {
                                        if (table.get(i).key != GENERATED) {
                                            table.get(i).key = KEY;
                                        }
                                        // System.out.println("found primary key: "+tableName+"."+dummy);
                                        break;
                                    }
                                }
                            }
                        }
                        continue;
                    }// found primary key definition
	
                    // now we know that we have a line holding a column definition
                    col = new Column();
	         	
                    st = new StringTokenizer(line);
	      		
                    // read colName
                    if (st.hasMoreTokens()) { 
                        col.name = st.nextToken();
                    }
	      			
                    // System.out.println("name: "+col.name);
	      		
                    // skip empty tokens and read colType
                    while (st.hasMoreTokens()) {
                        dummy = st.nextToken();
                        if (dummy.length() != 0) {
                            break;
                        }
                    }
                    col.type = convertType(dummy);
	      		
                    // System.out.println("type: "+dummy);
	      		
                    // check for key features
                    if (line.toLowerCase().indexOf(" primary ") >= 0) {
                        col.key = KEY;
                    }
                    if (line.toLowerCase().indexOf("auto_increment") >= 0) {
                        col.key = GENERATED;
                    }
	    				
                    // System.out.println("key:  "+col.key);
	
                    // extract default value
                    int pos = line.toLowerCase().indexOf(" default ");

                    if (pos >= 0) {
                        line = line.substring(pos + 9, line.length()).trim();
                        if (line.length() > 0) {
                            if ((line.charAt(0) == '\'')
                                    || (line.charAt(0) == '"')) {
                                col.defValue = line.substring(1, line.length() - 1);
                            } else {
                                col.defValue = line;
                            }
                        }
                    }
	      		
                    // System.out.println("def:  "+col.defValue);
	      		
                    table.add(col);
                }// extract column definition
                // ErrorLog.println("file: \n"+table.toString());
	      	
            }// while true
			
        } catch (Exception e) {
            ErrorLog.log(this, "parseSqlSource()", "Could not parse file " + fileName + ".sql", e);
            e.printStackTrace();
        }
    }
	
}


/**
 * holds the definition of the columns of a table
 */
class Table {
	
    String name;
	
    public Vector cols = new Vector();
    public Table(String name) {
        this.name = name;
    }
		
    // ads a column definition to the table
    public void add(Column col) {
        cols.addElement(col);
    }
		
    // returns the number of columns
    public int getSize() {
        return cols.size();
    }

    // returns the ith column
    public Column get(int i) {
        return (Column) cols.elementAt(i);
    }
		
    // returns the column with the specified name
    // or null if it does not exist
    public Column get(String name) {
        for (int i = 0; i < getSize(); i++) {
            if (get(i).name.equals(name)) {
                return get(i);
            }
        }
        return null;
    }
		
    // compares two table definitions
    // does not compare the key type!
    // case sensitive except for the table name
    // returns true if equal, false otherwise
    public boolean compareTo(Table table) {
        Column col = null;
        Column col2 = null;

        try {
            if (!name.toLowerCase().equals(table.name.toLowerCase())) {
                ErrorLog.println(name + " and " + table.name + " do not have the same name!");
                return false;
            }
            if (getSize() != table.getSize()) {
                ErrorLog.println(name + ": number of columns not identical!");
                return false;
            }
            for (int i = 0; i < getSize(); i++) {
                col = get(i);
                col2 = table.get(col.name);
                if (col2 == null) {
                    ErrorLog.println(name + ": could not find column " + col.name);
                    return false;
                }
                if (col.type != col2.type) {
                    ErrorLog.println(name + "." + col.name + ": columns have different types!");
                    return false;
                }
                if (col.key != Schema.GENERATED) {
                    if (!col.defValue.equals(col2.defValue)) {
                        ErrorLog.println(name + "." + col.name + ": columns have different default values!\n'" + col.defValue + "' and '" + col2.defValue + "'");
                        return false;
                    }
                }
            }
				
        } catch (Exception x) {
            x.printStackTrace();
            return false;
        }
        return true;
    }
		
    public String toString() {
        String s = "---------------------------------\n";

        s += "Table: " + name + "\n";
        for (int i = 0; i < getSize(); i++) {
            s += get(i).toString();
        }
        s += "---------------------------------\n";
        return s;
    }
}
	

/**
 * holds the definition for one column
 */
class Column {
    public String name = "";
    public int type = -1;
    public int key = Schema.FIELD;
    public String defValue = "";
		
    public String toString() {
        return name + " | " + type + " | " + key + " | " + defValue + "\n";
    }
		
}
