BEGIN;
CREATE TABLE "foro_catalogo" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(150) NOT NULL
)
;
CREATE TABLE "foro_escolaridad" (
    "catalogo_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "foro_catalogo" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "CAT_TIPOACTORES" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE" varchar(150) NOT NULL,
    "CLAVE" varchar(3) NOT NULL,
    "DESCRIPCION" varchar(200) NOT NULL
)
;
CREATE TABLE "CAT_TIPOINTEGRANTE" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE" varchar(150) NOT NULL,
    "CLAVE" varchar(3) NOT NULL,
    "DESCRIPCION" varchar(200) NOT NULL
)
;
CREATE TABLE "CAT_DEPENDENCIAS" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE" varchar(150) NOT NULL,
    "CLAVE" varchar(3) NOT NULL,
    "DESCRIPCION" varchar(200) NOT NULL
)
;
CREATE TABLE "MESA_TEMATICA_responsable" (
    "id" serial NOT NULL PRIMARY KEY,
    "mesatematica_id" integer NOT NULL,
    "dependencias_id" integer NOT NULL REFERENCES "CAT_DEPENDENCIAS" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("mesatematica_id", "dependencias_id")
)
;
CREATE TABLE "MESA_TEMATICA" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE_MESA" varchar(150) NOT NULL,
    "FECHA_TRABAJO" timestamp with time zone,
    "SEDE" text NOT NULL,
    "ID_MODERADOR" varchar(200) NOT NULL
)
;
ALTER TABLE "MESA_TEMATICA_responsable" ADD CONSTRAINT "mesatematica_id_refs_id_915de080" FOREIGN KEY ("mesatematica_id") REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "FUENTE" (
    "id" serial NOT NULL PRIMARY KEY,
    "DESCRIPCION" varchar(200) NOT NULL
)
;
CREATE TABLE "foro_instituciones" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL
)
;
CREATE TABLE "foro_cargo" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL
)
;
CREATE TABLE "INSCRIPCION_FORO" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE" varchar(100) NOT NULL,
    "APELLIDOS" varchar(60) NOT NULL,
    "CORREO_ELECTRONICO" varchar(40) NOT NULL,
    "TELEFONO" varchar(50) NOT NULL,
    "institucion_id" integer REFERENCES "foro_instituciones" ("id") DEFERRABLE INITIALLY DEFERRED,
    "cargo_id" integer REFERENCES "foro_cargo" ("id") DEFERRABLE INITIALLY DEFERRED,
    "INTRODUCCION" text NOT NULL,
    "SUBTEMA" varchar(100) NOT NULL,
    "TITULO" varchar(100) NOT NULL,
    "mesa_id" integer,
    "SUBTITULO" varchar(100) NOT NULL,
    "FECHA" date,
    "DOMICILIO" text NOT NULL,
    "COLONIA" varchar(200) NOT NULL,
    "ID_FUENTE" integer NOT NULL REFERENCES "FUENTE" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "PONENCIA" (
    "id" serial NOT NULL PRIMARY KEY,
    "FECHA_PONENCIA" date,
    "ID_INSCRIPCION_FORO" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "PROBLEMATICA" (
    "id" serial NOT NULL PRIMARY KEY,
    "inscripcion_foro_id" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "DESCRIPCION" varchar(500) NOT NULL
)
;
CREATE TABLE "ACTOR" (
    "id" serial NOT NULL PRIMARY KEY,
    "ID_PROBLEMATICA" integer NOT NULL REFERENCES "PROBLEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED,
    "NOMBRE" varchar(150) NOT NULL,
    "ID_TIPO_ACTOR" integer NOT NULL REFERENCES "CAT_TIPOACTORES" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "PLAZOS" (
    "id" serial NOT NULL PRIMARY KEY,
    "NOMBRE" varchar(150) NOT NULL,
    "DESCRIPCION" text NOT NULL
)
;
CREATE TABLE "BUZON_CIUDADANO" (
    "id" serial NOT NULL PRIMARY KEY,
    "EDAD" integer,
    "SEXO" integer,
    "escolaridad_id" integer NOT NULL REFERENCES "foro_escolaridad" ("catalogo_ptr_id") DEFERRABLE INITIALLY DEFERRED,
    "PROPUESTA" text NOT NULL,
    "FECHA_REGISTRO" date,
    "ID_MUNICIPIO" integer NOT NULL,
    "ID_PLAZO" integer REFERENCES "PLAZOS" ("id") DEFERRABLE INITIALLY DEFERRED,
    "usuario_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "BUZON_PROPOSITO" (
    "id" serial NOT NULL PRIMARY KEY,
    "DESCRIPCION" varchar(150) NOT NULL
)
;
CREATE TABLE "BUZON_SOLUCION" (
    "id" serial NOT NULL PRIMARY KEY,
    "DESCRIPCION" varchar(150) NOT NULL
)
;
CREATE TABLE "BUZON_SUGERENCIA_solucion" (
    "id" serial NOT NULL PRIMARY KEY,
    "buzonsugerencia_id" integer NOT NULL,
    "buzonsolucion_id" integer NOT NULL REFERENCES "BUZON_SOLUCION" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("buzonsugerencia_id", "buzonsolucion_id")
)
;
CREATE TABLE "BUZON_SUGERENCIA" (
    "id" serial NOT NULL PRIMARY KEY,
    "ID_BUZON_CIUDADANO" integer NOT NULL REFERENCES "BUZON_CIUDADANO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "ID_PROPOSITO" integer NOT NULL REFERENCES "BUZON_PROPOSITO" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
ALTER TABLE "BUZON_SUGERENCIA_solucion" ADD CONSTRAINT "buzonsugerencia_id_refs_id_7e9f7a8e" FOREIGN KEY ("buzonsugerencia_id") REFERENCES "BUZON_SUGERENCIA" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "EFECTO" (
    "id" serial NOT NULL PRIMARY KEY,
    "id_inscripcion_foro_id" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "DESCRIPCION" varchar(500) NOT NULL
)
;
CREATE TABLE "CAUSA" (
    "id" serial NOT NULL PRIMARY KEY,
    "id_inscripcion_foro_id" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "DESCRIPCION" varchar(500) NOT NULL
)
;
CREATE TABLE "CRITERIO" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL,
    "DESCRIPCION" text NOT NULL
)
;
CREATE TABLE "EVALUACION" (
    "id" serial NOT NULL PRIMARY KEY,
    "ID_MESA_TEMATICA" integer NOT NULL REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED,
    "ID_PONENCIA" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "ID_VALORACION" integer NOT NULL,
    "ID_CRITERIO" integer NOT NULL REFERENCES "CRITERIO" ("id") DEFERRABLE INITIALLY DEFERRED,
    "FECHA_EVALUACION" date
)
;
CREATE TABLE "MESA_INTEGRANTES" (
    "id" serial NOT NULL PRIMARY KEY,
    "ID_MESA_TEMATICA" integer NOT NULL REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED,
    "NOMBRE" varchar(60) NOT NULL,
    "DIRECCION" text NOT NULL,
    "TELEFONO" varchar(20) NOT NULL,
    "CORREO_ELECTRONICO" varchar(40) NOT NULL,
    "ID_TIPO_INTEGRANTE" integer NOT NULL REFERENCES "CAT_TIPOINTEGRANTE" ("id") DEFERRABLE INITIALLY DEFERRED,
    "CARGO" varchar(100) NOT NULL
)
;
CREATE TABLE "CAT_ESTADOS" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL
)
;
CREATE TABLE "CAT_MUNICIPIO" (
    "id" serial NOT NULL PRIMARY KEY,
    "DESCRIPCION" varchar(100) NOT NULL,
    "estado_id" integer NOT NULL REFERENCES "CAT_ESTADOS" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
ALTER TABLE "BUZON_CIUDADANO" ADD CONSTRAINT "ID_MUNICIPIO_refs_id_2d26e16e" FOREIGN KEY ("ID_MUNICIPIO") REFERENCES "CAT_MUNICIPIO" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "PROPUESTA" (
    "id" serial NOT NULL PRIMARY KEY,
    "DESCRIPCION" varchar(1000) NOT NULL,
    "id_inscripcion_foro_id" integer NOT NULL REFERENCES "INSCRIPCION_FORO" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "PROPUESTA_MESA" (
    "id" serial NOT NULL PRIMARY KEY,
    "ID_MESA_TEMATICA" integer NOT NULL REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED,
    "ID_PONENCIA" integer NOT NULL REFERENCES "PONENCIA" ("id") DEFERRABLE INITIALLY DEFERRED,
    "FECHA_PROPUESTA" date
)
;
CREATE TABLE "VALORACION" (
    "id" serial NOT NULL PRIMARY KEY,
    "CALIFICACION" integer,
    "DESCRIPCION" varchar(20) NOT NULL
)
;
ALTER TABLE "EVALUACION" ADD CONSTRAINT "ID_VALORACION_refs_id_ae634b0e" FOREIGN KEY ("ID_VALORACION") REFERENCES "VALORACION" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "REPORTES" (
    "id" serial NOT NULL PRIMARY KEY,
    "url" varchar(200) NOT NULL,
    "DESCRIPCION" varchar(20) NOT NULL
)
;
CREATE TABLE "foro_eje_foros" (
    "id" serial NOT NULL PRIMARY KEY,
    "eje_id" integer NOT NULL,
    "mesatematica_id" integer NOT NULL REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("eje_id", "mesatematica_id")
)
;
CREATE TABLE "foro_eje" (
    "catalogo_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "foro_catalogo" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
ALTER TABLE "foro_eje_foros" ADD CONSTRAINT "eje_id_refs_catalogo_ptr_id_ac0aa58f" FOREIGN KEY ("eje_id") REFERENCES "foro_eje" ("catalogo_ptr_id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "foro_mesa" (
    "catalogo_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "foro_catalogo" ("id") DEFERRABLE INITIALLY DEFERRED,
    "foro_id" integer NOT NULL REFERENCES "MESA_TEMATICA" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
ALTER TABLE "INSCRIPCION_FORO" ADD CONSTRAINT "mesa_id_refs_catalogo_ptr_id_6acff229" FOREIGN KEY ("mesa_id") REFERENCES "foro_mesa" ("catalogo_ptr_id") DEFERRABLE INITIALLY DEFERRED;

COMMIT;
