using System;
using System.Collections.Generic;
using System.IO;
using Godot;
using Microsoft.Data.Sqlite;
using SessionZero.SzfLib.File;
using SessionZero.SzfLib.Helpers;
using SessionZero.SzfLib.Objects;
using SessionZero.SzfLib.Parser;

namespace SessionZeroApp;

public static class LocalDatabaseHelper
{
    public const string DATA_PATH = "user://data";
    public const string LOCAL_DATABASE_FILE_PATH = "user://data/SessionZerolocal.db";

    public static string DataDir = ProjectSettings.GlobalizePath(DATA_PATH);
    public static string LocalDatabaseFilePath = ProjectSettings.GlobalizePath(LOCAL_DATABASE_FILE_PATH);

    public static List<SzfObject> SzfObjects { get; private set; } = new();

    public static void SetupLocalDatabase()
    {
        if (!Directory.Exists(DataDir))
        {
            GD.Print($"SZ WARNING: Local Data Directory does not exist, creating it at {DataDir}");
            Directory.CreateDirectory(DataDir);
        }

        if (!File.Exists(LocalDatabaseFilePath))
        {
            GD.Print($"SZ WARNING: Local Database file does not exist, creating a new one at {LocalDatabaseFilePath}");

            try
            {
                using var dbConnection = new SqliteConnection($"Data Source={LocalDatabaseFilePath}");
                dbConnection.Open();

                SetupLocalDbTables(dbConnection);

                GD.Print($"SZ INFO: Local Database file created at {LocalDatabaseFilePath}");
            }

            catch (SqliteException e)
            {
                GD.PrintErr($"SZ ERROR: Could not connect local database; {e}");
                return;
            }
        }

        GD.Print($"SZ INFO: Local Database verified at {LocalDatabaseFilePath}");
    }

    private static void SetupLocalDbTables(SqliteConnection dbConnection)
    {
        var sql = @"CREATE TABLE szf_data(
                        name TEXT NOT NULL,
                        id TEXT NOT NULL,
                        version FLOAT NOT NULL,
                        type TEXT NOT NULL,
                        path TEXT NOT NULL,
                        PRIMARY KEY (name, id, version)
                    );";

        try 
        {
            using var command = new SqliteCommand(sql, dbConnection);
            command.ExecuteNonQuery();

            GD.Print($"SZ INFO: Local database tables initialized");
        }

        catch (SqliteException e)
        {
            GD.PrintErr($"SZ ERROR: Could not set up local database tables; {e}");
            return;
        }
    }

    public static ISzfObject? GetSzfObject(string name, string guid, float version)
    {
        var sql = @"SELECT path FROM szf_data
        WHERE name = @name AND guid = @guid AND version = @version;";

        string selectedPath = "";

        try
        {
            using var connection = new SqliteConnection($"Data Source={LocalDatabaseFilePath}");
            connection.Open();

            using var command = new SqliteCommand(sql, connection);
            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@guid", guid);
            command.Parameters.AddWithValue("@version", version);

            using var reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while(reader.Read())
                {
                    selectedPath = reader.GetString(0);
                }
            }
        }
        catch (SqliteException e)
        {
            GD.PrintErr(e);
            return null;
        }


        try 
        {
            var content = File.ReadAllText(selectedPath);

            SzfParser parser = new();
            var szfObject = parser.Parse(content);

            return szfObject;
        }
        catch (Exception e) 
        {
            GD.PrintErr(e);
            return null;
        }
    }

    public static List<SzfDataset> GetAllDatasets()
    {
        var sql = @"SELECT path FROM szf_data
        WHERE type = @type";

        List<SzfDataset> datasets = new();

        try
        {
            using var connection = new SqliteConnection($"Data Source={LocalDatabaseFilePath}");
            connection.Open();

            using var command = new SqliteCommand(sql, connection);
            command.Parameters.AddWithValue("@type", "dataset");

            using var reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while(reader.Read())
                {
                    var path = reader.GetString(0);

                    try 
                    {
                        var content = File.ReadAllText(path);

                        SzfParser parser = new();
                        var szfObject = parser.Parse(content) as SzfDataset;

                        datasets.Add(szfObject);
                    }
                    catch (Exception e) 
                    {
                        GD.PrintErr(e);
                    }
                }
            }
        }
        catch (SqliteException e)
        {
            GD.PrintErr(e);
        }

        GD.Print($"Retrieving {datasets.Count} datasets");

        return datasets;
    }
}