/* 
 *   Load2600Issue.java
 *   w.r.e.c.
 *   10/13/2008
 *   
 *   Retrieve 2600 issue and save in database.
 */
package wepp;

import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.io.*;
import java.util.Properties;

import org.htmlparser.Node;
import org.htmlparser.Parser;
import org.htmlparser.filters.*;
import org.htmlparser.nodes.RemarkNode;
import org.htmlparser.nodes.TagNode;
import org.htmlparser.nodes.TextNode;
import org.htmlparser.util.NodeIterator;
import org.htmlparser.util.NodeList;


/**
 * @author wepp
 *
 */
public class Load2600Issue {

	/**
	 * @param args
	 */
	public static void main (String[] args) {

		/* local variables */		
		String sURL = "";
		String sSQL = "";
		String sTitle = "";
		String sUrlYear = "";
		String sUrlPeriod = "";
		String sPeriod = "";
		String sCoverLink = "";
		String sBackCoverLink = "";
	    String sFilename = "";
	    String sConnect;
	    String sUser;
	    String sPassword;
	    //String sFullFilename = "";
		// test: winter2006, winter2007, summer2008, autumn2004, winter1995,
		//       winter2002, winter1990, winter1988, winter2005, spring2003
		String sArg = "";
		
		Integer iYear = 0;
		Integer iIssuesID = -1;
		Integer iQuarter = 0;
		Integer iVolume = 0;
		
		Boolean bRepeat = false;
		
		Statement stmt;
		ResultSet rs;

		NodeList nlParse;
		NodeList nlChild;
		
		/* debug option */
		for (int i = 0; i < args.length; i++)  {
			if (args[i].compareToIgnoreCase ("debug") == 0)  {
				bDebug = true;
			}
			else if (args[i].length () > 0)  {
				sArg = args[i];
			}
		}

		/* load MySQL properties */
		//System.err.println ("curdir=" + System.getProperty("user.dir"));
		Properties properties = new Properties ();
	    try {
	        properties.load (Load2600Issue.class.getResourceAsStream("/Load2600Issue.properties"));
	        System.out.println (properties.toString());
	    }
	    catch (IOException e) {
	    	e.printStackTrace();
	    }
	    sConnect = properties.getProperty ("connect");
	    sUser = properties.getProperty ("user");
	    sPassword = properties.getProperty ("password");

		/* get issue titles from 2600 web site */
		try {
            
			/* Register the JDBC driver for MySQL */
			Class.forName (C_JDBC_CLASS);
        
			Connection con = DriverManager.getConnection (sConnect, sUser, sPassword);

			/* determine next period */
			if (sArg.equalsIgnoreCase ("next"))  {
				System.out.println ("Arg=" + sArg);
				
				/* get last issue in database */
				sSQL = "SELECT * " +
					   "FROM Issues " +
					   "WHERE IssueYear = (SELECT MAX(IssueYear) FROM Issues) " +
					   "ORDER BY Quarter DESC " +
					   "LIMIT 1;  ";
				stmt = con.createStatement ();
				if (bDebug)  System.out.println (sSQL);
			
				rs = stmt.executeQuery (sSQL);
				rs.last ();
				
				/* last quarter rolls over to next year */
				if (rs.getInt ("Quarter") == 4)  {
			        iYear = rs.getInt("IssueYear") + 1;
			        iQuarter = 1;
				}
				else  {
					iYear = rs.getInt("IssueYear");
					iQuarter = rs.getInt ("Quarter") + 1;
				}
				
			    switch (iQuarter) {
					case 1 :
						sPeriod = "spring";
						break;
					case 2 :
						sPeriod = "summer";
						break;
					case 3 :
						sPeriod = "autumn";
						break;
					case 4 :
						sPeriod = "winter";
						break;
			    }
			
				/* set next period/year */
				sArg = sPeriod + iYear.toString ();
			}
			
			/* get arg variables */
			System.out.println ("Arg=" + sArg);
			iYear = Integer.parseInt (sArg.substring(6, 10));
			sPeriod = sArg.substring (0, 6);
			sUrlYear = iYear.toString ();
			sUrlPeriod = sPeriod;
			
			/* special cases for cover link */
			if (sPeriod.toLowerCase ().equals ("winter"))  {
				sUrlYear = iYear.toString ();
				/* second year in url has 0 or 2 or 4 digits (assume 4 after 2007) */
				if (iYear == 1999 ||
					iYear == 2002 ||
					iYear == 2003 ||
					iYear == 2004 ||
					iYear == 2005 ||
					iYear >= 2007)  {
					sUrlYear = iYear.toString ();
					sUrlYear += iYear+1;  // four digits
				}
				else if (iYear == 1988 ||
						 iYear == 1989 ||
						 iYear == 1990 ||
						 iYear == 1991 ||
						 iYear == 1992)  {
					// zero digits
				}
				else  {
					sUrlYear = iYear.toString ();
					Integer i = iYear + 1;
					sUrlYear += i.toString ().substring (2);  // two digits
				}
			}
			if (sPeriod.toLowerCase ().equals ("autumn"))  {
				/* some have fall instead of autumn */
				if (iYear >= 2000 && iYear <= 2004)  sUrlPeriod = "fall";
			}
			
			/* build 2600 url */
			sURL = "http://store.2600.com/" + sUrlPeriod + sUrlYear + ".html";
			if (bDebug) System.out.println ("URL=" + sURL);
			
			/* format for database */
			sPeriod = sPeriod.substring(0,1).toUpperCase() + sPeriod.substring (1, 6).toLowerCase();
			if (sPeriod.equals ("Spring"))  iQuarter = 1;
			if (sPeriod.equals ("Summer"))  iQuarter = 2;
			if (sPeriod.equals ("Autumn"))  iQuarter = 3;
			if (sPeriod.equals ("Winter"))  iQuarter = 4;
			
			iVolume = iYear - 1983;
			if (bDebug) System.out.println ("Period=" + sPeriod + ", Year=" + iYear +  ", Quarter=" + iQuarter + ", Volume=" + iVolume);

			/* get page */
			URL url = new URL (sURL);
			URLConnection urlcon = url.openConnection ();
			urlcon.setRequestProperty( "User-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)" );
		    Parser parser = new Parser (urlcon);
		    
		    /* did we get redirected? */
		    if (!sURL.equalsIgnoreCase (parser.getURL ()))  {
		    	System.out.println ("Page not found, ignored!");
		    	if (bDebug)  System.out.println ("Redirected to=" + parser.getURL ());
		    }
		    else  {
		    	
			    /* get cover image link */
			    sCoverLink = "";
			    nlParse = parser.parse (new TagNameFilter ("img"));  // all image items
				for (NodeIterator ni = nlParse.elements(); ni.hasMoreNodes(); ) {
					// testing // printNode (ni.nextNode ());
					String sTmp = ni.nextNode ().getText ();
					if (bDebug)  System.out.println  (sTmp);
					//if (sTmp.toLowerCase ().contains (sPeriod.toLowerCase ()))  {
					if (sTmp.toLowerCase ().contains ("click to enlarge"))  {
						int iStart = sTmp.indexOf("\"");
						int iEnd = sTmp.indexOf("\"", iStart+1);
						sCoverLink = sTmp.substring(iStart+1, iEnd);
					}
				}
			    if (bDebug)  System.out.println ("CoverLink=" + sCoverLink);

			    /* get cover image */
			    sFilename = sPeriod + iYear.toString () + ".gif";
			    //sFullFilename = "/var/www/2600/FrontCoverImages/" + sFilename;
			    downloadFile (sCoverLink, sFilename);
			    sCoverLink = "FrontCoverImages/" + sFilename;
			    sBackCoverLink = "BackCoverImages/" + sFilename;
			    
				bRepeat = true;
			    while (bRepeat)  {
			    	bRepeat = false;
			    	
					/* get IssueID */
					sSQL = "SELECT * FROM Issues " + 
					       "WHERE Period = '" + sPeriod + "' " + 
					       "  AND IssueYear = " + iYear;
					stmt = con.createStatement ();
					if (bDebug)  System.out.println (sSQL);
					
					rs = stmt.executeQuery (sSQL);
					rs.last ();
					
					/* add if not in database */
					if (rs.getRow () < 1)  {
						bRepeat = true;
						System.out.println ("Add " + sPeriod + "/" + iYear + ".");
						sSQL = "INSERT INTO Issues (Period, IssueYear, CoverLink, BackCoverLink, Quarter, Volume, Pages) " +
						       "VALUES ('" + sPeriod + "'," + iYear.toString() + ",'" + sCoverLink + "','" + 
						                sBackCoverLink + "'," + iQuarter.toString() + "," + iVolume.toString() + ",68)";
						stmt = con.createStatement ();
						if (bDebug)  System.out.println (sSQL);
						stmt.execute (sSQL);
					}
					else  iIssuesID = rs.getInt ("IssuesID");
			    }
			    
			    /* articles */
			    parser.reset ();
			    nlParse = parser.parse(new TagNameFilter ("li"));  // all list items
			    for (NodeIterator ni = nlParse.elements (); ni.hasMoreNodes (); ) {
			    	nlChild = ni.nextNode ().getChildren ();
			    	sTitle = nlChild.elementAt (0).toPlainTextString ();
			    	/* remove new line, convert double quote to single */
			    	sTitle = sTitle.replaceAll ("\n", "");
			    	sTitle = sTitle.replaceAll ("\"", "'");
			    	System.out.println (sTitle);
			    	
			    	/* check if alrady on file */
			    	sSQL = "SELECT * FROM Articles " +
			    	       "WHERE IssuesID = " + iIssuesID +
			    	       "  AND Title = \"" + sTitle + "\"";
					stmt = con.createStatement ();
					if (bDebug)  System.out.println (sSQL);
					rs = stmt.executeQuery (sSQL);
					rs.last ();
					
					/* add if not on file */
					if (rs.getRow () < 1)  {
			    	
			    		/* update database with issue title */
				    	sSQL = "INSERT INTO Articles (IssuesID, AuthorsID, Title, Synopsis) " +
				    	       "VALUES (" + iIssuesID + ",1,\"" + sTitle + "\",'')";
						stmt = con.createStatement ();
						if (bDebug)  System.out.println (sSQL);
						stmt.execute (sSQL);
					}
			    }
			    
			    /* done */
			    con.close ();
		    }
		}
		catch (Exception e)  {
			e.printStackTrace ();
		}
		
		System.out.println ("Done @ " + new Date ());

	}  // main

	
	/* testing */
	static void printNode (Node node)  {

		if (node instanceof TextNode)  {
	        // downcast to TextNode
			TextNode text = (TextNode)node;
			System.out.print ("Text=");
			System.out.print (text.getText ());
			System.out.println ("=");
		}
		else if (node instanceof RemarkNode)  {
			RemarkNode remark = (RemarkNode) node;
			System.out.print ("remark=");
			System.out.print (remark.getText ());
			System.out.println ("=");
		}
		else if (node instanceof TagNode)  {

			try {

				// downcast to TagNode
	            TagNode tag = (TagNode)node;
				System.out.println (tag.getText ());
	
				NodeList nl = tag.getChildren ();
				if (nl != null)  {
					for (NodeIterator i = nl.elements (); i.hasMoreNodes (); ) {
						printNode (i.nextNode ());
					}
				}
			}
		    catch (Exception e)  {
		        e.printStackTrace ();
		    }
		}
	}  // printNode

	
	public static void downloadFile (String sAddress, String sFilename) {
		
		/* local variable */
		OutputStream out = null;
		URLConnection conn = null;
		InputStream  in = null;
		
		try {
			if (! new File (sFilename).exists ())  {
				URL url = new URL (sAddress);
				out = new BufferedOutputStream (new FileOutputStream (sFilename));
				conn = url.openConnection ();
				in = conn.getInputStream ();
				byte[] buffer = new byte[1024];
				int iRead;
				long lWritten = 0;
				while ((iRead = in.read (buffer)) != -1) {
					out.write(buffer, 0, iRead);
					lWritten += iRead;
				}
			if (bDebug)  System.out.println (sFilename + "\t" + lWritten);
			}
		}
		catch (Exception ex) {
			ex.printStackTrace ();
		}
		finally {
			try {
				if (in != null)  in.close();
				if (out != null)  out.close();
			}
			catch (IOException ioe) {
			}
		}
	}  // downloadFile


	/* class global */
    /* global constants */
    static final String C_JDBC_CLASS = "com.mysql.jdbc.Driver";
	
    /* global variables */
	private static boolean bDebug = false;

} // Load2600Issue