using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DatabazeHudby.Databaze
{
    class AlbumTable
    {
        public static String SQL_SELECT = "select nazev,datum_vydani from Album";
        public static String SQL_SELECT_ID = "select a.ID_alba,a.nazev,a.datum_vydani,a.pocet_pisnicek from Album a where ID_alba=@id";
        public static String SQL_INSERT = "INSERT INTO Album(nazev,datum_vydani) VALUES (@nazev,@datumVydani)";
        public static String SQL_DELETE_ID = "DELETE FROM \"Album\" WHERE ID_alba=@id";
        public static String SQL_UPDATE = "UPDATE \"Album\" SET nazev=@nazev,pocet_pisnicek=@pocetPisnicek,datum_vydani=@datumVydani where ID_alba=@idA";

        public AlbumTable()
        {
        }

        public static int Insert(Album Album)
        {
            Database db = new Database();
            db.Connect();

            SqlCommand command = db.CreateCommand(SQL_INSERT);
            PrepareCommand(command,Album);

            int ret = db.ExecuteNonQuery(command);
            db.Close();
            return ret;

        }

        public static int Update(Album album, int id)
        {
            Database db = new Database();
            db.Connect();
            SqlCommand command = db.CreateCommand(SQL_UPDATE);

            command.Parameters.Add(new SqlParameter("@idA", SqlDbType.Int));
            command.Parameters["@idA"].Value = id;

            PrepareCommand(command, album);
            int ret = db.ExecuteNonQuery(command);
            db.Close();
            return ret;
        }

        private static void PrepareCommand(SqlCommand command, Album Album)
        {
            command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            command.Parameters["@id"].Value = Album.IdAlba;

            command.Parameters.Add(new SqlParameter("@nazev", SqlDbType.VarChar, Album.Nazev.Length));
            command.Parameters["@nazev"].Value = Album.Nazev;

            command.Parameters.Add(new SqlParameter("@datumVydani", SqlDbType.Date));
            command.Parameters["@datumVydani"].Value = Album.DatumVydani;

            command.Parameters.Add(new SqlParameter("@pocetPisnicek", SqlDbType.Int));
            command.Parameters["@pocetPisnicek"].Value = Album.PocetPisnicek;

        }

        private static Collection<Album> Read(SqlDataReader reader)
        {
            Collection<Album> Alba = new Collection<Album>();

            while (reader.Read())
            {
                Album Album = new Album();
                Pisnicka Pisnicka = new Pisnicka();
                Album.IdAlba = reader.GetInt32(0);
                Album.Nazev = reader.GetString(1);
                Album.DatumVydani = reader.GetDateTime(2);
                Album.PocetPisnicek = reader.GetInt32(3);
                Alba.Add(Album);
            }
            return Alba;
        }

        private static Collection<Album> Read2(SqlDataReader reader2)
        {
            Collection<Album> Alba = new Collection<Album>();

            while (reader2.Read())
            {
                Album Album = new Album();
                Album.Nazev = reader2.GetString(0);
                Album.DatumVydani = reader2.GetDateTime(1);
                Alba.Add(Album);
            }
            return Alba;
        }

        public static Album SelectId(int id)
        {
            Database db = new Database();
            db.Connect();
            SqlCommand command = db.CreateCommand(SQL_SELECT_ID);

            command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            command.Parameters["@id"].Value = id;
            SqlDataReader reader = db.Select(command);

            Collection<Album> Alba = Read(reader);
            Album Album = null;
            if (Alba.Count == 1)
            {
                Album = Alba[0];
            }
            reader.Close();
            db.Close();
            return Album;
        }

        public static Collection<Album> SELECT()
        {
            Database db = new Database();
            db.Connect();
            SqlCommand command = db.CreateCommand(SQL_SELECT);
            SqlDataReader reader2 = db.Select(command);

            Collection<Album> Alba = Read2(reader2);
            reader2.Close();
            db.Close();
            return Alba;
        }

        public static int Delete(int id)
        {
            Database db = new Database();
            db.Connect();
            SqlCommand command = db.CreateCommand(SQL_DELETE_ID);

            command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            command.Parameters["@id"].Value = id;
            int ret = db.ExecuteNonQuery(command);

            db.Close();
            return ret;
        }


     public static void PridejPisnickuDoAlba(int id)
        {
            Database db = new Database();
            db.Connect();

            SqlCommand cmd = db.CreateCommand("PisnickaDoAlba");
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parm = new SqlParameter("@v_idAlba", SqlDbType.Int);
            parm.Value = id;
            parm.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(parm);

            try
            {
                int ret = db.ExecuteNonQuery(cmd);
            }
            catch
            {
                Console.WriteLine("neplatne ide alba");
            }
            db.Close();
        }

    }
}