---
title: "iPhones: Complement or Replacement"
author: "Mark J Handel"
date: "2022-10-11"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

```{r packages}
library(tidyverse)
library(magrittr)
library(dbplyr)
library(RSQLite)
library(ggplot2)
```

```{r connect_lightroom}
lrcat <- DBI::dbConnect(RSQLite::SQLite(),
                       dbname="/Users/handel/temp/lrcat_copy.lrcat")
```

```{sql baseline_data,connection=lrcat, output.var="raw_info"}
SELECT
  AgLibraryFile.id_local, 
  AgLibraryRootFolder.name AS rootfoldername,
  AgLibraryFolder.pathFromRoot AS PathFromRoot,
  AgLibraryFile.baseName AS basename,
  AgLibraryFile.extension AS filetype,
  Adobe_images.captureTime as capturetime,
  COALESCE(Adobe_images.rating,0) AS rating,
  Adobe_images.colorLabels AS colorlabel,
  Adobe_images.touchCount AS touchcount,
  AgHarvestedExifMetadata.focalLength AS focallength, 
  -- focal length mm
  ROUND(AgHarvestedExifMetadata.aperture,3) AS aperture,
  AgHarvestedExifMetadata.shutterSpeed AS shutterspeed, 
  -- format not understood
  ROUND(AgHarvestedExifMetadata.isoSpeedRating,0) AS iso,
  COALESCE(AgInternedExifCameraModel.value, "No EXIF") AS camera,
  COALESCE(AgInternedExifLens.value, "Unknown lens") AS lens
FROM AgLibraryFile 
  -- every image in catalog has an entry in this table
LEFT JOIN AgLibraryFolder 
  ON AgLibraryFolder.id_local=AgLibraryFile.folder
  -- I fundamentally don't understand how these joins are specified.
LEFT JOIN AgLibraryRootFolder 
  ON AgLibraryRootFolder.id_local=AgLibraryFolder.rootFolder
LEFT JOIN Adobe_images 
  ON AgLibraryFile.id_local=Adobe_images.rootFile
LEFT JOIN AgHarvestedExifMetadata 
  ON AgHarvestedExifMetadata.image = Adobe_images.id_local
LEFT JOIN AgInternedExifCameraModel 
  ON AgInternedExifCameraModel.id_local = AgHarvestedExifMetadata.cameraModelRef
LEFT JOIN AgInternedExifLens
  ON AgInternedExifLens.id_local = AgHarvestedExifMetadata.lensRef
```

```{r clean_data}

raw_info <- raw_info %>% 
  tibble() %>%
  mutate(capture_date = as.Date(ymd_hms(capturetime)))

drop_list <- raw_info %>% 
    filter(camera %in% c('Canon EOS 5D Mark III'), 
           capture_date < '2005-01-01') %>%
  select(id_local, capture_date)

clean_info <- raw_info %>%
  filter(! id_local %in% drop_list$id_local) %>%
  mutate(camera_type = recode(camera,
                              "Canon EOS 5D Mark II" = "DLSR",
                              "Canon EOS 5D Mark III" = "DSLR",
                              "Canon EOS 60D" = "DSLR",
                              "Canon PowerShot S100" = "Digital",
                              "DSC-RX1RM2" = "EVFF",
                              "Canon EOS 20D" = "DSLR",
                              "E990" = "Digital",
                              "Canon EOS M" = "Digital",
                              "iPad Pro (10.5-inch)" = "Tablet",
                              "iPhone 11 Pro" = "Phone",
                              "iPhone 13 Pro" = "Phone",
                              "iPhone 3GS" = "Phone",
                              "iPhone 4" = "Phone",
                              "iPhone 4S" = "Phone",
                              "iPhone 5" = "Phone",
                              "iPhone 5s" ="Phone",
                              "iPhone 6 Plus" = "Phone",
                              "iPhone 7 Plus" = "Phone",
                              "iPhone X" = "Phone",
                              "LEICA Q (Typ 116)" = "EVFF",
                              "LEICA Q2" = "EVFF",
                              "NIKON D100" = "DSLR",
                              "iPhone XS" = "Phone",
                              "Pixel" = "Phone",
                              "Ray-Ban Stories" = "Glasses",
                              "SM-C200" = "Phone",
                              "SM-G920V" = "Phone",
                              "SM-N920V" = "Phone",
                              "X100F" = "EVFF",
                              # This is not entirely correct.
                              "Unknown Camera" = "Digital",
                              "iPhone" = "Phone",
                              .default = "Digital"))
```

```{r base_numbers}

first_phone <- clean_info %>%
  filter(camera_type %in% "Phone",
         !is.na(capture_date),
         capture_date > ymd("2000-01-01")) %$%
  min(first_phone$capture_date, na.rm=TRUE)

quarterly <- clean_info %>%
  filter(camera_type %in% c("Phone", "EVFF", "DSLR"),
         capture_date > first_phone) %>%
  mutate(quarter = quarter(capture_date, type = "year.quarter")) %>%
  filter(quarter > 2005) %>%
  group_by(quarter, camera_type) %>%
  summarize(cnt = n(),.groups = "drop_last")
```


```{r type_time_plot}


ggplot(data=quarterly, 
       aes(quarter, cnt, color=camera_type)) +
  geom_line(stat='identity') +
  xlab("Date (by quarter)") +
  ylab("Raw count photos")
```


