/* SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; */
/* SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; */
/* SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; */

-- -----------------------------------------------------
-- Table `BigEyeStream`.`religion_group`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE religion_group';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE religion_group (
  id_religion_group NUMBER(10) NOT NULL,
  name VARCHAR2(45) NULL,
  PRIMARY KEY (id_religion_group))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE religion_group_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER religion_group_seq_tr
 BEFORE INSERT ON religion_group FOR EACH ROW
 WHEN (NEW.id_religion_group IS NULL)
BEGIN
 SELECT religion_group_seq.NEXTVAL INTO :NEW.id_religion_group FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `BigEyeStream`.`religion`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE religion';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE religion (
  id_religion NUMBER(10) NOT NULL,
  name VARCHAR2(45) NULL,
  religion_group NUMBER(10) NULL,
  PRIMARY KEY (id_religion)
 )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE religion_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER religion_seq_tr
 BEFORE INSERT ON religion FOR EACH ROW
 WHEN (NEW.id_religion IS NULL)
BEGIN
 SELECT religion_seq.NEXTVAL INTO :NEW.id_religion FROM DUAL;
END;
/

CREATE INDEX fk_religion_group_idx ON religion (religion_group ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`country`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE country';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE country (
  id_country NUMBER(10) NOT NULL,
  name VARCHAR2(45) NULL,
  iso VARCHAR2(45) NULL,
  phone_prefix VARCHAR2(45) NULL,
  PRIMARY KEY (id_country))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE country_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER country_seq_tr
 BEFORE INSERT ON country FOR EACH ROW
 WHEN (NEW.id_country IS NULL)
BEGIN
 SELECT country_seq.NEXTVAL INTO :NEW.id_country FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `BigEyeStream`.`ter_group`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ter_group';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE ter_group (
  id_group NUMBER(10) NOT NULL,
  name VARCHAR2(45) NOT NULL,
  abbr VARCHAR2(15) NULL,
  country NUMBER(10) NULL,
  religion NUMBER(10) NULL,
  leader NUMBER(10) NULL,
  dangerous VARCHAR2(45) NULL,
  status VARCHAR2(45) NULL,
  PRIMARY KEY (id_group)
 )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE group_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER group_seq_tr
 BEFORE INSERT ON ter_group FOR EACH ROW
 WHEN (NEW.id_group IS NULL)
BEGIN
 SELECT group_seq.NEXTVAL INTO :NEW.id_group FROM DUAL;
END;
/

CREATE INDEX fk_group_religion1_idx ON ter_group (religion ASC);
CREATE INDEX fk_country_idx ON ter_group (country ASC);
CREATE INDEX fk_peole_idx ON ter_group (leader ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`people`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE people';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE people (
  id_people NUMBER(10) NOT NULL,
  high VARCHAR2(45) NULL,
  eyes_color VARCHAR2(45) NULL,
  id_group NUMBER(10) NULL,
  PRIMARY KEY (id_people)
 )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE people_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER people_seq_tr
 BEFORE INSERT ON people FOR EACH ROW
 WHEN (NEW.id_people IS NULL)
BEGIN
 SELECT people_seq.NEXTVAL INTO :NEW.id_people FROM DUAL;
END;
/

CREATE INDEX fk_group_idx ON people (id_group ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`weapon`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE weapon';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE weapon (
  id_weapon NUMBER(10) NOT NULL,
  name VARCHAR2(45) NOT NULL,
  power NUMBER(10) NULL ,
  range NUMBER(10) NULL ,
  accuracy NUMBER(10) NULL ,
  PRIMARY KEY (id_weapon))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE weapon_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER weapon_seq_tr
 BEFORE INSERT ON weapon FOR EACH ROW
 WHEN (NEW.id_weapon IS NULL)
BEGIN
 SELECT weapon_seq.NEXTVAL INTO :NEW.id_weapon FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `BigEyeStream`.`target`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE target';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE target (
  id_target NUMBER(10) NOT NULL,
  id_country NUMBER(10) NULL,
  name VARCHAR2(45) NULL,
  gps_x BINARY_DOUBLE NULL,
  gps_y BINARY_DOUBLE NULL,
  existing NUMBER(3) NULL,
  PRIMARY KEY (id_target)
 )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE target_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER target_seq_tr
 BEFORE INSERT ON target FOR EACH ROW
 WHEN (NEW.id_target IS NULL)
BEGIN
 SELECT target_seq.NEXTVAL INTO :NEW.id_target FROM DUAL;
END;
/

CREATE INDEX fk_country_idx ON target (id_country ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`attack`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE attack';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE attack (
  id_people NUMBER(10) NOT NULL,
  id_group NUMBER(10) NOT NULL,
  id_target NUMBER(10) NOT NULL,
  id_weapon NUMBER(10) NOT NULL,
  status VARCHAR2(45) NOT NULL,
  attack_date TIMESTAMP(0) NOT NULL,
  id_attack NUMBER(10) NOT NULL
 ,
  PRIMARY KEY (id_attack))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE attack_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER attack_seq_tr
 BEFORE INSERT ON attack FOR EACH ROW
 WHEN (NEW.id_attack IS NULL)
BEGIN
 SELECT attack_seq.NEXTVAL INTO :NEW.id_attack FROM DUAL;
END;
/

CREATE INDEX people_idx ON attack (id_people ASC);
CREATE INDEX group_idx ON attack (id_group ASC);
CREATE INDEX target_idx ON attack (id_target ASC);
CREATE INDEX weapon_idx ON attack (id_weapon ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`identity`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE identity';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE identity (
  id_identity NUMBER(10) NOT NULL,
  id_people NUMBER(10) NULL,
  name VARCHAR2(45) NULL,
  surname VARCHAR2(45) NULL,
  country NUMBER(10) NULL,
  description VARCHAR2(45) NULL,
  main NUMBER(3) NULL
 ,
  PRIMARY KEY (id_identity))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE identity_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER identity_seq_tr
 BEFORE INSERT ON identity FOR EACH ROW
 WHEN (NEW.id_identity IS NULL)
BEGIN
 SELECT identity_seq.NEXTVAL INTO :NEW.id_identity FROM DUAL;
END;
/

CREATE INDEX fk_people_idx ON identity (id_people ASC);
CREATE INDEX fk_country_idx ON identity (country ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`email`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE email';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE email (
  id_identity NUMBER(10) NULL,
  email VARCHAR2(45) NULL
 )
;

CREATE INDEX fk_identity_idx ON email (id_identity ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`phone`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE phone';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE phone (
  id_identity NUMBER(10) NULL,
  phone_number VARCHAR2(45) NULL
 )
;

CREATE INDEX fk_identity_idx ON phone (id_identity ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`group_has_weapon`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE group_has_weapon';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE group_has_weapon (
  group_id_group NUMBER(10) NOT NULL,
  weapon_id_weapon NUMBER(10) NOT NULL,
  expected_quantity NUMBER(10) NULL,
  PRIMARY KEY (group_id_group, weapon_id_weapon)
 )
;

CREATE INDEX fk_grp_has_weapon_idx ON group_has_weapon (weapon_id_weapon ASC);
CREATE INDEX fk_grp_has_weapon_grp_idx ON group_has_weapon (group_id_group ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`people_has_group`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE people_has_group';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE people_has_group (
  people_id_people NUMBER(10) NOT NULL,
  group_id_group NUMBER(10) NOT NULL,
  PRIMARY KEY (people_id_people, group_id_group)
 )
;

CREATE INDEX fk_people_has_grp_idx ON people_has_group (group_id_group ASC);
CREATE INDEX fk_people_has_grp_idx ON people_has_group (people_id_people ASC);


-- -----------------------------------------------------
-- Table `BigEyeStream`.`enemy`
-- -----------------------------------------------------
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE enemy';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/ 

CREATE TABLE enemy (
  id_group NUMBER(10) NOT NULL,
  id_country NUMBER(10) NOT NULL,
  PRIMARY KEY (id_group, id_country)
 )
 ;

CREATE INDEX fk_grp_has_country_idx ON enemy (id_country ASC);
CREATE INDEX fk_grp_has_country_idx ON enemy (id_group ASC);