-- Tables

/***** Object:  Table [STOR].[T_STORAGE_LINK_MARK_SCHEMES] *****/
CREATE TABLE [STOR].[T_STORAGE_LINK_MARK_SCHEMES]
(
    [StorageLinkMarkSchemesID] uniqueidentifier NOT NULL,
    [Consumer] uniqueidentifier NULL,
    [Supplier] uniqueidentifier NULL,
    [MainSchema] bit NOT NULL,
    [MarkSchemes] int NOT NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [STOR].[T_STORAGE_LINK_MARK_SCHEMES] ADD CONSTRAINT [PK_T_STORAGE_LINK_MARK_SCHEMES]
PRIMARY KEY CLUSTERED ([StorageLinkMarkSchemesID]) ON [PRIMARY]
GO

ALTER TABLE [STOR].[T_STORAGE_LINK_MARK_SCHEMES] ADD CONSTRAINT [FK_T_CONSUMER_T_STORAGE_LINK_MARK_SCHEMES_Consumer]
FOREIGN KEY([Consumer]) REFERENCES [STOR].[T_CONSUMER] ([ConsumerID])
GO

ALTER TABLE [STOR].[T_STORAGE_LINK_MARK_SCHEMES] ADD CONSTRAINT [FK_T_SUPPLIER_T_STORAGE_LINK_MARK_SCHEMES_Supplier]
FOREIGN KEY([Supplier]) REFERENCES [STOR].[T_SUPPLIER] ([SupplierID])
GO

ALTER TABLE [STOR].[T_STORAGE_LINK_MARK_SCHEMES] ADD CONSTRAINT [FK_T_MARK_SCHEMES_T_STORAGE_LINK_MARK_SCHEMES_MarkSchemes]
FOREIGN KEY([MarkSchemes]) REFERENCES [STOR].[T_MARK_SCHEMES] ([Code])
GO

Insert INTO [STOR].[T_STORAGE_LINK_MARK_SCHEMES](
			StorageLinkMarkSchemesID,
			Consumer,
			Supplier,
			MainSchema,
			MarkSchemes)
select NEWID(),
		c.ConsumerID,
		null,
		1,
		c.MarkSchemes
from [STOR].[T_CONSUMER] c 
where c.MarkSchemes is not null

Insert INTO [STOR].[T_STORAGE_LINK_MARK_SCHEMES](
			StorageLinkMarkSchemesID,
			Consumer,
			Supplier,
			MainSchema,
			MarkSchemes)
select NEWID(),
		null,
		s.SupplierID,
		1,
		s.MarkSchemes
from [STOR].[T_SUPPLIER] s 
where s.MarkSchemes is not null

ALTER TABLE [STOR].[T_CONSUMER] DROP CONSTRAINT [FK_T_MARK_SCHEMES_T_CONSUMER_MarkSchemes]
GO

exec dbo.sp_AlterDropColumn '[STOR].[T_CONSUMER]' , 'MarkSchemes', 0
GO

ALTER TABLE [STOR].[T_SUPPLIER] DROP CONSTRAINT [FK_T_MARK_SCHEMES_T_SUPPLIER_MarkSchemes]
GO

exec dbo.sp_AlterDropColumn '[STOR].[T_SUPPLIER]' , 'MarkSchemes', 0
GO
