-- step 1

change the Asia/Shanghai timezone to the actual local timezone

cat << 'EOF' > change_retention_jobs_formulation
from system.jobs
where ends_with(job_name, "_retention")
select "create or replace job `"
|| job_name
|| "` schedule = \"0 0 * * * Asia/Shanghai\" nodegroup = \"job\" as "
|| statement
|| ";";
EOF

cat change_retention_jobs_formulation | scopeql -c - | awk -F'|' 'NR > 5 && NF > 1 {print $2}' | sed 's/^ *//' | tee change_retention_jobs_execution
cat change_retention_jobs_execution | scopeql -c -

-- step 2

from system.tables where table_name = "metering" select schema_name;

-- make sure the value is `t1`

-- then check if these indexes and views exist
from system.indexes where table_name = "metering" where index_name = "time_day" or index_name = "workspaceUUID_range";
from system.views where schema_name="t1";

-- if not exists
create range index time_day on t1.metering (floor(time, 24,"hour"));
create range index `workspaceUUID_range` on t1.metering(`workspaceUUID`);
create view t1._metering as from t1.metering distinct on`workspaceUUID`, __mergeid by __update_time desc;

-- alter table
alter table t1.metering cluster by (floor(time, 24, "hour"),`workspaceUUID`);
alter table t1.metering drop distinct key;

-- step 3

cat << 'EOF' > change_o_schema_formulation
from system.tables
where schema_name != "system"
and table_name = "o" or starts_with(table_name, "o_")
select "create range index time_day on "
|| schema_name || ".`" || table_name || "` (floor(time, 24, \"hour\")); "
|| "alter table " || schema_name || ".`" || table_name
|| "` cluster by (floor(time, 24, \"hour\"));"
EOF

cat change_o_schema_formulation | scopeql -c - | awk -F'|' 'NR > 5 && NF > 1 {print $2}' | sed 's/^ *//' | tee change_o_schema_execution
cat change_o_schema_execution | scopeql -c -

from system.indexes where index_name = "time_day" and starts_with(table_name, "o");
from system.tables where cluster_by = ["floor(time, 24, 'hour')"] and starts_with(table_name, "o") select database_name, schema_name , table_name;

-- step 4 remedy

scopeql -c 'from system.tables where table_name = "o"'

cat << 'EOF' > change_o_schema_formulation
from system.tables
where schema_name != "system"
and table_name = "o"
select "create range index time_day on "
|| schema_name || ".`" || table_name || "` (floor(time, 24, \"hour\")); "
|| "alter table " || schema_name || ".`" || table_name
|| "` cluster by (floor(time, 24, \"hour\"));"
EOF

cat change_o_schema_formulation | scopeql -c - | awk -F'|' 'NR > 6 && NF > 1 {print $2}' | sed 's/^ *//' | tee change_o_schema_execution
cat change_o_schema_execution | scopeql -c -

from system.indexes where index_name = "time_day" and starts_with(table_name, "o");
from system.tables where cluster_by = ["floor(time, 24, 'hour')"] and starts_with(table_name, "o") select database_name, schema_name , table_name;