CREATE OR REPLACE PROCEDURE "y4a_fin_analyst"."hungcht_update_table_database_monitoring"()
AS $BODY$
DECLARE
_sql_state TEXT;
_message TEXT;
BEGIN
    -- Step 1: insert into proc monitoring
    insert into "y4a_fin_analyst"."hungcht_proc_monitoring"
    (proc_name, run_date, end_date, status, err_message, err_sqlstate)
    VALUES
    ('hungcht_update_table_database_monitoring',
    current_timestamp AT TIME ZONE 'Asia/Bangkok',
    null,null,null,null);
    commit;
    
    -- Step 2: Insert new table created
    INSERT into y4a_fin_analyst.hungcht_table_database_monitoring
    select * from y4a_fin_analyst.hungcht_check_table A
    where not exists (select 1 from y4a_fin_analyst.hungcht_table_database_monitoring b
    where a.table_name = b.table_name
    and a.schema_name = b.schema_name
    and a.owner_name = b.owner_name);
    commit;
    
    -- Step 3: Update ROW_COUNT value and updated time
    TRUNCATE y4a_fin_analyst.hungcht_table_database_monitoring_tmp;
    COMMIT;
    
    INSERT INTO y4a_fin_analyst.hungcht_table_database_monitoring_tmp 
    select
    a.table_name,
    a.schema_name,
    a.owner_name,
    a.row_count,
    b."row_count" as last_row_count,
    a."row_count" - b."row_count" as row_change_cnt,
    COALESCE(b.table_type,a.table_type) as table_type,
    b.create_date,
    a.update_date
    from y4a_fin_analyst.hungcht_check_table A
    join y4a_fin_analyst.hungcht_table_database_monitoring b
    on a.table_name = b.table_name
    and a.schema_name = b.schema_name
    and a.owner_name = b.owner_name
    and a."row_count" <> b."row_count";
    commit;
    
    delete from y4a_fin_analyst.hungcht_table_database_monitoring A
    where exists (select 1 from y4a_fin_analyst.hungcht_table_database_monitoring_tmp b
    where a."table_name" = b."table_name"
    and a."schema_name" = b."schema_name"
    and a."owner_name" = b."owner_name");
    commit;
    
    insert into y4a_fin_analyst.hungcht_table_database_monitoring
    select * from y4a_fin_analyst.hungcht_table_database_monitoring_tmp;
    commit;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
_message := MESSAGE_TEXT,
_sql_state := RETURNED_SQLSTATE;
    		update y4a_fin_analyst.hungcht_proc_monitoring
    		set
    		end_date = current_timestamp AT TIME ZONE 'Asia/Bangkok',
    		status = 'error',
    		err_message = _message,
    		err_sqlstate = _sql_state
    		where proc_name = 'hungcht_update_table_database_monitoring'
    		and end_date is null;
    		commit;
END;
$BODY$
LANGUAGE plpgsql