21.sql

ALTER TABLE IF EXISTS transactions_has_categories  DROP CONSTRAINT IF EXISTS transactions_has_categories_transaction_category_id_fk;
ALTER TABLE IF EXISTS transactions_has_categories DROP CONSTRAINT IF EXISTS transactions_has_categories_transaction_id_fk;
DROP TABLE IF EXISTS transactions_has_categories;
DROP TABLE IF EXISTS transaction_categories;
DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
	id SERIAL PRIMARY KEY,
	transaction_id INTEGER,
	owner_id INTEGER NOT NULL,
	wallet_id INTEGER NOT NULL,
	customer_id INTEGER NOT NULL,
	number CHARACTER VARYING(9) DEFAULT 1,
	type SMALLINT DEFAULT 0,
	status SMALLINT DEFAULT 0,
	plots CHARACTER VARYING(2) NOT NULL DEFAULT 1,
	maturity DATE, -- vencimento.
	paid_at DATE, -- pagamento.
	total_amount NUMERIC(15,2) DEFAULT 0,
	total_paid NUMERIC(15,2) DEFAULT 0,
	total_balance NUMERIC(15,2) DEFAULT 0,
	obs TEXT,
	created_at TIMESTAMP(0) DEFAULT NOW(),
	updated_at TIMESTAMP(0) DEFAULT NOW(),
	CONSTRAINT transactions_transaction_id_fk FOREIGN KEY (transaction_id)
		REFERENCES transactions (id) MATCH SIMPLE
			ON UPDATE RESTRICT
			ON DELETE RESTRICT,
	CONSTRAINT transactions_owner_id_fk FOREIGN KEY (owner_id)
		REFERENCES persons (id) MATCH SIMPLE
			ON UPDATE RESTRICT
			ON DELETE RESTRICT,
	CONSTRAINT transactions_wallet_id_fk FOREIGN KEY (wallet_id)
		REFERENCES invoice_wallets (id) MATCH SIMPLE
			ON UPDATE RESTRICT
			ON DELETE RESTRICT
);

CREATE TABLE transaction_categories (
	id SERIAL PRIMARY KEY,
	name CHARACTER VARYING(25) NOT NULL,
	created_at TIMESTAMP(0) DEFAULT NOW()
);

CREATE TABLE transactions_has_categories (
	transaction_id INTEGER NOT NULL,
	transaction_category_id INTEGER NOT NULL,
	created_at TIMESTAMP(0) DEFAULT NOW(),
	CONSTRAINT transactions_pk PRIMARY KEY (transaction_id, transaction_category_id),
	CONSTRAINT transactions_has_categories_transaction_id_fk FOREIGN KEY (transaction_id)
		REFERENCES transactions (id) MATCH SIMPLE
			ON UPDATE RESTRICT
			ON DELETE RESTRICT,
	CONSTRAINT transactions_has_categories_transaction_category_id_fk FOREIGN KEY (transaction_category_id)
		REFERENCES transaction_categories (id) MATCH SIMPLE
			ON UPDATE RESTRICT
			ON DELETE RESTRICT
);