-- Create the matviews schema. This schema is only used by the CWAM stored
-- procedures.

drop schema if exists matviews cascade;
create schema matviews;
revoke all on schema matviews from public;

-- Populate the matviews schema.

create table matviews.Scenario(
	ScenarioID int4,
	TestRunID int4,
	DisabilityGroupID int2,
	CWAM real,
	primary key(TestRunID, ScenarioID, DisabilityGroupID)
);

create table matviews.Site(
	SiteID int2,
	TestRunID int4,
	DisabilityGroupID int2,
	CWAM real,
	StdDev real,
	ErrorMargin real,
	MinCWAM real,
	MaxCWAM real,
	ScenarioCount int2,
        DownloadCount int2,
        PageAverage real,
	UnavailablePages int2,
        UnparsablePages int2,
        TidyPages int2,
        NumberTests int2,
        ExhaustiveScan int2,
        primary key(TestRunID, SiteID, DisabilityGroupID)
);


create table matviews.Group(
	"Group" int2[],
	GroupHash int,
	TestRunID int4,
	DisabilityGroupID int2,
	CWAM real,
	StdDev real,
	ErrorMargin real,
	MinCWAM real,
	MaxCWAM real,
        SiteCount int2 --should be length of Group, but is saved to prevent errors and keep clear data
	--primary key(TestRunID, "Group", DisabilityGroupID) group could be longer than max 8kb
);
create index Matviews_Group_IDX on matviews.Group(TestRunID, GroupHash, DisabilityGroupID); --instead of primary key

create table matviews.GroupCache(
	GroupType varchar(127),
	GroupCode varchar(127),
	"Group" int2[],
	TestRunID int4,
	primary key(TestRunID, GroupType, GroupCode)
);

create table matviews.ScenarioContent(
	ScenarioID int4,
	MimeTypeID int2,
	InclusionTypeID int2,
	TestRunID int4,
	Result int,
	primary key(TestRunID, ScenarioID, MimeTypeID, InclusionTypeID)
);

create table matviews.SiteContent(
        SiteID int2,
        MimeTypeID int2,
        InclusionTypeID int2,
        TestRunID int4,
        Result real,
        primary key(TestRunID, SiteID, MimeTypeID, InclusionTypeID)
);

create table matviews.GroupContent(
        "Group" int2[],
        MimeTypeID int2,
        InclusionTypeID int2,
        TestRunID int4,
        Result real,
        primary key(TestRunID, "Group", MimeTypeID, InclusionTypeID)
);

create table matviews.GenericCache(
	FunctionName varchar(127),
	ArgumentsHash int4,
	Arguments int4[],
	TestRunID int4,
	Result float8
);
create index Matviews_GenericCache_IDX on matviews.GenericCache(TestRunID, FunctionName, ArgumentsHash); --instead of primary key

create table matviews.ScenarioTestRatio(
	ScenarioID int4,
	BarrierComputationID int2,
	TestRunID int4,
	TestRatio float,
	primary key(TestRunID, ScenarioID, BarrierComputationID)
);

create table matviews.SiteTestRatio(
	SiteID int2,
	BarrierComputationID int2,
	TestRunID int4,
	TestRatio float,
	primary key(TestRunID, SiteID, BarrierComputationID)
);