# RBAC Data Migration

## `/data`

Exported CSVs from the elerium database are stored here  
  

`CompanyExport`: TwitchCompanies  
`DeveloperByCompanies`: Developer Mappings for Companies
`DeveloperExport`: TwitchDevelopers (users)  
`GameByCompany`: Game Mappings for Companies  
`GamesExport`: Twitch Games 

## `/data-export`

Raq SQL & Linq Files for easy reference to see how they got exported.

## `/scripts`

TS Scripts that use the data given in the CSVs to import into the RBAC Database:

### `/scripts/rbac.sql`

RBAC SQL Schema

### `/scripts/db.ts`

RBAC SQL Connection

### `/scripts/utils.ts`

RBAC SQL Helper Methods

### `/scripts/psql statements`

RAW SQL Statements for importing CSVs directly into the given RBAC Tables


## Run Order

For all files, when we get the export, ensure each file is UTF-8, LF, and delete the extra 3 lines at the end of each file.

For CompanyExport.csv, delete the headers.

In GamesExport.csv, Ensure game identifier "im-sorry-ill-do-whatever-you-want-she-said" is properly quoted

In GameByCompany.csv, Ensure these games do not have duplicates. The process is to copy the game id company pair and delete any other rows of the same game id with different company ids.

- 489302,106
- 489303,106
- 489304,106
- 417892,615
- 492977,411
- 492841,52
- 497883,733
- 68059,311
- 387995,130
- 496256,446
- 493143,599
- 65654,464
- 461423,106
- 31902,106
- 489069,194

search for and delete rows containing the following games from GameByCompany.csv:

- 31822
- 499970

Delete data from the following tables (i.e. `delete from user_roles`):

- user_roles
- twitch_developers
- role_resources
- role_permissions
- permissions
- roles

Note: the other tables do not have data we need to delete. rbac_clients and domains are excluded since they have been preseeded. you can choose to delete from them if you like.

Run the following command to avoid typing password for each psql prompt:

```bash
export PGPASSWORD=$(sandstorm get --profile twitch-rbac-prod --role-arn "arn:aws:iam::734326455073:role/sandstorm/production/templated/role/sandstorm-agent-devsite-rbac-production" devsite/rbac/production/admin_db_password)
```

** if any of the psql copy commands fail on a line, they have likely partially inserted data. fix the csv issue, delete from the table, and try again **

1) `psql -h rbac-production-master.cbt09tmxkqne.us-west-2.rds.amazonaws.com -p 5433 -d rbac -U rbac_admin rbac < scripts/rbac.sql`: This restores the DB Schema
1) `yarn create:0` and run each of the `psql` statements
1) `yarn create:domain`
1) `yarn create:client`

For each of the following steps, the node command will output a psql copy command that needs to be copied and run. Here's an example running `yarn create:permissions`:

```bash
> yarn create:permissions
yarn run v1.9.2
$ ./node_modules/.bin/ts-node scripts/create-permissions.ts
execute:
psql -h rbac-production-master.cbt09tmxkqne.us-west-2.rds.amazonaws.com -p 5433 -d rbac -U rbac_admin -c  "\COPY permissions (name,description,domain_id) FROM './permissions.csv' DELIMITER ',' CSV HEADER;"
```

In this example, the following `psql` command should be run:

```bash
psql -h rbac-production-master.cbt09tmxkqne.us-west-2.rds.amazonaws.com -p 5433 -d rbac -U rbac_admin -c  "\COPY permissions (name,description,domain_id) FROM './permissions.csv' DELIMITER ',' CSV HEADER;"
```

1) `yarn create:roles`
1) `yarn create:permissions`
1) `yarn create:role-relations`
1) `yarn create:role-resources`
1) `yarn create:role-permissions`
1) `yarn create:user-roles`

3284 is the magic number of games as of 8/16. a couple more may just be new games but we shouldn't have less or a lot more.

example curl in prod:
```
curl -X POST -H 'Content-Type: application/json' 'http://devsite-rbac.prod.us-west2.justin.tv/twirp/code.justin.tv.devrel.rbac.RBAC/GetAllGamesWithCompanies' -d '{"limit":100,"offset":0}'
```
