-- Create the eiaodw schema.

create schema eiaodw;
revoke all on schema eiaodw from public;

-- Populate the eiaodw schema.
create table eiaodw.TestResult_T(
	Probability real,
	SubjectID int4,
	MinuteID int2,
	DateID int2,
	BarrierComputationVersionID int2,
	ResultID int2,
	ETLRunID int2,
	TestRunID int4
);

create table eiaodw.Language_T(
	LanguageID int2,
	Language varchar(100),
	LanguageAbbreviation varchar(5),
	LanguageFamilyID int2,
	LanguageFamily varchar(50),
	LanguageFamilyAbbreviation varchar(2)
);

create table eiaodw.LanguageUsage_T(
	ResourceVersionID int4,
	LanguageID int2
);

create table eiaodw.ResourceMediaTypeUsage_T(
	ResourceVersionID int4,
	MediaTypeID int2
);	

create table eiaodw.ScenarioMediaTypeUsage_T(
	ScenarioID int4,
	MediaTypeID int2
);

create table eiaodw.Subject_T(
	SubjectID int4,
	Line int4,
	Col int4,
	ResourceVersionID int4,
	Doctype varchar(1024),
	Encoding varchar(20),
	ContentType varchar(64),
	ContentLength int4,
	ActualSize int4,
	CopyLocation varchar(4096),
	AuthoringTool varchar(1024),
	TestRunID int4,
	TestRunNumber int4,
	ServerID int2,
	Server varchar(256),
	ServerVersion varchar(256),
	CompleteServerName varchar(1024),
	OperatingSystemFamilyID int2,
	OperatingSystemFamily varchar(20),
	ResourceID int4,
	URL varchar(4096),
	SiteID int2,
	Site varchar(255),
	SiteTitle varchar(1024),
	AddedBy varchar(256),
	NaceID int2,
	NaceCode varchar(32),
	NaceDescription varchar(1024),
	SecondLevelDomainID int2,
	SecondLevelDomain varchar(74),
	TopLevelDomainID int2,
	TopLevelDomain varchar(10),
	NutsLevel3ID int2,
	Nuts3Code varchar(5),
	Nuts3Name varchar(256),
	NutsLevel2ID int2,
	Nuts2Code varchar(4),
	Nuts2Name varchar(256),
	NutsLevel1ID int2,
	Nuts1Code varchar(3),
	Nuts1Name varchar(256),
	CountryID int2,
	Country varchar(128),
	CountryAbbreviation varchar(2),
	EUMember varchar(17),
	LastModifiedTime int2,
	LastModifiedDate int2
);

create table eiaodw.ScenarioCoverage_T(
	ResourceVersionID int4,
	ScenarioID int4
);

create table eiaodw.Scenario_T(
	ScenarioID int4,
	TestRunID int4,
	SiteID int2
);

create table eiaodw.Time_T(
	MinuteID int2,
	Minute varchar(2),
	HourID int2,
	Hour varchar(2)
);

create table eiaodw.Date_T(
	DateID int2,
	DayNumberInMonth int2,
	DayNumberInWeek int2,
	DayName varchar(9),
	Date date,
	MonthID int2,
	MonthNumberInYear int2,
	MonthName varchar(9),
	DaysInMonth int2,
	MonthNumberAfterEIAOStart int2,
	QuarterID int2,
	QuarterName varchar(14),
	QuarterNumberInYear int2,
	YearID int2,
	Year int2,
	WeekID int2,
	WeekNumberInYear int2,
	WeekYearID int2,
	WeekYear int2
);

create table eiaodw.UWEMTest_T(
	UWEMTestID int2,
	UWEMTestName varchar(64),
	UWEMTestTypeID int2,
	UWEMTestType varchar(64)
);

create table eiaodw.Result_T(
	ResultID int2,
	TextualDescription varchar(1024),
	ShortDescription varchar(128),
	ResultTypeID int2,
	ResultType varchar(10)
);

create table eiaodw.BarrierComputationVersion_T(
	BarrierComputationVersionID int2,
	BarrierComputationVersionName varchar(256),
	Version int2,
	FalseNegativeProb real,
	FalsePositiveProb real,
	BarrierComputationID int2,
	BarrierComputationName varchar(252),
	BarrierComputationRelease int2,
	BarrierComputationProducer varchar(256),
	Iterator int2,
	TechniqueID int2,
	Technique varchar(255),
	WCAGMinorID int2,
	WCAGMinor int2,
	Priority int2,
	Checkpoint varchar(2048),
	CheckpointURL varchar(256),
	WCAGMajorID int2,
	WCAGMajor int2,
	Guideline varchar(1024),
	GuidelineURL varchar(256),
	WCAGTypeID int2,
	WCAGType varchar(3),
	TestModeID int2,
	TestMode varchar(255)
);

create table eiaodw.DisabilityGroup_T(
	DisabilityGroupID int2,
	DisabilityGroup varchar(128)
);

create table eiaodw.DisabilityGroupRelevance_Fcui_T(
	DisabilityGroupID int2,
	BarrierComputationID int2,
	BarrierProbability real
);

create table eiaodw.ETLRun_T(
	ETLRunID int2,
	SourceModel varchar(256),
	SourceDB varchar(256),
	TimeUsage int4,
	DateID int2,
	MinuteID int2,
	ETLVersionID int2,
	Major int2,
	Minor int2,
	Build int2
);

create table eiaodw.MediaType_T(
	MediaTypeID int2,
	MediaType varchar(256)
);

create table eiaodw.MimeType_T(
	MimeTypeID int2,
	MimeType varchar(256),
	MimeSubtype varchar(256)
);

create table eiaodw.InclusionType_T(
	InclusionTypeID int2,
	InclusionType varchar(256)
);

create table eiaodw.TechnologyFinding_T(
	SubjectID int4,
	MimeTypeID int2,
	InclusionTypeID int2,
	DateID int2,
	MinuteID int2,
	TestRunID int4
);

-- create views
create view eiaodw.TestResult as select * from eiaodw.TestResult_T;
create view eiaodw.Language as select * from eiaodw.Language_T;
create view eiaodw.LanguageUsage as select * from eiaodw.LanguageUsage_T;
create view eiaodw.ScenarioMediaTypeUsage as select * from eiaodw.ScenarioMediaTypeUsage_T;
create view eiaodw.ResourceMediaTypeUsage as select * from eiaodw.ResourceMediaTypeUsage_T;
create view eiaodw.Subject as select * from eiaodw.Subject_T;
create view eiaodw.ScenarioCoverage as select * from eiaodw.ScenarioCoverage_T;
create view eiaodw.Scenario as select * from eiaodw.Scenario_T;
create view eiaodw.Time as select * from eiaodw.Time_T;
create view eiaodw.Date as select * from eiaodw.Date_T;
create view eiaodw.UWEMTest as select * from eiaodw.UWEMTest_T;
create view eiaodw.Result as select * from eiaodw.Result_T;
create view eiaodw.BarrierComputationVersion as select * from eiaodw.BarrierComputationVersion_T;
create view eiaodw.DisabilityGroup as select * from eiaodw.DisabilityGroup_T;
create view eiaodw.DisabilityGroupRelevance_Fcui as select * from eiaodw.DisabilityGroupRelevance_Fcui_T;
create view eiaodw.ETLRun as select * from eiaodw.ETLRun_T;
create view eiaodw.MediaType as select * from eiaodw.MediaType_T;
create view eiaodw.MimeType as select * from eiaodw.MimeType_T;
create view eiaodw.InclusionType as select * from eiaodw.InclusionType_T;
create view eiaodw.TechnologyFinding as select * from eiaodw.TechnologyFinding_T;

-- Create views
/*
create view eiaodw.TestResult as select
	SubjectID,
	MinuteID,
	DateID,
	BarrierComputationVersionID,
	ResultID
from eiaodw.TestResult_T;

create view eiaodw.Subject as select
	SubjectID,
	Line,
	Col,
	PageVersionID,
	ContentLength,
	ContentType,
	Encoding,
	CopyLocation,
	HTMLVersion,
	TestRunID,
	TestRunNumber,
	ServerID,
	Server,
	ServerVersion,
	CompleteServerName,
	OperatingSystemFamilyID,
	OperatingSystemFamily,
	LanguageID,
	Language,
	LanguageAbbreviation,
	LanguageFamilyID,
	LanguageFamily,
	LanguageFamilyAbbreviation,
	PageID,
	URL,
	SiteID,
	SiteTitle,
	AddedBy,
	DomainID,
	Domain,
	SecondLevelDomainID,
	SecondLevelDomain,
	TopLevelDomainID,
	TopLevelDomain,
	NutsLevel3ID,
	Nuts3Code,
	Nuts3Name,
	NutsLevel2ID,
	Nuts2Code,
	Nuts2Name,
	NutsLevel1ID,
	Nuts1Code,
	Nuts1Name,
	CountryID,
	Country,
	CountryAbbreviation,
	EUMember,
	LastModifiedTime,
	LastModifiedDate
from eiaodw.Subject_T;

create view eiaodw.ScenarioCoverage as select
	SubjectID,
	ScenarioID
from eiaodw.ScenarioCoverage_T;

create view eiaodw.Scenario as select
	ScenarioID,
	ScenarioTypeID,
	ScenarioType
from eiaodw.Scenario_T;

create view eiaodw.SiteCategorisation as select
	SiteID,
	CategoryID,
	CategoryWeight
from eiaodw.SiteCategorisation_T;

create view eiaodw.Category as select
	CategoryID,
	Category,
	SectorID,
	Sector
from eiaodw.Category_T;

create view eiaodw.TestTime as select
	MinuteID,
	Minute,
	HourID,
	Hour
from eiaodw.Time_T;

create view eiaodw.ModificationTime as select
	MinuteID,
	Minute,
	HourID,
	Hour
from eiaodw.Time_T;


create view eiaodw.TestDate as select
	DateID,
	DayNumberInMonth,
	DayNumberInWeek,
	DayName,
	Date,
	MonthID,
	MonthNumberInYear,
	MonthName,
	DaysInMonth,
	MonthNumberAfterEIAOStart,
	QuarterID,
	QuarterName,
	QuarterNumberInYear,
	WeekID,
	WeekNumberInYear,
	YearID,
	Year
from eiaodw.Date_T;

create view eiaodw.ModificationDate as select
	DateID,
	DayNumberInMonth,
	DayNumberInWeek,
	DayName,
	Date,
	MonthID,
	MonthNumberInYear,
	MonthName,
	DaysInMonth,
	MonthNumberAfterEIAOStart,
	QuarterID,
	QuarterName,
	QuarterNumberInYear,
	WeekID,
	WeekNumberInYear,
	YearID,
	Year
from eiaodw.Date_T;

create view eiaodw.UWEMTest as select
	UWEMTestID,
	UWEMTestName,
	UWEMTestTypeID,
	UWEMTestType
from eiaodw.UWEMTest_T;

create view eiaodw.UWEMCoverage as select
	BarrierComputationID,
	UWEMTestID,
	UWEMTestWeight
from eiaodw.UWEMCoverage_T;

create view eiaodw.Result as select
	ResultID,
	TextualDescription,
	ShortDescription,
	ResultTypeID,
	ResultType
from eiaodw.Result_T;

create view eiaodw.BarrierComputationVersion as select
	BarrierComputationVersionID,
	BarrierComputationVersionName,
	Version,
	BarrierComputationID,
	BarrierComputationName,
	BarrierComputationNumber,
	WAMID,
	Producer,
	Iterator,
	WAMName,
	ObservatoryRelease,
	ObservatoryReleaseNumber,
	WCAGMinorID,
	WCAGMinor,
	Priority,
	Checkpoint,
	CheckpointURL,
	WCAGMajorID,
	WCAGMajor,
	Guideline,
	GuidelineURL,
	WCAGTypeID,
	WCAGType
from eiaodw.BarrierComputationVersion_T;

create view eiaodw.DisabilityGroup as select
	DisabilityGroupID,
	DisabilityGroup
from eiaodw.DisabilityGroup_T;

create view eiaodw.DisabilityGroupRelevance_Fcui as select
	DisabilityGroupID,
	BarrierComputationID,
	BarrierProbability
from eiaodw.DisabilityGroupRelevance_Fcui_T;


-- Set permissions
grant usage on schema eiaodw to dwuser;
grant select on testresult, subject, sitecategorisation, category, 
modificationdate, modificationtime, testdate, testtime, 
disabilitygrouprelevance_fcui, disabilitygroup, uwemcoverage, uwemtest,
result, scenario, barriercomputationversion to dwuser;
*/