CREATE VIEW access AS
SELECT c.id AS content_id, u.id AS user_id,
    LEAST(MIN(simple.relevant_date),
        MIN(with_prereq.relevant_date),
        MIN(with_delay.relevant_date),
        MIN(with_both.relevant_date)) AS relevant_date
FROM content AS c
    LEFT OUTER JOIN simple ON simple.content_id = c.id
    LEFT OUTER JOIN with_prereq ON with_prereq.content_id = c.id
    LEFT OUTER JOIN with_delay ON with_delay.content_id = c.id
    LEFT OUTER JOIN with_both ON with_both.content_id = c.id
    CROSS JOIN base_user AS u
WHERE u.id = COALESCE(
    simple.user_id, with_delay.user_id, with_prereq.user_id,
    with_both.user_id)
GROUP BY c.id, u.id;

CREATE VIEW simple AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cc.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_delay AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cu.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_prereq AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    GREATEST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NOT NULL;

CREATE VIEW with_both AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    LEAST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NOT NULL;

CREATE TABLE content (
    id SERIAL
);
CREATE TABLE base_user (
    id SERIAL
);
CREATE TABLE circle (
    id SERIAL
);
CREATE TABLE circleuser (
    circle_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    relevant_date TIMESTAMP with time zone
);
CREATE TABLE circlecontent (
    circle_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    delay INTEGER,
    prereq_id INTEGER,
    relevant_date TIMESTAMP with time zone
);