/* TQuotation.java - SQL operations with the table 'quotation' in 
 * SQLite Android Wiktionary parsed database.
 *
 * Copyright (c) 2011-2012 Andrew Krizhanovsky <andrew.krizhanovsky at gmail.com>
 * Distributed under EPL/LGPL/GPL/AL/BSD multi-license.
 */

package wikokit.base.wikt.sql.quote;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import wikokit.base.wikt.sql.TLang;
import wikokit.base.wikt.sql.TMeaning;
import wikokit.base.wikt.sql.TWikiText;
import wikokit.base.wikt.word.WQuote;

/** Operations with the table 'quotation' in MySQL Wiktionary parsed database. */
public class TQuote {

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

    /** One sense of a word, field quote.meaning_id in database. */
    private TMeaning meaning;

    /** Language of the quotation, field quote.lang_id. */
    private TLang lang;

    /** Quotation text, field quote.text in database. */
    private String text;

    /** quotation reference information (year, author, etc.), field quote.ref_id in database. */
    private TQuotRef quot_ref;

    private final static TQuote[] NULL_TQUOTE_ARRAY = new TQuote[0];

    public TQuote(int _id, TMeaning _meaning, TLang _lang, String _text,
                  TQuotRef _quot_ref)
    {
        id          = _id;
        meaning     = _meaning;
        lang       = _lang;
        text   = _text;
        quot_ref = _quot_ref;
    }

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

    /** Gets meaning for this quotation from database. */
    public TMeaning getMeaning() {
        return meaning;
    }

    /** Gets language of the quotation. */
    public TLang getLanguage() {
        return lang;
    }

    /** Gets quotation text. */
    public String getText() {
        return text;
    }

    /** Gets reference data about quote from database. */
    public TQuotRef getReference() {
        return quot_ref;
    }

    /** Gets translation of the quotation from database. */
    public String getTranslation(SQLiteDatabase db) {
        TQuotTranslation t = TQuotTranslation.getByID(db, id);
        
        if(null != t)
            return t.getText();    
        return "";
    }

    /** Gets translation of the quotation from database. */
    public String getTransription(SQLiteDatabase db) {
        TQuotTranscription t = TQuotTranscription.getByID(db, id);

        if(null != t)
            return t.getText();
        return "";
    }


    /** Stores quotes related to this meaning into tables:
     * 'quote', 'quot_translation', 'quot_transcription' and 'quot_ref'.
     * The insertion into 'quot_ref' results in updating records in tables:
     * 'quot_year', 'quot_author', 'quot_publisher', and 'quot_source'.
     *
     * @param page_title word which are described in this article
     * @param tmeaning      corresponding record in table 'meaning' to this relation
     * @param lang          language of this meaning
     * @param wquotes       examples and quotations for this meaning
     */
    /*public static void storeToDB (Connect connect,String page_title,
                                  TMeaning _meaning, TLang _lang,
                                  WQuote[] wquotes)
    {
        if(null == _meaning || wquotes.length == 0) return;

        for(WQuote wq : wquotes)
        {
            TQuotRef quot_ref = TQuotRef.getOrInsertWithYears(connect, page_title,
                                        wq.getAuthor(), wq.getAuthorWikilink(),
                                        wq.getTitle(),  wq.getTitleWikilink(),
                                        wq.getPublisher(), wq.getSource(),
                                        wq.getYearFrom(), wq.getYearTo());

            TQuote q = TQuote.insert(connect, _meaning, _lang, wq.getText(), quot_ref);
            if(null != q)
            {
                int quote_id = q.getID();

                TQuotTranslation.  insert(connect, quote_id, wq.getTranslation());
                TQuotTranscription.insert(connect, quote_id, wq.getTranscription());
            }
        }
    }*/


    /** Inserts record into the table quote.<br><br>
     *
     * INSERT INTO quote (meaning_id,lang_id,text,ref_id) VALUES (1,286,"",NULL)
     *
     * @param _meaning meaning of a word corresponding to the quote
     * @param _lang language of the quote
     * @param _text quotation itself
     * @param _quot_ref bibliography and reference data
     * @return inserted record, or null if insertion failed
     */
    /*public static TQuote insert (Connect connect, TMeaning _meaning, TLang _lang, String _text,
                  TQuotRef _quot_ref)
    {
        if(null == _text || _text.length() == 0) {
            System.err.println("Error (TQuote.insert()):: quotation text is empty.");
            return null;
        }
        if(null == _meaning) {
            System.err.println("Error (TQuote.insert()):: _meaning is null.");
            return null;
        }
        if(null == _lang) {
            System.err.println("Error (TQuote.insert()):: _lang is null.");
            return null;
        }
        String quot_ref_id = (null == _quot_ref) ? "NULL" : "" + _quot_ref.getID();
        
        StringBuilder str_sql = new StringBuilder();
        String safe_text = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _text);
        str_sql.append("INSERT INTO quote (meaning_id,lang_id,text,ref_id) VALUES (");
        str_sql.append(_meaning.getID());
        str_sql.append(",");
        str_sql.append(_lang.getID());
        str_sql.append(",\"");
        str_sql.append(safe_text);
        str_sql.append("\",");
        str_sql.append(quot_ref_id);
        str_sql.append(")");
        TQuote 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 TQuote(rs.getInt("id"),
                                              _meaning, _lang, _text, _quot_ref);
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        }catch(SQLException ex) {
            System.err.println("SQLException (TQuotRef.insert):: _text='"+_text+"'; sql='" + str_sql.toString() + "' error=" + ex.getMessage());
        }
        return result;
    }*/

    /** Inserts quote and reference (ref. without years) records into the tables:
     * quote, quot_ref, quot_year, quot_author, quot_publisher, and quot_source.
     *
     * @param _text quotation itself
     * @param _meaning meaning of a word corresponding to the quote
     * @param _lang language of the quote
     * 
     * @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 TQuote insertWithReference (Connect connect,
                                String _text, TMeaning _meaning, TLang _lang,
                                
                                // reference data:
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source)
    {
        TQuotRef quot_ref = TQuotRef.getOrInsert(connect, _author, _author_wikilink,
                                    _title, _title_wikilink, _publisher, _source);

        return TQuote.insert(connect, _meaning, _lang, _text, quot_ref);
    }*/

    /** Inserts quote and reference (ref. without years) records into the tables:
     * quote, quot_ref, quot_year, quot_author, quot_publisher, and quot_source.
     *
     * @param page_title word which are described in this article
     *
     * @param _text quotation itself
     * @param _meaning meaning of a word corresponding to the quote
     * @param _lang language of the quote
     *
     * @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 TQuote insertWithYears (Connect connect, String page_title,
                                String _text, TMeaning _meaning, TLang _lang,

                                // reference data:
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source,
                                int _from, int _to)
    {
        TQuotRef quot_ref = TQuotRef.getOrInsertWithYears(connect, page_title,
                                    _author, _author_wikilink,
                                    _title, _title_wikilink, _publisher, _source,
                                    _from, _to);

        return TQuote.insert(connect, _meaning, _lang, _text, quot_ref);
    }*/

    /** Inserts quote (with translation, transcription) and reference
     * (ref. without years) records into the tables: quote, quot_translation,
     * quot_transcription, quot_ref, quot_year, quot_author, quot_publisher,
     * and quot_source.
     *
     * @param _text quotation itself
     * @param _translation translation of quotation
     * @param _transcription transcription of quotation
     * @param _meaning meaning of a word corresponding to the quote
     * @param _lang language of the quote
     *
     * @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 TQuote insertWithTranslationTranscription (Connect connect,
                                String _text, String _translation, String _transcription,
                                TMeaning _meaning, TLang _lang,

                                // reference data:
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source)
    {
        TQuotRef quot_ref = TQuotRef.getOrInsert(connect, _author, _author_wikilink,
                                    _title, _title_wikilink, _publisher, _source);

        TQuote q = TQuote.insert(connect, _meaning, _lang, _text, quot_ref);
        if(null == q)
            return null;
        int quote_id = q.getID();

        TQuotTranslation.insert(connect, quote_id, _translation);
        TQuotTranscription.insert(connect, quote_id, _transcription);
        return q;
    }*/

    /** Inserts quote (with translation, transcription) and reference
     * (with years) records into the tables: quote, quot_translation,
     * quot_transcription, quot_ref, quot_year, quot_author, quot_publisher,
     * and quot_source.
     *
     * @param page_title word which are described in this article
     *
     * @param _text quotation itself
     * @param _translation translation of quotation
     * @param _transcription transcription of quotation
     * @param _meaning meaning of a word corresponding to the quote
     * @param _lang language of the quote
     *
     * @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 TQuote insertWithYearsTranslationTranscription (
                                Connect connect, String page_title,
                                String _text, String _translation, String _transcription,
                                TMeaning _meaning, TLang _lang,

                                // reference data:
                                String _author,String _author_wikilink,
                                String _title, String _title_wikilink,
                                String _publisher, String _source,
                                int _from, int _to)
    {
        TQuotRef quot_ref = TQuotRef.getOrInsertWithYears(connect, page_title,
                                    _author, _author_wikilink,
                                    _title, _title_wikilink, _publisher, _source,
                                    _from, _to);

        TQuote q = TQuote.insert(connect, _meaning, _lang, _text, quot_ref);
        if(null == q)
            return null;
        int quote_id = q.getID();

        TQuotTranslation.insert(connect, quote_id, _translation);
        TQuotTranscription.insert(connect, quote_id, _transcription);
        return q;
    }*/

    /** Selects rows from the table 'quote' by the meaning_id.<br><br>.
     *
     * SELECT id,lang_id,text,ref_id FROM quote WHERE meaning_id=3
     *
     * @return empty array if data is absent
     */
    public static TQuote[] get (SQLiteDatabase db,TMeaning _meaning) {

        List<TQuote> list_quote = null;
        
        if(null == _meaning) {
            System.err.println("Error (wikt_parsed TQuote.get()):: null argument: meaning.");
            return NULL_TQUOTE_ARRAY;
        }
        
        // SELECT id,lang_id,text,ref_id FROM quote WHERE meaning_id=3
        Cursor c = db.query("quote", 
                new String[] { "id", "lang_id", "text", "ref_id"}, 
                "meaning_id=" + _meaning.getID(), 
                null, null, null, null);
        
        if (c.moveToFirst()) {
            do {
                int i_id = c.getColumnIndexOrThrow("id");
                int i_lang_id = c.getColumnIndexOrThrow("lang_id");
                int i_text = c.getColumnIndexOrThrow("text");
                int i_ref_id = c.getColumnIndexOrThrow("ref_id");
                
                int  _id        = c.getInt(i_id);
                int  _lang_id   = c.getInt(i_lang_id);
                String _text    = c.getString(i_text);
                int  _ref_id    = c.getInt(i_ref_id);
                
                TLang _lang = TLang.getTLangFast(_lang_id);
                TQuotRef _quot_ref = TQuotRef.getByID(db, _ref_id);

                if(null != _lang && null != _quot_ref) {
                    if(null == list_quote)
                               list_quote = new ArrayList<TQuote>();

                    list_quote.add(new TQuote(_id, _meaning, _lang, _text, _quot_ref));
                }
                   
            } while (c.moveToNext());
        }
        if (c != null && !c.isClosed()) {
            c.close();
        }
        /*
        StringBuilder str_sql = new StringBuilder();
        str_sql.append("SELECT id,lang_id,text,ref_id FROM quote WHERE meaning_id=");
        str_sql.append(meaning.getID());
        
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while (rs.next ())
                    {
                        int    _id = rs.getInt("id");
                        TLang _lang = TLang.getTLangFast(rs.getInt("lang_id"));
                        String _text = Encodings.bytesToUTF8(rs.getBytes("text"));
                        TQuotRef _quot_ref = TQuotRef.getByID(connect, rs.getInt("ref_id"));

                        if(null != _lang && null != _quot_ref) {
                            if(null == list_quote)
                                       list_quote = new ArrayList<TQuote>();

                            list_quote.add(new TQuote(_id, meaning, _lang, _text, _quot_ref));
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (TQuote.get()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }*/
        if(null == list_quote)
            return NULL_TQUOTE_ARRAY;
        return (TQuote[])list_quote.toArray(NULL_TQUOTE_ARRAY);
    }

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

        StringBuilder str_sql = new StringBuilder();
        str_sql.append("DELETE FROM quote 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.err.println("SQLException (TQuote.delete()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
    }*/
}
