CREATE TABLE public.count_invoices (
	id bigserial NOT NULL,
	client_id bigint NULL,
	supplier_id bigint NULL,
	invoice_date date NOT 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 money NOT NULL,
	invoice_vat money NOT NULL,
	currency varchar(3) NOT 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);

CREATE TABLE public.count_invoices_rows (
	id bigserial NOT NULL,
	invoice_id int8 NOT NULL,
    service_id int8 NULL,
    product_id int8 NULL,
	description varchar(255) NOT NULL,
	quantity int8 NULL,
	unit_price money NOT NULL,
	row_total money NOT NULL GENERATED ALWAYS AS (quantity * unit_price) stored,
	row_vat decimal(5,2) NOT NULL,
	row_total_vat money 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);

