# Policy Data Patch and Index Rebuild

## Concept

First of all, user must understand that any type of data patch as long as it's not a fixed and tested program, it will become a very dangerous movement. So proper engineering process including testing and script backup must be set in place to avoid if anything unexpected goes wrong.

Normally, if we want to rectify any of the policy data, there can be two approaches:

1. De-activate or update policy by API

If the cases are not much and there's business API to change, this is the more suggestive move. User can call cancellation API to cancel the policy and re-create a new policy by copying the data from original ones.

2. Delete or update the record by DB script

Before that, development must have a full understanding of our policy table structure. Also, user must bear in mind that the change might not only involve DB change, in some cases, ES index needs to be cleared as well. 


## Policy data patch process

If we are talking about API to change the data, then project team can do it by your own. However, if you want to run the db script, there will be special process posed from InsureMO side:

* Project team needs to raise special request to InsureMO Operation team to ask for an authorized account to access production database with read-only authority.
* Project team can prepare the dbscript according to the data in the database and run it in your development environment.
* Project team needs to raise another request to InsureMO Operation team to run the script in production.


## Policy data patch approach

The policy-related instance data patch usually consists of three steps: 
1.	Modify the data of the instance table.
2.	Modify the data of the log table.
3.	Rebuild the ES index.

<div class="docs-note"><span class="docs-admonitions-text">note</span>

Every SQL or script needs to mark how many lines are affected.

</div>

### Modify the Data of the Instance Table

You need to find the policy_id at t_pa_pl_policy through one of the proposal_no, policy_no, quotation_no.

````sql
SELECT t1.POLICY_ID FROM t_pa_pl_policy t1 WHERE t1.POLICY_NO = 'POTBTI00000003'
````

Then you can use this policy_id to find policy fields and write update SQL. Policy includes two types of fields. One is fixed-field which is in one of these tables: t_pa_pl_customer,t_pa_pl_coverage, etc; the other is dynamic fields in t_pa_pl_policy_element DYNAMIC_FIELDS column. You need to add JSON data to the old data. 

Example of updating fixed-field GENDER_CODE:

````sql
UPDATE t_pa_pl_customer t2 SET t2.GENDER_CODE = 'M' 
WHERE t2.POLICY_ID = (SELECT t1.POLICY_ID FROM t_pa_pl_policy t1 WHERE t1.POLICY_NO = 'POTBTI00000003');
````

Example of updating dynamic field IdNo:

 ````sql
UPDATE t_pa_pl_policy_element t2 SET t2.DYNAMIC_FIELDS = JSON_REPLACE(t2.DYNAMIC_FIELDS, '$.IdNo', 'IdNo1') 
WHERE t2.ELEMENT_TYPE = 'INSURED' 
AND t2.POLICY_ID = (SELECT t1.POLICY_ID FROM t_pa_pl_policy t1 WHERE t1.POLICY_NO = 'POTBTI00000003');
 ````

### Modify the Data of the Log Table

On the log table logic data patch like instance table, append **_l** to the instance table name.

Example of updating fixed-field GENDER_CODE:

````sql
UPDATE t_pa_pl_customer_l t2 SET t2.GENDER_CODE = 'M' 
WHERE t2.POLICY_ID = (SELECT t1.POLICY_ID FROM t_pa_pl_policy_l t1 WHERE t1.POLICY_NO = 'POTBTI00000003');
````

Example of updating dynamic field:

 ````sql
UPDATE t_pa_pl_policy_element_l t2 SET t2.DYNAMIC_FIELDS = JSON_REPLACE(t2.DYNAMIC_FIELDS, '$.IdNo', 'IdNo1') 
WHERE t2.ELEMENT_TYPE = 'INSURED' 
AND t2.POLICY_ID = (SELECT t1.POLICY_ID FROM t_pa_pl_policy_l t1 WHERE t1.POLICY_NO = 'POTBTI00000003');
 ````

Note: If there's any dynamic field to update, user can refer to below script for reference:

`
UPDATE t_prd_product_element SET DYNAMIC_FIELDS = JSON_SET(DYNAMIC_FIELDS, '$.FieldName', PRODUCT_ELEMENT_NAME)
WHERE ELEMENT_TYPE_ID =-15 AND PRODUCT_ELEMENT_NAME LIKE 'Iso%'
AND REPLACE(JSON_EXTRACT(DYNAMIC_FIELDS, '$.FieldName'), '"', '') != PRODUCT_ELEMENT_NAME ;
`

### Do Index Rebuild


If the data patch field is related to any of search API, user need to trigger index rebuild batch also to refresh the data.