-- invfifo is a shadow table to invdetail which contains the fifo data.




 
create table invfifo (
    invfifo_invdetail_id integer NOT NULL UNIQUE,
    
    -- unit is excluding shipping (it's mostly ignored now)
    invfifo_unitcost   numeric(18, 6)  NOT NULL,
    invfifo_landedunitcost NUMERIC(18,6) DEFAULT 0.0 NOT NULL,
    
    -- not used??
    invfifo_totalcost  numeric(18, 6)  NOT NULL,
    
    -- key columns which the calculation is done on .
    invfifo_qty_before  numeric(18, 6)  NOT NULL,
    invfifo_qty_after  numeric(18, 6)  NOT NULL,
    invfifo_cost_before  numeric(18, 6)  NOT NULL,
    invfifo_cost_after  numeric(18, 6)  NOT NULL,
    
    -- flags .. not used at present.. may not be in futer
    invfifo_is_estimate boolean  NOT NULL,
    invfifo_recalc_queued boolean  NOT NULL,
    
    -- this is used to determine if update_from_invdetail has been run.
     
    
    -- just stores cust/vend to save looking it up later.
    invfifo_vend_id INT DEFAULT 0,
    invfifo_cust_id INT DEFAULT 0,
    invfifo_cohead_id INT NOT NULL DEFAULT 0,
    -- FIXME - cohead_id might be a good idea here...
    
    
    -- is the transaction a reversal of another one - so has $0 effect on FIFO.
    -- should point to the invdetail_id of what it reverses, or -1 for random flagging...
    invfifo_void           INTEGER NOT NULL DEFAULT 0,
    
    CONSTRAINT  invfifo_invdetail_fk FOREIGN KEY ( invfifo_invdetail_id) REFERENCES invdetail (invdetail_id) 
   
);
alter table invfifo add column invfifo_landedunitcost NUMERIC(18,6) DEFAULT 0.0 NOT NULL;
-- void of which record.
alter table invfifo add column invfifo_void           INTEGER NOT NULL DEFAULT 0;
alter table invfifo add column invfifo_cohead_id INT NOT NULL DEFAULT 0;

GRANT ALL ON TABLE invfifo TO admin;
GRANT ALL ON TABLE invfifo TO xtrole;


CREATE INDEX invfifo_qty_before_indx
  ON invfifo
  USING btree
  (invfifo_qty_before);

CREATE INDEX invfifo_qty_after_indx
  ON invfifo
  USING btree
  (invfifo_qty_after);

CREATE INDEX invfifo_is_estimate_indx
  ON invfifo
  USING btree
  (invfifo_is_estimate);
  
CREATE INDEX invfifo_recalc_queued_indx
  ON invfifo
  USING btree
  (invfifo_recalc_queued);

CREATE INDEX invfifo_cust_id_indx
  ON invfifo
  USING btree
  (invfifo_cust_id);
 
CREATE INDEX invfifo_vend_id_indx
  ON invfifo
  USING btree
  (invfifo_vend_id); 
   
 CREATE INDEX invfifo_qty_before_indx
  ON invfifo
  USING btree
  (invfifo_qty_before);

CREATE INDEX invfifo_void_indx
  ON invfifo
  USING btree
  (invfifo_void);

CREATE INDEX invfifo_cohead_id_indx
  ON invfifo
  USING btree
  (invfifo_cohead_id);

alter table invfifo drop column invfifo_coitem_id;
 

 


-- update /inserts tirgger invfifo_fill()

CREATE OR REPLACE FUNCTION invdetailtriggerfifo() RETURNS trigger 
AS $BODY$
DECLARE
     
BEGIN
    
    PERFORM invfifo_fill(NEW.invdetail_id);
      
    RETURN NEW;   
        
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invdetailtriggerfifo()
  OWNER TO admin;
          



CREATE TRIGGER _invdetailtriggerfifo 
    AFTER INSERT ON invdetail
        FOR EACH ROW EXECUTE PROCEDURE invdetailtriggerfifo();
        

CREATE OR REPLACE FUNCTION invfifotriggerfifo() RETURNS trigger 
AS $BODY$
DECLARE
     
BEGIN
    
    IF (NEW.invfifo_void IS NOT NULL AND NEW.invfifo_void < 0 ) THEN
        RAISE EXCEPTION 'Voiding a transaction needs to know what was voided invdetail_id= %',  NEW.invfifo_invdetail_id ;
    END IF; 
    RETURN NEW;   
        
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invdetailtriggerfifo()
  OWNER TO admin;
          

 
CREATE TRIGGER _invfifotriggerfifo 
    AFTER INSERT OR UPDATE ON invfifo
        FOR EACH ROW EXECUTE PROCEDURE invfifotriggerfifo();
        

