/*****************************************************************************
*
* Filename         - $Workfile:   message_parts_triggers.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         -  Live Schema: triggers for message_parts table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

CREATE OR REPLACE TRIGGER ti_message_parts
BEFORE INSERT ON message_parts
FOR EACH ROW
DECLARE

  l_fields             process_message.t_field_list;
  l_element            VARCHAR2(1);
  l_type               VARCHAR2(3);
  l_primary_type       VARCHAR2(3);
  l_version            VARCHAR2(5)                     := NULL;
  l_parts              NUMBER                          := 0;
  l_parts_received     NUMBER                          := 0;
  l_part_number        NUMBER                          := 1;
  l_number_parts       NUMBER                          := 0;
  l_record             VARCHAR2(64)                    := NULL;
  l_target             site_info.airport_code%TYPE     := NULL;
  l_flight_no          flight.flight_no%TYPE           := NULL;
  l_flight_date        flight.flight_date%TYPE         := NULL;
  l_ib_airport         site_info.airport_code%TYPE     := NULL;
  l_flight_info_record VARCHAR2(1)                     := 'F';
  l_direction          messages.message_direction%TYPE := 'O';
  l_message_id         messages.message_id%TYPE;
  l_message_end        VARCHAR2(4000);

BEGIN

   -- Notes
   -- =====
   -- A valid messages will meet the following criteria
   --   1) No routing information is included before the message
   --   2) The message starts with a Standard Message Identifier e.g. BMM
   --   3) Every message record is terminated by CRLF or LF
   --   4) The message ends with an End of Message Identifier or End of Partition Identifier
   --   5) A valid .V record exists
   --
   -- For an Outgoing message, the associated MESSAGES record should already have been
   -- created and the message_id set accordingly.  No amendments will be made to the
   -- MESSAGES.PARTS or MESSAGES.PARTS_RECEIVED columns.
   --
   -- For an Incoming message, no message_id will be defined and the MESSAGES record may or may not
   -- exist already.

   -- Assign an ID for the message part
   SELECT message_part_id_seq.NEXTVAL INTO :NEW.message_part_id FROM dual;

   -- Set the timestamp
   :NEW.timestamp := SYSDATE;

   -- Trim and Validate the message
   :NEW.message_text1 := TRIM(REPLACE(:NEW.message_text1, CHR(13)));
   :NEW.message_text2 := TRIM(REPLACE(:NEW.message_text2, CHR(13)));

   -- Get Standard Message Identifier
   l_primary_type := SUBSTR(:NEW.message_text1, 1, 3);
   l_type := l_primary_type;
   IF (l_primary_type = 'BCM') THEN

     -- Get the Secondary Level Message Identifier
     l_type := SUBSTR(:NEW.message_text1, INSTR(:NEW.message_text1, CHR(10)) + 1, 3);

   END IF;

   -- If this is an incoming message then extra analysis required
   IF (NVL(:NEW.message_id, 0) = 0) THEN

     -- Extract the .V record
     l_record := process_message.find_record(:NEW.message_text1, :NEW.message_text2, 'V');

     -- Get the fields
     process_message.split_message_line(l_record, l_fields, l_element);

     -- Check for useful data
     IF (l_fields.COUNT > 0) THEN

       l_version := l_fields(1);
       l_target  := SUBSTR(l_fields(1), 3);

     ELSE

       RAISE process_message.INVALID_MESSAGE;

     END IF;

     IF (l_fields.COUNT > 1 AND SUBSTR(l_fields(2), 1, 4) = 'PART') THEN

       l_part_number := TO_NUMBER(SUBSTR(l_fields(2), 5));

     END IF;
     :NEW.part_no := l_part_number;

     -- Extract the Flight Information record
     IF (l_type IN ('BTM','BMM')) THEN

       l_flight_info_record := 'I';

     END IF;
     l_record := process_message.find_record(:NEW.message_text1, :NEW.message_text2, l_flight_info_record);

     -- Get the fields
     process_message.split_message_line(l_record, l_fields, l_element);

     -- Check for useful data
     IF (l_fields.COUNT >= 3) THEN

       l_flight_no := normalise_flight_no(l_fields(1));
       l_flight_date := l_fields(2);
       l_ib_airport := l_fields(3);

     END IF;

     -- Message Direction - Must be Inbound since Outbound messages will already be defined
     l_direction := 'I';

     -- Locate the parent message
     BEGIN

       SELECT m.message_id,
              m.parts,
              m.parts_received
       INTO   l_message_id,
              l_parts,
              l_parts_received
       FROM   messages m
       WHERE  m.message_type = l_type
         AND  m.message_direction = l_direction
         AND  m.version = l_version
         AND  m.flight_number = l_flight_no
         AND  m.flight_date = l_flight_date
         AND  m.originating_airport = l_ib_airport
         AND  m.parts <> m.parts_received
        AND  m.processed_state = 'U'
         AND  NOT EXISTS (SELECT 1
                          FROM   message_parts p
                          WHERE  p.message_id = m.message_id
                            AND  p.part_no = l_part_number);

     EXCEPTION

       WHEN no_data_found THEN

         l_message_id := NULL;
         l_parts := 0;
         l_parts_received := 0;

     END;

     IF (NVL(l_message_id, 0) = 0) THEN

       -- Create a new parent message
       INSERT INTO messages
       (
            message_type,
            message_direction,
            parts,
            version,
            flight_number,
            flight_date,
            originating_airport
       ) VALUES (
            l_type,
            l_direction,
            l_part_number,
            l_version,
            l_flight_no,
            l_flight_date,
            l_ib_airport
       ) RETURNING message_id INTO l_message_id;

     END IF;

     -- Set any existing incomplete messages to ERROR
     UPDATE messages
     SET    processed_state = 'E'
     WHERE  message_type = l_type
       AND  message_direction = l_direction
       AND  version = l_version
       AND  flight_number = l_flight_no
       AND  flight_date = l_flight_date
       AND  originating_airport = l_ib_airport
       AND  parts <> parts_received
       AND  processed_state = 'U'
       AND  message_id <> l_message_id;

     :NEW.message_id := l_message_id;

     -- Get the end of message identifier
     l_message_end := :NEW.message_text1;
     IF (:NEW.message_text2 IS NOT NULL) THEN

       l_message_end := SUBSTR(:NEW.message_text1, LENGTH(:NEW.message_text1) - 64) || :NEW.message_text2;

     END IF;

     -- Is this the final part?
     l_parts_received := l_parts_received + 1;
     IF (INSTR(l_message_end,'END' || l_primary_type,-1,1) > 0) THEN

       l_number_parts := l_part_number;

     ELSIF (INSTR(l_message_end,'ENDPART' || TRIM(TO_CHAR(l_part_number)),-1,1)>0) THEN

       l_number_parts := GREATEST(l_parts, l_part_number + 1);

     ELSE

       -- Check for special case of a BCM(BMM) message
       IF (l_primary_type = 'BMM' AND INSTR(l_message_end,'ENDBCM',-1,1) > 0) THEN

         l_number_parts := l_part_number;

       ELSE

         RAISE process_message.INVALID_MESSAGE;

       END IF;

     END IF;

     -- Add to the message
     UPDATE messages
     SET    parts = l_number_parts,
            parts_received = l_parts_received
     WHERE  message_id = l_message_id;

     -- Process the Message (if complete and incoming)
     IF (l_parts_received = l_number_parts) THEN

       IF (l_type = 'BMM') THEN

         -- Add an INTERIM record (type 9) to allow BMM to be processed in AFTER STATEMENT trigger
         -- This eliminates the possibility of Mutating Table errors
         INSERT INTO interim (id, numeric) values (9, :NEW.message_id);

       END IF;

     END IF;

   END IF;

END;
/

CREATE OR REPLACE TRIGGER tu_message_parts
BEFORE UPDATE ON message_parts
BEGIN
  -- Not allowed updates on this table
  RAISE_APPLICATION_ERROR(-20788, 'Updates on MESSAGE_PARTS table not allowed');
END;
/

CREATE OR REPLACE TRIGGER tias_message_parts
AFTER INSERT ON message_parts
DECLARE

  l_success            BOOLEAN;
  l_target             site_info.airport_code%TYPE;

  CURSOR c_interim9 IS
         SELECT id,
                numeric
         FROM   interim
         WHERE  id = 9;
BEGIN

  FOR r_bmm IN c_interim9 LOOP

    -- Fetch the target airport for this message
    SELECT SUBSTR(version, 3)
    INTO   l_target
    FROM   messages
    WHERE  message_id = r_bmm.numeric;

    -- Add a Trace record for a valid receive
    process_message.add_message_trace(r_bmm.numeric, NULL, 'MESSAGE_CREATE',
             l_target, NULL, multi_site.get_auto_user(l_target));

    process_message.decode_bmm(r_bmm.numeric, l_success);

  END LOOP;

  DELETE FROM interim WHERE id = 9;

END;
/

