package org.greenstone.gsdl3.util;

import org.greenstone.gsdl3.gs3build.database.GS3SQLConnectionFactory;
import org.greenstone.gsdl3.gs3build.database.GS3SQLConnection;

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

import java.util.ArrayList;

public class SQLQuery {
    GS3SQLConnection connection = null;
    
    public SQLQuery() {

    }

    public boolean setDatabase(String db_name) {
	connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);
	try {

	    // test the connection
	    String query = "select * from "+GSSQL.DOCUMENT_TABLE+GSSQL.END;
	    Statement s = connection.createStatement();
	    s.execute(query);
	    s.close();
	    return true;
	} catch (Exception e) {
	    System.err.println("SQLQuery.setDatabase():"+e);
	}
	closeConnection();
	return false;
    }

    public void closeConnection() {
	if (connection != null) {
	    connection.close();
	    connection=null;
	}
    }

    public String MGNum2OID(String mg_num) {
	// get doc id and meta id
	String query = "select "+ 
	    GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+
	    GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+
	    " from "+GSSQL.METADATA_VALUE_TABLE+GSSQL.COMMA+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+
	    " where "+
	    GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.LABEL+GSSQL.EQUALS_QUOTE+GSSQL.MG_NUM+GSSQL.QUOTE+
	    " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.VALUE+GSSQL.EQUALS_QUOTE+mg_num+GSSQL.QUOTE+
	    " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
	    " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
	    GSSQL.END;
	
	Statement statement = null;
	ResultSet results = null;
	String doc_id = null;
	String meta_id = null;
	try {
	    statement = connection.createStatement();
	    results = statement.executeQuery(query);
	    if (results.first()) {
		doc_id = results.getString(GSSQL.DOCUMENT_ID);
		meta_id = results.getString(GSSQL.METADATA_ID);
	    } else {
		statement.close();
		return null;
	    }
	} catch (java.sql.SQLException e) {
	    System.err.println("SQLQuery.MGNum2OID(): "+e);
	    return null;
	}

	// now get division label
	query = "select "+
	    GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+
	    " from "+GSSQL.DIVISION_TABLE+GSSQL.COMMA+GSSQL.DIVISION_METADATA_TABLE+
	    " where "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
	    " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
	    " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
	    GSSQL.END;

	
	String short_label = null;
	try {
	    results = statement.executeQuery(query);
	    if (results.first()) {
		short_label = results.getString(GSSQL.SHORT_LABEL);
	    } else {
		statement.close();
		return null;
	    }
	    statement.close();
	} catch (SQLException e) {

	    System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage());
	    return null;
	}
	
	return GS3OID.createOID(doc_id, short_label);
    }

    public String OID2MGNum(String oid) {
	String id = getDocumentMetadata(oid, "gsdl3.mgseqno");
	return id;

    }

    // does the document have sections, or is it just a single page document?
    public boolean isHierarchicalDocument(String oid) {
	// first just check the OID - it may already have section info in it
	if (!GS3OID.isDocTop(oid)) {
	    return true;
	}

	// now we need to check the database
	String query = "select * "+ 
	    " from "+GSSQL.STRUCTURE_TABLE +
	    " where "+ GSSQL.DOCUMENT_ID +GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
	    " and "+ GSSQL.STRUCTURE_ID +GSSQL.EQUALS_QUOTE + "Section" + GSSQL.QUOTE +
	    GSSQL.END;
	boolean is_hierarchical = false;
	try {
	    Statement statement = connection.createStatement();
	    ResultSet results = statement.executeQuery(query);
	    is_hierarchical = results.first();
	    statement.close();
	} catch (java.sql.SQLException e) {
	    return false;
	}
	
	return is_hierarchical;
    }
    
    // classifier metadata is not actually metadata
    public String getClassifierMetadata(String oid, String full_meta_name) {

	if (!full_meta_name.equals("Title") && !full_meta_name.equals("numleafdocs")) {
	    return null;
	}
	// get the description
	String field_name="";
	if (full_meta_name.equals("Title")) {
	    field_name = GSSQL.DESCRIPTION;
	} else {
	    field_name = GSSQL.NUM_LEAF_DOCUMENTS;
	}
	
	String query = "select "+ field_name+
	    " from " + GSSQL.CLASSIFIER_TABLE +
	    " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
	    GSSQL.END;
	
	String value = null;
	try {
	    Statement statement = connection.createStatement();
	    ResultSet results = statement.executeQuery(query);
	    if (results.first()) {
		value = results.getString(field_name);
	    }
	    statement.close();
	} catch (java.sql.SQLException e) {
	    
	    System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage());
	    return null;
	}
	return value;
	
    }
    
    public ArrayList getDocumentChildren(String oid) {

	ArrayList children = new ArrayList();
	String doc_id = oid;
	String section_id = "All"; // if we are just passed a doc id
	if (!GS3OID.isDocTop(oid)) {
	    doc_id = GS3OID.getDocID(oid);
	    section_id = GS3OID.getSectionLabel(oid);
	}
//  	int sep_index = oid.indexOf(".");
//  	if (sep_index != -1) {
//  	    doc_id = oid.substring(0, sep_index);
//  	    section_id = oid.substring(sep_index+1);
//  	}

	// get the division ref for this section
	String query = "select "+GSSQL.DIVISION_REF +
	    " from "+GSSQL.DIVISION_TABLE+
	    " where "+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
	    " and "+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
	    GSSQL.END;

	ResultSet results = null;
	Statement statement = null;
	String div_ref = null;
	try {
	    statement = connection.createStatement();
	    results = statement.executeQuery(query);
	    if (results.first()) {
		div_ref = results.getString(GSSQL.DIVISION_REF);
	    } else {
		statement.close();
		return null;
	    }
	} catch (java.sql.SQLException e) {
	    
	    System.err.println("SQLQuery.getDocumentChildren() Error1: "+e.getMessage());
	    return null;
	}

	// now get the list of children
	query = "select "+GSSQL.SHORT_LABEL +
	    " from "+GSSQL.DIVISION_TABLE+
	    " where "+ GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
	    " and "+GSSQL.PARENT_TYPE+GSSQL.EQUALS_QUOTE+GSSQL.DIVISION+GSSQL.QUOTE+
	    " and "+GSSQL.PARENT_REF+GSSQL.EQUALS_QUOTE+div_ref+GSSQL.QUOTE+
	    GSSQL.END;
	try {
	    results = statement.executeQuery(query);
	    while (results.next()) {
		String id = results.getString(GSSQL.SHORT_LABEL);
		children.add(GS3OID.createOID(doc_id, id));
	    }
	    statement.close();
	} catch (java.sql.SQLException e) {
	    
	    System.err.println("SQLQuery.getDocumentChildren() Error2: "+e.getMessage());
	    return null;
	}

	return children;
	
    }
    
    public boolean documentHasChildren(String oid) {
	// for now, use getChildren, but can we do this a better way??
	ArrayList children = getDocumentChildren(oid);
	if (children==null || children.size()==0) {
	    return false;
	}
	return true;
	
    }
    public ArrayList getClassifierChildren(String oid) {

	ArrayList children = new ArrayList();

	String query = "select "+ GSSQL.CLASSIFIER_ID +
	    " from " + GSSQL.CLASSIFIER_TABLE +
	    " where " + GSSQL.PARENT_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
	    " and " + GSSQL.NUM_LEAF_DOCUMENTS + GSSQL.NOT+GSSQL.EQUALS_QUOTE+ "0"+GSSQL.QUOTE +
	    GSSQL.END; 
	
	try {
	    Statement statement = connection.createStatement();
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		String id = results.getString(GSSQL.CLASSIFIER_ID);
		children.add(id);
	    }
	    statement.close();
	} catch (java.sql.SQLException e) {
	    
	    System.err.println("SQLQuery.getClassifierChildren Error: "+e.getMessage());
	    return null;
	}
	return children;
    }

    public ArrayList getClassifierDocChildren(String oid) {
	ArrayList children = new ArrayList();

	String query = "select "+ GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.DOCUMENT_ID+
	    " from " + GSSQL.CLASSIFIER_TABLE + GSSQL.COMMA+GSSQL.CLASS_DOCUMENT_TABLE+
	    " where " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
	    " and " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_REF+GSSQL.EQUALS+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.CLASSIFIER_REF+
	    " order by "+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ORDER+
	    GSSQL.END;
	try {
	    Statement statement = connection.createStatement();
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		String id = results.getString(GSSQL.DOCUMENT_ID);
		children.add(id);
	    }
	} catch (java.sql.SQLException e) {
		
	    System.err.println("SQLQuery.getClassifierDocChildren() Error: "+e.getMessage());
	    return null;
	}
	
	return children;
    }
    
    public String getDocumentMetadata(String oid, String full_meta_name) {

	String doc_id = oid;
	String section_id = "All"; // if we are just passed a doc id
	if (!GS3OID.isDocTop(oid)) {
	    doc_id = GS3OID.getDocID(oid);
	    section_id = GS3OID.getSectionLabel(oid);
	}
//  	int sep_index = oid.indexOf(".");
//  	if (sep_index != -1) {
//  	    doc_id = oid.substring(0, sep_index);
//  	    section_id = oid.substring(sep_index+1);
//  	}


	// get the metadata block id
	String query = "select "+ 
	    GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID +
	    " from " + GSSQL.DIVISION_METADATA_TABLE+GSSQL.COMMA+GSSQL.DIVISION_TABLE+
	    " where " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
	    " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
	    " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
	    " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+ 
	    GSSQL.END;
	
	Statement statement = null;
	ResultSet results = null;
	String meta_id = null;
	try {
	    statement = connection.createStatement();
	    results = statement.executeQuery(query);
	    if (results.first()) {
		meta_id = results.getString(GSSQL.METADATA_ID);
	    } else {
		statement.close();
		return null;
	    }
	} catch (java.sql.SQLException e) {

	    System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
	    return null;
	}
	
	// now get the list of namespace refs for the right namespace
	int sep_index = full_meta_name.indexOf('.');
	String meta_ns = "gsdl3";
	String meta_name = full_meta_name;
	if (sep_index != -1) {

	    meta_ns = full_meta_name.substring(0, sep_index);
	    meta_name = full_meta_name.substring(sep_index+1);
	}

	query = "select * "+ 
	    " from "+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.COMMA+GSSQL.METADATA_VALUE_TABLE+
	    " where "+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
	    " and "+ GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
	    " and " + GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
	    " and "+ GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_TYPE+GSSQL.EQUALS_QUOTE+meta_ns+GSSQL.QUOTE+
	    " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
	    GSSQL.END;
	
	String meta_value = null;
	try {
	    statement = connection.createStatement();
	    results = statement.executeQuery(query);
	    while (results.next()) {
		String m_name = results.getString(GSSQL.LABEL);
		if (meta_name.equals(m_name)) {
		    meta_value = results.getString(GSSQL.VALUE);
		    break;
		}
	    }
	    statement.close();
	} catch (java.sql.SQLException e) {

	    System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
	    return null;
	}
	
	return meta_value;
    }
    public static void main (String [] args) {

	SQLQuery self = new SQLQuery();
	self.setDatabase("gs3test");
	System.out.println("getting oid for mg num 3");
	String oid = self.MGNum2OID("stx.3");
	System.out.println("oid was "+oid);
	
	System.out.println("title metadata for mg num 3:");
	String meta = self.getDocumentMetadata(oid, "gsdl3.title");
	if (meta != null) {System.out.println(meta);}
	System.out.println("subject metadata for mg num 3:");
	meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
	if (meta != null) {System.out.println(meta);}

	System.out.println("converting back to mg, oid "+oid);
	String id = self.OID2MGNum(oid);
	System.out.println(""+id);

	System.out.println("getting oid for mg num 8");
	oid = self.MGNum2OID("stx.8");
	System.out.println("oid was "+oid);

	System.out.println("title metadata for mg num 8:");
	meta = self.getDocumentMetadata(oid, "gsdl3.title");
	if (meta != null) {System.out.println(meta);}
	System.out.println("subject metadata for mg num 8:");
	meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
	if (meta != null) {System.out.println(meta);}

	System.out.println("converting back to mg, oid "+oid);
	id = self.OID2MGNum(oid);
	System.out.println(""+id);
    }




}