--
-- Tables for the YATA extension
--

-- Annotations table
CREATE TABLE IF NOT EXISTS mediawiki.yata_annotation
(
   id              SERIAL NOT NULL,
   comment         TEXT,
   wiki_text       TEXT,
   page_id         INT NOT NULL,
   user_id         INT NOT NULL,
   start_char      INT,
   bookmark        VARCHAR (10),
   insert_date     timestamp (6) WITH TIME ZONE,
   modified_date   timestamp (6) WITH TIME ZONE,
   PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS yata_annot_page_idx ON mediawiki.yata_annotation (page_id);
CREATE INDEX IF NOT EXISTS yata_annot_user_idx ON mediawiki.yata_annotation (user_id);


--
-- Page-Annotations table
--
CREATE TABLE IF NOT EXISTS mediawiki.yata_page_annotation (
   id              SERIAL NOT NULL,
   page_id         INT NOT NULL,
   "name"          VARCHAR (255),
   "value"         VARCHAR (255),
   insert_date     timestamp (6) WITH TIME ZONE,
   modified_date   timestamp (6) WITH TIME ZONE,
   user_id         INT NOT NULL,
   PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS yata_page_annot_name_idx ON mediawiki.yata_page_annotation (name);
CREATE INDEX IF NOT EXISTS yata_page_annot_page_idx ON mediawiki.yata_page_annotation (page_id);

--
-- Tables for the YATA extension
--

-- Many to many table for annotation and category.
-- Any given annotation can be assigned to more than one category.
CREATE TABLE IF NOT EXISTS mediawiki.yata_annotation_category
(
   page_id         INT NOT NULL,
   annotation_id   INT NOT NULL,
   category_id     INT NOT NULL
);

CREATE INDEX IF NOT EXISTS yata_ann_cat_page_idx ON mediawiki.yata_annotation_category (page_id);
CREATE INDEX IF NOT EXISTS yata_ann_cat_annotation_idx ON mediawiki.yata_annotation_category (annotation_id);
CREATE INDEX IF NOT EXISTS yata_ann_cat_category_idx ON mediawiki.yata_annotation_category (category_id);

--
-- Tables for the YATA extension
--

-- Annotation Category table

CREATE TABLE IF NOT EXISTS mediawiki.yata_category
(
   id            SERIAL NOT NULL,
   "name"        VARCHAR (255),
   hashtag       VARCHAR (255),
   description   TEXT,
   parent_id     INT,
   PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS yata_category_name_idx   ON mediawiki.yata_category (name);
CREATE INDEX IF NOT EXISTS yata_category_parent_idx ON mediawiki.yata_category (parent_id);
CREATE UNIQUE INDEX IF NOT EXISTS yata_category_hashtag_unq ON mediawiki.yata_category(hashtag);

