CREATE TABLE [dbo].[actions] (
	[id] [smallint] NOT NULL ,
	[name] [varchar] ( 50 ) NOT NULL ,
	[name_ru] [varchar] ( 50 ) NULL ,
	[form] [bit] NOT NULL ,
	

 [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[doc_type_flow_types] (
	[id] [smallint] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[doc_type] [smallint] NOT NULL ,
	[flow_type] [smallint] NOT NULL ,
	[start_stage] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[doc_types] (
	[id] [smallint] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[db] [tinyint] NOT NULL ,
	[table] [nvarchar] ( 128 ) NOT NULL ,
	[name] [varchar] ( 50 ) NOT NULL ,
	[name_ru] [varchar] ( 50 ) NOT NULL ,
	[create_action] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[docs] (
	[id] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[doc] [uniqueidentifier] NOT NULL ,
	[type] [smallint] NOT NULL ,
	[flow_type] [smallint] NOT NULL ,
	[stage] [smallint] NOT NULL ,
	[state] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[flow_defs] (
	[id] [smallint] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[type] [smallint] NOT NULL ,
	[stage] [smallint] NOT NULL ,
	[state] [smallint] NOT NULL ,
	[action] [smallint] NOT NULL ,
	[type_next] [smallint] NULL ,
	[stage_next] [smallint] NULL ,
	[state_next] [smallint] NULL ,
	[roles] [varchar] ( 255 ) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[flow_types] (
	[id] [smallint] NOT NULL ,
	[name] [varchar] ( 50 ) NOT NULL ,
	[name_ru] [varchar] ( 50 ) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[stages] (
	[id] [smallint] NOT NULL ,
	[name] [varchar] ( 50 ) NULL ,
	[name_ru] [varchar] ( 50 ) NULL ,
	[process] [varchar] ( 50 ) NOT NULL ,
	[process_ru] [varchar] ( 50 ) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[states] (
	[id] [smallint] NOT NULL ,
	[name] [varchar] ( 50 ) NOT NULL ,
	[name_ru] [varchar] ( 50 ) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[workflow] (
	[id] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[doc] [int] NOT NULL ,
	[stage] [smallint] NOT NULL ,
	[state] [smallint] NOT NULL ,
	[action] [smallint] NOT NULL ,
	[sysuser] [nvarchar] ( 128 ) NOT NULL ,
	[user] [smallint] NULL ,
	[date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[actions] ADD 
	CONSTRAINT [DF_actions_form] DEFAULT ( 1 ) FOR [form],
	CONSTRAINT [DF_actions_list] DEFAULT ( 0 ) FOR 

,
	CONSTRAINT [PK_actions] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[doc_type_flow_types] ADD 
	CONSTRAINT [DF_doc_type_flow_types_start_stage] DEFAULT ( 1 ) FOR [start_stage],
	CONSTRAINT [PK_workflow_doc] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[doc_types] ADD 
	CONSTRAINT [DF_doc_types_start_state] DEFAULT ( 1 ) FOR [create_action],
	CONSTRAINT [PK_doc_type] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[docs] ADD 
	CONSTRAINT [DF_docs_doc] DEFAULT (newid()) FOR [doc],
	CONSTRAINT [DF_docs_flow_type] DEFAULT ( 0 ) FOR [flow_type],
	CONSTRAINT [DF_docs_stage] DEFAULT ( 0 ) FOR [stage],
	CONSTRAINT [DF_docs_state] DEFAULT ( 0 ) FOR [state],
	CONSTRAINT [PK_docs] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[flow_defs] ADD 
	CONSTRAINT [DF_flow_defs_type_next] DEFAULT ( 0 ) FOR [type_next],
	CONSTRAINT [PK_workflow_def] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[flow_types] ADD 
	CONSTRAINT [PK_type] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[stages] ADD 
	CONSTRAINT [PK_stage] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[states] ADD 
	CONSTRAINT [PK_state] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[workflow] ADD 
	CONSTRAINT [DF_workflow_sysuser] DEFAULT (suser_sname()) FOR [sysuser],
	CONSTRAINT [DF_workflow_date] DEFAULT (getdate()) FOR [date],
	CONSTRAINT [PK_workflow] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[apply_action] 
	@doc uniqueidentifier, 
	@action smallint
AS
	IF NOT EXISTS(SELECT  1  FROM [dbo].[docs] d INNER JOIN [dbo].[flow_defs] fd ON d.[flow_type] = fd.[type] AND d.[stage] = fd.[stage] AND d.[state] = fd.[state] AND fd.[action] = @action WHERE d.[doc] = @doc)
		RETURN  1 

BEGIN TRAN
	INSERT [dbo].[workflow] ([doc], [stage], [state], [action])
	SELECT [id], [stage], [state], @action
	FROM [dbo].[docs]
	WHERE [doc] = @doc
	IF @@ERROR <>  0  GOTO error

	UPDATE [dbo].[docs] SET 
		[stage] = fd.[stage_next],
		[state] = fd.[state_next]
	FROM [dbo].[docs]
		INNER JOIN [dbo].[flow_defs] fd ON [dbo].[docs].[flow_type] = fd.[type] AND [dbo].[docs].[stage] = fd.[stage] AND [dbo].[docs].[state] = fd.[state] AND fd.[action] = @action
	WHERE [dbo].[docs].[doc] = @doc
	IF @@ERROR <>  0  GOTO error
COMMIT TRAN
	RETURN  0 
error:
ROLLBACK TRAN
	RETURN  1 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[apply_action_by_name] 
	@doc uniqueidentifier,
	@action varchar( 255 )
AS
	DECLARE @act smallint, @ret int

	SELECT @act = [id] FROM [dbo].[actions] WHERE [name] = @action

	IF @@ROWCOUNT =  0 
		RETURN  1 

	EXEC @ret = [dbo].[apply_action] @doc, @act

	RETURN @ret
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[create_doc] 
	@type smallint,
	@doc uniqueidentifier OUTPUT
AS
	DECLARE @ret smallint, @ca smallint

BEGIN TRAN
SET TRAN ISOLATION LEVEL SERIALIZABLE

	INSERT [dbo].[docs] ([type]) SELECT @type
	IF @@ERROR <>  0  GOTO error
	
	SELECT @doc = [doc] FROM [dbo].[docs] 
	WHERE [id] = @@IDENTITY
	IF @@ERROR <>  0  GOTO error
	
	SELECT @ca = [create_action] FROM [dbo].[doc_types] WHERE [id] = @type
	EXEC @ret = [dbo].[apply_action] @doc, @ca
	IF @@ERROR <>  0  OR @ret <>  0  GOTO error
COMMIT TRAN
	RETURN  0 
error:
ROLLBACK TRAN
	RETURN  1 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[get_available_actions] 
	@doc uniqueidentifier
AS
	SELECT a.*
	FROM [dbo].[docs] d 
		INNER JOIN [dbo].[flow_defs] fd ON d.[flow_type] = fd.[type] AND d.[stage] = fd.[stage] AND d.[state] = fd.[state]
		INNER JOIN [dbo].[actions] a ON fd.[action] = a.[id] 
	WHERE 
		d.[doc] = @doc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[get_doc_flow_types] 
	@doc uniqueidentifier
AS
	SELECT ft.*
	FROM [dbo].[docs] d
		INNER JOIN [dbo].[doc_type_flow_types] df ON d.[type] = df.[doc_type]
		INNER JOIN [dbo].[flow_types] ft ON df.[flow_type] = ft.[id]
	WHERE d.[doc] = @doc
	ORDER BY ft.[id]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[get_doc_status] 
	@doc uniqueidentifier,
	@stage smallint OUTPUT,
	@state smallint OUTPUT
AS
	IF NOT EXISTS(	SELECT  1  FROM [dbo].[docs] WHERE [doc] = @doc)
		RETURN  1 

	SELECT @stage = [stage], @state = [state]
	FROM [dbo].[docs]
	WHERE [doc] = @doc

	RETURN  0 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[set_doc_flow_type] 
	@doc uniqueidentifier,
	@flow_type smallint
AS
	DECLARE @ret smallint
	
	IF NOT EXISTS(SELECT  1  FROM [dbo].[doc_type_flow_types] df INNER JOIN [dbo].[docs] d ON df.[doc_type] = d.[type] WHERE d.[doc] = @doc AND df.[flow_type] = @flow_type)
		OR NOT EXISTS(SELECT  1  FROM [dbo].[docs] d INNER JOIN [dbo].[flow_defs] fd ON d.[flow_type] = fd.[type] AND d.[stage] = fd.[stage] AND d.[state] = fd.[state] WHERE d.[doc] = @doc AND fd.[action] =  0 )
		RETURN  1 

BEGIN TRAN
	EXEC @ret = [dbo].[apply_action] @doc,  0   -- predefined (Set Workflow)
 
	IF @@ERROR <>  0  OR @ret <>  0  GOTO error

	UPDATE [dbo].[docs] SET
		[flow_type] = @flow_type,
		[stage] = df.[start_stage]
	FROM [dbo].[doc_type_flow_types] df 
		INNER JOIN [dbo].[docs] ON df.[doc_type] = [dbo].[docs].[type] 
	WHERE [dbo].[docs].[doc] = @doc AND df.[flow_type] = @flow_type
	IF @@ERROR <>  0  GOTO error

COMMIT TRAN
	RETURN  0 
error:
ROLLBACK TRAN
	RETURN  1 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO