/*
DROP DATABASE w3;
CREATE DATABASE IF NOT EXISTS w3 DEFAULT CHARSET=UTF8 DEFAULT COLLATE=utf8_bin;

USE w3;
*/

CREATE TABLE client (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    catalogue_id INTEGER,
    username varchar(255),
    password varchar(255), 
    suffix varchar(255)
) ENGINE=InnoDB;

CREATE TABLE namespace (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    namespace varchar(254)
) ENGINE=InnoDB;

CREATE TABLE model (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    client_id INTEGER,
    name varchar(255),
    namespace_id INTEGER,
    xml TEXT
) ENGINE=InnoDB;

CREATE TABLE field (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    model_id INTEGER,
    name varchar(50),
    printable varchar(50),
    field_type varchar(50),
    is_ml BOOLEAN
) ENGINE=InnoDB;

CREATE TABLE fvalue (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    field_id INTEGER,
    namespace_id INTEGER
) ENGINE=InnoDB;

CREATE TABLE map (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    namespace_id INTEGER,
    field_id INTEGER,
    schema_name varchar(100),
    element varchar(200),
    source_name varchar(100)
) ENGINE=InnoDB;

CREATE TABLE content (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    rid varchar(255),
    client_id INT,
    model_id INT,
    created TIMESTAMP DEFAULT '0000-00-00 00:00',
    updated TIMESTAMP,
    user_id INT,
    UNIQUE INDEX (rid)
) ENGINE=InnoDB;

CREATE TABLE deleted LIKE content;
ALTER TABLE deleted ADD COLUMN deleted TIMESTAMP DEFAULT '0000-00-00 00:00';

CREATE TABLE attachment (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    content_id INTEGER,
    filename varchar(1000)
) ENGINE=InnoDB;

CREATE TABLE querylog (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    gate varchar(50),
    query text,
    created TIMESTAMP DEFAULT NOW()
) ENGINE=InnoDB;

CREATE TABLE accesslog (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    content_id INTEGER,
    gate varchar(50),
    created TIMESTAMP DEFAULT NOW(),
    INDEX using BTREE(content_id)
) ENGINE=InnoDB;

CREATE TABLE acl (
    content_id INTEGER,
    owner varchar(255),
    viewUsers varchar(255),
    viewGroups varchar(255),
    editUsers varchar(255),
    editGroups varchar(255),
    downUsers varchar(255),
    downGroups varchar(255),
    INDEX (content_id),
    INDEX (owner)
) ENGINE=InnoDB;

CREATE TABLE single_string (
    content_id INTEGER,
    fvalue_id INTEGER,
    value TEXT,
    INDEX (content_id),
    INDEX (fvalue_id)
) ENGINE=InnoDB;

CREATE TABLE single_integer (
    content_id INTEGER,
    fvalue_id INTEGER,
    value INTEGER,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (value)
) ENGINE=InnoDB;

CREATE TABLE single_ml_string (
    content_id INTEGER,
    fvalue_id INTEGER,
    lang varchar(4),
    value TEXT,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (fvalue_id, lang)
) ENGINE=InnoDB;

CREATE TABLE single_ml_integer (
    content_id INTEGER,
    fvalue_id INTEGER,
    lang varchar(4),
    value INTEGER,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (fvalue_id, lang),
    INDEX (value)
) ENGINE=InnoDB;

CREATE TABLE multi_string (
    content_id INTEGER,
    fvalue_id INTEGER,
    value TEXT,
    INDEX (content_id),
    INDEX (fvalue_id)
) ENGINE=InnoDB;

CREATE TABLE multi_integer (
    content_id INTEGER,
    fvalue_id INTEGER,
    value INTEGER,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (value)
) ENGINE=InnoDB;

CREATE TABLE multi_ml_string (
    content_id INTEGER,
    fvalue_id INTEGER,
    lang varchar(4),
    value TEXT,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (lang)
) ENGINE=InnoDB;

CREATE TABLE multi_ml_integer (
    content_id INTEGER,
    fvalue_id INTEGER,
    lang varchar(4),
    value INTEGER,
    INDEX (content_id),
    INDEX (fvalue_id),
    INDEX (lang),
    INDEX (value)
) ENGINE=InnoDB;

CREATE TABLE content_fti (
    content_id INTEGER,
    content TEXT,
    namespace_id INT,
    INDEX (content_id),
    FULLTEXT (content)
) ENGINE=MyISAM DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;

CREATE TABLE microformat (
    content_id INTEGER,
    fvalue_id INTEGER,
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    value TEXT,
    INDEX (content_id),
    INDEX (fvalue_id)
) ENGINE=InnoDB;

CREATE TABLE vcard (
    content_id INTEGER,
    microformat_id INTEGER,
    first_name varchar(254),
    last_name varchar(254),
    email varchar(254),
    org varchar(1000),
    INDEX (content_id),
    INDEX (microformat_id)
) ENGINE=InnoDB CHARSET UTF8 COLLATE utf8_general_ci;

CREATE TABLE storedQuery (
    name varchar(32) NOT NULL PRIMARY KEY,
    description varchar(512),
    query TEXT
) ENGINE=InnoDB;

CREATE TABLE annotation (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(32),
    description varchar(255),
    client_id INTEGER,
    secure BOOLEAN,
    singleton BOOLEAN
) ENGINE=InnoDB;

CREATE TABLE content_anno (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    content_id INTEGER,
    annotation_id INTEGER,
    user_id INTEGER,
    strings_id INTEGER,
    INDEX (content_id),
    INDEX (annotation_id)
) ENGINE=InnoDB;

CREATE TABLE auth (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    viewUsers varchar(255),
    viewGroups varchar(255),
    editUsers varchar(255),
    editGroups varchar(255)
) ENGINE=InnoDB;

CREATE TABLE ca_x_auth (
    content_anno_id INTEGER,
    auth_id INTEGER,
    INDEX(content_anno_id)
) ENGINE=InnoDB;

CREATE TABLE strings (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    value TEXT
) ENGINE=InnoDB;

CREATE TABLE user (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(255)
) ENGINE=InnoDB;

CREATE TABLE content_refs (
    content_id INTEGER,
    pos INTEGER,
    ref_rid VARCHAR(255),
    INDEX (content_id)
) ENGINE=InnoDB;

CREATE TABLE anno_x_reserved (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    rid varchar(255),
    anno_id INTEGER
) ENGINE=InnoDB CHARSET UTF8 COLLATE utf8_general_ci;   

CREATE TABLE reserved (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    rid varchar(255),
    UNIQUE INDEX (rid)
) ENGINE=InnoDB;

CREATE TABLE upload_tokens (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    rid varchar(255),
    token varchar(255),
    created TIMESTAMP DEFAULT NOW()
) ENGINE=InnoDB;

CREATE TABLE upload_x_reserved (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    rid varchar(255),
    filename varchar(1000),
    deleted boolean
) ENGINE=InnoDB;

alter table content_anno add column last_action timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
update content_anno set last_action = (select created from content where content_id=content.id);

INSERT INTO namespace VALUES (NULL, 'http://trac.htk.tlu.ee/waramu/Collection');
INSERT INTO model VALUES (NULL, NULL, 'Collection', 
    (SELECT id FROM namespace WHERE namespace='http://trac.htk.tlu.ee/waramu/Collection'), NULL);

/*
insert into client (catalogue_id, username, password, suffix) values (12, 'test1', 'test2', 'sp.ee');
*/
