CREATE TABLE rpgsm.rhp_info(
  guid varchar(36) COLLATE "C" NOT NULL DEFAULT uuid_generate_v4(),
  version INTEGER NOT NULL ,
  info_type smallint NOT NULL,
  is_actual BOOLEAN NOT NULL,
  status smallint NOT NULL,
  start_date date NOT NULL,
  finish_date date NOT NULL,
  oktmo_guid varchar(36) COLLATE "C" NOT NULL,
  oktmo_code VARCHAR(11) NOT NULL,
  heat_supply_guid varchar(36) COLLATE "C",
  heat_distribution_guid varchar(36) COLLATE "C",
  consumer_guid varchar(36) COLLATE "C",
  readiness_heating_act_attachments_count smallint NOT NULL DEFAULT 0,
  revision_house_act_attachments_count smallint NOT NULL DEFAULT 0,
  readiness_heating_passport_attachments_count smallint NOT NULL DEFAULT 0,
  other_attachments_count smallint NOT NULL DEFAULT 0,
  revision_house_addresses_count integer NOT NULL DEFAULT 0,
  user_guid varchar(36) COLLATE "C" NOT NULL,
  last_editing_date timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT pk_rhp_info PRIMARY KEY (guid, version),
  CONSTRAINT chk_status CHECK (status = 1 OR status = 2 OR status = 4),
  CONSTRAINT chk_info_type CHECK (info_type = 1 OR info_type = 2)
);
COMMENT ON TABLE rpgsm.rhp_info IS 'Информация о готовности к отопительному периоду. Корневые сущности';
COMMENT ON COLUMN rpgsm.rhp_info.guid IS 'Идентификационный код';
COMMENT ON COLUMN rpgsm.rhp_info.version IS 'Тип информации. 1 - Информация о паспортах готовности к отопительному сезону (периоду) муниципального образования;
                    2 - Информация о готовности к отопительному сезону (периоду) объектов жилищно-коммунального хозяйства.';


CREATE TABLE rpgsm.rhp_revision_house_addresses(
  info_guid varchar(36) COLLATE "C" NOT NULL,
  house_guid varchar(36) COLLATE "C" NOT NULL,
  ao_guid varchar(36) COLLATE "C" NOT NULL,
  from_version BIGINT NOT NULL,
  to_version BIGINT,
  CONSTRAINT pk_revision_house_addresses PRIMARY KEY (info_guid, house_guid),--?
  CONSTRAINT fk_rhp_revision_house_addresses_from_version FOREIGN KEY (info_guid, from_version) REFERENCES rpgsm.rhp_info (guid,version),
  CONSTRAINT fk_rhp_revision_house_addresses_to_version FOREIGN KEY (info_guid, to_version) REFERENCES rpgsm.rhp_info (guid,version)
);

CREATE TABLE rpgsm.rhp_attachments(
  info_guid varchar(36) COLLATE "C" NOT NULL,
  fs_guid varchar(36) COLLATE "C" NOT NULL,
  from_version BIGINT NOT NULL,
  to_version BIGINT,
  type SMALLINT NOT NULL,
  CONSTRAINT pk_rhp_attachments PRIMARY KEY (info_guid, fs_guid),
  CONSTRAINT fk_rhp_attachments_from_version FOREIGN KEY (info_guid, from_version) REFERENCES rpgsm.rhp_info (guid,version),
  CONSTRAINT fk_rhp_attachments_to_version FOREIGN KEY (info_guid, to_version) REFERENCES rpgsm.rhp_info (guid,version)
);