-- Drop the eiaodw schema and its data
DROP SCHEMA eiaodw CASCADE;

-- Recreate the eiaodw schema
\i EIAODWR20Schema.sql
\i StoredProcedures.sql

-- Load eiaodw tables from datastaging tables
INSERT INTO eiaodw.Language_T
	(LanguageID,
	Language,
	LanguageAbbreviation,
	LanguageFamilyID,
	LanguageFamily,
	LanguageFamilyAbbreviation)
SELECT
	datastaging.Language.LanguageID,
	datastaging.Language.Language,
	datastaging.Language.LanguageAbbreviation,
	datastaging.LanguageFamily.LanguageFamilyID,
	datastaging.LanguageFamily.LanguageFamily,
	datastaging.LanguageFamily.LanguageFamilyAbbreviation
FROM
	datastaging.Language NATURAL JOIN
	datastaging.LanguageFamily
;

INSERT INTO eiaodw.LanguageUsage_T
	(ResourceVersionID,
	LanguageID)
SELECT
	datastaging.LanguageUsage.ResourceVersionID,
	datastaging.LanguageUsage.LanguageID
FROM
	datastaging.LanguageUsage
;

INSERT INTO eiaodw.MediaType_T
	(MediaTypeID,
	MediaType)
SELECT
	datastaging.MediaType.MediaTypeID,
	datastaging.MediaType.MediaType
FROM
	datastaging.MediaType
;

INSERT INTO eiaodw.ResourceMediaTypeUsage_T
	(ResourceVersionID,
	MediaTypeID)
SELECT
	datastaging.ResourceMediaTypeUsage.ResourceVersionID,
	datastaging.ResourceMediaTypeUsage.MediaTypeID
FROM
	datastaging.ResourceMediaTypeUsage
;

INSERT INTO eiaodw.ScenarioMediaTypeUsage_T
	(ScenarioID,
	MediaTypeID)
SELECT
	datastaging.ScenarioMediaTypeUsage.ScenarioID,
	datastaging.ScenarioMediaTypeUsage.MediaTypeID
FROM
	datastaging.ScenarioMediaTypeUsage
;


INSERT INTO eiaodw.BarrierComputationVersion_T
	(BarrierComputationVersionId,
	BarrierComputationVersionName,
	Version,
	FalseNegativeProb,
	FalsePositiveProb,
	BarrierComputationID,
	BarrierComputationName,
	BarrierComputationRelease,
	BarrierComputationProducer,
	Iterator,
	TechniqueID,
	Technique,
	WCAGMinorID,
	WCAGMinor,
	Priority,
	Checkpoint,
	CheckpointURL,
	WCAGMajorID,
	WCAGMajor,
	GuideLine,
	GuideLineURL,
	WCAGTypeID,
	WCAGType,
	TestModeID,
	TestMode)
SELECT
	datastaging.BarrierComputationVersion.BarrierComputationVersionID,
	datastaging.BarrierComputationVersion.BarrierComputationVersionName,
	datastaging.BarrierComputationVersion.Version,
	datastaging.BarrierComputationVersion.FalseNegativeProb,
	datastaging.BarrierComputationVersion.FalsePositiveProb,
	datastaging.BarrierComputationVersion.BarrierComputationID,
	datastaging.BarrierComputation.BarrierComputationName,
	datastaging.BarrierComputation.BarrierComputationRelease,
	datastaging.BarrierComputation.BarrierComputationProducer,
	datastaging.BarrierComputation.Iterator,
	datastaging.BarrierComputation.TechniqueID,
	datastaging.Technique.Technique,
	datastaging.BarrierComputation.WCAGMinorID,
	datastaging.WCAGMinor.WCAGMinor,
	datastaging.WCAGMinor.Priority,
	datastaging.WCAGMinor.Checkpoint,
	datastaging.WCAGMinor.CheckpointURL,
	datastaging.WCAGMinor.WCAGMajorID,
	datastaging.WCAGMajor.WCAGMajor,
	datastaging.WCAGMajor.GuideLine,
	datastaging.WCAGMajor.GuideLineURL,
	datastaging.WCAGMajor.WCAGTypeID,
	datastaging.WCAGType.WCAGType,
	datastaging.BarrierComputation.TestModeID,
	datastaging.TestMode.Testmode
FROM
	datastaging.BarrierComputationVersion NATURAL JOIN
	datastaging.BarrierComputation NATURAL JOIN
	datastaging.Technique NATURAL JOIN
	datastaging.WCAGMinor NATURAL JOIN
	datastaging.WCAGMajor NATURAL JOIN
	datastaging.WCAGType NATURAL JOIN
	datastaging.TestMode
;

INSERT INTO eiaodw.Date_T
	(DateID,
	DayNumberInMonth,
	DayNumberInWeek,
	DayName,
	Date,
	MonthID,
	MonthNumberInYear,
	MonthName,
	DaysInMonth,
	MonthNumberAfterEIAOStart,
	QuarterID,
	QuarterName,
	QuarterNumberInYear,
	YearID,
	Year,
	WeekID,
	WeekNumberInYear,
	WeekYearID,
	WeekYear)
SELECT
	datastaging.Date.DateID,
	datastaging.Date.DayNumberInMonth,
	datastaging.Date.DayNumberInWeek,
	datastaging.Date.DayName,
	datastaging.Date.Date,
	datastaging.Date.MonthID,
	datastaging.Month.MonthNumberInYear,
	datastaging.Month.MonthName,
	datastaging.Month.DaysInMonth,
	datastaging.Month.MonthNumberAfterEIAOStart,
	datastaging.Month.QuarterID,
	datastaging.Quarter.QuarterName,
	datastaging.Quarter.QuarterNumberInYear,
	datastaging.Quarter.YearID,
	QuarterYear.Year,
	datastaging.Date.WeekID,
	datastaging.Week.WeekNumberInYear,
	datastaging.Week.YearID,
	WeekYear.Year
FROM
	datastaging.Date NATURAL JOIN
	datastaging.Month NATURAL JOIN
	datastaging.Quarter JOIN
	datastaging.Week ON (datastaging.Date.WeekID = datastaging.Week.WeekID) JOIN
	datastaging.Year AS QuarterYear ON (datastaging.Quarter.YearID = QuarterYear.YearID) JOIN
	datastaging.Year AS WeekYear ON (datastaging.Week.YearID = WeekYear.YearID)
;

INSERT INTO eiaodw.DisabilityGroupRelevance_Fcui_T
	(DisabilityGroupID,
	BarrierComputationID,
	BarrierProbability)
SELECT
	datastaging.DisabilityGroupRelevance_Fcui.DisabilityGroupID,
	datastaging.DisabilityGroupRelevance_Fcui.BarrierComputationID,
	datastaging.DisabilityGroupRelevance_Fcui.BarrierProbability
FROM
	datastaging.DisabilityGroupRelevance_Fcui
;

INSERT INTO eiaodw.DisabilityGroup_T
	(DisabilityGroupID,
	DisabilityGroup)
SELECT
	datastaging.DisabilityGroup.DisabilityGroupID,
	datastaging.DisabilityGroup.DisabilityGroup
FROM
	datastaging.DisabilityGroup
;

INSERT INTO eiaodw.Result_T
	(ResultID,
	TextualDescription,
	ShortDescription,
	ResultTypeID,
	ResultType)
SELECT
	datastaging.Result.ResultID,
	datastaging.Result.TextualDescription,
	datastaging.Result.ShortDescription,
	datastaging.Result.ResultTypeID,
	datastaging.ResultType.ResultType
FROM
	datastaging.Result NATURAL JOIN
	datastaging.ResultType
;

INSERT INTO eiaodw.ScenarioCoverage_T
	(ResourceVersionID,
	ScenarioID)
SELECT
	datastaging.ScenarioCoverage.ResourceVersionID,
	datastaging.ScenarioCoverage.ScenarioID
FROM
	datastaging.ScenarioCoverage
;

INSERT INTO eiaodw.Scenario_T
	(ScenarioID,
	SiteID,
	TestRunID)
SELECT
	datastaging.Scenario.ScenarioID,
	datastaging.Scenario.SiteID,
	datastaging.Scenario.TestRunID
FROM
	datastaging.Scenario
;

INSERT INTO eiaodw.Subject_T
	(SubjectID,
	Line,
	Col,
	ResourceVersionID,
	Doctype,
	Encoding,
	ContentType,
	ContentLength,
	ActualSize,
	CopyLocation,
	AuthoringTool,
	TestRunID,
	TestRunNumber,
	ServerID,
	Server,
	ServerVersion,
	CompleteServerName,
	OperatingSystemFamilyID,
	OperatingSystemFamily,
	ResourceID,
	URL,
	SiteID,
	Site,
	SiteTitle,
	AddedBy,
	NaceID,
	NaceCode,
	NaceDescription,
	SecondLevelDomainID,
	SecondLevelDomain,
        TopLevelDomainID,
        TopLevelDomain,
        NutsLevel3ID,
        Nuts3Code,
        Nuts3Name,
        NutsLevel2ID,
        Nuts2Code,
        Nuts2Name,
        NutsLevel1ID,
        Nuts1Code,
        Nuts1Name,
        CountryID,
        Country,
        CountryAbbreviation,
        EUMember,
        LastModifiedTime,
        LastModifiedDate)
SELECT
	datastaging.Subject.SubjectID,
	datastaging.Subject.Line,
	datastaging.Subject.Col,
	datastaging.Subject.ResourceVersionID,
	datastaging.ResourceVersion.Doctype,
	datastaging.ResourceVersion.Encoding,
	datastaging.ResourceVersion.ContentType,
	datastaging.ResourceVersion.ContentLength,
	datastaging.ResourceVersion.ActualSize,
	datastaging.ResourceVersion.CopyLocation,
	datastaging.ResourceVersion.AuthoringTool,
	datastaging.ResourceVersion.TestRunID,
	datastaging.TestRun.TestRunNumber,
	datastaging.ResourceVersion.ServerID,
	datastaging.Server.Server,
	datastaging.Server.ServerVersion,
	datastaging.Server.CompleteServerName,
	datastaging.Server.OperatingSystemFamilyID,
	datastaging.OperatingSystemFamily.OperatingSystemFamily,
	datastaging.ResourceVersion.ResourceID,
	datastaging.Resource.URL,
	datastaging.Resource.SiteID,
	datastaging.Site.Site,
	datastaging.Site.SiteTitle,
	datastaging.Site.AddedBy,
	datastaging.Nace.NaceID,
	datastaging.Nace.NaceCode,
	datastaging.Nace.NaceDescription,
	datastaging.Site.SecondLevelDomainID,
	datastaging.SecondLevelDomain.SecondLevelDomain,
	datastaging.SecondLevelDomain.TopLevelDomainID,
	datastaging.TopLevelDomain.TopLevelDomain,
	datastaging.Site.NutsLevel3ID,
	datastaging.NutsLevel3.Nuts3Code,
	datastaging.NutsLevel3.Nuts3Name,
	datastaging.NutsLevel3.NutsLevel2ID,
	datastaging.NutsLevel2.Nuts2Code,
	datastaging.NutsLevel2.Nuts2Name,
	datastaging.NutsLevel2.NutsLevel1ID,
	datastaging.NutsLevel1.Nuts1Code,
	datastaging.NutsLevel1.Nuts1Name,
	datastaging.NutsLevel1.CountryID,
	datastaging.Country.Country,
	datastaging.Country.CountryAbbreviation,
	datastaging.Country.EUMember,
	datastaging.ResourceVersion.MinuteID,
	datastaging.ResourceVersion.DateID
FROM
	datastaging.Subject NATURAL JOIN
	datastaging.ResourceVersion NATURAL JOIN
	datastaging.TestRun NATURAL JOIN
	datastaging.Server NATURAL JOIN
	datastaging.OperatingSystemFamily NATURAL JOIN
	datastaging.Resource NATURAL JOIN
	datastaging.Site NATURAL JOIN
	datastaging.SecondLevelDomain NATURAL JOIN
	datastaging.TopLevelDomain NATURAL JOIN
	datastaging.NutsLevel3 NATURAL JOIN
	datastaging.NutsLevel2 NATURAL JOIN
	datastaging.NutsLevel1 NATURAL JOIN
	datastaging.Country NATURAL JOIN
	datastaging.Nace
;

INSERT INTO eiaodw.TestResult_T
	(Probability,
	SubjectID,
	MinuteID,
	DateID,
	BarrierComputationVersionID,
	ResultID,
	ETLRunID,
	TestRunID)
SELECT DISTINCT  -- "DISTINCT" needed until bug in crawler part fixed.
	datastaging.TestResult.Probability,
	datastaging.TestResult.SubjectID,
	datastaging.TestResult.MinuteID,
	datastaging.TestResult.DateID,
	datastaging.TestResult.BarrierComputationVersionID,
	datastaging.TestResult.ResultID,
	datastaging.TestResult.ETLRunID,
	datastaging.TestResult.TestRunID
FROM
	datastaging.TestResult
;

INSERT INTO eiaodw.Time_T
	(MinuteID,
	Minute,
	HourID,
	Hour)
SELECT
	datastaging.Minute.MinuteID,
	datastaging.Minute.Minute,
	datastaging.Minute.HourID,
	datastaging.Hour.Hour
FROM
	datastaging.Minute NATURAL JOIN
	datastaging.Hour
;

INSERT INTO eiaodw.UWEMTest_T
	(UWEMTestID,
	UWEMTestName,
	UWEMTestTypeID,
	UWEMTestType)
SELECT
	datastaging.UWEMTest.UWEMTestID,
	datastaging.UWEMTest.UWEMTestName,
	datastaging.UWEMTest.UWEMTestTypeID,
	datastaging.UWEMTestType.UWEMTestType
FROM
	datastaging.UWEMTest NATURAL JOIN
	datastaging.UWEMTestType
;

INSERT INTO eiaodw.ETLRun_T
	(ETLRunID,
	SourceModel,
	SourceDB,
	TimeUsage,
	DateID,
	MinuteID,
	ETLVersionID,
	Major,
	Minor,
	Build)
SELECT
	datastaging.ETLRun.ETLRunID,
	datastaging.ETLRun.SourceModel,
	datastaging.ETLRun.SourceDB,
	datastaging.ETLRun.TimeUsage,
	datastaging.ETLRun.DateID,
	datastaging.ETLRun.MinuteID,
	datastaging.ETLRun.ETLVersionID,
	datastaging.ETLVersion.Major,
	datastaging.ETLVersion.Minor,
	datastaging.ETLVersion.Build
FROM
	datastaging.ETLRun NATURAL JOIN
	datastaging.ETLVersion
;

INSERT INTO eiaodw.MimeType_T
	(MimeTypeID,
	MimeType,
	MimeSubType)
SELECT
	datastaging.MimeType.MimeTypeID,
	datastaging.MimeType.MimeType,
	datastaging.MimeType.MimeSubType
FROM
	datastaging.MimeType
;

INSERT INTO eiaodw.InclusionType_T
	(InclusionTypeID,
	InclusionType)
SELECT
	datastaging.InclusionType.InclusionTypeID,
	datastaging.InclusionType.InclusionType
FROM
	datastaging.InclusionType
;


INSERT INTO eiaodw.TechnologyFinding_T
	(SubjectID,
	MimeTypeID,
	InclusionTypeID,
	DateID,
	MinuteID,
	TestRunID)
SELECT
	datastaging.TechnologyFinding.SubjectID,
	datastaging.TechnologyFinding.MimeTypeID,
	datastaging.TechnologyFinding.InclusionTypeID,
	datastaging.TechnologyFinding.DateID,
	datastaging.TechnologyFinding.MinuteID,
	datastaging.TechnologyFinding.TestRunID
FROM
	datastaging.TechnologyFinding
;

-- Add constraints
\i AddEIAODWConstraints_R20.sql

-- Add indexes
\i AddEIAODWIndexes_R20.sql
/*

-- Update mat. views 
DROP INDEX matviews.DomainAndEUMembership_pkey;
TRUNCATE matviews.DomainAndEUMembership;
INSERT INTO matviews.DomainAndEUMembership(Domain, EUMembershipCode, TestRunID) 
       SELECT DISTINCT Domain, matviews.GetEUMembershipCode(EUMember), TestRunID FROM Subject;
CREATE UNIQUE INDEX DomainAndEUMembership_pkey ON matviews.DomainAndEUMembership(Domain varchar_pattern_ops, EUMembershipCode, TestRunID);


DELETE FROM matviews.EUMembershipResults WHERE TestRunID IN (SELECT TestRunID FROM matviews.TestRunStates WHERE State = -1); -- These results are invalid now
SELECT eiaodw.CWAM_EUMembership('EU member', 0::SMALLINT, TestRunID),
       eiaodw.CWAM_EUMembership('Applicant country', 0::SMALLINT, TestRunID),
       eiaodw.CWAM_EUMembership('Outside EU', 0::SMALLINT, TestRunID)
FROM matviews.TestRunStates WHERE State <= 0; -- Will (re-)fill the EU mat. views


UPDATE matviews.TestRunStates SET STATE = 1; -- 1 for finalized. 

*/
-- Vacuum
VACUUM ANALYZE;

\echo #############
\echo Done!