-- @tag: requirement_specs
-- @description: Pflichtenhefte
-- @depends: release_3_0_0
-- @charset: utf-8

-- Nur für Entwicklungszwecke:

-- DELETE FROM schema_info WHERE tag = 'requirement_specs';

-- BEGIN;
-- DROP TABLE requirement_spec_item_dependencies;
-- DROP TABLE requirement_spec_items;
-- DROP TABLE requirement_spec_text_blocks;
-- DROP TABLE requirement_specs;
-- DROP TABLE requirement_spec_versions;
-- DROP TABLE requirement_spec_predefined_texts;
-- DROP TABLE requirement_spec_types;
-- DROP TABLE requirement_spec_statuses;
-- DROP TABLE requirement_spec_risks;
-- DROP TABLE requirement_spec_complexities;
-- DROP TABLE requirement_spec_acceptance_statuses;
-- ALTER TABLE customer DROP COLUMN hourly_rate;
-- ALTER TABLE defaults DROP COLUMN requirement_spec_section_number_format;
-- ALTER TABLE defaults DROP COLUMN requirement_spec_function_block_number_format;

CREATE TABLE requirement_spec_acceptance_statuses (
       id          SERIAL,
       name        TEXT      NOT NULL,
       description TEXT,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (name, description)
);
CREATE TRIGGER mtime_requirement_spec_acceptance_statuses BEFORE UPDATE ON requirement_spec_acceptance_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted',                          'Abgenommen',                                1);
INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects',             'Mit Mängeln abgenommen',                    2);
INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects_to_be_fixed', 'Mit noch zu behebenden Mängeln abgenommen', 3);
INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('not_accepted',                      'Nicht abgenommen',                          4);



CREATE TABLE requirement_spec_complexities (
       id          SERIAL,
       description TEXT      NOT NULL,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (description)
);
CREATE TRIGGER mtime_requirement_spec_complexities BEFORE UPDATE ON requirement_spec_complexities FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_complexities (description, position) VALUES ('nicht bewertet',  1);
INSERT INTO requirement_spec_complexities (description, position) VALUES ('nur Anforderung', 2);
INSERT INTO requirement_spec_complexities (description, position) VALUES ('gering',          3);
INSERT INTO requirement_spec_complexities (description, position) VALUES ('mittel',          4);
INSERT INTO requirement_spec_complexities (description, position) VALUES ('hoch',            5);



CREATE TABLE requirement_spec_risks (
       id          SERIAL,
       description TEXT      NOT NULL,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (description)
);
CREATE TRIGGER mtime_requirement_spec_risks BEFORE UPDATE ON requirement_spec_risks FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_risks (description, position) VALUES ('nicht bewertet',  1);
INSERT INTO requirement_spec_risks (description, position) VALUES ('nur Anforderung', 2);
INSERT INTO requirement_spec_risks (description, position) VALUES ('gering',          3);
INSERT INTO requirement_spec_risks (description, position) VALUES ('mittel',          4);
INSERT INTO requirement_spec_risks (description, position) VALUES ('hoch',            5);



CREATE TABLE requirement_spec_statuses (
       id          SERIAL,
       name        TEXT      NOT NULL,
       description TEXT      NOT NULL,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (name, description)
);
CREATE TRIGGER mtime_requirement_spec_statuses BEFORE UPDATE ON requirement_spec_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('planning', 'In Planung',     1);
INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('running',  'In Bearbeitung', 2);
INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('done',     'Fertiggestellt', 3);



CREATE TABLE requirement_spec_types (
       id          SERIAL,
       description TEXT      NOT NULL,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (description)
);
CREATE TRIGGER mtime_requirement_spec_types BEFORE UPDATE ON requirement_spec_types FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_types (description, position) VALUES ('Pflichtenheft', 1);
INSERT INTO requirement_spec_types (description, position) VALUES ('Konzept',       2);



CREATE TABLE requirement_spec_predefined_texts (
       id          SERIAL,
       description TEXT      NOT NULL,
       title       TEXT      NOT NULL,
       text        TEXT      NOT NULL,
       position    INTEGER   NOT NULL,
       itime       TIMESTAMP DEFAULT now(),
       mtime       TIMESTAMP,

       PRIMARY KEY (id),
       UNIQUE (description)
);
CREATE TRIGGER mtime_requirement_spec_predefined_texts BEFORE UPDATE ON requirement_spec_predefined_texts FOR EACH ROW EXECUTE PROCEDURE set_mtime();



CREATE TABLE requirement_spec_versions (
       id             SERIAL,
       version_number INTEGER,
       description    TEXT NOT NULL,
       comment        TEXT,
       order_date     DATE,
       order_number   TEXT,
       order_id       INTEGER,
       itime          TIMESTAMP DEFAULT now(),
       mtime          TIMESTAMP,

       PRIMARY KEY (id),
       FOREIGN KEY (order_id) REFERENCES oe (id)
);
CREATE TRIGGER mtime_requirement_spec_versions BEFORE UPDATE ON requirement_spec_versions FOR EACH ROW EXECUTE PROCEDURE set_mtime();



CREATE TABLE requirement_specs (
       id                      SERIAL,
       type_id                 INTEGER        NOT NULL,
       status_id               INTEGER        NOT NULL,
       version_id              INTEGER,
       customer_id             INTEGER        NOT NULL,
       project_id              INTEGER,
       title                   TEXT           NOT NULL,
       hourly_rate             NUMERIC(8, 2)  NOT NULL DEFAULT 0,
       net_sum                 NUMERIC(12, 2) NOT NULL DEFAULT 0,
       working_copy_id         INTEGER,
       previous_section_number INTEGER        NOT NULL,
       previous_fb_number      INTEGER        NOT NULL,
       is_template             BOOLEAN                 DEFAULT FALSE,
       itime                   TIMESTAMP               DEFAULT now(),
       mtime                   TIMESTAMP,

       PRIMARY KEY (id),
       FOREIGN KEY (type_id)         REFERENCES requirement_spec_types    (id),
       FOREIGN KEY (status_id)       REFERENCES requirement_spec_statuses (id),
       FOREIGN KEY (version_id)      REFERENCES requirement_spec_versions (id),
       FOREIGN KEY (working_copy_id) REFERENCES requirement_specs         (id),
       FOREIGN KEY (customer_id)     REFERENCES customer                  (id),
       FOREIGN KEY (project_id)      REFERENCES project                   (id)
);
CREATE TRIGGER mtime_requirement_specs BEFORE UPDATE ON requirement_specs FOR EACH ROW EXECUTE PROCEDURE set_mtime();



CREATE TABLE requirement_spec_text_blocks (
       id                  SERIAL,
       requirement_spec_id INTEGER   NOT NULL,
       title               TEXT      NOT NULL,
       text                TEXT,
       position            INTEGER   NOT NULL,
       output_position     INTEGER   NOT NULL DEFAULT 1,
       is_flagged          BOOLEAN   NOT NULL DEFAULT FALSE,
       itime               TIMESTAMP NOT NULL DEFAULT now(),
       mtime               TIMESTAMP,

       PRIMARY KEY (id),
       FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id)
);
CREATE TRIGGER mtime_requirement_spec_text_blocks BEFORE UPDATE ON requirement_spec_text_blocks FOR EACH ROW EXECUTE PROCEDURE set_mtime();


CREATE TABLE requirement_spec_items (
       id                   SERIAL,
       requirement_spec_id  INTEGER        NOT NULL,
       item_type            TEXT           NOT NULL,
       parent_id            INTEGER,
       position             INTEGER        NOT NULL,
       fb_number            TEXT           NOT NULL,
       title                TEXT,
       description          TEXT,
       complexity_id        INTEGER,
       risk_id              INTEGER,
       time_estimation      NUMERIC(12, 2) NOT NULL DEFAULT 0,
       net_sum              NUMERIC(12, 2) NOT NULL DEFAULT 0,
       is_flagged           BOOLEAN        NOT NULL DEFAULT FALSE,
       acceptance_status_id INTEGER,
       acceptance_text      TEXT,
       itime                TIMESTAMP      NOT NULL DEFAULT now(),
       mtime                TIMESTAMP,

       PRIMARY KEY (id),
       FOREIGN KEY (requirement_spec_id)  REFERENCES requirement_specs (id),
       FOREIGN KEY (parent_id)            REFERENCES requirement_spec_items (id),
       FOREIGN KEY (complexity_id)        REFERENCES requirement_spec_complexities (id),
       FOREIGN KEY (risk_id)              REFERENCES requirement_spec_risks (id),
       FOREIGN KEY (acceptance_status_id) REFERENCES requirement_spec_acceptance_statuses (id),

       CONSTRAINT valid_item_type CHECK ((item_type = 'section') OR (item_type = 'function-block') OR (item_type = 'sub-function-block')),
       CONSTRAINT valid_parent_id_for_item_type CHECK (CASE
         WHEN (item_type = 'section') THEN parent_id IS NULL
         ELSE                              parent_id IS NOT NULL
       END)
);
CREATE TRIGGER mtime_requirement_spec_items BEFORE UPDATE ON requirement_spec_items FOR EACH ROW EXECUTE PROCEDURE set_mtime();



CREATE TABLE requirement_spec_item_dependencies (
       depending_item_id INTEGER NOT NULL,
       depended_item_id  INTEGER NOT NULL,

       PRIMARY KEY (depending_item_id, depended_item_id),
       FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id),
       FOREIGN KEY (depended_item_id)  REFERENCES requirement_spec_items (id)
);

ALTER TABLE customer ADD COLUMN hourly_rate NUMERIC(8, 2);


-- Trigger for updating time_estimation of function blocks from their
-- children (not for sections, not for sub function blocks).
CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$
  DECLARE
    item   RECORD;
    parent RECORD;
  BEGIN
    IF item_id IS NULL THEN
      RAISE DEBUG 'updateRSIE: item_id IS NULL';
      RETURN FALSE;
    END IF;

    SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
    RAISE DEBUG 'updateRSIE: item_id % parent_id %', item_id, item.parent_id;

    IF item.parent_id IS NULL THEN
      -- Don't do anything for sections.
      RAISE DEBUG 'updateRSIE: this is a section.';
      RETURN FALSE;
    END IF;

    SELECT * INTO parent FROM requirement_spec_items WHERE id = item.parent_id;
    RAISE DEBUG 'updateRSIE: parent_id of parent of item: %', parent.parent_id;

    IF parent.parent_id IS NOT NULL THEN
      -- Don't do anything for sub function blocks.
      RAISE DEBUG 'updateRSIE: this is sub function block.';
      RETURN FALSE;
    END IF;

    RAISE DEBUG 'updateRSIE: will do stuff now';

    UPDATE requirement_spec_items
      SET time_estimation = COALESCE((
        SELECT SUM(time_estimation)
        FROM requirement_spec_items
        WHERE parent_id = item_id
      ), 0)
      WHERE id = item_id;

    RETURN TRUE;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$
  BEGIN
    IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN
      PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id);
    END IF;
    IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN
      PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id);
    END IF;
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;
CREATE TRIGGER update_requirement_spec_item_time_estimation
AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items
FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();


-- Trigger for deleting depending stuff if a requirement spec item is deleted.
CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
  BEGIN
    RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
    DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);
    DELETE FROM requirement_spec_items             WHERE (parent_id         = OLD.id);

    RETURN OLD;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;
CREATE TRIGGER delete_requirement_spec_item_dependencies
BEFORE DELETE ON requirement_spec_items
FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();


-- Trigger for deleting depending stuff if a requirement spec is deleted.
CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
  DECLARE
    tname TEXT;
  BEGIN
    tname := 'tmp_delete_reqspec' || OLD.id;

    IF TG_WHEN = 'AFTER' THEN
      RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;
      EXECUTE 'DELETE FROM requirement_spec_versions ' ||
              'WHERE id IN (SELECT version_id FROM ' || tname || ')';

      RAISE DEBUG '  dropping table';
      EXECUTE 'DROP TABLE ' || tname;

      RETURN OLD;
    END IF;

    RAISE DEBUG 'before delete trigger on %', OLD.id;

    EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||
      'SELECT DISTINCT version_id '     ||
      'FROM requirement_specs '         ||
      'WHERE (version_id IS NOT NULL) ' ||
      '  AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';

    RAISE DEBUG '  Updating version_id and items for %', OLD.id;
    UPDATE requirement_specs      SET version_id = NULL                        WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);
    UPDATE requirement_spec_items SET item_type  = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;

    RAISE DEBUG '  Deleting stuff for %', OLD.id;

    DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);
    DELETE FROM requirement_spec_items       WHERE (requirement_spec_id = OLD.id);
    DELETE FROM requirement_specs            WHERE (working_copy_id     = OLD.id);

    RAISE DEBUG '  And we out for %', OLD.id;

    RETURN OLD;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;
CREATE TRIGGER delete_requirement_spec_dependencies
BEFORE DELETE ON requirement_specs
FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();

DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;
CREATE TRIGGER after_delete_requirement_spec_dependencies
AFTER DELETE ON requirement_specs
FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();


-- Add formats for section/function block numbers to defaults
ALTER TABLE defaults ADD   COLUMN requirement_spec_section_number_format        TEXT;
ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET DEFAULT 'A00';
ALTER TABLE defaults ADD   COLUMN requirement_spec_function_block_number_format TEXT;
ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000';

UPDATE defaults SET requirement_spec_section_number_format        = 'A00';
UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000';

ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET NOT NULL;
ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL;
