@echo Set view NODE_GOOD⠒V up;

create or replace view NODE_GOOD⠒V as
      select N.NODE⠒NAME,
             -- N.QUANTITY             as QUANTITY_AVAILABLE_AT_NODE,
             G.GOOD⠒NAME,
             NG.QUANTITY,
             NG.TASK_NAME,
             N.CENTRICITY⠒NAME,
             N.DIRECTION⠒CODE,
             N.NODE_TYPE⠒NAME,
             N.MAP⠒NAME,
             N.MAP⠒CODE,
             N.REGION⠒NAME,
             G.GOOD_CLASS⠒NAME,
             G.SIZE,
             G.WEIGHT,
             NG.DESCRIPTION         as NODE_GOOD⠒DESCRIPTION,
             N.NODE⠒DESCRIPTION,
             N.NODE_TYPE⠒DESCRIPTION,
             N.CENTRICITY⠒DESCRIPTION,
             N.DIRECTION⠒DESCRIPTION,
             N.AREA⠒DESCRIPTION,
             N.MAP⠒DESCRIPTION,
             N.REGION⠒DESCRIPTION,
             G.GOOD⠒DESCRIPTION,
             G.GOOD_CLASS⠒DESCRIPTION,
             NG.ID,
             NG.NODE⠒ID,
             NG.GOOD⠒ID,
             N.NODE_TYPE⠒ID,
             N.CENTRICITY⠒ID,
             N.DIRECTION⠒ID,
             N.AREA⠒ID,
             N.MAP⠒ID,
             N.REGION⠒ID,
             G.GOOD_CLASS⠒ID
        from NODE_GOOD NG
  inner join NODE⠒V N
          on NG.NODE⠒ID = N.ID
  inner join GOOD⠒V G
          on NG.GOOD⠒ID = G.ID;


create or replace rule NODE_GOOD⠒R_I as
  on insert
  to NODE_GOOD⠒V
  do instead
    insert into NODE_GOOD (NODE⠒ID,
                           GOOD⠒ID,
                           QUANTITY,
                           TASK_NAME,
                           DESCRIPTION)
        values (/* NODE⠒ID */           case
                                           -- ID given
                                           when new.NODE⠒ID is not null then new.NODE⠒ID
                                           -- names or codes given
                                           when new.NODE⠒NAME is not null
                                            and (   new.MAP⠒NAME is not null
                                                 or new.MAP⠒CODE is not null)
                                            and new.REGION⠒NAME is not null then (select ID
                                                                                    from NODE⠒V
                                                                                   where NODE⠒NAME = new.NODE⠒NAME
                                                                                     and NODE_TYPE⠒NAME = 'Crafting zone'
                                                                                     and (   MAP⠒NAME = new.MAP⠒NAME
                                                                                          or MAP⠒CODE = new.MAP⠒CODE)
                                                                                     and REGION⠒NAME = new.REGION⠒NAME)
                                           else null
                                        end,
                /* GOOD⠒ID */           case
                                            -- ID given
                                            when new.GOOD⠒ID is not null then new.GOOD⠒ID
                                            -- name given
                                            when new.GOOD⠒NAME is not null then (select ID
                                                                                   from GOOD⠒V
                                                                                  where GOOD⠒NAME = new.GOOD⠒NAME)
                                            else null
                                        end,
                /* QUANTITY */          new.QUANTITY,
                /* TASK_NAME */         new.TASK_NAME,
                /* DESCRIPTION */       new.NODE_GOOD⠒DESCRIPTION);

comment on rule NODE_GOOD⠒R_I on NODE_GOOD⠒V is
  'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion';

create or replace trigger NODE_GOOD⠒V⠒TR_B_DU_S
  before delete or update
  on NODE_GOOD⠒V
  for each statement
  execute function REJECT_OPERATION();


comment on column NODE_GOOD⠒V.QUANTITY is
  'Number of the good that initially can be picked up or must be dropped off depending on the node type. For crafting zones it is a pick-up number.';

comment on view NODE_GOOD⠒V is
  '
$Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE_GOOD%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $';


commit;