/* A program to make a web site from a database
 *
 * Copyright (C) 2015 Sidney Marshall (swm@cs.rit.edu)
 *
 * 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 3 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, see
 * <http://www.gnu.org/licenses/>.
 */

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;

/*
 * java -cp sqlite-jdbc-3.7.8-20111025.014814-1.jar\;. MakeWeb
 * use : separator on linux
 *
 * jar file can have class path specified
*/
/* full join:
 *
 * SELECT * FROM table_name_1
 *   LEFT OUTER JOIN table_name_2 ON id_1 = id_2
 * UNION SELECT * FROM table_name_2
 *   LEFT OUTER JOIN table_name_1 ON id_1 = id_2 ;
 */

// Note that 'xxx' is a string and "xxx" is an identifier.

/* ordered group_concat
 *
 * select parent_name, group_concat(child_name)
 * from
 *   (select parent_name, child_name from children order by child_name)
 * group by parent_name;
 */

/**
 * This class creates a web site from a database
 */
public class MakeWeb {
  static final boolean printFlag = true;
  static Connection conn; // the database connection

  /**
   * This method implements easy access to the specified rows of a
   * query.  The method queries the database and sets up parameters to
   * read specified columns from the returned table. If the number of
   * specified columns is zero then all columns are read in
   * order. This constructor will cause errors if a column name is
   * requested that does not exist. If there is no result set then an
   * empty result is returned allowing this method to be used for
   * general sql statements.
   *
   * @param query the query used for obtaining the table
   * @param columns the names of the columns to be returned
   * @return an ArrayList of ArrayList of the result of a query
   * @throws SQLException throws if problem with SQLite
   */
  static ArrayList<ArrayList<String>> table(String query, String...columns)
    throws SQLException {
    if(query == null) throw new SQLException("null query in table");
    int ncolumns = columns.length;
    ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
    Statement stat = conn.createStatement();
    try {
      stat.execute(query);
    } catch(SQLException e) {
      System.out.println("Bad SQL statement in table function:");
      System.out.println(query);
      throw e;
    }
    int count = stat.getUpdateCount();
    if(count >= 0) {
      stat.close();
      return result;
    }
    ResultSet rs = stat.getResultSet();
    ResultSetMetaData md = rs.getMetaData();
    int tablecolumns = md.getColumnCount();
    ArrayList<Integer> columnIndex = new ArrayList<Integer>();
    if(columns.length > 0) {
      ArrayList<String> columnName = new ArrayList<String>();
      columnName.add(""); // 0th entry
      for(int i = 1; i <= tablecolumns; i++) {
        columnName.add(md.getColumnName(i));
      }
      iloop: for(int i = 0; i < columns.length; i++) {
        for(int j = 1; j <= tablecolumns; j++) {
          if(columns[i].equals(columnName.get(j))) {
            columnIndex.add(j);
            continue iloop;
          }
        }
        columnIndex.add(null); // error - no such column name
      }
    } else {
      ncolumns = tablecolumns;
      for(int i = 0; i <= tablecolumns; i++) {
        columnIndex.add(i + 1);
      }
    }
    while(rs.next()) {
      ArrayList<String> data = new ArrayList<String>();
        for(int i = 0; i < ncolumns; i++) {
          data.add(rs.getString(columnIndex.get(i)));
        }
        result.add(data);
    }
    stat.close();
    return result;
  }

  /**
   * This method creates all pages specified by the database. It
   * assumes three tables in the database: substitutions, pagesets,
   * and scripts.
   *
   * substitutions(pageset, substitutions, comment)
   *   pageset - identifies the type of page being generated (unique)
   *   substitutions - an sql statement returning a table:
   *     first field is file name or null, rest replace $1-$9 (and $$)
   *   comment - a human comment telling what scripts in this line do
   *
   * pagesets(pageset, position, pagepart, comment)
   *   pageset - identifies the type of page being generated
   *   position - order in which to run pageparts in a pageset
   *   pagepart - an index into the scripts table for the scripts
   *   comment - a human comment telling what scripts in this line do
   *
   * scripts(pagepart, position, script, comment)
   *   pagepart - identifies the part of the page being generated
   *   position - order in which to run scripts in a pagepart
   *   script - an sql statement returning a "page" segment
   *     result each row is fields concatenated to be written to file
   *   comment - a human comment telling what scripts in this line do
   *
   * ---------- for reference only ----------
   * styles(selector, property, value, comment)
   *   selector - a css selector
   *   property - a css property name
   *   value - the value of this property
   *   comment - a human comment telling where this property is used
   *
   * ---------- algorithm ----------
   *
   * for each substitution
   *   get table of substitutions
   *   for each substitution (each table row)
   *     get and open file specified by first column (or no file for null)
   *     get matching pageset in order from pagesets and scripts
   *       for each script (after substitutions)
   *         for each line
   *           concatenate fields and write line to file
   *     close file
   * @param pagesets pagesets to be made - if empty all pagesets are made
   * @throws SQLException catch-all for SQLExceptions
   * @throws IOException catch-all for IOExceptions
   */
  static void make(String...pagesets) throws SQLException, IOException {
    // we need error messages that tell where we are in the scripts
    // Get and process pagegroups from master table substitutions
    long totalTime = 0;
    ArrayList<ArrayList<String>> subs = table("select * from substitutions order by pageset",
                           "pageset", "substitutions");
    Arrays.sort(pagesets);
    for(ArrayList<String> subsLine : subs) {
      String pageset = subsLine.get(0);
      if(pagesets.length != 0
         && Arrays.binarySearch(pagesets, pageset) < 0) continue;
      if(printFlag) System.out.print("pageset: " + pageset + " - ");
      long startTime = System.nanoTime();
      // run substitution sql to get list of substitutions for pagegroup
      ArrayList<ArrayList<String>> subsList = table(subsLine.get(1));
      for(ArrayList<String> subsListLine : subsList) {
        String filename = subsListLine.get(0);
        FileWriter out = null;
        if(filename != null) {
          out = new FileWriter(filename);
        }
        // get scripts for pageset
        ArrayList<ArrayList<String>> scripts
          = table("select script from pagesets join scripts where pageset is '" + pageset + "' and pagesets.pagepart is scripts.pagepart order by pagesets.position, scripts.position");
        for(ArrayList<String> scriptsLine : scripts) {
          // run substituted sql to get the tables to write out
          if(filename != null) {
            ArrayList<ArrayList<String>> page = table(substitute(scriptsLine.get(0), subsListLine));
            for(ArrayList<String> pageLine : page) {
              // write scriptsLine concatenated
              StringBuilder sb = new StringBuilder();
              for(int i = 0; i < pageLine.size(); i++) {
                String item = pageLine.get(i);
                if(item != null) sb.append(item);
              }
              sb.append('\n');
              out.write(sb.toString());
            }
          } else {
            String s = substitute(scriptsLine.get(0), subsListLine);
            Statement stat = conn.createStatement();
            try {
              stat.execute(s);
            } catch(SQLException e) {
              System.out.println("In pageset: " + pageset + " filename: " + filename + " command: " + s);
              throw e;
            }
            stat.close();
          }
        }
        if(out != null) {
          out.close();
        }
      }
      long stopTime = System.nanoTime();
      long milliseconds = (stopTime - startTime) / 1000000L;
      totalTime += milliseconds;
      if(printFlag) System.out.println(milliseconds + " msec");
    }
    if(printFlag) System.out.println("Total Time = " + totalTime);
  }

  /**
   * replaces $0-$9 in String s with subs.get(0) - subs.get(9). Also
   * replaces $$ with $. (Note that $0 is normally replaced by the
   * file name.)
   *
   * @param s the String to perform substitutions on
   * @param subs an ArrayList specifying substitutions for $0, $1 etc.
   * @return the string with substitutions
   */
  static String substitute(String s, ArrayList<String> subs) {
    StringBuilder sb = new StringBuilder();
    for(int i = 0; i < s.length(); i++) {
      char c;
      if((c = s.charAt(i)) != '$' || i == s.length() - 1) {
        sb.append(c);
      } else {
        i++;
        c = s.charAt(i);
        int val = Character.digit(c, 10);
        if(val < subs.size()) {
          sb.append(subs.get(val));
        } else if(c == '$') {
          sb.append(c);
        } else {
          sb.append('$');
          sb.append(c);
        }
      }
    }
    return sb.toString();
  }

  /**
   * if null return the string "null" otherwise surround the string
   * with ' and escape ' characters by '' to allow in SQL
   * statements. Commented out because now using ? for all variable
   * strings and this handles nulls too.
   *
   * We are now using prepared statements and '?' so this method is
   * not currently used.
   *
   * @param s the string to be escaped
   * @return the escaped string
   */
  /*
  static String s(String s) {
    if(s == null) return "null";
    return "'" + s.replace("'", "''") + "'";
  }
  */
  /**
   * An interface defining a callback for a file tree walk (used by
   * the sitemap option)
   */
  static interface FileCallBack {
    /**
     * @param entryid a unique reference for this file system object
     * @param name the name of directory entry
     * @param type 0 = file, 1 = dir, -1 = unknown (, 2 = link etc.)
     * @param path the path from root to this file object
     * @param parentid a unique reference of containing directory
     * @param length length of the file
     * @param date the modification date of the file
     */
    void file(int entryid, String name, int type, String path, int parentid, long length, long date);
  }

  /**
   * This class, given a top-level directory, creates in a database a
   * table "sitemap" containing entries representing the file and
   * directory structure under this top-level directory.  The format of
   * the database tables is:
   *
   * Command: java -cp makeweb.jar MakeWeb\$SiteMap test.db map .
   *
   * Table sitemap(parent, child)
   *
   * The top-level directory is represented by the empty string and all
   * other files are named relative to this top-level directory
   */
  static class SiteMap {

    /**
     * Gets the canonical name for a File or returns the name
     * "UNKNOWN"
     *
     * @param f the file name to be canonized
     * @return the canonical file name
     */
    static String canonicalName(File f) {
      try {
        return f.getCanonicalPath();
      } catch(Exception e) {
        e.printStackTrace();
      }
      return "UNKNOWN";
    }

    /**
     * Return a relative path relative to the given absolute path. If
     * the prefix is not a prefix of the absolute path just return the
     * unmodified absolute path.
     *
     * @param prefix the prefix to be stripped off of the path
     * @param absolute the path to be stripped
     * @return the stripped path
     */
    static String relativePath(String prefix, String absolute) {
      int index = absolute.indexOf(prefix);
      if(index == 0 && prefix.length() != absolute.length()) {
        String suffix = absolute.substring(prefix.length());
        if(suffix.charAt(0) == File.separatorChar) {
          suffix = suffix.substring(1);
        }
        return suffix;
      }
      return absolute;
    }

    String topBase;
    int id = 0; // current unique id
    Connection conn; // the database connection
    String table; // the table to update
    PreparedStatement ustat;

    /**
     * Insert into a database a file map of the specified
     * directory. The specified table is cleared before inserting the
     * file map. The table should be created with at least the
     * columns:
     *
     * create table xxx(entryid, name, type, path, parentid, length, date)
     *
     * @param conn the connection to the database
     * @param table the name of the table to use
     * @param top the top of the file system to create a map on
     */
    SiteMap(Connection conn, String table, File top) {
      this.conn = conn;
      this.table = table;
      String updateQuery = "insert into " + table +
        "(entryid, name, type, path, parentid, length, date)" +
        "values(?1,?2,?3,?4,?5,?6,?7)";
      Statement stat;
      try {
        stat = conn.createStatement();
        stat.executeUpdate("begin");
        stat.executeUpdate("delete from " + table);
        ustat = conn.prepareStatement(updateQuery);
        topBase = canonicalName(top);
        siteMap(new File(topBase), id, new DBInsert()); // top of hierarchy is 0
        ustat.close();
        stat.executeUpdate("end");
        stat.close();
      } catch(SQLException e) {
        e.printStackTrace();
      }
    }

    /**
     * Extend a site map for a directory - argument must be a
     * directory and must exist. Second argument must be a canonical
     * name string for the parent
     *
     * We want to fill in a sitemap table:
     *
     * sitemap(entryid, name, path, parentid, length, date)
     *
     * @param parentDir the current directory we are working on
     * @param parent its unique id of the directory we are working on
     * @param callBack the callback object for dealing with a child
     */
    void siteMap(File parentDir, int parent, FileCallBack callBack) {
      File[] childFiles = parentDir.listFiles();
      Arrays.sort(childFiles, new Comparator<File>(){
          public int compare(File f1, File f2) {
            return f1.getName().compareToIgnoreCase(f2.getName());
          }
        });
      for(int i = 0; i < childFiles.length; i++) {
        File childFile = new File(parentDir, childFiles[i].getName());
        String child = childFiles[i].getName();
        String fullName = relativePath(topBase, childFiles[i].getAbsolutePath());
        long length = childFile.length();
        long date = childFile.lastModified();
        ++id;
        if(childFile.isFile()) {
          callBack.file(id, child, 0, fullName, parent, length, date);
        } else if(childFile.isDirectory()) {
          callBack.file(id, child, 1, fullName, parent, length, date);
          siteMap(childFile, id, callBack);
        } else {
          callBack.file(id, child, -1, fullName, parent, length, date);
        }
      }
    }

    /**
     * insert into tablename (name1, name2) values(exp, exp)
     *
     * Suggest inserting:
     *
     * entryid - id of this entry
     * name - text name of entry
     * type - 0 is file, 1 is directory, -1 is unknown
     * path - string path from top
     * parentid - id of containing directory with top = 0
     * length - byte length of file, 0 for directory on windows, size on linux
     * date - milliseconds since the epoch (00:00:00 GMT, January 1, 1970)
     */
    class DBInsert implements FileCallBack {
      /*
        query = insert into <table> (entryid, name, type, path, parentid, length, date)
                values(?1,?2,?3,?4,?5,?6,?7)
      */
      /**
       * The prepared statement ustat is:
       *
       * insert into &lt;table&gt; (entryid, name, type, path, parentid, length, date)
       *
       * Insert the arguments into the database.
       *
       * @param entryid a unique reference for this file system object
       * @param name the name of directory entry
       * @param type 0 = file, 1 = dir, -1 = unknown (, 2 = link etc.)
       * @param path the path from root to this file object
       * @param parentid a unique reference of containing directory
       * @param length length of the file
       * @param date the modification date of the file
       */
      public void file(int entryid, String name, int type, String path, int parentid, long length, long date) {
        try {
        ustat.setInt(1, entryid);
        ustat.setString(2, name);
        ustat.setInt(3, type);
        ustat.setString(4, path.replace('\\', '/'));
        ustat.setInt(5, parentid);
        ustat.setLong(6, length);
        ustat.setLong(7, date);
        ustat.executeUpdate();
        } catch(SQLException e) {
          e.printStackTrace();
        }
      }
    } // class DBInsert implements FileCallBack

    /**
     * arg0 = database file name<br>
     * arg1 = database table name<br>
     * arg2 = name of top of file system<br><br>
     *
     * can be called from command line by:<br><br>
     *
     * java -cp makeweb.jar MakeWeb\$SiteMap test.db map .<br><br>
     *
     * where "." can be replaced by any existing directory
     * @param args command line arguments
     */
    public static void main(String...args) {
      Connection conn;
      try {
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:" + args[0]);
        File top = new File(args[2]);
        new SiteMap(conn, args[1], top);
      } catch(ClassNotFoundException ex) {
        ex.printStackTrace();
      } catch(SQLException ex) {
        ex.printStackTrace();
      }
    }
  } // class SiteMap

  /**
   * If first arg is "-map" then a sitemap structure is created in the
   * table "sitemap". If there is a second argument it will be used as
   * the root of the filemap, otherwise the root will be ".".
   *
   * All subsequent arguments are the "pagesets" to be made. If there
   * are no subsequent arguments then all "pagesets" are made. Note
   * that skipping some pagesets can result in errors.
   *
   * @param args command line arguments
   * @throws SQLException catch-all for SQLExceptions
   * @throws IOException catch-all for IOExceptions
   */
  public static void main(String...args) throws SQLException, IOException {
    String root = ".";
    /*
    if(args.length > 2) {
      System.err.println("Usage: java -jar makeweb [-map [root]]");
      return;
    }
    */
    int index = 0; // where pagesets list (if any) starts
    try {
      Class.forName("org.sqlite.JDBC");
    } catch(ClassNotFoundException ex) {
      ex.printStackTrace();
    }
    conn = DriverManager.getConnection("jdbc:sqlite:" + "squaredance.db");
    if(args.length >= 1) {
      if(args[0].equals("-map")) {
        index = 1;
        if(args.length >= 2) {
          root = args[1];
          index = 2;
        }
        File top = new File(root);
        new SiteMap(conn, "sitemap", top);
      }
    }

    ArrayList<String> subs = new ArrayList<String>();
    for(int i = index; i < args.length; i++) {
      subs.add(args[i]);
    }

    make(subs.toArray(new String[0]));
    System.out.println("Done.");
  }
} // class MakeWeb