Notes

-----------------------------------------------------------------------------------------------------
------------- 07/01/2019 - Remove datasource from financial report (forgot column drop) -------------
-----------------------------------------------------------------------------------------------------

ALTER TABLE financial_report
DROP COLUMN datasource_id;

--------------------------------------------------------
------------- 07/01/2019 - INSEE SIRENE V3 -------------
--------------------------------------------------------

INSERT INTO datasource (id,
provider_id,
code,
label,
file_name_expression,
last_discriminator,
discriminator_generator,
discriminator_expression,
charset,
first_relevant_row,
download_url)
SELECT nextval('datasource_id_seq'),
provider.id,
'SIRENE_V3',
'INSEE API - SIRENE V3',
'insee_api_sireneV3-(d{4}(?:-d{2}){2}).jsonl',
'2018-12-01',
'EVERY_DAY',
'yyyy-MM-dd',
'UTF-8',
1,
'https://api.insee.fr/entreprises/sirene/V3'
FROM provider
WHERE code = 'INSEE';

--------------------------------------------------------------------------------------
------------- 07/01/2019 - Remove roads with shitty labels like "------" -------------
--------------------------------------------------------------------------------------

UPDATE establishment
SET address_id = NULL
WHERE address_id IN (
SELECT address.id
FROM address
INNER JOIN road ON address.road_id = road.id
WHERE label SIMILAR TO '-+'
);

DELETE
FROM address
WHERE id IN (
SELECT address.id
FROM address
INNER JOIN road ON address.road_id = road.id
WHERE label SIMILAR TO '-+'
);

DELETE
FROM road
WHERE label SIMILAR TO '-+';

--------------------------------------------------------------------------------
------------- 07/01/2019 - Stop previous SIRENE datasource in 2018 -------------
--------------------------------------------------------------------------------

-- Delete existing files from 2019
DELETE
FROM datasource_file_row
WHERE datasource_file_id IN (
SELECT datasource_file.id
FROM datasource_file
INNER JOIN datasource ON datasource_file.datasource_id = datasource.id
WHERE datasource.code = 'SIRENE'
AND datasource_file.discriminator > '2018365'
);

DELETE
FROM datasource_file
WHERE id IN (
SELECT datasource_file.id
FROM datasource_file
INNER JOIN datasource ON datasource_file.datasource_id = datasource.id
WHERE datasource.code = 'SIRENE'
AND datasource_file.discriminator > '2018365'
);

-- Just to be sure
UPDATE datasource
SET last_discriminator = '2018365'
WHERE code = 'SIRENE'
AND last_discriminator > '2018365';

-------------------------------------------------------------------------------------------
------------- 11/01/2019 - Remove job codes (useless) + add job unit-enabling -------------
-------------------------------------------------------------------------------------------

ALTER TABLE job
DROP CONSTRAINT uc_job,
ADD CONSTRAINT uc_job UNIQUE (NAME),
DROP COLUMN code,
ADD COLUMN enabled BOOLEAN NOT NULL DEFAULT TRUE;

---------------------------------------------------------------
------------- 11/01/2019 - Just to be clear 🔫🔫🔫 -------------
---------------------------------------------------------------

UPDATE galex_parameter
SET description = 'Flag to check HS indexation status. /! TO NOT EDIT MANUALLY /!'
WHERE code = 'HS_INDEXATION_RUNNING';

-----------------------------------------------------------------
------------- 11/01/2019 - Disable SIRENEV3 for now -------------
-----------------------------------------------------------------

INSERT INTO job
(id, name, job_type, enabled)
VALUES (nextval('job_id_seq'), 'InseeSireneV3DownloadJob', 'Download', false),
(nextval('job_id_seq'), 'InseeSireneV3ImportJob', 'Import', false),
(nextval('job_id_seq'), 'InseeSireneV3NormalizeJob', 'Normalize', false);

------------------------------------------------------------------------------------
------------- 11/01/2019 - Company/Establishment upgrade for SIRENE V3 -------------
------------------------------------------------------------------------------------

-- Address
ALTER TABLE address
ADD COLUMN CEDEX_LABEL VARCHAR(255) NULL;

-- Range employees
ALTER TABLE range_employees
ADD COLUMN code VARCHAR(255) NULL,
ALTER COLUMN nb_employees_min DROP NOT NULL;

INSERT INTO range_employees(id, code, nb_employees_min, nb_employees_max)
VALUES (nextval('range_employees_id_seq'), 'NN', NULL, NULL);
UPDATE range_employees
SET code = '00'
WHERE nb_employees_min = 0
and nb_employees_max IS NULL;
UPDATE range_employees
SET code = '01'
WHERE nb_employees_min = 1
and nb_employees_max = 2;
UPDATE range_employees
SET code = '02'
WHERE nb_employees_min = 3
and nb_employees_max = 5;
UPDATE range_employees
SET code = '03'
WHERE nb_employees_min = 6
and nb_employees_max = 9;
UPDATE range_employees
SET code = '11'
WHERE nb_employees_min = 10
and nb_employees_max = 19;
UPDATE range_employees
SET code = '12'
WHERE nb_employees_min = 20
and nb_employees_max = 49;
UPDATE range_employees
SET code = '21'
WHERE nb_employees_min = 50
and nb_employees_max = 99;
UPDATE range_employees
SET code = '22'
WHERE nb_employees_min = 100
and nb_employees_max = 199;
UPDATE range_employees
SET code = '31'
WHERE nb_employees_min = 200
and nb_employees_max = 249;
UPDATE range_employees
SET code = '32'
WHERE nb_employees_min = 250
and nb_employees_max = 499;
UPDATE range_employees
SET code = '41'
WHERE nb_employees_min = 500
and nb_employees_max = 999;
UPDATE range_employees
SET code = '42'
WHERE nb_employees_min = 1000
and nb_employees_max = 1999;
UPDATE range_employees
SET code = '51'
WHERE nb_employees_min = 2000
and nb_employees_max = 4999;
UPDATE range_employees
SET code = '52'
WHERE nb_employees_min = 5000
and nb_employees_max = 9999;
UPDATE range_employees
SET code = '53'
WHERE nb_employees_min = 10000
and nb_employees_max IS NULL;
ALTER TABLE range_employees
ALTER COLUMN code SET NOT NULL;
ALTER TABLE range_employees
DROP CONSTRAINT uc_range_employees,
ADD CONSTRAINT uc_range_employees UNIQUE (code);

-- Company
ALTER TABLE company
ALTER COLUMN "name" DROP NOT NULL,
ADD COLUMN name_common VARCHAR(255) NULL,
ADD COLUMN acronym VARCHAR(255) NULL,
ADD COLUMN denomination VARCHAR(255) NULL,
ADD COLUMN denomination_common VARCHAR(255) NULL,
ADD COLUMN range_employees_id BIGINT NULL,
ADD COLUMN status_is_employing BOOLEAN NULL,
ADD COLUMN status_is_ess BOOLEAN NULL,
ADD COLUMN association_identifier VARCHAR(255) NULL;

ALTER TABLE company
DROP CONSTRAINT fk_company_4,
ADD CONSTRAINT fk_company_8 FOREIGN KEY (company_legal_type_id) REFERENCES company_legal_type,
DROP CONSTRAINT fk_company_5,
ADD CONSTRAINT fk_company_9 FOREIGN KEY (company_category_id) REFERENCES company_category,
ADD CONSTRAINT fk_company_10 FOREIGN KEY (range_employees_id) REFERENCES range_employees;

CREATE INDEX fk_company_8 ON company (company_legal_type_id);
CREATE INDEX fk_company_9 ON company (company_category_id);
CREATE INDEX fk_company_10 ON company (range_employees_id);

-- Establishment
ALTER TABLE establishment
ADD COLUMN sign VARCHAR(255) NULL,
ADD COLUMN denomination_common VARCHAR(255) NULL,
ADD COLUMN address_second_id BIGINT NULL,
ADD COLUMN range_employees_id BIGINT NULL,
ADD COLUMN status_is_employing BOOLEAN NULL,
ADD COLUMN creation_date TIMESTAMP WITHOUT TIME ZONE NULL;

ALTER TABLE establishment
DROP CONSTRAINT fk_establishment_6,
ADD CONSTRAINT fk_establishment_8 FOREIGN KEY (address_id) REFERENCES address,
ADD CONSTRAINT fk_establishment_9 FOREIGN KEY (address_second_id) REFERENCES address,
ADD CONSTRAINT fk_establishment_10 FOREIGN KEY (range_employees_id) REFERENCES range_employees;

CREATE INDEX fk_establishment_8 ON establishment (address_id);
CREATE INDEX fk_establishment_9 ON establishment (address_second_id);
CREATE INDEX fk_establishment_10 ON establishment (range_employees_id);

-- CompanyInfo/EstablishmentInfo
DELETE
FROM company_info USING (
SELECT company_id, range_employees_id, MAX(date_datasource_update) AS date_datasource_update
FROM company_info
GROUP BY company_id, range_employees_id
) AS last_company_info
WHERE company_info.company_id = last_company_info.company_id
AND company_info.date_datasource_update < last_company_info.date_datasource_update;

UPDATE company
SET range_employees_id = company_info.range_employees_id
FROM company_info
WHERE company.id = company_info.company_id;

DROP TABLE company_info;

DELETE
FROM establishment_info USING (
SELECT establishment_id, range_employees_id, MAX(date_datasource_update) AS date_datasource_update
FROM establishment_info
GROUP BY establishment_id, range_employees_id
) AS last_establishment_info
WHERE establishment_info.establishment_id = last_establishment_info.establishment_id
AND establishment_info.date_datasource_update < last_establishment_info.date_datasource_update;

UPDATE establishment
SET range_employees_id = establishment_info.range_employees_id
FROM establishment_info
WHERE establishment.id = establishment_info.establishment_id;

DROP TABLE establishment_info;

-- Company legal type
INSERT INTO company_legal_type(id, code, label)
VALUES (nextval('company_legal_type_id_seq'), '1000', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '4130', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '5430', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '5625', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '5631', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '6588', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7150', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7190', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7314', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7341', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7342', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7347', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7349', 'TO UPDATE'),
(nextval('company_legal_type_id_seq'), '7365', 'TO UPDATE');

UPDATE company_legal_type
SET label='Entrepreneur individuel' WHERE code='1000';
UPDATE company_legal_type
SET label='Indivision entre personnes physiques' WHERE code='2110';
UPDATE company_legal_type
SET label='Indivision avec personne morale' WHERE code='2120';
UPDATE company_legal_type
SET label='Société créée de fait entre personnes physiques' WHERE code='2210';
UPDATE company_legal_type
SET label='Société créée de fait avec personne morale' WHERE code='2220';
UPDATE company_legal_type
SET label='Société en participation entre personnes physiques' WHERE code='2310';
UPDATE company_legal_type
SET label='Société en participation avec personne morale' WHERE code='2320';
UPDATE company_legal_type
SET label='Société en participation de professions libérales' WHERE code='2385';
UPDATE company_legal_type
SET label='Fiducie' WHERE code='2400';
UPDATE company_legal_type
SET label='Paroisse hors zone concordataire' WHERE code='2700';
UPDATE company_legal_type
SET label='Autre groupement de droit privé non doté de la personnalité morale' WHERE code='2900';
UPDATE company_legal_type
SET label='Représentation ou agence commerciale d''état ou organisme public étranger immatriculé au RCS' WHERE code='3110';
UPDATE company_legal_type
SET label='Société commerciale étrangère immatriculée au RCS' WHERE code='3120';
UPDATE company_legal_type
SET label='Organisation internationale' WHERE code='3205';
UPDATE company_legal_type
SET label='État, collectivité ou établissement public étranger' WHERE code='3210';
UPDATE company_legal_type
SET label='Société étrangère non immatriculée au RCS' WHERE code='3220';
UPDATE company_legal_type
SET label='Autre personne morale de droit étranger' WHERE code='3290';
UPDATE company_legal_type
SET label='Établissement public national à caractère industriel ou commercial doté d''un comptable public' WHERE code='4110';
UPDATE company_legal_type
SET label='Établissement public national à caractère industriel ou commercial non doté d''un comptable public' WHERE code='4120';
UPDATE company_legal_type
SET label='Exploitant public' WHERE code='4130';
UPDATE company_legal_type
SET label='Établissement public local à caractère industriel ou commercial' WHERE code='4140';
UPDATE company_legal_type
SET label='Régie d''une collectivité locale à caractère industriel ou commercial' WHERE code='4150';
UPDATE company_legal_type
SET label='Institution Banque de France' WHERE code='4160';
UPDATE company_legal_type
SET label='Société de caution mutuelle' WHERE code='5191';
UPDATE company_legal_type
SET label='Société coopérative de banque populaire' WHERE code='5192';
UPDATE company_legal_type
SET label='Caisse de crédit maritime mutuel' WHERE code='5193';
UPDATE company_legal_type
SET label='Caisse (fédérale) de crédit mutuel' WHERE code='5194';
UPDATE company_legal_type
SET label='Association coopérative inscrite (droit local Alsace Moselle)' WHERE code='5195';
UPDATE company_legal_type
SET label='Caisse d''épargne et de prévoyance à forme coopérative' WHERE code='5196';
UPDATE company_legal_type
SET label='Société en nom collectif' WHERE code='5202';
UPDATE company_legal_type
SET label='Société en nom collectif coopérative' WHERE code='5203';
UPDATE company_legal_type
SET label='Société en commandite simple' WHERE code='5306';
UPDATE company_legal_type
SET label='Société en commandite simple coopérative' WHERE code='5307';
UPDATE company_legal_type
SET label='Société en commandite par actions' WHERE code='5308';
UPDATE company_legal_type
SET label='Société en commandite par actions coopérative' WHERE code='5309';
UPDATE company_legal_type
SET label='Société de Participations Financières de Profession Libérale Société en commandite par actions (SPFPL SCA)' WHERE code='5370';
UPDATE company_legal_type
SET label='Société d''exercice libéral en commandite par actions' WHERE code='5385';
UPDATE company_legal_type
SET label='SARL nationale' WHERE code='5410';
UPDATE company_legal_type
SET label='SARL d''économie mixte' WHERE code='5415';
UPDATE company_legal_type
SET label='SARL immobilière pour le commerce et l''industrie (SICOMI)' WHERE code='5422';
UPDATE company_legal_type
SET label='SARL immobilière de gestion' WHERE code='5426';
UPDATE company_legal_type
SET label='SARL d''aménagement foncier et d''équipement rural (SAFER)' WHERE code='5430';
UPDATE company_legal_type
SET label='SARL mixte d''intérêt agricole (SMIA)' WHERE code='5431';
UPDATE company_legal_type
SET label='SARL d''intérêt collectif agricole (SICA)' WHERE code='5432';
UPDATE company_legal_type
SET label='SARL d''attribution' WHERE code='5442';
UPDATE company_legal_type
SET label='SARL coopérative de construction' WHERE code='5443';
UPDATE company_legal_type
SET label='SARL coopérative de consommation' WHERE code='5451';
UPDATE company_legal_type
SET label='SARL coopérative artisanale' WHERE code='5453';
UPDATE company_legal_type
SET label='SARL coopérative d''intérêt maritime' WHERE code='5454';
UPDATE company_legal_type
SET label='SARL coopérative de transport' WHERE code='5455';
UPDATE company_legal_type
SET label='SARL coopérative ouvrière de production (SCOP)' WHERE code='5458';
UPDATE company_legal_type
SET label='SARL union de sociétés coopératives' WHERE code='5459';
UPDATE company_legal_type
SET label='Autre SARL coopérative' WHERE code='5460';
UPDATE company_legal_type
SET label='Société de Participations Financières de Profession Libérale Société à responsabilité limitée (SPFPL SARL)' WHERE code='5470';
UPDATE company_legal_type
SET label='Société d''exercice libéral à responsabilité limitée' WHERE code='5485';
UPDATE company_legal_type
SET label='SARL unipersonnelle' WHERE code='5498';
UPDATE company_legal_type
SET label='Société à responsabilité limitée (sans autre indication)' WHERE code='5499';
UPDATE company_legal_type
SET label='SA d''économie mixte à conseil d''administration' WHERE code='5515';
UPDATE company_legal_type
SET label='Fonds à forme sociétale à conseil d''administration' WHERE code='5520';
UPDATE company_legal_type
SET label='SA immobilière pour le commerce et l''industrie (SICOMI) à conseil d''administration' WHERE code='5522';
UPDATE company_legal_type
SET label='SA immobilière d''investissement à conseil d''administration' WHERE code='5525';
UPDATE company_legal_type
SET label='Société anonyme mixte d''intérêt agricole (SMIA) à conseil d''administration' WHERE code='5531';
UPDATE company_legal_type
SET label='SA d''intérêt collectif agricole (SICA) à conseil d''administration' WHERE code='5532';
UPDATE company_legal_type
SET label='SA d''attribution à conseil d''administration' WHERE code='5542';
UPDATE company_legal_type
SET label='SA coopérative de construction à conseil d''administration' WHERE code='5543';
UPDATE company_legal_type
SET label='SA de HLM à conseil d''administration' WHERE code='5546';
UPDATE company_legal_type
SET label='SA coopérative de production de HLM à conseil d''administration' WHERE code='5547';