/* TRelation.java - SQL operations with the table 'relation' 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.wikt.constant.Relation;
import wikokit.base.wikt.word.WRelation;
import wikokit.base.wikt.util.WikiText;

import wikokit.base.wikipedia.sql.Connect;
import wikokit.base.wikipedia.sql.Statistics;
import wikokit.base.wikipedia.sql.PageTableBase;
import wikokit.base.wikipedia.language.Encodings;

import java.sql.*;

import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Collection;
import wikokit.base.wikt.constant.Label;
import wikokit.base.wikt.util.LabelsWikiText;

/** An operations with the table 'relation' in MySQL wiktionary_parsed database.
 *
 * @see wikt.word.WRelation
 */
public class TRelation {
    private static final boolean DEBUG = true;
    
    /** Unique identifier in the table 'relation'. */
    private int id;

    /** One sense of a word. */
    private TMeaning meaning;               // int meaning_id;

    /** Text (wikified sometimes). */
    private TWikiText wiki_text;            // int wiki_text_id

    /** Semantic relation. */
    private TRelationType relation_type;    // int relation_type_id

    /** Summary of the definition for which synonyms (antonyms, etc.) are being given,
     * e.g. "flrink with cumplus" or "furp" in text
     * <PRE>
     * * (''flrink with cumplus''): [[flrink]], [[pigglehick]]
     * * (''furp''): [[furp]], [[whoodleplunk]]
     * </PRE>
     *
     * Disadvantage: the summary "flrink with cumplus" is repeated twice 
     *              in table for "flrink" and "pigglehick".
     *
     * Comment: is used in English Wiktionary, see http://en.wiktionary.org/wiki/Wiktionary:Entry_layout_explained#Synonyms
     * It is not used in Russian Wiktionary (NULL in database).
     */
    private String meaning_summary;
    
    private final static TRelation[] NULL_TRELATION_ARRAY = new TRelation[0];

    public TRelation(int _id,TMeaning _meaning,TWikiText _wiki_text,
                    TRelationType _relation_type,String _meaning_summary)
    {
        id              = _id;
        meaning         = _meaning;
        wiki_text       = _wiki_text;
        relation_type   = _relation_type;

        meaning_summary = _meaning_summary;
    }

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

    /** Gets meaning from database */
    public TMeaning getMeaning() {
        return meaning;
    }

    /** Gets a summary of the semantic relation meaning (e.g. title of list of synonyms). */
    public String getMeaningSummary() {
        return meaning_summary;
    }

    /** Gets text (wikified sometimes). */
    public TWikiText getWikiText() {
        return wiki_text;
    }
    
    /** Gets type of semantic relation. */
    public Relation getRelationType() {
        if(null == relation_type)
            return null;
        return relation_type.getRelation();
    }

    /** Inserts records into tables: 'wiki_text' and 'relation'.
     * The insertion into 'wiki_text' results in updating records in tables:
     * 'wiki_text_words', 'page_inflecton', 'inflection', and 'page'.
     *
     * @param tmeaning      corresponding record in table 'meaning' to this relation
     * @param meaning_n     number of this meaning (for polysemous words)
     * (e.g. m_relations.get(Relation.hypernymy)[meaning_n] = WRelation for this meaning.)
     * @param m_relations   map from semantic relation (e.g. synonymy) to array of WRelation (one WRelation contains a list of synonyms for one meaning).
     */
    public static void storeToDB (Connect connect,TMeaning tmeaning,int meaning_n,
                                  Map<Relation, WRelation[]> m_relations) {

        if(null == tmeaning || null == m_relations || m_relations.isEmpty()) return;

        Collection<Relation> rr = m_relations.keySet();
        for(Relation r : rr) {

            TRelationType trelation_type = TRelationType.getRelationFast(r);
            WRelation[] wr = m_relations.get(r);
            if(meaning_n < wr.length && null != wr[meaning_n]) {

                WRelation cur_rel = wr[meaning_n];

                String meaning_summary = cur_rel.getMeaningSummary();

                LabelsWikiText[] phrases = cur_rel.get();
                for(LabelsWikiText p : phrases) {

                    Label[] labels = p.getLabels();
                    // save labels to the table 'label_relation'
                    // todo
                    // ...
                    
                    WikiText wt = p.getWikiText();
                    TWikiText twiki_text = TWikiText.storeToDB(connect, wt);

                    if(null != twiki_text) {
                        TRelation.insert(connect, tmeaning, twiki_text, 
                                        trelation_type, meaning_summary);
                    }
                }
            }
        }
    }

    /** Inserts record into the table 'relation'.<br><br>
     * INSERT INTO relation (meaning_id,wiki_text_id,relation_type_id) VALUES (11,12,13);
     * or
     * INSERT INTO relation (meaning_id,wiki_text_id,relation_type_id,meaning_summary) VALUES (11,12,13,"sum");
     *
     * @param meaning       corresponding meaning of the word
     * @param wiki_text     synonym word (or phrase), or antonym, etc.
     * @param relation_type semantic relation
     * @param meaning_summary summary of the definition for which synonyms
     *                      (antonyms, etc.) are being given. It could be null.
     *
     * @return null if data is absent
     */
    public static TRelation insert (Connect connect,
            TMeaning meaning,TWikiText wiki_text,TRelationType relation_type,
            String meaning_summary) {

        if(null == meaning || null == wiki_text || null == relation_type) {
            System.out.println("Error (wikt_parsed TRelation.insert()):: null arguments, meaning="+meaning+", wiki_text="+wiki_text+", relation_type="+relation_type);
            return null;
        }
        
        StringBuilder str_sql = new StringBuilder();
        TRelation relation = null;
        try
        {
            boolean b_sum = null != meaning_summary && meaning_summary.length() > 0;

            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("INSERT INTO relation (meaning_id,wiki_text_id,relation_type_id");

                if(b_sum)
                    str_sql.append(",meaning_summary");

                str_sql.append(") VALUES (");
                str_sql.append(meaning.getID());
                str_sql.append(",");
                str_sql.append(wiki_text.getID());
                str_sql.append(",");
                str_sql.append(relation_type.getID());

                if(b_sum) {
                    str_sql.append(",\"");
                    str_sql.append(PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, meaning_summary));
                    str_sql.append("\"");
                }

                str_sql.append(")");
                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 ())
                        relation = new TRelation(rs.getInt("id"), meaning, wiki_text,
                                            relation_type, meaning_summary);
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        }catch(SQLException ex) {
            System.out.println("SQLException (TRelation.insert()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return relation;
    }

    /** Selects rows from the table 'relation' by the meaning_id.<br><br>.
     * SELECT id,wiki_text_id,relation_type_id,meaning_summary FROM relation WHERE meaning_id=11;
     * @return empty array if data is absent
     */
    public static TRelation[] get (Connect connect,TMeaning meaning) {

        if(null == meaning) {
            System.out.println("Error (wikt_parsed TRelation.get()):: null argument: meaning.");
            return NULL_TRELATION_ARRAY;
        }
        
        StringBuilder str_sql = new StringBuilder();
        List<TRelation> list_rel = null;
        
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT id,wiki_text_id,relation_type_id,meaning_summary FROM relation WHERE meaning_id=");
                str_sql.append(meaning.getID());
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while (rs.next ())
                    {
                        int          id =                               rs.getInt("id");
                        TWikiText    wt = TWikiText.getByID(connect,    rs.getInt("wiki_text_id"));
                        TRelationType r = TRelationType.getRelationFast(rs.getInt("relation_type_id"));

                        if(null != wt && null != r) {
                            if(null == list_rel)
                                       list_rel = new ArrayList<TRelation>();

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

                            list_rel.add(new TRelation(id, meaning, wt, r, sum));
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TRelation.java get()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }

        if(null == list_rel)
            return NULL_TRELATION_ARRAY;
        return (TRelation[])list_rel.toArray(NULL_TRELATION_ARRAY);
    }


    /** Counts number of rows from the table 'relation' related to the meaning_id.<br><br>.
     * SELECT COUNT(*) as a FROM relation WHERE meaning_id=11;
     * @return empty array if data is absent
     */
    public static int count (Connect connect,TMeaning meaning) {

        if(null == meaning) {
            System.out.println("Error (wikt_parsed TRelation.count()):: null argument: meaning.");
            return 0;
        }
        
        StringBuilder str_sql = new StringBuilder();
        int n = 0;

        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT COUNT(*) AS n FROM relation WHERE meaning_id=");
                str_sql.append(meaning.getID());
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                        n = rs.getInt("n");
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TRelation.count()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return n;
    }

    /** Selects row from the table 'relation' by ID.<br><br>
     * SELECT meaning_id,wiki_text_id,relation_type_id,meaning_summary FROM relation WHERE id=1;
     * @return null if data is absent
     */
    public static TRelation getByID (Connect connect,int id) {
        
        StringBuilder str_sql = new StringBuilder();
        TRelation relation = null;

        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT meaning_id,wiki_text_id,relation_type_id,meaning_summary FROM relation WHERE id=");
                str_sql.append(id);
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        TMeaning      m = TMeaning. getByID( connect,   rs.getInt("meaning_id"));
                        TWikiText    wt = TWikiText.getByID( connect,   rs.getInt("wiki_text_id"));
                        TRelationType r = TRelationType.getRelationFast(rs.getInt("relation_type_id"));
                        if(null != m && null != wt && null != r) {

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

                            relation = new TRelation(id, m, wt, r, sum);
                        }
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TRelation.java getByID()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return relation;
    }

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

        if(null == relation) {
            System.out.println("Error (wikt_parsed TRelation.delete()):: null argument page.");
            return;
        }
        StringBuilder str_sql = new StringBuilder();
        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("DELETE FROM relation WHERE id=");
                str_sql.append(relation.getID());
                s.execute (str_sql.toString());
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TRelation.java delete()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
    }

    /** Gets all semantic relation (pairs of word).
     * @return pairs of words,
     * e.g. car -> carriage, car -> automobile (synonyms)
     *      car -> vehicle (hyperohym)
     * or empty map, if relations are absent
     */
    public static Map<String,List<String>> getAllWordPairs (Connect connect) {

        // for each relation: get page<->wiki_text<->wiki_word + todo: type of relation

        long    t_start;
        float   t_work;

        int n_total = Statistics.Count(connect, "relation");
        System.out.println("Total relations: " + n_total);
        if(-1 == n_total)
            return null;
        t_start = System.currentTimeMillis();
        StringBuilder str_sql = new StringBuilder();
        Map<String,List<String>> m_words = null;

        try {
            Statement s = connect.conn.createStatement ();
            try {
                str_sql.append("SELECT meaning_id,wiki_text_id FROM relation");
    //str_sql.append("SELECT meaning_id,wiki_text_id FROM relation LIMIT 1000");
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    m_words = new HashMap<String,List<String>> ();
                    int n_cur = 0;
                    while (rs.next ())
                    {
                        //int          id =                               rs.getInt("id");
                        TMeaning      m = TMeaning. getByID( connect,   rs.getInt("meaning_id"));
                        TWikiText    wt = TWikiText.getByID( connect,   rs.getInt("wiki_text_id"));
                        //int wiki_text_id =  rs.getInt("wiki_text_id");

                        // skip today
                        /*
                        TRelationType r = TRelationType.getRelationFast(rs.getInt("relation_type_id"));
                        TRelation relation = null;
                        if(null != m && null != wt) && null != r)
                            relation = new TRelation(id, m, wt, r);*/

                        String page = null;
                        if(null != m) {
                            TLangPOS lang_pos = m.getLangPOS(connect);
                            if(null != lang_pos) {
                                TPage tpage = lang_pos.getPage();
                                if(null != tpage) {
                                    page = tpage.getPageTitle();
                                }
                            }
                        }

                        String w_rel = null;    // wiki word relation
                        if(null != page) {
                            //TWikiTextWords t_word = TWikiTextWords.getByID(connect, id);
                            TWikiTextWords t_word = TWikiTextWords.getOneByWikiText(connect, wt);
                            if(null != t_word) {
                                TPage tpage = t_word.getPage();
                                if(null != tpage) {
                                    w_rel = tpage.getPageTitle();
                                }
                            }
                        }

                        if(null != page && null != w_rel) {
                            List<String> list_rel = m_words.get(page);
                            if(null == list_rel) {
                                list_rel = new ArrayList<String>();
                                list_rel.add(w_rel);
                                m_words.put(page, list_rel);
                            } else {
                                if(!list_rel.contains(w_rel))
                                    list_rel.add(w_rel);
                            }
                        }

                        if(DEBUG && 0 == ++n_cur % 1000) {   // % 100
                            //if(n_cur<10900) continue;
                            long    t_cur, t_remain;

                            t_cur  = System.currentTimeMillis() - t_start;
                            t_remain = (long)((n_total - n_cur) * t_cur/(60f*1000f*(float)(n_cur)));
                                       // where time for 1 page = t_cur / n_cur
                                       // in min, since /(60*1000)
                            t_cur = (long)(t_cur/(60f*1000f));
                            //t_cur = t_cur/(60f*1000f));

                            System.out.println(n_cur + ": [" + page + ", " + w_rel +
                                    "], duration: "  + t_cur +   // t_cur/(60f*1000f) +
                                    " min, remain: " + t_remain +
                                    " min");
                        }


                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (wikt_parsed TRelation.java getAllWordPairs()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }

        long  t_end;
        t_end  = System.currentTimeMillis();
        t_work = (t_end - t_start)/1000f; // in sec
        System.out.println("\n\nTime sec:" + t_work +
                "\nTotal relations: " + n_total);

        return m_words;
    }
    
    /** Gets a word defined by a semantic relation (e.g. the page "car" contains "[[automobile]]"
     * in a section "Synonyms", then the "automobile" will be returned).
     * @param trelation defines relation (e.g. synonymy) and source word (e.g. "car")
     * @return word defined by a semantic relation (e.g. "automobile" for "car"), or null if search failed
     */
    public static TPage getWikifiedPage (Connect connect,TRelation trelation) {
        
        if(null == trelation)
            return null;
        
        TWikiText twt = trelation.getWikiText();
        if(null == twt)
            return null;

        TWikiTextWords t_word = TWikiTextWords.getOneByWikiText(connect, twt);
        if(null != t_word)
            return t_word.getPage();
        return null;
    }

    /** Gets type of semantic relation between a pair of word: title of a page
     * (page_title) and word on this page (word).
     */
    private static Relation getRelationBetweenPageTitleAndWord (Connect connect,String page_title,String word) {
        
        if(0 == word.length() || 0 == page_title.length() || word.equals(page_title))
            return null;

        TPage page = TPage.get(connect, page_title);    if(null == page)        return null;
        TPage page_word = TPage.get(connect, word);     if(null == page_word)   return null;
            
        TLangPOS[] lp = TLangPOS.get(connect, page);
        for(TLangPOS lang_pos : lp) {
            TMeaning[] tm = TMeaning.get(connect, lang_pos);
            for(TMeaning tmeaning : tm) {
                TRelation[] tr = TRelation.get(connect, tmeaning);
                for(TRelation trelation : tr) {
                    TPage p = TRelation.getWikifiedPage(connect, trelation);
                    if(null != p && word.equals(p.getPageTitle())) {
                        return trelation.getRelationType();
                    }
                }
            }
        }
        return null;
    }
    
    /** Gets type of semantic relation between a pair of word (word1 and word2).
     */
    public static Relation getRelationType (Connect connect,String word1,String word2) {
        Relation r;

        r = TRelation.getRelationBetweenPageTitleAndWord(connect, word1, word2);
        if(null != r)
            return r;
            
        r = TRelation.getRelationBetweenPageTitleAndWord(connect, word2, word1);
        return r;
    }
}
