CREATE TABLE HORSE (
    CODE_HORSE      INTEGER NOT NULL,
    CODE_FATHER     INTEGER DEFAULT -2 NOT NULL,
    CODE_MOTHER     INTEGER DEFAULT -3 NOT NULL,
    NAME            VARCHAR(50)
);

ALTER TABLE HORSE ADD CONSTRAINT PK_HORSE PRIMARY KEY (CODE_HORSE);
ALTER TABLE HORSE ADD CONSTRAINT FK_HORSE_REF_FATHER FOREIGN KEY (CODE_FATHER) REFERENCES HORSE (CODE_HORSE) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE HORSE ADD CONSTRAINT FK_HORSE_REF_MOTHER FOREIGN KEY (CODE_MOTHER) REFERENCES HORSE (CODE_HORSE) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

EXECUTE BLOCK
RETURNS (
    DEPTH INTEGER,
    MARK VARCHAR(20),
    CODE_HORSE INTEGER,
    IMBRID INTEGER,
    IMBRID_COLOR INTEGER,
    IMBRID_VALUE NUMERIC(15,6))
AS
DECLARE VARIABLE A_CODE_HORSE INTEGER = 742363;
DECLARE VARIABLE A_MAX_DEPTH INTEGER = 7;
DECLARE VARIABLE X_CODE_HORSE INTEGER;
DECLARE VARIABLE DEPTH_F INTEGER;
DECLARE VARIABLE DEPTH_M INTEGER;
DECLARE VARIABLE COUNTER INTEGER;
DECLARE VARIABLE COLOR INTEGER;
BEGIN
  /*
   * Процедура извлечения педигри
   * Developer: Симонов Денис
   * Copyright © 2011 Silentium
   */
  IMBRID_VALUE = 0;
  COUNTER = 0;
  DELETE FROM PEDIGREE_TMP WHERE CODE_ROOT_HORSE = :CODE_HORSE;
  INSERT INTO PEDIGREE_TMP (CODE_ROOT_HORSE,
                            MARK,
                            CODE_HORSE,
                            DEPTH,
                            IMBRID,
                            IMBRID_VALUE,
                            IMBRID_COLOR)
  WITH RECURSIVE PEDIGREE (
      CODE_ROOT_HORSE,
      CODE_HORSE,
      MARK,
      DEPTH)
  AS (SELECT HORSE.CODE_HORSE,
             HORSE.CODE_HORSE,
             CAST('' AS VARCHAR(80)),
             0
      FROM HORSE
      WHERE HORSE.CODE_HORSE = :A_CODE_HORSE
      UNION ALL
      SELECT PEDIGREE.CODE_ROOT_HORSE,
             HORSE.CODE_FATHER,
             'F' || PEDIGREE.MARK,
             PEDIGREE.DEPTH + 1
      FROM HORSE
      INNER JOIN PEDIGREE ON HORSE.CODE_HORSE = PEDIGREE.CODE_HORSE
      WHERE PEDIGREE.DEPTH < :A_MAX_DEPTH
      UNION ALL
      SELECT PEDIGREE.CODE_ROOT_HORSE,
             HORSE.CODE_MOTHER,
             'M' || PEDIGREE.MARK,
             PEDIGREE.DEPTH + 1
      FROM HORSE
      INNER JOIN PEDIGREE ON HORSE.CODE_HORSE = PEDIGREE.CODE_HORSE
      WHERE PEDIGREE.DEPTH < :A_MAX_DEPTH)
  SELECT CODE_ROOT_HORSE,
         MARK,
         CODE_HORSE,

         DEPTH,
         0,
         0,
         0
  FROM PEDIGREE;

  FOR WITH H (
          CODE_HORSE,
          DEPTH,
          MARK,
          S,
          PREV_MARK)
      AS (SELECT CODE_HORSE,
                 DEPTH,
                 MARK,
                 SUBSTRING(MARK FROM CHAR_LENGTH(MARK) FOR 1) AS S,
                 CASE
                   WHEN DEPTH > 1
                   THEN SUBSTRING(MARK FROM 2 FOR CHAR_LENGTH(MARK))
                 END AS PREV_MARK
          FROM PEDIGREE_TMP
          WHERE CODE_ROOT_HORSE = :A_CODE_HORSE AND
                CODE_HORSE > 0 AND
                DEPTH > 0)
      SELECT F1.CODE_HORSE,
             F1.DEPTH AS F_DEPTH,
             M1.DEPTH AS M_DEPTH
      FROM H F1
      INNER JOIN H M1 ON F1.CODE_HORSE = M1.CODE_HORSE
      WHERE F1.S = 'F' AND
            M1.S = 'M' AND
            NOT EXISTS(SELECT *
                       FROM H F2
                       INNER JOIN H M2 ON F2.CODE_HORSE = M2.CODE_HORSE
                       WHERE F2.S = 'F' AND
                             M2.S = 'M' AND
                             F2.MARK = F1.PREV_MARK AND
                             M2.MARK = M1.PREV_MARK)
      ORDER BY F1.DEPTH, M1.DEPTH
      INTO X_CODE_HORSE,
           DEPTH_F,
           DEPTH_M
  DO
  BEGIN

    COLOR = NULL;
    SELECT IMBRID_COLOR
    FROM PEDIGREE_TMP
    WHERE CODE_HORSE = :X_CODE_HORSE AND
          CODE_ROOT_HORSE = :A_CODE_HORSE ROWS 1
    INTO :COLOR;

    IF (COLOR = 0) THEN
    BEGIN
      SELECT AVALUE
      FROM HUE
      WHERE BYORDER = :COUNTER + 1
      INTO :COLOR;
      COUNTER = COUNTER + 1;
      IF (COUNTER > 8) THEN
        COUNTER = 0;
    END

    UPDATE PEDIGREE_TMP
    SET IMBRID = 1,
        IMBRID_COLOR = :COLOR,
        IMBRID_VALUE = IMBRID_VALUE + POWER(0.5, :DEPTH_F + :DEPTH_M - 1)
    WHERE CODE_HORSE = :X_CODE_HORSE AND
          CODE_ROOT_HORSE = :A_CODE_HORSE;

  END

  SELECT SUM(IMBRID_VALUE)
  FROM (SELECT MIN(IMBRID_VALUE) AS IMBRID_VALUE
        FROM PEDIGREE_TMP
        WHERE CODE_ROOT_HORSE = :A_CODE_HORSE
        GROUP BY CODE_HORSE)
  INTO :IMBRID_VALUE;

  UPDATE PEDIGREE_TMP
  SET IMBRID_VALUE = :IMBRID_VALUE
  WHERE CODE_HORSE = :A_CODE_HORSE AND
        CODE_ROOT_HORSE = :A_CODE_HORSE;

  FOR SELECT CODE_HORSE,
             DEPTH,
             MARK,
             IMBRID,
             IMBRID_COLOR,
             IMBRID_VALUE
      FROM PEDIGREE_TMP
      WHERE CODE_ROOT_HORSE = :A_CODE_HORSE
      INTO :CODE_HORSE,
           :DEPTH,
           :MARK,
           :IMBRID,
           :IMBRID_COLOR,
           :IMBRID_VALUE
  DO
    SUSPEND;
END

CREATE GLOBAL TEMPORARY TABLE PEDIGREE_TMP (
    CODE_ROOT_HORSE  INTEGER NOT NULL,
    MARK             VARCHAR(20) NOT NULL,
    CODE_HORSE       INTEGER NOT NULL,
    DEPTH            INTEGER NOT NULL,
    IMBRID           INTEGER DEFAULT 0 NOT NULL,
    IMBRID_COLOR     INTEGER,
    IMBRID_VALUE     NUMERIC(15,6)
) ON COMMIT DELETE ROWS;



ALTER TABLE PEDIGREE_TMP ADD CONSTRAINT PK_PEDIGREE_TMP PRIMARY KEY (CODE_ROOT_HORSE, MARK);

CREATE INDEX PEDIGREE_TMP_IDX_CODE_HORSE ON PEDIGREE_TMP (CODE_HORSE);
CREATE INDEX PEDIGREE_TMP_IDX_CODE_ROOTHORSE ON PEDIGREE_TMP (CODE_ROOT_HORSE);

CREATE TABLE HUE (
    CODE_HUE  INTEGER NOT NULL,
    NAME      VARCHAR(30) NOT NULL,
    AVALUE    INTEGER NOT NULL,
    BYORDER   SMALLINT
);


ALTER TABLE HUE ADD CONSTRAINT UNQ_HUE_NAME UNIQUE (NAME);
ALTER TABLE HUE ADD CONSTRAINT PK_HUE PRIMARY KEY (CODE_HUE);
CREATE INDEX HUE_IDX_ORDER ON HUE (BYORDER);

INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (1, 'Красный', 255, 1);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (2, 'Голубой', 16711680, 2);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (3, 'Зелёный', 32768, 3);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (4, 'Коричневый', 2763429, 4);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (5, 'Оливковый', 32896, 5);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (6, 'Серый', 808080, 6);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (7, 'Оранжевый', 42495, 7);
INSERT INTO HUE (CODE_HUE, NAME, AVALUE, BYORDER)
         VALUES (8, 'Шоколадный', 1993170, 8);

COMMIT WORK;