using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NeshinaPolina2111
{
    internal class DB
    {
        private static DB instance;
        private MySqlConnection connection;
        private string connectionString = "server=git.pgk.alspio.com; port=3306; username=ISP41_Neshina; password=ISP41_Neshina; database=ISP41_Neshina_Dem2011; Sslmode=none; Charset=utf8mb4";

        private DB()
        {
            connection = new MySqlConnection(connectionString);
            OpenConnection();
        }

        public static DB GetInstance()
        {
            if (instance == null)
                instance = new DB();
            return instance;
        }

        public void OpenConnection()
        {
            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();
        }

        public MySqlConnection GetConnection() => connection;

        public static decimal CalculateProductCost(int productId) //метод расчёта для модуоля 2
        {
            string query = @"
        SELECT 
            COALESCE(SUM(m.PriceUnitMaterial * pc.Quantity), 0) AS TotalCost
        FROM Product_composition pc
        JOIN Materials_import m ON pc.idMaterials_import = m.idMaterials_import
        WHERE pc.idProducts_import = @productId";

            var conn = DB.GetInstance().GetConnection();
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            using (var cmd = new MySqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@productId", productId);
                var result = cmd.ExecuteScalar();
                return Convert.ToDecimal(result);
            }
        }

        //это для выполнения метода из 4 модуля рассчёт
        public static int CalculateMaterialToPurchase(
      int productTypeId,
      int materialTypeId,
      int quantityProduced,
      decimal param1,
      decimal param2,
      decimal stockMaterial)
        {//1 валидация
            if (productTypeId <= 0 ||
                materialTypeId <= 0 ||
                quantityProduced <= 0 ||
                param1 <= 0 ||
                param2 <= 0 ||
                stockMaterial < 0)
            {
                return -1;
            }

            // загрузка коэфф тип продукт
            decimal productCoefficient;
            string productQuery = "SELECT Coefficient FROM Product_type_import WHERE idProduct_type_import = @productTypeId";
            var conn = DB.GetInstance().GetConnection();
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            using (var cmd = new MySqlCommand(productQuery, conn))
            {
                cmd.Parameters.AddWithValue("@productTypeId", productTypeId);
                var result = cmd.ExecuteScalar();
                if (result == null || result == DBNull.Value)
                    return -1; // тип продукции не найден
                productCoefficient = Convert.ToDecimal(result);
            }

            // загрузка процент брака
            decimal scrapPercent;
            string materialQuery = "SELECT Procent FROM Material_type_import WHERE idMaterial_type_import = @materialTypeId";
            using (var cmd = new MySqlCommand(materialQuery, conn))
            {
                cmd.Parameters.AddWithValue("@materialTypeId", materialTypeId);
                var result = cmd.ExecuteScalar();
                if (result == null || result == DBNull.Value)
                    return -1; // тип материала не найден
                scrapPercent = Convert.ToDecimal(result);
            }

            // базовая потребность на 1
            decimal baseNeedPerUnit = param1 * param2 * productCoefficient;

            // общая потреб без брак
            decimal totalNeed = baseNeedPerUnit * quantityProduced;

            // + учесть брак
            decimal scrapMultiplier = 1.0m + (scrapPercent / 100.0m);
            decimal totalNeedWithScrap = totalNeed * scrapMultiplier;

            // минус остатки склада
            decimal needToPurchase = totalNeedWithScrap - stockMaterial;

            if (needToPurchase <= 0)
                return 0;

            // округлить до целого
            // купить только целое
            int resultInt = (int)Math.Ceiling(needToPurchase);

            return resultInt;
        }
    }
}