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

package wikokit.base.wikt.sql;

import wikokit.base.wikipedia.sql.Connect;
import java.sql.*;

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

/** SQL operations with the table 'translation_entry' in Wiktionary parsed database.
 *
 * @see wikt.word.WTranslationEntry
 */
public class TTranslationEntry {

    /** Unique identifier in the table 'translation_entry'. */
    private int id;

    /** Link to the table 'translation', which links to language, POS, and (may be) meaning.
     */
    private TTranslation translation;   // int translation_id;

    /** Translation into 'lang'.
     */
    private TLang lang;                 // int lang_id;
    
    /** Text of translation (Wikified text). */
    private TWikiText wiki_text;

    private final static TTranslationEntry[] NULL_TTRANSLATIONENTRY_ARRAY = new TTranslationEntry[0];

    public TTranslationEntry(int _id,TTranslation _translation,TLang _lang,TWikiText _wiki_text) {
        id          = _id;
        translation = _translation;
        lang        = _lang;
        wiki_text  = _wiki_text;
    }

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

    /** Gets wikified text from the database' table 'wiki_text'. */
    public TWikiText getWikiText() {
        return wiki_text;
    }

    /** Gets translation from the database' table 'translation'. */
    public TTranslation getTranslation() {
        return translation;
    }

    /** Gets language from database */
    public TLang getLang() {
        return lang;
    }

    /** Inserts record into the table 'meaning'.<br><br>
     * INSERT INTO translation_entry (translation_id,lang_id,wiki_text_id) VALUES (1,2,3);
     * @param lang_pos  ID of language and POS of wiki page which will be added
     * @param meaning_id defines meaning (sense) in table 'meaning', it could be null (todo check)
     * @param meaning_summary
     * @return inserted record, or null if insertion failed
     */
    public static TTranslationEntry insert (Connect connect,TTranslation trans,
            TLang lang,TWikiText wiki_text) {

        if(null == trans || null == lang || null == wiki_text) {
            System.out.println("Error (wikt_parsed TTranslationEntry.insert()):: null arguments: trans = "+trans+
                    "; lang="+ lang +"; wiki_text=" + wiki_text);
            return null;
        }
        
        StringBuilder str_sql = new StringBuilder();
        TTranslationEntry trans_entry = null;
        try
        {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("INSERT INTO translation_entry (translation_id,lang_id,wiki_text_id) VALUES (");
                str_sql.append(trans.getID());
                str_sql.append(",");
                str_sql.append(lang.getID());
                str_sql.append(",");
                str_sql.append(wiki_text.getID());
                str_sql.append(")");
                if(s.executeUpdate (str_sql.toString()) > 0) {
                    ResultSet rs = connect.conn.prepareStatement( "SELECT LAST_INSERT_ID() AS id" ).executeQuery();
                    try {
                        if (rs.next ()) {
                            trans_entry = new TTranslationEntry(rs.getInt("id"), trans, lang, wiki_text);
                            //System.out.println("TTranslationEntry.insert()):: summary='" + trans.getMeaningSummary() +
                            //        "'; id=" + rs.getInt("id") +
                            //        "'; translation_id=" + trans.getID() +
                            //        "; wiki_text='" + wiki_text.getText() + "; lang='" + lang.getLanguage().getName() + "'");
                        }
                    } finally {
                        rs.close();
                    }
                }
            } finally {
                s.close();
            }
        }catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TTranslationEntry.insert()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return trans_entry;
    }

    /** Selects rows from the table 'translation_entry' by ID.<br><br>
     * SELECT translation_id,lang_id,wiki_text_id FROM translation_entry WHERE id=1;
     * @return empty array if data is absent
     */
    public static TTranslationEntry getByID (Connect connect,int id) {
        
        StringBuilder str_sql = new StringBuilder();
        TTranslationEntry trans_entry = null;
        
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT translation_id,lang_id,wiki_text_id FROM translation_entry WHERE id=");
                str_sql.append(id);
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        TTranslation trans = TTranslation.getByID(connect,  rs.getInt("translation_id"));
                        TLang        lang  = TLang.getTLangFast(            rs.getInt("lang_id"));
                        TWikiText    wiki_text = TWikiText.getByID(connect, rs.getInt("wiki_text_id"));

                        if(null != trans && null != lang && null != wiki_text)
                            trans_entry = new TTranslationEntry(id, trans, lang, wiki_text);
                        else
                            System.out.println("Error (wikt_parsed TTranslationEntry.getByID()):: null value: trans = "+trans+
                            "; lang="+ lang +"; wiki_text=" + wiki_text);
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TTranslationEntry.getByID()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return trans_entry;
    }
    
    /** Selects rows from the table 'translation_entry' by translation ID and language ID.<br><br>
     * SELECT id,wiki_text_id FROM translation_entry WHERE translation_id=1 AND lang_id=2;
     * @return empty array if data is absent
     */
    public static TTranslationEntry[] getByLanguageAndTranslation (Connect connect,
                                        TTranslation trans, TLang lang) {

        if(null == trans || null == lang) {
            System.out.println("Error (TTranslationEntry.getByLanguageAndTranslation()):: null arguments, trans="+trans+", lang="+lang);
            return null;
        }
        
        StringBuilder str_sql = new StringBuilder();
        List<TTranslationEntry> list_trans = null;
        
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT id,wiki_text_id FROM translation_entry WHERE translation_id=");
                             // SELECT id,wiki_text_id FROM translation_entry WHERE translation_id=1 AND lang_id=2;
                str_sql.append(trans.getID());
                str_sql.append(" AND lang_id=");
                str_sql.append(lang.getID());
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while(rs.next ())
                    {
                        int id  = rs.getInt("id");
                        TWikiText wiki_text = TWikiText.getByID(connect, rs.getInt("wiki_text_id"));

                        if(null != wiki_text) {
                            if(null == list_trans)
                                       list_trans = new ArrayList<TTranslationEntry>();
                            list_trans.add(new TTranslationEntry(id, trans, lang, wiki_text));
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TTranslationEntry.getByLanguageAndTranslation()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        if(null == list_trans)
            return NULL_TTRANSLATIONENTRY_ARRAY;
        return ((TTranslationEntry[])list_trans.toArray(NULL_TTRANSLATIONENTRY_ARRAY));
    }

    /** Selects rows from the table 'translation_entry' by wiki text ID and 
     * language ID. Selects one language entry from one translation box.<br><br>
     * 
     * SELECT id,translation_id FROM translation_entry WHERE wiki_text_id=3 AND lang_id=2;
     * @return empty array, if data is absent
     */
    public static TTranslationEntry[] getByWikiTextAndLanguage (Connect connect,
                                        TWikiText wiki_text, TLang lang) {
                                        
        if(null == wiki_text || null == lang) {
            System.out.println("Error (TTranslationEntry.getByWikiTextAndLanguage()):: null arguments, wiki_text="+wiki_text+", lang="+lang);
            return null;
        }

        StringBuilder str_sql = new StringBuilder();
        List<TTranslationEntry> list_entry = null;

        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT id,translation_id FROM translation_entry WHERE wiki_text_id=");
                             // SELECT id,translation_id FROM translation_entry WHERE wiki_text_id=3 AND lang_id=2;
                str_sql.append(wiki_text.getID());
                str_sql.append(" AND lang_id=");
                str_sql.append(lang.getID());
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while (rs.next ())
                    {
                        TTranslation trans = TTranslation.getByID(connect, rs.getInt("translation_id"));
                        if(null != trans) {
                            if(null == list_entry)
                                list_entry = new ArrayList<TTranslationEntry>();
                            list_entry.add(new TTranslationEntry(rs.getInt("id"), trans, lang, wiki_text));
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TTranslationEntry.getByWikiTextAndLanguage()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }

        if(null == list_entry)
            return NULL_TTRANSLATIONENTRY_ARRAY;
        return (TTranslationEntry [])list_entry.toArray(NULL_TTRANSLATIONENTRY_ARRAY);
    }

    /** Selects rows (all languages and translations entries from one translation box)
     * from the table 'translation_entry' by translation ID.<br><br>
     * SELECT id,lang_id,wiki_text_id FROM translation_entry WHERE translation_id=1;
     * @return empty array if data is absent
     */
    public static TTranslationEntry[] getByTranslation (Connect connect,TTranslation trans) {

        if(null == trans) {
            System.out.println("Error (TTranslationEntry.getByTranslation()):: null arguments: translation.");
            return null;
        }

        StringBuilder str_sql = new StringBuilder();
        List<TTranslationEntry> list_trans = null;
        
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT id,lang_id,wiki_text_id FROM translation_entry WHERE translation_id=");
                str_sql.append(trans.getID());
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while(rs.next ())
                    {
                        int   id    = rs.getInt("id");
                        TLang tlang = TLang.getTLangFast(rs.getInt("lang_id"));
                        TWikiText wiki_text = TWikiText.getByID(connect, rs.getInt("wiki_text_id"));

                        if(null != tlang && null != wiki_text) {
                            if(null == list_trans)
                                       list_trans = new ArrayList<TTranslationEntry>();
                            list_trans.add(new TTranslationEntry(id, trans, tlang, wiki_text));
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed getByWikiTextAndLanguage.getByTranslation()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        if(null == list_trans)
            return NULL_TTRANSLATIONENTRY_ARRAY;
        return ((TTranslationEntry[])list_trans.toArray(NULL_TTRANSLATIONENTRY_ARRAY));
    }

    /** Deletes row from the table 'translation_entry' by a value of ID.<br>
     *  DELETE FROM translation_entry WHERE id=1;
     * @param  id  unique ID in the table `translation_entry`
     */
    public static void delete (Connect connect,TTranslationEntry trans_entry) {

        if(null == trans_entry) {
            System.out.println("Error (wikt_parsed TTranslationEntry.delete()):: null argument 'translation_entry'");
            return;
        }
        StringBuilder str_sql = new StringBuilder();
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("DELETE FROM translation_entry WHERE id=");
                str_sql.append(trans_entry.getID());
                s.execute (str_sql.toString());
                //System.out.println("TTranslationEntry.delete()):: summary='" + trans_entry.getTranslation().getMeaningSummary() +
                //        "'; id=" + trans_entry.getID() + "; wiki_text='" + trans_entry.getWikiText().getText() + "'");
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TTranslationEntry.delete()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
    }

}
