# SMBUD Delivery 2

## Data generation
To generate data we used this website: https://extendsclass.com/json-generator.html. We write down the document generator, but since it was not possible to iterate on random values we decided to generate several json files. Every json files contains 20 documents, in which every person has a fixed number of vaccine doses and tests. For example we created a file with 20 docs, each one with 0 vaccine doses and 1 test, and we did it for doses = [0,1,2] and tests = [0,1,2]. Phone number was generated with italian standard mobile phone number. As place types we identified hospitals, pharmacies and test centers where people can both get a test or get a vaccine.

### Initial document generator

repeat(20,
{
	id: random(),
	name:firstname(),
	surname:lastname(), 
	age: random(12,100),
	job: choice('Student', 'Employee', 'Retired','Freelance'),
    phone_number: random(300000000,399000000),
    city: city(),
	emergency_contact:{
		name: firstname(),
		surname:lastname(),
		phone_number:random(300000000,399000000)
	},
	vaccines: 
  repeat(2,
      {
		brand: choice('Pfizer', 'Moderna', 'J&J'),
        lot: random(100000,999999), 
		production_date:{"$date":function randomDate() {
          var start = new Date('2020-10-01T00:00:00');
          var end = new Date('2021-03-07T00:00:00');
   		  return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
}},
    
    
		date:{"$date":function randomDate() {
          var start = new Date('2021-03-07T00:00:00');
          var end = new Date('2021-12-07T00:00:00');
   		  return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
}},
		place:{
			city:city(),
			GPS_position_lat:randomFloat(-180,180),
          	GPS_position_long:randomFloat(-180,180),
          	type:choice('Hospital','test center', 'pharmacy')
		},
		doctor:{
			name:firstname(),
			surname:lastname(), 
			role:choice('Doctor','Nurse')
		}
	}
	),
	tests: 
   repeat(2,
      {
        test_number:random(),
		result:randomBool(),
		date:{"$date":function randomDate() {
          var start = new Date('2020-10-01T00:00:00');
          var end = new Date('2021-12-07T00:00:00');
   return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
}},
		place:{
			city:city(),
			GPS_position_lat:randomFloat(-180,180),
          	GPS_position_long:randomFloat(-180,180),
          	type:choice('Hospital','test center', 'pharmacy')
		},
		doctor:{
			name:firstname(),
			surname:lastname(), 
			role:choice('Doctor','Nurse')
		}
	}
          )
      
}
)

## Commands

Start with

use covid_data

db.certificates.

### Add new person

db.certificates.insertOne(
    {
      "id": "61aca01ff0bb73943e8ac073",
      "name": "Blankenship",
      "surname": "Harding",
      "age": 70,
      "job": "Student",
      "phone_number": "[phone]",
      "city": "Englevale",
      "emergency_contact": {
        "name": "Crawford",
        "surname": "Alvarado",
        "phone_number": "[phone]",
        "address": "Herzl Street"
      },
      "vaccines": [],
      "tests": []
    }
)

### Add a new test for an existing person

db.certificates.updateOne(
    {
        "id": "61aca01ff0bb73943e8ac073"
    },
    {
        $push: {
            "tests": {
              "test_number": 0,
              "result": false,
              "date": {
                  "$date": "Mon Dec 13 2021 00:15:38 GMT+0100 (hora estándar de Europa central)"
              },
              "place": {
                "name": "",
                "address": "Lawn Court",
                "city": "Charco",
                "GPS_position": "",
                "type": "Hospital"
              },
              "doctor": {
                "name": "Tucker",
                "surname": "Bartlett",
                "role": "Doctor"
              }
            }
        }
    }
)

### Add a new vaccine for an existing person

db.certificates.updateOne(
    {
        "id": "61aca01ff0bb73943e8ac073"
    },
    {
        $push: {
            "vaccines": {
              "brand": "Moderna",
              "lot": 151347,
              "production_date": {
                  "$date": "Thu Dec 09 2021 01:03:57 GMT+0100 (hora estándar de Europa central)"
              },
              "dose": 0,
              "date": {
                  "$date": "Sat Mar 27 2021 23:00:46 GMT+0100 (hora estándar de Europa central)"
              },
              "place": {
                "name": "",
                "address": "Moffat Street",
                "city": "Hackneyville",
                "GPS_position": "",
                "type": "test center"
              },
              "doctor": {
                "name": "Smith",
                "surname": "Franks",
                "role": "Nurse"
              }
            }
        }
    }
)

## Queries

### Count the number of people with the full vaccination schema

db.certificates.find(
    { vaccines: { $size: 2 } }
).count()

### Does a person has the 2 doses + 14 days and no positive tests (green pass active)

> To check if a green pass is valid or not
<!--

db.certificates.find(
    { 
        id: "XXXX", $and: [
            { vaccines: { $size: 2 }}, 
            { tests: {
                $not: {
                        $elemMatch: {
                            result: true,
                            date: { $gt: new Date(ISODate().getTime() - 1000 * 60 * 60 * 24 * 14) }
                        }            
                    }
                }
            }
        ]
    }
)

db.certificates.find(
    { 
        id: "XXXX", $and: [
            { vaccines: { $size: 2 }}, 
            { or: [
                    {
                        $expr: { $neq: [{ $last: "$tests.result" }, true]}
                    },
                    {
                        $expr: { $lt: [{$last: "$tests.date"}, new Date(ISODate().getTime() - 1000 * 60 * 60 * 24 * 14)]}
                    }
                ]
            }   
                
        ]
    }
)

### People that are currently positive (have a positive test less than 14 days prior)

<!-- Checks any te

db.certificates.find(
    { 
        tests: {
            $elemMatch: {
                result: true,
                date: { $gt: new Date(ISODate().getTime() - 1000 * 60 * 60 * 24 * 14) }
            }            
        }
    }
)

db.certificates.find(
    { 
        $and: [
            {
                $expr: { $eq: [{ $last: "$tests.result" }, true]}
            },
            {
                $expr: {$gt: [{$last: "$tests.date"}, new Date(ISODate().getTime() - 1000 * 60 * 60 * 24 * 14)]}
            }
            
        ]
    }
)

### total number of people that got tested in a certain months

// db.certificates.aggregate([{"$match": {
//      $or:[
//           {"tests.0.date" : {"$gt" :  ISODate("2021-11-01T00:00:00Z"),"$lt": ISODate("2021-12-01T00:00Z")}},
//           {"tests.1.date" : {"$gt" :  ISODate("2021-11-01T00:00:00Z"),"$lt": ISODate("2021-12-01T00:00Z")}},
//          ]  
//     }},
//     {"$group":{
//          "_id" : true,
//          "count": {"$sum" :1}
// }}])

// This is another version of the same query that can account for any number of tests
db.certificates.aggregate([
    {
        "$match": {
            tests: {
                    $elemMatch: {
                        date: {
                            $gt: ISODate("2021-11-01T00:00:00Z"),
                            $lt: ISODate("2021-12-01T00:00:00Z")
                        }
                    }            
                }
            }
    },
    {
        "$group":{
             "_id" : true,
             "count": {"$sum" :1}
        }
    }
])

### Proportion of positive vs total tests

db.certificates.aggregate(
    [
        {$unwind: "$tests"}, 
        {$group : { _id : "$tests.result", count: { $sum: 1 } }},
    ]
)

// Will return
{ _id: false, count: 96 }
{ _id: true, count: 84 }

### Number of vaccines per type place

// db.Covid_Coll.aggregate([{"$match": {
//   $or:[
//       {"vaccines.0.place.type" : {"$eq" : "Hospital"}},
//       {"vaccines.1.place.type" : {"$eq" : "Hospital"}},
//     ]  
//     }},
//     {"$group":{
//           "_id" : true,
//           "count": {"$sum" :1}
// }}])


// This version gets the number of vaccines per hospital - test center and pharmacy
db.certificates.aggregate(
    [
        {$unwind: "$vaccines"}, 
        {$group : { _id : "$vaccines.place.type", count: { $sum: 1 } }}
    ]
)