--
--	Stats structure and process changes
--

drop table stats_project_build_tmp;
drop table tmp_projs_releases_tmp;
begin; delete from stats_project where day is null or week is null; commit;
drop table stats_project_tmp;
drop table topproj_admins;
DROP TABLE frs_dlstats_agg;
DROP TABLE frs_dlstats_filetotal_agg_old;
DROP TABLE stats_agg_pages_by_browser;
DROP TABLE stats_agg_pages_by_day_old;
DROP TABLE stats_agr_filerelease;
DROP TABLE stats_agr_project;
drop table group_cvs_history;
drop table project_counts_tmp;


--
--  Change the date format of stats_agg_site_by_group
--
--  Populated daily by site_stats.php
--
CREATE TABLE frs_dlstats_file_agg_tmp AS
SELECT
	substring(day::text from 1 for 6)::int AS month,
	substring(day::text from 7 for 2)::int AS day,
	file_id,
	downloads
	from frs_dlstats_file_agg;

DROP TABLE frs_dlstats_file_agg;
ALTER TABLE frs_dlstats_file_agg_tmp RENAME TO frs_dlstats_file_agg;

CREATE UNIQUE INDEX frsdlfileagg_month_day_file ON frs_dlstats_file_agg(month,day,file_id);


drop index httpdl_fid;
drop index httpdl_group_id;
create index statshttpdl_day_fileid ON stats_http_downloads(day,filerelease_id);
drop index ftpdl_fid;
drop index ftpdl_group_id;
create index statsftpdl_day_fileid ON stats_ftp_downloads(day,filerelease_id);

--
--	Create an archive table of project_weekly_metric
--
--	Populated by project_weekly_metric.php
--
CREATE TABLE stats_project_metric (
month int not null default 0,
day int not null default 0,
ranking int not null default 0,
percentile float not null default 0,
group_id int not null default 0
);

CREATE UNIQUE INDEX statsprojectmetric_month_day_group ON stats_project_metric(month,day,group_id);


--
--	Change the date format of stats_agg_site_by_group
--
--	Populated daily by site_stats.php
--
CREATE TABLE stats_agg_site_by_group_tmp AS
SELECT 
	substring(day::text from 1 for 6)::int AS month, 
	substring(day::text from 7 for 2)::int AS day, 
	group_id,
	count
	from stats_agg_site_by_group ;

DROP TABLE stats_agg_site_by_group;
ALTER TABLE stats_agg_site_by_group_tmp RENAME TO stats_agg_site_by_group;

DROP TABLE stats_agg_site_by_day;

CREATE UNIQUE INDEX statssitebygroup_month_day_group ON stats_agg_site_by_group(month,day,group_id);


--
--	Change the date format of stats_agg_logo_by_group
--
--	Populated daily by site_stats.php
--
CREATE TABLE stats_agg_logo_by_group_tmp AS
SELECT	   
	substring(day::text from 1 for 6)::int AS month,
	substring(day::text from 7 for 2)::int AS day,
	group_id,
	count 
	from stats_agg_logo_by_group ;

DROP TABLE stats_agg_logo_by_group;
ALTER TABLE stats_agg_logo_by_group_tmp RENAME TO stats_agg_logo_by_group;

CREATE UNIQUE INDEX statslogobygroup_month_day_group ON stats_agg_logo_by_group(month,day,group_id);


--
-- Subdomain pages
--
create table stats_subd_pages (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
pages INT NOT NULL DEFAULT 0
);

--
--	Migrate data from old stats_project table
--
INSERT INTO stats_subd_pages
SELECT month,day,group_id,subdomain_views 
FROM stats_project WHERE subdomain_views > 0;

CREATE UNIQUE INDEX statssubdpages_month_day_group ON stats_subd_pages(month,day,group_id);


create table stats_cvs_user (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
user_id INT NOT NULL DEFAULT 0,
checkouts INT NOT NULL DEFAULT 0,
commits INT NOT NULL DEFAULT 0,
adds INT NOT NULL DEFAULT 0
);

create table stats_cvs_group (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
checkouts INT NOT NULL DEFAULT 0,
commits INT NOT NULL DEFAULT 0,
adds INT NOT NULL DEFAULT 0
);

--
--	Migrate data from old stats_project table
--
INSERT INTO stats_cvs_group 
SELECT month,day,group_id,cvs_checkouts,cvs_commits,cvs_adds 
FROM stats_project 
WHERE cvs_checkouts > 0 
OR cvs_commits > 0 
OR cvs_adds > 0;

CREATE UNIQUE INDEX statscvsgroup_month_day_group ON stats_cvs_group(month,day,group_id);


DROP INDEX archive_project_day;
DROP INDEX archive_project_month;
DROP INDEX archive_project_monthday;
DROP INDEX archive_project_week;
DROP INDEX project_log_group;

--
--	Populated daily by site_stats.php
--
create table stats_project_developers (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
developers INT NOT NULL DEFAULT 0
);

--
--	Migrate data from old stats_project table
--

CREATE UNIQUE INDEX statsprojectdev_month_day_group ON stats_project_developers(month,day,group_id);


--
--	Reorg and normalize stats_project as much as feasible
--
--	Populated daily by site_stats.php
--
DROP TABLE stats_project;

create table stats_project (
month INT NOT NULL DEFAULT 0,
day INT NOT NULL DEFAULT 0,
group_id INT NOT NULL DEFAULT 0,
file_releases INT DEFAULT 0,
msg_posted INT DEFAULT 0,
msg_uniq_auth INT DEFAULT 0,
bugs_opened INT DEFAULT 0,
bugs_closed INT DEFAULT 0,
support_opened INT DEFAULT 0,
support_closed INT DEFAULT 0,
patches_opened INT DEFAULT 0,
patches_closed INT DEFAULT 0,
artifacts_opened INT DEFAULT 0,
artifacts_closed INT DEFAULT 0,
tasks_opened INT DEFAULT 0,
tasks_closed INT DEFAULT 0,
help_requests INT DEFAULT 0
);

CREATE UNIQUE INDEX statsproject_month_day_group ON stats_project(month,day,group_id);


CREATE TABLE "stats_project_months" (
	"month" integer,
	"group_id" integer,
	"developers" integer,
	"group_ranking" integer,
	"group_metric" double precision,
	"logo_showings" integer,
	"downloads" integer,
	"site_views" integer,
	"subdomain_views" integer,
	"page_views" integer,
	"file_releases" integer,
	"msg_posted" integer,
	"msg_uniq_auth" integer,
	"bugs_opened" integer,
	"bugs_closed" integer,
	"support_opened" integer,
	"support_closed" integer,
	"patches_opened" integer,
	"patches_closed" integer,
	"artifacts_opened" integer,
	"artifacts_closed" integer,
	"tasks_opened" integer,
	"tasks_closed" integer,
	"help_requests" integer,
	"cvs_checkouts" integer,
	"cvs_commits" integer,
	"cvs_adds" integer
);

CREATE  INDEX "statsprojectmonths_groupid" on "stats_project_months" using btree ( "group_id" "int4_ops" );
CREATE  INDEX "statsprojectmonths_groupid_mont" on "stats_project_months" using btree ( "group_id" "int4_ops", "month" "int4_ops" );


CREATE TABLE "stats_site_pages_by_day" (
	"month" integer,
	"day" integer,
	"site_page_views" integer
);

CREATE UNIQUE INDEX "statssitepgsbyday_oid" on "stats_site_pages_by_day" using btree ( "oid" "oid_ops" );
CREATE  INDEX "statssitepagesbyday_month_day" on "stats_site_pages_by_day" using btree ( "month" "int4_ops", "day" "int4_ops" );


CREATE TABLE "stats_site_pages_by_month" (
	"month" integer,
	"site_page_views" integer
);


CREATE TABLE "stats_site_months" (
	"month" integer,
	"site_page_views" integer,
	"downloads" integer,
	"subdomain_views" integer,
	"msg_posted" integer,
	"bugs_opened" integer,
	"bugs_closed" integer,
	"support_opened" integer,
	"support_closed" integer,
	"patches_opened" integer,
	"patches_closed" integer,
	"artifacts_opened" integer,
	"artifacts_closed" integer,
	"tasks_opened" integer,
	"tasks_closed" integer,
	"help_requests" integer,
	"cvs_checkouts" integer,
	"cvs_commits" integer,
	"cvs_adds" integer
);

CREATE  INDEX "statssitemonths_month" on "stats_site_months" using btree ( "month" "int4_ops" );


--
--	Reorg and normalize the stats_site table
--
--	Populated daily by site_stats.php
--
create table stats_site_tmp AS 
select month,day,uniq_users,sessions,total_users,new_users,new_projects 
from stats_site;

DROP TABLE stats_site;
ALTER TABLE stats_site_tmp RENAME TO stats_site;

CREATE UNIQUE INDEX statssite_month_day on stats_site(month,day);