CREATE TABLE messages (
    message_id  INT4            NOT NULL,
    target      VARCHAR(99),
    client_id   INT             NOT NULL,
    content     VARCHAR(999),
    time_in     TIMESTAMP       NOT NULL,
    time_out    TIMESTAMP,
    time_got    TIMESTAMP,
    status      INT             NOT NULL,
    attempts    INT             NOT NULL,
    priority    INT             NOT NULL DEFAULT 0,
    notify      INT,
    device      INT,
    delay       INT,
    validity    INT             NOT NULL DEFAULT -1,
    route_id    INT,
    CONSTRAINT messages_primary PRIMARY KEY(message_id),
    CONSTRAINT messages_client_id
        FOREIGN KEY(client_id)  REFERENCES clients(client_id),
    CONSTRAINT messages_status
        FOREIGN KEY(status)     REFERENCES mstatus(status)
);

CREATE TABLE messages_archive (
    message_id  INT4            NOT NULL,
    target      VARCHAR(99),
    client_id   INT             NOT NULL,
    content     VARCHAR(999),
    time_in     TIMESTAMP       NOT NULL,
    time_out    TIMESTAMP,
    time_got    TIMESTAMP,
    status      INT             NOT NULL,
    attempts    INT             NOT NULL,
    priority    INT             NOT NULL,
    notify      INT,
    device      INT,
    delay       INT,
    validity    INT             NOT NULL,
    route_id    INT
);

CREATE VIEW messages_all AS
    SELECT * FROM messages
    UNION SELECT * FROM messages_archive;