\set ECHO none
\pset format unaligned
SET search_path TO provsql_test,provsql;

CREATE TABLE person (
    id integer NOT NULL,
    name text NOT NULL,
    date_of_birth date,
    height smallint
);

CREATE TABLE reliability (
    person integer NOT NULL,
    score double precision NOT NULL
);

CREATE TABLE room (
    id integer NOT NULL,
    name text NOT NULL,
    area smallint
);

CREATE TABLE sightings (
    "time" time without time zone NOT NULL,
    person integer NOT NULL,
    room integer NOT NULL,
    witness integer
);

COPY person (id, name, date_of_birth, height) FROM stdin;
0	Titus	1969-04-03	163
1	Norah	1983-10-15	194
2	Ginny	1989-10-23	169
3	Demetra	1957-07-20	167
4	Sheri	1950-10-19	195
5	Karleen	2004-09-01	199
6	Daisey	2002-08-19	163
7	Audrey	2009-12-20	167
8	Alaine	1956-09-07	192
9	Edwin	1987-02-21	210
10	Shelli	1985-03-05	195
11	Santina	1991-09-04	164
12	Bart	1989-08-12	163
13	Harriette	1959-06-24	160
14	Jody	1962-12-18	202
15	Theodora	1995-11-08	204
16	Roman	1964-12-14	171
17	Jack	1976-06-11	167
18	Daphine	1998-09-21	191
19	Kyra	1966-05-04	202
\.

COPY reliability (person, score) FROM stdin;
0	0.23828493492944236
1	0.657319818187148019
2	0.745325911826738019
3	0.656730287512349964
4	0.942979116189337052
5	0.600921893448834954
6	0.874435606539356036
7	0.990416985535926053
8	0.59251775051353095
9	0.688247502287665958
10	0.939401152561129993
11	0.960847979674174013
12	0.818769283596453956
13	0.834442059579594053
14	0.788371825897704048
15	0.620618845450902956
16	0.977769943596806024
17	0.840542782838639035
19	0.681836780693319988
\.

COPY room (id, name, area) FROM stdin;
0	Dining room	23
1	Blue bedroom	20
2	Red bedroom	31
3	Yellow bedroom	27
4	Green bedroom	37
5	Living room	14
6	Kitchen	18
7	First bathroom	26
8	Second bathroom	34
9	Library	27
\.

COPY sightings ("time", person, room, witness) FROM stdin;
02:30:00	19	8	0
05:00:00	11	9	0
03:00:00	19	2	0
13:00:00	8	8	0
22:30:00	5	1	0
05:30:00	19	5	0
16:00:00	11	8	0
18:30:00	11	9	0
13:30:00	11	4	0
13:30:00	6	7	0
05:30:00	13	3	0
10:00:00	5	0	0
04:00:00	14	6	0
01:30:00	12	1	0
15:00:00	1	5	0
21:00:00	16	6	0
06:30:00	17	6	1
01:30:00	10	5	1
09:30:00	9	7	1
07:00:00	17	1	1
10:30:00	3	3	1
01:00:00	18	2	1
09:00:00	17	2	1
05:30:00	18	6	1
04:30:00	16	2	2
15:30:00	14	8	2
19:00:00	1	8	2
22:00:00	5	9	2
22:30:00	0	0	2
18:00:00	10	3	2
06:00:00	11	5	2
05:00:00	17	8	2
17:00:00	14	3	2
17:30:00	12	9	2
22:30:00	10	3	2
21:00:00	5	7	2
09:00:00	9	4	2
08:30:00	18	2	2
10:00:00	13	3	2
23:00:00	7	9	2
13:30:00	5	6	3
19:00:00	16	3	3
03:00:00	16	4	3
12:30:00	16	0	3
20:30:00	8	0	3
14:00:00	15	1	3
22:00:00	8	3	3
10:00:00	15	7	3
11:00:00	15	3	3
00:00:00	15	4	3
22:00:00	14	9	3
02:30:00	15	7	4
08:00:00	11	6	4
15:00:00	13	3	4
20:00:00	8	7	4
21:00:00	7	3	4
19:00:00	15	7	4
22:30:00	9	6	5
06:00:00	0	1	5
02:30:00	0	5	5
17:30:00	1	1	5
18:00:00	7	4	5
04:30:00	18	3	5
14:30:00	17	9	5
21:30:00	15	4	5
10:00:00	1	9	5
03:00:00	3	0	5
05:30:00	3	8	5
19:30:00	17	4	6
16:30:00	0	5	7
11:00:00	1	9	7
13:30:00	18	8	7
13:00:00	12	9	7
19:30:00	3	9	7
20:30:00	3	0	7
15:00:00	6	3	7
19:30:00	6	7	7
19:30:00	10	5	7
13:00:00	5	3	8
15:00:00	14	2	8
01:00:00	1	6	8
08:00:00	7	4	8
09:30:00	12	1	8
20:30:00	12	9	8
10:30:00	11	9	8
06:30:00	7	0	8
11:30:00	13	1	8
15:30:00	5	0	9
04:00:00	6	1	9
22:30:00	2	5	9
01:00:00	8	6	9
13:30:00	15	1	9
07:00:00	19	9	9
21:00:00	7	2	9
18:00:00	0	3	9
08:30:00	14	0	9
03:30:00	11	9	9
05:30:00	3	6	9
20:00:00	15	3	9
06:00:00	7	6	9
16:00:00	14	1	9
19:00:00	7	1	10
12:00:00	17	8	10
09:00:00	8	7	10
21:00:00	8	1	10
01:00:00	8	0	10
09:30:00	17	5	10
08:00:00	3	4	10
21:00:00	18	1	10
22:00:00	3	4	10
11:00:00	15	2	10
01:30:00	18	1	10
08:00:00	14	0	10
06:00:00	7	2	10
04:00:00	18	2	10
21:00:00	12	4	10
01:00:00	4	0	10
18:30:00	13	4	10
22:00:00	5	1	10
23:30:00	11	6	10
04:30:00	5	3	11
04:30:00	12	2	11
13:30:00	7	1	11
08:30:00	7	9	11
00:00:00	6	7	11
11:30:00	6	1	11
20:00:00	6	2	11
07:00:00	9	6	11
10:00:00	16	2	11
04:00:00	8	0	11
07:30:00	15	1	11
20:30:00	10	9	11
19:30:00	3	0	11
04:30:00	4	7	11
12:00:00	1	0	11
23:30:00	17	9	11
18:00:00	4	4	11
21:00:00	0	6	11
14:30:00	17	8	12
07:30:00	10	8	12
05:30:00	13	7	12
19:30:00	18	5	12
21:30:00	8	4	12
21:30:00	11	5	12
13:30:00	3	9	12
08:00:00	2	2	12
08:00:00	5	1	12
01:00:00	13	7	12
15:00:00	19	3	12
21:30:00	3	3	12
11:00:00	12	2	13
04:30:00	3	0	13
02:30:00	3	9	13
05:30:00	5	5	13
01:00:00	1	5	13
09:00:00	15	2	13
22:00:00	18	7	13
18:30:00	7	7	13
08:30:00	18	7	13
09:30:00	6	1	13
21:00:00	6	5	13
16:30:00	19	5	13
15:30:00	1	6	14
07:30:00	7	9	14
04:30:00	13	2	14
10:00:00	17	9	14
07:30:00	12	5	14
15:30:00	8	6	14
10:00:00	18	9	14
18:00:00	0	6	14
17:30:00	2	7	14
18:30:00	5	5	14
04:00:00	4	8	14
12:30:00	7	4	14
00:30:00	19	5	14
14:30:00	9	1	14
09:00:00	3	9	14
14:00:00	7	2	14
00:30:00	12	6	14
16:00:00	8	4	15
23:00:00	12	1	15
13:30:00	18	2	15
11:30:00	2	4	15
00:00:00	10	9	15
00:30:00	3	7	15
03:30:00	3	1	15
00:30:00	0	2	15
16:30:00	10	2	15
08:00:00	8	6	15
06:00:00	2	2	15
03:00:00	13	1	15
06:00:00	8	5	15
15:00:00	18	3	15
01:30:00	3	0	15
02:30:00	5	8	15
22:30:00	19	7	15
22:00:00	15	4	16
10:30:00	0	5	17
17:00:00	1	5	17
12:30:00	5	3	17
00:00:00	19	7	17
12:00:00	1	7	17
16:00:00	5	7	17
14:00:00	3	8	17
14:30:00	14	0	17
04:00:00	6	3	19
11:00:00	4	5	19
15:30:00	5	5	19
\.

ALTER TABLE ONLY person
    ADD CONSTRAINT person_pkey PRIMARY KEY (id);

ALTER TABLE ONLY reliability
    ADD CONSTRAINT reliability_pkey PRIMARY KEY (person);

ALTER TABLE ONLY room
    ADD CONSTRAINT room_pkey PRIMARY KEY (id);

ALTER TABLE ONLY reliability
    ADD CONSTRAINT reliability_person_fkey FOREIGN KEY (person) REFERENCES person(id);

ALTER TABLE ONLY sightings
    ADD CONSTRAINT sightings_person_fkey FOREIGN KEY (person) REFERENCES person(id);

ALTER TABLE ONLY sightings
    ADD CONSTRAINT sightings_room_fkey FOREIGN KEY (room) REFERENCES room(id);

ALTER TABLE ONLY sightings
    ADD CONSTRAINT sightings_witness_fkey FOREIGN KEY (witness) REFERENCES person(id);

CREATE TABLE s AS
  SELECT time, person.name AS person, p2.name AS witness, room.name AS room
  FROM sightings JOIN person ON person=id JOIN person AS p2 ON witness=p2.id JOIN room ON room=room.id;

SELECT add_provenance('s');
SELECT create_provenance_mapping('witness_mapping','s','witness');

CREATE TABLE consistent_s AS
    SELECT time,person,room
    FROM s
  EXCEPT
    SELECT s1.time,s1.person,s1.room
    FROM s AS s1, s AS s2
    WHERE s1.person=s2.person AND s1.time=s2.time AND s1.room<>s2.room;

CREATE TABLE suspects AS
  SELECT DISTINCT person FROM consistent_s
  WHERE room LIKE '% bedroom' AND time BETWEEN '00:00:00' AND '08:00:00';

ALTER table s ADD COLUMN count int;
UPDATE s SET count=1;
SELECT create_provenance_mapping('count_mapping','s','count');
CREATE TABLE results AS SELECT *,sr_counting(provenance(),'count_mapping') AS c FROM suspects ORDER BY c, person;
SELECT remove_provenance('results');
SELECT * FROM results;
DROP TABLE results;

ALTER table s ADD COLUMN reliability float;
UPDATE s
SET reliability=score
FROM reliability, person
WHERE reliability.person=person.id AND person.name=s.witness;
DO $$ BEGIN
PERFORM set_prob(provenance(),reliability) FROM s;
END $$;
CREATE TABLE results AS
SELECT *,
         ROUND(probability_evaluate(provenance(),'possible-worlds')::NUMERIC,5)
FROM suspects
WHERE probability_evaluate(provenance(),'possible-worlds')>0.99 AND
      person<>'Daphine';
SELECT remove_provenance('results');
SELECT * FROM results;
DROP TABLE results;