# TCI Migration (Khorasan Razavi)
We want to move data of following tables from one server (A: Khorasan Razavi) to another server (B: TCI).

User tables:
- users
- user_attrs
- normal_users

Group table; we only need this table for group mapping:
- groups

## Step 1 (Server A)
Export tables data from server **A** as CSV files and import them into server **B**.

Create a directory in `/tmp/` to save our export files:
```
# rm -rf /tmp/migration_a/
# mkdir /tmp/migration_a/
# chmod 777 /tmp/migration_a/
```

Export User tables data:
```
IBSng=# COPY (SELECT user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date FROM users) TO '/tmp/migration_a/migration_a_users.csv' WITH DELIMITER ',';

IBSng=# COPY (SELECT user_id, attr_name, attr_value FROM user_attrs) TO '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';

IBSng=# COPY (SELECT user_id, normal_username, normal_password, second_normal_username FROM normal_users) TO '/tmp/migration_a/migration_a_normal_users.csv' WITH DELIMITER ',';
```

Export Group table data:
```
IBSng=# COPY (SELECT group_id, group_name, status, isp_id, comment FROM groups) TO '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';
```

Now that we have exported data from the database, we need to move them to server **B**:
```
# cd /tmp/
# tar zcf migration_a.tar.gz migration_a
# rm -rf migration_a
```

Now we need to move the file `/tmp/migration_a.tar.gz` to server **B** by SCP, FTP, etc.

## Step 2 (Server B)
Suppose we moved exported data files which was generated in step 1 to `/tmp/` directory, now we need to extract them:
```
# cd /tmp/
# rm -rf migration_a
# tar xf migration_a.tar.gz
# rm -rf migration_a.tar.gz
```

### Step 2.1
Now we need to create temporary tables and fill them with exported data from server A.

Create temporary User tables:
```
IBSng=# CREATE TABLE migration_a_users AS SELECT user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date FROM users LIMIT 1;
IBSng=# TRUNCATE migration_a_users;

IBSng=# CREATE TABLE migration_a_user_attrs AS SELECT user_id, attr_name, attr_value FROM user_attrs LIMIT 1;
IBSng=# TRUNCATE migration_a_user_attrs;

IBSng=# CREATE TABLE migration_a_normal_users AS SELECT user_id, normal_username, normal_password, second_normal_username FROM normal_users LIMIT 1;
IBSng=# TRUNCATE migration_a_normal_users;
```

Create temporary Group table:
```
IBSng=# CREATE TABLE migration_a_groups AS SELECT group_id, group_name, status, isp_id, comment FROM groups LIMIT 1;
IBSng=# TRUNCATE migration_a_groups;
```

### Step 2.2
Import data from CSV files of server A to temporary tables we just created;

Import User tables data:
```
IBSng=# COPY migration_a_users (user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date) FROM '/tmp/migration_a/migration_a_users.csv' WITH DELIMITER ',';

IBSng=# COPY migration_a_user_attrs (user_id, attr_name, attr_value) FROM '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';

IBSng=# COPY migration_a_normal_users (user_id, normal_username, normal_password, second_normal_username) FROM '/tmp/migration_a/migration_a_normal_users.csv' WITH DELIMITER ',';
```

Import Group table data:
```
IBSng=# COPY migration_a_groups (group_id, group_name, status, isp_id, comment) FROM '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';
```

### Step 2.3
For this migration we need to clean our imported data according to following requirements:

- Convert all user passwords to lower case
- Remove users with no username
- We need to move only some specific attributes of users

#### Step 2.3.1
Convert all user passwords to lower case:
```
IBSng=# UPDATE migration_a_normal_users SET normal_password = LOWER(normal_password);
```

#### Step 2.3.2
Remove users with no username:
```
IBSng=# CREATE UNIQUE INDEX migration_a_users_user_id_idx ON migration_a_users (user_id);
IBSng=# CREATE UNIQUE INDEX migration_a_normal_users_user_id_idx ON migration_a_normal_users (user_id);

IBSng=# CREATE TABLE migration_a_no_username AS SELECT user_id FROM migration_a_users WHERE NOT EXISTS (SELECT migration_a_normal_users.user_id FROM migration_a_normal_users WHERE migration_a_users.user_id = migration_a_normal_users.user_id);
IBSng=# CREATE UNIQUE INDEX migration_a_no_username_user_id_idx ON migration_a_no_username (user_id);

IBSng=# DELETE FROM migration_a_users WHERE EXISTS (SELECT migration_a_no_username.user_id FROM migration_a_no_username WHERE migration_a_users.user_id = migration_a_no_username.user_id);
IBSng=# DELETE FROM migration_a_user_attrs WHERE EXISTS (SELECT migration_a_no_username.user_id FROM migration_a_no_username WHERE migration_a_user_attrs.user_id = migration_a_no_username.user_id);

IBSng=# DROP TABLE migration_a_no_username;
```

> Note: We need to execute following queries, because we may have dangling records in user tables:
```
IBSng=# DELETE FROM migration_a_normal_users WHERE NOT EXISTS (SELECT migration_a_users.user_id FROM migration_a_users WHERE migration_a_normal_users.user_id = migration_a_users.user_id);
IBSng=# DELETE FROM migration_a_user_attrs WHERE NOT EXISTS (SELECT migration_a_users.user_id FROM migration_a_users WHERE migration_a_user_attrs.user_id = migration_a_users.user_id);
```

#### Step 2.3.3
We need to move only some specific attributes of users, the attributes are:

- abs_exp_date
- address
- assign_dns
- assign_ip
- assign_route_ip
- cell_phone
- comment
- custom_field_center
- custom_field_dedicated_sim_number
- custom_field_document_number
- custom_field_establishing_date
- custom_field_melli_code
- email
- first_login
- lock
- multi_login
- name
- night_free
- phone
- postal_code
- radius_attrs
- real_first_login

Write attribute names to `/tmp/migration_a/migration_a_attr_names.csv`:
```
cat << EOF > /tmp/migration_a/migration_a_attr_names.csv
abs_exp_date
address
assign_dns
assign_ip
assign_route_ip
cell_phone
comment
custom_field_center
custom_field_dedicated_sim_number
custom_field_document_number
custom_field_establishing_date
custom_field_melli_code
email
first_login
lock
multi_login
name
night_free
phone
postal_code
radius_attrs
real_first_login
EOF
```

```
IBSng=# CREATE TABLE migration_a_attr_names AS SELECT attr_name FROM user_attrs LIMIT 1;
IBSng=# TRUNCATE migration_a_attr_names;
IBSng=# COPY migration_a_attr_names (attr_name) FROM '/tmp/migration_a/migration_a_attr_names.csv' WITH DELIMITER ',';
IBSng=# CREATE UNIQUE INDEX migration_a_attr_names_attr_name_idx ON migration_a_attr_names (attr_name);

IBSng=# DELETE FROM migration_a_user_attrs WHERE NOT EXISTS (SELECT migration_a_attr_names.attr_name FROM migration_a_attr_names WHERE migration_a_user_attrs.attr_name = migration_a_attr_names.attr_name);

IBSng=# DROP TABLE migration_a_attr_names;
```

### Step 2.4
To make column ids of temporary tables compatible with server B, we create a new column named `<id_column>_new` and rename the current column name to `<id_column>_old`; then we fill `<id_column>_new` with corresponded values.

Add/Rename User tables ID columns:
```
IBSng=# ALTER TABLE migration_a_users RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN user_id_new bigint;
IBSng=# ALTER TABLE migration_a_users RENAME COLUMN group_id TO group_id_old;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_id_new bigint;

IBSng=# ALTER TABLE migration_a_user_attrs RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_user_attrs ADD COLUMN user_id_new bigint;

IBSng=# ALTER TABLE migration_a_normal_users RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_normal_users ADD COLUMN user_id_new bigint;
```

Add/Rename Group tables ID columns:
```
IBSng=# ALTER TABLE migration_a_groups RENAME COLUMN group_id TO group_id_old;
IBSng=# ALTER TABLE migration_a_groups ADD COLUMN group_id_new bigint;
```

### Step 2.5
Fill temporary tables with new IDs.

Generate new IDs for User table:
```
IBSng=# UPDATE migration_a_users SET user_id_new = nextval('users_user_id_seq');
```

Suppose we have a csv file named `migration_a_group_mapping.csv` with two columns in `/tmp/migration_a/` directory:

- old group name
- new group ID

> Note: The CSV file has no header

Now we need to import group mappings to our database (server B):
```
IBSng=# CREATE TABLE migration_a_group_mapping AS SELECT group_name, group_id FROM groups LIMIT 1;
IBSng=# TRUNCATE migration_a_group_mapping;
IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_name TO group_name_old;
IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_id TO group_id_new;

IBSng=# COPY migration_a_group_mapping (group_name_old, group_id_new) FROM '/tmp/migration_a/migration_a_group_mapping.csv' WITH DELIMITER ',';
```

Fill Group table with new IDs:
```
IBSng=# UPDATE migration_a_groups SET group_id_new = migration_a_group_mapping.group_id_new FROM migration_a_group_mapping WHERE migration_a_groups.group_name = migration_a_group_mapping.group_name_old;
```

### Step 2.6
Map old IDs to new IDs and fill corresponded values in temporary tables.

Map User tables old IDs to new IDs:
```
IBSng=# UPDATE migration_a_users SET group_id_new = migration_a_groups.group_id_new FROM migration_a_groups WHERE migration_a_users.group_id_old = migration_a_groups.group_id_old;

IBSng=# UPDATE migration_a_user_attrs SET user_id_new = migration_a_users.user_id_new FROM migration_a_users WHERE migration_a_user_attrs.user_id_old = migration_a_users.user_id_old;

IBSng=# UPDATE migration_a_normal_users SET user_id_new = migration_a_users.user_id_new FROM migration_a_users WHERE migration_a_normal_users.user_id_old = migration_a_users.user_id_old;
```

> Note: We need to fill `group_id_new` column of `migration_a_users` table with default group of **unknown-group** with ID of **3752**, where we have no group mapping:
```
IBSng=# UPDATE migration_a_users SET group_id_new = 3752 WHERE group_id_new is NULL;
```

## Step 3
Update isp_id of records:
```
IBSng=# UPDATE migration_a_users SET isp_id = <province_isp_id>;
```

> Note: ISP ID of Khorasan Razavi is **40**

## Step 4
Delete users from temporary tables (server A) with the same normal_username in server B:
```
IBSng=# CREATE TABLE migration_a_users_dup AS SELECT user_id_old as user_id_dup FROM migration_a_normal_users WHERE normal_username IN (SELECT normal_username FROM normal_users);

IBSng=# DELETE FROM migration_a_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DELETE FROM migration_a_user_attrs WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DELETE FROM migration_a_normal_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DROP TABLE migration_a_users_dup;
```

## Step 5
TODO: Backup from the database and especially a separate backup from tables which are going to be changed.

### Step 5.1
Insert temporary tables records into main tables.

Insert User tables records:
```
IBSng=# INSERT INTO users (user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date) SELECT user_id_new, isp_id, credit, deposit, deposit_recharge, status, group_id_new, creation_date, nearest_exp_date FROM migration_a_users;

IBSng=# ALTER TABLE user_attrs DISABLE TRIGGER update_user_nearest_exp_date_trigger;
IBSng=# INSERT INTO user_attrs (user_id, attr_name, attr_value) SELECT user_id_new, attr_name, attr_value FROM migration_a_user_attrs;
IBSng=# ALTER TABLE user_attrs ENABLE TRIGGER update_user_nearest_exp_date_trigger;

IBSng=# ALTER TABLE normal_users DISABLE TRIGGER users_prevent_duplicate_username_trigger;
IBSng=# INSERT INTO normal_users (user_id, normal_username, normal_password, second_normal_username) SELECT user_id_new, normal_username, normal_password, second_normal_username FROM migration_a_normal_users;
IBSng=# ALTER TABLE normal_users ENABLE TRIGGER users_prevent_duplicate_username_trigger;
```

> Note: In inserting records to **normal_users** table, remember to disable the trigger of the table; otherwise it takes a very long time to insert records.

## Step 7
Now that we completed the migration, we need to reload new users.

### Step 7.1
Export IDs of new users.

Export User IDs:
```
IBSng=# COPY (SELECT user_id_new FROM migration_a_users) TO '/tmp/migration_a/migration_a_users_reload.csv' WITH DELIMITER ',';
```

### Step 7.2
Run reload script for new users. These scripts should be run on master App of IBSng.

Move `reload_users_by_csv.py` to `/opt/` and then run User reload script:
```
# python /usr/local/IBSng/addons/client/client.py -u <web_panel_username> -p <web_panel_password> -i /opt/reload_users_by_csv.py
```

> Source code of `realod_users_by_csv.py` script:
```python
from core.user import user_main

with open("/tmp/migration_a/migration_a_users_reload.csv", "r") as fd:
    user_ids = []
    for line in fd.readlines():
        user_id_str = line.strip("\r\n ")
        if user_id_str:
            user_ids.append(long(user_id_str))
            if len(user_ids) == 10:
                user_main.getActionManager().broadcastChange(user_ids)
                user_ids = []

    user_main.getActionManager().broadcastChange(user_ids)
```

## Notes
Creat mapping files of migrated data for third party applications: (user_id_old, user_id_new, group_name_old, group_name_new, abs_exp_date) and (group_name_old, group_name_new)

Export users summary:
```
IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_old text;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_new text;

IBSng=# UPDATE migration_a_users SET group_name_old = migration_a_groups.group_name FROM migration_a_groups WHERE migration_a_users.group_id_old = migration_a_groups.group_id_old;
IBSng=# UPDATE migration_a_users SET group_name_new = groups.group_name FROM groups WHERE migration_a_users.group_id_new = groups.group_id;

IBSng=# CREATE TABLE migration_a_users_summary AS SELECT user_id_old, user_id_new, group_name_old, group_name_new FROM migration_a_users;
IBSng=# ALTER TABLE migration_a_users_summary ADD COLUMN abs_exp_date text;
IBSng=# UPDATE migration_a_users_summary SET abs_exp_date = migration_a_user_attrs.attr_value FROM migration_a_user_attrs WHERe migration_a_users_summary.user_id_old = migration_a_user_attrs.user_id_old AND migration_a_user_attrs.attr_name = 'abs_exp_date';

IBSng=# COPY (SELECT user_id_old, user_id_new, group_name_old, group_name_new, abs_exp_date FROM migration_a_users_summary) TO '/tmp/migration_a/migration_a_users_summary.csv' WITH DELIMITER ',' CSV HEADER;
```

Export groups summary:
```
IBSng=# ALTER TABLE migration_a_group_mapping ADD COLUMN group_name_new text;
IBSng=# UPDATE migration_a_group_mapping SET group_name_new = groups.group_name FROM groups WHERE migration_a_group_mapping.group_id_new = groups.group_id;

IBSng=# COPY (SELECT group_name_old, group_name_new FROM migration_a_group_mapping) TO '/tmp/migration_a/migration_a_groups_summary.csv' WITH DELIMITER ',' CSV HEADER;
```