using System.Collections.Generic;
using System.Data.SqlClient;

using Baza_Reklam.Classes.Model;

namespace Baza_Reklam.Classes.Repositories
{
    public class ModuleRepository : Repository<Module>
    {
        public Module FindByName(string name, int sectionId)
        {
            string query =
                "SELECT M.Id, M.MOD_TYP, CAST(M.MOD_SZER AS decimal(9,2)), CAST(M.MOD_WYS AS decimal(9,2)), M.Cena, M.CENA_MIN, M.opis, M.MultiModule, M.MaxHorizontal, M.MaxVertical, M.MarginHorizontal, M.MarginVertical FROM dbo.[NAZWY MODUŁÓW] M INNER JOIN dbo.SekcjeModuly SM ON SM.ModulId=M.Id INNER JOIN dbo.Grzbiety S ON S.Id=SM.SekcjaId WHERE S.Id={0} AND M.MOD_TYP='{1}'";
            query = string.Format(query, sectionId, name);

            Module module = null;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //TODO: get rid of the connection string on this level
                conn = new SqlConnection(ConnString.getConnString().Value);
                conn.Open();
                cmd = new SqlCommand(query, conn);
                reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        module = new Module(reader.GetInt32(0), reader.GetString(1));
                        if (!reader.IsDBNull(2))
                            module.Width = reader.GetDecimal(2);
                        if (!reader.IsDBNull(3))
                            module.Height = reader.GetDecimal(3);
                        if (!reader.IsDBNull(4))
                            module.Price = reader.GetDecimal(4);
                        if (!reader.IsDBNull(5))
                            module.MinPrice = reader.GetDecimal(5);

                        module.Description = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);

                        module.MultiModule = reader.IsDBNull(7) ? false : reader.GetBoolean(7);
                        module.MaxHorizontal = reader.GetInt32(8);
                        module.MaxVertical = reader.GetInt32(9);
                        module.MarginHorizontal = reader.GetInt32(10);
                        module.MarginVertical = reader.GetInt32(11);
                    }
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null) cmd.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }
            return module;
        }


        public List<Module> FindAllActiveBySection(Section section)
        {
            string query = "SELECT M.Id, M.MOD_TYP, CAST(M.MOD_SZER AS decimal(9,2)), CAST(M.MOD_WYS AS decimal(9,2)), M.Cena, M.CENA_MIN, M.opis, M.MultiModule, M.MaxHorizontal, M.MaxVertical, M.MarginHorizontal, M.MarginVertical FROM dbo.[NAZWY MODUŁÓW] M INNER JOIN dbo.SekcjeModuly SM ON SM.ModulId=M.Id INNER JOIN dbo.Grzbiety S ON S.Id=SM.SekcjaId WHERE S.Id={0} AND S.Active=1 AND M.Active=1";
            query = string.Format(query, section.Id);

            List<Module> modules = new List<Module>();

            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(ConnString.getConnString().Value);
                conn.Open();
                cmd = new SqlCommand(query, conn);
                reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Module module = new Module(reader.GetInt32(0), reader.GetString(1));
                        if (!reader.IsDBNull(2))
                            module.Width = reader.GetDecimal(2);
                        if (!reader.IsDBNull(3))
                            module.Height = reader.GetDecimal(3);
                        if (!reader.IsDBNull(4))
                            module.Price = reader.GetDecimal(4);
                        if (!reader.IsDBNull(5))
                            module.MinPrice = reader.GetDecimal(5);

                        module.Description   =  reader.IsDBNull(6) ? string.Empty : reader.GetString(6);

                        module.MultiModule = reader.IsDBNull(7) ? false : reader.GetBoolean(7);
                        module.MaxHorizontal = reader.GetInt32(8);
                        module.MaxVertical = reader.GetInt32(9);
                        module.MarginHorizontal = reader.GetInt32(10);
                        module.MarginVertical = reader.GetInt32(11);

                        module.Section = section;

                        modules.Add(module);
                    }
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null) cmd.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }
            return modules;
        }
    }
}
