SET SQL DIALECT 3; 

ALTER TABLE ALL_TOP_WEBS DROP CONSTRAINT FK_ALL_TOP_WEBS_A;
ALTER TABLE ALL_TOP_WEBS DROP CONSTRAINT FK_ALL_TOP_WEBS_W;
ALTER TABLE ALL_TOP_WEBS_VISITORS DROP CONSTRAINT FK_ALL_TOP_WEBS;
ALTER TABLE ALL_TOP_WEBS_VISITORS DROP CONSTRAINT FK_ALL_TOP_WEBS_VISITORS_U;
ALTER TABLE USER_TOP_WEBS DROP CONSTRAINT FK_USER_TOP_WEBS_A;
ALTER TABLE USER_TOP_WEBS DROP CONSTRAINT FK_USER_TOP_WEBS_U;
ALTER TABLE USER_TOP_WEBS DROP CONSTRAINT FK_USER_TOP_WEBS_W;

ALTER TABLE ALL_TOP_WEBS ADD CONSTRAINT FK_ALL_TOP_WEBS_A FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;
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) ON DELETE CASCADE;
ALTER TABLE USER_TOP_WEBS ADD CONSTRAINT FK_USER_TOP_WEBS_A FOREIGN KEY (AP_ID) REFERENCES AP_LIST (AP_ID) ON DELETE CASCADE;

SET TERM ^ ;

ALTER 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
  MAX_DATE = -1;
  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^

ALTER 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
      WEB_NAME = NULL;
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      
      SUSPEND;
  END
END^

ALTER 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
      WEB_NAME = NULL;
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      
      SUSPEND;
  END
END^

ALTER 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
      USERNAME = NULL;
      SELECT USERNAME FROM USER_LIST WHERE USER_ID = :USER_ID INTO :USERNAME;
      
      SUSPEND;
  END
END^

ALTER 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
      USERNAME = NULL;
      SELECT USERNAME FROM USER_LIST WHERE USER_ID = :USER_ID INTO :USERNAME;
      
      SUSPEND;
  END
END^

ALTER 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
      WEB_NAME = NULL;
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      
      SUSPEND;
  END
END^

ALTER 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
      WEB_NAME = NULL;
      SELECT WEB_NAME FROM WEB_LIST WHERE WEB_ID = :WEB_ID INTO :WEB_NAME;
      
      SUSPEND;
  END
END^

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

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

SET TERM ; ^

