/* TQuotAuthor.java - author of quotation,
 * SQL operations with the table 'quot_author' 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 wikokit.base.wikipedia.sql.Connect;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import wikokit.base.wikipedia.language.Encodings;
import wikokit.base.wikipedia.sql.PageTableBase;

/** Author of quotation and
 * operations with the table 'quot_author' in MySQL Wiktionary parsed database.
 *
 * Remark: quot_author table has UNIQUE compound KEY (name, wikilink),
 * and wikilink field could be NULL.
 */
public class TQuotAuthor {
    
    /** Inique identifier of the author. */
    private int id;

    /** Author's name of the quote. */
    private String name;

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

    private final static TQuotAuthor[] NULL_TQUOTAUTHOR_ARRAY = new TQuotAuthor[0];

    public TQuotAuthor(int _id,String _name,String _wikilink)
    {
        id          = _id;
        name        = _name;
        wikilink    = _wikilink;
    }
    
    public TQuotAuthor(int _id,String _name)
    {
        id          = _id;
        name        = _name;
        wikilink    = "";
    }

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

    /** Gets author's name from database. */
    public String getName() {
        return name;
    }

    /** Gets wikilink to Wikipedia (format: [[w:name|]]) for the author. */
    public String getWikilink() {
        return wikilink;
    }
    
    /** Inserts record into the table 'quot_author'.<br><br>
     *
     * @param _name author's name, it is not empty or NULL
     * @param _wikilink  link to author's name in Wikipedia (format: [[w:name|]]),
     *                   _wikilink can be empty or NULL
     * @return inserted record, or null if insertion failed
     */
    public static TQuotAuthor insertName (Connect connect,String _name) {

        return insertNameWikilink(connect, _name, "");
    } 
      

    /** Inserts record into the table 'quot_author'.<br><br>
     * INSERT INTO quot_author (name,wikilink) VALUES ("Isaac Asimov", "Isaac Asimov");
     *
     * @param _name author's name, it is not empty or NULL
     * @param _wikilink  link to author's name in Wikipedia (format: [[w:name|]]),
     *                   it could be empty ("")
     * @return inserted record, or null if insertion failed
     */
    public static TQuotAuthor insertNameWikilink (Connect connect,String _name,String _wikilink) {

        if(null == _name || 0 == _name.length()) {
            System.out.println("Error (TQuotAuthor.insertNameAndWikilink()):: null argument: author's name.");
            return null;
        }
        if(null == _wikilink)
            _wikilink = "";
        StringBuilder str_sql = new StringBuilder();
        String safe_name     = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _name);
        String safe_wikilink = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _wikilink);
        str_sql.append("INSERT INTO quot_author (name,wikilink) VALUES (\"");
        str_sql.append(safe_name);
        str_sql.append("\", \"");

        str_sql.append(safe_wikilink);
        str_sql.append("\")");

        TQuotAuthor result = null;
        try
        {
            Statement s = connect.conn.createStatement ();
            try {
                s.executeUpdate (str_sql.toString());
                s = connect.conn.createStatement ();
                ResultSet rs = s.executeQuery ("SELECT LAST_INSERT_ID() as id");
                try {
                    if (rs.next ())
                        result = new TQuotAuthor(rs.getInt("id"), _name, _wikilink);
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }

        }catch(SQLException ex) {
            System.out.println("SQLException (TQuotAuthor.insertName):: _name='"+_name+"'; _wikilink='"+_wikilink+"'; sql='" + str_sql.toString() + "' error=" + ex.getMessage());
        }
        return result;
    }

    /** Get's first record from the table 'quot_author' by the author's name.<br><br>.
     * SELECT id,wikilink FROM quot_author WHERE name="Azimov" LIMIT 1;
     *
     * @param _name author's name
     * @return NULL if data is absent
     */
    public static TQuotAuthor getFirst (Connect connect,String _name) {

        if(null == _name || 0 == _name.length()) {
            System.out.println("Error (TQuotAuthor[] TQuotAuthor.get()):: null argument: author's name.");
            return null;
        }
        StringBuilder str_sql = new StringBuilder();
        String safe_name = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _name);
        str_sql.append("SELECT id,wikilink FROM quot_author WHERE name=\"");
        str_sql.append(safe_name);
        str_sql.append("\" LIMIT 1");

        TQuotAuthor result = null;
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        int    _id = rs.getInt("id");
                        byte[] bb = rs.getBytes("wikilink");
                        String _wikilink = null == bb ? "" : Encodings.bytesToUTF8(bb);
                        result = new TQuotAuthor(_id, _name, _wikilink);
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotAuthor.get()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return result;
    }

    /** Get's array of records from the table 'quot_author' by the author's name.<br><br>.
     * SELECT id,wikilink FROM quot_author WHERE name="Azimov";
     *
     * @param _name author's name
     * @return NULL if data is absent
     */
    public static TQuotAuthor[] get (Connect connect,String _name) {

        if(null == _name || 0 == _name.length()) {
            System.out.println("Error (TQuotAuthor[] TQuotAuthor.get()):: null argument: author's name.");
            return null;
        }
        StringBuilder str_sql = new StringBuilder();
        String safe_name = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _name);
        str_sql.append("SELECT id,wikilink FROM quot_author WHERE name=\"");
        str_sql.append(safe_name);
        str_sql.append("\";");

        List<TQuotAuthor> list_authors = null;
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    while (rs.next ())
                    {
                        if(null == list_authors)
                                   list_authors = new ArrayList<TQuotAuthor>();
                        int    _id = rs.getInt("id");
                        byte[] bb = rs.getBytes("wikilink");
                        String _wikilink = null == bb ? "" : Encodings.bytesToUTF8(bb);
                        list_authors.add(new TQuotAuthor(_id, _name, _wikilink));
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotAuthor.get()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        if(null == list_authors)
            return NULL_TQUOTAUTHOR_ARRAY;
        return (TQuotAuthor[])list_authors.toArray(NULL_TQUOTAUTHOR_ARRAY);
    }

    /** Get's first suitable row from the table 'quot_author' by the author's name and wikilink.<br><br>.
     * SELECT id,wikilink FROM quot_author WHERE name="Azimov" AND wikilink="" LIMIT 1;
     *
     * @param _name author's name
     * @param _wikilink author's name, it could be NULL or empty ("")
     *
     * @return NULL if data is absent
     */
    public static TQuotAuthor get (Connect connect, String _name, String _wikilink) {

        if(null == _name || 0 == _name.length()) {
            System.out.println("Error (TQuotAuthor TQuotAuthor.get()):: null argument: author's name.");
            return null;
        }
        if(null == _wikilink)
            _wikilink = "";
        StringBuilder str_sql = new StringBuilder();
        String safe_name     = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _name);
        String safe_wikilink = PageTableBase.convertToSafeStringEncodeToDBWunderscore(connect, _wikilink);
        str_sql.append("SELECT id FROM quot_author WHERE name=\"");
        str_sql.append(safe_name);
        str_sql.append("\" AND wikilink=\"");
        str_sql.append(safe_wikilink);
        str_sql.append("\" LIMIT 1");

        TQuotAuthor result = null;
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                        result = new TQuotAuthor(rs.getInt("id"), _name, _wikilink);
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotAuthor.get()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return result;
    }

    /** Gets ID of a record or inserts record (if it is absent)
     * into the table 'quot_author'.
     *
     * @param _author author's name
     * @param _author_wikilink  link to author's name in Wikipedia (format: [[w:name|]]),
     *                          it could be empty ("")
     */
    public static TQuotAuthor getOrInsert (Connect connect,String _author,String _author_wikilink) {

        if(null == _author || 0 == _author.length())
            return null;

        TQuotAuthor a = TQuotAuthor.getFirst(connect, _author);
        if(null == a)
            a = TQuotAuthor.insertNameWikilink(connect, _author, _author_wikilink);
        return a;
    }

    /** Selects row from the table 'quot_author' by ID.<br><br>
     *
     * SELECT name,wikilink FROM quot_author WHERE id=1
     *
     * @return null if data is absent
     */
    public static TQuotAuthor getByID (Connect connect,int id) {
        
        StringBuilder str_sql = new StringBuilder();
        str_sql.append("SELECT name,wikilink FROM quot_author WHERE id=");
        str_sql.append(id);

        TQuotAuthor quot_author = null;
        try {
            Statement s = connect.conn.createStatement ();
            try {
                ResultSet rs = s.executeQuery (str_sql.toString());
                try {
                    if (rs.next ())
                    {
                        byte[] bb = rs.getBytes("name");
                        String _name = null == bb ? null : Encodings.bytesToUTF8(bb);

                        bb = rs.getBytes("wikilink");
                        String _wikilink = null == bb ? null : Encodings.bytesToUTF8(bb);

                        quot_author = new TQuotAuthor(id, _name, _wikilink);
                    }
                } finally {
                    rs.close();
                }
            } finally {
                s.close();
            }
        } catch(SQLException ex) {
            System.out.println("SQLException (TQuotAuthor.getByID()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
        return quot_author;
    }

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

        StringBuilder str_sql = new StringBuilder();
        str_sql.append("DELETE FROM quot_author 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 (TQuotAuthor.delete()):: sql='" + str_sql.toString() + "' " + ex.getMessage());
        }
    }
    
}
