<commit_before>﻿using EDDiscovery.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace EDDiscovery2.DB
{
    public class VisitedSystemsClass : InMemory.VisitedSystemsClass
    {
        public ISystem curSystem;
        public ISystem prevSystem;
        public ISystem lastKnownSystem;
        public string strDistance;

        public VisitedSystemsClass()
        {
        }

        public VisitedSystemsClass(DataRow dr)
        {
            id = (int)(long)dr["id"];
            Name = (string)dr["Name"];
            Time = (DateTime)dr["Time"];
            Commander = (int)(long)dr["Commander"];
            Source = (int)(long)dr["Source"];
            Unit = (string)dr["Unit"];
            EDSM_sync = (bool)dr["edsm_sync"];
            MapColour = (int)(long)dr["Map_colour"];

            if (System.DBNull.Value == dr["X"])
            {
                X = 0;
                Y = 0;
                Z = 0;
            }
            else
            {
                X = (double)dr["X"];
                Y = (double)dr["Y"];
                Z = (double)dr["Z"];
            }
        }

        public bool Add()
        {
            using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
            {
                return Add(cn);
            }
        }

        private bool Add(SQLiteConnection cn)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 30;
                cmd.CommandText = "Insert into VisitedSystems (Name, Time, Unit, Commander, Source, edsm_sync, map_colour, X, Y, Z) values (@name, @time, @unit, @commander, @source, @edsm_sync, @map_colour, @x, @y, @z)";
                cmd.Parameters.AddWithValue("@name", Name);
                cmd.Parameters.AddWithValue("@time", Time);
                cmd.Parameters.AddWithValue("@unit", Unit);
                cmd.Parameters.AddWithValue("@commander", Commander);
                cmd.Parameters.AddWithValue("@source", Source);
                cmd.Parameters.AddWithValue("@edsm_sync", EDSM_sync);
                cmd.Parameters.AddWithValue("@map_colour", MapColour);
                cmd.Parameters.AddWithValue("@x", X);
                cmd.Parameters.AddWithValue("@y", Y);
                cmd.Parameters.AddWithValue("@z", Z);

                SQLiteDBClass.SqlNonQueryText(cn, cmd);

                using (SQLiteCommand cmd2 = new SQLiteCommand())
                {
                    cmd2.Connection = cn;
                    cmd2.CommandType = CommandType.Text;
                    cmd2.CommandTimeout = 30;
                    cmd2.CommandText = "Select Max(id) as id from VisitedSystems";

                    id = (int)(long)SQLiteDBClass.SqlScalar(cn, cmd2);
                }
                return true;
            }
        }

        public new bool Update()
        {
            using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
            {
                return Update(cn);
            }
        }

        private bool Update(SQLiteConnection cn)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 30;
                cmd.CommandText = "Update VisitedSystems set Name=@Name, Time=@Time, Unit=@Unit, Commander=@commander, Source=@Source, edsm_sync=@edsm_sync, map_colour=@map_colour, X=@x, Y=@y, Z=@z  where ID=@id";
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@Name", Name);
                cmd.Parameters.AddWithValue("@Time", Time);
                cmd.Parameters.AddWithValue("@unit", Unit);
                cmd.Parameters.AddWithValue("@commander", Commander);
                cmd.Parameters.AddWithValue("@source", Source);
                cmd.Parameters.AddWithValue("@edsm_sync", EDSM_sync);
                cmd.Parameters.AddWithValue("@map_colour", MapColour);
                cmd.Parameters.AddWithValue("@x", X);
                cmd.Parameters.AddWithValue("@y", Y);
                cmd.Parameters.AddWithValue("@z", Z);
                SQLiteDBClass.SqlNonQueryText(cn, cmd);

                return true;
            }
        }



        static public VisitedSystemsClass Parse(DateTime lasttime, string line)
        {
            VisitedSystemsClass sp = new VisitedSystemsClass();

            try
            {
                Regex pattern;
                int hour, min, sec;

                /* MKW: Use regular expressions to parse the log; much more readable and robust.
                 * Example log entry:
                
                From  ED  2.1 /1.6
                   {19:21:15} System:"Ooscs Fraae JR-L d8-112" StarPos:(-11609.469,639.594,20141.875)ly  NormalFlight
                string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";

                new from beta3?
                {18:15:14} System:"Pleiades Sector HR-W d1-41" StarPos:(-83.969,-146.156,-334.219)ly Body:0 RelPos:(-1.19887e+07,-9.95573e+06,2.55124e+06)km Supercruise
                string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly Body:(?<Body>\d+) StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";


                Pre ED 2.1/1.6
                    {09:36:16} System:0(Thuechea JE-O b11-0) Body:1 Pos:(-6.67432e+009,7.3151e+009,-1.19125e+010) Supercruise
                 
                 * Also, please note that due to E:D bugs, these entries can be at the end of a line as well, not just on a line of their own.
                 * The RegExp below actually just finds the pattern somewhere in the line, so it caters for rubbish at the end too.
                 */

                if (line.Contains("StarPos:")) // new  ED 2.1 format
                {

                    //{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:"(?<SystemName>[^"]+)" StarPos:\((?<Pos>.*?)\)ly( +(?<TravelMode>\w+))?
                    //{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:"(?<SystemName>[^"]+)" StarPos:\((?<Pos>.*?)\)ly( +(?<TravelMode>\w+))?
                    //string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";
                    string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly Body:(?<Body>\\d+) RelPos:\\((?<Pos>.*?)\\)km( +(?<TravelMode>\\w+))?";
                    pattern = new Regex(rgexpstr);

                    Match match = pattern.Match(line);

                    hour = int.Parse(match.Groups["Hour"].Value);
                    min = int.Parse(match.Groups["Minute"].Value);
                    sec = int.Parse(match.Groups["Second"].Value);

                    //sp.Nr = int.Parse(match.Groups["Body"].Value);
                    sp.Name = match.Groups["SystemName"].Value;
                    string pos = match.Groups["Pos"].Value;
                    try
                    {
                        string[] xyzpos = pos.Split(',');
                        var culture = new System.Globalization.CultureInfo("en-US");
                        sp.X = float.Parse(xyzpos[0], culture);
                        sp.Y = float.Parse(xyzpos[1], culture);
                        sp.Z = float.Parse(xyzpos[2], culture);
                    }
                    catch
                    {
                        sp.X = 0;
                        sp.Y = 0;
                        sp.Z = 0;
                    }
                }
                else
                {
                    pattern = new Regex(@"{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:\d+\((?<SystemName>.*?)\) Body:(?<Body>\d+) Pos:\(.*?\)( (?<TravelMode>\w+))?");
                    Match match = pattern.Match(line);

                    hour = int.Parse(match.Groups["Hour"].Value);
                    min = int.Parse(match.Groups["Minute"].Value);
                    sec = int.Parse(match.Groups["Second"].Value);

                    //sp.Nr = int.Parse(match.Groups["Body"].Value);
                    sp.Name = match.Groups["SystemName"].Value;
<commit_msg>Fixed regexp for parsing new ED l log format<commit_after>﻿using EDDiscovery.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace EDDiscovery2.DB
{
    public class VisitedSystemsClass : InMemory.VisitedSystemsClass
    {
        public ISystem curSystem;
        public ISystem prevSystem;
        public ISystem lastKnownSystem;
        public string strDistance;

        public VisitedSystemsClass()
        {
        }

        public VisitedSystemsClass(DataRow dr)
        {
            id = (int)(long)dr["id"];
            Name = (string)dr["Name"];
            Time = (DateTime)dr["Time"];
            Commander = (int)(long)dr["Commander"];
            Source = (int)(long)dr["Source"];
            Unit = (string)dr["Unit"];
            EDSM_sync = (bool)dr["edsm_sync"];
            MapColour = (int)(long)dr["Map_colour"];

            if (System.DBNull.Value == dr["X"])
            {
                X = 0;
                Y = 0;
                Z = 0;
            }
            else
            {
                X = (double)dr["X"];
                Y = (double)dr["Y"];
                Z = (double)dr["Z"];
            }
        }

        public bool Add()
        {
            using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
            {
                return Add(cn);
            }
        }

        private bool Add(SQLiteConnection cn)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 30;
                cmd.CommandText = "Insert into VisitedSystems (Name, Time, Unit, Commander, Source, edsm_sync, map_colour, X, Y, Z) values (@name, @time, @unit, @commander, @source, @edsm_sync, @map_colour, @x, @y, @z)";
                cmd.Parameters.AddWithValue("@name", Name);
                cmd.Parameters.AddWithValue("@time", Time);
                cmd.Parameters.AddWithValue("@unit", Unit);
                cmd.Parameters.AddWithValue("@commander", Commander);
                cmd.Parameters.AddWithValue("@source", Source);
                cmd.Parameters.AddWithValue("@edsm_sync", EDSM_sync);
                cmd.Parameters.AddWithValue("@map_colour", MapColour);
                cmd.Parameters.AddWithValue("@x", X);
                cmd.Parameters.AddWithValue("@y", Y);
                cmd.Parameters.AddWithValue("@z", Z);

                SQLiteDBClass.SqlNonQueryText(cn, cmd);

                using (SQLiteCommand cmd2 = new SQLiteCommand())
                {
                    cmd2.Connection = cn;
                    cmd2.CommandType = CommandType.Text;
                    cmd2.CommandTimeout = 30;
                    cmd2.CommandText = "Select Max(id) as id from VisitedSystems";

                    id = (int)(long)SQLiteDBClass.SqlScalar(cn, cmd2);
                }
                return true;
            }
        }

        public new bool Update()
        {
            using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
            {
                return Update(cn);
            }
        }

        private bool Update(SQLiteConnection cn)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 30;
                cmd.CommandText = "Update VisitedSystems set Name=@Name, Time=@Time, Unit=@Unit, Commander=@commander, Source=@Source, edsm_sync=@edsm_sync, map_colour=@map_colour, X=@x, Y=@y, Z=@z  where ID=@id";
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@Name", Name);
                cmd.Parameters.AddWithValue("@Time", Time);
                cmd.Parameters.AddWithValue("@unit", Unit);
                cmd.Parameters.AddWithValue("@commander", Commander);
                cmd.Parameters.AddWithValue("@source", Source);
                cmd.Parameters.AddWithValue("@edsm_sync", EDSM_sync);
                cmd.Parameters.AddWithValue("@map_colour", MapColour);
                cmd.Parameters.AddWithValue("@x", X);
                cmd.Parameters.AddWithValue("@y", Y);
                cmd.Parameters.AddWithValue("@z", Z);
                SQLiteDBClass.SqlNonQueryText(cn, cmd);

                return true;
            }
        }



        static public VisitedSystemsClass Parse(DateTime lasttime, string line)
        {
            VisitedSystemsClass sp = new VisitedSystemsClass();

            try
            {
                Regex pattern;
                int hour, min, sec;

                /* MKW: Use regular expressions to parse the log; much more readable and robust.
                 * Example log entry:
                
                From  ED  2.1 /1.6
                   {19:21:15} System:"Ooscs Fraae JR-L d8-112" StarPos:(-11609.469,639.594,20141.875)ly  NormalFlight
                string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";

                new from beta3?
                {18:15:14} System:"Pleiades Sector HR-W d1-41" StarPos:(-83.969,-146.156,-334.219)ly Body:0 RelPos:(-1.19887e+07,-9.95573e+06,2.55124e+06)km Supercruise
                string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly Body:(?<Body>\d+) StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";


                Pre ED 2.1/1.6
                    {09:36:16} System:0(Thuechea JE-O b11-0) Body:1 Pos:(-6.67432e+009,7.3151e+009,-1.19125e+010) Supercruise
                 
                 * Also, please note that due to E:D bugs, these entries can be at the end of a line as well, not just on a line of their own.
                 * The RegExp below actually just finds the pattern somewhere in the line, so it caters for rubbish at the end too.
                 */

                if (line.Contains("StarPos:")) // new  ED 2.1 format
                {

                    //{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:"(?<SystemName>[^"]+)" StarPos:\((?<Pos>.*?)\)ly( +(?<TravelMode>\w+))?
                    //{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:"(?<SystemName>[^"]+)" StarPos:\((?<Pos>.*?)\)ly( +(?<TravelMode>\w+))?
                    //string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly( +(?<TravelMode>\\w+))?";
                    string rgexpstr = "{(?<Hour>\\d+):(?<Minute>\\d+):(?<Second>\\d+)} System:\"(?<SystemName>[^\"]+)\" StarPos:\\((?<Pos>.*?)\\)ly Body:(?<Body>\\d+) RelPos:\\((?<RelPos>.*?)\\)km( +(?<TravelMode>\\w+))?";
                    pattern = new Regex(rgexpstr);

                    Match match = pattern.Match(line);

                    hour = int.Parse(match.Groups["Hour"].Value);
                    min = int.Parse(match.Groups["Minute"].Value);
                    sec = int.Parse(match.Groups["Second"].Value);

                    //sp.Nr = int.Parse(match.Groups["Body"].Value);
                    sp.Name = match.Groups["SystemName"].Value;
                    string pos = match.Groups["Pos"].Value;
                    try
                    {
                        string[] xyzpos = pos.Split(',');
                        var culture = new System.Globalization.CultureInfo("en-US");
                        sp.X = double.Parse(xyzpos[0], culture);
                        sp.Y = double.Parse(xyzpos[1], culture);
                        sp.Z = double.Parse(xyzpos[2], culture);
                    }
                    catch
                    {
                        sp.X = 0;
                        sp.Y = 0;
                        sp.Z = 0;
                    }
                }
                else
                {
                    pattern = new Regex(@"{(?<Hour>\d+):(?<Minute>\d+):(?<Second>\d+)} System:\d+\((?<SystemName>.*?)\) Body:(?<Body>\d+) Pos:\(.*?\)( (?<TravelMode>\w+))?");
                    Match match = pattern.Match(line);

                    hour = int.Parse(match.Groups["Hour"].Value);
                    min = int.Parse(match.Groups["Minute"].Value);
                    sec = int.Parse(match.Groups["Second"].Value);

                    //sp.Nr = int.Parse(match.Groups["Body"].Value);
                    sp.Name = match.Groups["SystemName"].Value;

{'fraction_non_alphanumeric': 0.1624183573203861, 'fraction_numerical': 0.017686838911045603, 'mean_word_length': 1.6322835843602617, 'pattern_counts': {'":': 0, '<': 95, '<?xml version=': 0, '>': 95, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5883755', 'n_tokens_mistral': 5582, 'n_tokens_neox': 5032, 'n_words': 920}