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

namespace Sensor.Gateway.DataXfer
{
    class Constants
    {
        #region File Constants
        // File Constants
        public const string STORES_STRING = "store";
        public const string GLOBALS_STRING = "globals";
        public const string NETWORK_STRING = "neighborhood";
        public const string WEATHER_STRING = "weather";
        public const int STORES_FILE = 1;
        public const int GLOBALS_FILE = 2;
        public const int NETWORK_FILE = 3;
        public const int WEATHER_FILE = 4;
        #endregion File Constants

        #region Koala Constants
        // Koala Constants
        public const int KOALA_PACKET_START = 4;
        public const int KOALA_RECORD_LEN = 26;     // 4 for clock, 20 for data, 2 for CRC (cubhill version)
        public const int KOALA_PAYLOAD_LEN = 20;    // 20 bytes of data
        #endregion Koala Constants

        #region GPS Constants
        public static DateTime GPS_0 = new DateTime(1980, 1, 6, 0, 0, 0);
        public const double GPS_WEEK_S = 7 * 24 * 3600;
        public static DateTime UTC_0 = new DateTime(1970, 1, 1, 0, 0, 0);
        #endregion
        /**
         * SQL Strings here ..
         **/
        #region SQL Strings
        public const Int16 GPS_NEIGHBOR_ID = -1;
        // Insert new record in the FlashRecord table
        public const string SQL_FLASH_INSERT =
            "INSERT INTO FlashRecord "
            + "(downloadid, boxID, downloadts, leftPtr, rightPtr, localclock, rebootCounter, raw, CRC, processFlag)"
            + "VALUES(@downloadid, @boxid, @downloadts, @leftPtr, @rightPtr, @localclock, @rebootCounter, @rawdump, @CRC, @processFlag)";

        public const string SQL_ANCHOR_INSERT = 
            @"Insert INTO BrazilAnchors
                (box1, lc1, rc1, box2, lc2, rc2, downloadId, downloadTs, processFlag)
                VALUES (@box1, @lc1, @rc1, @box2, @lc2, @rc2, @downloadId, @downloadTs, @processFlag)";
        // Insert Weather Record
        public const string SQL_WEATHER_INSERT =
          "INSERT INTO WeatherRecord VALUES(@downloadid, @unixts, @abbrv, @reading, @wuFlag, @luyfFlag)";

        // Insert new record in the RawDownload Table
        public const string SQL_DOWNLOAD_INSERT = "INSERT INTO RawDownload VALUES (@processType, @downloadFile, @downloadTime, @numBytes, @processFlag)";

        // Get the downloadid for the last download
        public const string SQL_GET_DOWNLOADID = "SELECT max(downloadid) FROM RawDownload WHERE downloadFile = ";

        // Get the last downloadTS for all nodes
        public const string SQL_GET_LASTDOWNLOAD = "SELECT boxid, max(leftPtr) as leftPtr FROM FlashRecord GROUP BY boxid";

        // Get the last downloadTS for Weather Station
        public const string SQL_GET_LAST_WEATHER_DOWNLOAD = "SELECT max(unixts) as ts FROM WeatherRecord";
        
        // Get the last journal entry
        public const string SQL_GET_LASTJOURNAL = "SELECT max(journal.unixts) FROM Journal join rawdownload on journal.downloadid=rawdownload.downloadid WHERE rawdownload.downloadfile not like '%store%'";

        // Get the last link entry
        public const string SQL_GET_LASTLINK = "select max(unixts) FROM DownloadPath";

        // Get the journal id sql stub
        public const string SQL_JOURNAL_ID_STUB = "SELECT LogID FROM LogDesc WHERE Name = ";

        // Insert new record in the journal record
        public const string SQL_JOURNAL_INSERT = "INSERT INTO Journal VALUES (@downloadid, @boxid, @unixts, @logid, @value)";

        // Insert new record in the DownloadPath table
        public const string SQL_DWLDPATH_INSERT = "INSERT INTO DownloadPath VALUES (@downloadid, @unixts, @id, @src, @dest, @path)";

        // Insert new record in the Neighbor table
        public const string SQL_NEIGHBOR_INSERT = "INSERT INTO NeighborTable VALUES (@download, @unixts, @id, @node1, @node2, @RSSI, @LQI)";

        // Get last flash record data for node
        public const string SQL_GET_LAST_FLASHRECORD = "SELECT downloadId, downloadts, boxID, leftPtr, rightPtr, localclock, raw, rebootCounter, CRC from FlashRecord where boxID=@boxId and leftptr=(select max(leftptr) from flashrecord where boxID=@boxId)";

        // Get last Anchor record for node
        public const string SQL_GET_LAST_ANCHOR = "select max(lc1) as lc1, rc1 from brazilanchors where box1=@box1 and rc1 = (select max(rc1) from brazilAnchors where box1=@box1 group by box1) group by rc1";

        public const string SQL_GET_LAST_JOURNAL_FOR_BOX = "select count(*) as count from Journal where boxId=@boxId and unixts=@unixts";

        public const string SQL_COUNT_MATCHING_ANCHORS = "SELECT count(*) from brazilanchors where box1=@box1 and rc1=@rc1 and lc1=@lc1 and box2=@box2 and rc2=@rc2 and lc2=@lc2";
        #endregion SQL Strings
    }
}