CREATE TABLE public.count_events (
	id bigserial NOT NULL,
	sm_log_id int8 NOT NULL,
	"date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	meta jsonb NOT NULL DEFAULT '{}'::jsonb,
	objecttype varchar(45) NOT NULL,
	status varchar(7) NOT NULL
);
CREATE UNIQUE INDEX count_events_id_idx ON public.count_events USING btree (id);

CREATE TABLE public.count_events_configuration (
	id serial NOT NULL,
	"object" varchar(255) NOT NULL,
	status varchar(3) NOT NULL,
	"configuration" jsonb NOT NULL DEFAULT '{}'::json
);

CREATE TABLE public.count_invoices (
	id bigserial NOT NULL,
    created_by bigint NULL,
	client_id bigint NULL,
	supplier_id bigint NULL,
	invoice_date date NOT NULL,
    date_due date NULL,
	service_date date NULL,
	service_due date NULL,
	invoice_number varchar(50) NULL,
    invoice_sequence int NULL,
    invoice_year int2 NOT NULL GENERATED ALWAYS AS (EXTRACT(YEAR FROM invoice_date)) stored,
	invoice_total numeric(12, 2) NOT NULL DEFAULT 0,
	invoice_vat numeric(12, 2) NOT NULL DEFAULT 0,
	currency varchar(3) NOT NULL,
    reference varchar(255) NULL,
	paid bool NOT NULL,
	credit_note bool NOT NULL,
	proforma bool NOT NULL,
	meta jsonb NULL,
	status varchar(3) NULL,
	CONSTRAINT count_invoices_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX count_invoices_client_id_idx ON public.count_invoices (client_id,supplier_id,invoice_date,invoice_number);
ALTER TABLE public.count_invoices ADD CONSTRAINT count_invoices_fk FOREIGN KEY (client_id) REFERENCES public.registry_entities(id);
ALTER TABLE public.count_invoices ADD CONSTRAINT count_invoices_un UNIQUE (reference,proforma,credit_note);



CREATE TABLE public.count_invoices_rows (
	id bigserial NOT NULL,
    ord int2 NOT NULL DEFAULT 1,
	invoice_id int8 NOT NULL,
    service_id int8 NULL,
    product_id int8 NULL,
	description varchar(255) NOT NULL,
	quantity numeric(8, 2) NULL,
	unit_price numeric(12, 2) NOT NULL,
    unit_measure varchar(50) NULL,
	row_total numeric(12, 2) NOT NULL GENERATED ALWAYS AS (quantity * unit_price) stored,
	row_vat decimal(5,2) NOT NULL,
	row_total_vat numeric(12, 2) NOT NULL GENERATED ALWAYS AS ((quantity * unit_price) + (((quantity * unit_price) / 100) * row_vat)) stored,
	meta jsonb NULL,
	CONSTRAINT count_invoices_rows_pk PRIMARY KEY (id)
);
CREATE INDEX count_invoices_rows_invoice_id_idx ON public.count_invoices_rows (invoice_id,description,unit_price);
ALTER TABLE public.count_invoices_rows ADD CONSTRAINT count_invoices_rows_fk FOREIGN KEY (invoice_id) REFERENCES public.count_invoices(id) ON DELETE CASCADE;


CREATE TABLE public.code_list_currencies (
	code varchar(3) NOT NULL,
	description varchar(50) NULL,
	ord int8 NULL,
	icon varchar(255) NULL,
	misc varchar(255) NULL,
	properties jsonb NULL,
	CONSTRAINT code_list_currencies_pk PRIMARY KEY (code)
);

CREATE TABLE public.count_contracts (
	id serial4 NOT NULL,
	description varchar(255) NOT NULL,
	client_id int4 NULL,
	meta jsonb NULL
);

CREATE TABLE public.count_contracts_rows (
	id serial4 NOT NULL,
    ord int4 NOT NULL,
    description varchar(255) NOT NULL,
	contract_id int4 NOT NULL,
	codelist varchar(255) NOT NULL,
	code varchar(7) NOT NULL,
	price decimal(7, 2) NOT NULL,
	meta jsonb NULL
);

CREATE TABLE public.code_list_count_measure_unit (
	code varchar(3) NOT NULL,
	description varchar(50) NULL,
	ord int4 NULL,
	icon varchar(255) NULL,
	misc varchar(255) NULL,
	properties jsonb NULL DEFAULT '{}'::json,
	CONSTRAINT code_list_count_measure_unit_pk PRIMARY KEY (code)
);

CREATE TABLE public.count_cash_flow (
	id serial4 NOT NULL,
	direction varchar(3) NULL,
	date_time timestamp NULL,
	"transaction" varchar(255) NULL,
	client_id int4 NULL,
	supplier_id int4 NULL,
	reference varchar(255) NULL,
	invoiced bool NULL,
	meta jsonb NULL
);
CREATE UNIQUE INDEX count_cash_flow_id_idx ON public.count_cash_flow USING btree (id);

CREATE TABLE public.code_list_count_document_types (
	code varchar(2) NOT NULL,
	description varchar(60) NOT NULL,
	ord int8 NULL,
	icon varchar(255) NULL,
	misc varchar(255) NULL,
	properties jsonb NULL,
	CONSTRAINT idx_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_count_document_types_idx ON public.code_list_count_document_types USING btree (code);


