-- vymahanie pohladavok

-- pohladavky posunute na vymahanie
CREATE TABLE debt_collection (
	id SERIAL NOT NULL,
	supplier INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	customer INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	collector INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	evid INT,
	invoice NUMERIC(10,0),
	base_amount NUMERIC(12,2) NOT NULL,
	due_date DATE NOT NULL,
	interest_rate NUMERIC(7,4),
	interest_interval_days INT,
	cost_supplier BOOLEAN NOT NULL DEFAULT FALSE,
	started DATE NOT NULL,
	active BOOLEAN NOT NULL DEFAULT TRUE,
	PRIMARY KEY (id)
);

CREATE INDEX debt_collection_supplier ON debt_collection (supplier);
CREATE INDEX debt_collection_customer ON debt_collection (customer);
CREATE INDEX debt_collection_collector ON debt_collection (collector);
CREATE INDEX debt_collection_evid ON debt_collection (evid);

GRANT SELECT, INSERT, UPDATE ON debt_collection TO billing;
GRANT USAGE ON debt_collection_id_seq TO billing;

GRANT SELECT, UPDATE ON debt_collection TO extranet;

COMMENT ON TABLE debt_collection IS 'zoznam pohladavok posunutych na vymahanie externou firmou';
COMMENT ON COLUMN debt_collection.supplier IS 'veritel';
COMMENT ON COLUMN debt_collection.customer IS 'dlznik';
COMMENT ON COLUMN debt_collection.collector IS 'vymahac';
COMMENT ON COLUMN debt_collection.evid IS 'cislo zmluvy';
COMMENT ON COLUMN debt_collection.invoice IS 'cislo faktury, ak sa vymaha konkretna faktura';
COMMENT ON COLUMN debt_collection.base_amount IS 'suma istiny v eur';
COMMENT ON COLUMN debt_collection.due_date IS 'povodny datum splatnosti';
COMMENT ON COLUMN debt_collection.interest_rate IS 'sadzba uroku z omeskania v percentach';
COMMENT ON COLUMN debt_collection.interest_interval_days IS 'interval sadzby uroku (1 ak ide o denny urok, 365 ak ide o rocny urok)';
COMMENT ON COLUMN debt_collection.cost_supplier IS 'priznak ci naklady vymahania znasa veritel (TRUE=veritel, FALSE=dlznik)';
COMMENT ON COLUMN debt_collection.started IS 'datum odovzdania na vymahanie';
COMMENT ON COLUMN debt_collection.active IS 'priznak ci tato pohladavka je este vymahana';

-- zaznamy a zmeny stavu ku vymahaniu
CREATE TABLE debt_collection_record (
	id SERIAL NOT NULL,
	debt INT NOT NULL REFERENCES debt_collection (id) ON UPDATE CASCADE ON DELETE CASCADE,
	person INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	created TIMESTAMP NOT NULL DEFAULT NOW(),
	message VARCHAR,
	collected_amount NUMERIC(12,2),
	PRIMARY KEY (id)
);

CREATE INDEX debt_collection_record_debt ON debt_collection_record (debt);
CREATE INDEX debt_collection_record_person ON debt_collection_record (person);

GRANT SELECT, INSERT, UPDATE ON debt_collection_record TO billing, extranet;
GRANT USAGE ON debt_collection_record_id_seq TO billing, extranet;

COMMENT ON TABLE debt_collection_record IS 'zaznamy a zmeny stavu ku vymahanym pohladavkam';
COMMENT ON COLUMN debt_collection_record.debt IS 'ID pohladavky z tabulky debt_collection';
COMMENT ON COLUMN debt_collection_record.person IS 'osoba, ktora zaznam vytvorila (vo vacsine pripadov asi vymahac)';
COMMENT ON COLUMN debt_collection_record.created IS 'datum vytvorenia zaznamu';
COMMENT ON COLUMN debt_collection_record.message IS 'sprava o stave - nestrukturovana informacia';
COMMENT ON COLUMN debt_collection_record.collected_amount IS 'vymozena suma, ak tento zaznam nieco vymohol';

-- rozsirenie vymahania o stav a o polozky, ktore sa este nezacali vymahat
CREATE TABLE debt_collection_status (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	sysname VARCHAR NOT NULL,
	PRIMARY KEY(id)
);

INSERT INTO debt_collection_status (sysname, name) VALUES
	('created','na schválenie'),
	('approved','schválené'),
	('active','vymáhané'),
	('done','ukončené'),
	('cancelled','zrušené');

ALTER TABLE debt_collection ALTER COLUMN collector DROP NOT NULL;
ALTER TABLE debt_collection ALTER COLUMN started DROP NOT NULL;
ALTER TABLE debt_collection DROP COLUMN active;
ALTER TABLE debt_collection ADD COLUMN status INT NOT NULL REFERENCES debt_collection_status(id) ON UPDATE CASCADE ON DELETE RESTRICT;

GRANT SELECT ON debt_collection_status TO extranet, billing;

-- POZOR: vyssie nad tymto miestom uz nemenit - vyvorene uz aj v ostrej DB 20.2.2018

INSERT INTO message_template (sysname, name, description, type, handler)
VALUES (
	'debt/collection', 'Odovzdanie zoznamu pohľadávok vymáhacej firme',
	'Automatická správa emailom obsahujúca zoznam nových pohľadávok na vymáhanie',
	23, null
);

INSERT INTO message_template_variant (template, layout, medium_variant, subject, body, attachment)
VALUES (
	(SELECT id FROM message_template WHERE sysname='debt/collection'),
	(SELECT id FROM message_layout WHERE sysname='plaintext-email-company'),
	(SELECT id FROM message_medium_variant WHERE medium=1 AND params='html:no,plaintext:yes'),
	'Pohladavky na vymahanie',
	'Vážená spoločnosť,\nv prílohe Vám zasielame zoznam pohľadávok na vymáhanie.',
	'CSV so zoznamom pohľadávok'
);
