// QSql.java
// Andrew Davison, ad@coe.psu.ac.th, June 2023

/*  This code is based on QueryDB.java from
    http://coe.psu.ac.th/~ad/party3/sqlite/

     ____   _____       _
    / __ \ / ____|     | |
   | |  | | (___   __ _| |
   | |  | |\___ \ / _` | |
   | |__| |____) | (_| | |
    \___\_\_____/ \__, |_|
                     | |
                     |_|
  
  Usage: QSql [-hpV] -d=<dbName> [-q=<query>]
  Queries a SQLite database.
    -d, --database=<dbName>   SQLite database
    -h, --help                Show this help message and exit.
    -p, --pretty-print        display an ASCII table
    -q, --query=<query>       SQL Query
    -V, --version             Print version information and exit.

  Uses JDBC, SQLite and DBTablePrinter, which requires the following JARs in libs/ :
       * sqlite-jdbc-3.16.1.jar
       * DBTablePrinter.jar
   
  Picocli features:
    * named options and positional parameters
    * POSIX-style grouped short options 
    * automatic help and version options
    * exit codes
    * help header
    * ANSI colors


  Usage examples:
    > compile QSql.java

    > run QSql -h
    > run QSql -d=worldcities.db
    > run QSql -d=worldcities.db -q="select * from cities WHERE city = 'London'"
    > run QSql -d=worldcities.db  -p -q="select * from cities WHERE city = 'London'"

*/


import java.sql.*;
import java.util.concurrent.*;

import picocli.CommandLine;
import picocli.CommandLine.*;

import net.efabrika.util.*;


@Command(name = "QSql", 
     mixinStandardHelpOptions = true,
     version = "QSql 1.0",
     description = "Queries a SQLite database.",
     header = { "@|cyan " +
              "   ____   _____       _  \n" +
              "  / __ \\ / ____|     | | \n" +
              " | |  | | (___   __ _| | \n" +
              " | |  | |\\___ \\ / _` | | \n" +
              " | |__| |____) | (_| | | \n" +
              "  \\___\\_\\_____/ \\__, |_| \n" +
              "                   | |   \n" +
              "                   |_|   \n|@"} )
public class QSql implements Callable<Integer>
{
  private static final String DB_CONN = "jdbc:sqlite:";


  @Option(names = {"-q", "--query"},
          description = "SQL Query")
  private String query = "SELECT name FROM sqlite_master WHERE type = 'table'";
                      // list the table names in this database
                   // "select name from PRAGMA_TABLE_INFO('cities')";
                       //list the column names in the cities table

  @Option(names = {"-d", "--database"},
          description = "SQLite database",
          required = true)
  private String dbName;

  @Option(names = {"-p", "--pretty-print"},
          description = "Display an ASCII table.")
  private boolean isPretty = false;


  public Integer call()
  {
    try {
      Connection conn = DriverManager.getConnection(DB_CONN + dbName);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(query);

      if (isPretty)
        DBTablePrinter.printResultSet(rs);
      else {
        ResultSetMetaData rsmd = rs.getMetaData();
        int numCols = rsmd.getColumnCount();
        while (rs.next()) {
          for (int i=1; i <= numCols; i++)
            System.out.print(rs.getObject(i).toString() + " ");
          System.out.println();
        }
      }

      conn.close();
      return 0;
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
      return 1;
    }
  } /* call */


  // ----------------------------------

  public static void main(String[] args)
  {
    int exitCode = new CommandLine(new QSql()).execute(args);
    System.exit(exitCode);
  }


}  // end of QSql class