CREATE TABLE [AlignmentClassic].[Alignment]  ( 
    [AlnID]           	int NOT NULL,
    [SeqTypeID]       	tinyint NOT NULL,
    [AlignmentName]   	varchar(max) NULL,
    [ParentAlnID]     	int NULL,
    [NextColumnNumber]	int NOT NULL,
    CONSTRAINT [PK_Alignment] PRIMARY KEY([AlnID])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[Alignment]
    ADD CONSTRAINT [FK_Alignment_SequenceType]
	FOREIGN KEY([SeqTypeID])
	REFERENCES [dbo].[SequenceType]([SeqTypeID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[Alignment]
    ADD CONSTRAINT [FK_Alignment_Alignment]
	FOREIGN KEY([ParentAlnID])
	REFERENCES [AlignmentClassic].[Alignment]([AlnID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
-----------
CREATE TABLE [AlignmentClassic].[AlignmentColumn]  ( 
    [AlnID]        	int NOT NULL,
    [ColumnNumber] 	int NOT NULL,
    [ColumnOrdinal]	int NOT NULL,
    CONSTRAINT [PK_AlignmentColumn] PRIMARY KEY([AlnID],[ColumnNumber])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[AlignmentColumn]
    ADD CONSTRAINT [FK_AlignmentColumn_Alignment]
	FOREIGN KEY([AlnID])
	REFERENCES [AlignmentClassic].[Alignment]([AlnID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
---------

CREATE TABLE [AlignmentClassic].[AlignmentMissingColumns]  ( 
    [SeqID]       	int NOT NULL,
    [AlnID]       	int NOT NULL,
    [ColumnNumber]	int NOT NULL,
    CONSTRAINT [PK_AlignmentMissingColumns] PRIMARY KEY([SeqID],[AlnID],[ColumnNumber])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[AlignmentMissingColumns]
    ADD CONSTRAINT [FK_AlignmentMissingColumns_AlnSequence]
	FOREIGN KEY([AlnID], [SeqID])
	REFERENCES [AlignmentClassic].[AlnSequence]([AlnID], [SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[AlignmentMissingColumns]
    ADD CONSTRAINT [FK_AlignmentMissingColumns_AlignmentColumn]
	FOREIGN KEY([AlnID], [ColumnNumber])
	REFERENCES [AlignmentClassic].[AlignmentColumn]([AlnID], [ColumnNumber])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
---
CREATE TABLE [AlignmentClassic].[AlnSequence]  ( 
    [AlnID]              	int NOT NULL,
    [SeqID]              	int NOT NULL,
    [FirstNTColumnNumber]	int NOT NULL,
    [LastNTColumnNumber] 	int NOT NULL,
    CONSTRAINT [PK_AlnSequence] PRIMARY KEY([AlnID],[SeqID])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[AlnSequence]
    ADD CONSTRAINT [FK_AlnSequence_SequenceMain]
	FOREIGN KEY([SeqID])
	REFERENCES [SecureMetadata].[SequenceMain]([SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[AlnSequence]
    ADD CONSTRAINT [FK_AlnSequence_Alignment]
	FOREIGN KEY([AlnID])
	REFERENCES [AlignmentClassic].[Alignment]([AlnID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
---
CREATE TABLE [AlignmentClassic].[Sequence]  ( 
    [SeqID]           	int NOT NULL,
    [AlnID]           	int NOT NULL,
    [ColumnNumber]    	int NOT NULL,
    [Base]            	char(1) NOT NULL,
    [NucleotideNumber]	int NOT NULL,
    CONSTRAINT [PK_CI_Sequence_AlnIDColIDSeqID] PRIMARY KEY([AlnID],[ColumnNumber],[SeqID])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[Sequence]
    ADD CONSTRAINT [FK_Sequence_AlnSequence]
	FOREIGN KEY([AlnID], [SeqID])
	REFERENCES [AlignmentClassic].[AlnSequence]([AlnID], [SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[Sequence]
    ADD CONSTRAINT [FK_Sequence_AlignmentColumn]
	FOREIGN KEY([AlnID], [ColumnNumber])
	REFERENCES [AlignmentClassic].[AlignmentColumn]([AlnID], [ColumnNumber])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
----
CREATE TABLE [AlignmentClassic].[SequenceRegions]  ( 
    [AlnID]    	int NOT NULL,
    [SeqID]    	int NOT NULL,
    [Start]    	int NOT NULL,
    [Stop]     	int NOT NULL,
    [Region]   	int NOT NULL,
    [SubRegion]	char(1) NOT NULL,
    CONSTRAINT [PK_SequenceRegions_AlnIDSeqIDStartStop] PRIMARY KEY([AlnID],[SeqID],[Start],[Stop])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[SequenceRegions]
    ADD CONSTRAINT [FK_SequenceRegions_Sequence_Stop]
	FOREIGN KEY([AlnID], [Stop], [SeqID])
	REFERENCES [AlignmentClassic].[Sequence]([AlnID], [ColumnNumber], [SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[SequenceRegions]
    ADD CONSTRAINT [FK_SequenceRegions_Sequence_Start]
	FOREIGN KEY([AlnID], [Start], [SeqID])
	REFERENCES [AlignmentClassic].[Sequence]([AlnID], [ColumnNumber], [SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
----
CREATE TABLE [AlignmentClassic].[StagingLabels]  ( 
    [NucNbrStart]      	int NULL,
    [NucNbrEnd]        	int NULL,
    [StructureType]    	varchar(20) NULL,
    [StructureID]      	int NOT NULL,
    [ParentStructureID]	int NULL,
    [LegacyName]       	varchar(24) NULL,
    [OmitFlag]         	int NULL,
    CONSTRAINT [PK_StagingLabels] PRIMARY KEY([StructureID])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[StagingLabels]
    ADD CONSTRAINT [FK_StagingLabels_StagingLabels]
	FOREIGN KEY([ParentStructureID])
	REFERENCES [AlignmentClassic].[StagingLabels]([StructureID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
----
CREATE TABLE [AlignmentClassic].[StructureExtent]  ( 
    [AlnID]        	int NOT NULL,
    [SeqID]        	int NOT NULL,
    [StructID]     	int NOT NULL,
    [ExtentOrdinal]	int NOT NULL,
    [NucNbrStart]  	int NOT NULL,
    [NucNbrEnd]    	int NOT NULL,
    [StructTypeID] 	int NOT NULL,
    CONSTRAINT [PK__StructureExtent__3F466844] PRIMARY KEY([AlnID],[SeqID],[StructID],[ExtentOrdinal])
)
ON [PRIMARY]
GO
ALTER TABLE [AlignmentClassic].[StructureExtent]
    ADD CONSTRAINT [FK_StructureExtent_StructureTypes]
	FOREIGN KEY([StructTypeID])
	REFERENCES [AlignmentClassic].[StructureTypes]([StructureTypeID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[StructureExtent]
    ADD CONSTRAINT [FK_StructureExtent_StagingLabels]
	FOREIGN KEY([StructID])
	REFERENCES [AlignmentClassic].[StagingLabels]([StructureID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [AlignmentClassic].[StructureExtent]
    ADD CONSTRAINT [FK_StructureExtent_AlnSequence]
	FOREIGN KEY([AlnID], [SeqID])
	REFERENCES [AlignmentClassic].[AlnSequence]([AlnID], [SeqID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
------
CREATE TABLE [AlignmentClassic].[StructureTypes]  ( 
    [StructureTypeID]	int NOT NULL,
    [StructureType]  	varchar(20) NULL,
    CONSTRAINT [PK__StructureTypes__0DAF0CB0] PRIMARY KEY([StructureTypeID])
)
ON [PRIMARY]
GO
----
CREATE TABLE [SecureMetadata].[SequenceMain]  ( 
    [SeqID]          	int NOT NULL,
    [TaxID]          	int NOT NULL,
    [LocationID]     	tinyint NOT NULL,
    [SeqTypeID]      	tinyint NOT NULL,
    [SeqLength]      	int NOT NULL,
    [PercentComplete]	tinyint NOT NULL,
    [Source]         	varchar(8000) NULL,
    [Comment]        	varchar(8000) NULL,
    [VisibilityLevel]	tinyint NOT NULL,
    CONSTRAINT [PK_SequenceMain] PRIMARY KEY NONCLUSTERED([SeqID])
)
ON [PRIMARY]
GO
ALTER TABLE [SecureMetadata].[SequenceMain]
    ADD CONSTRAINT [FK_SequenceMain_Taxonomy]
	FOREIGN KEY([TaxID])
	REFERENCES [Taxonomy].[Taxonomy]([TaxID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [SecureMetadata].[SequenceMain]
    ADD CONSTRAINT [FK_SequenceMain_SequenceType]
	FOREIGN KEY([SeqTypeID])
	REFERENCES [dbo].[SequenceType]([SeqTypeID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [SecureMetadata].[SequenceMain]
    ADD CONSTRAINT [FK_SequenceMain_SecurityVisibility]
	FOREIGN KEY([VisibilityLevel])
	REFERENCES [SecureMetadata].[SecurityVisibility]([VisibilityLevel])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
ALTER TABLE [SecureMetadata].[SequenceMain]
    ADD CONSTRAINT [FK_SequenceMain_CellLocationInfo]
	FOREIGN KEY([LocationID])
	REFERENCES [dbo].[CellLocationInfo]([LocationID])
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO
----
CREATE TABLE [dbo].[SequenceType]  ( 
    [SeqTypeID]   	tinyint NOT NULL,
    [MoleculeType]	varchar(100) NOT NULL,
    [GeneType]    	varchar(100) NOT NULL,
    [GeneName]    	varchar(100) NOT NULL,
    CONSTRAINT [PK_SequenceType] PRIMARY KEY([SeqTypeID])
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[SequenceType]
    ADD CONSTRAINT [UN_SequenceType]
	UNIQUE ([SeqTypeID], [GeneName])
GO