--------------------------------------------------------
--  POPULATE OE SCHEMA WITH AUTHENTICATION MECHANISM
--------------------------------------------------------

--------------------------------------------------------
-- DDL for Dropping Tables
--------------------------------------------------------
  DROP TABLE "OE"."CUSTOMER_PASSWORD";
  DROP TABLE "OE"."CUSTOMERS_GROUP";
  DROP TABLE "OE"."SECURITY_GROUPS";

--------------------------------------------------------
--  DDL for Table SECURITY_GROUPS
--------------------------------------------------------
  CREATE TABLE "OE"."SECURITY_GROUPS" 
   ("SG_ID" NUMBER, "SG_NAME" VARCHAR2(20 BYTE), "SG_DESCRIPTION" VARCHAR2(20 BYTE)) 
   SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   NOCOMPRESS NOLOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);

--------------------------------------------------------
--  DDL for Table CUSTOMERS_GROUP
--------------------------------------------------------
  CREATE TABLE "OE"."CUSTOMERS_GROUP" 
   ("CSG_CUST_ID" NUMBER, "CSG_SG_ID" NUMBER) 
   SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   NOCOMPRESS NOLOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);

--------------------------------------------------------
--  DDL for Table CUSTOMER_PASSWORD
--------------------------------------------------------
  CREATE TABLE "OE"."CUSTOMER_PASSWORD" 
   ("CUSTPWD_CUSTID" NUMBER, "CUSTPWD_PASSWORD" VARCHAR2(10 BYTE)) 
   SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   NOCOMPRESS NOLOGGING 
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);

--------------------------------------------------------
--  Constraints for Table SECURITY_GROUPS
--------------------------------------------------------
  ALTER TABLE "OE"."SECURITY_GROUPS" ADD CONSTRAINT "SG_PK" PRIMARY KEY ("SG_ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      ENABLE;

--------------------------------------------------------
--  Constraints for Table CUSTOMERS_GROUP
--------------------------------------------------------
  ALTER TABLE "OE"."CUSTOMERS_GROUP" ADD CONSTRAINT "CG_PK" PRIMARY KEY ("CSG_CUST_ID", "CSG_SG_ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      ENABLE;

--------------------------------------------------------
--  Constraints for Table CUSTOMER_PASSWORD
--------------------------------------------------------
  ALTER TABLE "OE"."CUSTOMER_PASSWORD" MODIFY ("CUSTPWD_CUSTID" NOT NULL ENABLE);
  ALTER TABLE "OE"."CUSTOMER_PASSWORD" MODIFY ("CUSTPWD_PASSWORD" NOT NULL ENABLE);
  ALTER TABLE "OE"."CUSTOMER_PASSWORD" ADD CONSTRAINT "CUSTOMER_PASSWORD_PK" PRIMARY KEY ("CUSTPWD_CUSTID", "CUSTPWD_PASSWORD")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table CUSTOMERS_GROUP
--------------------------------------------------------
  ALTER TABLE "OE"."CUSTOMERS_GROUP" ADD CONSTRAINT "CG_FK_CUSTOMERS" FOREIGN KEY ("CSG_CUST_ID")
	  REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ENABLE;
  ALTER TABLE "OE"."CUSTOMERS_GROUP" ADD CONSTRAINT "CG_SG_FK" FOREIGN KEY ("CSG_SG_ID")
	  REFERENCES "OE"."SECURITY_GROUPS" ("SG_ID") ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table CUSTOMER_PASSWORD
--------------------------------------------------------
  ALTER TABLE "OE"."CUSTOMER_PASSWORD" ADD CONSTRAINT "CUSTOMER-CUSTPWD" FOREIGN KEY ("CUSTPWD_CUSTID")
    REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ENABLE;
	  
--------------------------------------------------------
--  Populating tables with initial data
--------------------------------------------------------
INSERT INTO OE.SECURITY_GROUPS (SG_ID,SG_NAME,SG_DESCRIPTION) values (1,'OECustomers','The Security ');

-- Associating customers to the OECustomers group
INSERT INTO OE.CUSTOMERS_GROUP (CSG_CUST_ID,CSG_SG_ID) SELECT CUSTOMER_ID, 1 FROM "OE"."CUSTOMERS";
-- Defining sample password to customers
INSERT INTO OE.CUSTOMER_PASSWORD (CUSTPWD_CUSTID,CUSTPWD_PASSWORD) SELECT CUSTOMER_ID, '<PASSWORD>' FROM "OE"."CUSTOMERS";

COMMIT;