
BEGIN;

-- Circ Matrix Weights
CREATE TABLE config.circ_matrix_weights (
    id                      SERIAL  PRIMARY KEY,
    name                    TEXT    NOT NULL UNIQUE,
    org_unit                NUMERIC(6,2)   NOT NULL,
    grp                     NUMERIC(6,2)   NOT NULL,
    circ_modifier           NUMERIC(6,2)   NOT NULL,
    marc_type               NUMERIC(6,2)   NOT NULL,
    marc_form               NUMERIC(6,2)   NOT NULL,
    marc_bib_level          NUMERIC(6,2)   NOT NULL,
    marc_vr_format          NUMERIC(6,2)   NOT NULL,
    copy_circ_lib           NUMERIC(6,2)   NOT NULL,
    copy_owning_lib         NUMERIC(6,2)   NOT NULL,
    user_home_ou            NUMERIC(6,2)   NOT NULL,
    ref_flag                NUMERIC(6,2)   NOT NULL,
    juvenile_flag           NUMERIC(6,2)   NOT NULL,
    is_renewal              NUMERIC(6,2)   NOT NULL,
    usr_age_lower_bound     NUMERIC(6,2)   NOT NULL,
    usr_age_upper_bound     NUMERIC(6,2)   NOT NULL
);

-- Hold Matrix Weights
CREATE TABLE config.hold_matrix_weights (
    id                      SERIAL  PRIMARY KEY,
    name                    TEXT    NOT NULL UNIQUE,
    user_home_ou            NUMERIC(6,2)   NOT NULL,
    request_ou              NUMERIC(6,2)   NOT NULL,
    pickup_ou               NUMERIC(6,2)   NOT NULL,
    item_owning_ou          NUMERIC(6,2)   NOT NULL,
    item_circ_ou            NUMERIC(6,2)   NOT NULL,
    usr_grp                 NUMERIC(6,2)   NOT NULL,
    requestor_grp           NUMERIC(6,2)   NOT NULL,
    circ_modifier           NUMERIC(6,2)   NOT NULL,
    marc_type               NUMERIC(6,2)   NOT NULL,
    marc_form               NUMERIC(6,2)   NOT NULL,
    marc_bib_level          NUMERIC(6,2)   NOT NULL,
    marc_vr_format          NUMERIC(6,2)   NOT NULL,
    juvenile_flag           NUMERIC(6,2)   NOT NULL,
    ref_flag                NUMERIC(6,2)   NOT NULL
);

-- Linking between weights and org units
CREATE TABLE config.weight_assoc (
    id                      SERIAL  PRIMARY KEY,
    active                  BOOL    NOT NULL,
    org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    circ_weights            INT     REFERENCES config.circ_matrix_weights (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
    hold_weights            INT     REFERENCES config.hold_matrix_weights (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
);
CREATE UNIQUE INDEX cwa_one_active_per_ou ON config.weight_assoc (org_unit) WHERE active;

COMMIT;
