/*
 * 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 (?,?,?)",
                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",
                    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 (?,?)",
                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,
                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) {
        return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " +
                "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " +
                "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> {
                    com.juick.User u = new com.juick.User();
                    u.setUID(rs.getInt(1));
                    u.setUName(rs.getString(2));
                    u.MessagesCount = rs.getInt(3);
                    return u;
                }, uid, cnt);
    }

    public static boolean haveUserInArray(List<com.juick.User> arr, int uid) {
        for (User user : arr) {
            if (user.getUID() == 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.setUser(new User());
                    msg.getUser().setUID(uuid);
                    msg.setText(rs.getString(2));
                    msg.setDate(rs.getTimestamp(3));
                    return msg;
                }, uid, uid_to, uid, uid_to);

        sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 "
                + "WHERE user_id_to=? AND user_id=?", 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()),pm.ts 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.setUser(new User());
                msg.getUser().setUID(rs.getInt(1));
                msg.getUser().setUName(rs.getString(2));
                msg.setText(rs.getString(3));
                msg.TimeAgo = rs.getInt(4);
                msg.setDate(rs.getTimestamp(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()),pm.ts 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.setUser(new User());
                msg.getUser().setUID(rs.getInt(1));
                msg.getUser().setUName(rs.getString(2));
                msg.setText(rs.getString(3));
                msg.TimeAgo = rs.getInt(4);
                msg.setDate(rs.getTimestamp(5));
                msgs.add(0, msg);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return msgs;
    }
}