--
-- PostgreSQL database dump
--

-- Dumped from database version 11.22 (Raspbian 11.22-0+deb10u1)
-- Dumped by pg_dump version 13.13 (Debian 13.13-0+deb11u1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

--
-- Name: observations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.observations (
    date date NOT NULL,
    count smallint,
    observer integer,
    species text,
    observation_id integer NOT NULL,
    behaviour text,
    ring_number text
);


--
-- Name: observations_observation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.observations_observation_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: observations_observation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.observations_observation_id_seq OWNED BY public.observations.observation_id;


--
-- Name: observers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.observers (
    observer_name text,
    reference text,
    observer_id integer NOT NULL
);


--
-- Name: observers_observer_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.observers_observer_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: observers_observer_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.observers_observer_id_seq OWNED BY public.observers.observer_id;


--
-- Name: species; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.species (
    species text NOT NULL,
    category text,
    reference text
);


--
-- Name: observations observation_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observations ALTER COLUMN observation_id SET DEFAULT nextval('public.observations_observation_id_seq'::regclass);


--
-- Name: observers observer_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observers ALTER COLUMN observer_id SET DEFAULT nextval('public.observers_observer_id_seq'::regclass);


--
-- Data for Name: observations; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO public.observations VALUES ('2024-01-03', 3, 4, 'Unicorn', 2, 'eating', 'M2005');
INSERT INTO public.observations VALUES ('2024-03-11', 2, 3, 'Wyvern', 3, 'sleaping', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 20, 4, 'Unicorn', 4, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 3, 1, 'Siren', 1, 'singing', NULL);
INSERT INTO public.observations VALUES ('2024-03-04', 2, 3, 'Unicorn', 5, 'Flying', 'R1');
INSERT INTO public.observations VALUES ('2024-03-03', 2, 4, 'Siren', 7, 'eating', 'null');
INSERT INTO public.observations VALUES ('2024-03-11', 23, 6, 'Wyvern', 8, 'sleaping', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 10, 8, 'Unicorn', 9, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 23, 6, 'Blue Eyes', 11, 'sleaping', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 10, 8, 'Hobbit', 12, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-04', 2, 3, 'Unicorn', 13, 'bioinformating', 'R1');
INSERT INTO public.observations VALUES ('2024-03-04', 2, 3, 'Unicorn', 14, 'eating', 'R1');
INSERT INTO public.observations VALUES ('2024-03-11', 7, 6, 'Siren', 6, 'judging', NULL);
INSERT INTO public.observations VALUES ('2024-03-04', 2, 3, 'Unicorn', 24, 'eatinng', 'R1');
INSERT INTO public.observations VALUES ('2024-03-04', 2, 3, 'Unicorn', 28, 'mathing', 'R1');
INSERT INTO public.observations VALUES ('2024-10-12', 5, 5, 'Wyvern', 30, 'flyin', 'null');
INSERT INTO public.observations VALUES ('2024-03-11', 7, 5, 'Siren', 31, 'sleeping', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 4, 5, 'Hobbit', 32, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 4, 5, 'Unicorn', 33, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 28, 1, 'Mermaid', 10, 'growling', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 7, 5, 'Siren', 34, 'sleeping', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 4, 5, 'Hobbit', 35, 'eating', NULL);
INSERT INTO public.observations VALUES ('2024-03-11', 4, 5, 'Unicorn', 36, 'eating', NULL);


--
-- Data for Name: observers; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO public.observers VALUES ('Aram', 'https://www.marinespecies.org/aphia.php?p=taxdetails&id=125125', 1);
INSERT INTO public.observers VALUES ('Hoang', 'https://www.marinespecies.org/aphia.php?p=taxdetails&id=125125', 2);
INSERT INTO public.observers VALUES ('Joveria', 'https://www.marinespecies.org/aphia.php?p=taxdetails&id=125125', 3);
INSERT INTO public.observers VALUES ('Emma', 'https://www.marinespecies.org/aphia.php?p=taxdetails&id=125125', 4);
INSERT INTO public.observers VALUES ('Mounir', 'https://www.marinespecies.org/aphia.php?p=taxdetails&id=125125', 5);
INSERT INTO public.observers VALUES ('Miki', 'asdasdas', 6);
INSERT INTO public.observers VALUES ('Emma', 'https://www.gbif.org/species/4306417', 8);
INSERT INTO public.observers VALUES ('Miki', 'https://www.gbif.org/species/4895381', 9);


--
-- Data for Name: species; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO public.species VALUES ('Wyvern', 'Dragon', 'https://dragons.fandom.com/wiki/Wyvern');
INSERT INTO public.species VALUES ('Lernaean Hydra', 'Dragon', 'https://dragons.fandom.com/wiki/Lernaean_Hydra');
INSERT INTO public.species VALUES ('Quetzalcoatl', 'Dragon', 'https://dragons.fandom.com/wiki/Quetzalcoatl');
INSERT INTO public.species VALUES ('Mermaid', 'Water spirit', 'https://en.wikipedia.org/wiki/Mermaid');
INSERT INTO public.species VALUES ('Unicorn', 'Mammal', 'https://en.wikipedia.org/wiki/Unicorn');
INSERT INTO public.species VALUES ('Hobbit', 'Mammal', NULL);
INSERT INTO public.species VALUES ('Blue Eyes', 'Dragon', NULL);
INSERT INTO public.species VALUES ('Siren', 'Water spirit', NULL);


--
-- Name: observations_observation_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.observations_observation_id_seq', 36, true);


--
-- Name: observers_observer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.observers_observer_id_seq', 9, true);


--
-- Name: observers new_observer_id; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observers
    ADD CONSTRAINT new_observer_id UNIQUE (observer_id);


--
-- Name: observations observations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observations
    ADD CONSTRAINT observations_pkey PRIMARY KEY (observation_id);


--
-- Name: observers observer_name_ukey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observers
    ADD CONSTRAINT observer_name_ukey UNIQUE (observer_name, reference);


--
-- Name: observers observer_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observers
    ADD CONSTRAINT observer_pkey PRIMARY KEY (observer_id);


--
-- Name: species pk_species_name; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.species
    ADD CONSTRAINT pk_species_name PRIMARY KEY (species);


--
-- Name: observations fk_observer; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observations
    ADD CONSTRAINT fk_observer FOREIGN KEY (observer) REFERENCES public.observers(observer_id);


--
-- Name: observations fk_species; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.observations
    ADD CONSTRAINT fk_species FOREIGN KEY (species) REFERENCES public.species(species);


--
-- PostgreSQL database dump complete
--

