-- DROP TABLE IF EXISTS dc_blog;
CREATE TABLE dc_blog (
    blog_id VARCHAR(32) NOT NULL,
    blog_uid VARCHAR(32) NOT NULL,
    blog_creadt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    blog_upddt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    blog_url VARCHAR(255) NOT NULL,
    blog_name VARCHAR(255) NOT NULL,
    blog_desc TEXT,
    blog_status SMALLINT DEFAULT 1 NOT NULL,
    PRIMARY KEY (blog_id)
);

-- DROP TABLE IF EXISTS dc_category;
CREATE TABLE dc_category (
    cat_id BIGINT NOT NULL,
    blog_id VARCHAR(32) NOT NULL,
    cat_title VARCHAR(255) NOT NULL,
    cat_url VARCHAR(255) NOT NULL,
    cat_desc TEXT,
    cat_position INTEGER DEFAULT 0,
    cat_lft INTEGER,
    cat_rgt INTEGER,
    UNIQUE (cat_url, blog_id),
    CONSTRAINT dc_fk_category_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (cat_id)
);

-- DROP TABLE IF EXISTS dc_user;
CREATE TABLE dc_user (
    user_id VARCHAR(32) NOT NULL,
    user_super SMALLINT,
    user_status SMALLINT DEFAULT 1 NOT NULL,
    user_pwd VARCHAR(40) NOT NULL,
    user_change_pwd SMALLINT DEFAULT 0 NOT NULL,
    user_recover_key VARCHAR(32) DEFAULT NULL,
    user_name VARCHAR(255) DEFAULT NULL,
    user_firstname VARCHAR(255) DEFAULT NULL,
    user_displayname VARCHAR(255) DEFAULT NULL,
    user_email VARCHAR(255) DEFAULT NULL,
    user_url VARCHAR(255) DEFAULT NULL,
    user_desc TEXT,
    user_default_blog VARCHAR(32) DEFAULT NULL,
    user_options TEXT,
    user_lang VARCHAR(5) DEFAULT NULL,
    user_tz VARCHAR(128) DEFAULT 'UTC' NOT NULL,
    user_post_status SMALLINT DEFAULT (-2) NOT NULL,
    user_creadt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    user_upddt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    CONSTRAINT dc_fk_user_default_blog FOREIGN KEY (user_default_blog) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE SET NULL,
    PRIMARY KEY (user_id)
);

-- DROP TABLE IF EXISTS dc_post;
CREATE TABLE dc_post (
    post_id BIGINT NOT NULL,
    blog_id VARCHAR(32) NOT NULL,
    user_id VARCHAR(32) NOT NULL,
    cat_id BIGINT,
    post_dt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    post_tz VARCHAR(128) DEFAULT 'UTC' NOT NULL,
    post_creadt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    post_upddt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    post_password VARCHAR(32) DEFAULT NULL,
    post_type VARCHAR(32) DEFAULT 'post' NOT NULL,
    post_format VARCHAR(32) DEFAULT 'xhtml' NOT NULL,
    post_url VARCHAR(255) NOT NULL,
    post_lang VARCHAR(5) DEFAULT NULL,
    post_title VARCHAR(255) DEFAULT NULL,
    post_excerpt TEXT,
    post_excerpt_xhtml TEXT,
    post_content TEXT,
    post_content_xhtml TEXT NOT NULL,
    post_notes TEXT,
    post_meta TEXT,
    post_words TEXT,
    post_status SMALLINT DEFAULT 0 NOT NULL,
    post_selected SMALLINT DEFAULT 0 NOT NULL,
    post_position INTEGER DEFAULT 0 NOT NULL,
    post_open_comment SMALLINT DEFAULT 0 NOT NULL,
    post_open_tb SMALLINT DEFAULT 0 NOT NULL,
    nb_comment INTEGER DEFAULT 0 NOT NULL,
    nb_trackback INTEGER DEFAULT 0 NOT NULL,
    UNIQUE (post_url, post_type, blog_id),
    CONSTRAINT dc_fk_post_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT dc_fk_post_category FOREIGN KEY (cat_id) REFERENCES dc_category(cat_id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT dc_fk_post_user FOREIGN KEY (user_id) REFERENCES dc_user(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (post_id)
);

-- DROP TABLE IF EXISTS dc_comment;
CREATE TABLE dc_comment (
    comment_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    comment_dt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    comment_tz VARCHAR(128) DEFAULT 'UTC' NOT NULL,
    comment_upddt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    comment_author VARCHAR(255) DEFAULT NULL,
    comment_email VARCHAR(255) DEFAULT NULL,
    comment_site VARCHAR(255) DEFAULT NULL,
    comment_content TEXT,
    comment_words TEXT,
    comment_ip VARCHAR(39) DEFAULT NULL,
    comment_status SMALLINT DEFAULT 0,
    comment_spam_status VARCHAR(128) DEFAULT 0,
    comment_spam_filter VARCHAR(32) DEFAULT NULL,
    comment_trackback SMALLINT DEFAULT 0 NOT NULL,
    CONSTRAINT dc_fk_comment_post FOREIGN KEY (post_id) REFERENCES dc_post(post_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (comment_id)
);

-- DROP TABLE IF EXISTS dc_link;
CREATE TABLE dc_link (
    link_id BIGINT NOT NULL,
    blog_id VARCHAR(32) NOT NULL,
    link_href VARCHAR(255) NOT NULL,
    link_title VARCHAR(255) NOT NULL,
    link_desc VARCHAR(255),
    link_lang VARCHAR(5),
    link_xfn VARCHAR(255),
    link_position INTEGER DEFAULT 0 NOT NULL,
    CONSTRAINT dc_fk_link_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (link_id)
);

-- DROP TABLE IF EXISTS dc_log;
CREATE TABLE dc_log (
    log_id BIGINT NOT NULL,
    user_id VARCHAR(32),
    blog_id VARCHAR(32),
    log_table VARCHAR(255) NOT NULL,
    log_dt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    log_ip VARCHAR(39) NOT NULL,
    log_msg TEXT,
    CONSTRAINT dc_fk_log_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE SET NULL,
    PRIMARY KEY (log_id)
);

-- DROP TABLE IF EXISTS dc_media;
CREATE TABLE dc_media (
    media_id BIGINT NOT NULL,
    user_id VARCHAR(32) NOT NULL,
    media_path VARCHAR(255) NOT NULL,
    media_title VARCHAR(255) NOT NULL,
    media_file VARCHAR(255) NOT NULL,
    media_dir VARCHAR(255) DEFAULT '.' NOT NULL,
    media_meta TEXT,
    media_dt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    media_creadt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    media_upddt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    media_private SMALLINT DEFAULT 0 NOT NULL,
    CONSTRAINT dc_fk_media FOREIGN KEY (media_id) REFERENCES dc_media(media_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT dc_fk_media_user FOREIGN KEY (user_id) REFERENCES dc_user(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (media_id)
);

-- DROP TABLE IF EXISTS dc_meta;
CREATE TABLE dc_meta (
    meta_id VARCHAR(255) NOT NULL,
    meta_type VARCHAR(64) NOT NULL,
    post_id BIGINT NOT NULL,
    CONSTRAINT dc_fk_meta_post FOREIGN KEY (post_id) REFERENCES dc_post(post_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (meta_id, meta_type, post_id)
);

-- DROP TABLE IF EXISTS dc_permissions;
CREATE TABLE dc_permissions (
    user_id VARCHAR(32) NOT NULL,
    blog_id VARCHAR(32) NOT NULL,
    permissions TEXT,
    CONSTRAINT dc_fk_permissions_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT dc_fk_permissions_user FOREIGN KEY (user_id) REFERENCES dc_user(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (user_id, blog_id)
);

-- DROP TABLE IF EXISTS dc_ping;
CREATE TABLE dc_ping (
    post_id BIGINT NOT NULL,
    ping_url VARCHAR(255) NOT NULL,
    ping_dt TIMESTAMP without time zone DEFAULT now() NOT NULL,
    CONSTRAINT dc_fk_ping_post FOREIGN KEY (post_id) REFERENCES dc_post(post_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (post_id, ping_url)
);

-- DROP TABLE IF EXISTS dc_post_media;
CREATE TABLE dc_post_media (
    media_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    link_type VARCHAR(32) NOT NULL DEFAULT 'attachment',
    CONSTRAINT dc_fk_media_post FOREIGN KEY (post_id) REFERENCES dc_post(post_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (media_id, post_id, link_type)
);

-- DROP TABLE IF EXISTS dc_pref;
CREATE TABLE dc_pref (
    pref_id VARCHAR(255) NOT NULL,
    user_id VARCHAR(32),
    pref_ws VARCHAR(32) DEFAULT 'system' NOT NULL,
    pref_value text,
    pref_type VARCHAR(8) DEFAULT 'string' NOT NULL,
    pref_label TEXT,
    CONSTRAINT dc_fk_pref_user FOREIGN KEY (user_id) REFERENCES dc_user(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE (pref_ws, pref_id, user_id)
);

-- DROP TABLE IF EXISTS dc_session;
CREATE TABLE dc_session (
    ses_id VARCHAR(40) NOT NULL,
    ses_time INTEGER DEFAULT 0 NOT NULL,
    ses_start INTEGER DEFAULT 0 NOT NULL,
    ses_value TEXT NOT NULL,
    PRIMARY KEY (ses_id)
);

-- DROP TABLE IF EXISTS dc_setting;
CREATE TABLE dc_setting (
    setting_id VARCHAR(255) NOT NULL,
    blog_id VARCHAR(32),
    setting_ns VARCHAR(32) DEFAULT 'system' NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(8) DEFAULT 'string' NOT NULL,
    setting_label TEXT,
    CONSTRAINT dc_fk_setting_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE (setting_ns, setting_id, blog_id)
);

-- DROP TABLE IF EXISTS dc_spamrule;
CREATE TABLE dc_spamrule (
    rule_id bigint NOT NULL,
    blog_id VARCHAR(32),
    rule_type VARCHAR(16) DEFAULT 'word' NOT NULL,
    rule_content VARCHAR(128) NOT NULL,
    CONSTRAINT dc_fk_spamrule_blog FOREIGN KEY (blog_id) REFERENCES dc_blog(blog_id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (rule_id)
);

-- DROP TABLE IF EXISTS dc_version;
CREATE TABLE dc_version (
    module VARCHAR(64) NOT NULL,
    version VARCHAR(32) NOT NULL,
    PRIMARY KEY (module)
);

CREATE INDEX dc_idx_pref_user_id ON dc_pref USING btree (user_id);
CREATE INDEX dc_idx_pref_user_id_null ON dc_pref USING btree (((user_id IS NULL)));
CREATE INDEX dc_idx_spamrule_blog_id ON dc_spamrule USING btree (blog_id);
CREATE INDEX dc_idx_spamrule_blog_id_null ON dc_spamrule USING btree (((blog_id IS NULL)));
CREATE INDEX dc_idx_blog_blog_upddt ON dc_blog USING btree (blog_upddt);
CREATE INDEX dc_idx_blog_post_post_dt_post_id ON dc_post USING btree (blog_id, post_dt, post_id);
CREATE INDEX dc_idx_blog_post_post_status ON dc_post USING btree (blog_id, post_status);
CREATE INDEX dc_idx_category_blog_id ON dc_category USING btree (blog_id);
CREATE INDEX dc_idx_category_cat_lft_blog_id ON dc_category USING btree (blog_id, cat_lft);
CREATE INDEX dc_idx_category_cat_rgt_blog_id ON dc_category USING btree (blog_id, cat_rgt);
CREATE INDEX dc_idx_comment_post_id ON dc_comment USING btree (post_id);
CREATE INDEX dc_idx_comment_post_id_dt_status ON dc_comment USING btree (post_id, comment_dt, comment_status);
CREATE INDEX dc_idx_link_blog_id ON dc_link USING btree (blog_id);
CREATE INDEX dc_idx_log_user_id ON dc_log USING btree (user_id);
CREATE INDEX dc_idx_media_media_path ON dc_media USING btree (media_path, media_dir);
CREATE INDEX dc_idx_media_user_id ON dc_media USING btree (user_id);
CREATE INDEX dc_idx_meta_meta_type ON dc_meta USING btree (meta_type);
CREATE INDEX dc_idx_meta_post_id ON dc_meta USING btree (post_id);
CREATE INDEX dc_idx_permissions_blog_id ON dc_permissions USING btree (blog_id);
CREATE INDEX dc_idx_post_blog_id ON dc_post USING btree (blog_id);
CREATE INDEX dc_idx_post_cat_id ON dc_post USING btree (cat_id);
CREATE INDEX dc_idx_post_media_post_id ON dc_post_media USING btree (post_id);
CREATE INDEX dc_idx_post_post_dt ON dc_post USING btree (post_dt);
CREATE INDEX dc_idx_post_post_dt_post_id ON dc_post USING btree (post_dt, post_id);
CREATE INDEX dc_idx_post_user_id ON dc_post USING btree (user_id);
CREATE INDEX dc_idx_setting_blog_id ON dc_setting USING btree (blog_id);
CREATE INDEX dc_idx_setting_blog_id_null ON dc_setting USING btree (((blog_id IS NULL)));
CREATE INDEX dc_idx_user_user_default_blog ON dc_user USING btree (user_default_blog);
CREATE INDEX dc_idx_user_user_super ON dc_user USING btree (user_super);