DROP TABLE IF EXISTS CRIMES;
CREATE TABLE IF NOT EXISTS CRIMES
(
    PRIMARY KEY (id),
    id           INT           NOT NULL IDENTITY,
    name         VARCHAR(45)   NOT NULL,
    min_sentence INT           NOT NULL,
    max_sentence INT           NULL,
    lifetime     TINYINT       NOT NULL,
    description  VARCHAR(1000) NULL
);
DROP TABLE IF EXISTS PRISONERS;
CREATE TABLE IF NOT EXISTS PRISONERS
(
    PRIMARY KEY (id),
    id                INT         NOT NULL IDENTITY,
    name              VARCHAR(45) NOT NULL,
    surname           VARCHAR(45) NOT NULL,
    sentence_start    DATE        NOT NULL,
    sentence_duration VARCHAR(45),
    lifetime          TINYINT     NOT NULL,
    crime_id          INT         NOT NULL,
    CONSTRAINT fk_user_crime
        FOREIGN KEY (crime_id)
            REFERENCES CRIMES (id)
);
INSERT INTO CRIMES (name, min_sentence, max_sentence, description, lifetime)
VALUES ('theft', 90, 1800,
        'the felonious taking and removing of personal property with intent to deprive the rightful owner of it',
        0),
       ('murder', 3600, null, 'the unlawful premeditated killing of one human being by another', 1),
       ('drug possesion', 180, 2250,
        'having one or more illegal drugs in one''s possession, either for personal use, distribution, sale or otherwise',
        0);
INSERT INTO PRISONERS(name, surname, sentence_start, sentence_duration, lifetime, crime_id)
VALUES ('Jan', 'Kowalski', '2014-12-12', 100, 0, 2),
       ('Janusz', 'Nowak', '1970-02-03', null, 1, 1),
       ('Zbigniew', 'Malinowski', '2012-05-03', 250, 0, 0);