/*
|| Script: SFA_Setup.sql
||
|| Creates:
|| - A new schema (SFA)
|| - New tables 
|| - Reporting views
|| - Materialized view
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 
|| Database 11g SQL Plan Baseline Management (SPM) and should be carefully 
|| proofread before executing it against any existing Oracle database to 
|| insure that no potential damage can occur.
||
*/

-----
-- Create SFA user and SFA_ROLE role
-----
DROP ROLE sfa_role;
CREATE ROLE sfa_role;

DROP USER sfa CASCADE;
CREATE USER sfa
    IDENTIFIED BY sfa
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 100M ON example
    PROFILE default;

GRANT CREATE TABLE TO sfa;
GRANT CREATE VIEW TO sfa;
GRANT CREATE PROCEDURE TO sfa;
GRANT EXECUTE ANY PROCEDURE TO sfa;
GRANT CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM TO sfa;
GRANT CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM TO sfa;
GRANT sfa_role to SFA;

-----
-- Create SFA schema objects 
-----
-- Create SALES_REGIONS table
-----
DROP TABLE sfa.sales_regions CASCADE CONSTRAINTS PURGE;
CREATE TABLE sfa.sales_regions
(
  region_id   NUMBER(3)    NOT NULL,
  abbr        CHAR(4)      NOT NULL,
  description VARCHAR2(32) DEFAULT 'UNDEFINED' NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sfa.sales_regions
  ADD CONSTRAINT sales_regions_pk
  PRIMARY KEY (region_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-----
-- Create SALES_DISTRICTS table
-----
DROP TABLE sfa.sales_districts CASCADE CONSTRAINTS PURGE;
CREATE TABLE sfa.sales_districts
(
  district_id   NUMBER(3)    NOT NULL,
  abbr          CHAR(4)      NOT NULL,
  description   VARCHAR2(32) DEFAULT 'UNDEFINED' NOT NULL,
  region_id     NUMBER(3)    NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sfa.sales_districts
  ADD CONSTRAINT sales_districts_pk
  PRIMARY KEY (district_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sfa.sales_districts 
  ADD CONSTRAINT sales_district_region_fk
  FOREIGN KEY (region_id)
  REFERENCES sfa.sales_regions (region_id);

-----
-- Create SALES_ZONES table
-----
DROP TABLE sfa.sales_zones CASCADE CONSTRAINTS PURGE;
CREATE TABLE sfa.sales_zones
(
  district_id   NUMBER(3)    NOT NULL,
  geo_id        VARCHAR2(40) NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sfa.sales_zones
  ADD CONSTRAINT sales_zones_pk
  PRIMARY KEY (district_id, geo_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sfa.sales_zones
  ADD CONSTRAINT sales_zone_district_fk
  FOREIGN KEY (district_id)
  REFERENCES sfa.sales_districts (district_id);

-----
-- Grant appropriate permissions to SFA and SFA_ROLE
-----
GRANT SELECT ON sh.countries        TO sfa;
GRANT SELECT ON sh.customers        TO sfa;
GRANT SELECT ON sh.products         TO sfa;
GRANT SELECT ON sh.promotions       TO sfa;
GRANT SELECT ON sh.sales            TO sfa;
GRANT SELECT ON sh.times            TO sfa;
GRANT SELECT ON sfa.sales_regions   TO sfa_role;
GRANT INSERT ON sfa.sales_regions   TO sfa_role;
GRANT UPDATE ON sfa.sales_regions   TO sfa_role;
GRANT DELETE ON sfa.sales_regions   TO sfa_role;
GRANT SELECT ON sfa.sales_districts TO sfa_role;
GRANT INSERT ON sfa.sales_districts TO sfa_role;
GRANT UPDATE ON sfa.sales_districts TO sfa_role;
GRANT DELETE ON sfa.sales_districts TO sfa_role;
GRANT SELECT ON sfa.sales_zones     TO sfa_role;
GRANT INSERT ON sfa.sales_zones     TO sfa_role;
GRANT UPDATE ON sfa.sales_zones     TO sfa_role;
GRANT DELETE ON sfa.sales_zones     TO sfa_role;

-----
-- Create SFA reporting views
-----
CREATE OR REPLACE VIEW sfa.sales_hierarchy AS
   SELECT
      SR.abbr "Rgn",
      SR.description "Region Name",
      SD.abbr "Dist",
      SD.description "District Name"
     FROM 
        sfa.sales_regions SR,
        sfa.sales_districts SD
    WHERE SD.region_id = SR.region_id
/

CREATE OR REPLACE VIEW sfa.sales_assignments 
   (rgn_abbr, dst_abbr, geo_id)
AS
   SELECT
      SR.abbr,
      SD.abbr,
      SZ.geo_id
     FROM 
        sfa.sales_regions SR,
        sfa.sales_districts SD,
        sfa.sales_zones SZ
    WHERE SD.region_id = SR.region_id
      AND SZ.district_id = SD.district_id
/    

CREATE OR REPLACE VIEW sfa.sales_made
   (rgn_abbr, dst_abbr, geo_id, cust_id, total_sales, amount_sold)
AS
   SELECT
       SR.abbr
      ,SD.abbr
      ,SZ.geo_id
      ,C.cust_id
      ,SUM(SH.amount_sold)
      ,SUM(SH.quantity_sold)
     FROM 
       sfa.sales_regions SR
      ,sfa.sales_districts SD
      ,sfa.sales_zones SZ
      ,sh.customers C
      ,sh.sales SH
    WHERE SD.region_id = SR.region_id
      AND SZ.district_id = SD.district_id
      AND C.cust_state_province = SZ.geo_id
      AND C.cust_ID = SH.cust_id
    GROUP BY 
       SR.abbr
      ,SD.abbr
      ,SZ.geo_id
      ,C.cust_id    
    ORDER BY 
       SR.abbr
      ,SD.abbr
      ,SZ.geo_id
      ,C.cust_id    
/    

GRANT SELECT ON sfa.sales_assignments TO sfa_role;
GRANT SELECT ON sfa.sales_hierarchy TO sfa_role;
GRANT SELECT ON sfa.sales_made TO sfa_role;

-----
-- Perform SFA tables initial data loading
-----
-- Load SFA.SALES_REGIONS
INSERT INTO sfa.sales_regions VALUES (1, 'NE00', 'Northeastern United States');
INSERT INTO sfa.sales_regions VALUES (2, 'SE00', 'Southeastern United States');
INSERT INTO sfa.sales_regions VALUES (3, 'CN00', 'Central United States');
INSERT INTO sfa.sales_regions VALUES (4, 'SW00', 'Southwestern United States');
INSERT INTO sfa.sales_regions VALUES (5, 'NW00', 'Northwestern United States');
COMMIT;
   
-- Load SFA.SALES_DISTRICTS
INSERT INTO sfa.sales_districts VALUES (1,  'NE10', 'New England', 1);
INSERT INTO sfa.sales_districts VALUES (2,  'NE20', 'New York', 1);
INSERT INTO sfa.sales_districts VALUES (3,  'SE10', 'Mid-Atlantic', 2);
INSERT INTO sfa.sales_districts VALUES (4,  'SE20', 'Deep South', 2);
INSERT INTO sfa.sales_districts VALUES (5,  'CN10', 'Midwest', 3);
INSERT INTO sfa.sales_districts VALUES (6,  'CN20', 'Great Plains', 3);
INSERT INTO sfa.sales_districts VALUES (7,  'SW10', 'Pacific Coast', 4);
INSERT INTO sfa.sales_districts VALUES (8,  'SW20', 'West Central', 4);
INSERT INTO sfa.sales_districts VALUES (9,  'NW10', 'Oregon-Washington', 5);
INSERT INTO sfa.sales_districts VALUES (10, 'NW20', 'Mid NorthWest', 5);
COMMIT;

-- Load SFA.SALES_ZONES
INSERT INTO sfa.sales_zones VALUES (1 , 'CT');
INSERT INTO sfa.sales_zones VALUES (1 , 'ME');
INSERT INTO sfa.sales_zones VALUES (1 , 'NH');
INSERT INTO sfa.sales_zones VALUES (1 , 'RI');
INSERT INTO sfa.sales_zones VALUES (1 , 'VT');
INSERT INTO sfa.sales_zones VALUES (2 , 'DE');
INSERT INTO sfa.sales_zones VALUES (2 , 'MA');
INSERT INTO sfa.sales_zones VALUES (2 , 'MD');
INSERT INTO sfa.sales_zones VALUES (2 , 'NY');
INSERT INTO sfa.sales_zones VALUES (2 , 'NJ');
INSERT INTO sfa.sales_zones VALUES (3 , 'DC');
INSERT INTO sfa.sales_zones VALUES (3 , 'FL');
INSERT INTO sfa.sales_zones VALUES (3 , 'GA');
INSERT INTO sfa.sales_zones VALUES (3 , 'NC');
INSERT INTO sfa.sales_zones VALUES (3 , 'SC');
INSERT INTO sfa.sales_zones VALUES (3 , 'VA');
INSERT INTO sfa.sales_zones VALUES (4 , 'AR');
INSERT INTO sfa.sales_zones VALUES (4 , 'AL');
INSERT INTO sfa.sales_zones VALUES (4 , 'MO');
INSERT INTO sfa.sales_zones VALUES (4 , 'LA');
INSERT INTO sfa.sales_zones VALUES (4 , 'MS');
INSERT INTO sfa.sales_zones VALUES (5 , 'KY');
INSERT INTO sfa.sales_zones VALUES (5 , 'OH');
INSERT INTO sfa.sales_zones VALUES (5 , 'PA');
INSERT INTO sfa.sales_zones VALUES (5 , 'TN');
INSERT INTO sfa.sales_zones VALUES (5 , 'WV');
INSERT INTO sfa.sales_zones VALUES (6 , 'IL');
INSERT INTO sfa.sales_zones VALUES (6 , 'IN');
INSERT INTO sfa.sales_zones VALUES (6 , 'MI');
INSERT INTO sfa.sales_zones VALUES (6 , 'MN');
INSERT INTO sfa.sales_zones VALUES (6 , 'WI');
INSERT INTO sfa.sales_zones VALUES (7 , 'IO');
INSERT INTO sfa.sales_zones VALUES (7 , 'KS');
INSERT INTO sfa.sales_zones VALUES (7 , 'NE');
INSERT INTO sfa.sales_zones VALUES (7 , 'ND');
INSERT INTO sfa.sales_zones VALUES (7 , 'SD');
INSERT INTO sfa.sales_zones VALUES (8 , 'AZ');
INSERT INTO sfa.sales_zones VALUES (8 , 'CO');
INSERT INTO sfa.sales_zones VALUES (8 , 'NM');
INSERT INTO sfa.sales_zones VALUES (8 , 'OK');
INSERT INTO sfa.sales_zones VALUES (8 , 'TX');
INSERT INTO sfa.sales_zones VALUES (9 , 'AK');
INSERT INTO sfa.sales_zones VALUES (9 , 'WA');
INSERT INTO sfa.sales_zones VALUES (9 , 'WY');
INSERT INTO sfa.sales_zones VALUES (9 , 'ID');
INSERT INTO sfa.sales_zones VALUES (9 , 'MT');
INSERT INTO sfa.sales_zones VALUES (10 , 'CA');
INSERT INTO sfa.sales_zones VALUES (10 , 'HA');
INSERT INTO sfa.sales_zones VALUES (10 , 'NV');
INSERT INTO sfa.sales_zones VALUES (10 , 'OR');
INSERT INTO sfa.sales_zones VALUES (10 , 'UT');
COMMIT;

-----
-- Create a new materialized view (MV) that combines Sales
-- Force Administration (SFA) information with Customer and 
-- Sales information. This MV will be refreshed automatically
-- whenever any SFA information changes.
-----
DROP MATERIALIZED VIEW sfa.mv_sales_summary;
CREATE MATERIALIZED VIEW sfa.mv_sales_summary
    TABLESPACE example
    BUILD IMMEDIATE
    ENABLE QUERY REWRITE
AS
SELECT
    SR.abbr                 AS rgn_abbr
   ,SD.abbr                 AS dst_abbr
   ,SZ.geo_id               AS ter_abbr        
   ,C.cust_id               AS cust_id
   ,SUM(quantity_sold)      AS tot_qty_sold
   ,AVG(quantity_sold)      AS avg_qty_sold
   ,COUNT(quantity_sold)    AS cnt_qty_sold
   ,SUM(amount_sold)        AS tot_amt_sold
   ,AVG(amount_sold)        AS avg_amt_sold
   ,COUNT(amount_sold)      AS cnt_amt_sold
  FROM 
    sfa.sales_regions SR
   ,sfa.sales_districts SD
   ,sfa.sales_zones SZ
   ,sh.customers C
   ,sh.sales SH
 WHERE SD.region_id = SR.region_id
   AND SZ.district_id = SD.district_id
   AND C.cust_state_province = SZ.geo_id
   AND C.cust_ID = SH.cust_id
 GROUP BY 
    SR.abbr
   ,SD.abbr
   ,SZ.geo_id
   ,C.cust_id    
 ORDER BY 
    SR.abbr
   ,SD.abbr
   ,SZ.geo_id
   ,C.cust_id    
;  

-----
-- Gather statistics on all database objects in the SFA schema
-----
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SFA', cascade => TRUE);
END;
/