/* TQuotRef.java - quotation reference information (year, author, etc.),
 * SQL operations with the table 'quot_ref' in Wiktionary parsed database.
 *
 * Copyright (c) 2011 Andrew Krizhanovsky <andrew.krizhanovsky at gmail.com>
 * Distributed under EPL/LGPL/GPL/AL/BSD multi-license.
 */

package wikokit.base.wikt.sql.quote;

import java.sql.*;
import wikokit.base.wikipedia.language.Encodings;
import wikokit.base.wikipedia.sql.Connect;
import wikokit.base.wikipedia.sql.PageTableBase;

/** Quotation reference information (year, author, etc.) and
 * operations with the table 'quot_ref' in MySQL Wiktionary parsed database. */
public class TQuotRef {

    /** Quotation reference unique identifier. */
    private int id;

    /** Title of the work. */
    private String title;

    /** A wikilink to a book in Wikipedia (format: [[w:title|]]),
     * it could not be NULL, though it can be empty (""). */
    private String wikilink;

    /** Quote date, field quot_ref.year_id in database. */
    private TQuotYear year;

    /** Quote author, field quot_ref.author_id in database. */
    private TQuotAuthor author;

    /** Quote publisher, field quot_ref.publisher_id in database. */
    private TQuotPublisher publisher;

    /** Quote source, field quot_ref.source_id in database. */
    private TQuotSource source;

    public TQuotRef(int _id, String _title, String _wikilink,
            TQuotYear _year, TQuotAuthor _author,
            TQuotPublisher _publisher, TQuotSource _source)
    {
        id          = _id;
        title       = _title;
        wikilink    = _wikilink;

        year   = _year;
        author = _author;
        publisher = _publisher;
        source = _source;
    }

    /** Gets unique ID from database */
    public int getID() {
        return id;
    }

    /** Gets book's title from database. */
    public String getTitle() {
        return title;
    }
    

    /** Gets wikilink to Wikipedia (format: [[w:title|]]) for the book. */
    public String getWikilink() {
        return wikilink;
    }

    /** Gets date of quote from database. */
    public TQuotYear getYear() {
        return year;
    }

    /** Gets date (Start-End) of quote from database. */
    public String getYearsRange() {
        if (null == year)
            return "";

        int _from = year.getFrom();
        int _to = year.getTo();

        if(-1 == _from && -1 == _to) // it means that there is no info about years
            return "";

        if(-1 == _to || _from == _to) // if YYYY-YYYY, then return "YYYY"
            return "" + _from;

        return "" + _from + "-" + _to;
    }

    /** Gets author(s) of quote from database. */
    public TQuotAuthor getAuthor() {
        return author;
    }

    /** Gets name of author(s) of quote from database. */
    public String getAuthorName() {
        return (null != author) ? author.getName() : "";
    }

    /** Gets publisher of quote from database. */
    public TQuotPublisher getPublisher() {
        return publisher;
    }

    /** Gets name of publisher of quote from database. */
    public String getPublisherName() {
        return (null != publisher) ? publisher.getText() : "";
    }

    /** Gets source of quote from database. */
    public TQuotSource getSource() {
        return source;
    }

    /** Gets name of author(s) of quote from database. */
    public String getSourceName() {
        return (null != source) ? source.getText() : "";
    }


    /** @return true if all input strings are NULL or empty.
    */
    private static boolean isEmptyString (String a, String b, String c,
                                            String d, String e, String f)
    {
        if (null != a && a.length() > 0) return false;
        if (null != b && b.length() > 0) return false;
        if (null != c && c.length() > 0) return false;
        if (null != d && d.length() > 0) return false;
        if (null != e && e.length() > 0) return false;
        if (null != f && f.length() > 0) return false;

        return true;
    }

    /** Gets record from the table quot_ref.<br><br>
     *
     * SELECT id FROM quot_ref WHERE year_id is NULL AND author_id=7 AND title="test" AND title_wikilink="" AND publisher_id is NULL AND source_id=4
     *
     * @param author_id ID of author's name or "NULL",
     * @param _title title of the work
     * @param _title_wikilink link to a book in Wikipedia (format: [[w:title|]]),
     *                        it could be empty ("")
     * @param publisher_id ID of publisher or "NULL"
     * @param source_id ID of source or "NULL"
     * @return inserted record, or null if insertion failed
     */
    private static TQuotRef get (Connect connect,
                                TQuotYear y, TQuotAuthor a,
                                String _title, String _title_wikilink,
                                TQuotPublisher p, TQuotSource src)
    {
        String year_id = (null == y) ? " IS NULL" : "=" + y.getID();
        String author_id = (null == a) ? " IS NULL" : "=" + a.getID();
        String author_name = (null == a) ? "NULL" : "" + a.getName();
        String publisher_id = (null == p) ? " IS NULL" : "=" + p.getID();
        String source_id = (null == src) ? " IS NULL" : "=" + src.getID();

        String safe_title = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _title);
        String safe_title_wikilink = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _title_wikilink);

        StringBuilder str_sql = new StringBuilder();
        str_sql.append("SELECT id FROM quot_ref WHERE year_id");
        str_sql.append(year_id);
        str_sql.append(" AND author_id");
        str_sql.append(author_id);
        str_sql.append(" AND title=\"");
        str_sql.append(safe_title);
        str_sql.append("\" AND title_wikilink=\"");
        str_sql.append(safe_title_wikilink);
        str_sql.append("\" AND publisher_id");
        str_sql.append(publisher_id);
        str_sql.append(" AND source_id");
        str_sql.append(source_id);
        TQuotRef result = null;
        try
        {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        result = new TQuotRef(rs.getInt("id"), _title, _title_wikilink,
                                              y, // TQuotYear _year
                                              a, // TQuotAuthor _author,
                                              p, // TQuotPublisher _publisher,
                                              src); // TQuotSource _source
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        }catch(SQLException ex) {
            System.out.println("SQLException (TQuotRef.get):: _author='"+author_name+"'; _title='"+_title+"'; sql='" + str_sql.toString() + "' error=" + ex.getMessage());
        }
        return result;
    }

    /** Selects row from the table 'quot_ref' by ID.<br><br>
     * SELECT year_id,author_id,title,title_wikilink,publisher_id,source_id FROM quot_ref WHERE id=1;
     *
     * @return null if data is absent
     */
    public static TQuotRef getByID (Connect connect,int id) {
        
        StringBuilder str_sql = new StringBuilder();
        str_sql.append("SELECT year_id,author_id,title,title_wikilink,publisher_id,source_id FROM quot_ref WHERE id=");
        str_sql.append(id);
        TQuotRef quot_ref = null;
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        int i = rs.getInt("year_id");
                        TQuotYear _year = (0 == i) ? null : TQuotYear.getByID(connect, i);

                        i = rs.getInt("author_id");
                        TQuotAuthor _author = (0 == i) ? null : TQuotAuthor.getByID(connect, i);

                        byte[] bb = rs.getBytes("title");
                        String _title = null == bb ? null : Encodings.bytesToUTF8(bb);

                        bb = rs.getBytes("title_wikilink");
                        String _title_wikilink = null == bb ? null : Encodings.bytesToUTF8(bb);

                        i = rs.getInt("publisher_id");
                        TQuotPublisher _publisher = (0 == i) ? null : TQuotPublisher.getByID(connect, i);

                        i = rs.getInt("source_id");
                        TQuotSource _source = (0 == i) ? null : TQuotSource.getByID(connect, i);

                        quot_ref = new TQuotRef(id, _title, _title_wikilink,
                                              _year,        // TQuotYear
                                              _author,      // TQuotAuthor
                                              _publisher,   // TQuotPublisher
                                              _source);     // TQuotSource
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotRef.getByID()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return quot_ref;
    }

    /** Inserts (without years) records into the tables: quot_ref, quot_year, quot_author,
     * quot_publisher, and quot_source.<br><br>
     *
     * INSERT INTO quot_ref (author_id,title,title_wikilink,publisher_id,source_id) VALUES (1,"","",NULL,NULL);
     *
     * @param author_id ID of author's name or "NULL",
     * @param _title title of the work
     * @param _title_wikilink link to a book in Wikipedia (format: [[w:title|]]),
     *                        it could be empty ("")
     * @param publisher_id ID of publisher or "NULL"
     * @param source_id ID of source or "NULL"
     * @return inserted record, or null if insertion failed
     */
    private static TQuotRef insertByID (Connect connect,
                                TQuotYear y, TQuotAuthor a,
                                String _title, String _title_wikilink,
                                TQuotPublisher p, TQuotSource src)
    {
        String year_id = (null == y) ? "NULL" : "" + y.getID();
        String author_id = (null == a) ? "NULL" : "" + a.getID();
        String author_name = (null == a) ? "NULL" : "" + a.getName();
        String publisher_id = (null == p) ? "NULL" : "" + p.getID();
        String source_id = (null == src) ? "NULL" : "" + src.getID();
        
        String safe_title = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _title);
        String safe_title_wikilink = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _title_wikilink);
        StringBuilder str_sql = new StringBuilder();
        str_sql.append("INSERT INTO quot_ref (year_id,author_id,title,title_wikilink,publisher_id,source_id) VALUES (");
        str_sql.append(year_id);
        str_sql.append(",");
        str_sql.append(author_id);
        str_sql.append(",\"");
        str_sql.append(safe_title);
        str_sql.append("\",\"");
        str_sql.append(safe_title_wikilink);
        str_sql.append("\",");
        str_sql.append(publisher_id);
        str_sql.append(",");
        str_sql.append(source_id);
        str_sql.append(")");
        TQuotRef result = null;
        try
        {
            Statement s = connect.conn.createStatement ();
            try {
                s.executeUpdate (str_sql.toString());
            } finally {
                s.close();
            }

            s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery ("SELECT LAST_INSERT_ID() as id");
                try {
                    if (rs.next ())
                        result = new TQuotRef(rs.getInt("id"), _title, _title_wikilink,
                                              y, // TQuotYear _year
                                              a, // TQuotAuthor _author,
                                              p, // TQuotPublisher _publisher,
                                              src); // TQuotSource _source
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        }catch(SQLException ex) {
            System.out.println("SQLException (TQuotRef.insert):: _author='"+author_name+"'; _title='"+_title+"'; sql='" + str_sql.toString() + "' error=" + ex.getMessage());
        }
        return result;
    }

    /** Inserts (without years) records into the tables: quot_ref, quot_year, quot_author,
     * quot_publisher, and quot_source.<br><br>
     *
     * INSERT INTO quot_ref (author_id,title,title_wikilink,publisher_id,source_id) VALUES (1,"","",NULL,NULL);
     *
     * @param _author author's name,
     * @param _author_wikilink link to author's name in Wikipedia (format: [[w:name|]]),
     * @param _title title of the work
     * @param _title_wikilink link to a book in Wikipedia (format: [[w:title|]]),
     *                        it could be empty ("")
     * @param _publisher quote book publisher
     * @param _source quote source
     * @return inserted record, or null if insertion failed
     */
    public static TQuotRef insert (Connect connect,String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source)
    {
        if(isEmptyString(_author, _author_wikilink, _title, _title_wikilink, _publisher, _source)) {
            System.out.println("Error (TQuotRef.insert()):: all arguments are empty.");
            return null;
        }

        TQuotAuthor a = TQuotAuthor.getOrInsert(connect, _author, _author_wikilink);
        TQuotPublisher p = TQuotPublisher.getOrInsert(connect, _publisher);
        TQuotSource src = TQuotSource.getOrInsert(connect, _source);

        TQuotYear y = null;
        return insertByID (connect, y, a,
                                _title, _title_wikilink,
                                p, src);
    }

    /** Inserts (with years) records into the tables: quot_ref, quot_year, quot_author,
     * quot_publisher, and quot_source.<br><br>
     *
     * INSERT INTO quot_ref (year_id,author_id,title,title_wikilink,publisher_id,source_id) VALUES (9,1,"","",NULL,NULL);
     *
     * @param page_title word which are described in this article
     *
     * @param _author author's name,
     * @param _author_wikilink link to author's name in Wikipedia (format: [[w:name|]]),
     * @param _title title of the work
     * @param _title_wikilink link to a book in Wikipedia (format: [[w:title|]]),
     *                        it could be empty ("")
     * @param _publisher quote book publisher
     * @param _source quote source
     * @param _from start date of a writing book with the quote
     * @param _to finish date of a writing book with the quote
     * @return inserted record, or null if insertion failed
     */
    public static TQuotRef insertWithYears (Connect connect,String page_title,
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source,
                                int _from,int _to)
    {
        if(isEmptyString(_author, _author_wikilink, _title, _title_wikilink, _publisher, _source)) {
            System.out.println("Error (TQuotRef.insertWithYears()):: all arguments are empty.");
            return null;
        }

        TQuotYear y = TQuotYear.getOrInsert(connect, _from, _to, page_title);
        TQuotAuthor a = TQuotAuthor.getOrInsert(connect, _author, _author_wikilink);
        TQuotPublisher p = TQuotPublisher.getOrInsert(connect, _publisher);
        TQuotSource src = TQuotSource.getOrInsert(connect, _source);

        return insertByID (connect, y, a,
                                _title, _title_wikilink,
                                p, src);
    }

    /** Gets ID of a record or inserts (without years) records (if they are absent)
     * into the tables: quot_ref, quot_year, quot_author, quot_publisher,
     * and quot_source.
     *
     * @param _author author's name,
     * @param _author_wikilink link to author's name in Wikipedia (format: [[w:name|]]),
     * @param _title title of the work
     * @param _title_wikilink link to a book in Wikipedia (format: [[w:title|]]),
     *                        it could be empty ("")
     * @param _publisher quote book publisher
     * @param _source quote source
     * @return found or inserted record, or null.
     */
    public static TQuotRef getOrInsert (Connect connect,String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source)
    {
        if(isEmptyString(_author, _author_wikilink, _title, _title_wikilink, _publisher, _source)) {
            // System.out.println("Error (TQuotRef.getOrInsert()):: all arguments are empty.");
            return null;
        }

        TQuotAuthor a = TQuotAuthor.getOrInsert(connect, _author, _author_wikilink);
        TQuotPublisher p = TQuotPublisher.getOrInsert(connect, _publisher);
        TQuotSource src = TQuotSource.getOrInsert(connect, _source);
        TQuotYear y = null;

        TQuotRef quot_ref = TQuotRef.get(connect, y, a,
                                _title, _title_wikilink,
                                p, src);
        if(null == quot_ref)
            quot_ref = insertByID (connect, y, a,
                                _title, _title_wikilink,
                                p, src);
        return quot_ref;
    }

    public static TQuotRef getOrInsertWithYears (Connect connect,String page_title,
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source,
                                int _from,int _to)
    {
        if(isEmptyString(_author, _author_wikilink, _title, _title_wikilink, _publisher, _source)) {
            // System.out.println("Error (TQuotRef.getOrInsertWithYears()):: all arguments are empty.");
            return null;
        }

        TQuotAuthor a = TQuotAuthor.getOrInsert(connect, _author, _author_wikilink);
        TQuotPublisher p = TQuotPublisher.getOrInsert(connect, _publisher);
        TQuotSource src = TQuotSource.getOrInsert(connect, _source);
        TQuotYear y = TQuotYear.getOrInsert(connect, _from, _to, page_title);

        TQuotRef quot_ref = TQuotRef.get(connect, y, a,
                                _title, _title_wikilink,
                                p, src);
        if(null == quot_ref)
            quot_ref = insertByID (connect, y, a,
                                _title, _title_wikilink,
                                p, src);
        return quot_ref;
    }

    /** Deletes row from the table 'quot_ref' by a value of ID.<br><br>
     * DELETE FROM quot_ref WHERE id=4;
     */
    public void delete (Connect connect) {

        StringBuilder str_sql = new StringBuilder();
        str_sql.append("DELETE FROM quot_ref WHERE id=");
        str_sql.append( id );
        try {
            Statement s = connect.conn.createStatement ();
            try {
                s.execute (str_sql.toString());
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotRef.delete()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
    }

}
