-- Extracts UK SIC 2007 (the ONS list), UK SIC 2003 and the ONS equivalence between them from the IDM database ("@SIC" schema)
-- into data/prepared/*.csv, in the shape tools/load_uk_sic.sql expects. Run it against a scratch clone, never production:
--
--    psql -d IDM -f tools/export_uk_sic.sql         (from the repo root; \copy writes on the machine psql runs on)
--
-- What the ONS list looks like, found by looking at it:
--   * 21 sections, 87 divisions (division 16 has no row of its own: it is only named in the "division" column), 272 groups;
--   * the 4-digit CLASSES have no row of their own either, only a 5-digit padded form ("0162" -> "01620"). For a class that is
--     subdivided the padded row (01620) is not a code of its own: the subclasses (01621, 01629) are the codes. For a class that
--     is not subdivided the padded row (01110) is its only subclass.
--   * the list has 74990 "Non-trading company", a Companies House code that is not in SIC 2007 itself: left out, with a warning;
--   * the list has two mistyped codes (45119 for 45112, 82922 for 82912): corrected below, with a warning;
--   * the equivalence table has stray trailing spaces in a few codes ('45112 ', '82912 '), which are trimmed here; with that
--     every 2007 code in it is a code of the list.
\set ON_ERROR_STOP on

CREATE TEMP TABLE ons AS SELECT sic, description, section, division, "group" AS grp, class FROM "@SIC".sic2007;

-- classes that are subdivided: some 5-digit code of the class is not the padded form
CREATE TEMP TABLE subdivided AS SELECT DISTINCT class FROM ons WHERE length(sic) = 5 AND sic <> class || '0';

CREATE TEMP TABLE uk2007 AS
   SELECT section AS code, 'section' AS level, '' AS parent, initcap(lower(description)) AS label FROM "@SIC"._sic2007_ons WHERE agg_level = 'SECTION'
 UNION ALL
   SELECT d.division, 'division', d.section, (SELECT o.description FROM ons o WHERE o.sic = d.division) FROM (SELECT DISTINCT division, section FROM ons WHERE length(sic) = 5) d
 UNION ALL
   SELECT g.grp, 'group', g.division, (SELECT o.description FROM ons o WHERE o.sic = g.grp) FROM (SELECT DISTINCT grp, division FROM ons WHERE length(sic) = 5) g
 UNION ALL
   SELECT c.class, 'class', c.grp, (SELECT o.description FROM ons o WHERE o.sic = c.class || '0' ) FROM (SELECT DISTINCT class, grp FROM ons WHERE length(sic) = 5) c
 UNION ALL
   SELECT o.sic, 'subclass', o.class, o.description FROM ons o
    WHERE length(o.sic) = 5 AND NOT (o.sic = o.class || '0' AND o.class IN (SELECT class FROM subdivided));

-- Two disagreements between the ONS list and the ONS equivalence table in the database, both typing slips in the list
-- (the list's own class column and the equivalence table agree with each other and with the title):
--   45119 "Sale of used cars and light motor vehicles", class 4511  is  45112 in the equivalence table;
--   82922 "Activities of credit bureaus", class 8291  is  82912 in the equivalence table.
-- The list is corrected to match, loudly. Each correction applies only if the data is exactly as described; if the data has
-- changed, nothing is corrected and the checks below stop the export.
DO $$ DECLARE n integer; BEGIN
   UPDATE uk2007 SET code = '45112' WHERE code = '45119' AND level = 'subclass' AND parent = '4511' AND label = 'Sale of used cars and light motor vehicles';
   GET DIAGNOSTICS n = ROW_COUNT;
   IF n = 1 THEN RAISE WARNING 'CORRECTION: ONS list code 45119 is 45112 in the equivalence table; used 45112'; END IF;
   UPDATE uk2007 SET code = '82912' WHERE code = '82922' AND level = 'subclass' AND parent = '8291' AND label = 'Activities of credit bureaus';
   GET DIAGNOSTICS n = ROW_COUNT;
   IF n = 1 THEN RAISE WARNING 'CORRECTION: ONS list code 82922 is 82912 in the equivalence table; used 82912'; END IF;
END $$;

-- 74990 "Non-trading company" (class 7499) is not part of the classification: it is in neither NACE Rev.2 nor the ONS
-- equivalence table, and the UK datahub file lists it with the other Companies House pseudo-codes (98000, 99999). It is left out.
DO $$ DECLARE n integer; BEGIN
   DELETE FROM uk2007 WHERE (code = '74990' AND level = 'subclass' AND label ILIKE 'Non-trading company%') OR (code = '7499' AND level = 'class');
   GET DIAGNOSTICS n = ROW_COUNT;
   IF n = 2 THEN RAISE WARNING 'LEFT OUT: 74990 Non-trading company (class 7499) is a Companies House code, not a SIC 2007 code'; END IF;
END $$;

-- every 2007 code of the equivalence table must be in the list; if not, stop (nothing is added silently)
DO $$ DECLARE n integer; BEGIN
   SELECT count(DISTINCT trim(e.sic2007)) INTO n FROM "@SIC".sic2003_sic2007_equivalence e WHERE trim(e.sic2007) NOT IN (SELECT code FROM uk2007);
   IF n > 0 THEN RAISE EXCEPTION '% code(s) of the 2003-2007 equivalence are not in the ONS list', n; END IF;
END $$;

\copy (SELECT code, level, parent, trim(label) AS label FROM uk2007 ORDER BY code COLLATE "C") TO 'data/prepared/uk_sic_2007.csv' (FORMAT csv, HEADER)

\copy (SELECT trim(sic2003) AS code, 'code' AS level, '' AS parent, trim(min(sic2003_desc)) AS label FROM "@SIC".sic2003_sic2007_equivalence GROUP BY trim(sic2003) ORDER BY trim(sic2003)) TO 'data/prepared/uk_sic_2003.csv' (FORMAT csv, HEADER)

\copy (SELECT DISTINCT trim(sic2003) AS from_code, trim(sic2007) AS to_code FROM "@SIC".sic2003_sic2007_equivalence ORDER BY 1, 2) TO 'data/prepared/uk_sic_2003_2007_pairs.csv' (FORMAT csv, HEADER)

SELECT level, count(*) FROM uk2007 GROUP BY level ORDER BY min(length(code));
SELECT count(*) AS sic2003_codes, count(DISTINCT sic2003_desc) AS distinct_titles FROM (SELECT DISTINCT trim(sic2003) AS sic2003, trim(sic2003_desc) AS sic2003_desc FROM "@SIC".sic2003_sic2007_equivalence) x;