Newsbot.Collector/Newsbot.Collector.Database/Repositories/ArticlesTable.cs

using System.Data;
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Newsbot.Collector.Domain.Entities;
using Newsbot.Collector.Domain.Interfaces;
using Newsbot.Collector.Domain.Models;
using Npgsql;

namespace Newsbot.Collector.Database.Repositories;

public class ArticlesTable : IArticlesRepository
{
    //private readonly string _connectionString;

    private DatabaseContext _context;  

    public ArticlesTable(string connectionString)
    {
        //_connectionString = connectionString;
        _context = new DatabaseContext(connectionString);
    }

    public ArticlesTable(DatabaseContext context)
    {
        _context = context;
    }

    //public ArticlesTable(IConfiguration configuration)
    //{
    //    var conn = configuration.GetConnectionString("database");
    //    if (conn is null) conn = "";
    //    
    //    _context = new DatabaseContext(conn);
    //}

    public List<ArticlesEntity> List(int page = 0, int count = 25)
    {
        //using var context = new DatabaseContext(_connectionString);
        
        var query = _context.Articles
            .Skip(page * count)
            .OrderBy(d => d.PubDate)
            .Take(25);
        Console.WriteLine(query.ToQueryString());
        return query.ToList();
    }

    public ArticlesEntity GetById(Guid id)
    {
        //using var context = new DatabaseContext(_connectionString);
        var query = _context.Articles
            .FirstOrDefault(d => d.Id.Equals(id));
        query ??= new ArticlesEntity();
        return query;
    }

    public ArticlesEntity GetByUrl(string url)
    {
        //using var context = new DatabaseContext(_connectionString);
        var res = _context.Articles.FirstOrDefault(d => d.Url!.Equals(url));
        res ??= new ArticlesEntity();
        return res;
    }

    public List<ArticlesEntity> ListBySourceId(Guid id, int page, int count)
    {
        //using var context = new DatabaseContext(_connectionString);
        var res = _context.Articles
            .Skip(page * count)
            .Where(d => d.SourceId.Equals(id));
        return res.ToList();
    }

    public ArticlesEntity New(ArticlesEntity model)
    {
        //using var context = new DatabaseContext(_connectionString);
        model.Id = new Guid();
        var query = _context.Articles.Add(model);
        _context.SaveChanges();
        return model;
    }
    
    /*
    public ArticlesModel NewDapper(ArticlesModel model)
    {
        model.ID = Guid.NewGuid();

        using var conn = OpenConnection(_connectionString);
        var q =
            "INSERT INTO Articles (id, sourceid, tags, title, url, pubdate, video, videoheight, videowidth, thumbnail, description, authorname, authorimage, codeiscommit, codeisrelease) Values (@id, @sourceid, @tags, @title, @url, @pubdate, @video, @videoheight, @videowidth, @thumbnail, @description, @authorname, @authorimage, @codeiscommit, @codeisrelease);";
        var res = conn.Execute(q, new
        {
            id = model.ID,
            sourceid = model.SourceID,
            tags = model.Tags,
            title = model.Title,
            url = model.URL,
            pubdate = model.PubDate,
            video = model.Video,
            videoheight = model.VideoHeight,
            videowidth = model.VideoWidth,
            thumbnail = model.Thumbnail,
            description = model.Description,
            authorname = model.AuthorName,
            authorimage = model.AuthorImage,
            codeiscommit = model.CodeIsCommit,
            codeisrelease = model.CodeIsRelease
        });
        return model;
    }
    */

    public void DeleteAllBySourceId(Guid sourceId)
    {
        //using var context = new DatabaseContext(_connectionString);
        var res = _context.Articles
            .Where(d => d.SourceId.Equals(sourceId))
            .ToList();

        foreach (var item in res)
        {
            _context.Articles.Remove(item);
        }

        _context.SaveChanges();
    }

    //private IDbConnection OpenConnection(string connectionString)
    //{
    //    //var conn = new NpgsqlConnection(_connectionString);
    //    //conn.Open();
    //    //return conn;
    //}
}