CREATE TABLE enapo_ppas_distribution_installation (
	invoice_id varchar,
	customer_id varchar,
	place_id varchar,
	eic varchar NULL,
	billing_class varchar NULL,
	measurement_type varchar NULL,
	contract_contract_id varchar NULL,
	contract_move_in_date date NULL,
	contract_move_out_date date NULL,
	tdd_class varchar NULL,
	address_street varchar NULL,
	address_house_number varchar NULL,
	address_house_org_number varchar NULL,
	address_city varchar NULL,
	address_city_part varchar NULL,
	address_post_code varchar NULL,
	address_country varchar NULL,
	address_ruian_id int8 NULL,
	created_at timestamptz DEFAULT now() NULL,
	updated_at timestamptz NULL,
	CONSTRAINT enapo_ppas_distribution_installation_pk PRIMARY KEY (invoice_id, customer_id, place_id)
);

CREATE INDEX idx_ppas_distribution_installation_eic ON enapo_ppas_distribution_installation(eic);
CREATE INDEX idx_ppas_distribution_installation_place_id ON enapo_ppas_distribution_installation(place_id);

-- Move the already stored installations out of the invoice header, its still necessary to download history because there can be more than one installation per invoice
INSERT INTO enapo_ppas_distribution_installation (
	invoice_id, customer_id, place_id, eic, billing_class, measurement_type,
	contract_contract_id, contract_move_in_date, contract_move_out_date, tdd_class,
	address_street, address_house_number, address_house_org_number, address_city,
	address_city_part, address_post_code, address_country, address_ruian_id
)
SELECT
	id, customer_id, place_id, eic, installations_billing_class, installations_measurement_type,
	installations_contract_contract_id, installations_contract_move_in_date, installations_contract_move_out_date,
	installations_tdd_class, installations_address_street, installations_address_house_number,
	installations_address_house_org_number, installations_address_city, installations_address_city_part,
	installations_address_post_code, installations_address_country, installations_address_ruian_id
FROM enapo_ppas_distribution_invoice
WHERE place_id IS NOT NULL
ON CONFLICT (invoice_id, customer_id, place_id) DO NOTHING;

ALTER TABLE enapo_ppas_distribution_invoice_device ADD COLUMN place_id varchar NULL;
ALTER TABLE enapo_ppas_distribution_invoice_price ADD COLUMN place_id varchar NULL;

-- Existing rows were written when only installations[0] was stored
UPDATE enapo_ppas_distribution_invoice_device d
SET place_id = s.place_id
FROM (
	SELECT invoice_id, min(place_id) AS place_id
	FROM enapo_ppas_distribution_installation
	GROUP BY invoice_id
) s
WHERE d.invoice_id = s.invoice_id;

UPDATE enapo_ppas_distribution_invoice_price p
SET place_id = s.place_id
FROM (
	SELECT invoice_id, customer_id, min(place_id) AS place_id
	FROM enapo_ppas_distribution_installation
	GROUP BY invoice_id, customer_id
) s
WHERE p.invoice_id = s.invoice_id AND p.customer_id = s.customer_id;

-- Rows whose invoice never reached the installation table cannot be attributed to a consumption point.
-- Deleting them here would be silent data loss, so the migration stops instead and leaves the call to us.
DO $$
DECLARE
	orphan_devices bigint;
	orphan_prices bigint;
BEGIN
	SELECT count(*) INTO orphan_devices FROM enapo_ppas_distribution_invoice_device WHERE place_id IS NULL;
	SELECT count(*) INTO orphan_prices FROM enapo_ppas_distribution_invoice_price WHERE place_id IS NULL;

	IF orphan_devices > 0 OR orphan_prices > 0 THEN
		RAISE EXCEPTION
			'Cannot scope distribution rows by place_id: % device and % price rows belong to invoices missing from enapo_ppas_distribution_installation. Resolve or remove them, then re-run.',
			orphan_devices, orphan_prices;
	END IF;
END $$;

ALTER TABLE enapo_ppas_distribution_invoice_device
	ALTER COLUMN place_id SET NOT NULL,
	DROP CONSTRAINT enapo_ppas_distribution_invoice_device_pk,
	ADD CONSTRAINT enapo_ppas_distribution_invoice_device_pk
		PRIMARY KEY (device_serial_number, invoice_id, place_id, date_from, date_to, reading_type, kind);

ALTER TABLE enapo_ppas_distribution_invoice_price
	ALTER COLUMN place_id SET NOT NULL,
	DROP CONSTRAINT enapo_ppas_distribution_invoice_price_pk,
	ADD CONSTRAINT enapo_ppas_distribution_invoice_price_pk
		PRIMARY KEY (invoice_id, customer_id, place_id, date_from, date_to, kind, price_type);

CREATE INDEX idx_ppas_distribution_invoice_device_place_id ON enapo_ppas_distribution_invoice_device(place_id);
CREATE INDEX idx_ppas_distribution_invoice_price_place_id ON enapo_ppas_distribution_invoice_price(place_id);

-- timeSlot is a fraction of the billed period (e.g. 0.19355 = 6 of 31 days), not an int
ALTER TABLE enapo_ppas_distribution_invoice_price
	ALTER COLUMN time_slot TYPE numeric;

DROP INDEX IF EXISTS idx_ppas_invoice_eic;
DROP INDEX IF EXISTS idx_ppas_invoice_place_id;



ALTER TABLE enapo_ppas_distribution_invoice
	DROP COLUMN eic,
	DROP COLUMN place_id,
	DROP COLUMN installations_billing_class,
	DROP COLUMN installations_measurement_type,
	DROP COLUMN installations_contract_contract_id,
	DROP COLUMN installations_contract_move_in_date,
	DROP COLUMN installations_contract_move_out_date,
	DROP COLUMN installations_tdd_class,
	DROP COLUMN installations_address_street,
	DROP COLUMN installations_address_house_number,
	DROP COLUMN installations_address_house_org_number,
	DROP COLUMN installations_address_city,
	DROP COLUMN installations_address_city_part,
	DROP COLUMN installations_address_post_code,
	DROP COLUMN installations_address_country,
	DROP COLUMN installations_address_ruian_id;
