package database;

import data.Tweet;
import data.User;
import java.sql.SQLException;
import java.util.Locale;

/**
 * Utilities to create queries.
 *
 * @author Maurice Laveaux
 */
public class QueryUtils {

    public final static String insertProfile
            = buildQuery("twitteruser", new String[]{"userid"},
            "userid", "displayname", "timezone", "tweetcount", "followercount",
            "followedcount", "location", "tweetname", "createdat::timestamptz",
            "language", "description", "verified");

    public final static String insertTweet
            = buildQuery("tweet", new String[]{"tweetid"},
            "tweetid", "createdat::timestamptz", "favcount", "retweetcount",
            "text", "coordinates::point",
            "language", "retweetid", "replytweetid", "place",
            "userid", "category");

    public final static String insertHash
            = buildQuery("hashtag", null, "tweetid", "hashtag");

    public final static String insertUserUrl
            = buildQuery("userUrl", null, "userid", "url");

    public final static String insertTweetUrl
            = buildQuery("tweetUrl", null, "tweetid", "url");

    public final static String insertMentions
            = buildQuery("mentionsuser", null, "tweetid", "userid");

    public final static String insertBrand
            = buildQuery("mentionsbrand", null, "tweetid", "brand");

    /**
     * Builds an upsert query for a table and keys.
     *
     * @param table Table to insert or update the data.
     * @param primaryKeys The primary keys for the table or null if all keys
     * form the primary key.
     * @param keys The keys to be inserted or updated.
     * @return A SQL query containing named parameters.
     */
    private static String buildQuery(String table, String[] primaryKeys,
            String... keys) {
        String sep = "";
        String fields = ""; // a, b, c
        String values = ""; // :a, :b, :c
        String set_values = ""; // a = nv.a, b = nv.b, c = nv.c
        String pkey_matches = "";
        for (String field : keys) {
            String key = field.replaceFirst("::\\w+", "");
            fields += sep + key;
            values += sep + ":" + field;
            // "u" is "table that gets updated", "nv" is "new values"
            set_values += sep + key + " = nv." + key;
            sep = ", ";
        }
        sep = "";
        // assume that all fields form the primary key if pks are missing
        if (primaryKeys == null) {
            primaryKeys = keys;
        }
        for (String pkey : primaryKeys) {
            assert !pkey.contains("::") : "Specify primary keys when type cast is given";
            pkey_matches += sep + "u." + pkey + " = nv." + pkey;
            sep = " AND ";
        }

        String sql;
        // CTE that holds new values
        sql = "WITH nv (" + fields + ") AS (\n";
        sql += "    VALUES (" + values + ")\n";
        sql += ")\n";
        // CTE that tries to update the table with new values (PSQL extension)
        sql += ", upsert AS (\n";
        sql += "    UPDATE " + table + " u SET " + set_values + " FROM nv\n";
        sql += "    WHERE " + pkey_matches + " RETURNING 1\n";
        sql += ")\n";
        // if nothing got updated, insert new entry
        sql += "INSERT INTO " + table + " (" + fields + ")\n";
        sql += "SELECT " + fields + " FROM nv\n";
        sql += "WHERE NOT EXISTS (SELECT 1 FROM upsert)";
        return sql;
    }

    public static void setInsertParams(NamedPreparedStatement tweetStatement,
            NamedPreparedStatement profileStatement,
            Tweet tweet, String tweetText) throws SQLException {
        tweetStatement.setLong("tweetid", tweet.id);
        tweetStatement.setTimestamp("createdat", tweet.created_at);
        tweetStatement.setInt("favcount", tweet.favorite_count);
        tweetStatement.setLong("retweetcount", tweet.retweet_count);
        tweetStatement.setString("text", tweetText);
        if (tweet.coordinates != null) {
            float[] coords = tweet.coordinates.coordinates;
            String coords_str = String.format(Locale.ENGLISH, "%f,%f", coords[0], coords[1]);
            tweetStatement.setString("coordinates", coords_str);
        } else {
            tweetStatement.setString("coordinates", null);
        }
        tweetStatement.setString("language", tweet.lang);
        if (tweet.retweeted_status != null) {
            tweetStatement.setLong("retweetid", tweet.retweeted_status.id);
        } else {
            tweetStatement.setLong("retweetid", null);
        }
        if (tweet.in_reply_to_status_id != null) {
            tweetStatement.setLong("replytweetid", tweet.in_reply_to_status_id);
        } else {
            tweetStatement.setLong("replytweetid", null);
        }
        // TODO: place is not a string...
        if (tweet.place != null) {
            tweetStatement.setString("place", tweet.place.full_name + " " + tweet.place.country);
        } else {
            tweetStatement.setString("place", null);
        }

        User twuser = tweet.user;
        tweetStatement.setLong("userid", twuser.id);
        profileStatement.setLong("userid", twuser.id);
        profileStatement.setString("displayname", twuser.name);
        profileStatement.setString("timezone", twuser.time_zone);
        profileStatement.setInt("tweetcount", twuser.statuses_count);
        profileStatement.setInt("followercount", twuser.followers_count);
        profileStatement.setInt("followedcount", twuser.friends_count);
        String userLocation = getUserLocation(twuser);
        profileStatement.setString("location", userLocation);
        profileStatement.setString("tweetname", twuser.screen_name);
        profileStatement.setTimestamp("createdat", twuser.created_at);
        profileStatement.setString("language", twuser.lang);
        profileStatement.setString("description", twuser.description);
        profileStatement.setBoolean("verified", twuser.verified);
    }

    public static void setInsertBrandParams(NamedPreparedStatement brandStmt,
            long id, String brand) throws SQLException {
        brandStmt.setLong("tweetid", id);
        brandStmt.setString("brand", brand);
        // TODO: rating (positive)
    }

    private static String getUserLocation(User user) {
        String location = user.location;
        if (location != null && location.contains("\0")) {
            System.err.println("Warning: \\0 location found for user " + user);
            location = location.replace("\0", "");
        }
        return location;
    }
}