
CREATE TABLE client_parameter (
    param TEXT NOT NULL CHECK (param REGEXP '^[A-Z_]+$'),
    value TEXT,

    CONSTRAINT pk_parameter PRIMARY KEY (param)
) STRICT;

CREATE TABLE branch (
    zwstid      TEXT    NOT NULL CHECK (zwstid REGEXP '^[A-Z]$'),
    name        TEXT    NOT NULL,

    country     INTEGER DEFAULT NULL,
    postal_dest TEXT    DEFAULT NULL,
    address     TEXT    DEFAULT NULL,

    phone_nr    TEXT DEFAULT NULL CHECK (phone_nr  REGEXP '^\+[0-9]{1,3}( [0-9]+)+(-[0-9]+)?$'),
    fax_nr      TEXT DEFAULT NULL CHECK (fax_nr    REGEXP '^\+[0-9]{1,3}( [0-9]+)+(-[0-9]+)?$'),
    mobile_nr   TEXT DEFAULT NULL CHECK (mobile_nr REGEXP '^\+[0-9]{1,3}( [0-9]+)+(-[0-9]+)?$'),

    CONSTRAINT pk_branch PRIMARY KEY (zwstid),
    CONSTRAINT sk_branch_name UNIQUE (name),
    CONSTRAINT fk_branch_postal_dest FOREIGN KEY (country, postal_dest) REFERENCES postal_dest (country, id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE wine_attribute (
    attrid        TEXT    NOT NULL CHECK (attrid REGEXP '^[A-Z]+$'),
    name          TEXT    NOT NULL,
    active        INTEGER NOT NULL CHECK (active     IN (TRUE, FALSE)) DEFAULT TRUE,

    max_kg_per_ha INTEGER,
    strict        INTEGER NOT NULL CHECK (strict     IN (TRUE, FALSE)) DEFAULT FALSE,
    fill_lower    INTEGER NOT NULL CHECK (fill_lower IN (0, 1, 2))     DEFAULT 0,

    CONSTRAINT pk_wine_attribute PRIMARY KEY (attrid),
    CONSTRAINT sk_wine_attribute_name UNIQUE (name)
) STRICT;

-- all values in the table are stored with precision 4!
CREATE TABLE area_commitment_type (
    vtrgid         TEXT NOT NULL CHECK (vtrgid = sortid || COALESCE(attrid, '') || disc),
    sortid         TEXT NOT NULL,
    attrid         TEXT,
    disc           TEXT DEFAULT NULL CHECK (disc REGEXP '^[A-Z0-9]+$'),

    min_kg_per_ha  INTEGER,
    penalty_per_kg INTEGER,
    penalty_amount INTEGER,
    penalty_none   INTEGER,

    CONSTRAINT pk_area_commitment_type PRIMARY KEY (vtrgid),
    CONSTRAINT sk_area_commitment_type_sort_attr UNIQUE (sortid, attrid, disc),
    CONSTRAINT fk_area_commitment_type_wine_variety FOREIGN KEY (sortid) REFERENCES wine_variety (sortid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_area_commitment_type_wine_attribute FOREIGN KEY (attrid) REFERENCES wine_attribute (attrid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE wine_cultivation (
    cultid      TEXT NOT NULL CHECK (cultid REGEXP '^[A-Z][A-Z0-9]*$'),
    name        TEXT NOT NULL,
    description TEXT DEFAULT NULL,

    CONSTRAINT pk_wine_cultivation PRIMARY KEY (cultid),
    CONSTRAINT sk_wine_cultivation_name UNIQUE (name)
) STRICT;

CREATE TABLE member (
    mgnr             INTEGER NOT NULL,
    predecessor_mgnr INTEGER          DEFAULT NULL,

    name             TEXT    NOT NULL,
    prefix           TEXT             DEFAULT NULL,
    given_name       TEXT             DEFAULT NULL,
    middle_names     TEXT             DEFAULT NULL,
    suffix           TEXT             DEFAULT NULL,
    attn             TEXT             DEFAULT NULL,

    birthday         TEXT             CHECK (birthday   REGEXP '^[1-9][0-9]{3}(-(0[1-9]|1[012])(-(0[1-9]|[12][0-9]|3[01]))?)?$') DEFAULT NULL,
    entry_date       TEXT             CHECK (entry_date REGEXP '^[1-9][0-9]{3}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$')       DEFAULT CURRENT_DATE,
    exit_date        TEXT             CHECK (exit_date  REGEXP '^[1-9][0-9]{3}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$')       DEFAULT NULL,

    business_shares  INTEGER NOT NULL DEFAULT 0,
    accounting_nr    TEXT             DEFAULT NULL,
    zwstid           TEXT CHECK (NOT active OR zwstid IS NOT NULL),

    lfbis_nr         TEXT             CHECK (lfbis_nr REGEXP '^[0-9]{7}$')               DEFAULT NULL,
    ustid_nr         TEXT             CHECK (ustid_nr REGEXP '^[A-Z]{2}[A-Z0-9]{2,12}$') DEFAULT NULL,
    juridical_pers   INTEGER NOT NULL CHECK (juridical_pers IN (TRUE, FALSE))            DEFAULT FALSE,
    volllieferant    INTEGER NOT NULL CHECK (volllieferant  IN (TRUE, FALSE))            DEFAULT FALSE,
    buchführend      INTEGER NOT NULL CHECK (buchführend    IN (TRUE, FALSE))            DEFAULT FALSE,
    organic          INTEGER NOT NULL CHECK (organic        IN (TRUE, FALSE))            DEFAULT FALSE,
    funktionär       INTEGER NOT NULL CHECK (funktionär     IN (TRUE, FALSE))            DEFAULT FALSE,
    active           INTEGER NOT NULL CHECK (active         IN (TRUE, FALSE))            DEFAULT TRUE,
    deceased         INTEGER NOT NULL CHECK (deceased       IN (TRUE, FALSE))            DEFAULT FALSE,

    iban             TEXT CHECK (iban REGEXP '^[A-Z]{2}[0-9]{2}[A-Z0-9]{8,30}$')               DEFAULT NULL,
    bic              TEXT CHECK (bic  REGEXP '^[A-Z0-9]{4}[A-Z]{2}[A-Z0-9]{2}([A-Z0-9]{3})?$') DEFAULT NULL,

    country          INTEGER NOT NULL,
    postal_dest      TEXT    NOT NULL,
    address          TEXT    NOT NULL,

    default_kgnr     INTEGER,
    contact_postal   INTEGER NOT NULL CHECK (contact_postal IN (TRUE, FALSE)) DEFAULT TRUE,
    contact_email    INTEGER NOT NULL CHECK (contact_email  IN (TRUE, FALSE)) DEFAULT FALSE,

    comment          TEXT             DEFAULT NULL,
    ctime            INTEGER NOT NULL DEFAULT (UNIXEPOCH()),
    mtime            INTEGER NOT NULL DEFAULT (UNIXEPOCH()),

    CONSTRAINT pk_member PRIMARY KEY (mgnr),
    CONSTRAINT fk_member_member FOREIGN KEY (predecessor_mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_member_postal_dest FOREIGN KEY (country, postal_dest) REFERENCES postal_dest (country, id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_member_wb_kg FOREIGN KEY (default_kgnr) REFERENCES wb_kg (kgnr)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_member_branch FOREIGN KEY (zwstid) REFERENCES branch (zwstid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE member_billing_address (
    mgnr        INTEGER NOT NULL,

    name        TEXT    NOT NULL,
    country     INTEGER NOT NULL,
    postal_dest TEXT    NOT NULL,
    address     TEXT    NOT NULL,

    CONSTRAINT pk_member_billing_address PRIMARY KEY (mgnr),
    CONSTRAINT fk_member_billing_address_member FOREIGN KEY (mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_member_billing_address_postal_dest FOREIGN KEY (country, postal_dest) REFERENCES postal_dest (country, id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE member_telephone_number (
    mgnr    INTEGER NOT NULL,
    nr      INTEGER NOT NULL,

    type    TEXT    NOT NULL CHECK (type REGEXP '^[a-z_]+$'),
    number  TEXT    NOT NULL CHECK (number REGEXP '^\+[0-9]{1,3}( [0-9]+)+(-[0-9]+)?$' AND LENGTH(REPLACE(REPLACE(REPLACE(number, '+', ''), ' ', ''), '-', '')) <= 15),
    comment TEXT DEFAULT NULL,

    CONSTRAINT pk_member_telephone_number PRIMARY KEY (mgnr, nr),
    CONSTRAINT fk_member_telephone_number_member FOREIGN KEY (mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) STRICT;

CREATE TABLE member_email_address (
    mgnr    INTEGER NOT NULL,
    nr      INTEGER NOT NULL,

    address TEXT    NOT NULL CHECK (address REGEXP '^[^@ ]+@([a-z0-9_\x2Däöüß]+\.)+[a-z]{2,}$'),
    comment TEXT DEFAULT NULL,

    CONSTRAINT pk_member_email_address PRIMARY KEY (mgnr, nr),
    CONSTRAINT fk_member_email_address_member FOREIGN KEY (mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) STRICT;

CREATE TABLE area_commitment (
    fbnr      INTEGER NOT NULL,
    mgnr      INTEGER NOT NULL,

    vtrgid    TEXT    NOT NULL,
    cultid    TEXT             DEFAULT NULL,
    area      INTEGER NOT NULL,

    kgnr      INTEGER NOT NULL,
    gstnr     TEXT    NOT NULL,
    rdnr      INTEGER,

    year_from INTEGER CHECK (year_from >= 1000 AND year_from <= 9999) DEFAULT NULL,
    year_to   INTEGER CHECK (year_to   >= 1000 AND year_to   <= 9999) DEFAULT NULL,

    comment   TEXT             DEFAULT NULL,
    ctime     INTEGER NOT NULL DEFAULT (UNIXEPOCH()),
    mtime     INTEGER NOT NULL DEFAULT (UNIXEPOCH()),

    CONSTRAINT pk_area_commitment PRIMARY KEY (fbnr),
    CONSTRAINT fk_area_commitment_member FOREIGN KEY (mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_area_commitment_area_commitment_type FOREIGN KEY (vtrgid) REFERENCES area_commitment_type (vtrgid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_area_commitment_wine_cultivation FOREIGN KEY (cultid) REFERENCES wine_cultivation (cultid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_area_commitment_wb_kg FOREIGN KEY (kgnr) REFERENCES wb_kg (kgnr)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_area_commitment_wb_rd FOREIGN KEY (kgnr, rdnr) REFERENCES wb_rd (kgnr, rdnr)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE member_history (
    mgnr            INTEGER NOT NULL,
    date            TEXT    NOT NULL CHECK (date REGEXP '^[1-9][0-9]{3}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$') DEFAULT CURRENT_DATE,
    type            TEXT    NOT NULL CHECK (type REGEXP '^[a-z_]+$'),

    business_shares INTEGER NOT NULL,
    comment         TEXT DEFAULT NULL,

    CONSTRAINT pk_member_history PRIMARY KEY (mgnr, date, type),
    CONSTRAINT fk_member_history_member FOREIGN KEY (mgnr) REFERENCES member (mgnr)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) STRICT;
