/********************* ROLES **********************/

/********************* UDFS ***********************/

/****************** GENERATORS ********************/

CREATE GENERATOR GEN_CLEARANCEDETAILS_ID;
CREATE GENERATOR GEN_CLEARANCE_ID;
CREATE GENERATOR GEN_COLLECTIONS_ID;
CREATE GENERATOR GEN_PARTICULARS_ID;
CREATE GENERATOR GEN_SUMOFCOL_N_DEP_ID;
CREATE GENERATOR GEN_SUMOFCOL_N_REMIT_ID;
CREATE GENERATOR GEN_TAXPAYER_N_COLREC_ID;
CREATE GENERATOR GEN_USERINFO_ID;
CREATE GENERATOR GEN_USERS_ID;
/******************** DOMAINS *********************/

/******************* PROCEDURES ******************/

/******************** TABLES **********************/

CREATE TABLE CLEARANCEDETAILS
(
  CLEARANCEID Integer NOT NULL,
  AGE Integer,
  GENDER Varchar(6),
  CIVILSTATUS Varchar(25),
  ADDRESS Varchar(100),
  REMARKS Varchar(100),
  PURPOSE Varchar(50),
  COLID Integer,
  PRIMARY KEY (CLEARANCEID)
);
CREATE TABLE COLLECTIONS
(
  COLID Integer NOT NULL,
  NAME Varchar(50),
  PARTNAME Varchar(25),
  TRACKING_ORNO Varchar(25),
  AMOUNTPAID Float,
  ISSUEDBY Varchar(50),
  ISSUEDDATE Date,
  P_NUM Integer,
  UI_ID Integer,
  PRIMARY KEY (COLID)
);
CREATE TABLE PARTICULARS
(
  P_NUM Integer NOT NULL,
  DESCRIPTION Varchar(50),
  AMOUNT Float,
  PARTTYPE Varchar(25),
  CREATEDBY Varchar(50),
  DATECREATED Date,
  UI_ID Integer,
  PRIMARY KEY (P_NUM)
);
CREATE TABLE USERS
(
  UI_ID Integer NOT NULL,
  IMAGE Blob sub_type 0,
  FULLNAME Varchar(50),
  DESIGNATION Varchar(25),
  ADDRESS Varchar(25),
  CONTACTNUM Varchar(15),
  USERNAME Varchar(25),
  UPASSWORD Varchar(25),
  DATECREATED Date,
  USERACCESS Varchar(25),
  STATUS Varchar(25),
  PRIMARY KEY (UI_ID)
);
/********************* VIEWS **********************/

/******************* EXCEPTIONS *******************/

/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER CHECK_25 FOR USERS ACTIVE
AFTER UPDATE POSITION 1
^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER CHECK_26 FOR USERS ACTIVE
AFTER DELETE POSITION 1
^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER CHECK_27 FOR USERS ACTIVE
AFTER UPDATE POSITION 1
^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER CHECK_28 FOR USERS ACTIVE
AFTER DELETE POSITION 1
^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER CLEARANCEDETAILS_BI FOR CLEARANCEDETAILS ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.CLEARANCEID IS NULL) THEN
    NEW.CLEARANCEID = GEN_ID(GEN_CLEARANCEDETAILS_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_CLEARANCEDETAILS_ID, 0);
    if (tmp < new.CLEARANCEID) then
      tmp = GEN_ID(GEN_CLEARANCEDETAILS_ID, new.CLEARANCEID-tmp);
  END
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER COLLECTIONS_BI FOR COLLECTIONS ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.COLID IS NULL) THEN
    NEW.COLID = GEN_ID(GEN_COLLECTIONS_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_COLLECTIONS_ID, 0);
    if (tmp < new.COLID) then
      tmp = GEN_ID(GEN_COLLECTIONS_ID, new.COLID-tmp);
  END
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER PARTICULARS_BI FOR PARTICULARS ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.P_NUM IS NULL) THEN
    NEW.P_NUM = GEN_ID(GEN_PARTICULARS_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_PARTICULARS_ID, 0);
    if (tmp < new.P_NUM) then
      tmp = GEN_ID(GEN_PARTICULARS_ID, new.P_NUM-tmp);
  END
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER USERS_BI FOR USERS ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.UI_ID IS NULL) THEN
    NEW.UI_ID = GEN_ID(GEN_USERS_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_USERS_ID, 0);
    if (tmp < new.UI_ID) then
      tmp = GEN_ID(GEN_USERS_ID, new.UI_ID-tmp);
  END
END^
SET TERM ; ^

ALTER TABLE CLEARANCEDETAILS ADD
  FOREIGN KEY (COLID) REFERENCES COLLECTIONS (COLID) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE COLLECTIONS ADD
  FOREIGN KEY (P_NUM) REFERENCES PARTICULARS (P_NUM) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE COLLECTIONS ADD
  FOREIGN KEY (UI_ID) REFERENCES USERS (UI_ID) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE PARTICULARS ADD
  FOREIGN KEY (UI_ID) REFERENCES USERS (UI_ID) ON UPDATE CASCADE ON DELETE CASCADE;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CLEARANCEDETAILS TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON COLLECTIONS TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PARTICULARS TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON USERS TO  SYSDBA WITH GRANT OPTION;

