-- rozdelenie tabulky billitem pre jednotlive zmluvne strany
-- a dalsie zmeny suvisiace s prerabkami billingu

\encoding UTF8



-- nastavovanie zdanitelneho plnenia, ticket #61028
CREATE TABLE tarifitem_vat_type (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	comment VARCHAR,
	PRIMARY KEY (id)
);

INSERT INTO tarifitem_vat_type (name, comment) VALUES ('Štandardný režim','Deň zdaniteľného plnenia je deň dodania služby, alebo prijatia úhrady'),('Výnimka pre telco','Deň zdaniteľného plnenia je deň vystavenia faktúry');

GRANT SELECT ON tarifitem_vat_type TO extranet, billing;

COMMENT ON TABLE tarifitem_vat_type IS 'Ciselnik moznosti datumov zdanitelneho plnenia';

ALTER TABLE tarifitem ADD COLUMN vat_type INT NOT NULL DEFAULT 1 REFERENCES tarifitem_vat_type (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE tarifitemrate ADD COLUMN vat_type INT NOT NULL DEFAULT 1 REFERENCES tarifitem_vat_type (id) ON UPDATE CASCADE ON DELETE RESTRICT;

COMMENT ON COLUMN tarifitem.vat_type IS 'Sposob akym sa urci datum zdanitelneho plnenia';
COMMENT ON COLUMN tarifitemrate.vat_type IS 'Sposob akym sa urci datum zdanitelneho plnenia';



-- prepis billingu, ticket #62257
CREATE TABLE contract_party (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	PRIMARY KEY (id)
);

-- mohol by byt enum... ?
INSERT INTO contract_party (name) VALUES ('supplier'),('customer'),('mediator'),('referrer'),('matchmaker');

GRANT SELECT ON contract_party TO extranet, billing;

COMMENT ON TABLE contract_party IS 'Ciselnik zmluvnych stran';



CREATE TABLE billitems_type (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	flexi_evidence VARCHAR,
	flexi_type VARCHAR,
	PRIMARY KEY (id)
);

INSERT INTO billitems_type (name, flexi_evidence, flexi_type) VALUES ('Faktúra','faktura-vydana','FAKTURA'),('Dobropis','faktura-vydana','DOBROPIS'),('Výzva k úhrade','faktura-vydana','VYZVA'),('Proforma','faktura-vydana','PROFORMA'),('Objednávka','objednavka-vydana','OBV'),('Zaplatený kredit',null,null),('Spotreba kreditu',null,null);

GRANT SELECT ON billitems_type TO extranet, billing;

COMMENT ON TABLE billitems_type IS 'Typy zaznamov billitems';



CREATE TABLE tarifitem_currency (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	code VARCHAR NOT NULL,
	symbol VARCHAR NOT NULL,
	fiat BOOLEAN NOT NULL DEFAULT TRUE,
	PRIMARY KEY (id)
);

INSERT INTO tarifitem_currency (name, code, symbol, fiat) VALUES ('Euro','EUR','€',true),('SMS do SR/ČR','XSMS','SMS',false);

GRANT SELECT ON tarifitem_currency TO extranet, billing;

COMMENT ON TABLE tarifitem_currency IS 'Ciselnik penaznych a inych uctovatelnych mien a jednotiek (napr. vernostne body)';
COMMENT ON COLUMN tarifitem_currency.code IS 'Trojpismenny kod meny podla ISO 4217, alebo iny vymysleny ticker ak ide o pripad fiat=false';
COMMENT ON COLUMN tarifitem_currency.symbol IS 'Znak meny v UTF';
COMMENT ON COLUMN tarifitem_currency.fiat IS 'Ci je mena oficialna v beznom slova zmysle (true), alebo ide o internu menu firmy (false)';

ALTER TABLE tarifitemrate ADD COLUMN currency INT NOT NULL DEFAULT 1 REFERENCES tarifitem_currency (id) ON UPDATE CASCADE ON DELETE RESTRICT;

COMMENT ON COLUMN tarifitemrate.currency IS 'Mena, v ktorej je cena vyjadrena';



CREATE TABLE billitems (
	id SERIAL NOT NULL,

	-- identifikacia stran, ktorych sa zaznam tyka
	issuer_type INT NOT NULL REFERENCES contract_party (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	issuer_person INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	client_type INT NOT NULL REFERENCES contract_party (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	client_person INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	evid INT,

	-- typ zaznamu
	type INT NOT NULL REFERENCES billitems_type (id) ON UPDATE CASCADE ON DELETE RESTRICT,

	-- vazby na udaje z ktorych polozka vznikla
	contract_item INT REFERENCES contract_item (id) ON UPDATE CASCADE ON DELETE SET NULL,
	tarifitem INT REFERENCES tarifitem (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	billitem_corrected INT REFERENCES billitems (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	billitem_joiner INT,

	-- opisne udaje o polozke
	name VARCHAR NOT NULL,
	textinfo TEXT,
	comment TEXT,

	-- udaje ku cene polozky
	quantity NUMERIC(12,2) NOT NULL,
	rate NUMERIC(14,8),
	currency INT NOT NULL DEFAULT 1 REFERENCES tarifitem_currency ON UPDATE CASCADE ON DELETE RESTRICT,
	discount NUMERIC(5,4) NOT NULL DEFAULT 0.0,
	vat_coef NUMERIC(2,0),
	vat_included BOOLEAN,
	vat_type INT NOT NULL DEFAULT 1 REFERENCES tarifitem_vat_type (id) ON UPDATE CASCADE ON DELETE RESTRICT,

	-- ine udaje
	automatic BOOLEAN NOT NULL DEFAULT FALSE,
	invoice_asap BOOLEAN NOT NULL DEFAULT FALSE,
	created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
	tax_date DATE,

	-- casove intervaly
	period_start TIMESTAMP WITH TIME ZONE NOT NULL,
	period_end TIMESTAMP WITH TIME ZONE NOT NULL,
	bill_start TIMESTAMP WITH TIME ZONE,
	bill_end TIMESTAMP WITH TIME ZONE,

	-- kod faktury
	invoice VARCHAR,

	-- zauctovanie - varchar preto, lebo tam moze byt kod s nulou na zaciatku, napr. 041001
	acct_debit VARCHAR(6),
	acct_credit VARCHAR(6),

	PRIMARY KEY (id)
);

CREATE INDEX billitems_issuer_type ON billitems (issuer_type);
CREATE INDEX billitems_issuer_person ON billitems (issuer_person);
CREATE INDEX billitems_client_type ON billitems (client_type);
CREATE INDEX billitems_client_person ON billitems (client_person);
CREATE INDEX billitems_evid ON billitems (evid);
CREATE INDEX billitems_contract_item ON billitems (contract_item);
CREATE INDEX billitems_period ON billitems (period_start, period_end);
CREATE INDEX billitems_invoice ON billitems (invoice);
CREATE INDEX billitems_billitem_corrected ON billitems (billitem_corrected);

GRANT SELECT, INSERT, UPDATE ON billitems TO extranet, billing;
GRANT USAGE ON billitems_id_seq TO extranet, billing;

COMMENT ON COLUMN billitems.issuer_type IS 'Typ dodavatela - supplier, alebo mediator';
COMMENT ON COLUMN billitems.issuer_person IS 'Osoba, ktora vystupuje ako dodavatel';
COMMENT ON COLUMN billitems.client_type IS 'Typ vazby dodavatela na klienta - ci je klientom customer, mediator, referrer';
COMMENT ON COLUMN billitems.client_person IS 'Osoba, ktora vystupuje ako klient';
COMMENT ON COLUMN billitems.evid IS 'Cislo zmluvy, ktorej sa tento zaznam tyka (nepovinne)';
COMMENT ON COLUMN billitems.type IS 'Typ dokladu - o aky danovy doklad alebo pohyb prepaid kreditu ide';
COMMENT ON COLUMN billitems.currency IS 'Mena alebo jednotka v ktorej je zaznam uctovany (okrem EUR moze byt napr. pocet SMS, tel. minut a pod.)';
COMMENT ON COLUMN billitems.contract_item IS 'Odkaz na polozku zmluvy ak zaznam vznikol fakturaciou polozky zmluvy';
COMMENT ON COLUMN billitems.tarifitem IS 'Odkaz na polozku tarify';
COMMENT ON COLUMN billitems.billitem_corrected IS 'Odkaz na opravovanu/dobropisovanu polozku';
COMMENT ON COLUMN billitems.billitem_joiner IS 'Cislo suvisiacej skupiny zaznamov (vyberat zo sekvencie billitem_id_seq)';
COMMENT ON COLUMN billitems.name IS 'Nazov polozky';
COMMENT ON COLUMN billitems.textinfo IS 'Spresnujuce textove informacie (pre zakaznika)';
COMMENT ON COLUMN billitems.comment IS 'Poznamka interna';
COMMENT ON COLUMN billitems.quantity IS 'Mnozstvo jednotiek';
COMMENT ON COLUMN billitems.rate IS 'Jednotkova cena';
COMMENT ON COLUMN billitems.discount IS 'Zlava vyjadrena ako cislo od 0.0 (bez zlavy) do 1.0 (100% zlava)';
COMMENT ON COLUMN billitems.vat_coef IS 'Sadzba DPH';
COMMENT ON COLUMN billitems.vat_included IS 'Ci je DPH zahrnute v jednotkovej cene';
COMMENT ON COLUMN billitems.automatic IS 'Ci bola polozka vytvorena automaticky (skriptom)';
COMMENT ON COLUMN billitems.invoice_asap IS 'Ci sa polozka fakturuje ASAP (surne) alebo staci zahrnut do najblizsej faktury';
COMMENT ON COLUMN billitems.created IS 'Datum vytvorenia polozky';
COMMENT ON COLUMN billitems.tax_date IS 'Datum zdanitelneho plnenia polozky';
COMMENT ON COLUMN billitems.vat_type IS 'Sposob akym sa urci datum zdanitelneho plnenia';
COMMENT ON COLUMN billitems.period_start IS 'Zaciatok uctovaneho obdobia';
COMMENT ON COLUMN billitems.period_end IS 'Koniec uctovaneho obdobia';
COMMENT ON COLUMN billitems.bill_start IS 'Prvy den uctovania tejto polozky';
COMMENT ON COLUMN billitems.bill_end IS 'Posledny den uctovania tejto polozky';
COMMENT ON COLUMN billitems.invoice IS 'Cislo faktury ktora polozku obsahuje';
COMMENT ON COLUMN billitems.acct_debit IS 'Sposob zauctovania v podvojnom uctovnictve (strana MD) - ako sluzbu na vyslej fakture uctuje ten kto ju vydal';
COMMENT ON COLUMN billitems.acct_credit IS 'Sposob zauctovania v podvojnom uctovnictve (strana Dal) - ako sluzbu na vyslej fakture uctuje ten kto ju vydal';



-- pomocny stlpec pre mazanie faktur, ticket #61029
ALTER TABLE message ADD COLUMN code VARCHAR;
COMMENT ON COLUMN message.code IS 'Kod objektu, o ktory v sprave ide, specificky podla typu spravy - napr. cislo faktury pri el.fakture';



-- billinterval ma byt aj v tarifitemrate
ALTER TABLE tarifitemrate ADD COLUMN billinterval INT REFERENCES billinterval (id) ON UPDATE CASCADE ON DELETE RESTRICT;
COMMENT ON COLUMN tarifitemrate.billinterval IS 'Interval uctovania (podla tabulky billinterval)';
