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

namespace ConstraintCleaner
{
    public class ConstraintCleanerApp
    {

        public string ConnectionString { get; set; }
        public StringBuilder SqlScript { get; set; }

        const string CheckConstraints = "DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;";

        public ConstraintCleanerApp()
        {
            SqlScript = new StringBuilder();
        }


        private List<ConstraintTableDAO> GetViolations()
        {
            List<ConstraintTableDAO> output = new List<ConstraintTableDAO>();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(CheckConstraints, conn))
                {
                    if (conn.State != System.Data.ConnectionState.Open)
                        conn.Open();

                    SqlDataReader executeReader = cmd.ExecuteReader();

                    while(executeReader.Read())
                    {
                        output.Add(new ConstraintTableDAO() { 
                            Table = executeReader.GetString(0),
                            Constraint = executeReader.GetString(1),
                            WhereStatement = executeReader.GetString(2),
                        });
                    }


                    if (conn.State != System.Data.ConnectionState.Closed)
                        conn.Close();
                }
            }
            return output;
        }
        public void Run()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            List<ConstraintTableDAO> violations = GetViolations();

            while (violations.Count > 0)
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    if (conn.State != System.Data.ConnectionState.Open)
                        conn.Open();

                    foreach (var violation in violations)
                    {
                        string DeleteSql = string.Format("DELETE FROM {0} where {1}", violation.Table, violation.WhereStatement.Replace("'",""));
                        SqlScript.AppendLine(string.Format("{0};", DeleteSql));

                        SqlCommand cmd = new SqlCommand(DeleteSql, conn);
                        cmd.ExecuteNonQuery();

                    }

                    if (conn.State != System.Data.ConnectionState.Closed)
                        conn.Close();
                }
                violations = GetViolations();
            }

            SqlScript.AppendLine("EXEC sp_msforeachtable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"");
            
            File.WriteAllText("Cleanup.sql", SqlScript.ToString());
        }

    }
}
