-- create sequences--------------------------------
CREATE SEQUENCE "ARCHA"."ID_CHEMICAL" INCREMENT BY 1 START WITH 1 
CREATE SEQUENCE "ARCHA"."ID_PROP" INCREMENT BY 1 START WITH 1 
CREATE SEQUENCE "ARCHA"."ID_OUT" INCREMENT BY 1 START WITH 1 
CREATE SEQUENCE "ARCHA"."ID_ATTRIBUTS" INCREMENT BY 1 START WITH 1 
CREATE SEQUENCE "ARCHA"."ID_KPT" INCREMENT BY 1 START WITH 1 


-------------------------CREATE TABLES--------------------------

--Table CHEMICAL
CREATE TABLE "CHEMICAL" ("ID_CHEMICAL" NUMBER NOT NULL, 
	"Z" FLOAT(126) DEFAULT NULL, 
	"Y" FLOAT(126) DEFAULT NULL, 
	"FE_H" FLOAT(126) DEFAULT NULL, 
	"M_H" FLOAT(126) DEFAULT NULL,
	CONSTRAINT "PK_id_chemical" PRIMARY KEY("ID_CHEMICAL") , 
	CONSTRAINT "U_CHEMICAL" UNIQUE("Z", "Y", "FE_H", "M_H") USING INDEX;

-- Table PROPRIETIES
CREATE TABLE "PROPRIETIES" ("ID_PROP" NUMBER NOT NULL, 
	"ID_CHEMICAL" NUMBER NOT NULL, 
	"TYPE" CHAR(40 byte) DEFAULT NULL, 
	"MASS_LOSS" FLOAT(126) DEFAULT NULL, 
	"PHOT_SYSTEM" VARCHAR2(150 byte) DEFAULT NULL, 
	"HED_TYPE" VARCHAR2(150 byte) DEFAULT NULL, 
CONSTRAINT "FK_id_chemical" FOREIGN KEY("ID_CHEMICAL") REFERENCES "CHEMICAL"("ID_CHEMICAL"), 
CONSTRAINT "PK_id_prop" PRIMARY KEY("ID_PROP"),
CONSTRAINT "U_PROP" UNIQUE("ID_CHEMICAL", "TYPE", "MASS_LOSS","PHOT_SYSTEM", "HED_TYPE") USING INDEX  

-- Table PROGRAM_PARAM
CREATE TABLE "PROGRAM_PARAM" ("ID_PROG" NUMBER NOT NULL, 
	"CODE_NAME" VARCHAR2(20 byte) DEFAULT NULL, 
	"VERSION" VARCHAR2(10 byte) DEFAULT NULL, 
	"EOS" VARCHAR2(50 byte) DEFAULT NULL, 
	"RAD_OPACITY" VARCHAR2(150 byte) DEFAULT NULL, 
	"COND_OPACITY" VARCHAR2(150 byte) DEFAULT NULL, 
	"NUCL_RATES" VARCHAR2(150 byte) DEFAULT NULL, 
	"NEUTRINO_LOSSES" VARCHAR2(150 byte) DEFAULT NULL, 
	"BOUNDARY_COND" VARCHAR2(150byte) DEFAULT NULL, 
	"NOTE" VARCHAR2(600 byte) DEFAULT NULL, 
CONSTRAINT "PK_id_prog" PRIMARY KEY("ID_PROG") USING INDEX 

-- Table SCENARIO
CREATE TABLE "SCENARIO" ("ID_SCENARIO" NUMBER NOT NULL, 
"SCENARIO_TYPE" VARCHAR2(20 byte) DEFAULT NULL, 
CONSTRAINT "PK_id_scenario" PRIMARY KEY("ID_SCENARIO") USING INDEX,
CONSTRAINT "U_SCENARIO_TYPE" UNIQUE("SCENARIO_TYPE") USING INDEX 

-- Table OUT_FILE
CREATE TABLE "OUT_FILE" ("ID_OUT" NUMBER NOT NULL, 
"ID_PROP" NUMBER NOT NULL, 
"ID_SCENARIO" NUMBER NOT NULL, 
"ID_PROG" NUMBER NOT NULL, 
"FILE_TYPE" VARCHAR2(10 byte) DEFAULT NULL, "PATH" VARCHAR2(250 byte) DEFAULT NULL, 
"FILENAME" VARCHAR2(100 byte) DEFAULT NULL, 
"MASS" FLOAT(126)DEFAULT NULL, 
"AGE" FLOAT(126) DEFAULT NULL, 
"ID_KPT" NUMBER,
CONSTRAINT "FK_ID_KPT" FOREIGN KEY("ID_KPT") REFERENCES "PHYSIC_FILE"("ID_KPT"), 
CONSTRAINT "FK_id_prog" FOREIGN KEY("ID_PROG") REFERENCES "PROGRAM_PARAM"("ID_PROG"), 
CONSTRAINT "FK_id_PROP" FOREIGN KEY("ID_PROP") REFERENCES "PROPRIETIES"("ID_PROP"), 
CONSTRAINT "FK_id_scenario" FOREIGN KEY("ID_SCENARIO") REFERENCES "SCENARIO"("ID_SCENARIO"), 
CONSTRAINT "PK_id_out" PRIMARY KEY("ID_OUT"),
CONSTRAINT "U_FILENAME" UNIQUE("FILENAME") 

--------------------------------------------------------------------------------------
-- Secondary tables:

-- Table PHYSIC_FILE
CREATE TABLE "PHYSIC_FILE" ("ID_KPT" NUMBER NOT NULL, 
"PHYSIC_PATH" VARCHAR2(250 byte) DEFAULT NULL, 
"PHYSIC_FILE" VARCHAR2(100 byte) DEFAULT NULL, 
CONSTRAINT "PK_id_kpt" PRIMARY KEY("ID_KPT")


-- Table KEY_POINTS
CREATE TABLE "KEY_POINTS" ("ID_KPT" NUMBER NOT NULL, 
"N_ROW" NUMBER NOT NULL, 
"LOG_T" FLOAT(126) DEFAULT NULL, 
"H_HE" FLOAT(126) DEFAULT NULL, 
"LOG_L" FLOAT(126) DEFAULT NULL, "LOG_T_E" FLOAT(126) DEFAULT NULL, 
"LOG_T_C" FLOAT(126)DEFAULT NULL, 
"LOG_R_C" FLOAT(126) DEFAULT NULL, 
"M_CC" FLOAT(126) DEFAULT NULL, 
"M_C_HE" FLOAT(126) DEFAULT NULL, 
"M_C_CO" FLOAT(126) DEFAULT NULL, 
"M_CE" FLOAT(126) DEFAULT NULL, 
"L_PP_L_S" FLOAT(126) DEFAULT NULL, 
"L_CNO_L_S" FLOAT(126) DEFAULT NULL, 
"L_3A_L_S" FLOAT(126) DEFAULT NULL, 
"L_GR_L_S" FLOAT(126) DEFAULT NULL, 
"HE_SUP" FLOAT(126) DEFAULT NULL, 
"M_TOT" FLOAT(126) DEFAULT NULL, 
"LOG_T_MAX" FLOAT(126) DEFAULT NULL, 
CONSTRAINT "FK_ID_KPTS" FOREIGN KEY("ID_KPT") REFERENCES "PHYSIC_FILE"("ID_KPT")) 


-- Table UNITS
CREATE TABLE "UNITS" ("TABLE_NAME" VARCHAR2(50 byte) DEFAULT NULL NOT NULL, 
"COLUMN_NAME" VARCHAR2(50 byte) DEFAULT NULL NOT NULL, 
"UNIT" VARCHAR2(100 byte) DEFAULT NULL,
CONSTRAINT "PK_id_units" PRIMARY KEY("TABLE_NAME", "COLUMN_NAME") USING INDEX