--
-- Tables for the YATA extension
--

-- Annotations table
CREATE TABLE IF NOT EXISTS /*_*/yata_annotation (
    -- unique identifier for every annotation
    id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

    -- the comment itself.
    comment blob,

    -- the wiki text which is annotated
    wiki_text blob,

    -- foreign key to page.page_id
    page_id int unsigned NOT NULL,

    -- foreign key to user.user_id
    user_id int unsigned NOT NULL,

    -- start position of the annotation according to the wiki sourcecode
    start_char int unsigned,

    -- a unique bookmark for every annotation 
    -- so we later can link directly to the part 
    -- of the page an annotation was found.
    bookmark varchar(6),

    insert_date varbinary(14),
    modified_date varbinary(14)

)/*$wgDBTableOptions*/;

DROP INDEX IF EXISTS /*i*/yata_annot_page_idx ON /*_*/yata_annotation;
DROP INDEX IF EXISTS /*i*/yata_annot_user_idx ON /*_*/yata_annotation;
CREATE INDEX /*i*/yata_annot_page_idx ON /*_*/yata_annotation (page_id);
CREATE INDEX /*i*/yata_annot_user_idx ON /*_*/yata_annotation (user_id);


-- Page-Annotations table
CREATE TABLE IF NOT EXISTS /*_*/yata_page_annotation (
    -- unique identifier for every annotation
    id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

    -- foreign key to page.page_id
    page_id int unsigned NOT NULL,

    name varchar(255) NOT NULL,
    value varchar(255) NOT NULL,

    insert_date varbinary(14),
    modified_date varbinary(14),
    user_id int unsigned NOT NULL

)/*$wgDBTableOptions*/;

DROP INDEX IF EXISTS /*i*/yata_page_annot_name_idx   ON /*_*/yata_page_annotation;
DROP INDEX IF EXISTS /*i*/yata_page_annot_page_idx   ON /*_*/yata_page_annotation;
CREATE INDEX /*i*/yata_page_annot_name_idx   ON /*_*/yata_page_annotation (name);
CREATE INDEX /*i*/yata_page_annot_page_idx   ON /*_*/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 /*_*/yata_annotation_category (

    -- foreign key to a page (for faster lookups)
    page_id int unsigned NOT NULL,

    -- foreign key to an annotation
    annotation_id int unsigned NOT NULL,

    -- foreign key to a category
    category_id int unsigned NOT NULL
)/*$wgDBTableOptions*/;

DROP INDEX IF EXISTS /*i*/yata_ann_cat_page_idx ON /*_*/yata_annotation_category;
DROP INDEX IF EXISTS /*i*/yata_ann_cat_annotation_idx ON /*_*/yata_annotation_category;
DROP INDEX IF EXISTS /*i*/yata_ann_cat_category_idx ON /*_*/yata_annotation_category;

CREATE INDEX /*i*/yata_ann_cat_page_idx ON /*_*/yata_annotation_category (page_id);
CREATE INDEX /*i*/yata_ann_cat_annotation_idx ON /*_*/yata_annotation_category (annotation_id);
CREATE INDEX /*i*/yata_ann_cat_category_idx ON /*_*/yata_annotation_category (category_id);

--
-- Tables for the YATA extension
--

-- Annotation Category table

-- Annotation category table
CREATE TABLE IF NOT EXISTS /*_*/yata_category (
    -- unique identifier for every annotation category
    id            int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name          varchar(255),
    hashtag       varchar(255),
    description   blob,
    -- categories are organized as a tree
    parent_id     int unsigned
);

DROP INDEX IF EXISTS /*i*/yata_category_name   ON /*_*/yata_category;
DROP INDEX IF EXISTS /*i*/yata_category_parent ON /*_*/yata_category;
DROP INDEX IF EXISTS /*i*/yata_category_hashtag_unq ON /*_*/yata_category;

CREATE INDEX /*i*/yata_category_name   ON /*_*/yata_category (name);
CREATE INDEX /*i*/yata_category_parent ON /*_*/yata_category (parent_id);
CREATE UNIQUE INDEX /*i*/yata_category_hashtag_unq ON /*_*/yata_category(hashtag);

