CREATE SEQUENCE IF NOT EXISTS seq_flx_pix
    START 1
    INCREMENT 1
    NO MAXVALUE
    CACHE 1;

CREATE TABLE IF NOT EXISTS flx_pix (
	filial int NOT NULL,
	banco int NOT NULL,
	id bigint NOT NULL,
	txid character varying (35) NOT NULL CHECK (LENGTH(txid) BETWEEN 26 AND 35),
	tipo character varying (4) NOT NULL CHECK (lower(tipo) IN ('cob', 'cobv')),
	emv character varying (2000) NOT NULL,
	qrcode character varying NOT NULL,
	criacao timestamp without time zone NOT NULL,
	expiracao bigint NOT NULL,
	valor numeric(18,2) NOT NULL,
	status character varying (1) NOT NULL CHECK (status IN ('', 'A', 'B', 'C')),



    CONSTRAINT pk_flx_pix PRIMARY KEY (filial, banco, id),
	CONSTRAINT uk_flx_pix UNIQUE (filial, txid),
    CONSTRAINT fk_flx_pix_filial FOREIGN KEY (filial)
        REFERENCES public.bas_filial (codigo) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT fk_flx_pix_banco FOREIGN KEY (banco)
        REFERENCES public.flx_banco (codigo) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS flx_pix_log (
    filial int NOT NULL,
    banco int NOT NULL,
    id bigint NOT NULL,
    sequencia bigint GENERATED ALWAYS AS IDENTITY,
	hora timestamp WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    last_request json NOT NULL,

    CONSTRAINT pk_flx_pix_log PRIMARY KEY (filial, banco, id, sequencia),
    CONSTRAINT fk_flx_pix_log FOREIGN KEY (filial, banco, id)
        REFERENCES public.flx_pix (filial, banco, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS flx_pix_devedor (
	filial int NOT NULL,
	banco int NOT NULL,
	id bigint NOT NULL,
	nome character varying (200),
	cpf character varying (11),
	cnpj character varying (14),

    CONSTRAINT pk_flx_pix_devedor PRIMARY KEY (filial, banco, id),
    CONSTRAINT fk_flx_pix_devedor FOREIGN KEY (filial, banco, id)
        REFERENCES public.flx_pix (filial, banco, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS flx_pix_devedor_endereco (
	filial int NOT NULL,
	banco int NOT NULL,
	id bigint NOT NULL,
	email character varying (200),
	logradouro character varying (200),
	cidade character varying (200),
	uf character varying (2),
	cep character varying (8),

    CONSTRAINT pk_flx_pix_devedor_endereco PRIMARY KEY (filial, banco, id),
    CONSTRAINT fk_flx_pix_devedor_endereco FOREIGN KEY (filial, banco, id)
        REFERENCES public.flx_pix_devedor (filial, banco, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
);

CREATE TABLE flx_pix_location(
	filial integer NOT NULL,
	banco integer NOT NULL,
	locationid bigint NOT NULL,
	locationurl character varying (200) NOT NULL,
	tipo character varying (4) NOT NULL CHECK (lower(tipo) IN ('cob', 'cobv')),
	criacao timestamp without time zone NOT NULL,

	CONSTRAINT pk_flx_pix_location PRIMARY KEY (filial, banco, locationid),
	CONSTRAINT fk_flx_pix_location_filial FOREIGN KEY (filial)
        REFERENCES public.bas_filial (codigo) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT fk_flx_pix_location_banco FOREIGN KEY (banco)
        REFERENCES public.flx_banco (codigo) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
);

CREATE TABLE flx_pix_relacionamento_pedido(
	filial integer NOT NULL,
	banco integer NOT NULL,
	id bigint NOT NULL,
	filial_orc integer NOT NULL,
	orcamento integer NOT NULL,
	sequencia integer NOT NULL,

	CONSTRAINT pk_flx_pix_relacionamento_pedido PRIMARY KEY (filial, banco, id, filial_orc, orcamento, sequencia),
	CONSTRAINT uk_flx_pix_relacionamento_pedido_id UNIQUE (filial, banco, id),
	CONSTRAINT uk_flx_pix_relacionamento_pedido_pagto_ped UNIQUE (filial_orc, orcamento, sequencia),
    CONSTRAINT fk_flx_pix_relacionamento_pedido_id FOREIGN KEY (filial, banco, id)
        REFERENCES public.flx_pix (filial, banco, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT fk_flx_pix_relacionamento_pedido_pagto_ped FOREIGN KEY (filial_orc, orcamento, sequencia)
        REFERENCES public.vnd_pagto_ped (filial, nr_pedido, sequencia) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT

);