/* DROP FEATURE TABLES (4) */

DROP TABLE PLACE CASCADE CONSTRAINTS;

DROP INDEX COUNTRY1_SPIDX;
DROP TABLE COUNTRY CASCADE CONSTRAINTS;
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='COUNTRY';

DROP TABLE BOOK CASCADE CONSTRAINTS;

DROP TABLE PHILOSOPHER CASCADE CONSTRAINTS;


/* DROP JOIN TABLES (2) */

DROP INDEX IS_FRIEND0_PHILOSOPHER1_ID_IDX;
DROP INDEX IS_FRIEND1_PHILOSOPHER2_ID_IDX;
DROP TABLE IS_FRIEND_OF CASCADE CONSTRAINTS;

DROP INDEX IS_AUTHOR_2_PHILOSOPHER_ID_IDX;
DROP INDEX IS_AUTHOR_OF_BOOK_ID_IDX;
DROP TABLE IS_AUTHOR_OF CASCADE CONSTRAINTS;


/* DROP PROPERTY TABLES (1) */

DROP TABLE SUBJECT CASCADE CONSTRAINTS;


/* DROP SEQUENCES (1) */

DROP SEQUENCE FID_seq;

/* CREATE SEQUENCES (1) */

CREATE SEQUENCE FID_seq;


/* CREATE FEATURE TABLES (4) */

CREATE TABLE PLACE (
    ID INTEGER NOT NULL,
    NAME VARCHAR2(2000) NOT NULL,
    COUNTRY_ID INTEGER,
    PRIMARY KEY (ID)
);

CREATE TABLE COUNTRY (
    ID INTEGER NOT NULL,
    NAME VARCHAR2(2000) NOT NULL,
    GEOM MDSYS.SDO_GEOMETRY NOT NULL,
    PRIMARY KEY (ID)
);
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('COUNTRY', 'GEOM', SDO_DIM_ARRAY ( SDO_DIM_ELEMENT ( 'LONGITUDE', -180, 180, 0.00005), SDO_DIM_ELEMENT ('LATITUDE',-90,90,0.00005)),8192);
CREATE INDEX COUNTRY1_SPIDX ON COUNTRY(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE TABLE BOOK (
    ID INTEGER NOT NULL,
    TITLE VARCHAR2(2000) NOT NULL,
    PUB_DATE DATE,
    PRIMARY KEY (ID)
);

CREATE TABLE PHILOSOPHER (
    ID INTEGER NOT NULL,
    NAME VARCHAR2(2000) NOT NULL,
    SEX CHAR NOT NULL,
    DATE_OF_BIRTH DATE NOT NULL,
    PLACE_OF_BIRTH INTEGER NOT NULL,
    DATE_OF_DEATH DATE NOT NULL,
    PLACE_OF_DEATH INTEGER NOT NULL,
    PRIMARY KEY (ID)
);


/* CREATE JOIN TABLES (2) */

CREATE TABLE IS_FRIEND_OF (
    PHILOSOPHER1_ID INTEGER NOT NULL,
    PHILOSOPHER2_ID INTEGER NOT NULL,
    PRIMARY KEY (PHILOSOPHER1_ID,PHILOSOPHER2_ID)
);
CREATE INDEX IS_FRIEND0_PHILOSOPHER1_ID_IDX ON IS_FRIEND_OF(PHILOSOPHER1_ID);
CREATE INDEX IS_FRIEND1_PHILOSOPHER2_ID_IDX ON IS_FRIEND_OF(PHILOSOPHER2_ID);

CREATE TABLE IS_AUTHOR_OF (
    PHILOSOPHER_ID INTEGER NOT NULL,
    BOOK_ID INTEGER NOT NULL,
    PRIMARY KEY (PHILOSOPHER_ID,BOOK_ID)
);
CREATE INDEX IS_AUTHOR_2_PHILOSOPHER_ID_IDX ON IS_AUTHOR_OF(PHILOSOPHER_ID);
CREATE INDEX IS_AUTHOR_OF_BOOK_ID_IDX ON IS_AUTHOR_OF(BOOK_ID);


/* CREATE PROPERTY TABLES (1) */

CREATE TABLE SUBJECT (
    PHILOSOPHER_ID INTEGER NOT NULL,
    NAME VARCHAR2(2000) NOT NULL
);
