using System.Text;

namespace HotelPms.Share.Data.Script;

public class TableCreator : IDisposable
{
    public TableCreator()
    {

    }

    /// <summary>
    /// 初期化
    /// 必ずPKがある
    /// </summary>
    /// <param name="name">テーブル名</param>
    public TableCreator(string name)
    {
        Name = name;
    }

    /// <summary>
    /// テーブル名
    /// </summary>
    public string Name { get; set; } = string.Empty;
    
    /// <summary>
    /// テーブルの説明
    /// </summary>
    public string Description { get; set; } = string.Empty; 

    /// <summary>
    /// パーティションの構成
    /// </summary>
    public string PartitionSchema { get; set; } = string.Empty;

    /// <summary>
    /// パーティション使用列名
    /// </summary>
    public string PartitionKey { get; set; } = string.Empty;

    /// <summary>
    /// 項目
    /// </summary>
    public List<Field> Fields { get; set; } = new List<Field>();

    /// <summary>
    /// 項目
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>
    public virtual Field this[int index]
    {
        get { return Fields[index]; }
    }

    /// <summary>
    /// 名称で検索する
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public virtual Field? this[string name]
    {
        get { return Fields.FirstOrDefault<Field>(item => item.Name == name); }
    }

    /// <summary>
    /// メモリー解放
    /// </summary>
    public void Dispose()
    {
        Fields.Clear();
    }

    /// <summary>
    /// SQL文の作成
    /// テーブルを削除してから、新規作成する
    /// </summary>
    /// <returns></returns>
    public override string ToString()
    {
        StringBuilder desc = new StringBuilder();
        // テーブルの説明
        if (Description.Length > 0)
        {
            desc.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{Name}';");
        }

        StringBuilder cols = new StringBuilder();
        StringBuilder pk = new StringBuilder();
        foreach(Field item in Fields)
        {
            if (item.IsPrimaryKey) 
            { 
                if(pk.Length > 0) { pk.Append(",");  }
                pk.Append($"[{item.Name}] ASC");
            }

            cols.AppendLine($"[{item.Name}] {item.DataType} {(item.IsIdentity ? "IDENTITY(1,1)" : string.Empty)} {(item.IsPrimaryKey ? "NOT" : string.Empty)} NULL,");

            if (item.Description.Length > 0)
            {
                desc.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{item.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{Name}', @level2type=N'COLUMN',@level2name=N'{item.Name}';");
            }
        }

        //PARTITION構成の場合、最後にON [PARTITION構成]([列名])
        string partition = string.Empty;
        if(PartitionSchema.Length > 0) { partition = $"ON [{PartitionSchema}]([{PartitionKey}])"; }
        ;
        return $@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{Name}') AND type in (N'U'))
DROP TABLE {Name}

CREATE TABLE [{Name}](
{cols.ToString()}
 CONSTRAINT [PK_{Name}] PRIMARY KEY CLUSTERED 
(
	{pk.ToString()}
)) {partition}

{desc.ToString()}
";
    }


}