using System;
using System.ComponentModel;
using System.Diagnostics;
using System.Data.SQLite;
using System.Net;
using System.Runtime.InteropServices;
using static System.Net.Mime.MediaTypeNames;

namespace Simpsons_generator;

class Program
{

    private static readonly string FolderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Simpsons_generator");
    private static readonly string OverrideBrowser = Path.Combine(FolderPath, "overrideBrowser.xml");
    public static readonly string filePath = Path.Combine(FolderPath, "episodes-watched.csv");
    

    static SQLiteConnection CreateConnection()
    {

        SQLiteConnection sqliteconn;

        if (!(File.Exists($"{FolderPath}/database.db")))
        {
            Console.WriteLine("Creating database.db");
            using (var client = new WebClient())
            {
                client.DownloadFile("https://gitea.tryton.geeraert.eu//pi-ux-ce/Simpsons_generator/raw/branch/main/database.db", $"{FolderPath}/database.db");
            }
            
        }
        
        // Create a new database connection:
        var sqllitedatasource = $"Data Source={FolderPath}/database.db;Version=3;New=True;Compress=True;";
        sqliteconn = new SQLiteConnection(sqllitedatasource);
        // Open the connection:
        try
        {
            sqliteconn.Open();
        }
        catch (Exception ex)
        {

        }
        return sqliteconn;
    }

    private static void Openit(string url)
    {
        try
        {
            Process.Start(url);
        }
        catch
        {
            // hack because of this: https://github.com/dotnet/corefx/issues/10361
            if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
            {
                Console.WriteLine("Using Windows");
                url = url.Replace("&", "^&");
                if (File.Exists(OverrideBrowser))
                {
                    string browserPath = File.ReadAllText(OverrideBrowser);
                    Console.WriteLine("browser:   "+browserPath);
                    Process.Start(new ProcessStartInfo(browserPath, url) { CreateNoWindow = true });
                }
                else {
                    Process.Start(new ProcessStartInfo(url) { UseShellExecute = true });
                }
                    
            }
            else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
            {
                Console.WriteLine("Using Linux");
                try
                {
                    Process.Start("xdg-open", url);

                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    throw;
                }
            }
            else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
            {
                Console.WriteLine("Using Mac");
                Process.Start("open", url);
            }
            else
            {
                throw;
            }
        }

        //Process.Start(new ProcessStartInfo("C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe", url_generated) { CreateNoWindow = true });



    }

    static object RunSqlQuery(SQLiteConnection conn, string query)
    {
        SQLiteDataReader sqliteDatareader;
        SQLiteCommand sqliteCmd;
        sqliteCmd = conn.CreateCommand();
        sqliteCmd.CommandText = query;
        sqliteDatareader = sqliteCmd.ExecuteReader();
        if (sqliteDatareader.Read())
        {
            return sqliteDatareader["result"];
        }
        else
        {
            return false;
        }
            
    }

    
    private static void SaveIntoDatabase(SQLiteConnection conn,int season, int episode)
    {
        SQLiteCommand sqliteCmd;
        sqliteCmd = conn.CreateCommand();
        string updateQuery = $"update episodes set seen=1 WHERE season = {season} and episode = {episode}";
        sqliteCmd.CommandText = updateQuery;
        sqliteCmd.ExecuteNonQuery();
    }


    static void Main(string[] args)
    {

        string urlGenerated;
        int randomSeason;
        int randomEpisode; 

        SQLiteConnection sqliteconn;
        sqliteconn = CreateConnection();
        
        //check if the old excel exist and convert it to SQLLITE
        if (File.Exists(filePath))
        {
            Console.WriteLine("Excel file present");
            var linesRead = File.ReadLines(filePath);
            
            foreach (var lineRead in linesRead)
            { 
                Console.WriteLine(lineRead);
                string[] words = lineRead.Split('-');
                int season = Convert.ToInt16(words[3]);
                int episode = Convert.ToInt16(words[5]);
                SaveIntoDatabase(sqliteconn,season,episode);
                Console.WriteLine(season);
                Console.WriteLine(episode);
                
            }
            System.IO.File.Move(filePath,filePath+"_converted.csv");
        }
        
        bool restart = false;
        do
        {
            restart = false;
            
            //get maximum number of seasons
            string maximumNumberOfSeasonsQuery = "SELECT COUNT(DISTINCT season) as result FROM episodes;";
            int maximumNumberSeasons = Convert.ToInt32(RunSqlQuery(sqliteconn, maximumNumberOfSeasonsQuery));

            
            //random for the season
            Random rnd = new Random();
            randomSeason  = rnd.Next(1, maximumNumberSeasons);

            Console.WriteLine("random season: "+randomSeason);

            //get maximum number of episodes for this season
            string maximumNumberOfEpisodesQuery = $"SELECT COUNT() as result FROM episodes WHERE season = {randomSeason};";
            int maximumNumberOfEpisodes = Convert.ToInt32(RunSqlQuery(sqliteconn, maximumNumberOfEpisodesQuery));
            
            randomEpisode = rnd.Next(1, maximumNumberOfEpisodes);
            Console.WriteLine("random episode: "+randomEpisode);
            
            //url generated
            string urlGeneratedQuery = $"SELECT url as result FROM episodes WHERE season = {randomSeason} and episode = {randomEpisode};";
            urlGenerated = Convert.ToString(RunSqlQuery(sqliteconn, urlGeneratedQuery));
            
            
            //CheckEpisodeAlreadyRun
            string checkEpisodeAlreadyRunQuery = $"SELECT seen as result FROM episodes WHERE season = {randomSeason} and episode = {randomEpisode};";
            bool checkEpisodeAlreadyRun = Convert.ToBoolean(RunSqlQuery(sqliteconn, checkEpisodeAlreadyRunQuery));

            if (checkEpisodeAlreadyRun)
            {
                //return;
                restart = true;
                Console.WriteLine("already watched");
                
            }



        } while (restart);
        Console.WriteLine("urlGenerated "+urlGenerated);
        Openit(urlGenerated);
        
        SaveIntoDatabase(sqliteconn,randomSeason,randomEpisode);

    }
}