DROP TABLE ReportTopic IF EXISTS; 	/* Datasource created */
DROP TABLE Recipient IF EXISTS; 	/* Datasource created */
DROP TABLE ReportType IF EXISTS; 	/* Datasource created */
DROP TABLE AgendaTopic IF EXISTS; 	/* TABLE DELETED */
DROP TABLE MeetingTopic IF EXISTS; 	/* Datasource created */
DROP TABLE IssueSubscriber IF EXISTS; 	/* Datasource created */
DROP TABLE TaskSubscriber IF EXISTS; 	/* Datasource created */
DROP TABLE Task IF EXISTS; 		/* Datasource created */
DROP TABLE Participant IF EXISTS; 	/* Datasource created */
DROP TABLE MeetingType IF EXISTS; 	/* Datasource created */
DROP TABLE Stakeholder IF EXISTS; 	/* Datasource created */
DROP TABLE Issue IF EXISTS; 		/* Datasource created */
DROP TABLE IssueLikelihood IF EXISTS; 	/* Datasource created */
DROP TABLE IssueType IF EXISTS; 	/* Datasource created */
DROP TABLE Category IF EXISTS; 		/* Datasource created */
DROP TABLE IssueImpact IF EXISTS; 	/* Datasource created */
DROP TABLE Project IF EXISTS; 		/* Datasource created */
DROP TABLE Associate IF EXISTS; 	/* Datasource created */
DROP TABLE TeamMember IF EXISTS; 	/* Datasource created */
DROP TABLE Organization IF EXISTS; 	/* Datasource created */
DROP TABLE Community IF EXISTS; 	/* Datasource created */
DROP TABLE ReportTemplate IF EXISTS; 	/* Datasource created */
DROP TABLE MinutesTemplate IF EXISTS; 	/* Datasource created */
DROP TABLE AgendaTemplate IF EXISTS; 	/* Datasource created */
DROP TABLE PrintTemplate IF EXISTS; 	/* TABLE DELETED */
DROP TABLE License IF EXISTS; 		/* Datasource created */
DROP TABLE Account IF EXISTS; 		/* Datasource created */

/**********************************/
/* Table Name: Account */
/**********************************/

CREATE TABLE Account(
		AccountID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		AccountNumber VARCHAR(16) NOT NULL,
		AccountName VARCHAR(64) NOT NULL,
		Enabled BOOLEAN DEFAULT TRUE,
		ExpireDate DATE,
		ContactName VARCHAR(64),
		ContactEmail VARCHAR(64),
		ContactPhone VARCHAR(32),
		CreditCardNumber VARCHAR(32),
		CreditCardName VARCHAR(64),
		CreditCardExpMMYY CHAR(5),
		CreditCardCCV CHAR(3),
		Location VARCHAR(64),
		TimeZone VARCHAR(64),
		Language VARCHAR(64),
		NumberFormat VARCHAR(64),
		DateFormat VARCHAR(64),
		Currency VARCHAR(64),
		PurchaseOrderNumber VARCHAR(16),
		PurchaseOrderContact VARCHAR(64),
		PurchaseOrderPhone VARCHAR(32),
		PurchaseOrderPayer VARCHAR(32),
		PurchaseOrderEmail VARCHAR(64),
		PurchaseOrderStartDate DATE,
		PurchaseOrderExpDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_AccountName UNIQUE (AccountName)
);

/**********************************/
/* Table Name: Licenses */
/**********************************/

CREATE TABLE License(
		LicenseID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		LicenseName VARCHAR(16),
		Enabled BOOLEAN DEFAULT TRUE,
		StartDate DATE,
		ExpireDate DATE,
		LicenseType VARCHAR(16) NOT NULL,
		EffectiveDays INT DEFAULT 0,
		UserCount INT DEFAULT 0,
		MaximumGB INT DEFAULT 0,
		PrioritySupport BOOLEAN DEFAULT FALSE,
		Renewable BOOLEAN DEFAULT TRUE,
		MonthlyFee FLOAT NOT NULL,
		QuarterlyFee FLOAT NOT NULL,
		YearlyFee FLOAT NOT NULL,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_LicenseName UNIQUE (LicenseName)
);

/**********************************/
/* Table Name: Agenda Templates */
/**********************************/

CREATE TABLE AgendaTemplate(
		AgendaTemplateID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT, /* will be null for public templates */
		TemplateName VARCHAR(64),
		TemplateTitle VARCHAR(64),
		FileName VARCHAR(128),
		Enabled BOOLEAN DEFAULT TRUE,
		StartDate DATE,
		ExpireDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_AgendaTitle UNIQUE (CommunityID, TemplateName)
);

/**********************************/
/* Table Name: Minutes Templates */
/**********************************/

CREATE TABLE MinutesTemplate(
		MinutesTemplateID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT, /* will be null for public templates */
		TemplateName VARCHAR(64),
		TemplateTitle VARCHAR(64),
		FileName VARCHAR(128),
		Enabled BOOLEAN DEFAULT TRUE,
		StartDate DATE,
		ExpireDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_MinutesTitle UNIQUE (CommunityID, TemplateName)
);
/**********************************/
/* Table Name: Report Templates */
/**********************************/

CREATE TABLE ReportTemplate(
		ReportTemplateID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT, /* will be null for public templates */
		TemplateName VARCHAR(64),
		TemplateTitle VARCHAR(64),
		FileName VARCHAR(128),
		Enabled BOOLEAN DEFAULT TRUE,
		StartDate DATE,
		ExpireDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_ReportTitle UNIQUE (CommunityID, TemplateName)
);

/**********************************/
/* Table Name: Community */
/**********************************/

CREATE TABLE Community(
		CommunityID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		AccountID BIGINT NOT NULL,
		CommunityName VARCHAR(64) NOT NULL,
		LicenseID BIGINT NOT NULL,
		Enabled BOOLEAN DEFAULT TRUE,
		ExpireDate DATE,
		ContactName VARCHAR(64),
		ContactEmail VARCHAR(64),
		ContactPhone VARCHAR(32),
		Location VARCHAR(64),
		TimeZone VARCHAR(64),
		Language VARCHAR(64),
		NumberFormat VARCHAR(64),
		DateFormat VARCHAR(64),
		AgendaTemplateID BIGINT,
		AgendaPrintOptions CHAR(64),
		MinutesTemplateID BIGINT,
		MinutesPrintOptions CHAR(64),
		ReportTemplateID BIGINT,
		ReportPrintOptions CHAR(64),
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_CommunityName UNIQUE (CommunityName),
	CONSTRAINT IDX_Community_License FOREIGN KEY (LicenseID) REFERENCES License (LicenseID),
	CONSTRAINT IDX_Community_Account FOREIGN KEY (AccountID) REFERENCES Account (AccountID),
	CONSTRAINT IDX_Community_AgendaTemplate FOREIGN KEY (AgendaTemplateID) REFERENCES AgendaTemplate (AgendaTemplateID),
	CONSTRAINT IDX_Community_MinutesTemplate FOREIGN KEY (MinutesTemplateID) REFERENCES MinutesTemplate (MinutesTemplateID),
	CONSTRAINT IDX_Community_ReportTemplate FOREIGN KEY (ReportTemplateID) REFERENCES ReportTemplate (ReportTemplateID)
);

/**********************************/
/* Table Name: Organization */
/**********************************/

CREATE TABLE Organization(
		OrganizationID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		OrganizationName VARCHAR(64) NOT NULL,
		Internal BOOLEAN DEFAULT FALSE,
		ContactName VARCHAR(64),
		ContactEmail VARCHAR(64),
		ContactPhone VARCHAR(64),
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_OrganizationName UNIQUE (CommunityID, OrganizationName),
	CONSTRAINT IDX_Organization_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Team Members */
/**********************************/

CREATE TABLE TeamMember(
		TeamMemberID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		TeamMemberName VARCHAR(64) NOT NULL,
		Enabled BOOLEAN DEFAULT TRUE,
		TeamMemberEmail VARCHAR(64),
		EmailConfirmed BOOLEAN DEFAULT FALSE,
		OrganizationID BIGINT,
		BasePhone VARCHAR(64),
		MobilePhone VARCHAR(64),
		BaseLocation VARCHAR(64),
		BaseTimeZone VARCHAR(64),
		BaseLanguage VARCHAR(64),
		BaseNumberFormat VARCHAR(64),
		BaseDateFormat VARCHAR(64),
		CommunityAdmin BOOLEAN,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_TeamMemberName UNIQUE (CommunityID, TeamMemberName),
	CONSTRAINT IDX_TeamMemberEmail UNIQUE (CommunityID, TeamMemberEmail),
	CONSTRAINT IDX_TeamMemberEmail_1 UNIQUE (TeamMemberEmail), /* Initially, an Email may exist in only one Community*/
	CONSTRAINT IDX_TeamMember_Organization FOREIGN KEY (OrganizationID) REFERENCES Organization (OrganizationID),
	CONSTRAINT IDX_TeamMember_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Associates */
/**********************************/

CREATE TABLE Associate(
		AssociateID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		AssociateName VARCHAR(64) NOT NULL,
		Enabled BOOLEAN DEFAULT TRUE,
		AssociateEmail VARCHAR(64),
		EmailConfirmed BOOLEAN DEFAULT FALSE,
		BasePhone VARCHAR(64),
		MobilePhone VARCHAR(64),
		BaseLocation VARCHAR(64),
		BaseTimeZone VARCHAR(64),
		BaseLanguage VARCHAR(64),
		BaseNumberFormat VARCHAR(64),
		BaseDateFormat VARCHAR(64),
		CompanyAdmin BOOLEAN,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_AssociateName UNIQUE (AssociateName),
	CONSTRAINT IDX_AssociateEmail UNIQUE (AssociateEmail)
);

/**********************************/
/* Table Name: Projects */
/**********************************/

CREATE TABLE Project(
		ProjectID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		ProjectNumber VARCHAR(16) NOT NULL,
		ProjectTitle VARCHAR(64),
		TaskOrder VARCHAR(32),
		Inactive BOOLEAN DEFAULT FALSE,
		OwningProjectID BIGINT,
		ProjectManagerID BIGINT NOT NULL,
		LastIssueNumber INT DEFAULT 0,
		LastTaskNumber INT DEFAULT 0,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	

	CONSTRAINT IDX_ProjectNumber UNIQUE (CommunityID, ProjectNumber),

	CONSTRAINT IDX_ProjectTitle UNIQUE (CommunityID, ProjectTitle),
	CONSTRAINT IDX_TaskOrder UNIQUE (CommunityID, TaskOrder),
	CONSTRAINT IDX_Project_ProjectManager FOREIGN KEY (ProjectManagerID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Project_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Issue Impacts */
/**********************************/

CREATE TABLE IssueImpact(
		ImpactID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		ImpactNumber SMALLINT NOT NULL,
		ImpactTitle VARCHAR(16) NOT NULL,
		DefaultValue BOOLEAN DEFAULT TRUE,
		Desc VARCHAR(64),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_ImpactNumber UNIQUE (CommunityID, ImpactNumber),

	CONSTRAINT IDX_ImpactTitle UNIQUE (CommunityID, ImpactTitle),
	CONSTRAINT IDX_IssueImpacts_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Categories */
/**********************************/

CREATE TABLE Category(
		CategoryID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		CategoryName VARCHAR(16) NOT NULL,
		CategoryTitle VARCHAR(64),
		Desc VARCHAR(128),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_CategoryName UNIQUE (CommunityID, CategoryName),

	CONSTRAINT IDX_Categories_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Issue Likelihoods */
/**********************************/

CREATE TABLE IssueLikelihood(
		LikelihoodID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		CommunityID BIGINT NOT NULL,
		LikelihoodNumber SMALLINT NOT NULL,
		Probability FLOAT NOT NULL,
		LikelihoodTitle VARCHAR(64),
		Desc VARCHAR(128),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_LikelihoodNumber UNIQUE (CommunityID, LikelihoodNumber),	
	CONSTRAINT IDX_LikelihoodTitle UNIQUE (CommunityID, LikelihoodTitle),		
	CONSTRAINT IDX_IssueLikelihood_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Issue Types */
/**********************************/

CREATE TABLE IssueType(
		IssueTypeID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		IssueTypeTitle VARCHAR(16),
		Desc VARCHAR(255),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			
	CONSTRAINT IDX_IssueTypeTitle UNIQUE (IssueTypeTitle)
);

/**********************************/
/* Table Name: Issues */
/**********************************/

CREATE TABLE Issue(
		IssueID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		IssueNumber INT NOT NULL,
		IssueTitle VARCHAR(64) NOT NULL,
		CategoryID BIGINT,
		ImpactID BIGINT NOT NULL,
		LikelihoodID BIGINT,
		OwnerID BIGINT,
		Status VARCHAR(16),
		DueDate DATE,
		ClosedDate DATE,
		IssueTypeID BIGINT NOT NULL,
		OriginatorID BIGINT,
		OriginalDueDate DATE,
		Resolution LONGVARCHAR,
		Private BOOLEAN DEFAULT FALSE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_IssueNumber UNIQUE (ProjectID, IssueNumber),
	CONSTRAINT IDX_IssueTitle UNIQUE (ProjectID, IssueTitle),
	CONSTRAINT IDX_Issue_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_Issue_Category FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
	CONSTRAINT IDX_Issue_Impact FOREIGN KEY (ImpactID) REFERENCES IssueImpact (ImpactID),
	CONSTRAINT IDX_Issue_Likelihood FOREIGN KEY (LikelihoodID) REFERENCES IssueLikelihood (LikelihoodID),
	CONSTRAINT IDX_Issue_IssueType FOREIGN KEY (IssueTypeID) REFERENCES IssueType (IssueTypeID),
	CONSTRAINT IDX_Issue_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Stakeholders */
/**********************************/

CREATE TABLE Stakeholder(
		TeamMemberID BIGINT NOT NULL,		
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		Role VARCHAR(64) NOT NULL,
		ProjectAdmin BOOLEAN,
		Desc VARCHAR(128),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_Stakeholder_PK PRIMARY KEY (ProjectID, TeamMemberID, Role),
	CONSTRAINT IDX_Stakeholder_TeamMember FOREIGN KEY (TeamMemberID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Stakeholder_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_Stakeholder_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);


/**********************************/
/* Table Name: Meeting Types */
/**********************************/

CREATE TABLE MeetingType(
		MeetingTypeID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		MeetingName VARCHAR(64) NOT NULL,
		Purpose VARCHAR(255),
		Frequency VARCHAR(255),
		Location VARCHAR(255),
		TeleconferenceInfo VARCHAR(255),
		LeaderID BIGINT,
		AgendaTemplateID BIGINT,
		AgendaPrintOptions CHAR(64),
		MinutesTemplateID BIGINT,
		MinutesPrintOptions CHAR(64),
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_MeetingName UNIQUE (ProjectID, MeetingName),
	CONSTRAINT IDX_MeetingType_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_MeetingType_AgendaTemplate FOREIGN KEY (AgendaTemplateID) REFERENCES AgendaTemplate (AgendaTemplateID),
	CONSTRAINT IDX_MeetingType_MinutesTemplate FOREIGN KEY (MinutesTemplateID) REFERENCES MinutesTemplate (MinutesTemplateID),
	CONSTRAINT IDX_MeetingType_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Participants */
/**********************************/

CREATE TABLE Participant(
		MeetingTypeID BIGINT NOT NULL,
		TeamMemberID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		Required BOOLEAN DEFAULT TRUE,
		Desc VARCHAR(128),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_Participant_PK PRIMARY KEY (MeetingTypeID, TeamMemberID),
	CONSTRAINT IDX_Participant_Meeting FOREIGN KEY (MeetingTypeID) REFERENCES MeetingType (MeetingTypeID),
	CONSTRAINT IDX_Participant_TeamMember FOREIGN KEY (TeamMemberID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Participant_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Tasks */
/**********************************/

CREATE TABLE Task(
		TaskID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		OwnerID BIGINT,
		IssueID BIGINT NOT NULL,
		TaskNumber INT,
		Status VARCHAR(16),
		TaskTitle VARCHAR(64),
		DueDate DATE,
		CompletedDate DATE,
		OriginalDueDate DATE,
		StartDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_TaskNumber UNIQUE (ProjectID, TaskNumber),
	CONSTRAINT IDX_Task_Owner FOREIGN KEY (OwnerID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Task_Issue FOREIGN KEY (IssueID) REFERENCES Issue (IssueID),
	CONSTRAINT IDX_Task_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_Task_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Task Subscribers */
/**********************************/

CREATE TABLE TaskSubscriber(
		TaskID BIGINT NOT NULL,
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		TeamMemberID BIGINT NOT NULL,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_TaskSubscriber_PK PRIMARY KEY (TaskID, TeamMemberID),
	CONSTRAINT IDX_TaskSubscriber_Task FOREIGN KEY (TaskID) REFERENCES Task (TaskID),
	CONSTRAINT IDX_TaskSubscriber_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_TaskSubscriber_TeamMember FOREIGN KEY (TeamMemberID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_TaskSubscriber_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Issue Subscribers */
/**********************************/

CREATE TABLE IssueSubscriber(
		IssueID BIGINT NOT NULL,
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		TeamMemberID BIGINT NOT NULL,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_IssueSubscriber_PK PRIMARY KEY (IssueID, TeamMemberID),
	CONSTRAINT IDX_IssueSubscriber_Issue FOREIGN KEY (IssueID) REFERENCES Issue (IssueID),
	CONSTRAINT IDX_IssueSubscriber_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_IssueSubscriber_TeamMember FOREIGN KEY (TeamMemberID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_IssueSubscriber_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Meeting Topics */
/**********************************/

CREATE TABLE MeetingTopic(
		MeetingTopicID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		MeetingTypeID BIGINT NOT NULL,
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		TopicNumber INT NOT NULL,
		SessionType VARCHAR(64),
		LeaderID BIGINT,
		Title VARCHAR(64),
		ExpectedResults VARCHAR(255),
		SearchEnabled BOOLEAN DEFAULT FALSE,
		IssueTypeID BIGINT,
		CategoryID BIGINT,
		ImpactID BIGINT,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_MeetingTopicNumber UNIQUE (MeetingTypeID, TopicNumber),
	CONSTRAINT IDX_MeetingTopic_Meeting FOREIGN KEY (MeetingTypeID) REFERENCES MeetingType (MeetingTypeID),
	CONSTRAINT IDX_MeetingTopic_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_MeetingTopic_Leader FOREIGN KEY (LeaderID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_MeetingTopic_Category FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
	CONSTRAINT IDX_MeetingTopic_Impact FOREIGN KEY (ImpactID) REFERENCES IssueImpact (ImpactID),
	CONSTRAINT IDX_MeetingTopic_IssueType FOREIGN KEY (IssueTypeID) REFERENCES IssueType (IssueTypeID),
	CONSTRAINT IDX_MeetingTopic_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Report Types */
/**********************************/

CREATE TABLE ReportType(
		ReportTypeID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		ReportName VARCHAR(64),
		Purpose VARCHAR(64),
		Frequency VARCHAR(64),
		AuthorID BIGINT,
		ReportTemplateID BIGINT,
		ReportPrintOptions CHAR(64),
		Desc VARCHAR(256),
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_ReportName UNIQUE (ProjectID, ReportName),
	CONSTRAINT IDX_ReportType_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_ReportType_Leader FOREIGN KEY (AuthorID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_ReportType_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID),
	CONSTRAINT IDX_ReportType_Report FOREIGN KEY (ReportTemplateID) REFERENCES ReportTemplate (ReportTemplateID)
);

/**********************************/
/* Table Name: Recipients */
/**********************************/

CREATE TABLE Recipient(		
		ReportTypeID BIGINT NOT NULL,
		TeamMemberID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		Direct BOOLEAN DEFAULT TRUE,
		Desc VARCHAR(128),
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT IDX_Recipient_PK PRIMARY KEY (ReportTypeID, TeamMemberID),
	CONSTRAINT IDX_Recipient_Report FOREIGN KEY (ReportTypeID) REFERENCES ReportType (ReportTypeID),
	CONSTRAINT IDX_Recipient_TeamMember FOREIGN KEY (TeamMemberID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Recipient_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

/**********************************/
/* Table Name: Report Topics */
/**********************************/

CREATE TABLE ReportTopic(
		ReportTopicID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ReportTypeID BIGINT NOT NULL,
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		TopicNumber INT NOT NULL,
		TopicTitle VARCHAR(64),
		SearchEnabled BOOLEAN,
		IssueTypeID BIGINT,
		CategoryID BIGINT,
		ImpactID BIGINT,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_ReportTopicNumber UNIQUE (ReportTypeID, TopicNumber),
	CONSTRAINT IDX_ReportTopic_Report FOREIGN KEY (ReportTypeID) REFERENCES ReportType (ReportTypeID),
	CONSTRAINT IDX_ReportTopic_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_ReportTopic_Category FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
	CONSTRAINT IDX_ReportTopic_Impact FOREIGN KEY (ImpactID) REFERENCES IssueImpact (ImpactID),
	CONSTRAINT IDX_ReportTopic_IssueType FOREIGN KEY (IssueTypeID) REFERENCES IssueType (IssueTypeID),
	CONSTRAINT IDX_ReportTopic_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);