
-- DROP IF EXISTS PROCEDURE dbo.utils_GenerateCSharpClass
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'utils_GenerateCSharpClass')
DROP PROCEDURE [dbo].[utils_GenerateCSharpClass]
GO

-- =============================================
-- Author:		Pablo Lopez<juan.garcia@toptal.com>
-- Create date: 2019-06-06
-- Description:	
--		Generate a C# class with Table and Columns 
--		mappings from a view or table passing it's 
--		name and a desired namespace.
--
--	This stored procedure depends on function:
--		utils_Singularize
-- =============================================
CREATE PROCEDURE [dbo].[utils_GenerateCSharpClass]
	-- Add the parameters for the stored procedure here
	@tableName varchar(128)
	,@classNamespace varchar(max) = 'CAPI.Custom.Entities'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @className varchar(128) = right(replace(@tableName, ' ', '_'), charindex('_', reverse(replace(@tableName, ' ', '_')) + '_') - 1)
set @className = dbo.utils_Singularize(@className)

declare @Result varchar(max) = 'namespace ' + @classNamespace + '
{
	using System;
	using System.ComponentModel.DataAnnotations;
	using System.ComponentModel.DataAnnotations.Schema;

	[Table("' + @tableName + '")]
	public class ' + @className + '
	{'

select @Result = @Result + '
		' + IsKey + '[Column("'+ColumnName+'")]
		public ' + ColumnType + NullableSign + ' ' +ColumnName + ' { get; set; }
'
from
(
    select 
        --replace(col.name, ' ', '_') ColumnName,
		col.name ColumnName,
		case col.name
			when 'recId' then 'Id'
			else right(replace(col.name, ' ', '_'), charindex('_', reverse(replace(col.name, ' ', '_')) + '_') - 1)
		end PropertyName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'double'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign,
		case col.is_identity 
			when 1 then '[Key]
			'
			else '' end IsKey
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@tableName)
) t
order by ColumnName

set @Result = @Result  + '	}
}'

print @Result
select @tableName as TableName, @className as ClassName, @Result as GeneratedCode
END
GO


