BEGIN;
CREATE TABLE "AtencionCiudadana_estado" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("nombre")
)
;
CREATE TABLE "AtencionCiudadana_municipio" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL,
    "estado_id" integer NOT NULL REFERENCES "AtencionCiudadana_estado" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("nombre", "estado_id")
)
;
CREATE TABLE "AtencionCiudadana_localidad" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL,
    "municipio_id" integer NOT NULL REFERENCES "AtencionCiudadana_municipio" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("nombre", "municipio_id")
)
;
CREATE TABLE "AtencionCiudadana_dependencia_usuarios" (
    "id" serial NOT NULL PRIMARY KEY,
    "dependencia_id" integer NOT NULL,
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("dependencia_id", "user_id")
)
;
CREATE TABLE "AtencionCiudadana_dependencia" (
    "id" serial NOT NULL PRIMARY KEY,
    "clave" varchar(3) NOT NULL,
    "nombre" varchar(50) NOT NULL,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("nombre")
)
;
ALTER TABLE "AtencionCiudadana_dependencia_usuarios" ADD CONSTRAINT "dependencia_id_refs_id_5625118e" FOREIGN KEY ("dependencia_id") REFERENCES "AtencionCiudadana_dependencia" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "AtencionCiudadana_estatus" (
    "id" serial NOT NULL PRIMARY KEY,
    "clave" varchar(3) NOT NULL,
    "nombre" varchar(50) NOT NULL,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("nombre")
)
;
CREATE TABLE "AtencionCiudadana_lugar" (
    "id" serial NOT NULL PRIMARY KEY,
    "padre_id" integer,
    "nombre" varchar(50) NOT NULL,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    UNIQUE ("padre_id", "nombre")
)
;
ALTER TABLE "AtencionCiudadana_lugar" ADD CONSTRAINT "padre_id_refs_id_4770cb37" FOREIGN KEY ("padre_id") REFERENCES "AtencionCiudadana_lugar" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "AtencionCiudadana_solicitud" (
    "id" serial NOT NULL PRIMARY KEY,
    "nombre" varchar(50) NOT NULL,
    "apellido_paterno" varchar(50) NOT NULL,
    "apellido_materno" varchar(50) NOT NULL,
    "sexo" varchar(1) NOT NULL,
    "curp" varchar(18) NOT NULL,
    "localidad_id" integer NOT NULL REFERENCES "AtencionCiudadana_lugar" ("id") DEFERRABLE INITIALLY DEFERRED,
    "direccion" text,
    "codigo_postal" integer,
    "identificacion" varchar(13) NOT NULL,
    "fecha_nacimiento" date,
    "correo_electronico" varchar(30),
    "telefono" varchar(40),
    "celular" varchar(10),
    "solicitud" text NOT NULL,
    "dependencia_id" integer REFERENCES "AtencionCiudadana_dependencia" ("id") DEFERRABLE INITIALLY DEFERRED,
    "foto" varchar(100),
    "estatus_id" integer NOT NULL REFERENCES "AtencionCiudadana_estatus" ("id") DEFERRABLE INITIALLY DEFERRED,
    "leido" boolean NOT NULL,
    "asigna_id" integer REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    "resuelto" boolean NOT NULL
)
;
CREATE TABLE "AtencionCiudadana_asignaciones" (
    "id" serial NOT NULL PRIMARY KEY,
    "solicitud_id" integer NOT NULL REFERENCES "AtencionCiudadana_solicitud" ("id") DEFERRABLE INITIALLY DEFERRED,
    "dependencia_id" integer NOT NULL REFERENCES "AtencionCiudadana_dependencia" ("id") DEFERRABLE INITIALLY DEFERRED,
    "nota" text NOT NULL,
    "asigna_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone
)
;
CREATE TABLE "AtencionCiudadana_bitacora" (
    "id" serial NOT NULL PRIMARY KEY,
    "fecha" timestamp with time zone NOT NULL,
    "solicitud_id" integer NOT NULL REFERENCES "AtencionCiudadana_solicitud" ("id") DEFERRABLE INITIALLY DEFERRED,
    "estatus_id" integer NOT NULL REFERENCES "AtencionCiudadana_estatus" ("id") DEFERRABLE INITIALLY DEFERRED,
    "recibio_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fecha_registro" timestamp with time zone NOT NULL,
    "fecha_ultimo_cambio" timestamp with time zone,
    "dependencia_id" integer REFERENCES "AtencionCiudadana_dependencia" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("solicitud_id", "estatus_id", "recibio_id", "dependencia_id")
)
;
COMMIT;