dbscr.motohr.init1:

  - if not has_domain(, JROBO_MOTOHR)
 
  - do CREATE DOMAIN JROBO_MOTOHR AS INTEGER
 
  # TABLES
  - |
    do CREATE TABLE MH_COUNTER_TYPE (
        ID       INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_COUNTERTYPE PRIMARY KEY,
        DESCR    VARCHAR(128),
        TIMER    SMALLINT DEFAULT 0,
        DELETED  SMALLINT DEFAULT 0
      )
  - |
    do ALTER TABLE MH_COUNTER_TYPE ALTER ID RESTART WITH 1

  - |
    do CREATE TABLE MH_MACH (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_MACH PRIMARY KEY,
        NAME        VARCHAR(128) NOT NULL CONSTRAINT MH_UQ_MACH_NAME UNIQUE,
        TAGNAME     VARCHAR(128),
        DESCR       VARCHAR(128)
      )

  - |
    do CREATE TABLE MH_COUNTER (
        ID              INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_COUNTER PRIMARY KEY,
        MACH_ID         INTEGER CONSTRAINT MH_FK_COUNTER_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE,
        COUNTER_TYPE_ID INTEGER CONSTRAINT MH_FK_COUNTER_TYPE REFERENCES MH_COUNTER_TYPE ON DELETE CASCADE ON UPDATE CASCADE,
        SEC             INTEGER DEFAULT 2147483647,
        DELETED         SMALLINT DEFAULT 0
      )

  - |
    do CREATE TABLE MH_MACH_STATE (
        MACH_ID     INTEGER CONSTRAINT MH_FK_MACHSTATE_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE,
        DT          TIMESTAMP,
        RUNNING     SMALLINT,
        STATCNT     INTEGER,
        STATSEC     INTEGER
      )

  - |
    do CREATE TABLE MH_STAT (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_STAT PRIMARY KEY,
        MACH_ID     INTEGER CONSTRAINT MH_FK_STAT_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE,
        PERIOD      INTEGER,
        CNT         INTEGER,
        SEC         INTEGER
      )

  - |
    do CREATE TABLE MH_INTERVAL (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_INVERVAL PRIMARY KEY,
        MACH_ID     INTEGER CONSTRAINT MH_FK_INTERVAL_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE,
        DTBEG       TIMESTAMP,
        DTEND       TIMESTAMP,
        SEC         INTEGER
      )

  - |
    do CREATE TABLE MH_LOGBOOK (
        ID          INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_LOGBOOK PRIMARY KEY,
        MACH_ID     INTEGER CONSTRAINT MH_FK_LOGBOOK_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE,
        DT          TIMESTAMP,
        TEXT        VARCHAR(512),
        FIO         VARCHAR(64)
      )
 

  # INDEXES
  - do CREATE INDEX MH_IX_STAT_PERIOD ON MH_STAT (PERIOD)
  - do CREATE INDEX MH_IX_COUNTER_MACHTYPE ON MH_COUNTER (MACH_ID, COUNTER_TYPE_ID)
 
  # FUNCTIONS
  - |
    do CREATE FUNCTION MH_CNT_SEC (
      SEC INTEGER,
      DT TIMESTAMP,
      RUNNING SMALLINT,
      TIMER SMALLINT)
    RETURNS INTEGER
    AS
    BEGIN
      IF (SEC = 2147483647) THEN
        RETURN SEC;
      RETURN  CAST(SEC + IIF(TIMER = 0, 1, -1) * IIF(RUNNING = 0, 0, (CURRENT_TIMESTAMP - DT) * 86400) AS INTEGER);
    END

  # VIEWS
  - |
    do CREATE VIEW MH_COUNTER_CALC
    AS
    SELECT
      M.ID MACH_ID,
      C.ID COUNTER_ID,
      CT.ID COUNTER_TYPE_ID,
      M.NAME MACH_NAME,
      M.DESCR MACH_DESCR,
      MS.RUNNING RUNNING,
      CT.DESCR COUNTER_DESCR,
      CT.TIMER TIMER,
      MH_CNT_SEC(C.SEC, MS.DT, MS.RUNNING, CT.TIMER) SEC
    FROM MH_COUNTER C
    JOIN MH_COUNTER_TYPE CT ON CT.ID = C.COUNTER_TYPE_ID
    JOIN MH_MACH M ON M.ID = C.MACH_ID
    JOIN MH_MACH_STATE MS ON MS.MACH_ID = M.ID
    WHERE
      C.DELETED=0
    --
    -- Usage example (clause PLAN is optional and used for the best perfomance):
    --
    -- select * from mh_pivot p
    -- where counter_name = 'total' and mach_name = 'M1001'
    -- plan join (
    --   p m index (mh_uq_mach_name),
    --   p ct index(mh_uq_countertype_name),
    --   p c index(mh_ix_counter_machtype),
    --   p ms index (mh_fk_machstate_mach))