-- 删除smiles为空的
DELETE FROM "t_prod_smiles" struct WHERE smiles IS NULL;

-- 删除smiles是错的的
DELETE FROM "t_prod_smiles" struct WHERE bingo.checkmolecule(smiles) IS NOT NULL;

-- 删除t_product没有相关记录的
with to_delete as (
    SELECT
    struct.*
    FROM "t_prod_smiles" struct

    LEFT JOIN t_product prd
    ON prd.id = struct."id"

    WHERE prd.id is null
)
DELETE FROM "t_prod_smiles" struct
USING to_delete
WHERE struct.id = to_delete.id;

-- 更新结构式不一致的
with tmp as (
    SELECT
    struct.*,
    COALESCE(prd.smiles, '') new_smiles
    FROM t_prod_smiles struct

    LEFT JOIN t_product prd
    ON prd.id = struct."id"

    WHERE COALESCE(prd.smiles, '') != COALESCE(struct.smiles, '')
),to_update as (
    SELECT
    tmp.*
    FROM tmp

    WHERE bingo.checkmolecule(tmp.new_smiles) is null
)
-- SELECT * FROM to_update
UPDATE t_prod_smiles struct
SET smiles = to_update.new_smiles,
update_time = now()
FROM to_update
WHERE to_update.id = struct.id;

-- 新增
with tmp as (
    SELECT
    prd.id,
    COALESCE(prd.smiles, '') smiles,
    now() create_time,
    now() update_time,
    1 is_delete
    FROM t_product prd

    LEFT JOIN t_prod_smiles struct
    ON prd.id = struct."id"

    WHERE 1=1
    AND struct.id is null
    AND COALESCE(prd.smiles, '') != ''
)
INSERT INTO t_prod_smiles(id, smiles, create_time, update_time, is_delete)
SELECT
tmp.*
FROM tmp

WHERE 1=1
AND bingo.checkmolecule(tmp.smiles) is null