SeaTec Table API Reference

SELECT 'grp-' + CAST(g.GroupCompanyId AS varchar(20))    AS source_id,
       COALESCE(g.ModifiedDate, g.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       g.GroupCompanyName                                 AS name,
       'group'                                            AS kind,
       NULL                                               AS parent_source_id,
       NULL                                               AS external_ref,
       NULL                                               AS country,
       CASE WHEN ISNULL(g.Active, 1) = 1 THEN 1 ELSE 0 END AS is_active
FROM   dbo.GroupCompany g

UNION ALL

SELECT 'cst-' + CAST(c.CstId AS varchar(20)),
       COALESCE(c.modDate, c.crDate),
       CAST(0 AS bit),
       c.CstCompany,
       'manager',
       CASE WHEN c.GroupCompanyId IS NULL THEN NULL
            ELSE 'grp-' + CAST(c.GroupCompanyId AS varchar(20)) END,
       c.CustRefNo,
       co.CountryCode,
       CASE WHEN ISNULL(c.Active, 1) = 1 THEN 1 ELSE 0 END
FROM   dbo.Customer c
LEFT   JOIN dbo.CBM_Country co ON co.conId = c.conId

UNION ALL

SELECT 'own-' + CAST(o.OwnerId AS varchar(20)),
       COALESCE(o.modDate, o.crDate),
       CAST(0 AS bit),
       o.OwnerNm, 'owner', NULL, o.VatNo, co.CountryCode, 1
FROM   dbo.CBM_Owner o
LEFT   JOIN dbo.CBM_Country co ON co.conId = o.conId;

SELECT 'flt-' + CAST(f.FleetID AS varchar(20))          AS source_id,
       COALESCE(f.modDate, f.crDate)                      AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'cst-' + CAST(f.fleetCustomer AS varchar(20))      AS organisation_source_id,
       f.fleetName                                        AS name
FROM   dbo.CBM_Fleet f;

SELECT 'ship-' + CAST(s.ShipId AS varchar(20))           AS source_id,
       COALESCE(s.modDate, s.crDate)                      AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       CASE WHEN s.ShipFleet IS NULL THEN NULL
            ELSE 'flt-' + CAST(s.ShipFleet AS varchar(20)) END  AS fleet_source_id,
       'cst-' + CAST(s.ShipCompany AS varchar(20))        AS organisation_source_id,
       CASE WHEN s.OwnerId IS NULL THEN NULL
            ELSE 'own-' + CAST(s.OwnerId AS varchar(20)) END    AS owner_source_id,
       s.ShipName                                         AS name,
       s.ShipIMOno                                        AS imo_no,
       t.Typeofshipname                                   AS ship_type,
       TRY_CAST(s.grosstonnage AS decimal(18,2))          AS gross_tonnage,
       TRY_CAST(s.yearofbuild AS int)                     AS year_built,
       CASE WHEN ISNULL(s.IsDisabled, 0) = 1 THEN 0 ELSE 1 END  AS is_active
FROM   dbo.Ship s
LEFT   JOIN dbo.Typeofship t ON t.Typeofshipid = s.typeofshipid;

SELECT 'unit-' + CAST(sm.shipModId AS varchar(20))       AS source_id,
       CASE WHEN sm.LastSampleDate > sm.crDate
            THEN sm.LastSampleDate ELSE sm.crDate END      AS source_modified_at,
       CAST(0 AS bit)                                      AS source_deleted,
       'ship-' + CAST(sm.shipId AS varchar(20))            AS ship_source_id,
       m.macName                                           AS name,
       'lube_oil'                                          AS stream,
       ot.ShortDesc                                        AS application,
       man.manName                                         AS make,
       mo.modNo                                            AS model,
       sm.SrNo                                             AS serial_no,
       og.oilGrade                                         AS oil_grade,
       ob.obrandName                                       AS oil_brand,
       TRY_CAST(sm.OilVol AS decimal(18,2))                AS charge_volume_l,
       sm.Schedule                                         AS sampling_interval_days,
       sm.LastSampleDate                                   AS last_sampled_at,
       CASE WHEN ISNULL(sm.IsCritical, 0) = 1 THEN 1 ELSE 0 END AS is_critical,
       sm.SeaTecMachineryId                                AS external_ref,
       CASE WHEN ISNULL(sm.IsDisabled, 0) = 1 THEN 0 ELSE 1 END AS is_active
FROM   dbo.ShipModel sm
JOIN   dbo.Machinery m    ON m.macId    = sm.macId
LEFT   JOIN dbo.Model mo  ON mo.modId   = sm.modId
LEFT   JOIN dbo.Manufacturer man ON man.manId = mo.manId
LEFT   JOIN dbo.OilType ot ON ot.otypeId = m.otypeId
LEFT   JOIN dbo.OilGrade og ON og.oilId  = sm.OilGradeId
LEFT   JOIN dbo.OilBrand ob ON ob.obrandId = og.obrandId;

SELECT 'st-lo-' + CAST(t.TestTypeId AS varchar(20)) AS source_id,
       GETUTCDATE()                                   AS source_modified_at,
       CAST(0 AS bit)                                 AS source_deleted,
       'lube_oil'                                     AS stream,
       t.TestType                                     AS name,
       NULL                                           AS short_code
FROM   dbo.LOTestType t

UNION ALL

SELECT 'st-fo-' + CAST(o.OilTypeId AS varchar(20)),
       COALESCE(o.ModifiedDate, o.CreatedDate),
       CAST(0 AS bit), 'fuel_oil', o.OilTypeName, NULL
FROM   dbo.FO_OilType o

UNION ALL

SELECT 'st-wa-' + CAST(w.WaterTypeId AS varchar(20)),
       COALESCE(w.ModifiedDate, w.CreatedDate),
       CAST(0 AS bit), 'water', w.WaterTypeName, w.WaterShortCode
FROM   dbo.WA_WaterType w;

SELECT 'p-fo-' + CAST(p.ParameterId AS varchar(20))  AS source_id,
       COALESCE(p.ModifiedDate, p.CreatedDate)        AS source_modified_at,
       CAST(0 AS bit)                                 AS source_deleted,
       'fuel_oil'                                     AS stream,
       p.PName                                        AS name,
       p.PShortName                                   AS short_name,
       'fo.' + p.PCSVName                             AS code,
       p.PUnit                                        AS unit,
       'number'                                       AS data_type,
       me.MethodName                                  AS method,
       b.BlockTitle                                   AS report_block,
       p.SequenceNo                                   AS sequence_no,
       CASE WHEN ISNULL(p.Active, 1) = 1 THEN 1 ELSE 0 END AS is_active
FROM   dbo.FO_Parameters p
LEFT   JOIN dbo.FO_Method me ON me.MethodId = p.PMethod
LEFT   JOIN dbo.FO_Block b   ON b.BlockId   = p.BlockId

UNION ALL

SELECT 'p-wa-' + CAST(p.ParameterId AS varchar(20)),
       COALESCE(p.ModifiedDate, p.CreatedDate),
       CAST(0 AS bit), 'water',
       p.ParamterName, p.ShortName,
       'wa.' + p.CsvHeaderName,
       p.Unit, 'number',
       me.MethodName, b.BlockName, p.SequenceNo,
       CASE WHEN ISNULL(p.IsActive, 1) = 1 THEN 1 ELSE 0 END
FROM   dbo.WA_Parameter p
LEFT   JOIN dbo.WA_Method me ON me.MethodId = p.MethodId
LEFT   JOIN dbo.WA_Block b   ON b.BlockId   = p.BlockId;

-- The same (code, column) pairs the pivot uses. Load once.
-- Unit and short name are picked up from Norms where the name matches;
-- fill the rest in by hand, once.

WITH lo(code, legacy_column, report_block) AS (
  SELECT * FROM (VALUES
    ('appearance', 'Appearance', 'Condition'),
    ('vis40', 'Vis40', 'Condition'),
    ('vis100', 'Vischk00', 'Condition'),
    ('vis_index', 'VisIndex', 'Condition'),
    ('flash_point', 'FlashPoint', 'Condition'),
    ('water_pct', 'Water', 'Condition'),
    ('water_kf_ppm', 'WaterKFppm', 'Condition'),
    ('emulsified_water', 'EmulsifiedWater', 'Condition'),
    ('tbn', 'BaseNumber', 'Condition'),
    ('tan', 'AcidNo', 'Condition'),
    ('soot_insolubles', 'SootInsolubles', 'Condition'),
    ('pentane_insolubles', 'PentaneInsolubles', 'Condition'),
    ('sulphated_ash', 'SulphatedAsh', 'Condition'),
    ('oxidation', 'Oxidation', 'Condition'),
    ('oxidation_abs', 'OxidationAbsolute', 'Condition'),
    ('oxidation_products', 'OxidationProducts', 'Condition'),
    ('nitration_abs', 'NitrationAbsolute', 'Condition'),
    ('sulphation_abs', 'SulphationAbsolute', 'Condition'),
    ('glycol', 'GLYCOL', 'Condition'),
    ('fuel_dilution', 'FUELDILUTION', 'Condition'),
    ('asphaltenes', 'Asphaltenes', 'Condition'),
    ('chlorine', 'Chlorine', 'Condition'),
    ('ph', 'pH', 'Condition'),
    ('specific_gravity', 'SpecificGravity', 'Condition'),
    ('conductivity', 'Conductivity', 'Condition'),
    ('liquid_corrosion', 'LiquidCorrosionConcentration', 'Condition'),
    ('vapour_corrosion', 'VapourPhaseCorrosion', 'Condition'),
    ('rocking_test', 'RockingTest', 'Condition'),
    ('sulphur', 'Sulpher', 'Condition'),
    ('fuel_p', 'FuelP', 'Condition'),
    ('oil_condition_index', 'OILCONDITIONINDEX', 'Condition'),
    ('water_scavenger', 'WaterScavenger', 'Condition'),
    ('calcium', 'Calcium', 'Additive elements'),
    ('magnesium', 'Magnesium', 'Additive elements'),
    ('zinc', 'Zinc', 'Additive elements'),
    ('phosphorus', 'Phosphorus', 'Additive elements'),
    ('barium', 'Barium', 'Additive elements'),
    ('boron', 'Boron', 'Additive elements'),
    ('iron', 'Iron', 'Wear metals'),
    ('copper', 'Copper', 'Wear metals'),
    ('lead', 'Lead', 'Wear metals'),
    ('tin', 'Tin', 'Wear metals'),
    ('chromium', 'Chromium', 'Wear metals'),
    ('aluminium', 'Aluminium', 'Wear metals'),
    ('nickel', 'Nickel', 'Wear metals'),
    ('titanium', 'Titanium', 'Wear metals'),
    ('silver', 'Silver', 'Wear metals'),
    ('manganese', 'Manganese', 'Wear metals'),
    ('vanadium', 'Vanadium', 'Wear metals'),
    ('molybdenum', 'Molybdenum', 'Wear metals'),
    ('silicon', 'Silicon', 'Contaminants'),
    ('sodium', 'Sodium', 'Contaminants'),
    ('lithium', 'Lithium', 'Contaminants'),
    ('pq_index', 'PQIndex', 'Ferrous debris and cleanliness'),
    ('ferrous_debris', 'FERROUSDEBRIS', 'Ferrous debris and cleanliness'),
    ('iso4406', 'ISOCODE', 'Ferrous debris and cleanliness'),
    ('iso_4um', '4um', 'Ferrous debris and cleanliness'),
    ('iso_6um', '6um', 'Ferrous debris and cleanliness'),
    ('iso_14um', '14um', 'Ferrous debris and cleanliness'),
    ('iso4407', 'ISOCODE4407', 'Ferrous debris and cleanliness'),
    ('iso4407_4um', '4um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_6um', '6um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_14um', '14um4407', 'Ferrous debris and cleanliness'),
    ('nas1638', 'NAS1638Class', 'Ferrous debris and cleanliness'),
    ('pc_4um', 'ParticleCount4', 'Ferrous debris and cleanliness'),
    ('pc_5um', 'ParticleCount5', 'Ferrous debris and cleanliness'),
    ('pc_6um', 'ParticleCount6', 'Ferrous debris and cleanliness'),
    ('pc_7um', 'ParticleCount7', 'Ferrous debris and cleanliness'),
    ('pc_10um', 'ParticleCount10', 'Ferrous debris and cleanliness'),
    ('pc_14um', 'ParticleCount14', 'Ferrous debris and cleanliness'),
    ('pc_20um', 'ParticleCount20', 'Ferrous debris and cleanliness'),
    ('pc_30um', 'ParticleCount30', 'Ferrous debris and cleanliness'),
    ('pc_5_15um', 'ParticleCount5-15um', 'Ferrous debris and cleanliness'),
    ('pc_15_25um', 'ParticleCount15-25um', 'Ferrous debris and cleanliness'),
    ('pc_25_50um', 'ParticleCount25-50um', 'Ferrous debris and cleanliness'),
    ('pc_50_100um', 'ParticleCount50-100um', 'Ferrous debris and cleanliness'),
    ('pc_gt_100um', 'ParticleCountGreater100um', 'Ferrous debris and cleanliness'),
    ('engine_hours', 'EngineHours', 'Operating context'),
    ('liner_hours', 'LinerHours', 'Operating context'),
    ('crown_hours', 'CrownHours', 'Operating context'),
    ('piston_ring_hours', 'PistonRingHours', 'Operating context'),
    ('feed_rate', 'FeedRate', 'Operating context'),
    ('fuel_sulphur_pct', 'FuelSulphurlevel', 'Operating context'),
    ('engine_load', 'EngineLoad', 'Operating context'),
    ('engine_rpm', 'EngineRpm', 'Operating context'),
    ('scav_temp', 'ScavTemp', 'Operating context'),
    ('scav_press', 'ScavPress', 'Operating context'),
    ('jcw_temp_in', 'JCWTmpIn', 'Operating context'),
    ('jcw_temp_out', 'JCWTmpOut', 'Operating context'),
    ('tc_rpm', 'TCRpm', 'Operating context'),
    ('sea_temp', 'SeaTemperature', 'Operating context'),
    ('ambient_temp', 'AmbientTemp', 'Operating context'),
    ('ambient_humidity', 'AmbientHumidity', 'Operating context'),
    ('mcr', 'MCR', 'Operating context'),
    ('sloc', 'SLOC', 'Operating context'),
    ('tc_cut_out', 'TcCutOut', 'Operating context'),
    ('fuel_valves_hr', 'FuelValvesHr', 'Operating context'),
    ('stroke_42', 'Stroke42', 'Operating context'),
    ('last_filter_change_hrs', 'LastFilterChangeHrs', 'Operating context'),
    ('whr_bypass', 'WHRByPass', 'Operating context')
  ) AS v(code, legacy_column, report_block)
)
SELECT 'p-lo-' + lo.code                          AS source_id,
       GETUTCDATE()                               AS source_modified_at,
       CAST(0 AS bit)                             AS source_deleted,
       'lube_oil'                                 AS stream,
       COALESCE(n.normName, lo.legacy_column)     AS name,
       n.ShortName                                AS short_name,
       'lo.' + lo.code                            AS code,
       COALESCE(n.UnitToDisplay, n.Unit)          AS unit,
       'number'                                   AS data_type,
       NULL                                       AS method,
       lo.report_block                            AS report_block,
       ROW_NUMBER() OVER (ORDER BY lo.report_block, lo.code) AS sequence_no,
       1                                          AS is_active
FROM   lo
LEFT   JOIN dbo.Norms n
       ON  REPLACE(n.normName, ' ', '') = lo.legacy_column
       OR  REPLACE(n.ShortName, ' ', '') = lo.legacy_column;

-- One row per lube-oil code with its legacy column and, where the
-- name matched, the Norms row. The limit queries and the optional
-- ColourCode join read this. Build it once, hand-fix the rows where
-- normId came back null, and keep it.

WITH lo(code, legacy_column, report_block) AS (
  SELECT * FROM (VALUES
    ('appearance', 'Appearance', 'Condition'),
    ('vis40', 'Vis40', 'Condition'),
    ('vis100', 'Vischk00', 'Condition'),
    ('vis_index', 'VisIndex', 'Condition'),
    ('flash_point', 'FlashPoint', 'Condition'),
    ('water_pct', 'Water', 'Condition'),
    ('water_kf_ppm', 'WaterKFppm', 'Condition'),
    ('emulsified_water', 'EmulsifiedWater', 'Condition'),
    ('tbn', 'BaseNumber', 'Condition'),
    ('tan', 'AcidNo', 'Condition'),
    ('soot_insolubles', 'SootInsolubles', 'Condition'),
    ('pentane_insolubles', 'PentaneInsolubles', 'Condition'),
    ('sulphated_ash', 'SulphatedAsh', 'Condition'),
    ('oxidation', 'Oxidation', 'Condition'),
    ('oxidation_abs', 'OxidationAbsolute', 'Condition'),
    ('oxidation_products', 'OxidationProducts', 'Condition'),
    ('nitration_abs', 'NitrationAbsolute', 'Condition'),
    ('sulphation_abs', 'SulphationAbsolute', 'Condition'),
    ('glycol', 'GLYCOL', 'Condition'),
    ('fuel_dilution', 'FUELDILUTION', 'Condition'),
    ('asphaltenes', 'Asphaltenes', 'Condition'),
    ('chlorine', 'Chlorine', 'Condition'),
    ('ph', 'pH', 'Condition'),
    ('specific_gravity', 'SpecificGravity', 'Condition'),
    ('conductivity', 'Conductivity', 'Condition'),
    ('liquid_corrosion', 'LiquidCorrosionConcentration', 'Condition'),
    ('vapour_corrosion', 'VapourPhaseCorrosion', 'Condition'),
    ('rocking_test', 'RockingTest', 'Condition'),
    ('sulphur', 'Sulpher', 'Condition'),
    ('fuel_p', 'FuelP', 'Condition'),
    ('oil_condition_index', 'OILCONDITIONINDEX', 'Condition'),
    ('water_scavenger', 'WaterScavenger', 'Condition'),
    ('calcium', 'Calcium', 'Additive elements'),
    ('magnesium', 'Magnesium', 'Additive elements'),
    ('zinc', 'Zinc', 'Additive elements'),
    ('phosphorus', 'Phosphorus', 'Additive elements'),
    ('barium', 'Barium', 'Additive elements'),
    ('boron', 'Boron', 'Additive elements'),
    ('iron', 'Iron', 'Wear metals'),
    ('copper', 'Copper', 'Wear metals'),
    ('lead', 'Lead', 'Wear metals'),
    ('tin', 'Tin', 'Wear metals'),
    ('chromium', 'Chromium', 'Wear metals'),
    ('aluminium', 'Aluminium', 'Wear metals'),
    ('nickel', 'Nickel', 'Wear metals'),
    ('titanium', 'Titanium', 'Wear metals'),
    ('silver', 'Silver', 'Wear metals'),
    ('manganese', 'Manganese', 'Wear metals'),
    ('vanadium', 'Vanadium', 'Wear metals'),
    ('molybdenum', 'Molybdenum', 'Wear metals'),
    ('silicon', 'Silicon', 'Contaminants'),
    ('sodium', 'Sodium', 'Contaminants'),
    ('lithium', 'Lithium', 'Contaminants'),
    ('pq_index', 'PQIndex', 'Ferrous debris and cleanliness'),
    ('ferrous_debris', 'FERROUSDEBRIS', 'Ferrous debris and cleanliness'),
    ('iso4406', 'ISOCODE', 'Ferrous debris and cleanliness'),
    ('iso_4um', '4um', 'Ferrous debris and cleanliness'),
    ('iso_6um', '6um', 'Ferrous debris and cleanliness'),
    ('iso_14um', '14um', 'Ferrous debris and cleanliness'),
    ('iso4407', 'ISOCODE4407', 'Ferrous debris and cleanliness'),
    ('iso4407_4um', '4um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_6um', '6um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_14um', '14um4407', 'Ferrous debris and cleanliness'),
    ('nas1638', 'NAS1638Class', 'Ferrous debris and cleanliness'),
    ('pc_4um', 'ParticleCount4', 'Ferrous debris and cleanliness'),
    ('pc_5um', 'ParticleCount5', 'Ferrous debris and cleanliness'),
    ('pc_6um', 'ParticleCount6', 'Ferrous debris and cleanliness'),
    ('pc_7um', 'ParticleCount7', 'Ferrous debris and cleanliness'),
    ('pc_10um', 'ParticleCount10', 'Ferrous debris and cleanliness'),
    ('pc_14um', 'ParticleCount14', 'Ferrous debris and cleanliness'),
    ('pc_20um', 'ParticleCount20', 'Ferrous debris and cleanliness'),
    ('pc_30um', 'ParticleCount30', 'Ferrous debris and cleanliness'),
    ('pc_5_15um', 'ParticleCount5-15um', 'Ferrous debris and cleanliness'),
    ('pc_15_25um', 'ParticleCount15-25um', 'Ferrous debris and cleanliness'),
    ('pc_25_50um', 'ParticleCount25-50um', 'Ferrous debris and cleanliness'),
    ('pc_50_100um', 'ParticleCount50-100um', 'Ferrous debris and cleanliness'),
    ('pc_gt_100um', 'ParticleCountGreater100um', 'Ferrous debris and cleanliness'),
    ('engine_hours', 'EngineHours', 'Operating context'),
    ('liner_hours', 'LinerHours', 'Operating context'),
    ('crown_hours', 'CrownHours', 'Operating context'),
    ('piston_ring_hours', 'PistonRingHours', 'Operating context'),
    ('feed_rate', 'FeedRate', 'Operating context'),
    ('fuel_sulphur_pct', 'FuelSulphurlevel', 'Operating context'),
    ('engine_load', 'EngineLoad', 'Operating context'),
    ('engine_rpm', 'EngineRpm', 'Operating context'),
    ('scav_temp', 'ScavTemp', 'Operating context'),
    ('scav_press', 'ScavPress', 'Operating context'),
    ('jcw_temp_in', 'JCWTmpIn', 'Operating context'),
    ('jcw_temp_out', 'JCWTmpOut', 'Operating context'),
    ('tc_rpm', 'TCRpm', 'Operating context'),
    ('sea_temp', 'SeaTemperature', 'Operating context'),
    ('ambient_temp', 'AmbientTemp', 'Operating context'),
    ('ambient_humidity', 'AmbientHumidity', 'Operating context'),
    ('mcr', 'MCR', 'Operating context'),
    ('sloc', 'SLOC', 'Operating context'),
    ('tc_cut_out', 'TcCutOut', 'Operating context'),
    ('fuel_valves_hr', 'FuelValvesHr', 'Operating context'),
    ('stroke_42', 'Stroke42', 'Operating context'),
    ('last_filter_change_hrs', 'LastFilterChangeHrs', 'Operating context'),
    ('whr_bypass', 'WHRByPass', 'Operating context')
  ) AS v(code, legacy_column, report_block)
)
SELECT lo.code, lo.legacy_column, lo.report_block, n.normId
INTO   dbo.lo_param_map
FROM   lo
LEFT   JOIN dbo.Norms n
       ON  REPLACE(n.normName, ' ', '') = lo.legacy_column
       OR  REPLACE(n.ShortName, ' ', '') = lo.legacy_column;

-- what still needs a hand-assigned normId
SELECT code, legacy_column FROM dbo.lo_param_map WHERE normId IS NULL;

-- fuel oil
SELECT 'tpl-fo-' + CAST(t.PTemplateId AS varchar(20))        AS source_id,
       COALESCE(t.ModifiedDate, t.CreatedDate)              AS source_modified_at,
       CAST(0 AS bit)                                       AS source_deleted,
       'st-fo-' + CAST(t.OilTypeId AS varchar(20))          AS sample_type_source_id,
       t.PTemplateName                                      AS name,
       NULL                                                 AS organisation_source_id,
       NULL                                                 AS spec_reference,
       NULL                                                 AS boilerplate,
       CASE WHEN ISNULL(t.Active, 1) = 1 THEN 1 ELSE 0 END  AS is_active
FROM   dbo.FO_ParameterTemplate t

UNION ALL

-- water: the report boilerplate lives on the template
SELECT 'tpl-wa-' + CAST(t.TemplateId AS varchar(20)),
       COALESCE(t.ModifiedDate, t.CreatedDate),
       CAST(0 AS bit),
       'st-wa-' + CAST(t.WaterTypeId AS varchar(20)),
       CASE WHEN st.SubTypeName IS NULL THEN t.TemplateName
            ELSE t.TemplateName + ' (' + st.SubTypeName + ')' END,
       NULL,
       NULL,
       (SELECT t.FooterText      AS footer,
               t.CertificateText AS certificate,
               t.BackgroundText  AS background,
               t.No_of_Analysis  AS no_of_analysis
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
       CASE WHEN ISNULL(t.IsActive, 1) = 1 THEN 1 ELSE 0 END
FROM   dbo.WA_Template t
LEFT   JOIN dbo.WA_SubType st ON st.SubTypeId = t.SubTypeId;

-- fuel oil: parameters sit in blocks; order by block, then by insertion
SELECT 'tpl-fo-' + CAST(d.PTemplateId AS varchar(20))
       + ':p-fo-' + CAST(d.ParameterId AS varchar(20))      AS source_id,
       COALESCE(d.ModifiedDate, d.CreatedDate)              AS source_modified_at,
       CAST(0 AS bit)                                       AS source_deleted,
       'tpl-fo-' + CAST(d.PTemplateId AS varchar(20))        AS template_source_id,
       'p-fo-' + CAST(d.ParameterId AS varchar(20))         AS parameter_source_id,
       CASE WHEN ISNULL(p.IsCompulsory, 0) = 1 THEN 1 ELSE 0 END AS is_compulsory,
       ROW_NUMBER() OVER (PARTITION BY d.PTemplateId
                          ORDER BY b.TitleOrder, d.PTemplate_Block_ParameterId) AS sequence_no
FROM   dbo.FO_ParameterTemplateBlockDetails d
JOIN   dbo.FO_Parameters p ON p.ParameterId = d.ParameterId
LEFT   JOIN dbo.FO_ParameterTemplateBlock b
       ON b.PTemplateId = d.PTemplateId AND b.BlockId = d.BlockId

UNION ALL

-- water: a flat mapping with no order column
SELECT 'tpl-wa-' + CAST(m.TemplateId AS varchar(20))
       + ':p-wa-' + CAST(m.ParameterId AS varchar(20)),
       COALESCE(m.ModifiedDate, m.CreatedDate),
       CAST(0 AS bit),
       'tpl-wa-' + CAST(m.TemplateId AS varchar(20)),
       'p-wa-' + CAST(m.ParameterId AS varchar(20)),
       CASE WHEN ISNULL(p.IsCompulsory, 0) = 1 THEN 1 ELSE 0 END,
       NULL
FROM   dbo.WA_ParameterTemplateMapping m
JOIN   dbo.WA_Parameter p ON p.ParameterId = m.ParameterId;

-- lube oil: one report per upload batch per vessel
SELECT 'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS source_id,
       MAX(COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived)) AS source_modified_at,
       CAST(0 AS bit)                                      AS source_deleted,
       'ship-' + CAST(r.VesselID AS varchar(20))           AS ship_source_id,
       'cst-' + CAST(MAX(r.CompanyID) AS varchar(20))      AS organisation_source_id,
       NULL                                                AS template_source_id,
       'lube_oil'                                          AS stream,
       MAX(r.FORMNO)                                       AS report_no,
       CASE WHEN MAX(CAST(r.isAssess AS int)) = 1
            THEN 'assessed' ELSE 'received' END            AS status,
       MAX(r.MDoilUnitRating)                              AS overall_rating,
       MIN(r.DateReceived)                                 AS received_at,
       MAX(r.FTSamplePublishDate)                          AS published_at,
       NULL                                                AS pdf_url
FROM   dbo.AssessmentReport r
WHERE  r.VesselID IS NOT NULL AND r.CsvID IS NOT NULL
GROUP  BY r.CsvID, r.VesselID

UNION ALL

-- fuel oil: the header is already the report
SELECT 'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       'cst-' + CAST(cs.CstId AS varchar(20)),
       'tpl-fo-' + CAST(h.ParameterTemplateId AS varchar(20)),
       'fuel_oil',
       h.PDFName,
       CASE WHEN h.PublishDate IS NOT NULL THEN 'published'
            WHEN h.UnpublishDate IS NOT NULL THEN 'unpublished'
            ELSE 'in_progress' END,
       h.OverAllGrade,
       h.UploadDate,
       h.PublishDateUTC,
       h.PDFName
FROM   dbo.FO_CSVHeader h
JOIN   dbo.Ship s  ON s.ShipId = h.ShipId
JOIN   dbo.Customer cs ON cs.CstId = s.ShipCompany

UNION ALL

-- water: same shape, from WA_CSVHeader
SELECT 'rpt-wa-' + CAST(h.WAReportHeaderId AS varchar(20)),
       COALESCE(h.PublishDate, h.CreatedDate),
       CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       'cst-' + CAST(h.CompanyId AS varchar(20)),
       'tpl-wa-' + CAST(h.TemplateId AS varchar(20)),
       'water',
       h.TestReportNoOrCertificateRefNo,
       CASE WHEN h.IsPublish = 1 THEN 'published' ELSE 'in_progress' END,
       NULL,
       NULL,
       h.PublishDateUTC,
       h.PDFName
FROM   dbo.WA_CSVHeader h;

-- lube oil: one AssessmentReport row IS one sample
SELECT 'smp-lo-' + CAST(r.RSID AS varchar(20))            AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS report_source_id,
       'unit-' + CAST(sm.shipModId AS varchar(20))        AS equipment_unit_source_id,
       'st-lo-' + CAST(ISNULL(r.TestType, 1) AS varchar(20)) AS sample_type_source_id,
       r.SampleNumber                                     AS sample_no,
       r.BOTTLENO                                         AS bottle_no,
       r.DateSampled                                      AS sampled_at,
       r.DateDispatched                                   AS dispatched_at,
       r.DateReceived                                     AS received_at,
       r.Samplingpoint                                    AS sampling_point,
       r.SampledBy                                        AS sampled_by,
       TRY_CAST(r.UnitServiceHours AS decimal(18,2))      AS unit_hours,
       TRY_CAST(r.OilServiceHours AS decimal(18,2))       AS oil_hours,
       r.OilGrade                                         AS oil_grade,
       r.oilUnitRating                                    AS rating,
       NULL                                               AS latitude,
       NULL                                               AS longitude,
       (SELECT r.GOMId          AS gom_id,
               r.WorkOrderNumber AS work_order_no,
               r.DailyMakeUp     AS daily_make_up,
               r.FORMNO          AS form_no
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)             AS context
FROM   dbo.AssessmentReport r
LEFT   JOIN dbo.ShipModel sm
       ON  sm.shipId = r.VesselID
       AND sm.macId  = r.MachineryID
       AND ISNULL(sm.SrNo, '') = ISNULL(r.SerialNo, '')
WHERE  r.VesselID IS NOT NULL AND r.CsvID IS NOT NULL;

-- fuel oil: the header is also the sample (FO_CSVDetails hangs off the header)
SELECT 'smp-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       'unit-fo-' + CAST(h.ShipId AS varchar(20)),
       'st-fo-' + CAST(h.OilTypeId AS varchar(20)),
       h.PDFName, NULL, h.UploadDate, NULL, h.UploadDate,
       NULL, NULL, NULL, NULL, NULL, h.OverAllGrade, NULL, NULL,
       (SELECT h.LabComments    AS lab_comment,
               h.VesselComments AS vessel_comment,
               b.FO_BunkerOrTankName AS bunker_or_tank
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM   dbo.FO_CSVHeader h
LEFT   JOIN dbo.FO_BunkerOrTank b ON b.FO_BunkerOrTankID = h.FOBunkerOrTankId

UNION ALL

-- water: one row per sample in the report
SELECT 'smp-wa-' + CAST(d.WACSVHeaderDetailsMappingId AS varchar(20)),
       COALESCE(d.SampleCollectiondate, d.LabReceiptDate),
       CAST(0 AS bit),
       'rpt-wa-' + CAST(d.WAReportHeaderId AS varchar(20)),
       'unit-wa-' + CAST(h.ShipId AS varchar(20))
                  + '-' + CAST(t.WaterTypeId AS varchar(20)),
       'st-wa-' + CAST(t.WaterTypeId AS varchar(20)),
       d.SampleNumber, NULL, d.SampleCollectiondate, NULL, d.LabReceiptDate,
       d.SamplingPoint, NULL, NULL, NULL, NULL, NULL,
       TRY_CAST(d.Latitude AS decimal(9,6)),
       TRY_CAST(d.Longitude AS decimal(9,6)),
       (SELECT d.Vessel_Comment AS vessel_comment,
               d.Lab_Comment    AS lab_comment,
               d.AWBNo          AS awb_no
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM   dbo.WA_CSVHeaderDetailsMapping d
JOIN   dbo.WA_CSVHeader h  ON h.WAReportHeaderId = d.WAReportHeaderId
JOIN   dbo.WA_Template t   ON t.TemplateId = h.TemplateId;

SELECT 'asm-lo-' + CAST(r.RSID AS varchar(20))           AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.AssessmentDate) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS report_source_id,
       'smp-lo-' + CAST(r.RSID AS varchar(20))            AS sample_source_id,
       r.oilUnitRating                                    AS rating,
       r.CommentOil                                       AS comment_oil,
       r.CommentUnit                                      AS comment_unit,
       r.CommentSum                                       AS summary,
       COALESCE(r.ActionSum, r.Action)                    AS action,
       CAST(r.ReviewBy AS varchar(20))                    AS assessed_by,
       r.AssessmentDate                                   AS assessed_at
FROM   dbo.AssessmentReport r
WHERE  r.isAssess = 1

UNION ALL

-- fuel oil keeps three separate comment fields on the header
SELECT 'asm-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       'smp-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       h.OverAllGrade,
       h.LabGradeComments,
       h.LabSpecificationComments,
       h.LabOperationalComments,
       NULL,
       CAST(h.ReviewedBy AS varchar(20)),
       h.PublishDateUTC
FROM   dbo.FO_CSVHeader h
WHERE  COALESCE(h.LabGradeComments, h.LabSpecificationComments,
                h.LabOperationalComments) IS NOT NULL;

SELECT 'rv-lo-' + CAST(r.RSID AS varchar(20)) + '-' + v.code  AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'smp-lo-' + CAST(r.RSID AS varchar(20))            AS sample_source_id,
       'p-lo-' + v.code                                   AS parameter_source_id,
       LTRIM(RTRIM(v.value_raw))                          AS value_raw,
       TRY_CAST(REPLACE(LTRIM(RTRIM(v.value_raw)), ',', '.') AS decimal(18,4)) AS value_num,
       NULL                                               AS unit,
       NULL                                               AS flag,   -- see note below
       CAST(0 AS bit)                                     AS is_retest,
       1                                                  AS revision,
       NULL                                               AS comment
FROM   dbo.AssessmentReport r
CROSS  APPLY (VALUES
    -- Condition
    ('appearance', r.Appearance),
    ('vis40', r.Vis40),
    ('vis100', r.Vischk00),
    ('vis_index', r.VisIndex),
    ('flash_point', r.FlashPoint),
    ('water_pct', r.Water),
    ('water_kf_ppm', r.WaterKFppm),
    ('emulsified_water', r.EmulsifiedWater),
    ('tbn', r.BaseNumber),
    ('tan', r.AcidNo),
    ('soot_insolubles', r.SootInsolubles),
    ('pentane_insolubles', r.PentaneInsolubles),
    ('sulphated_ash', r.SulphatedAsh),
    ('oxidation', r.Oxidation),
    ('oxidation_abs', r.OxidationAbsolute),
    ('oxidation_products', r.OxidationProducts),
    ('nitration_abs', r.NitrationAbsolute),
    ('sulphation_abs', r.SulphationAbsolute),
    ('glycol', r.GLYCOL),
    ('fuel_dilution', r.FUELDILUTION),
    ('asphaltenes', r.Asphaltenes),
    ('chlorine', r.Chlorine),
    ('ph', r.pH),
    ('specific_gravity', r.SpecificGravity),
    ('conductivity', r.Conductivity),
    ('liquid_corrosion', r.LiquidCorrosionConcentration),
    ('vapour_corrosion', r.VapourPhaseCorrosion),
    ('rocking_test', r.RockingTest),
    ('sulphur', r.Sulpher),
    ('fuel_p', r.FuelP),
    ('oil_condition_index', r.OILCONDITIONINDEX),
    ('water_scavenger', r.WaterScavenger),
    -- Additive elements
    ('calcium', r.Calcium),
    ('magnesium', r.Magnesium),
    ('zinc', r.Zinc),
    ('phosphorus', r.Phosphorus),
    ('barium', r.Barium),
    ('boron', r.Boron),
    -- Wear metals
    ('iron', r.Iron),
    ('copper', r.Copper),
    ('lead', r.Lead),
    ('tin', r.Tin),
    ('chromium', r.Chromium),
    ('aluminium', r.Aluminium),
    ('nickel', r.Nickel),
    ('titanium', r.Titanium),
    ('silver', r.Silver),
    ('manganese', r.Manganese),
    ('vanadium', r.Vanadium),
    ('molybdenum', r.Molybdenum),
    -- Contaminants
    ('silicon', r.Silicon),
    ('sodium', r.Sodium),
    ('lithium', r.Lithium),
    -- Ferrous debris and cleanliness
    ('pq_index', r.PQIndex),
    ('ferrous_debris', r.FERROUSDEBRIS),
    ('iso4406', r.ISOCODE),
    ('iso_4um', r.[4um]),
    ('iso_6um', r.[6um]),
    ('iso_14um', r.[14um]),
    ('iso4407', r.ISOCODE4407),
    ('iso4407_4um', r.[4um4407]),
    ('iso4407_6um', r.[6um4407]),
    ('iso4407_14um', r.[14um4407]),
    ('nas1638', r.NAS1638Class),
    ('pc_4um', r.ParticleCount4),
    ('pc_5um', r.ParticleCount5),
    ('pc_6um', r.ParticleCount6),
    ('pc_7um', r.ParticleCount7),
    ('pc_10um', r.ParticleCount10),
    ('pc_14um', r.ParticleCount14),
    ('pc_20um', r.ParticleCount20),
    ('pc_30um', r.ParticleCount30),
    ('pc_5_15um', r.[ParticleCount5-15um]),
    ('pc_15_25um', r.[ParticleCount15-25um]),
    ('pc_25_50um', r.[ParticleCount25-50um]),
    ('pc_50_100um', r.[ParticleCount50-100um]),
    ('pc_gt_100um', r.ParticleCountGreater100um),
    -- Operating context
    ('engine_hours', r.EngineHours),
    ('liner_hours', r.LinerHours),
    ('crown_hours', r.CrownHours),
    ('piston_ring_hours', r.PistonRingHours),
    ('feed_rate', r.FeedRate),
    ('fuel_sulphur_pct', r.FuelSulphurlevel),
    ('engine_load', r.EngineLoad),
    ('engine_rpm', r.EngineRpm),
    ('scav_temp', r.ScavTemp),
    ('scav_press', r.ScavPress),
    ('jcw_temp_in', r.JCWTmpIn),
    ('jcw_temp_out', r.JCWTmpOut),
    ('tc_rpm', r.TCRpm),
    ('sea_temp', r.SeaTemperature),
    ('ambient_temp', r.AmbientTemp),
    ('ambient_humidity', r.AmbientHumidity),
    ('mcr', r.MCR),
    ('sloc', r.SLOC),
    ('tc_cut_out', r.TcCutOut),
    ('fuel_valves_hr', r.FuelValvesHr),
    ('stroke_42', r.Stroke42),
    ('last_filter_change_hrs', r.LastFilterChangeHrs),
    ('whr_bypass', r.WHRByPass)
) AS v(code, value_raw)
WHERE  v.value_raw IS NOT NULL
  AND  LTRIM(RTRIM(v.value_raw)) <> '';

SELECT 'rv-fo-' + CAST(d.FOReportsDetailsId AS varchar(20)) AS source_id,
       COALESCE(d.ModifiedDate, d.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'smp-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20)) AS sample_source_id,
       'p-fo-' + CAST(d.ParameterId AS varchar(20))       AS parameter_source_id,
       d.Value                                            AS value_raw,
       TRY_CAST(REPLACE(d.Value, ',', '.') AS decimal(18,4)) AS value_num,
       NULL                                               AS unit,
       LOWER(d.ColorDesign)                               AS flag,
       ISNULL(d.ReTestFlag, 0)                            AS is_retest,
       ISNULL(d.Version, 1)                               AS revision,
       d.Comments                                         AS comment
FROM   dbo.FO_CSVDetails d
WHERE  ISNULL(d.ActiveVersion, 1) = 1

UNION ALL

SELECT 'rv-wa-' + CAST(d.WAReportsDetailsId AS varchar(20)),
       COALESCE(d.ModifiedDate, d.CreatedDate),
       CAST(0 AS bit),
       'smp-wa-' + CAST(d.WACSVHeaderDetailsMappingId AS varchar(20)),
       'p-wa-' + CAST(d.ParameterId AS varchar(20)),
       d.Value,
       TRY_CAST(REPLACE(d.Value, ',', '.') AS decimal(18,4)),
       NULL,
       CASE c.Colour WHEN 1 THEN 'red' WHEN 2 THEN 'yellow'
                     WHEN 3 THEN 'normal' END,
       ISNULL(d.ReTestFlag, 0), 1, d.Comments
FROM   dbo.WA_CSVDetails d
LEFT   JOIN dbo.WA_ColourCode c
       ON  c.WA_CSVHeaderDetailsMappingId = d.WACSVHeaderDetailsMappingId
       AND c.ParaID = d.ParameterId;

-- yellow band
SELECT 'lr-oil-' + CAST(o.onId AS varchar(20)) + '-y'   AS source_id,
       GETUTCDATE()                                     AS source_modified_at,
       CAST(0 AS bit)                                   AS source_deleted,
       'p-lo-' + map.code                               AS parameter_source_id,
       'oil_grade'                                      AS scope_kind,
       og.oilGrade                                      AS scope_ref,
       'yellow'                                         AS band,
       sy.sign                                          AS operator,
       o.val1Y                                          AS value_1,
       o.Val2Y                                          AS value_2,
       o.PercentY                                       AS percent,
       20                                               AS precedence
FROM   dbo.OilNorms o
JOIN   dbo.OilGrade og ON og.oilId = o.oilId
JOIN   dbo.lo_param_map map ON map.normId = o.normId
LEFT   JOIN dbo.Sign sy ON sy.signId = o.signIdY
WHERE  o.signIdY IS NOT NULL

UNION ALL

-- red band: same row, the R columns
SELECT 'lr-oil-' + CAST(o.onId AS varchar(20)) + '-r',
       GETUTCDATE(), CAST(0 AS bit),
       'p-lo-' + map.code, 'oil_grade', og.oilGrade, 'red',
       sr.sign, o.val1R, o.Val2R, o.PercentR, 20
FROM   dbo.OilNorms o
JOIN   dbo.OilGrade og ON og.oilId = o.oilId
JOIN   dbo.lo_param_map map ON map.normId = o.normId
LEFT   JOIN dbo.Sign sr ON sr.signId = o.signIdR
WHERE  o.signIdR IS NOT NULL;

-- fuel oil: FO_BlockParameterLimit holds Yellow1/2 and Red1/2 side by side
SELECT 'lr-fo-' + CAST(l.BlockParameterLimitId AS varchar(20)) + '-' + b.band,
       COALESCE(l.ModifiedDate, l.CreatedDate),
       CAST(0 AS bit),
       'p-fo-' + CAST(l.ParameterId AS varchar(20)),
       'fuel_grade',
       lim.FuelGradeName,
       b.band,
       op.Sign,
       b.v1, b.v2, NULL, 20
FROM   dbo.FO_BlockParameterLimit l
JOIN   dbo.FO_Limits lim ON lim.LimitsId = l.LimitId
LEFT   JOIN dbo.FO_Operator op ON op.OperatorId = l.OperatorId
CROSS  APPLY (VALUES ('yellow', l.Yellow1, l.Yellow2),
                     ('red',    l.Red1,    l.Red2)) AS b(band, v1, v2)
WHERE  b.v1 IS NOT NULL

UNION ALL

-- water: WA_ModelNorms, same idea, scoped to a template
SELECT 'lr-wa-' + CAST(n.TemplateParameterId AS varchar(20)) + '-' + b.band,
       GETUTCDATE(), CAST(0 AS bit),
       'p-wa-' + CAST(n.ParameterId AS varchar(20)),
       'template', t.TemplateName, b.band,
       sg.sign, b.v1, b.v2, NULL, 20
FROM   dbo.WA_ModelNorms n
JOIN   dbo.WA_Template t ON t.TemplateId = n.TemplateId
CROSS  APPLY (VALUES ('yellow', n.Val1Yellow, n.Val2Yellow, n.SignIdYellow),
                     ('red',    n.Val1Red,    n.Val2Red,    n.SignIdRed)) AS b(band, v1, v2, sid)
LEFT   JOIN dbo.WA_Sign sg ON sg.signId = b.sid
WHERE  b.v1 IS NOT NULL;

SELECT DISTINCT
       'rls-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20))
                 + '-' + CAST(d.ParameterId AS varchar(20)) + '-' + b.band AS source_id,
       COALESCE(d.ModifiedDate, d.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20)) AS report_source_id,
       'p-fo-' + CAST(d.ParameterId AS varchar(20))       AS parameter_source_id,
       b.band                                             AS band,
       op.Sign                                            AS operator,
       b.v1                                               AS value_1,
       b.v2                                               AS value_2
FROM   dbo.FO_CSVDetails d
LEFT   JOIN dbo.FO_Operator op ON op.OperatorId = d.OperatorId
CROSS  APPLY (VALUES ('yellow', d.Yellow1, d.Yellow2),
                     ('red',    d.Red1,    d.Red2)) AS b(band, v1, v2)
WHERE  ISNULL(d.ActiveVersion, 1) = 1 AND b.v1 IS NOT NULL

UNION ALL

SELECT 'rls-wa-' + CAST(n.Id AS varchar(20)) + '-' + b.band,
       GETUTCDATE(), CAST(0 AS bit),
       'rpt-wa-' + CAST(n.WAReportHeaderId AS varchar(20)),
       'p-wa-' + CAST(n.ParameterId AS varchar(20)),
       b.band, sg.sign, b.v1, b.v2
FROM   dbo.WA_ModelNorms_Reviewed n
CROSS  APPLY (VALUES ('yellow', n.Val1Yellow, n.Val2Yellow, n.SignIdYellow),
                     ('red',    n.Val1Red,    n.Val2Red,    n.SignIdRed)) AS b(band, v1, v2, sid)
LEFT   JOIN dbo.WA_Sign sg ON sg.signId = b.sid
WHERE  b.v1 IS NOT NULL;

-- one bunker unit per vessel that has ever had a fuel-oil report
SELECT DISTINCT
       'unit-fo-' + CAST(h.ShipId AS varchar(20))         AS source_id,
       GETUTCDATE()                                       AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'ship-' + CAST(h.ShipId AS varchar(20))            AS ship_source_id,
       'Bunker fuel'                                      AS name,
       'fuel_oil'                                         AS stream,
       'bunker'                                           AS application,
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       0                                                  AS is_critical,
       NULL                                               AS external_ref,
       1                                                  AS is_active
FROM   dbo.FO_CSVHeader h
WHERE  h.ShipId IS NOT NULL

UNION ALL

-- one unit per vessel per water type
SELECT DISTINCT
       'unit-wa-' + CAST(h.ShipId AS varchar(20))
                  + '-' + CAST(t.WaterTypeId AS varchar(20)),
       GETUTCDATE(), CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       w.WaterTypeName + ' system',
       'water',
       LOWER(REPLACE(w.WaterTypeName, ' ', '_')),
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       0, NULL, 1
FROM   dbo.WA_CSVHeader h
JOIN   dbo.WA_Template t  ON t.TemplateId = h.TemplateId
JOIN   dbo.WA_WaterType w ON w.WaterTypeId = t.WaterTypeId;

-- lube oil
SELECT COUNT(*) FROM dbo.AssessmentReport
WHERE VesselID IS NOT NULL AND CsvID IS NOT NULL;          -- = sample rows (lo)

SELECT COUNT(DISTINCT CAST(CsvID AS varchar(20)) + '-'
                    + CAST(VesselID AS varchar(20)))
FROM   dbo.AssessmentReport
WHERE  VesselID IS NOT NULL AND CsvID IS NOT NULL;          -- = report rows (lo)

-- result values: samples x populated columns, the number to expect
SELECT COUNT(*)
FROM   dbo.AssessmentReport r
CROSS  APPLY (VALUES (r.Iron), (r.Copper), (r.Vis40) /* … all columns … */) AS v(val)
WHERE  v.val IS NOT NULL AND LTRIM(RTRIM(v.val)) <> '';

-- fuel oil and water
SELECT COUNT(*) FROM dbo.FO_CSVDetails WHERE ISNULL(ActiveVersion,1) = 1;
SELECT COUNT(*) FROM dbo.WA_CSVDetails;

-- was: COALESCE(d.SampleCollectiondate, d.LabReceiptDate)
(SELECT MIN(v) FROM (VALUES (COALESCE(h.PublishDate, h.CreatedDate)), (GETUTCDATE())) AS c(v))
                                                       AS source_modified_at,

(SELECT MIN(v) FROM (VALUES (CASE WHEN sm.LastSampleDate > sm.crDate
                                  THEN sm.LastSampleDate ELSE sm.crDate END),
                            (GETUTCDATE())) AS c(v))     AS source_modified_at,

SELECT COUNT(*) FROM (<extract query>) q WHERE q.source_modified_at > GETUTCDATE();

CROSS  APPLY (VALUES ('yellow', l.Yellow1, l.Yellow2),
                     ('red',    l.Red1,    l.Red2)) AS raw(band, t1, t2)
CROSS  APPLY (SELECT raw.band,
                     TRY_CAST(REPLACE(NULLIF(LTRIM(RTRIM(raw.t1)), ''), ',', '.') AS decimal(18,4)) AS v1,
                     TRY_CAST(REPLACE(NULLIF(LTRIM(RTRIM(raw.t2)), ''), ',', '.') AS decimal(18,4)) AS v2) AS b
WHERE  b.v1 IS NOT NULL

CROSS  APPLY (VALUES ('yellow', d.Yellow1, d.Yellow2),
                     ('red',    d.Red1,    d.Red2)) AS raw(band, t1, t2)
CROSS  APPLY (SELECT raw.band,
                     TRY_CAST(REPLACE(NULLIF(LTRIM(RTRIM(raw.t1)), ''), ',', '.') AS decimal(18,4)) AS v1,
                     TRY_CAST(REPLACE(NULLIF(LTRIM(RTRIM(raw.t2)), ''), ',', '.') AS decimal(18,4)) AS v2) AS b
WHERE  <current-revision filter from D7> AND b.v1 IS NOT NULL

SELECT t, COUNT(*) AS n
FROM   dbo.FO_BlockParameterLimit l
CROSS  APPLY (VALUES (l.Yellow1), (l.Yellow2), (l.Red1), (l.Red2)) AS x(t)
WHERE  NULLIF(LTRIM(RTRIM(x.t)), '') IS NOT NULL
  AND  TRY_CAST(REPLACE(LTRIM(RTRIM(x.t)), ',', '.') AS decimal(18,4)) IS NULL
GROUP  BY t ORDER BY n DESC;

SELECT 'lr-mod-' + CAST(n.mnId AS varchar(20))          AS source_id,
       GETUTCDATE()                                     AS source_modified_at,
       CAST(0 AS bit)                                   AS source_deleted,
       'p-lo-' + map.code                               AS parameter_source_id,
       'model'                                          AS scope_kind,
       mo.modNo                                         AS scope_ref,
       'red'                                            AS band,
       s.sign                                           AS operator,
       n.val1                                           AS value_1,
       n.val2                                           AS value_2,
       n.[percent]                                      AS [percent],
       30                                               AS precedence
FROM   dbo.ModelNorms n
JOIN   dbo.Model mo         ON mo.modId = n.modId
JOIN   dbo.lo_param_map map ON map.normId = n.normId
LEFT   JOIN dbo.Sign s      ON s.signId = n.signId
WHERE  n.val1 IS NOT NULL OR n.[percent] IS NOT NULL

UNION ALL

SELECT 'lr-cst-' + CAST(n.cnId AS varchar(20)),
       GETUTCDATE(), CAST(0 AS bit),
       'p-lo-' + map.code, 'customer', c.CstCompany, 'red',
       s.sign, n.val1, n.val2, n.[percent], 50
FROM   dbo.CustomerNorms n
JOIN   dbo.Customer c       ON c.CstId = n.cstId
JOIN   dbo.lo_param_map map ON map.normId = n.normId
LEFT   JOIN dbo.Sign s      ON s.signId = n.signId
WHERE  n.val1 IS NOT NULL OR n.[percent] IS NOT NULL;

SELECT 'unit-pur-' + CAST(sp.shipPurId AS varchar(20))   AS source_id,
       (SELECT MIN(v) FROM (VALUES (COALESCE(sp.modDate, sp.crDate)), (GETUTCDATE())) AS c(v))
                                                         AS source_modified_at,
       CAST(0 AS bit)                                    AS source_deleted,
       'ship-' + CAST(NULLIF(sp.ShipId, 0) AS varchar(20)) AS ship_source_id,
       'Purifier'                                        AS name,
       'lube_oil'                                        AS stream,
       'purifier'                                        AS application,
       man.manName                                       AS make,
       pm.purNo                                          AS model,
       NULL                                              AS serial_no,
       og.oilGrade                                       AS oil_grade,
       ob.obrandName                                     AS oil_brand,
       TRY_CAST(sp.OilVol AS decimal(18,2))              AS charge_volume_l,
       sp.Schedule                                       AS sampling_interval_days,
       sp.LastSampleDate                                 AS last_sampled_at,
       0                                                 AS is_critical,
       NULL                                              AS external_ref,
       1                                                 AS is_active
FROM   dbo.ShipPurifier sp
LEFT   JOIN dbo.PurifierModel pm  ON pm.purId   = sp.PurId
LEFT   JOIN dbo.Manufacturer man  ON man.manId  = pm.manId
LEFT   JOIN dbo.OilGrade og       ON og.oilId   = sp.OilGradeId
LEFT   JOIN dbo.OilBrand ob       ON ob.obrandId = og.obrandId;

OUTER  APPLY (SELECT TOP 1 c.shipModId
              FROM   dbo.ShipModel c
              WHERE  c.shipId = r.VesselID
                AND  (   (r.SEATECMACHINERYID IS NOT NULL AND c.SeaTecMachineryId = r.SEATECMACHINERYID)
                      OR (c.macId = r.MachineryID
                          AND (   REPLACE(ISNULL(c.SrNo, ''), ' ', '') = REPLACE(ISNULL(r.SerialNo, ''), ' ', '')
                               OR (SELECT COUNT(*) FROM dbo.ShipModel c2
                                   WHERE c2.shipId = r.VesselID AND c2.macId = r.MachineryID) = 1)))
              ORDER  BY CASE WHEN c.SeaTecMachineryId = r.SEATECMACHINERYID THEN 0
                             WHEN REPLACE(ISNULL(c.SrNo, ''), ' ', '') = REPLACE(ISNULL(r.SerialNo, ''), ' ', '') THEN 1
                             ELSE 2 END,
                        c.shipModId) AS sm

SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.ShipModel c WHERE c.shipId = r.VesselID) THEN 'vessel has no units'
            WHEN NOT EXISTS (SELECT 1 FROM dbo.ShipModel c WHERE c.shipId = r.VesselID AND c.macId = r.MachineryID) THEN 'no unit of that machinery'
            ELSE 'several units, serial does not match' END AS reason,
       COUNT(*) AS samples
FROM   dbo.AssessmentReport r
OUTER  APPLY (<the join above>) sm
WHERE  r.VesselID IS NOT NULL AND r.CsvID IS NOT NULL AND sm.shipModId IS NULL
GROUP  BY CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.ShipModel c WHERE c.shipId = r.VesselID) THEN 'vessel has no units'
               WHEN NOT EXISTS (SELECT 1 FROM dbo.ShipModel c WHERE c.shipId = r.VesselID AND c.macId = r.MachineryID) THEN 'no unit of that machinery'
               ELSE 'several units, serial does not match' END;

CREATE TABLE dbo.sample_detail_map (
  stream       varchar(2)  NOT NULL,
  parameter_id int         NOT NULL,
  target       varchar(40) NOT NULL,
  PRIMARY KEY (stream, parameter_id)
);
INSERT INTO dbo.sample_detail_map (stream, parameter_id, target) VALUES
  ('fo', 5, 'sampled_at'),
  ('fo', 6, 'dispatched_at'),
  ('fo', 8, 'received_at'),
  ('fo', 11, 'sampling_point'),
  ('fo', 98, 'bottle_no'),
  ('fo', 3, 'sample_seal_no'),
  ('fo', 18, 'seal_no_lab'),
  ('fo', 19, 'seal_no_vessel'),
  ('fo', 20, 'seal_no_supplier'),
  ('fo', 21, 'seal_no_marpol'),
  ('fo', 4, 'seal_integrity'),
  ('fo', 7, 'bunker_port'),
  ('fo', 9, 'bunker_supplier'),
  ('fo', 10, 'barge_name'),
  ('fo', 12, 'sampling_method'),
  ('wa', 165, 'sampled_at'),
  ('wa', 56, 'sampled_at'),
  ('wa', 70, 'sampled_at'),
  ('wa', 26, 'received_at'),
  ('wa', 55, 'received_at'),
  ('wa', 106, 'received_at'),
  ('wa', 30, 'sampling_point'),
  ('wa', 82, 'sampling_point'),
  ('wa', 29, 'sampled_by'),
  ('wa', 60, 'sampled_by'),
  ('wa', 27, 'analyst'),
  ('wa', 58, 'analyst'),
  ('wa', 28, 'container'),
  ('wa', 57, 'container'),
  ('wa', 71, 'sampling_time'),
  ('wa', 158, 'sampling_time'),
  ('wa', 50, 'test_started'),
  ('wa', 4, 'test_started'),
  ('wa', 67, 'test_ended'),
  ('wa', 68, 'test_ended'),
  ('wa', 151, 'port'),
  ('wa', 166, 'port'),
  ('wa', 148, 'make'),
  ('wa', 149, 'model'),
  ('wa', 146, 'model'),
  ('wa', 102, 'bunker_port_and_date');

OUTER  APPLY (SELECT
                     MAX(CASE WHEN m.target = 'sampled_at' THEN v.Value END) AS sampled_at,
                     MAX(CASE WHEN m.target = 'dispatched_at' THEN v.Value END) AS dispatched_at,
                     MAX(CASE WHEN m.target = 'received_at' THEN v.Value END) AS received_at,
                     MAX(CASE WHEN m.target = 'sampling_point' THEN v.Value END) AS sampling_point,
                     MAX(CASE WHEN m.target = 'bottle_no' THEN v.Value END) AS bottle_no,
                     MAX(CASE WHEN m.target = 'sample_seal_no' THEN v.Value END) AS sample_seal_no,
                     MAX(CASE WHEN m.target = 'seal_no_lab' THEN v.Value END) AS seal_no_lab,
                     MAX(CASE WHEN m.target = 'seal_no_vessel' THEN v.Value END) AS seal_no_vessel,
                     MAX(CASE WHEN m.target = 'seal_no_supplier' THEN v.Value END) AS seal_no_supplier,
                     MAX(CASE WHEN m.target = 'seal_no_marpol' THEN v.Value END) AS seal_no_marpol,
                     MAX(CASE WHEN m.target = 'seal_integrity' THEN v.Value END) AS seal_integrity,
                     MAX(CASE WHEN m.target = 'bunker_port' THEN v.Value END) AS bunker_port,
                     MAX(CASE WHEN m.target = 'bunker_supplier' THEN v.Value END) AS bunker_supplier,
                     MAX(CASE WHEN m.target = 'barge_name' THEN v.Value END) AS barge_name,
                     MAX(CASE WHEN m.target = 'sampling_method' THEN v.Value END) AS sampling_method
              FROM   dbo.FO_CSVDetails v
              JOIN   dbo.sample_detail_map m ON m.stream = 'fo' AND m.parameter_id = v.ParameterId
              WHERE  v.FOReoprtsHeaderId = h.FOReportHeaderId
                AND  <current-revision filter from D7, on v>) AS pv

-- in the select list:
--   bottle_no      pv.bottle_no
--   sampled_at     COALESCE(COALESCE(TRY_CONVERT(datetime2, pv.sampled_at, 126), TRY_CONVERT(datetime2, REPLACE(pv.sampled_at, '-', ' '), 106)), h.UploadDate)
--   dispatched_at  COALESCE(TRY_CONVERT(datetime2, pv.dispatched_at, 126), TRY_CONVERT(datetime2, REPLACE(pv.dispatched_at, '-', ' '), 106))
--   received_at    COALESCE(COALESCE(TRY_CONVERT(datetime2, pv.received_at, 126), TRY_CONVERT(datetime2, REPLACE(pv.received_at, '-', ' '), 106)), h.UploadDate)
--   sampling_point pv.sampling_point
--   context        (SELECT h.LabComments AS lab_comment, h.VesselComments AS vessel_comment,
--                          b.FO_BunkerOrTankName AS bunker_or_tank,
--                          pv.sample_seal_no, pv.seal_no_lab, pv.seal_no_vessel, pv.seal_no_supplier,
--                          pv.seal_no_marpol, pv.seal_integrity, pv.bunker_port, pv.bunker_supplier,
--                          pv.barge_name, pv.sampling_method
--                   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

OUTER  APPLY (SELECT
                     MAX(CASE WHEN m.target = 'sampled_at' THEN v.Value END) AS sampled_at,
                     MAX(CASE WHEN m.target = 'received_at' THEN v.Value END) AS received_at,
                     MAX(CASE WHEN m.target = 'sampling_point' THEN v.Value END) AS sampling_point,
                     MAX(CASE WHEN m.target = 'sampled_by' THEN v.Value END) AS sampled_by,
                     MAX(CASE WHEN m.target = 'analyst' THEN v.Value END) AS analyst,
                     MAX(CASE WHEN m.target = 'container' THEN v.Value END) AS container,
                     MAX(CASE WHEN m.target = 'sampling_time' THEN v.Value END) AS sampling_time,
                     MAX(CASE WHEN m.target = 'test_started' THEN v.Value END) AS test_started,
                     MAX(CASE WHEN m.target = 'test_ended' THEN v.Value END) AS test_ended,
                     MAX(CASE WHEN m.target = 'port' THEN v.Value END) AS port,
                     MAX(CASE WHEN m.target = 'make' THEN v.Value END) AS make,
                     MAX(CASE WHEN m.target = 'model' THEN v.Value END) AS model,
                     MAX(CASE WHEN m.target = 'bunker_port_and_date' THEN v.Value END) AS bunker_port_and_date
              FROM   dbo.WA_CSVDetails v
              JOIN   dbo.sample_detail_map m ON m.stream = 'wa' AND m.parameter_id = v.ParameterId
              WHERE  v.WACSVHeaderDetailsMappingId = d.WACSVHeaderDetailsMappingId) AS pv

-- in the select list, the sample's own column first, the parameter where it is empty:
--   sampled_at     COALESCE(d.SampleCollectiondate, COALESCE(TRY_CONVERT(datetime2, pv.sampled_at, 126), TRY_CONVERT(datetime2, REPLACE(pv.sampled_at, '-', ' '), 106)))
--   received_at    COALESCE(d.LabReceiptDate, COALESCE(TRY_CONVERT(datetime2, pv.received_at, 126), TRY_CONVERT(datetime2, REPLACE(pv.received_at, '-', ' '), 106)))
--   sampling_point COALESCE(d.SamplingPoint, pv.sampling_point)
--   sampled_by     pv.sampled_by
--   context        (SELECT d.Vessel_Comment AS vessel_comment, d.Lab_Comment AS lab_comment,
--                          d.AWBNo AS awb_no, pv.analyst, pv.container, pv.sampling_time,
--                          pv.test_started, pv.test_ended, pv.port, pv.make, pv.model,
--                          pv.bunker_port_and_date
--                   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

-- fuel-oil branch
  AND d.ParameterId IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dbo.sample_detail_map m WHERE m.stream = 'fo' AND m.parameter_id = d.ParameterId)
-- water branch
  AND NOT EXISTS (SELECT 1 FROM dbo.sample_detail_map m WHERE m.stream = 'wa' AND m.parameter_id = d.ParameterId)

-- fuel-oil branch (FO_Parameters.Active)
CASE WHEN EXISTS (SELECT 1 FROM dbo.sample_detail_map m
                  WHERE m.stream = 'fo' AND m.parameter_id = p.ParameterId) THEN 0
     WHEN ISNULL(p.Active, 1) = 1 THEN 1 ELSE 0 END                  AS is_active
-- water branch (WA_Parameter.IsActive)
CASE WHEN EXISTS (SELECT 1 FROM dbo.sample_detail_map m
                  WHERE m.stream = 'wa' AND m.parameter_id = p.ParameterId) THEN 0
     WHEN ISNULL(p.IsActive, 1) = 1 THEN 1 ELSE 0 END                AS is_active

SELECT 'wa' AS stream, ParameterId, ParamterName FROM dbo.WA_Parameter
WHERE  ParamterName LIKE '%date%' OR ParamterName LIKE '%time%' OR ParamterName LIKE '%by%'
   OR  ParamterName LIKE '%port%' OR ParamterName LIKE '%seal%' OR ParamterName LIKE '%bottle%'
UNION ALL
SELECT 'fo', ParameterId, PName FROM dbo.FO_Parameters
WHERE  PName LIKE '%date%' OR PName LIKE '%seal%' OR PName LIKE '%port%'
   OR  PName LIKE '%name%' OR PName LIKE '%bottle%';

NULLIF(LTRIM(RTRIM(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.ParamterName,
    '<p>', ''), '</p>', ''), '<br>', ''), '<br/>', ''), '<strong>', ''), '</strong>', ''),
    '<sup>', ''), '</sup>', ''), '<sub>', ''), '</sub>', ''), '&amp;', '&'), '&nbsp;', ' ')
)), '')                       AS name,
NULLIF(LTRIM(RTRIM(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.Unit,
    '<p>', ''), '</p>', ''), '<br>', ''), '<br/>', ''), '<strong>', ''), '</strong>', ''),
    '<sup>', ''), '</sup>', ''), '<sub>', ''), '</sub>', ''), '&amp;', '&'), '&nbsp;', ' ')
)), '')                               AS unit,

WHERE  (   d.ActiveVersion = 1
    OR (d.ActiveVersion IS NULL
        AND ISNULL(d.Version, 1) = (SELECT MAX(ISNULL(d2.Version, 1))
                                    FROM   dbo.FO_CSVDetails d2
                                    WHERE  d2.FOReoprtsHeaderId = d.FOReoprtsHeaderId
                                      AND  d2.ParameterId       = d.ParameterId)))

SELECT ISNULL(CAST(Version AS varchar(10)), 'NULL') AS version,
       ISNULL(CAST(ActiveVersion AS varchar(1)), 'NULL') AS active_version,
       COUNT(*) AS rows
FROM   dbo.FO_CSVDetails
GROUP  BY Version, ActiveVersion
ORDER  BY version, active_version;

'ship-' + CAST(NULLIF(sm.shipId, 0) AS varchar(20))      AS ship_source_id

-- ship
'flt-' + CAST(NULLIF(s.ShipFleet, 0) AS varchar(20))      AS fleet_source_id,
'own-' + CAST(NULLIF(s.OwnerId, 0) AS varchar(20))        AS owner_source_id,
-- organisation, Customer branch
'grp-' + CAST(NULLIF(c.GroupCompanyId, 0) AS varchar(20)) AS parent_source_id,

SELECT COUNT(*) FROM (<child extract>) c
LEFT   JOIN (<parent extract>) p ON p.source_id = c.<parent>_source_id
WHERE  c.<parent>_source_id IS NOT NULL AND p.source_id IS NULL;

SELECT sample_source_id, parameter_source_id, COUNT(*)
FROM   (<result_value extract>) v
GROUP  BY sample_source_id, parameter_source_id
HAVING COUNT(*) > 1;   -- p-fo-71 and p-fo-72 (injection table) are expected