package com.etretatlogiciels.dvdcatalog;

import java.io.*;
import java.sql.*;
import java.util.*;
import com.etretatlogiciels.dvdcatalog.util.*;

/** ----------------------------------------------------------------------------
** DvdCatalogLoad: an application to load database with Russ' DVD list. It opens
** a simple text file and reads in DVDs line by line formatting an SQL statement
** which is then issued to the database.
** 
** @author rbateman
*/
public class DvdCatalogLoad
{
   private DvdCatalogProperties properties;

   static         // static initialization of class members...
   {
      // force the loading of the MySQL driver...
      try
      {
         Class.forName("com.mysql.jdbc.Driver");
      }
      catch (ClassNotFoundException ex)
      {
         System.out.println("MySQL driver not found!");
         System.exit(-1);
      }
   }

   static void usage()
   {
      // usage information displayed if no filename argument passed...
      System.out.println("USAGE: java DvdLoad <filename>");
      System.out.println("\twhere <filename> is delimited file");
   }

   /**
   ** DvdLoad main() usage: <tt><b>java DvdLoad &lt;filename></b></tt>
   **
   ** <pre>
   ** &lt;filename> is the name of a file containing DVD titles
   ** </pre>
   */
   public static void main( String[] args )
   {
      DvdCatalogLoad dvd = new DvdCatalogLoad();
      
      // if not enough arguments, display usage and bail
      if (args.length < 1)
      {
         usage();
         System.exit(0);
      }

      // do it
      try
      {
         dvd.LoadFromFile(args);
      }

      catch (Exception e)
      {
         e.printStackTrace();
      }
   }

   public DvdCatalogLoad()
   {
      properties = DvdCatalogProperties.getInstance();
   }

   private String getURL()
   {
      String   url;
      String   server   = properties.getProperty("dbserver");
      String   database = properties.getProperty("database");
      String   user     = properties.getProperty("dbuser");
      String   password = properties.getProperty("dbpassword");

      url = "jdbc:mysql://" + server + "/" + database +
         "?user=" + user + "&password=" + password;

      return url;
   }
   
   private int getRating( String rating )
   {
      // these checks unneeded since they representat the defualt:
      // if (rating.compareToIgnoreCase("-")     == 0)   return 0;
      // if (rating.compareToIgnoreCase("(NR)")  == 0)   return 0;
      if (rating.compareToIgnoreCase("G")     == 0)   return 1;
      if (rating.compareToIgnoreCase("PG")    == 0)   return 2;
      if (rating.compareToIgnoreCase("PG-13") == 0)   return 3;
      if (rating.compareToIgnoreCase("R")     == 0)   return 4;
      return 0;
   }

   private String filterQuotes( String string )
   {
      int            index, added;
      StringBuilder  sb = new StringBuilder(string);

      /*
      ** Find and replace every single quote with two quotes. This is
      ** useful because SQL prefers single-quoted arguments while, in
      ** our example, DVD titles make use of the single quote as an
      ** apostrophe, hence prematurely terminating a title or other
      ** string argument like the star list or comment.
      */
      added = 0;
      index = 0;

      while ((index = string.indexOf('\'', index)) != -1)
      {
         sb.insert(index+added, '\'');
         added++;
         index++;
      }

      return sb.toString();
   }

   /*
   ** Insert values into the database from a text file with the DVD titles
   ** one field to a line and separated by one or more blank lines.
   ** @param elements
   */
   private void executeUpdate( List<String> elements )
   {
      try
      {
         Connection  conn = DriverManager.getConnection(getURL());

         // the new for loop...
         for (String entry : elements)
         {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(entry);
         }
      }

      catch (SQLException sex)
      {
         sex.printStackTrace();
         throw new Error(sex);
      }
   }

   /** ------------------------------------------------------------------------
   ** Importing data from a delimited file.
   ** @param commandArgs contains the name of a delimited file.
   ** @throws IllegalArgumentException
   */
   public void LoadFromFile( String[] commandArgs )
      throws IllegalArgumentException
   {
      String         table = properties.getProperty("dvdtable");
      BufferedReader in = null;

      // open the file
      try
      {
         /*
         ** Here's the database schema...
         ** fieldname   type           sample contents
         ** <id>        INTEGER
         ** <title>     VARCHAR(80)    2010: The Year We Make Contact
         ** <year>      INT(4)         1984
         ** <length>    INTEGER        114
         ** <rating>    VARCHAR(5)     PG
         ** <languages> INTEGER        F
         ** <genre>     INTEGER        [genre] Science Fiction, Drama,
         **                               Politics, Thriller, Religious,
         **                               Adventure, Action, Comedy, Fantasy,
         **                               Military, Suspense, Mystery, Music,
         **                               Musical, Chick Flick, Cartoon,
         **                               History
         ** <stars>     VARCHAR(128)   [stars] Roy Scheider, John Lithgow,
         **                               Helen Mirren
         ** <comment>   VARCHAR(80)    (not part of loader program)
         ** (blank line or end of file)
         **
         ** What 'to_database' should be for this film...
         ** "INSERT INTO dvdlist (title, year, length, rating, languages,     \
         **    genre, stars) VALUE ('2010: The Year We Make Contact', '1984', \
         **    '114', '2', '2', 'Roy Scheider, John Lithgow, Helen Mirren');" 
         */
         File  loadFile = new File(commandArgs[0]);

         in = new BufferedReader(new FileReader(loadFile));

         do
         {
            boolean        title, year, length, rating, languages;
            List<String>   elements;
            String         genreList, starList, comment, columnList,
                           to_database, values, line;
            GenreBits      genreBits;

            columnList = "INSERT INTO "      // first half of SQL command
                           + table + " (";
            values     = " VALUES (";        // second half of command
            genreList  = "";                 // holds list of genres
            starList   = "";                 // holds list of stars
            comment    = "";                 // holds comments
            title      = false;
            year       = false;
            length     = false;
            rating     = false;
            languages  = false;

            while ((line = in.readLine()) != null)
            {
               /*
               ** We collect genre and stars which may span more than one line,
               ** but the lines are prompted so we know what they are.
               */
               if (line.startsWith("[genre] "))
               {
                  genreList += line.substring(8);
                  continue;
               }

               if (line.startsWith("[stars] "))
               {
                  starList += filterQuotes(line.substring(8));
                  continue;
               }

               if (line.startsWith("[comment] "))
               {
                  comment += filterQuotes(line.substring(10));
                  continue;
               }

               if (!title)                   // always the first field...
               {
                  title       = true;
                  columnList += "title";
                  values     += "'" + filterQuotes(line) + "'";
                  continue;
               }
               else if (!year)
               {
                  year        = true;
                  columnList += ", year";
                  values     += ", '" + line + "'";
                  continue;
               }
               else if (!length)
               {
                  if (  line.equals("-")     // means "unspecified"
                     || line.equals("?"))    // or "unknown"...
                     line = "0";             // ...which we represent as 0

                  length      = true;
                  columnList += ", length";
                  values     += ", '" + line + "'";
                  continue;
               }
               else if (!rating)
               {
                  int   r = getRating(line);
   
                  rating      = true;
                  columnList += ", rating";
                  values     += ", '" + String.valueOf(r) + "'";
                  continue;
               }
               else if (!languages)
               {
                  LangBits langBits = new LangBits();
   
                  if (line.equals("-"))      // means "no language support"
                     line = "E";             // which we take to be English

                  languages   = true;
                  columnList += ", languages";
   
                  langBits.addListOfLangs(line);
                  values += ", '" + langBits.integerValue() + "'";
                  continue;
               }

               /*
               ** As soon as we get a completely blank line, we fall through
               ** and write the record. This means that spurious blank lines
               ** must be disallowed.
               */
               if (line.length() != 0)
                  continue;

               /*
               ** Fire the SQL INSERTion only after last value has been
               ** gathered. This is apparent by a zero-length line. Then,
               ** reset everything and go around for the next title. Since
               ** the genre and stars info might have gone over more than
               ** one line as strings, we convert them to bits, then add
               ** them in as such.
               **
               ** If the genre is unknown (unlikely), or stars unknown or
               ** there is no comment, then these lines should not even be
               ** present (no "[stars]" etc.).
               **
               ** We terminate the SQL INSERT statement.
               */
               genreBits = new GenreBits();

               if (genreList.length() > 0)
               {
                  genreBits.addListOfGenres(genreList);
                  columnList += ", genre";
                  values     += ", '" + genreBits.integerValue() + "'";
               }

               if (starList.length() > 0)
               {
                  columnList += ", stars";
                  values     += ", '" + starList + "'";
               }

               if (comment.length() > 0)
               {
                  columnList += ", comment";
                  values     += ", '" + comment + "'";
               }

               // assemble the full command...
               columnList += ")";
               values     += ");";
               to_database = columnList + values;

               // status line (or debug statement)...
               System.out.println(to_database);

               // start a new set of elements each time...
               elements = new Vector<String>();
               elements.add(to_database);
               executeUpdate(elements);

               // reinitialize state variables...
               columnList  = "INSERT INTO " + table + " (";
               values      = " VALUES (";
               starList    = "";
               genreList   = "";
               comment     = "";
               title       = false;
               year        = false;
               length      = false;
               rating      = false;
               languages   = false;
            }
            
            if (line == null)
               break;
         }
         while (true);
      }

      catch (IOException ioe)
      {
         ioe.printStackTrace();
      }

      finally
      {
         // don't forget to release our resources
         if (in != null)
         {
            try { in.close(); } 
            catch (IOException ioe) { ; }
         }
      }
   }
}