/*
 * [SelectRandomQuote.java]
 *
 * Summary: Select and format a random quotation from the database.
 *
 * Copyright: (c) 2014-2017 Roedy Green, Canadian Mind Products, http://mindprod.com
 *
 * Licence: This software may be copied and used freely for any purpose but military.
 *          http://mindprod.com/contact/nonmil.html
 *
 * Requires: JDK 1.8+
 *
 * Created with: JetBrains IntelliJ IDEA IDE http://www.jetbrains.com/idea/
 *
 * Version History:
 *  1.0 2014-08-08 initial version
 */
package com.mindprod.quotedb;

import com.mindprod.common18.BigDate;
import com.mindprod.common18.ST;
import com.mindprod.fastcat.FastCat;
import com.mindprod.htmlmacros.macro.Born;
import com.mindprod.htmlmacros.macro.Macro;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.TimeZone;

import static java.lang.System.*;

/**
 * Select and format a random quotation from the database.
 *
 * @author Roedy Green, Canadian Mind Products
 * @version 1.0 2014-08-08 initial version
 * @since 2014-08-08
 */
public class SelectRandomQuote
    {
    // declarations
    private static final Macro bornDelegate = new Born();

    private static final TimeZone DEFAULT_TIMEZONE = TimeZone.getTimeZone( "UTC" );

    private static int authorNumber = 0;

    private static int uniqueQuoteID = 0;

    private static Integer birthYear = null;

    private static Integer deathYear = null;

    private static String authorName = null;

    private static String commentary = null;

    private static String cssClass = null;

    private static String notesAboutAuthor = null;

    private static String quotation = null;

    private static String source = null;

    private static String title = null;

    private static BigDate birthDate = null;

    private static BigDate composedDate = null;

    private static BigDate deathDate = null;

    /**
     * The connection.  Handle to the database
     */
    private static Connection conn = null;

    private static PreparedStatement getAllAuthorFields = null;

    private static PreparedStatement getAllQuoteFields = null;

    private static PreparedStatement getCount = null;

    private static PreparedStatement getUniqueQuoteID = null;

    /**
     * choose a quasirandom quotation
     *
     * @param aggregate which aggregate
     * @param hash      hash - random seed
     *
     * @return uniqueID of quotation
     */
    private static int chooseARandomQuotation( String aggregate, long hash ) throws SQLException
        {
        // make hash positive
        hash &= 0x7fffffff;
        // SQL will take modulus relative to the count.
        // look up that scrambled index to find quote index.
        getCount.setLong( 1, hash );
        getCount.setString( 2, aggregate );
        ResultSet rs = getCount.executeQuery();
        final int uniqueQuoteID;
        if ( rs.next() )  // note next will be called to navigate to the first result
            {
            uniqueQuoteID = rs.getInt( 1 );
            rs.close();
            }
        else
            {
            uniqueQuoteID = 1;
            }
        // quote index in where the quotation we will present is.
        return uniqueQuoteID;
        }// /method

    /**
     * compose the quotation into HTML from fields read from database.
     *
     * @return full quotation.
     */
    private static String composeQuotation()
        {
        final FastCat sb = new FastCat( 27 );
        sb.append( "<blockquote class=\"", cssClass, "\"><!-- id ", uniqueQuoteID, " -->\n" );
        if ( !ST.isEmpty( title ) )
            {
            sb.append( "<h2>", title, "</h2>\n" );
            }
        sb.append( quotation );  // includes final <br> </p> etc.
        sb.append( "\n~ <span class=\"quoth\">", authorName, "</span>\n" );
        // append birth death age, or birth age
        final String birthDeathInfo;
        if ( birthYear == null )
            {
            // no dates
            birthDeathInfo = null;
            }
        else if ( deathYear == null )
            {
            // just birth
            // handles anniversaries of birth (birthdays), birth year or precise birth date.
            birthDeathInfo = bornDelegate.expandMacro( new String[] { birthDate == null ? birthYear.toString() : birthDate.toString() }, true /* quiet */, false /* verbose */ );
            }
        else
            {
            birthDeathInfo = bornDelegate.expandMacro( new String[] { birthDate == null ? birthYear.toString() : birthDate.toString(),
                    deathDate == null ? deathYear.toString() : deathDate.toString() }, true /* quiet */, false /* verbose */ );
            }
        if ( !ST.isEmpty( birthDeathInfo ) )
            {
            sb.append( birthDeathInfo, "\n" );
            }
        if ( composedDate != null )
            {
            sb.append( "<span class=\"date\">", composedDate, "</span>\n" );
            }
        if ( !ST.isEmpty( source ) )
            {
            sb.append( "<span class=\"source\">", source, "</span>\n" );
            }
        if ( !ST.isEmpty( notesAboutAuthor ) )
            {
            sb.append( "<span class=\"authornotes\">", notesAboutAuthor, "</span>\n" );
            }
        if ( !ST.isEmpty( commentary ) )
            {
            sb.append( commentary, "\n" );
            }
        sb.append( "</blockquote>\n" );
        return sb.toString();
        }

    private static void getQuotationAndAuthorFields( int uniqueQuoteID ) throws SQLException
        {
        getAllQuoteFields.setInt( 1, uniqueQuoteID );
        ResultSet rs = getAllQuoteFields.executeQuery();
        if ( rs.next() )  // note next will be called to navigate to the first result
            {
            authorName = rs.getString( "authorName" );
            birthYear = rs.getInt( "birthYear" );
            birthDate = new BigDate( rs.getDate( "birthDate" ), DEFAULT_TIMEZONE );
            if ( rs.wasNull() || birthDate.isNull() )
                {
                birthDate = null;
                }
            cssClass = rs.getString( "cssClass" );
            deathYear = rs.getInt( "deathYear" );
            deathDate = new BigDate( rs.getDate( "deathDate" ), DEFAULT_TIMEZONE );
            if ( rs.wasNull() || deathDate.isNull() )
                {
                deathDate = null;
                }
            notesAboutAuthor = rs.getString( notesAboutAuthor );
            commentary = rs.getString( "commentary" );
            composedDate = new BigDate( rs.getDate( "composedDate" ), DEFAULT_TIMEZONE );
            if ( rs.wasNull() || composedDate.isNull() )
                {
                composedDate = null;
                }
            quotation = rs.getString( "quotation" );
            source = rs.getString( "source" );
            title = rs.getString( "title" );
            rs.close();
            }
        }// /method

    // /declarations
    // methods
    public static void fireup()
        {
        try
            {
            conn = Connect.connect();
            getUniqueQuoteID = conn.prepareStatement( "SELECT scrambler[ CAST(? % array_length(aggregate.scrambler, 1) as INT)+1] " +
                                                      "FROM aggregate WHERE aggregateName=?" );
            getAllQuoteFields = conn.prepareStatement( "SELECT author.authorname " +
                                                       "author.birthyear " +
                                                       "author.birthdate " +
                                                       "author.cssClass" +
                                                       "author.deathYear " +
                                                       "author.deathDate " +
                                                       "author.notesAboutAuthor " +
                                                       "quote.commentary " +
                                                       "quote.composedDate " +
                                                       "quote.quotation " +
                                                       "quote.source  " +
                                                       "quote.title" +
                                                       "FROM author " +
                                                       "INNER JOIN quote " +
                                                       "ON author.uniqueAuthorID = quote.uniqueAuthorID " +
                                                       "WHERE quote.uniqueQuoteID = %" );
            // array_length(anyarray, int)
            }
        catch ( SQLException e )
            {
            err.println( "cannot connect to Postgre database. " + e.getMessage() );
            }
        }// /method

    /**
     * this is not truly random.  You will get the same quote if you repeat with the same hash,
     * unless the quote database has changed for that aggregate.
     *
     * @param aggregate name of aggregate, anything on tap in database .g. general, gaymarriage
     * @param hash      64 bit hash of facts about place where quotation will be placed.
     *
     * @return text of the quotation. Macros will not be expanded.
     */
    public static String generateRandomQuote( final String aggregate, long hash )
        {
        try
            {
            uniqueQuoteID = chooseARandomQuotation( aggregate, hash );
            getQuotationAndAuthorFields( uniqueQuoteID );
            }
        catch ( SQLException e )
            {
            err.println( "cannot access Postgre database. " + e.getMessage() );
            }
        return composeQuotation();
        }// /method
    // /methods
    }