BEGIN;

-- Schema

DROP SCHEMA IF EXISTS species CASCADE;

CREATE SCHEMA species;

CREATE TABLE species.description(
    sid int PRIMARY KEY,
    genus text UNIQUE NOT NULL,
    common_name text,
    global_conservation int CHECK (0 <= global_conservation AND global_conservation <= 6),
    local_conservation int CHECK (0 <= local_conservation AND local_conservation <= 6),
    blacklisted boolean
);

CREATE TABLE species.observation(
    oid SERIAL PRIMARY KEY,
    species int REFERENCES species.description(sid),
    observed_time timestamp NOT NULL,
    observed_lat float,
    observed_long float,
    location text,
    CHECK (location IS NOT NULL OR (observed_lat IS NOT NULL AND observed_long IS NOT NULL))
);

CREATE TABLE species.eats(
    sid1 int NOT NULL REFERENCES species.description(sid),
    sid2 int NOT NULL REFERENCES species.description(sid)
);

-- Data

INSERT INTO species.description(sid, genus, common_name, global_conservation, local_conservation, blacklisted) VALUES
(1, 'Rattus Norvegicus', 'Brown rat', 6, 6, false),
(2, 'Larus Canus', 'Common gull', 6, 6, false),
(3, 'Lutra', 'Eurasian otter', 5, 4, false),
(4, 'Osmoderna eremita', 'Hermit beetle', 5, 4, false),
(5, 'Malacodea regelaria', NULL, NULL, 3, false),
(6, 'Balaenoptera physalus', 'Fin whale', 4, 6, false),
(7, 'Emberiza calandra', 'Corn bunting', 6, 0, false),
(8, 'Stelis phaeoptera', NULL, NULL, 0, false),
(9, 'Arion vulgaris', 'Spanish slug', 6, 6, true),
(10, 'Lupinus polyphyllus', 'garden lupin', 6, 6, true);

INSERT INTO species.observation(species, observed_time, observed_lat, observed_long, location) VALUES
(1, '2020-10-21 02:14:10', 59.89, 10.72, NULL), 
(3, '2021-01-01 02:14:10', 59.39, 10.78, NULL), 
(5, '2020-11-03 02:14:10', 59.47, 11.02, NULL), 
(2, '2020-01-17 02:14:10', 58.91, 10.34, NULL), 
(6, '2019-12-24 02:14:10', 59.88, 11.01, NULL), 
(1, '2020-06-09 02:14:10', NULL, NULL, 'Oslo'), 
(1, '2021-01-02 02:14:10', 59.81, 9.94, NULL), 
(10, '2020-04-30 02:14:10', 59.11, 10.70, NULL), 
(2, '2019-02-12 02:14:10', 59.36, 11.08, NULL), 
(1, '2020-08-22 02:14:10', 59.89, 9.28, NULL), 
(10, '2020-10-20 02:14:10', 60.01, 10.78, NULL), 
(9, '2020-03-26 02:14:10', 59.37, 10.23, NULL), 
(2, '2020-09-04 02:14:10', 59.41, 10.40, NULL), 
(1, '2019-12-03 02:14:10', 59.39, 11.04, NULL), 
(10, '2020-10-06 02:14:10', 59.11, 10.83, NULL), 
(2, '2020-01-03 02:14:10', 59.19, 9.31, NULL), 
(9, '2020-07-07 02:14:10', 59.64, 10.61, NULL), 
(10, '2020-04-19 02:14:10', 59.23, 10.39, NULL), 
(1, '2019-03-08 02:14:10', NULL, NULL, 'Drammen'), 
(2, '2021-01-21 02:14:10', NULL, NULL, 'Oslo'), 
(8, '2020-11-30 02:14:10', 58.99, 9.78, NULL), 
(9, '2020-10-09 02:14:10', 59.83, 10.37, NULL), 
(1, '2020-07-23 02:14:10', NULL, NULL, 'Nord-marka');

INSERT INTO species.eats VALUES -- Not real data!
(1, 4),
(3, 4),
(2, 1),
(9, 10),
(2, 9),
(9, 4),
(4, 10);

--INSERT INTO mappings.direct_mappings VALUES
--('species', 'description', NULL, NULL, 'http://example.org'),
--('species', 'observation', NULL, NULL, 'http://example.org');

COMMIT;
