package com.arsdigita.content;

import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.io.*;

import oracle.sql.*;
import oracle.xml.parser.v2.*;
import oracle.jdbc.driver.*;

import org.w3c.dom.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;
import org.xml.sax.SAXException;

#sql iterator TypeIter(String object_type, String table_name, 
                       String id_column);
#sql iterator AttrIter(String attribute_name);

public class XMLExchange {

  public static void main(String[] args) throws Exception {

    Integer revisionID = new Integer(args[0]);
    PrintWriter out
      = new PrintWriter(
          new BufferedWriter(new OutputStreamWriter(System.out)));
 
    exportRevision(revisionID, out);
  }

  public static int importRevision(Integer itemID, Integer revisionID, 
                                   CLOB loc) throws SQLException, IOException, 
                                   XMLParseException, SAXException {

    DOMParser parser = new DOMParser();

    parser.parse(loc.getCharacterStream());
    XMLDocument doc = parser.getDocument();

    doc.print(System.out);
    
    XMLElement revision = (XMLElement) doc.getDocumentElement();

    // Create the revision

    String title = getChildText(revision, "title");
    String description = getChildText(revision, "description");
    String publishDate = getChildText(revision, "publish_date");
    String mimeType = getChildText(revision, "mime_type");
    String text = getChildText(revision, "text");

    #sql { begin 
      :OUT revisionID := content_revision.new(
              title        => :title,
              description  => :description,
              publish_date => to_date(:publishDate),
              mime_type    => :mimeType,
              text         => :text,
              item_id      => content_symlink.resolve(:itemID),
              revision_id  => :revisionID);
      end;
    };

    // Query for additional tables in which to insert extended attributes

    TypeIter typeIter;

    #sql typeIter = {
      select                                                
        object_type, table_name, id_column
      from                                                
        acs_object_types                                  
      where                                               
        object_type ^= 'acs_object'                       
      and                                                 
        object_type ^= 'content_revision'                 
      connect by                                          
        prior supertype = object_type                     
      start with                                          
        object_type = (                                   
          select object_type from acs_objects where object_id = :revisionID
        )
      order by
        level desc
    };

    String objectType;
    String dmlColumns, dmlValues;
    ArrayList attributes = new ArrayList();
    AttrIter attrIter;

    // Use JDBC for the actual insert rather than SQLJ because we need
    // to build the DML dynamically

    Connection conn = DefaultContext.getDefaultContext().getConnection();

    while (typeIter.next()) {

      objectType = typeIter.object_type();
      dmlColumns = "insert into " + typeIter.table_name() + "(" + 
        typeIter.id_column();
      dmlValues = ") values ( ?";

      // query the attributes of the table
      #sql attrIter = {
        select
          attribute_name
        from
          acs_attributes
        where
          object_type = :objectType
        order by
          attribute_name
      };

      while (attrIter.next()) {
        dmlColumns += ", " + attrIter.attribute_name();
        dmlValues += ",?";
        attributes.add(attrIter.attribute_name());
      }
      
      PreparedStatement stmt = conn.prepareStatement(dmlColumns + 
                                                     dmlValues + ")");
      
      stmt.setInt(1, revisionID.intValue());
      for (int i = 0; i < attributes.size(); i++) { 
        stmt.setString(i + 2, 
                       getChildText(revision, (String) attributes.get(i)));
      }

      stmt.execute();
      stmt.close();
      attributes.clear();
    }

    return revisionID.intValue();
  }

  // Write XML to a CLOB

  public static int exportRevision(Integer revisionID, CLOB loc) 
    throws SQLException, IOException {

    PrintWriter out = new PrintWriter(loc.getCharacterOutputStream());
    exportRevision(revisionID, out);

    return revisionID.intValue();
  }

  // Default implementation of a function to write an XML
  // representation of a content revision to an output stream.

  public static int exportRevision(Integer revisionID, PrintWriter out) 
    throws SQLException  {

    try {

      XMLDocument doc = new XMLDocument();

      // get the content type

      String contentType;
      String tableName;
      String isContentType;
      #sql { 
        select 
	  object_type, table_name, content_type.is_content_type(object_type)
        into 
          :contentType, :tableName, :isContentType
	from 
	  acs_object_types
	where
	  object_type = ( select object_type from acs_objects
			  where object_id = :revisionID )
      };

      // screeen out non-content-types that are revisioned, such as templates
      if (isContentType.equals("f")) return -1;

      XMLElement revision = new XMLElement(contentType);
      doc.appendChild(revision);

      // select attributes for the revision
      AttrIter attrIter;

      #sql attrIter = { 
	select
	  attribute_name
	from
	  ( select
	      object_type, level sort_level
	    from
	      acs_object_types
	    where
	      object_type <> 'acs_object'
	    start with
	      object_type = :contentType
	    connect by
	      object_type = prior supertype
	  ) types,
	  acs_attributes attrs
	where
	  attrs.object_type = types.object_type
	order by
	  types.sort_level desc, attrs.sort_order
      };

      // build the query to select attributes from the view for the
      // content type
      String attrQuery = "select revision_id";

      while (attrIter.next()) {
	attrQuery += ", " + attrIter.attribute_name();
      }

      attrQuery += " from " + tableName + "x where revision_id = ?";

      // select a row from the attribute view for the content type
      Connection conn = DefaultContext.getDefaultContext().getConnection();

      PreparedStatement stmt = conn.prepareStatement(attrQuery);

      stmt.setInt(1, revisionID.intValue());
      ResultSet rs = stmt.executeQuery();
      ResultSetMetaData md = rs.getMetaData();

      if (rs.next()) {
	for (int i = 1; i <= md.getColumnCount(); i++) {
	  // create an XML element for each attribute
	  String colName = md.getColumnName(i);
	  String colValue = rs.getString(i);
	  if (colValue == null) colValue = "";
	  appendTextOnlyElement(revision, colName, colValue);
	}
      }

      stmt.close();
      doc.print(out);

    } catch (Exception e) {
      throw new SQLException("Failed to generate XML document for revision " +
                             revisionID + ": " + e);
    }

    return revisionID.intValue();
  }

  private static String getChildText(XMLElement element, String name) {
    
    NodeList nodes = element.getChildrenByTagName(name);
    if (nodes.getLength() == 0) return null;
    
    // get the text node under this node
    Node textNode = nodes.item(0).getFirstChild();

    return textNode.getNodeValue();
  }

  private static void setChildText(XMLElement element, String text) {

    XMLText textNode = new XMLText(text);
    element.appendChild(textNode);
  }

  private static void appendTextOnlyElement(XMLElement parent, 
                                            String name, String text) {
    XMLElement element = new XMLElement(name);
    setChildText(element, text);
    parent.appendChild(element);
  }
}