/* Script designed to demonstrate select count(*) problem; */

CREATE TABLE parent(
	parent_id INT4 PRIMARY KEY,
	parent_name text,
	parent_activity int2
);
GRANT ALL on parent TO PUBLIC;
INSERT INTO parent VALUES (0,'zero',0);
INSERT INTO parent VALUES (1,'one',0);
INSERT INTO parent VALUES (2,'two',0);

CREATE TABLE entry(
	entry_id INT4 PRIMARY KEY,
	entry_description_text TEXT,
	entry_live bool NOT NULL DEFAULT 't'
);
GRANT ALL on entry TO PUBLIC;
INSERT INTO entry VALUES (1,'entry one','t');
INSERT INTO entry VALUES (2,'entry two','t');
INSERT INTO entry VALUES (3,'entry three','t');

CREATE TABLE entry_parent_map(
	parent_id INT4 NOT NULL REFERENCES parent ON DELETE CASCADE,
	entry_id INT4 NOT NULL REFERENCES entry ON DELETE CASCADE
);
GRANT ALL on entry_parent_map TO PUBLIC;
INSERT INTO entry_parent_map VALUES (0,1);
INSERT INTO entry_parent_map VALUES(1,2);
INSERT INTO entry_parent_map VALUES (2,3);
CREATE INDEX e_p_m_p_idx ON entry_parent_map (parent_id);
CREATE INDEX e_p_m_e_idx ON entry_parent_map (entry_id);

CREATE TABLE detail (
	detail_id INT4 PRIMARY KEY,
	entry_id INT4 NOT NULL REFERENCES entry ON DELETE NO ACTION,
	amount INT4 NOT NULL
);
GRANT ALL ON detail to PUBLIC;
CREATE INDEX detail_entry_id_idx ON detail (entry_id);
INSERT INTO detail VALUES (1,1,1);
INSERT INTO detail VALUES (2,1,5);
INSERT INTO detail VALUES (3,2,2);
INSERT INTO detail VALUES (4,2,100);
INSERT INTO detail VALUES (5,3,7);



CREATE VIEW entry_view AS
SELECT entry.entry_id as id,
       entry.entry_description_text as description_text,
       entry_parent_map.parent_id,
       min (detail.amount) as minamount
FROM   entry,
       entry_parent_map,
       parent,
       detail
WHERE  entry_live='t'
  AND  entry.entry_id=entry_parent_map.entry_id
  AND  entry_parent_map.parent_id=parent.parent_id
  AND  parent.parent_activity=0
  AND  detail.entry_id=entry.entry_id
GROUP BY 
      entry.entry_id,
      entry.entry_description_text,
      entry_parent_map.parent_id
;
GRANT SELECT ON entry_view TO PUBLIC;

SELECT * from entry_view;

SELECT count(*) from entry_view;

