using SYBDDatabaseImplement.Models;
using Microsoft.EntityFrameworkCore;
using Npgsql;

namespace SYBDDatabaseImplement
{
    public class SYBDDatabase : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (optionsBuilder.IsConfigured == false)
            {
                optionsBuilder.UseNpgsql(@"Host=192.168.50.49;Database=postgres;Username=postgres;");
            }
            base.OnConfiguring(optionsBuilder);
        }
		private NpgsqlConnection GetConnection()
		{
			return new NpgsqlConnection("Host=192.168.50.49;Database=postgres;Username=postgres;");
		}

		public virtual DbSet<Auto> auto { set; get; }

        public virtual DbSet<Client> client { set; get; }

        public virtual DbSet<Insurance> insurance{ set; get; }

        public virtual DbSet<Order> order { set; get; }
        public virtual DbSet<Worker> worker { set; get; }

		public List<Sequence> GetSequences()
		{
			List<Sequence> sequences = new List<Sequence>();

			{
				using var conn = GetConnection();
				conn.Open();
				using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
					"FROM pg_sequences " +
					"WHERE sequencename = 'worker_id_seq'", conn);
				using var reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					sequences.Add(new Sequence
					{
						Id = "worker",
						Count = reader.GetInt32(1)
					});
				}
			}
			{
				using var conn = GetConnection();
				conn.Open();
				using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
					"FROM pg_sequences " +
					"WHERE sequencename = 'auto_id_seq'", conn);
				using var reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					sequences.Add(new Sequence
					{
						Id = "auto",
						Count = reader.GetInt32(1)
					});
				}
			}
			{
				using var conn = GetConnection();
				conn.Open();
				using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
					"FROM pg_sequences " +
					"WHERE sequencename = 'client_id_seq'", conn);
				using var reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					sequences.Add(new Sequence
					{
						Id = "client",
						Count = reader.GetInt32(1)
					});
				}
			}
			{
				using var conn = GetConnection();
				conn.Open();
				using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
					"FROM pg_sequences " +
					"WHERE sequencename = 'insurance_id_seq'", conn);
				using var reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					sequences.Add(new Sequence
					{
						Id = "insurance",
						Count = reader.GetInt32(1)
					});
				}
			}
			{
				using var conn = GetConnection();
				conn.Open();
				using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
					"FROM pg_sequences " +
					"WHERE sequencename = 'order_id_seq'", conn);
				using var reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					sequences.Add(new Sequence
					{
						Id = "order",
						Count = reader.GetInt32(1)
					});
				}
			}

			return sequences;
		}
	}
}