/*
 * Copyright (C) 2010  Laurentian University
 * Dan Scott <dscott@laurentian.ca> 
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 */

DROP SCHEMA IF EXISTS social CASCADE;

BEGIN;

CREATE SCHEMA social;

CREATE TABLE social.user_rating (
    id          BIGSERIAL   PRIMARY KEY,
    record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id),
    value       INT         NOT NULL,
    creator     INT         NOT NULL REFERENCES actor.usr (id),
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
CREATE INDEX social_user_rating_record_idx ON social.user_rating( record );
CREATE INDEX social_user_rating_creator_idx ON social.user_rating( creator );

CREATE TABLE social.user_review (
    id          BIGSERIAL   PRIMARY KEY,
    record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id),
    value       TEXT        NOT NULL,
    creator     INT         NOT NULL REFERENCES actor.usr (id),
    editor      INT         REFERENCES actor.usr (id),
    approver    INT         REFERENCES actor.usr (id),
    approved    BOOL        NOT NULL DEFAULT FALSE,
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
CREATE INDEX social_user_review_approved_idx ON social.user_review( approved );
CREATE INDEX social_user_review_record_idx ON social.user_review( record );
CREATE INDEX social_user_review_creator_idx ON social.user_review( creator );

CREATE TABLE social.tag (
    id          BIGSERIAL   PRIMARY KEY,
    value       TEXT        NOT NULL,
    approver    INT         REFERENCES actor.usr (id),
    approved    BOOL        NOT NULL DEFAULT FALSE,
    editor      INT         REFERENCES actor.usr (id),
    edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
CREATE INDEX social_tag_approved_idx ON social.tag( approved );

CREATE TABLE social.biblio_tag_map (
    id          BIGSERIAL   PRIMARY KEY,
    record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id),
    tag         BIGINT      NOT NULL REFERENCES social.tag (id),
    creator     INT         NOT NULL REFERENCES actor.usr (id),
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
CREATE INDEX social_biblio_tag_map_tag_record_idx ON social.biblio_tag_map( record );
CREATE INDEX social_biblio_tag_map_tag_creator_idx ON social.biblio_tag_map( creator );

CREATE OR REPLACE VIEW social.activity_stream AS
SELECT actor, object, target, stamped, activity FROM (
  (
    SELECT acirc.usr AS "actor", acirc.id AS "object", acn.record AS "target", acirc.xact_finish AS "stamped", 'circ' AS "activity"
      FROM action.circulation acirc
        INNER JOIN asset.copy ac ON ac.id = acirc.target_copy
        INNER JOIN asset.call_number acn ON acn.id = ac.call_number
      WHERE xact_finish IS NOT NULL
  )
  UNION
  (
    SELECT creator AS "actor", id AS "object", record AS "target", create_date AS "stamped", 'add_review' AS activity
      FROM social.user_review
      WHERE approved IS NOT NULL
  )
  UNION
  (
    SELECT creator AS "actor", id AS "object", record AS "target", edit_date AS "stamped", 'add_rating' AS activity
      FROM social.user_rating
  )
  UNION
  (
      SELECT owner AS "actor", id AS "object", NULL AS "target", create_time, 'add_bookbag' AS "activity"
        FROM container.biblio_record_entry_bucket
        WHERE pub IS TRUE
  )
  UNION
  (
      SELECT cbreb.owner AS "actor", cbrebi.target_biblio_record_entry AS "object", cbrebi.bucket AS "target", cbrebi.create_time, 'add_bookbag_item' AS "activity"
        FROM container.biblio_record_entry_bucket_item cbrebi
          INNER JOIN container.biblio_record_entry_bucket cbreb ON cbrebi.bucket = cbreb.id
        WHERE cbreb.pub IS TRUE
  )
) AS activities
ORDER BY 4 DESC;

COMMIT;
