using Microsoft.Data.Sqlite;

namespace SimPas2_Windows.Managers
{
  public class NoteManager
  {
    private readonly string mConnectionString;
    private readonly string mCulture;

    private string mJpLang;

    public NoteManager(string databasePath, string culture)
    {
      mConnectionString = $"Data Source={databasePath}";
      mCulture = culture;

      mJpLang = "ja-JP";
    }

    private bool AlreadyExists(string name, int? excludeId = null)
    {
      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        com.CommandText = @"
          SELECT COUNT(*) FROM Notes
          WHERE UPPER(Name) = UPPER(@name)";
        com.Parameters.AddWithValue("@name", name);

        if (excludeId.HasValue)
        {
          com.CommandText += " AND Id != @excludeId";
          com.Parameters.AddWithValue("@excludeId", excludeId.Value);
        }

        return Convert.ToInt32(com.ExecuteScalar()) > 0;
      }
    }

    public void AddNote(string name)
    {
      if (string.IsNullOrWhiteSpace(name))
      {
        string err = mCulture == mJpLang
          ? "ファイル名を御入力下さい。"
          : "Please fill in the filename.";
        throw new ArgumentException(err);
      }

      if (AlreadyExists(name))
      {
        string err = mCulture == mJpLang
          ? $"ファイル名「{name}」付きメモは既に存在します。"
          : $"A note with the filename '{name}' already exists.";
        throw new ArgumentException(err);
      }

      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        com.CommandText = @"
          INSERT INTO Notes (Name)
          VALUES ($name)";
        com.Parameters.AddWithValue("$name", name);
        com.ExecuteNonQuery();
      }
    }

    public bool EditNote(int id, string name)
    {
      if (string.IsNullOrWhiteSpace(name))
      {
        string err = mCulture == mJpLang
          ? "ファイル名を御入力下さい。" :
          "Please fill in the filename.";
        throw new ArgumentException(err);
      }

      if (AlreadyExists(name, id))
      {
        string err = mCulture == mJpLang
          ? $"ファイル名「{name}」付きメモは既に存在します。"
          : $"A note with the filename '{name}' already exists.";
        throw new ArgumentException(err);
      }

      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        com.CommandText = @"
          UPDATE Notes
          SET Name = $name
          WHERE Id = $id";
        com.Parameters.AddWithValue("id", id);
        com.Parameters.AddWithValue("$name", name);

        return com.ExecuteNonQuery() > 0;
      }
    }

    public bool SaveNote(int id, string text)
    {
      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        com.CommandText = @"
          UPDATE Notes
          SET Text = @text
          WHERE Id = @id";
        com.Parameters.AddWithValue("@id", id);
        com.Parameters.AddWithValue("@text", text ?? (object)DBNull.Value);

        return com.ExecuteNonQuery() > 0;
      }
    }

    public bool DeleteNote(int id)
    {
      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        com.CommandText = "DELETE FROM Notes WHERE Id = $id";
        com.Parameters.AddWithValue("$id", id);

        return com.ExecuteNonQuery() > 0;
      }
    }

    public List<(int Id, string Name, string Text)> GetAll(string keyword = "")
    {
      var notes = new List<(int, string, string)>();
      using (SqliteConnection conn = new SqliteConnection(mConnectionString))
      {
        conn.Open();
        SqliteCommand com = conn.CreateCommand();
        if (string.IsNullOrWhiteSpace(keyword))
        {
          com.CommandText = @"
          SELECT Id, Name, Text FROM Notes
          ORDER BY Name ASC";
        }
        else
        {
          com.CommandText = @"
          SELECT Id, Name, Text FROM Notes
          WHERE Name LIKE @keyword
          ORDER BY Name ASC";
          com.Parameters.AddWithValue("@keyword", $"%{keyword}%");
        }

        using (SqliteDataReader reader = com.ExecuteReader())
        {
          while (reader.Read())
          {
            notes.Add((
              reader.GetInt32(0),
              reader.GetString(1),
              reader.IsDBNull(2) ? string.Empty : reader.GetString(2)
            ));
          }
        }
      }

      return notes;
    }
  }
}