using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Odbc;
using System.Collections;
using OpenMetaverse;

namespace CS422API
{
    class API
    {
        /// <summary>
        /// The timestamp format used in the DB (e.g. "2010-02-22 19:47:52").  
        /// Use String.Format(DB_DATE_FORMAT, [DateTime Object])
        /// to convert a DateTime into a timestamp string suitable for the DB.
        /// </summary>
        public const string DB_DATE_FORMAT = "{0:yyyy-MM-dd HH:mm:ss}";

        /// <summary>
        /// Ordering of locations for getPlaces()
        /// </summary>
        public enum Ordering { Latest, Oldest };

        /// <summary>
        /// Returns an OdbcConnection object to the second life DB.  
        /// Make sure to close this object after you're done using it.
        /// </summary>
        public static OdbcConnection getConnection()
        {
            OdbcConnection conn = new OdbcConnection("DSN=secondlife_ro");
            return conn;
        }

        /// <summary>
        /// Executes a SQL query on on the given DB connection
        /// </summary>
        /// 
        /// <param name="conn">Connection to DB</param>
        /// <param name="query">Query to execute</param>
        /// <returns>OdbcDataReader of query results</returns>
        public static OdbcDataReader executeQuery(OdbcConnection conn, string query)
        {
            conn.Open();
            OdbcCommand cmd = new OdbcCommand(query, conn);
            OdbcDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        /// <summary>
        /// Finds all chat events involving the given avatar between the specified times.
        /// Avatars may be the speakers or listeners.
        /// </summary>
        /// 
        /// <param name="avatars">Avatars that were chatting</param>
        /// <param name="start">Start time</param>
        /// <param name="end">End time</param>
        /// <returns></returns>
        public static ChatEvent[] getChats(UUID avatar, DateTime start, DateTime end)
        {
            string startTimeString = String.Format(DB_DATE_FORMAT, start);
            string endTimeString = String.Format(DB_DATE_FORMAT, end);

            string query = "SELECT * from chat " +
                "WHERE (a_id = '" + avatar + "' OR c_speaker = '" + avatar +
                "') AND c_timestamp >= '" + startTimeString +
                "' AND c_timestamp <= '" + endTimeString + "';";
            //Console.WriteLine(query);
            List<ChatEvent> chats = new List<ChatEvent>();

            OdbcConnection conn = getConnection();
            OdbcDataReader dr = executeQuery(conn, query);
            while (dr.Read())
            {
                ChatEvent ce = new ChatEvent();
                ce.listener = new UUID(dr.GetString(1));
                ce.timestamp = DateTime.Parse(dr.GetString(3));
                float x = dr.GetFloat(4);
                float y = dr.GetFloat(5);
                float z = dr.GetFloat(6);
                ce.location = new Vector3(x, y, z);
                ce.speaker = new UUID(dr.GetString(7));
                ce.message = dr.GetString(8);
                chats.Add(ce);
            }
            dr.Close();
            conn.Close();

            return chats.ToArray();
        }

        /// <summary>
        /// Returns an array of locations where the given avatar was in the time range
        /// </summary>
        /// 
        /// <param name="avatar">Avatar to check location for</param>
        /// <param name="start">Start time</param>
        /// <param name="end">End time</param>
        /// <param name="order">Order of locations</param>
        /// <returns></returns>
        public static Vector3[] getPlaces(UUID avatar, DateTime start, DateTime end, Ordering order)
        {            
            string startTimeString = String.Format(DB_DATE_FORMAT, start);
            string endTimeString = String.Format(DB_DATE_FORMAT, end);
            string sqlOrdering = "";
            switch (order)
            {
                case Ordering.Latest:
                    sqlOrdering = "m_timestamp DESC";
                    break;
                case Ordering.Oldest:
                    sqlOrdering = "m_timestamp ASC";
                    break;
                default:
                    sqlOrdering = "m_timestamp DESC";
                    break;
            }
            string query = "SELECT DISTINCT m_pos_x, m_pos_y, m_pos_z " +
                "FROM avatars, moments " +
                "WHERE moments.a_id = '" + avatar.ToString() +
                "' AND m_timestamp >= '" + startTimeString +
                "' AND m_timestamp <= '" + endTimeString +
                "' ORDER BY " + sqlOrdering;
            //Console.WriteLine(query);

            List<Vector3> places = new List<Vector3>();
            OdbcConnection conn = getConnection();
            OdbcDataReader dr = executeQuery(conn, query);
            while (dr.Read())
            {
                Vector3 loc = new Vector3(dr.GetFloat(0), dr.GetFloat(1), dr.GetFloat(2));
                places.Add(loc);
            }
            dr.Close();
            conn.Close();

            return places.ToArray();
        }

        /// <summary>
        /// Given a location and start/end times, returns all avatars within 20m of the location
        /// in the given time.
        /// </summary>
        /// 
        /// <param name="location">Location to check</param>
        /// <param name="start">Start time</param>
        /// <param name="end">End time</param>
        /// <returns></returns>
        public static UUID[] getAvatars(Vector3 location, DateTime start, DateTime end)
        {
            string startTimeString = String.Format(DB_DATE_FORMAT, start);
            string endTimeString = String.Format(DB_DATE_FORMAT, end);
            string query = "SELECT DISTINCT avatars.a_id, avatars.a_name " +
                "FROM avatars, moments " +
                "WHERE moments.a_id = avatars.a_id " +
                "AND m_timestamp >= '" + startTimeString +
                "' AND m_timestamp <= '" + endTimeString +
                "' AND (m_pos_x - " + location.X + ") < 20 " +
                " AND (m_pos_y - " + location.Y + ") < 20 " +
                " AND (m_pos_z - " + location.Z + ") < 20 ;";
            //Console.WriteLine(query);

            OdbcConnection conn = getConnection();
            OdbcDataReader dr = executeQuery(conn, query);

            List<UUID> avatars = new List<UUID>();
            while (dr.Read())
            {
                avatars.Add(new UUID(dr.GetString(0)));
            }
            dr.Close();
            conn.Close();

            return avatars.ToArray();
        }

        /// <summary>
        /// Look for meetings that involved all given avatars within the specified time period.
        /// NOTE: avatars[0] must be an intelligent avatar.
        /// </summary>
        /// <param name="avatars">Avatars in the meeting</param>
        /// <param name="start">Start time</param>
        /// <param name="end">End time</param>
        /// <returns></returns>
        public static Meeting[] getMeetings(UUID[] avatars, DateTime start, DateTime end)
        {
            // Retrieve all meetings with the host = avatars[0]
            UUID host = avatars[0];
            string startStr = String.Format(DB_DATE_FORMAT, start);
            string endStr = String.Format(DB_DATE_FORMAT, end);
            string query = "SELECT * from meetings, moments " +
                "WHERE meetings.m_id = moments.m_id " +
                "AND moments.a_id = '" + host + "' " +
                "AND moments.m_timestamp >= '" + startStr + "' " +
                "AND moments.m_timestamp <= '" + endStr + "' " +
                "ORDER BY moments.m_timestamp asc;";
            //Console.WriteLine(query);

            OdbcConnection conn = getConnection();
            OdbcDataReader dr = executeQuery(conn, query);
            Dictionary<int, MeetingMoment> meetingMoments = new Dictionary<int, MeetingMoment>();
            if (dr.HasRows)
            {
                // First, read in all the MeetingMoments
                while (dr.Read())
                {
                    int momentId = Convert.ToInt32(dr["m_id"]);
                    string listenerUUID = dr.GetString(2);
                    string hostUUID = dr.GetString(4);                    
                    DateTime time = DateTime.Parse(dr.GetString(6));
                    Vector3 location = new Vector3(dr.GetFloat(7), dr.GetFloat(8), dr.GetFloat(9));
                    MeetingMoment mm = new MeetingMoment(momentId, time, location);
                    mm.addAvatar(new UUID(hostUUID));
                    mm.addAvatar(new UUID(listenerUUID));

                    if (meetingMoments.ContainsKey(momentId))
                    {
                        meetingMoments[momentId].addAvatar(new UUID(listenerUUID));
                    }
                    else
                    {
                        meetingMoments.Add(momentId, mm);
                    }
                }
            } else
            {
                return new Meeting[0];
            }
            conn.Close();
            dr.Close();
            
            MeetingMoment[] mmArray = meetingMoments.Values.ToArray();
            Array.Sort(mmArray);

            // Now we actually try to find the continuous meeting objects
            List<Meeting> meetings = new List<Meeting>();
            MeetingMoment startMM = null;  // The question mark denotes a nullable type, otherwise structs cannot be null
            DateTime? last = null;
            for (int i=0; i<mmArray.Length; i++)
            {
                MeetingMoment mm = mmArray[i];
                if (startMM == null)
                {
                    // Check if this MeetingMoment is the start of a new meeting
                    bool containsAllAvatars = true;
                    foreach (UUID avatar in avatars)
                    {
                        if (!mm.avatars.Contains(avatar))
                        {
                            containsAllAvatars = false;
                            break;
                        }
                    }
                    if (containsAllAvatars)
                    {
                        startMM = mm;
                        last = mm.time;
                    }
                }
                else
                {
                    // Look for the end of the meeting.  This can occur if an avatar leaves or the next MeetingMoment is not continuous.
                    bool containsAllAvatars = true;
                    foreach (UUID avatar in avatars)
                    {

                        if (!mm.avatars.Contains(avatar))
                        {
                            containsAllAvatars = false;
                            break;
                        }
                    }

                    TimeSpan span = mm.time - (DateTime)last;
                    if (containsAllAvatars && span.TotalSeconds < 15)
                    {
                        // The meeting continues
                        last = mm.time;
                    }
                    else
                    {
                        // The meeting has come to an end
                        Meeting newMeeting = new Meeting(avatars, startMM.time, (DateTime)last, startMM.location);
                        meetings.Add(newMeeting);
                        startMM = null;
                        last = null;
                    }

                    // Also need to return the last meeting (we will not see another MeetingMoment)
                    if (i == mmArray.Length - 1)
                    {
                        Meeting newMeeting = new Meeting(avatars, startMM.time, (DateTime)last, startMM.location);
                        meetings.Add(newMeeting);
                    }
                }
            }

            return meetings.ToArray();
        }

        /// <summary>
        /// Class representing a single meeting moment in time.  A series of continuous
        /// meeting moments will be merged into a Meeting struct.
        /// </summary>
        private class MeetingMoment : IComparable<MeetingMoment>
        {
            public int id;
            public HashSet<UUID> avatars = new HashSet<UUID>();
            public DateTime time;
            public Vector3 location;

            public MeetingMoment(int id, DateTime time, Vector3 location)
            {
                this.id = id;
                this.time = time;
                this.location = location;
            }

            public void addAvatar(UUID a)
            {
                avatars.Add(a);
            }

            public int CompareTo(MeetingMoment other)
            {
                return this.time.CompareTo(other.time);
            }
        }
    }
}

