-- ---------------------------------------------------------------------------------
function migration_check(obj)
    if not obj:test() then
        analiz:log("migration_check:impl")
        obj:migration()
    else
        analiz:log("migration_check:skip")
    end
end
-- ---------------------------------------------------------------------------------
function create_migration_engine1()
    local mig1 = {}
    mig1.pwd = "AMLWeb";
    mig1.pwd_plib = "PLIB";

    function mig1:test()
        analiz:set("tmp", "select top 1 lex_engine from NAMECHECK")
        analiz:execute(
            "echo ANALIZ_SAFE_EXECUTE err etmp LOAD_STRING_DATA_FROM_SQL tmp " ..
                self.pwd .. " $(tmp)")
        analiz:execute(
            "ANALIZ_SAFE_EXECUTE err etmp LOAD_STRING_DATA_FROM_SQL tmp " ..
                self.pwd .. " $(tmp)")
        analiz:execute("echo etmp=$(etmp),err=$(err)")
        -- analiz:execute("abort 1")

        local err = analiz:get("etmp")
        if err == "" then
            analiz:log("mig1:migration1:ok")
            return true
        end
        analiz:log("mig1:migration1:not found")
        return false
    end

    function mig1:migration()
        analiz:log("----mig1:migration.start")

        analiz:set("tmp1",
                   "ALTER TABLE [dbo].[NAMECHECK] ADD lex_engine varchar(255) NULL")
        analiz:set("tmp1_2",
                   "ALTER TABLE [dbo].NAMECHECK_BUFFER ADD lex_engine varchar(255) NULL")

        analiz:set("tmp2",
                   "ALTER TABLE [dbo].[SingleNamecheckResults] ADD [dt] [datetime] NULL DEFAULT (getdate())")
        analiz:set("tmp3", GetGetLockedRecord())
        analiz:set("tmp4", GetSync_CHECK_STATE())

        analiz:set("tmp1",
                   "ALTER TABLE [dbo].[CHECK_STATE] ADD FINAS_IN_STATE int NULL")
        analiz:set("tmp5", CreateCurrentListTable())

        analiz:execute("EXEC_SQL_MULTI " .. self.pwd .. " $(tmp1)")
        analiz:execute("EXEC_SQL_MULTI " .. self.pwd .. " $(tmp1_2)")
        analiz:execute("EXEC_SQL_MULTI " .. self.pwd_plib .. " $(tmp2)")
        analiz:execute("EXEC_SQL_MULTI " .. self.pwd_plib .. " $(tmp3)")
        analiz:execute("EXEC_SQL_MULTI " .. self.pwd_plib .. " $(tmp4)")
        analiz:execute("EXEC_SQL_MULTI " .. self.pwd_plib .. " $(tmp5)")

        analiz:log("----mig1:migration.end")

    end

    function mig1:rollback()

        analiz:log("----mig1:rollback.start")

        analiz:set("tmp1", "ALTER TABLE [dbo].nameresult drop column lex_engine")
        analiz:set("tmp1_2",
                   "ALTER TABLE [dbo].NAMECHECK_BUFFER drop column lex_engine")
        analiz:set("tmp2",
                   "ALTER TABLE [dbo].[SingleNamecheckResults] drop column [dt] ")
        analiz:set("tmp2_1",
                   "ALTER TABLE [dbo].[CHECK_STATE] drop column FINAS_IN_STATE ")

        analiz:set("tmp3", "DROP PROCEDURE [dbo].[GetLockedRecord]")
        analiz:set("tmp4", "DROP PROCEDURE [dbo].[Sync_CHECK_STATE]")

        analiz:execute("EXEC_SQL " .. self.pwd .. " $(tmp1)")
        analiz:execute("EXEC_SQL " .. self.pwd .. " $(tmp1_2)")
        analiz:execute("EXEC_SQL " .. self.pwd_plib .. " $(tmp2)")
        analiz:execute("EXEC_SQL " .. self.pwd_plib .. " $(tmp2_1)")
        analiz:execute("EXEC_SQL " .. self.pwd_plib .. " $(tmp3)")
        analiz:execute("EXEC_SQL " .. self.pwd_plib .. " $(tmp4)")

        analiz:log("----mig1:rollback.end")

    end

    return mig1
end

function create_migration_engine()
    local m1 = create_migration_engine1()
    return m1
end

function CreateCurrentListTable()
    -- USE [plib_namecheck]
    local str = [[CREATE TABLE [dbo].[CurrentListTable](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[dt] [datetime] NULL DEFAULT (getdate()),
	[comment] [varchar](255) NULL,
	[Sender] [varchar](255) NULL,
	[ScriptTable] [varchar](255) NULL
) 
]]
    return str
end

function GetGetLockedRecord()
    local str = [[
USE [staging_namecheck]
GO
/****** Object:  StoredProcedure [dbo].[GetLockedRecord]    Script Date: 25.02.2016 12:44:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetLockedRecord]
	@ServerTag varchar(255),
	@mode varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--SET TRANSACTION ISOLATION LEVEL SERIALIZEABLE;
	declare @count int
	declare @bid varchar(255)
	declare @fast varchar(255)

	set @fast =@mode

	if @fast ='fast' begin
		select BUSINESSCASEID as IDNAME,FirstName as CNAME1, LastName as CNAME2 from namecheck 
	   where 1=0
	   return
	end

	select @count = count(*) from namecheck where lex_engine=@ServerTag and WORKING_STATE=0

	if @count=0 BEGIN
	--BEGIN TRAN T1;

	select top 1 @bid=BUSINESSCASEID
		
		 from namecheck
		 -- with (updlock, rowlock)
		  where lex_engine is NULL and WORKING_STATE=0
		 order by CREATION_DATE

	 UPDATE  namecheck SET  lex_engine = @ServerTag 
	    where lex_engine is NULL and WORKING_STATE=0 and BUSINESSCASEID=@bid
		 
      -- COMMIT TRAN T1;
	end


    -- Insert statements for procedure here
	select BUSINESSCASEID as IDNAME,FirstName as CNAME1, LastName as CNAME2 from namecheck 
	 where lex_engine=@ServerTag and WORKING_STATE=0
END

GO
]]
    return str
end

function GetSync_CHECK_STATE()
    local str = [[
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sync_CHECK_STATE
AS
BEGIN
 declare @in_flag       int
 declare @records_count int

 select top 1 @in_flag=INSTATE from CHECK_STATE

 select top 1 @records_count = count(*) from namecheck where  WORKING_STATE=0 --and lex_engine is NULL

 if @records_count=0 and @in_flag=1 begin
   update CHECK_STATE set INSTATE=0
 end

 if @records_count=1 and @in_flag=0 begin
   update CHECK_STATE set INSTATE=1
 end

select * from check_state
END
GO

  ]]

    return str
end
-- --------------------------------------------------------------------------
function create_migration_engine_batch()
    local m = {}

    function fix_(a)
        local aa = a
        local r = {"?", "-", "\\", "/", ".", "&", ":", " ", "{", "}"}
        for i = 1, #r do aa = ReplaceValue(aa, r[i], "_") end
        return aa
    end

    local fam_count = analiz:get("family_count")
    local fam_id = analiz:get("family_id")
    --  local pref = fix_(BuildString("$(COMPUTERNAME)")).."_"..ReplaceValue(BuildString("$(UNIQUE_IDENTIFIER)"),"_management_"..fam_count,"")
    local pref = fix_(BuildString("$(COMPUTERNAME)")) .. "_" ..
                     ReplaceValue(BuildString("$(UNIQUE_IDENTIFIER)"),
                                  "_" .. fam_id .. "_" .. fam_count, "")
    m.table_name = "Check_ResultsTable_local_" .. pref
    --  analiz:log(m.table_name)

    -- ---------------------------
    function m:test()
        if not (analiz:get("type") == "BATCH") then
            analiz:log("skip.type:" .. analiz:get("type"))
            return true
        end

        --    if not (analiz:get("family_id")=="management") then
        --     analiz:log("skip.family_id:"..analiz:get("family_id")) 
        --     return false
        --    end

        analiz:set("tmp4", "select lex_engine as C from " .. self.table_name)
        analiz:set("tmp", "")
        analiz:set("tmp1", "")
        analiz:execute(
            "ANALIZ_SAFE_EXECUTE tmp tmp1 SET_PARAM_FROM_SQL tmp2 AMLWeb C $(tmp4)")
        analiz:execute(
            "echo check migration: tmp,tmp1=$(tmp), '$(tmp1)',$(tmp4)")
        local ret = analiz:get("tmp1") == ""
        if ret then
            analiz:log("true")
        else
            analiz:log("false")
        end
        return ret
    end
    -- ---------------------------
    function m:migration()
        --    analiz:log("migration_engine_batch")
        --    local t = self:test()
        --    if not t then
        --       analiz:log("migration_engine_batch:skip")
        --        return
        --    end
        analiz:log("migration_engine_batch.execute")
        analiz:set("tmp4", "ALTER TABLE " .. self.table_name ..
                       " ADD lex_engine varchar(255) NULL")
        analiz:execute("echo EXEC_SQL AMLWeb $(tmp4)")
        analiz:execute("EXEC_SQL AMLWeb $(tmp4)")
        analiz:log("migration_engine_batch.execute.end")
    end
    return m
end


