-- Sequence: rips_data_id_seq

-- DROP SEQUENCE rips_data_id_seq;

CREATE SEQUENCE rips_data_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE rips_data_id_seq
  OWNER TO postgres;

-- Table: rips_data

-- DROP TABLE rips_data;

CREATE TABLE rips_data
(
  appointment_code character varying(10) NOT NULL,
  rips smallint NOT NULL,
  id integer NOT NULL DEFAULT nextval('rips_data_id_seq'::regclass),
  date_create timestamp without time zone NOT NULL,
  id_user integer NOT NULL,
  CONSTRAINT rips_data_pkey PRIMARY KEY (id),
  CONSTRAINT fk_appointment_code_rips_data FOREIGN KEY (appointment_code)
      REFERENCES appointment (appointment_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_user_sonata_rips_data FOREIGN KEY (id_user)
      REFERENCES user_sonata (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE rips_data
  OWNER TO postgres;
---------------------------------------------------------------
DROP TABLE "public"."appointment_product_receipt";
DROP TABLE "public"."bill_receipt";
DROP TABLE "public"."bill_requirement";
DROP TABLE "public"."bill";
DROP TABLE "public"."consent_forms";
ALTER TABLE "public"."contract" DROP CONSTRAINT "contract_id_contract_type_fkey";
ALTER TABLE "public"."appointment" DROP CONSTRAINT "appointment_patient_consent";
ALTER TABLE "public"."appointment" DROP COLUMN "patient_consent";
ALTER TABLE "public"."appointment" DROP COLUMN "patient_datas_in_edition";
DROP TABLE "public"."headquarter_contract";
DROP TABLE "public"."product_contract";
DROP TABLE "public"."contract_rate";
DROP TABLE "public"."contract_type";
DROP TABLE "public"."contract";
DROP TABLE "public"."eps_patient";
DROP TABLE "public"."eps_requirement";
DROP TABLE "public"."headquarter_product";
DROP TABLE "public"."fields";
DROP TABLE "public"."menu";
DROP TABLE "public"."menu_description";
DROP TABLE "public"."module";
DROP TABLE "public"."parameter_config";
DROP TABLE "public"."patient_consent";
DROP TABLE "public"."product";
DROP TABLE "public"."product_type";
DROP TABLE "public"."provider";
ALTER TABLE "public"."room_appointment_study_receipt" DROP COLUMN "id_receipt";
DROP TABLE "public"."receipt";
DROP TABLE "public"."study_recommendation";
DROP TABLE "public"."recommendation_antecedent";
DROP TABLE "public"."recommendation";
DROP TABLE "public"."antecedent";
DROP TABLE "public"."result_template";
DROP TABLE "public"."status_patient_datas_edition";
---------------------------------------------------------------
ALTER TABLE appointment
  ADD COLUMN id_user_reading integer;
ALTER TABLE appointment
  ADD CONSTRAINT id_user_reading FOREIGN KEY (id_user_reading) REFERENCES user_sonata (id) ON UPDATE CASCADE ON DELETE RESTRICT;

---------------------------------------------------------------
-- Sequence: id_hisris_seq

-- DROP SEQUENCE id_hisris_seq;

CREATE SEQUENCE id_hisris_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE id_hisris_seq
  OWNER TO postgres;


/*
Date: 2015-08-06 17:32:47
*/

---------------------------------------------------------------

-- ----------------------------
-- Table structure for ti_hisris
-- ----------------------------
CREATE TABLE "public"."ti_hisris" (
"id" int4 DEFAULT nextval('id_hisris_seq'::regclass) NOT NULL,
"idagrupado" int4,
"fuente" varchar(2) COLLATE "default",
"nrodoc" varchar(50) COLLATE "default",
"nlinea" varchar(100) COLLATE "default",
"origen" varchar(1) COLLATE "default",
"codser" varchar(2) COLLATE "default",
"tipide" varchar(2) COLLATE "default",
"nroide" varchar(20) COLLATE "default",
"nombre" varchar(20) COLLATE "default",
"apell1" varchar(15) COLLATE "default",
"apell2" varchar(15) COLLATE "default" NOT NULL,
"telefo" varchar(25) COLLATE "default",
"celula" varchar(20) COLLATE "default",
"direcc" varchar(100) COLLATE "default",
"sexo" varchar(1) COLLATE "default",
"zresid" varchar(1) COLLATE "default",
"tipmed" varchar(1) COLLATE "default",
"medico" varchar(20) COLLATE "default",
"codact" varchar(6) COLLATE "default",
"nomact" varchar(512) COLLATE "default",
"obsord" varchar(255) COLLATE "default",
"fnacim" varchar(20) COLLATE "default",
"rhsang" varchar(4) COLLATE "default",
"correo" varchar(40) COLLATE "default",
"fechas" varchar(21) COLLATE "default" NOT NULL,
"esthis" varchar(2) COLLATE "default",
"codusr" varchar(20) COLLATE "default",
"nrosol" varchar(15) COLLATE "default",
"fechar" varchar(21) COLLATE "default" NOT NULL,
"codtec" varchar(20) COLLATE "default",
"codrad" varchar(20) COLLATE "default",
"estris" varchar(10) COLLATE "default",
"result" varchar(4000) COLLATE "default",
"nomrem" varchar(30) COLLATE "default",
"lectura" bool DEFAULT true,
"reporte" bool DEFAULT false,
"codigounico" varchar(100) COLLATE "default"
)
WITH (OIDS=FALSE)

;

---------------------------------------------------------------
-- ----------------------------
-- Alter Sequences Owned By
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table ti_hisris
-- ----------------------------
ALTER TABLE "public"."ti_hisris" ADD PRIMARY KEY ("id");

---------------------------------------------------------------
ALTER TABLE plates_registry
  ADD COLUMN amount_ma character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_compresion character varying(10);
ALTER TABLE plates_registry
 ADD COLUMN amount_mgyagd character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_ctdi character varying(10);
ALTER TABLE plates_registry
 ADD COLUMN  amount_dlp character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN weight character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_mci character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_qradiof character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_imgcine character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_imgfluoro character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_dapugy character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_cumulate_dose character varying(10);
ALTER TABLE plates_registry
  ADD COLUMN amount_time_fluo character varying(10);

---------------------------------------------------------------
ALTER TABLE "public"."rips_data"
ADD COLUMN "date_init" timestamp,
ADD COLUMN "date_end" timestamp;
---------------------------------------------------------------
ALTER TABLE "public"."appointment"
ADD COLUMN "id_user_receipt" int4;
---------------------------------------------------------------
ALTER TABLE "public"."appointment"
ADD CONSTRAINT "appointment_id_user_receipt" FOREIGN KEY ("id_user_receipt") REFERENCES "public"."user_sonata" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

---------------------------------------------------------------
/* 1- Ejecutar para agregar campo a la tabla study list */
alter table study_list add column consent bool;

/* 1- Ejecutar primero secuencia */
CREATE SEQUENCE consent_id_seq;

/* 2- Crear tabla consent (Consentimientos) */
CREATE TABLE "public".consent (
"id" int8 DEFAULT nextval('consent_id_seq'::regclass) NOT NULL,
"name" varchar(150) COLLATE "default" NOT NULL,
"description" text COLLATE "default",
"current" timestamp(6) DEFAULT (('now'::text)::timestamp without time zone)::timestamp(0) without time zone NOT NULL,
CONSTRAINT "consent_pkey" PRIMARY KEY ("id"),
CONSTRAINT "consent_name_unique" UNIQUE ("name")
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."consent" OWNER TO "postgres";

COMMENT ON TABLE "public"."consent" IS 'Descripción: En esta tabla se registran los diferentes consentimientos para luego en una tabla intermedia asociarcelos a los diferentes estudios';

/*  3- Crear tabla donde se relacionan los consentimientos con los estudios */
CREATE TABLE "public"."rel_study_list_consent" (
"id_consent" int8 NOT NULL,
"id_study_list" int8 NOT NULL,
CONSTRAINT "rel_study_list_consent_pkey" PRIMARY KEY ("id_consent", "id_study_list"),
CONSTRAINT "rel_study_list_consent_id_consent_fkey" FOREIGN KEY ("id_consent") REFERENCES "public"."consent" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT "rel_study_list_consent_id_study_list_fkey" FOREIGN KEY ("id_study_list") REFERENCES "public"."study_list" ("id_study_list") ON DELETE RESTRICT ON UPDATE CASCADE
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."rel_study_list_consent" OWNER TO "postgres";

COMMENT ON TABLE "public"."rel_study_list_consent" IS 'Descripción: Tabla intermedia entre consentimientos y los estudios ya que esta es una relación de muchos a muchos.';

DROP TABLE ti_hisris;

CREATE TABLE ti_hisris
(
  id integer NOT NULL DEFAULT nextval('id_hisris_seq'::regclass),
  idagrupado integer,
  codigounico character varying(100),
  fuente character varying(2),
  nrodoc character varying(50),
  nlinea character varying(100),
  origen character varying(1),
  codser character varying(2),
  histor character varying(50),
  ingres character varying(50),
  fechad date,
  tipide character varying(2),
  nroide character varying(20),
  nombre character varying(20),
  apell1 character varying(15),
  apell2 character varying(15),
  telefo character varying(25),
  celula character varying(20),
  direcc character varying(100),
  sexo character varying(1),
  zresid character varying(1),
  tipmed character varying(1),
  medico character varying(20),
  codact character varying(6),
  nomact character varying(512),
  obsord character varying(255),
  fnacim character varying(20),
  rhsang character varying(4),
  correo character varying(40),
  fechas character varying(21),
  esthis character varying(2),
  codusr character varying(20),
  nrosol character varying(15),
  fechar character varying(21),
  codtec character varying(20),
  codrad character varying(20),
  estris character varying(20),
  result character varying(4000),
  docrem character varying(20),
  nomrem character varying(30),
  lectura boolean DEFAULT true,
  reporte boolean DEFAULT false,
  fechaingreso character varying(50),
  estado character varying(20),
  CONSTRAINT ti_hisris_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ti_hisris
  OWNER TO postgres;
  COMMENT ON TABLE ti_hisris
  IS 'Tabla de paso que se crea para trabajar con las integraciones de los HIS.';

---------------------------------------------------------------
CREATE TABLE "public"."patient_consent" (
"patient_consent" varchar(10) COLLATE "default" NOT NULL,
"physical_consent_url" varchar(200) COLLATE "default",
"current" timestamp(6) DEFAULT (('now'::text)::timestamp without time zone)::timestamp(0) without time zone NOT NULL,
CONSTRAINT "idpatient_consent" PRIMARY KEY ("patient_consent")
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."patient_consent" OWNER TO "postgres";

COMMENT ON TABLE "public"."patient_consent" IS 'Descripción: Tabla que aplica el documento de consentimiento escaneado el cual no se esta aplicando en este momento pero más adelante si esta pensado aplicarle esta opción.';

---------------------------------------------------------------
--Se crea nueva columna en la tabla appointment para relacionar los consentimientos informados con el estudio del paciente.
ALTER TABLE "public"."appointment"
ADD COLUMN "consent_patient" varchar(10);

ALTER TABLE "public"."appointment"
ADD CONSTRAINT "appointment_consent" FOREIGN KEY ("consent_patient") REFERENCES "public"."patient_consent" ("patient_consent") ON DELETE RESTRICT ON UPDATE CASCADE;

---------------------------------------------------------------
ALTER TABLE "public"."user_sm_languages"
DROP CONSTRAINT "user_sm_languages_pkey",
DROP COLUMN "id";

ALTER TABLE "public"."user_sm_microphones"
DROP CONSTRAINT "user_sm_microphones_pkey",
DROP COLUMN "id";

ALTER TABLE "public"."user_sm_topics"
DROP CONSTRAINT "user_sm_topics_pkey",
DROP COLUMN "id_user_sm";

---------------------------------------------------------------
ALTER TABLE "public"."headquarter" ADD COLUMN "integration_his" boolean DEFAULT '0';

---------------------------------------------------------------
--1-Primero crear la secuencia.
CREATE SEQUENCE speciality_id_speciality_seq;

--2-Crear la tabla con la secuencia
CREATE TABLE "public"."speciality" (
"id_speciality" int8 DEFAULT nextval('speciality_id_speciality_seq'::regclass) NOT NULL,
"name_speciality" varchar(25) COLLATE "default",
"description" text COLLATE "default",
CONSTRAINT "speciality_pkey" PRIMARY KEY ("id_speciality")
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."speciality" OWNER TO "postgres";

COMMENT ON TABLE "public"."speciality" IS 'Esta tabla contiene las diferentes especiales de los estudios.';

--3-Adicionar colummna en study_list
ALTER TABLE study_list ADD COLUMN id_speciality int8;

--4-Relacionar study_list con la especialidad
ALTER TABLE "public"."study_list"
ADD CONSTRAINT "study_list_id_speciality" FOREIGN KEY ("id_speciality") REFERENCES "public"."speciality" ("id_speciality") ON DELETE CASCADE ON UPDATE CASCADE;



