-- odstranenie supplier z tarifitem a zavedenei tarifitemgroup (RT #49331)

BEGIN;

CREATE TABLE tarifitemgroup (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	sort SMALLINT,
	PRIMARY KEY (id)
);

COMMENT ON TABLE tarifitemgroup IS 'skupiny tarifnych poloziek';
COMMENT ON COLUMN tarifitemgroup.name IS 'nazov skupiny (pozor - zobrazi sa pouzivatelovi v tarife)';
COMMENT ON COLUMN tarifitemgroup.sort IS 'poradie, v akom sa skupiny zobrazia v tarife';

ALTER TABLE tarifitem DROP CONSTRAINT tarifitem_supplier_fkey;
ALTER TABLE tarifitem RENAME COLUMN supplier TO tarifitemgroup;

GRANT SELECT, INSERT, UPDATE ON tarifitemgroup TO extranet;
GRANT USAGE ON tarifitemgroup_id_seq TO extranet;

INSERT INTO tarifitemgroup (id, name, sort) SELECT id, name, id AS sort FROM person WHERE id IN (SELECT DISTINCT tarifitemgroup FROM tarifitem);

ALTER TABLE tarifitem ADD CONSTRAINT tarifitem_tarifitemgroup_fkey FOREIGN KEY (tarifitemgroup) REFERENCES tarifitemgroup (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER INDEX tarifitem_supplier RENAME TO tarifitem_tarifitemgroup;

COMMIT;
