CREATE TABLE t_demande
(
  id_tech character varying(24) NOT NULL,
  id_requerant character varying(24),
  dem_ref_demande_pays integer,
  dem_ref_demande_suite character varying(16),
  dem_pays_remb integer,
  dem_version character varying(19),
  dem_dt_deb_rbt timestamp without time zone,
  dem_dt_fin_rbt timestamp without time zone,
  dem_iban character varying(35),
  dem_bic character varying(25),
  dem_nom_titulaire character varying,
  dem_type_titulaire character varying(14),
  dem_code_monnaie integer,
  dem_montant_import double precision,
  dem_montant_acqui double precision,
  dem_num_4000 character varying,
  CONSTRAINT t_demande_pkey PRIMARY KEY (id_tech),
  CONSTRAINT fk_code_monnaie_demande FOREIGN KEY (dem_code_monnaie)
      REFERENCES n_monnaie (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_pays_remb_demande FOREIGN KEY (dem_pays_remb)
      REFERENCES n_code_pays (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_ref_pays_demande FOREIGN KEY (dem_ref_demande_pays)
      REFERENCES n_code_pays (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_demande_t_fiscalis FOREIGN KEY (id_tech)
      REFERENCES t_fiscalis (id_tech) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_demande_t_operateur FOREIGN KEY (id_requerant)
      REFERENCES t_operateur (id_tech) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_dem_ref
  ON t_demande
  USING btree
  (dem_ref_demande_pays, dem_ref_demande_suite);
CREATE INDEX idx_dem_req
  ON t_demande
  USING btree
  (id_requerant);

-- Table: t_operation
CREATE TABLE t_operation
(
  id_tech character varying(24) NOT NULL,
  id_demande character varying(24) NOT NULL,
  id_fournisseur character varying(24),
  type_operation character varying(10),
  op_num_seq integer NOT NULL,
  op_num_ref character varying,
  op_dt_delivrance timestamp without time zone NOT NULL,
  op_code_monnaie integer,
  op_montant_imposable double precision,
  op_montant_tva double precision,
  op_prorata_deduction double precision,
  op_montant_tva_deductible double precision,
  op_num_seq_rebeca character varying(50),
  imp_info_ref_importation character varying,
  acq_facture_simplifiee boolean,
  CONSTRAINT t_operation_pkey PRIMARY KEY (id_tech),
  CONSTRAINT fk_code_monnaie_operation FOREIGN KEY (op_code_monnaie)
      REFERENCES n_monnaie (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_operation_t_demande FOREIGN KEY (id_demande)
      REFERENCES t_demande (id_tech) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_operation_t_operateur FOREIGN KEY (id_fournisseur)
      REFERENCES t_operateur (id_tech) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE INDEX idx_op_fournisseur
  ON t_operation
  USING btree
  (id_fournisseur);
CREATE INDEX idx_operation_demande
  ON t_operation
  USING btree
  (id_demande);

-- Table: t_bien_service
CREATE TABLE t_bien_service
(
  id_tech character varying(24) NOT NULL,
  id_operation character varying(24) NOT NULL,
  bs_code integer,
  bs_sous_code integer,
  bs_description character varying,
  bs_code_langue integer,
  CONSTRAINT t_bien_service_pkey PRIMARY KEY (id_tech),
  CONSTRAINT fk_code_bien_service FOREIGN KEY (bs_code)
      REFERENCES n_bien_service (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_code_langue_bien_service FOREIGN KEY (bs_code_langue)
      REFERENCES n_langue (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_sous_code_bien_service FOREIGN KEY (bs_sous_code)
      REFERENCES n_bien_service (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_bien_service_t_operation FOREIGN KEY (id_operation)
      REFERENCES t_operation (id_tech) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_bien_service_code
  ON t_bien_service
  USING btree
  (bs_code);
CREATE INDEX idx_bien_service_operation
  ON t_bien_service
  USING btree
  (id_operation);
CREATE INDEX idx_bien_service_sous_code
  ON t_bien_service
  USING btree
  (bs_sous_code);

-- Table: n_bien_service
CREATE TABLE n_bien_service
(
  id integer NOT NULL,
  parent integer,
  code character varying(20) NOT NULL,
  lib_code character varying(512),
  debut_validite timestamp without time zone NOT NULL,
  fin_validite timestamp without time zone NOT NULL,
  CONSTRAINT n_bien_service_pkey PRIMARY KEY (id),
  CONSTRAINT e FOREIGN KEY (parent)
      REFERENCES n_bien_service (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);