/*
 * Title  : acp_tab.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0
 * Purpose: Auto Capacity Planner
 * History: N/A
 */

SET ECHO OFF
SET FEEDBACK ON
SET VERIFY OFF

DEFINE DAT_TS=&DAT_TS
DEFINE IDX_TS=&IDX_TS

rem 00.Config Tables

CREATE TABLE SITES(
	SITE_KEY	NUMBER,
	SITE_NAME	VARCHAR2(50)	NOT NULL,
	TIME_ZONE	CHAR(3)		NOT NULL,
	SITE_DESC	VARCHAR2(250),
	SITE_STATUS	CHAR DEFAULT 'P'
)TABLESPACE &&DAT_TS
/

--SITE_STATUS=A: Active; P: Passive; S: System
--Note: The site key should be between 1 (to) n (Where n is a positive integer)
--site_key=0 is a system site having config data under user with key 0.

ALTER TABLE SITES ADD(
	CONSTRAINT PK_SITES_KEY	PRIMARY KEY(SITE_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE SITES ADD(
	CONSTRAINT UK_SITES_NAME UNIQUE(SITE_NAME)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE SITES ADD(
	CONSTRAINT CK_SITES_STATUS CHECK(SITE_STATUS IN ('A','P','S'))
)
/

create or replace view v$mysite as 
select
	site_key key,
	site_name name,
	time_zone
from
	sites
where
	site_status='A';

CREATE TABLE ACP_DB_RUN(
	ACP_DB_RUN_KEY		NUMBER,
	SITE_KEY		NUMBER,
	DB_NAME			VARCHAR2(8)	NOT NULL,
	DB_CLASS		CHAR		NOT NULL,
	DB_GROUP		VARCHAR2(10),
	DB_DESC			VARCHAR2(100),
	HOST_IP			VARCHAR2(15),
	NLS_LANG		VARCHAR2(50),
	USERNAME		VARCHAR2(30),
	PASSWORD		VARCHAR2(30),
	TNS_NAME		VARCHAR2(128),
	DB_LINK			VARCHAR2(128),
	IS_RUN			CHAR		DEFAULT 'Y' NOT NULL
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_DB_RUN ADD(
	CONSTRAINT PK_ACP_DB_RUN PRIMARY KEY(ACP_DB_RUN_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_DB_RUN ADD(
	CONSTRAINT FK_ACP_DB_RUN_SITEKEY FOREIGN KEY(SITE_KEY)
	REFERENCES SITES
)
/

ALTER TABLE ACP_DB_RUN ADD(
	CONSTRAINT CK_ACP_DB_RUN_DBCLASS CHECK(DB_CLASS IN ('C','P','U','D','T','E','R'))
)
/

ALTER TABLE ACP_DB_RUN ADD(
	CONSTRAINT CK_ACP_DB_RUN_ISRUN CHECK(IS_RUN IN ('Y','N'))
)
/

CREATE TABLE ACP_LOG(
	ACP_LOG_KEY		NUMBER,
	ACP_DB_RUN_KEY		NUMBER			NOT NULL,
	SGS_CLASS		CHAR(2)			NOT NULL,
	TIME_STAMP		DATE	DEFAULT SYSDATE	NOT NULL,
	STATUS			CHAR			NOT NULL,
	COMMENTS		VARCHAR2(100)
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_LOG ADD(
	CONSTRAINT PK_ACP_LOG PRIMARY KEY(ACP_LOG_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_LOG ADD(
	CONSTRAINT FK_ACP_LOG_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_LOG ADD(
	CONSTRAINT CK_ACP_LOG_SGSCLASS CHECK(SGS_CLASS IN ('DB','TS','US'))
)
/

ALTER TABLE ACP_LOG ADD(
	CONSTRAINT CK_ACP_LOG_STATUS CHECK(STATUS IN ('P','F'))
)
/

CREATE TABLE ACP_DB_SGS(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_DB_SGS_KEY		NUMBER,
	DATA_CLASS		CHAR,
	DAY			DATE		NOT NULL,
	TOTAL_BYTES		NUMBER,
	USED_BYTES		NUMBER,
	PCT_USED		NUMBER
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_DB_SGS ADD(
	CONSTRAINT PK_ACP_DB_SGS PRIMARY KEY(ACP_DB_RUN_KEY,ACP_DB_SGS_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_DB_SGS ADD(
	CONSTRAINT FK_ACP_DB_SGS_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_DB_SGS ADD(
	CONSTRAINT CK_ACP_DB_SGS_DATACLASS CHECK(DATA_CLASS IN ('P','V'))
)
/

--P:Permanent;V:Volatile

CREATE TABLE ACP_TS_SGS(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_TS_SGS_KEY		NUMBER,
	DATA_CLASS		CHAR,
	TS_NAME			VARCHAR2(30)	NOT NULL,
	DAY			DATE		NOT NULL,
	TOTAL_BYTES		NUMBER,
	USED_BYTES		NUMBER,
	PCT_USED		NUMBER
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_TS_SGS ADD(
	CONSTRAINT PK_ACP_TS_SGS PRIMARY KEY(ACP_DB_RUN_KEY,ACP_TS_SGS_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_TS_SGS ADD(
	CONSTRAINT FK_ACP_TS_SGS_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_TS_SGS ADD(
	CONSTRAINT CK_ACP_TS_SGS_DATACLASS CHECK(DATA_CLASS IN ('P','V'))
)
/

--P:Permanent;V:Volatile

CREATE TABLE ACP_US_SGS(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_US_SGS_KEY		NUMBER,
	DATA_CLASS		CHAR,
	USER_NAME		VARCHAR2(30)	NOT NULL,
	DAY			DATE		NOT NULL,
	USED_BYTES		NUMBER
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_US_SGS ADD(
	CONSTRAINT PK_ACP_US_SGS PRIMARY KEY(ACP_DB_RUN_KEY,ACP_US_SGS_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_US_SGS ADD(
	CONSTRAINT FK_ACP_US_SGS_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_US_SGS ADD(
	CONSTRAINT CK_ACP_US_SGS_DATACLASS CHECK(DATA_CLASS IN ('S','U'))
)
/

--S:System;U-User

CREATE TABLE ACP_DBW_SGR(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_DBW_SGR_KEY		NUMBER,
	DATA_CLASS		CHAR,
	YEAR			NUMBER		NOT NULL,
	WEEK#			NUMBER		NOT NULL,
	GROWTH_BYTES		NUMBER		NOT NULL
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_DBW_SGR ADD(
	CONSTRAINT PK_ACP_DBW_SGR PRIMARY KEY(ACP_DB_RUN_KEY,ACP_DBW_SGR_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_DBW_SGR ADD(
	CONSTRAINT FK_ACP_DBW_SGR_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_DBW_SGR ADD(
	CONSTRAINT CK_ACP_DBW_SGR_DATACLASS CHECK(DATA_CLASS IN ('S','U'))
)	
/

CREATE TABLE ACP_TSW_SGR(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_TSW_SGR_KEY		NUMBER,
	DATA_CLASS		CHAR,
	TS_NAME			VARCHAR2(30)	NOT NULL,
	YEAR			NUMBER		NOT NULL,
	WEEK#			NUMBER		NOT NULL,
	GROWTH_BYTES		NUMBER		NOT NULL
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_TSW_SGR ADD(
	CONSTRAINT PK_ACP_TSW_SGR PRIMARY KEY(ACP_DB_RUN_KEY,ACP_TSW_SGR_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_TSW_SGR ADD(
	CONSTRAINT FK_ACP_TSW_SGR_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_TSW_SGR ADD(
	CONSTRAINT CK_ACP_TSW_SGS_DATACLASS CHECK(DATA_CLASS IN ('S','U'))
)
/

CREATE TABLE ACP_USW_SGR(
	ACP_DB_RUN_KEY		NUMBER,
	ACP_USW_SGR_KEY		NUMBER,
	DATA_CLASS		CHAR,
	USER_NAME		VARCHAR2(30)	NOT NULL,
	YEAR			NUMBER		NOT NULL,	
	WEEK#			NUMBER		NOT NULL,
	GROWTH_BYTES		NUMBER		NOT NULL
)TABLESPACE &&DAT_TS
/

ALTER TABLE ACP_USW_SGR ADD(
	CONSTRAINT PK_ACP_USW_SGR PRIMARY KEY(ACP_DB_RUN_KEY,ACP_USW_SGR_KEY)
	USING INDEX TABLESPACE &&IDX_TS
)
/

ALTER TABLE ACP_USW_SGR ADD(
	CONSTRAINT FK_ACP_USW_SGR_ACPDBRUNKEY FOREIGN KEY(ACP_DB_RUN_KEY)
	REFERENCES ACP_DB_RUN
)
/

ALTER TABLE ACP_USW_SGR ADD(
	CONSTRAINT CK_ACP_USW_SGR_DATACLASS CHECK(DATA_CLASS IN ('S','U'))
)
/

CREATE OR REPLACE VIEW V$ACP_DB_SGS_GB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_DB_SGS_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	TO_CHAR(A.DAY,'Mon dd, yyyy') DAY,
	TO_NUMBER(TO_CHAR(A.DAY,'YYYYMMDD')) ADAY,
	ROUND(A.TOTAL_BYTES/(1024*1024*1024),2) TOTAL_GBYTES,
	ROUND(A.USED_BYTES/(1024*1024*1024),2) USED_GBYTES,
	PCT_USED
FROM
	ACP_DB_SGS A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
/

CREATE OR REPLACE VIEW V$ACP_TS_SGS_MB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_TS_SGS_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	A.TS_NAME,
	TO_CHAR(A.DAY,'Mon dd, yyyy') DAY,
	TO_NUMBER(TO_CHAR(A.DAY,'YYYYMMDD')) ADAY,
	ROUND(A.TOTAL_BYTES/(1024*1024),2) TOTAL_MBYTES,
	ROUND(A.USED_BYTES/(1024*1024),2) USED_MBYTES,
	PCT_USED
FROM
	ACP_TS_SGS A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
/

CREATE OR REPLACE VIEW V$ACP_US_SGS_MB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_US_SGS_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	A.USER_NAME,
	TO_CHAR(A.DAY,'Mon dd, yyyy') DAY,
	TO_NUMBER(TO_CHAR(A.DAY,'YYYYMMDD')) ADAY,
	ROUND(A.USED_BYTES/(1024*1024),2) USED_MBYTES
FROM
	ACP_US_SGS A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
/

CREATE OR REPLACE VIEW V$ACP_DBW_SGR_GB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_DBW_SGR_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	YEAR,
	WEEK#,
	TO_CHAR(ROUND(A.GROWTH_BYTES/(1024*1024*1024),2),'999G999G999D99','NLS_NUMERIC_CHARACTERS=''.,''') GROWTH_GBYTES
FROM
	ACP_DBW_SGR A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
AND	ROUND(A.GROWTH_BYTES/(1024*1024*1024),2)>0
ORDER BY
	YEAR DESC,
	WEEK# DESC,
	GROWTH_BYTES DESC
/

CREATE OR REPLACE VIEW V$ACP_TSW_SGR_MB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_TSW_SGR_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	A.TS_NAME,
	YEAR,
	WEEK#,
	TO_CHAR(ROUND(A.GROWTH_BYTES/(1024*1024),2),'999G999G999D99','NLS_NUMERIC_CHARACTERS=''.,''') GROWTH_MBYTES
FROM
	ACP_TSW_SGR A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
AND	ROUND(A.GROWTH_BYTES/(1024*1024),2)>0	
ORDER BY
	YEAR DESC,
	WEEK# DESC,
	GROWTH_BYTES DESC
/	

CREATE OR REPLACE VIEW V$ACP_USW_SGR_MB AS
SELECT 
	A.ACP_DB_RUN_KEY,
	A.ACP_USW_SGR_KEY,
	R.DB_NAME,
	A.DATA_CLASS,
	A.USER_NAME,
	YEAR,
	WEEK#,
	TO_CHAR(ROUND(A.GROWTH_BYTES/(1024*1024),2),'999G999G999D99','NLS_NUMERIC_CHARACTERS=''.,''') GROWTH_MBYTES
FROM
	ACP_USW_SGR A,ACP_DB_RUN R
WHERE
	A.ACP_DB_RUN_KEY=R.ACP_DB_RUN_KEY
AND	ROUND(A.GROWTH_BYTES/(1024*1024),2)>0	
ORDER BY
	YEAR DESC,
	WEEK# DESC,
	GROWTH_BYTES DESC
/
