Signum Framework Principles

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Signum.Utilities;
using System.Data.SqlClient;
using Signum.Engine;
using Signum.Entities;
using Signum.Utilities.DataStructures;
using Signum.Engine.Properties;
using Signum.Engine.Maps;


namespace Signum.Engine
{
    internal static class SqlBuilder
    {
        public readonly static SqlDbType PrimaryKeyType = SqlDbType.Int;
        public readonly static string PrimaryKeyName = "Id";

        public readonly static SqlDbType TicksType = SqlDbType.BigInt;
        public readonly static string TicksName = "Ticks";

        public readonly static SqlDbType ToStrType = SqlDbType.NVarChar;
        public readonly static string ToStrName = "ToStr";

        public readonly static int MaxParametersInSQL = 2000;

        public static SqlPreCommand CreateTable(string table, List<string> campos)
        {
            return new SqlPreCommandSimple("CREATE TABLE {0}(\r\n{1}\r\n)".Formato(table.SqlScape(), campos.ToString(",\r\n").Indent(2)));
        }


        static HashSet<string> Keywords = Resources.__SqlKeywords__.Lines().Select(a => a.Trim().ToUpper()).ToHashSet();

        public static string SqlScape(this string ident)
        {
            if (Keywords.Contains(ident.ToUpper()))
            {
                return "[" + ident + "]";
            }
            return ident; 
        }

   

        internal static SqlPreCommand UpdateSetIdEntity(string table, List<SqlParameter> parameters, int id, long ticks)
        {
            SqlParameter ticksParam = SqlParameterBuilder.CreateParameter("ticks", SqlDbType.BigInt, false, ticks);

            return SqlPreCommand.Combine(Spacing.Simple,
                RestoreLastID(id),
                new SqlPreCommandSimple(
                    "UPDATE {0} SET \r\n{1}\r\n WHERE id = @LastEntityID AND ticks = {2}".Formato(table.SqlScape(),
                    parameters.ToString(p => "{0} = {1}".Formato(p.SourceColumn.SqlScape(), p.ParameterName).Indent(2), ",\r\n"),
                    ticksParam.ParameterName), parameters.And(ticksParam).ToList()),
                new SqlPreCommandSimple("IF @@ROWCOUNT=0\r\nRAISERROR('{0}', 16, 1)"
                    .Formato(Resources.ConcurrencyErrorOnDatabaseTable0Id1.Formato(table, id)))
                    );
        }

        internal static SqlPreCommand UpdateSetId(string table, List<SqlParameter> parameters, int id)
        {
            return SqlPreCommand.Combine(Spacing.Simple,
                RestoreLastID(id),
                new SqlPreCommandSimple(
                    "UPDATE {0} SET \r\n{1}\r\n WHERE id = @LastEntityID".Formato(table.SqlScape(),
                    parameters.ToString(p => "{0} = {1}".Formato(p.SourceColumn.SqlScape(), p.ParameterName).Indent(2), ",\r\n")),
                    parameters.ToList()));
        }

        internal static SqlPreCommand Insert(string table, List<SqlParameter> parameters)
        {
            return new SqlPreCommandSimple("INSERT {0} ({1}) \r\n VALUES ({2})".Formato(table,
                parameters.ToString(p => p.SourceColumn.SqlScape(), ", "),
                parameters.ToString(p => p.ParameterName, ", ")), parameters);
        }

        internal static SqlPreCommand InsertSaveID(string table, List<SqlParameter> parameters, IdentifiableEntity entitiesToUpdate)
        {
            return SqlPreCommand.Combine(Spacing.Simple,
                new SqlPreCommandSimple("INSERT INTO {0} ({1})\r\n OUTPUT INSERTED.id INTO @MyIdTable\r\n VALUES ({2})".Formato(table.SqlScape(),
                       parameters.ToString(p => p.SourceColumn.SqlScape(), ", "),
                       parameters.ToString(p => p.ParameterName, ", ")), parameters) { EntitiesToUpdate = entitiesToUpdate },
                new SqlPreCommandSimple("SET @LastEntityID = SCOPE_IDENTITY() ")); 
        }

        internal static SqlPreCommand RestoreLastID(int id)
        {
            SqlParameter pid = SqlParameterBuilder.CreateReferenceParameter(SqlBuilder.PrimaryKeyName, false, id);

            return new SqlPreCommandSimple("SET @LastEntityID = {0}".Formato(pid.ParameterName), new List<SqlParameter> { pid });
        }

        internal static SqlPreCommand DeleteSql(string table)
        {
            return new SqlPreCommandSimple("DELETE {0} WHERE id = @LastEntityID".Formato(table.SqlScape()));
        }
      
        internal static SqlPreCommand DeclareIDsMemoryTable()
        {
            return new SqlPreCommandSimple("DECLARE @MyIdTable table(id int)");
        }

        internal static SqlPreCommandSimple DeclareLastEntityID()
        {
            return new SqlPreCommandSimple("DECLARE @LastEntityID int");
        }

        internal static SqlPreCommand SelectIDMemoryTable()
        {
            return new SqlPreCommandSimple("SELECT id FROM @MyIdTable as InsertedIdTable");
        }

        internal static SqlPreCommandSimple SelectLastEntityID()
        {
            return new SqlPreCommandSimple("SELECT @LastEntityID as LastID");
        }

        internal static SqlPreCommand RelationalDeleteScope(string table, string backIdColumn)
        {
            return new SqlPreCommandSimple("DELETE {0} WHERE {1} = @LastEntityID".Formato(table.SqlScape(), backIdColumn));
        }

        internal static SqlPreCommand RelationalInsertScope(string table, string backIdColumn, List<SqlParameter> campoParameters)
        {
            return new SqlPreCommandSimple("INSERT INTO {0} ({1}, {2}) VALUES ( @LastEntityID, {3})".Formato(table.SqlScape(), backIdColumn.SqlScape(),
                campoParameters.ToString(p => p.SourceColumn.SqlScape(), ", "),
                campoParameters.ToString(p => p.ParameterName, ", ")), campoParameters);
        }

        internal static SqlPreCommand SelectByIds(string table, string[] columns, string column, int[] ids)
        {
            List<SqlParameter> parameters = ids.Select(id=>SqlParameterBuilder.CreateReferenceParameter("val", false, id)).ToList();

            return new SqlPreCommandSimple("SELECT {0} FROM {1} WHERE {2} IN ({3})".Formato(
            columns.ToString(a => a.SqlScape(), ", "), table.SqlScape(), column.SqlScape(),
            parameters.ToString(p => p.ParameterName, ", ")), parameters);
        }

        internal static SqlPreCommand SelectCount(string table, int id)
        {
            SqlParameter idParam = SqlParameterBuilder.CreateReferenceParameter("id", false, id);

            return new SqlPreCommandSimple("SELECT COUNT(id) FROM {0} WHERE  id = {1}".Formato(table.SqlScape(), idParam.ParameterName), new List<SqlParameter> { idParam });
        }

        internal static SqlPreCommand SelectAll(string table, string[] columns)
        {
            return new SqlPreCommandSimple("SELECT {0} FROM {1}".Formato(
            columns.ToString(a=>a.SqlScape(), ", "),
            table.SqlScape()));
        }

        #region Create Tables

        public static SqlPreCommand CreateTableSql(ITable t)
        {
            return CreateTable(t.Name, t.Columns.Values.Select(c => SqlBuilder.CreateField(c)).ToList());
        }

        internal static SqlPreCommand DropTable(string table)
        {
            return new SqlPreCommandSimple("DROP TABLE {0}".Formato(table.SqlScape())); 
        }

        internal static SqlPreCommand AlterTableDropColumn(string table, string columnName)
        {
            return new SqlPreCommandSimple("ALTER TABLE {0} DROP COLUMN {1}".Formato(table.SqlScape(), columnName.SqlScape())); 
        }

        internal static SqlPreCommand AlterTableAddColumn(string table,  IColumn column)
        {
            return new SqlPreCommandSimple("ALTER TABLE {0} ADD {1} -- DEFAULT( )".Formato(table, CreateField(column)));
        }

        internal static SqlPreCommand AlterTableAlterColumn(string table, IColumn column)
        {
            return new SqlPreCommandSimple("ALTER TABLE {0} ALTER COLUMN {1}".Formato(table.SqlScape(), CreateField(column)));
        }

        public static string CreateField(IColumn c)
        {
            return SqlBuilder.CreateField(c.Name, c.SqlDbType, c.Size, c.Scale, c.Nullable, c.PrimaryKey, c.Identity);
        }

        
        public static string CreatePrimaryKeyField(bool identity)
        {
            return CreateField(PrimaryKeyName, PrimaryKeyType, null, null, false, true, identity);
        }

        public static string CreateReferenceField(string name, bool nullable)
        {
            return CreateField(name, PrimaryKeyType, null, null, nullable, false, false);
        }

        public static string CreateField(string name, SqlDbType type, int? size, int? scale, bool nullable, bool primaryKey, bool identity)
        {
            return "{0} {1}{2} {3}{4}{5}".Formato(
                name.SqlScape(),
                type.ToString().ToUpper(),
                GetSizeScale(size, scale),
                identity ? "IDENTITY " : "",
                nullable ? "NULL" : "NOT NULL",
                primaryKey ? " PRIMARY KEY" : "");
        }

        private static string GetSizeScale(int? size, int? scale)
        {
            if (size == null)
                return "";

            if (size == int.MaxValue)
                return "(MAX)";

            if (scale == null)
                return "({0})".Formato(size);

            return "({0},{1})".Formato(size, scale);
        }

        public static SqlPreCommand AlterTableForeignKeys(ITable t)
        {
            return t.Columns.Values.Select(c =>
                c.ReferenceTable == null ? null : SqlBuilder.AlterTableAddForeignKey(t.Name, c.Name, c.ReferenceTable.Name)).Combine(Spacing.Simple);
        }

        public static SqlPreCommand CreateIndicesSql(ITable t)
        {
            return t.Columns.Values.Select(c => SqlBuilder.CreateIndex(c.Index, t.Name, c.Name)).Combine(Spacing.Simple);
        }

        internal static SqlPreCommand DropIndex(string indexName, string table)
        {
            return new SqlPreCommandSimple("DROP INDEX {0}.{1}".Formato(table.SqlScape(), indexName.SqlScape()));
        }

        public static SqlPreCommand CreateIndex(Index index, string table, params string[] fieldNames)
        {
            if (index == Index.None)
                return null;


            if (index == Index.Multiple || index == Index.Unique)
            {

                return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".Formato(
                    index == Index.Unique ? "UNIQUE " : "",
                    IndexName(table, fieldNames),
                    table.SqlScape(),
                    fieldNames.ToString(a => a.SqlScape(), ", ")));
            }

            if (index == Index.UniqueMultiNulls)
            {
                string triggerName = "UT_{0}_{1}".Formato(table, fieldNames.ToString("_"));

                return new SqlPreCommandSimple(
@"CREATE  trigger {0} on {1} for insert, update as 
BEGIN  
    IF (select max(cnt) from 
            (select count(i.{2}) as cnt from {1}, inserted i where {1}.{2}=i.{2} group by i.{2}) x) > 1 
    raiserror('{3}',16,1) 
END".Formato(triggerName.SqlScape(), table.SqlScape(), fieldNames.Single().SqlScape(), Resources.The2alreadyexistontable1));
            }



            return null;
        }

        public static string IndexName(string table, params string[] fieldNames)
        {
            return "IX_{0}_{1}".Formato(table, fieldNames.ToString("_")).SqlScape();
        }

        public static SqlPreCommand AlterTableDropForeignKey(string table, string foreingKeyName)
        {
            return new SqlPreCommandSimple("ALTER TABLE {0} DROP CONSTRAINT {1} ".Formato(
                table.SqlScape(),
                foreingKeyName.SqlScape()));
        }


        public static SqlPreCommand AlterTableAddForeignKey(string table, string fieldName, string foreignTable)
        {
            return new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4})".Formato(
                table.SqlScape(),
                ForeignKeyName(table, fieldName),
                fieldName.SqlScape(),
                foreignTable.SqlScape(),
                PrimaryKeyName.SqlScape()));
        }

        public static string ForeignKeyName(string table, string fieldName)
        {
            return "FK_{0}_{1}".Formato(table, fieldName).SqlScape();
        }
        #endregion



      
    }
}