--DROP TABLE metadata_status CASCADE CONSTRAINTS PURGE;
--DROP TABLE user_relations CASCADE CONSTRAINTS PURGE;

--DROP TABLE products CASCADE CONSTRAINTS PURGE;
--DROP TABLE products_to_categories CASCADE CONSTRAINTS PURGE;
--DROP TABLE product_categories CASCADE CONSTRAINTS PURGE;

--DROP TABLE product_status CASCADE CONSTRAINTS PURGE;
--DROP TABLE article_status CASCADE CONSTRAINTS PURGE;
--DROP TABLE metadata_log CASCADE CONSTRAINTS PURGE;

CREATE TABLE METADATA_STATUS(
  RELEASED CHAR,
  PROGRESS VARCHAR(100),
  EDITOR VARCHAR(200),
  SUBEDITOR VARCHAR(200),
  ID NUMBER(38) DEFAULT -1 NOT NULL,
  TYP VARCHAR(32) DEFAULT 'data'
);

CREATE TABLE  USER_RELATIONS (
  EDITOR VARCHAR(200) NOT NULL,
  SUBEDITOR VARCHAR(200) NOT NULL
);

CREATE TABLE PRODUCTS (
 ID NUMBER(38),
 FID VARCHAR(250),
 NAME VARCHAR(250),
 TYPE VARCHAR(15),
 MAP VARCHAR(255),
 MINSCALE NUMBER(38),
 MAXSCALE NUMBER(38),
 MINAREA NUMBER(38),
 DATE_ADDED DATE,
 LAST_MODIFIED DATE,
 STATUS NUMBER(38)
);

CREATE TABLE PRODUCTS_TO_CATEGORIES(
  PRODUCTS_ID NUMBER(38),
  PRODUCT_POSITION NUMBER(38),
  CATEGORIES_ID NUMBER(38)
);

CREATE TABLE PRODUCT_CATEGORIES (
  ID NUMBER(38),
  NAME VARCHAR(80),
  PARENT NUMBER(38)
);

CREATE TABLE PRODUCT_STATUS (
  ID NUMBER(38),
  TITLE VARCHAR(250),
  TYP NUMBER(38),
  PROGRESS NUMBER(38),
  EDITOR VARCHAR(32),
  ARTICLESIZE NUMBER(38),
  ARTICLEWORKINGSIZE NUMBER(38),
  ARTICLETOCHECKSIZE NUMBER(38),
  ARTICLEBEFOREQUALITYSIZE NUMBER(38),
  ARTICLEAFTERQUALITYSIZE NUMBER(38),
  ARTICLEVALIDSIZE NUMBER(38)
);

CREATE TABLE ARTICLE_STATUS (
  ID NUMBER(38),
  PRODUCTID NUMBER(38),
  TYP NUMBER(38),
  TITLE VARCHAR(250),
  SUBEDITOR VARCHAR(32),
  PROGRESS NUMBER(38)
);

CREATE TABLE METADATA_LOG (
  ID NUMBER(38),
  FILEIDENTIFIER VARCHAR(250),
  LASTMODIFIED DATE,
  USERNAME VARCHAR(32),
  OPERATION NUMBER(38)
);

ALTER TABLE MD_METADATA ADD TESTDATA VARCHAR(255);

CREATE INDEX IDX_METADATA_STATUS ON METADATA_STATUS (RELEASED, PROGRESS, EDITOR, SUBEDITOR, TYP );
CREATE INDEX IDX_USER_RELATIONS ON USER_RELATIONS (EDITOR, SUBEDITOR);
CREATE INDEX IDX_PRODUCTS ON PRODUCTS (FID, NAME, TYPE, MAP, MINSCALE, MAXSCALE, MINAREA, DATE_ADDED, LAST_MODIFIED, STATUS);
CREATE INDEX IDX_PRODUCTS_TO_CATEGORIES ON PRODUCTS_TO_CATEGORIES (PRODUCTS_ID, PRODUCT_POSITION, CATEGORIES_ID);
CREATE INDEX IDX_PRODUCT_CATEGORIES ON PRODUCT_CATEGORIES (NAME, PARENT);
CREATE INDEX IDX_PRODUCT_STATUS ON PRODUCT_STATUS (TITLE, TYP, PROGRESS, EDITOR, ARTICLESIZE, ARTICLEWORKINGSIZE, ARTICLETOCHECKSIZE, ARTICLEBEFOREQUALITYSIZE, ARTICLEAFTERQUALITYSIZE, ARTICLEVALIDSIZE);
CREATE INDEX IDX_ARTICLE_STATUS ON ARTICLE_STATUS (PRODUCTID, TYP, TITLE, SUBEDITOR, PROGRESS);
CREATE INDEX IDX_METADATA_LOG ON METADATA_LOG (FILEIDENTIFIER, LASTMODIFIED, USERNAME, OPERATION);

COMMIT;