-- Table: picture_categories

-- DROP TABLE picture_categories;

CREATE TABLE picture_categories
(
  pcid serial NOT NULL,
  pid int4 NOT NULL,
  cat_id int4,
  CONSTRAINT picture_categories_pkey PRIMARY KEY (pcid),
  CONSTRAINT picture_categories_pid_key UNIQUE (pid, cat_id)
) 
WITHOUT OIDS;
ALTER TABLE picture_categories OWNER TO fanart_central;


-- Index: idx_picture_categories_cat_id

-- DROP INDEX idx_picture_categories_cat_id;

CREATE INDEX idx_picture_categories_cat_id
  ON picture_categories
  USING btree
  (cat_id);

-- Index: idx_picture_categories_pid

-- DROP INDEX idx_picture_categories_pid;

CREATE INDEX idx_picture_categories_pid
  ON picture_categories
  USING btree
  (pid);

-- Index: idx_picture_categories_pid_and_cat_id

-- DROP INDEX idx_picture_categories_pid_and_cat_id;

CREATE INDEX idx_picture_categories_pid_and_cat_id
  ON picture_categories
  USING btree
  (pid, cat_id);



-- Trigger: _FAC_replication_logtrigger_28 on picture_categories

-- DROP TRIGGER "_FAC_replication_logtrigger_28" ON picture_categories;

CREATE TRIGGER "_FAC_replication_logtrigger_28"
  AFTER INSERT OR UPDATE OR DELETE
  ON picture_categories
  FOR EACH ROW
  EXECUTE PROCEDURE "_FAC_replication".logtrigger('_FAC_replication', 28, 'kvv');





-- Table: pictures

-- DROP TABLE pictures;

CREATE TABLE pictures
(
-- Inherited:   rating_nudity int2 NOT NULL,
-- Inherited:   rating_violence int2 NOT NULL,
-- Inherited:   rating_sex int2 NOT NULL,
-- Inherited:   rating_racism int2 NOT NULL,
-- Inherited:   rating_spoilers int2 NOT NULL,
-- Inherited:   rating_yaoi int2 NOT NULL,
-- Inherited:   rating_yuri int2 NOT NULL,
-- Inherited:   rating_profanity int2,
  pid serial NOT NULL,
  user_id int4 NOT NULL,
  title varchar(75) NOT NULL DEFAULT ''::character varying,
  filename varchar(120) NOT NULL,
  keywords varchar(75) NOT NULL DEFAULT ''::character varying,
  description text NOT NULL DEFAULT ''::text,
  date_submitted timestamp NOT NULL DEFAULT now(),
  date_edited timestamp NOT NULL DEFAULT now(),
  views int4 NOT NULL DEFAULT 0,
  approved int4 NOT NULL DEFAULT 0,
  ipaddr inet NOT NULL,
  admin_notes text NOT NULL DEFAULT ''::text,
  image_type int4 NOT NULL,
  score int4 NOT NULL DEFAULT 0,
  media_id int2 NOT NULL DEFAULT 0,
  refrence varchar(50) NOT NULL DEFAULT ''::character varying,
  time_taken varchar(50) NOT NULL DEFAULT ''::character varying,
  skill_level int2 NOT NULL DEFAULT 0,
  section_id int4 NOT NULL DEFAULT 0,
  faves_count int4 NOT NULL DEFAULT 0,
  comments_count int4 NOT NULL DEFAULT 0,
  requested_by int4,
  trade_with int4,
  collaboration_with int4,
  contest_entry varchar(25),
  gift_for int4,
  size_width int2,
  size_height int2,
  fti_description tsvector,
  fti_keywords tsvector,
  fti_title tsvector,
  fti_all tsvector,
  old_url text NOT NULL DEFAULT ''::text,
  CONSTRAINT pictures_pkey PRIMARY KEY (pid),
  CONSTRAINT nudity_check CHECK (rating_nudity = 0 OR rating_nudity = 1 OR rating_nudity = 2 OR rating_nudity = 3),
  CONSTRAINT profanity_check CHECK (rating_profanity = 0 OR rating_profanity = 1 OR rating_profanity = 2 OR rating_profanity = 3),
  CONSTRAINT racism_check CHECK (rating_racism = 0 OR rating_racism = 1 OR rating_racism = 2 OR rating_racism = 3),
  CONSTRAINT sex_check CHECK (rating_sex = 0 OR rating_sex = 1 OR rating_sex = 2 OR rating_sex = 3),
  CONSTRAINT spoiler_check CHECK (rating_spoilers = 0 OR rating_spoilers = 1 OR rating_spoilers = 2 OR rating_spoilers = 3),
  CONSTRAINT violence_check CHECK (rating_violence = 0 OR rating_violence = 1 OR rating_violence = 2 OR rating_violence = 3),
  CONSTRAINT yaoi_check CHECK (rating_yaoi = 0 OR rating_yaoi = 1),
  CONSTRAINT yuri_check CHECK (rating_yuri = 0 OR rating_yuri = 1)
) INHERITS (base_ratings) 
WITHOUT OIDS;
ALTER TABLE pictures OWNER TO fanart_central;


-- Index: idx_date_submitted

-- DROP INDEX idx_date_submitted;

CREATE INDEX idx_date_submitted
  ON pictures
  USING btree
  (date_submitted);

-- Index: idx_date_submitted_date

-- DROP INDEX idx_date_submitted_date;

CREATE INDEX idx_date_submitted_date
  ON pictures
  USING btree
  (date_trunc('day'::text, date_submitted));

-- Index: idx_description

-- DROP INDEX idx_description;

CREATE INDEX idx_description
  ON pictures
  USING gist
  (fti_description);

-- Index: idx_faves_count

-- DROP INDEX idx_faves_count;

CREATE INDEX idx_faves_count
  ON pictures
  USING btree
  (faves_count);

-- Index: idx_ft_all

-- DROP INDEX idx_ft_all;

CREATE INDEX idx_ft_all
  ON pictures
  USING gist
  (fti_all);

-- Index: idx_keywords

-- DROP INDEX idx_keywords;

CREATE INDEX idx_keywords
  ON pictures
  USING gist
  (fti_keywords);

-- Index: idx_picture_comments_count

-- DROP INDEX idx_picture_comments_count;

CREATE INDEX idx_picture_comments_count
  ON pictures
  USING btree
  (comments_count);

-- Index: idx_pictured_date_submitted_and_approved

-- DROP INDEX idx_pictured_date_submitted_and_approved;

CREATE INDEX idx_pictured_date_submitted_and_approved
  ON pictures
  USING btree
  (date_submitted, approved);

-- Index: idx_pictures_approved_btree

-- DROP INDEX idx_pictures_approved_btree;

CREATE INDEX idx_pictures_approved_btree
  ON pictures
  USING btree
  (approved);

-- Index: idx_pictures_date_edited

-- DROP INDEX idx_pictures_date_edited;

CREATE INDEX idx_pictures_date_edited
  ON pictures
  USING btree
  (date_edited);

-- Index: idx_pictures_faves_count_comments_count_score_views

-- DROP INDEX idx_pictures_faves_count_comments_count_score_views;

CREATE INDEX idx_pictures_faves_count_comments_count_score_views
  ON pictures
  USING btree
  (faves_count, comments_count, score, views);

-- Index: idx_pictures_image_type

-- DROP INDEX idx_pictures_image_type;

CREATE INDEX idx_pictures_image_type
  ON pictures
  USING btree
  (image_type);

-- Index: idx_pictures_is_approved

-- DROP INDEX idx_pictures_is_approved;

CREATE INDEX idx_pictures_is_approved
  ON pictures
  USING btree
  (approved)
  WHERE approved > 0;

-- Index: idx_pictures_media_id

-- DROP INDEX idx_pictures_media_id;

CREATE INDEX idx_pictures_media_id
  ON pictures
  USING btree
  (media_id);

-- Index: idx_pictures_section_id

-- DROP INDEX idx_pictures_section_id;

CREATE INDEX idx_pictures_section_id
  ON pictures
  USING btree
  (section_id);

-- Index: idx_pictures_user_id

-- DROP INDEX idx_pictures_user_id;

CREATE INDEX idx_pictures_user_id
  ON pictures
  USING btree
  (user_id);

-- Index: idx_rating

-- DROP INDEX idx_rating;

CREATE INDEX idx_rating
  ON pictures
  USING btree
  (rating_nudity, rating_violence, rating_sex, rating_racism, rating_spoilers, rating_yaoi, rating_yuri, rating_profanity);

-- Index: idx_score

-- DROP INDEX idx_score;

CREATE INDEX idx_score
  ON pictures
  USING btree
  (score);

-- Index: idx_title

-- DROP INDEX idx_title;

CREATE INDEX idx_title
  ON pictures
  USING gist
  (fti_title);

-- Index: idx_views

-- DROP INDEX idx_views;

CREATE INDEX idx_views
  ON pictures
  USING btree
  (views);



-- Trigger: _FAC_replication_logtrigger_33 on pictures

-- DROP TRIGGER "_FAC_replication_logtrigger_33" ON pictures;

CREATE TRIGGER "_FAC_replication_logtrigger_33"
  AFTER INSERT OR UPDATE OR DELETE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE "_FAC_replication".logtrigger('_FAC_replication', 33, 'vvvvvvvvkvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv');

-- Trigger: fti_update_all on pictures

-- DROP TRIGGER fti_update_all ON pictures;

CREATE TRIGGER fti_update_all
  BEFORE INSERT OR UPDATE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE tsearch2('fti_all', 'title', 'keywords', 'description');

-- Trigger: fti_update_description on pictures

-- DROP TRIGGER fti_update_description ON pictures;

CREATE TRIGGER fti_update_description
  BEFORE INSERT OR UPDATE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE tsearch2('fti_description', 'description');

-- Trigger: fti_update_keywords on pictures

-- DROP TRIGGER fti_update_keywords ON pictures;

CREATE TRIGGER fti_update_keywords
  BEFORE INSERT OR UPDATE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE tsearch2('fti_keywords', 'keywords');

-- Trigger: fti_update_title on pictures

-- DROP TRIGGER fti_update_title ON pictures;

CREATE TRIGGER fti_update_title
  BEFORE INSERT OR UPDATE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE tsearch2('fti_title', 'title');

-- Trigger: update_user_pictures_count on pictures

-- DROP TRIGGER update_user_pictures_count ON pictures;

CREATE TRIGGER update_user_pictures_count
  BEFORE INSERT OR DELETE
  ON pictures
  FOR EACH ROW
  EXECUTE PROCEDURE update_user_pictures_count();




-- Table: category

-- DROP TABLE category;

CREATE TABLE category
(
  cat_id serial NOT NULL,
  base_name varchar(50) NOT NULL,
  description text NOT NULL DEFAULT ''::text,
  admin_notes text NOT NULL DEFAULT ''::text,
  picture_count int4 NOT NULL DEFAULT 0,
  story_count int4 NOT NULL DEFAULT 0,
  fti_description tsvector NOT NULL,
  status category_status NOT NULL DEFAULT 0,
  num_refrences int4 NOT NULL DEFAULT 0,
  master_refrence int4,
  CONSTRAINT category_pkey PRIMARY KEY (cat_id)
) 
WITHOUT OIDS;
ALTER TABLE category OWNER TO fanart_central;


-- Index: idx_category_master_refrence

-- DROP INDEX idx_category_master_refrence;

CREATE INDEX idx_category_master_refrence
  ON category
  USING btree
  (master_refrence);

-- Index: idx_category_num_refrences

-- DROP INDEX idx_category_num_refrences;

CREATE INDEX idx_category_num_refrences
  ON category
  USING btree
  (num_refrences);

-- Index: idx_fti_description

-- DROP INDEX idx_fti_description;

CREATE INDEX idx_fti_description
  ON category
  USING gist
  (fti_description);



-- Trigger: _FAC_replication_logtrigger_5 on category

-- DROP TRIGGER "_FAC_replication_logtrigger_5" ON category;

CREATE TRIGGER "_FAC_replication_logtrigger_5"
  AFTER INSERT OR UPDATE OR DELETE
  ON category
  FOR EACH ROW
  EXECUTE PROCEDURE "_FAC_replication".logtrigger('_FAC_replication', 5, 'kvvvvvvvvv');

-- Trigger: fti_description_update on category

-- DROP TRIGGER fti_description_update ON category;

CREATE TRIGGER fti_description_update
  BEFORE INSERT OR UPDATE
  ON category
  FOR EACH ROW
  EXECUTE PROCEDURE tsearch2('fti_description', 'description');




-- Table: category_hier

-- DROP TABLE category_hier;

CREATE TABLE category_hier
(
  hier_id serial NOT NULL,
  name varchar(50),
  parent int4 NOT NULL,
  created_by int4,
  requested_by int4,
  date_created timestamp DEFAULT now(),
  "type" int2,
  child_count int4 NOT NULL DEFAULT 0,
  cat_id int4 NOT NULL,
  approved boolint NOT NULL DEFAULT 0,
  name_tree ltree NOT NULL,
  id_tree ltree NOT NULL,
  is_master boolint NOT NULL DEFAULT 1,
  approved_by int4,
  CONSTRAINT category_hierchy_pkey PRIMARY KEY (hier_id),
  CONSTRAINT category_hier_cat_id_fkey FOREIGN KEY (cat_id)
      REFERENCES category (cat_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
) 
WITHOUT OIDS;
ALTER TABLE category_hier OWNER TO fanart_central;


-- Index: idx_category_hier_approved

-- DROP INDEX idx_category_hier_approved;

CREATE INDEX idx_category_hier_approved
  ON category_hier
  USING btree
  (approved);

-- Index: idx_category_hier_cat_id

-- DROP INDEX idx_category_hier_cat_id;

CREATE INDEX idx_category_hier_cat_id
  ON category_hier
  USING btree
  (cat_id);

-- Index: idx_category_hier_is_master

-- DROP INDEX idx_category_hier_is_master;

CREATE INDEX idx_category_hier_is_master
  ON category_hier
  USING btree
  (is_master);

-- Index: idx_category_hier_parent

-- DROP INDEX idx_category_hier_parent;

CREATE INDEX idx_category_hier_parent
  ON category_hier
  USING btree
  (parent);



-- Trigger: _FAC_replication_logtrigger_6 on category_hier

-- DROP TRIGGER "_FAC_replication_logtrigger_6" ON category_hier;

CREATE TRIGGER "_FAC_replication_logtrigger_6"
  AFTER INSERT OR UPDATE OR DELETE
  ON category_hier
  FOR EACH ROW
  EXECUTE PROCEDURE "_FAC_replication".logtrigger('_FAC_replication', 6, 'kvvvvvvvvvvvvv');

-- Trigger: trigger_update_category_hier_children_count on category_hier

-- DROP TRIGGER trigger_update_category_hier_children_count ON category_hier;

CREATE TRIGGER trigger_update_category_hier_children_count
  AFTER INSERT OR DELETE
  ON category_hier
  FOR EACH ROW
  EXECUTE PROCEDURE update_category_children_count();

-- Trigger: update_category_ltrees on category_hier

-- DROP TRIGGER update_category_ltrees ON category_hier;

CREATE TRIGGER update_category_ltrees
  BEFORE INSERT OR UPDATE
  ON category_hier
  FOR EACH ROW
  EXECUTE PROCEDURE update_category_ltrees();