/*
 * Juick
 * Copyright (C) 2008-2011, Ugnich Anton
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.juick.server;

import com.juick.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author Ugnich Anton
 */
public class PMQueries {

    private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName());

    public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) {
        boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)",
                new Object[] {uid_from, uid_to, body}) > 0;            

        if (ret) {
            sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) "
                    + "VALUES (?,?,NOW(),1) "
                    + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1",
                    new Object[] {uid_from, uid_to});                
        }
        return ret;
    }

    public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) {
        return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)",
                new Object[] {uid, jid}) > 0;        
    }

    public static boolean removePMinRoster(Connection sql, int uid, String jid) {
        boolean ret = false;
        PreparedStatement stmt = null;
        try {
            stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?");
            stmt.setInt(1, uid);
            stmt.setString(2, jid);
            ret = stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(null, stmt);
        }
        return ret;
    }

    public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) {
        List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster "
                + "WHERE user_id=? AND jid=?", Integer.class,
                new Object[] {uid, jid});
            return res.size() > 0;        
    }

    public static String getLastView(Connection sql, int uid_from, int uid_to) {
        String ret = null;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?");
            stmt.setInt(1, uid_from);
            stmt.setInt(2, uid_to);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getString(1);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
        List<User> users = sql.query("SELECT user_id,unread FROM pm_streams "
                + "WHERE user_id_to=? AND unread>0 "
                + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> {
                    com.juick.User u = new com.juick.User();
                    u.UID = rs.getInt(1);
                    u.MessagesCount = rs.getInt(2);
                    return u;
                }, new Object[]{uid});

        if (users.size() < cnt) {
            List<User> addUsers = sql.query("SELECT user_id,user_id_to "
                    + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) "
                    + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) "
                    + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> {
                        User u = new com.juick.User();
                        int uuid = rs.getInt(1) + rs.getInt(2) - uid;
                        u.UID = uuid;
                        return u;
                    }, new Object[]{uid, uid, cnt * 2});
            for (User addUser : addUsers) {
                if (!haveUserInArray(users, addUser.UID)) {
                    users.add(addUser);
                    if (users.size() >= cnt) {
                        break;
                    }
                }
            }
        }
        if (!users.isEmpty()) {
            UserQueries.fillUsersByID(sql, users);
        }

        return users;
    }

    public static boolean haveUserInArray(List<com.juick.User> arr, int uid) {
        for (User user : arr) {
            if (user.UID == uid) {
                return true;
            }
        }
        return false;
    }

    public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) {
        List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm "
                + "WHERE (user_id=? AND user_id_to=?) "
                + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20",
                (rs, rowNum) -> {
                    com.juick.Message msg = new com.juick.Message();
                    int uuid = rs.getInt(1);
                    msg.User = new com.juick.User();
                    msg.User.UID = uuid;
                    msg.Text = rs.getString(2);
                    msg.TimestampString = rs.getString(3);
                    return msg;
                }, new Object[]{uid, uid_to, uid, uid_to});

        sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 "
                + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to});

        return msgs;
    }

    public static List<com.juick.Message> getLastPMInbox(Connection sql, int uid) {
        List<com.juick.Message> msgs = new ArrayList<>(20);

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.Message msg = new com.juick.Message();
                msg.User = new com.juick.User();
                msg.User.UID = rs.getInt(1);
                msg.User.UName = rs.getString(2);
                msg.Text = rs.getString(3);
                msg.TimeAgo = rs.getInt(4);
                msg.TimestampString = rs.getString(5);
                msgs.add(0, msg);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return msgs;
    }

    public static List<com.juick.Message> getLastPMSent(Connection sql, int uid) {
        List<com.juick.Message> msgs = new ArrayList<>(20);

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.Message msg = new com.juick.Message();
                msg.User = new com.juick.User();
                msg.User.UID = rs.getInt(1);
                msg.User.UName = rs.getString(2);
                msg.Text = rs.getString(3);
                msg.TimeAgo = rs.getInt(4);
                msg.TimestampString = rs.getString(5);
                msgs.add(0, msg);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return msgs;
    }
}