SET SQL DIALECT 3; 

/* Generators */
CREATE GENERATOR AP_LIST_AP_ID_GEN;
CREATE GENERATOR USER_LIST_USER_ID_GEN;
CREATE GENERATOR WEB_LIST_WEB_ID_GEN;

/* Domain definitions */
CREATE DOMAIN AP_TYPE AS CHAR(1) NOT NULL;
CREATE DOMAIN COUNTER AS BIGINT
         DEFAULT 0 NOT NULL;
CREATE DOMAIN DAY_NUM AS INTEGER NOT NULL;
CREATE DOMAIN HOUR_NUM AS SMALLINT
         CHECK (VALUE BETWEEN 0 AND 23) NOT NULL;
CREATE DOMAIN ID AS INTEGER;
CREATE DOMAIN LANG_ID AS CHAR(2) NOT NULL;
CREATE DOMAIN UUID AS CHAR(35);
CREATE DOMAIN VARCHAR_128 AS VARCHAR(128);
CREATE DOMAIN VARCHAR_256 AS VARCHAR(256);

/* Tables - general lists */
CREATE TABLE AP_LIST (AP_ID ID NOT NULL,
        AP_TYPE AP_TYPE NOT NULL,
        AP_BEGIN DAY_NUM NOT NULL,
        AP_END DAY_NUM NOT NULL,
PRIMARY KEY (AP_ID));

CREATE TABLE USER_LIST (USER_ID ID NOT NULL,
        UUID UUID NOT NULL,
        USERNAME VARCHAR_256,
        FULLNAME VARCHAR_128,
        EMAIL VARCHAR_128,
        DESCRIPTION VARCHAR_256,
PRIMARY KEY (USER_ID));

/* Tables - data in/out */
CREATE TABLE DATAINOUT_H (DAY_NUM DAY_NUM NOT NULL,
        HOUR_NUM HOUR_NUM NOT NULL,
        USER_ID ID NOT NULL,
        DATA_IN COUNTER NOT NULL,
        DATA_OUT COUNTER NOT NULL,
        DATA_TOTAL COMPUTED BY (DATA_IN+DATA_OUT));

CREATE TABLE DATAINOUT_D (DAY_NUM DAY_NUM NOT NULL,
        USER_ID ID NOT NULL,
        DATA_IN COUNTER NOT NULL,
        DATA_OUT COUNTER NOT NULL,
        DATA_TOTAL COMPUTED BY (DATA_IN+DATA_OUT),
CONSTRAINT PK_DATAINOUT_D PRIMARY KEY (DAY_NUM, USER_ID));

CREATE TABLE DATAINOUT_W (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        DATA_IN COUNTER NOT NULL,
        DATA_OUT COUNTER NOT NULL,
        DATA_TOTAL COMPUTED BY (DATA_IN+DATA_OUT),
CONSTRAINT PK_DATAINOUT_W PRIMARY KEY (AP_ID, USER_ID));

CREATE TABLE DATAINOUT_M (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        DATA_IN COUNTER NOT NULL,
        DATA_OUT COUNTER NOT NULL,
        DATA_TOTAL COMPUTED BY (DATA_IN+DATA_OUT),
CONSTRAINT PK_DATAINOUT_M PRIMARY KEY (AP_ID, USER_ID));

/* Tables - ISS */
CREATE TABLE ISS_LIST (ISS_ID ID NOT NULL,
        LANG LANG_ID NOT NULL,
        ISS_NAME VARCHAR_128,
CONSTRAINT PK_ISS_LIST PRIMARY KEY (ISS_ID, LANG));

CREATE TABLE ISS_D (DAY_NUM DAY_NUM NOT NULL,
        USER_ID ID NOT NULL,
        ISS_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_ISS_D PRIMARY KEY (DAY_NUM, USER_ID, ISS_ID));

CREATE TABLE ISS_W (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        ISS_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_ISS_W PRIMARY KEY (AP_ID, USER_ID, ISS_ID));

CREATE TABLE ISS_M (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        ISS_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_ISS_M PRIMARY KEY (AP_ID, USER_ID, ISS_ID));

/* Tables - protocols */
CREATE TABLE PROTO_LIST (PROTO_ID ID NOT NULL,
        LANG LANG_ID NOT NULL,
        PROTO_NAME VARCHAR_128,
CONSTRAINT PK_PROT_LIST PRIMARY KEY (PROTO_ID, LANG));

CREATE TABLE PROTO_D (DAY_NUM DAY_NUM NOT NULL,
        USER_ID ID NOT NULL,
        PROTO_ID ID NOT NULL,
        DATA_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_PROTO_D PRIMARY KEY (DAY_NUM, USER_ID, PROTO_ID));

CREATE TABLE PROTO_W (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        PROTO_ID ID NOT NULL,
        DATA_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_PROTO_W PRIMARY KEY (AP_ID, USER_ID, PROTO_ID));

CREATE TABLE PROTO_M (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        PROTO_ID ID NOT NULL,
        DATA_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_PROTO_M PRIMARY KEY (AP_ID, USER_ID, PROTO_ID));

/* Tables - web cccess */
CREATE TABLE WEB_ACCESS_BATCHES (BATCH_NAME VARCHAR(128) CHARACTER SET NONE NOT NULL,
PRIMARY KEY (BATCH_NAME));

CREATE TABLE WEB_LIST (WEB_ID ID NOT NULL,
        WEB_NAME VARCHAR_256,
        WEB_NAME_HASH BIGINT,
CONSTRAINT PK_WEB_LIST PRIMARY KEY (WEB_ID));

CREATE TABLE WEB_ACCESS_D (DAY_NUM DAY_NUM NOT NULL,
        USER_ID ID NOT NULL,
        WEB_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_WEB_ACCESS_D PRIMARY KEY (DAY_NUM, USER_ID, WEB_ID));

CREATE TABLE WEB_ACCESS_AP (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        WEB_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_WEB_ACCESS_AP PRIMARY KEY (AP_ID, USER_ID, WEB_ID));

CREATE TABLE ALL_TOP_WEBS (AP_ID ID NOT NULL,
        WEB_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_ALL_TOP_WEBS PRIMARY KEY (AP_ID, WEB_ID));

CREATE TABLE ALL_TOP_WEBS_VISITORS (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        WEB_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_ALL_TOP_WEBS_VISITORS PRIMARY KEY (AP_ID, USER_ID, WEB_ID));

CREATE TABLE USER_TOP_WEBS (AP_ID ID NOT NULL,
        USER_ID ID NOT NULL,
        WEB_ID ID NOT NULL,
        REQUESTS_TOTAL COUNTER NOT NULL,
CONSTRAINT PK_USER_TOP_WEBS PRIMARY KEY (AP_ID, USER_ID, WEB_ID));

/*  Index definitions */
CREATE INDEX IDX_AP_LIST_BEGIN ON AP_LIST(AP_BEGIN);
CREATE INDEX IDX_AP_LIST_END ON AP_LIST(AP_END);
CREATE INDEX IDX_DATAINOUT_H ON DATAINOUT_H(DAY_NUM, USER_ID);
CREATE DESCENDING INDEX IDX_WEB_ACCESS_D_DAY_NUM ON WEB_ACCESS_D(DAY_NUM);
CREATE INDEX IDX_WEB_LIST_WEB_NAME_HASH ON WEB_LIST(WEB_NAME_HASH);
 
ALTER TABLE ALL_TOP_WEBS ADD CONSTRAINT FK_ALL_TOP_WEBS_A FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID);
 
ALTER TABLE ALL_TOP_WEBS ADD CONSTRAINT FK_ALL_TOP_WEBS_W FOREIGN KEY (WEB_ID) REFERENCES WEB_LIST (WEB_ID);
 
ALTER TABLE ALL_TOP_WEBS_VISITORS ADD CONSTRAINT FK_ALL_TOP_WEBS FOREIGN KEY (AP_ID, WEB_ID) REFERENCES ALL_TOP_WEBS (AP_ID, WEB_ID);
 
ALTER TABLE ALL_TOP_WEBS_VISITORS ADD CONSTRAINT FK_ALL_TOP_WEBS_VISITORS_U FOREIGN KEY (USER_ID) REFERENCES USER_LIST (USER_ID);
 
ALTER TABLE DATAINOUT_M ADD CONSTRAINT FK_DATAINOUT_M_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE DATAINOUT_W ADD CONSTRAINT FK_DATAINOUT_W_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE ISS_M ADD CONSTRAINT FK_ISS_M_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE ISS_W ADD CONSTRAINT FK_ISS_W_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE PROTO_M ADD CONSTRAINT FK_PROTO_M_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE PROTO_W ADD CONSTRAINT FK_PROTO_W_AP FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
 
ALTER TABLE USER_TOP_WEBS ADD CONSTRAINT FK_USER_TOP_WEBS_A FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID);
 
ALTER TABLE USER_TOP_WEBS ADD CONSTRAINT FK_USER_TOP_WEBS_U FOREIGN KEY (USER_ID) REFERENCES USER_LIST (USER_ID);
 
ALTER TABLE USER_TOP_WEBS ADD CONSTRAINT FK_USER_TOP_WEBS_W FOREIGN KEY (WEB_ID) REFERENCES WEB_LIST (WEB_ID);

SET TERM ^ ;

/* Triggers */
CREATE TRIGGER AI_AP_LIST_AP_ID FOR AP_LIST 
ACTIVE BEFORE INSERT POSITION 0 
AS
BEGIN
  IF (NEW.AP_ID IS NULL) THEN
      NEW.AP_ID = GEN_ID(AP_LIST_AP_ID_GEN, 1);
END ^
 
CREATE TRIGGER AI_USER_LIST_USER_ID FOR USER_LIST 
ACTIVE BEFORE INSERT POSITION 0 
AS
BEGIN
  IF (NEW.USER_ID IS NULL) THEN
      NEW.USER_ID = GEN_ID(USER_LIST_USER_ID_GEN, 1);
END ^

/* Procedure - AGGR_WEB_ACCESS */
CREATE PROCEDURE AGGR_WEB_ACCESS (
	USER_COUNT INTEGER,
	WEB_COUNT INTEGER,
	VISITOR_COUNT INTEGER,
	ACCESS_COUNT INTEGER)
AS 

DECLARE VARIABLE MAX_DATE INTEGER;
DECLARE VARIABLE MY_SQL VARCHAR(256);
DECLARE VARIABLE AP_ID INTEGER;
DECLARE VARIABLE USER_ID INTEGER;
DECLARE VARIABLE ALL_AP_ID INTEGER;
DECLARE VARIABLE ALL_WEB_ID INTEGER;
DECLARE VARIABLE ALL_REQUESTS_TOTAL BIGINT;

BEGIN
  SELECT MAX(DAY_NUM) FROM WEB_ACCESS_D INTO :MAX_DATE;

  FOR SELECT AP_ID FROM AP_LIST A WHERE AP_END < :MAX_DATE AND EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_AP B WHERE A.AP_ID = B.AP_ID) INTO :AP_ID DO
  BEGIN
    
    DELETE FROM USER_TOP_WEBS WHERE AP_ID = :AP_ID;

    FOR SELECT DISTINCT USER_ID FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID INTO :USER_ID DO
    BEGIN
      MY_SQL = 'INSERT INTO USER_TOP_WEBS SELECT FIRST ' || USER_COUNT || ' * FROM WEB_ACCESS_AP WHERE AP_ID = ' || AP_ID || ' AND USER_ID = ' || USER_ID || ' ORDER BY REQUESTS_TOTAL DESC';
      EXECUTE STATEMENT :MY_SQL;
    END

    
    DELETE FROM ALL_TOP_WEBS WHERE AP_ID = :AP_ID;
    DELETE FROM ALL_TOP_WEBS_VISITORS WHERE AP_ID = :AP_ID;

    MY_SQL = 'SELECT FIRST ' || WEB_COUNT || ' AP_ID, WEB_ID, SUM(REQUESTS_TOTAL) AS REQUESTS_TOTAL FROM WEB_ACCESS_AP WHERE AP_ID = ' || AP_ID || ' GROUP BY AP_ID, WEB_ID ORDER BY 3 DESC';

    FOR EXECUTE STATEMENT :MY_SQL INTO :ALL_AP_ID, :ALL_WEB_ID, :ALL_REQUESTS_TOTAL DO
    BEGIN
      INSERT INTO ALL_TOP_WEBS(AP_ID, WEB_ID, REQUESTS_TOTAL) VALUES(:ALL_AP_ID, :ALL_WEB_ID, :ALL_REQUESTS_TOTAL);

      MY_SQL = 'INSERT INTO ALL_TOP_WEBS_VISITORS SELECT FIRST ' || VISITOR_COUNT || ' * FROM WEB_ACCESS_AP WHERE AP_ID = ' || ALL_AP_ID || ' AND WEB_ID = ' || ALL_WEB_ID || ' ORDER BY REQUESTS_TOTAL DESC';
      EXECUTE STATEMENT :MY_SQL;
    END

    
    DELETE FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID;
  END

  
  DELETE FROM WEB_ACCESS_D WHERE DAY_NUM <= :MAX_DATE - :ACCESS_COUNT;
END ^

/* Procedure - GET_ALL_TOP_WEBS_AP */
CREATE PROCEDURE GET_ALL_TOP_WEBS_AP (
	AP_ID INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	WEB_ID INTEGER,
	WEB_NAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE IS_ROW SMALLINT;
DECLARE VARIABLE MY_SQL VARCHAR(256);
BEGIN
  IS_ROW = 0;
  SELECT 1 FROM RDB$DATABASE WHERE EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID) INTO :IS_ROW;

  IF (IS_ROW = 1) THEN
    MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' WEB_ID, SUM(REQUESTS_TOTAL) AS REQUESTS_TOTAL FROM WEB_ACCESS_AP WHERE AP_ID = ' || AP_ID || ' GROUP BY AP_ID, WEB_ID ORDER BY 2 DESC';
  ELSE
    MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' WEB_ID, REQUESTS_TOTAL FROM ALL_TOP_WEBS WHERE AP_ID = ' || AP_ID || ' ORDER BY REQUESTS_TOTAL DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :WEB_ID, :REQUESTS DO
  BEGIN
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      SUSPEND;
  END
END ^

/* Procedure - GET_ALL_TOP_WEBS_D */
CREATE PROCEDURE GET_ALL_TOP_WEBS_D (
	DAY_NUM INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	WEB_ID INTEGER,
	WEB_NAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE MY_SQL VARCHAR(256);
BEGIN
  MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' WEB_ID, SUM(REQUESTS_TOTAL) AS REQUESTS_TOTAL FROM WEB_ACCESS_D WHERE DAY_NUM = ' || DAY_NUM || ' GROUP BY DAY_NUM, WEB_ID ORDER BY 2 DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :WEB_ID, :REQUESTS DO
  BEGIN
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      SUSPEND;
  END
END ^

/* Procedure - GET_ALL_TOP_WEBS_VISITORS_AP */
CREATE PROCEDURE GET_ALL_TOP_WEBS_VISITORS_AP (
	AP_ID INTEGER,
	WEB_ID INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	USER_ID INTEGER,
	USERNAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE IS_ROW SMALLINT;
DECLARE VARIABLE TABLE_NAME VARCHAR(50);
DECLARE VARIABLE MY_SQL VARCHAR(256);
BEGIN
  IS_ROW = 0;
  SELECT 1 FROM RDB$DATABASE WHERE EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID AND WEB_ID = :WEB_ID) INTO :IS_ROW;

  IF (IS_ROW = 1) THEN
    TABLE_NAME = 'WEB_ACCESS_AP';
  ELSE
    TABLE_NAME = 'ALL_TOP_WEBS_VISITORS';
    
  MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' USER_ID, REQUESTS_TOTAL FROM ' || TABLE_NAME || ' WHERE AP_ID = ' || AP_ID || ' AND WEB_ID = ' || WEB_ID || ' ORDER BY REQUESTS_TOTAL DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :USER_ID, :REQUESTS DO
  BEGIN
      SELECT USERNAME FROM USER_LIST WHERE USER_ID = :USER_ID INTO :USERNAME;
      SUSPEND;
  END
END ^

/* Procedure - GET_ALL_TOP_WEBS_VISITORS_D */
CREATE PROCEDURE GET_ALL_TOP_WEBS_VISITORS_D (
	DAY_NUM INTEGER,
	WEB_ID INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	USER_ID INTEGER,
	USERNAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE MY_SQL VARCHAR(256);
BEGIN
  MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' USER_ID, REQUESTS_TOTAL FROM WEB_ACCESS_D WHERE DAY_NUM = ' || DAY_NUM || ' AND WEB_ID = ' || WEB_ID || ' ORDER BY REQUESTS_TOTAL DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :USER_ID, :REQUESTS DO
  BEGIN
      SELECT USERNAME FROM USER_LIST WHERE USER_ID = :USER_ID INTO :USERNAME;
      SUSPEND;
  END
END ^

/* Procedure - GET_USER_TOP_WEBS_AP */
CREATE PROCEDURE GET_USER_TOP_WEBS_AP (
	AP_ID INTEGER,
	USER_ID INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	WEB_ID INTEGER,
	WEB_NAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE IS_ROW SMALLINT;
DECLARE VARIABLE MY_SQL VARCHAR(256);
DECLARE VARIABLE TABLE_NAME VARCHAR(50);
BEGIN
  IS_ROW = 0;
  SELECT 1 FROM RDB$DATABASE WHERE EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID AND USER_ID = :USER_ID) INTO :IS_ROW;

  IF (IS_ROW = 1) THEN
    TABLE_NAME = 'WEB_ACCESS_AP';
  ELSE
    TABLE_NAME = 'USER_TOP_WEBS';
    
  MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' WEB_ID, REQUESTS_TOTAL FROM ' || TABLE_NAME || ' WHERE AP_ID = ' || AP_ID || ' AND USER_ID = ' || USER_ID || ' ORDER BY REQUESTS_TOTAL DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :WEB_ID, :REQUESTS DO
  BEGIN
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      SUSPEND;
  END
END ^

/* Procedure - GET_USER_TOP_WEBS_D */
CREATE PROCEDURE GET_USER_TOP_WEBS_D (
	DAY_NUM INTEGER,
	USER_ID INTEGER,
	MAX_COUNT INTEGER)
RETURNS (
	WEB_ID INTEGER,
	WEB_NAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	REQUESTS BIGINT)
AS 

DECLARE VARIABLE MY_SQL VARCHAR(256);
BEGIN
  MY_SQL = 'SELECT FIRST ' || MAX_COUNT || ' WEB_ID, REQUESTS_TOTAL FROM WEB_ACCESS_D WHERE DAY_NUM = ' || DAY_NUM || ' AND USER_ID = ' || USER_ID || ' ORDER BY REQUESTS_TOTAL DESC';

  FOR EXECUTE STATEMENT :MY_SQL INTO :WEB_ID, :REQUESTS DO
  BEGIN
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      SUSPEND;
  END
END ^

/* Procedure - INSERT_WEB_ACCESS */
CREATE PROCEDURE INSERT_WEB_ACCESS (
	DAY_NUM INTEGER,
	USER_ID INTEGER,
	WEB_NAME VARCHAR(256) CHARACTER SET UNICODE_FSS,
	WEB_NAME_HASH BIGINT,
	REQUESTS_TOTAL BIGINT)
AS 

DECLARE VARIABLE WEB_ID INTEGER;
DECLARE VARIABLE AP_ID INTEGER;
DECLARE VARIABLE IS_ROW SMALLINT;
BEGIN
  
  WEB_ID = -1;
  SELECT WEB_ID FROM WEB_LIST WHERE WEB_NAME_HASH = :WEB_NAME_HASH AND WEB_NAME = :WEB_NAME INTO :WEB_ID;

  IF (WEB_ID = -1) THEN
  BEGIN
    WEB_ID = GEN_ID(WEB_LIST_WEB_ID_GEN, 1);
    INSERT INTO WEB_LIST(WEB_ID, WEB_NAME, WEB_NAME_HASH) VALUES(:WEB_ID, :WEB_NAME, :WEB_NAME_HASH);
  END

  
  IS_ROW = 0;
  SELECT 1 FROM RDB$DATABASE WHERE EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_D WHERE DAY_NUM = :DAY_NUM AND USER_ID = :USER_ID AND WEB_ID = :WEB_ID) INTO :IS_ROW;

  IF (IS_ROW = 1) THEN
    UPDATE WEB_ACCESS_D SET REQUESTS_TOTAL = REQUESTS_TOTAL + :REQUESTS_TOTAL WHERE DAY_NUM = :DAY_NUM AND USER_ID = :USER_ID AND WEB_ID = :WEB_ID;
  ELSE
    INSERT INTO WEB_ACCESS_D(DAY_NUM, USER_ID, WEB_ID, REQUESTS_TOTAL) VALUES(:DAY_NUM, :USER_ID, :WEB_ID, :REQUESTS_TOTAL);

  
  FOR SELECT AP_ID FROM AP_LIST WHERE AP_BEGIN <= :DAY_NUM AND AP_END >= :DAY_NUM INTO :AP_ID DO
  BEGIN
    IS_ROW = 0;
    SELECT 1 FROM RDB$DATABASE WHERE EXISTS(SELECT FIRST 1 * FROM WEB_ACCESS_AP WHERE AP_ID = :AP_ID AND USER_ID = :USER_ID AND WEB_ID = :WEB_ID) INTO :IS_ROW;

    IF (IS_ROW = 1) THEN
      UPDATE WEB_ACCESS_AP SET REQUESTS_TOTAL = REQUESTS_TOTAL + :REQUESTS_TOTAL WHERE AP_ID = :AP_ID AND USER_ID = :USER_ID AND WEB_ID = :WEB_ID;
    ELSE
      INSERT INTO WEB_ACCESS_AP(AP_ID, USER_ID, WEB_ID, REQUESTS_TOTAL) VALUES(:AP_ID, :USER_ID, :WEB_ID, :REQUESTS_TOTAL);
  END
END ^

/* Procedure - USER_LIST_USER_ID_AUTOINC */
CREATE PROCEDURE USER_LIST_USER_ID_AUTOINC
RETURNS (
	NEW_VALUE INTEGER)
AS 

BEGIN
  NEW_VALUE = GEN_ID(USER_LIST_USER_ID_GEN, 1);
END ^

/* Procedure - SCHEMA_VERSION */
CREATE PROCEDURE SCHEMA_VERSION
RETURNS (
	CURRENT_VERSION DOUBLE PRECISION,
	MINIMUM_VERSION DOUBLE PRECISION)
AS 

BEGIN
  CURRENT_VERSION = 1.00;
  MINIMUM_VERSION = 1.00;
  SUSPEND;
END ^

SET TERM ; ^

