-- delete all tax definitions
DELETE FROM tax_calculation_rule;
DELETE FROM tax_class;
DELETE FROM tax_calculation_rate;
DELETE FROM tax_calculation;

-- create  tax_class
INSERT INTO tax_class(class_name, class_type) VALUES
('Umsatzsteuerpfichtige Güter 19%','PRODUCT'),
('Umsatzsteuerpfichtige Güter 7%','PRODUCT'),
('Umsatzsteuerfreie Güter','PRODUCT'),
('inkl. Mehrwertsteuer','CUSTOMER');

-- update ids
UPDATE tax_class SET class_id = 19 where class_name = 'Umsatzsteuerpfichtige Güter 19%';
UPDATE tax_class SET class_id = 7 where class_name = 'Umsatzsteuerpfichtige Güter 7%';
UPDATE tax_class SET class_id = 0 where class_name = 'Umsatzsteuerfreie Güter';
UPDATE tax_class SET class_id = 1 where class_name = 'inkl. Mehrwertsteuer';

-- create tax_calculation_rate
INSERT INTO tax_calculation_rate(tax_country_id, tax_region_id, tax_postcode, code, rate, zip_is_range, zip_from, zip_to) VALUES
('DE', 0, '*', '19% Steuer', 19.00, NULL, NULL, NULL),
('DE', 0, '*', '7% Steuer', 7.00, NULL, NULL, NULL),
('DE', 0, '*', '0% Steuer', 0.00, NULL, NULL, NULL);


INSERT INTO tax_calculation_rule(code, priority, position) VALUES
('Produkte mit 19% Mwst.', 2, 0),
('Produkte mit 7% Mwst.', 1, 0),
('Umsatzsteuerfreie Güter', 0, 0);


--  create roule for 19%
INSERT INTO tax_calculation	
select (
	select tax_calculation_rate_id
	from tax_calculation_rate
	where code = '19% Steuer'
	and tax_country_id = 'DE'
) tax_calculation_rate_id, (
	select tax_calculation_rule_id
	from tax_calculation_rule
	where code = 'Produkte mit 19% Mwst.'
) tax_calculation_rule_id, (
	select class_id
	from tax_class
	where class_name = 'inkl. Mehrwertsteuer'
	and class_type = 'CUSTOMER'
) customer_tax_class_id, (
	select class_id 	 	
	from tax_class
	where class_name = 'Umsatzsteuerpfichtige Güter 19%'
	and class_type = 'PRODUCT'
) product_tax_class_id;


--  create roule for 7%
INSERT INTO tax_calculation	
select (
	select tax_calculation_rate_id
	from tax_calculation_rate
	where code = '7% Steuer'
	and tax_country_id = 'DE'
) tax_calculation_rate_id, (
	select tax_calculation_rule_id
	from tax_calculation_rule
	where code = 'Produkte mit 7% Mwst.'
) tax_calculation_rule_id, (
	select class_id
	from tax_class
	where class_name = 'inkl. Mehrwertsteuer'
	and class_type = 'CUSTOMER'
) customer_tax_class_id, (
	select class_id 	 	
	from tax_class
	where class_name = 'Umsatzsteuerpfichtige Güter 7%'
	and class_type = 'PRODUCT'
) product_tax_class_id;


--  create roule for 0%
INSERT INTO tax_calculation	
select (
	select tax_calculation_rate_id
	from tax_calculation_rate
	where code = '0% Steuer'
	and tax_country_id = 'DE'
) tax_calculation_rate_id, (
	select tax_calculation_rule_id
	from tax_calculation_rule
	where code = 'Umsatzsteuerfreie Güter'
) tax_calculation_rule_id, (
	select class_id
	from tax_class
	where class_name = 'inkl. Mehrwertsteuer'
	and class_type = 'CUSTOMER'
) customer_tax_class_id, (
	select class_id 	 	
	from tax_class
	where class_name = 'Umsatzsteuerfreie Güter'
	and class_type = 'PRODUCT'
) product_tax_class_id;

-- update ids (old)
--UPDATE tax_calculation_rule SET tax_calculation_rule_id = 19 where code = 'Produkte mit 19% Mwst.';
--UPDATE tax_calculation_rule SET tax_calculation_rule_id = 7 where code = 'Produkte mit 7% Mwst.';
--UPDATE tax_calculation_rule SET tax_calculation_rule_id = 0 where code = 'Umsatzsteuerfreie Güter';

-- view all tax roules
SELECT * from tax_class;

--SELECT tax_calculation_rule_id, code
--FROM tax_calculation_rule