{
    "$schema": "http://json-schema.org/draft-07/schema#",
    "title": "Generated schema for Root",
    "type": "object",
    "properties": {
        "color": {
            "type": "string"
        },
        "isTrashed": {
            "type": "boolean"
        },
        "isPinned": {
            "type": "boolean"
        },
        "isArchived": {
            "type": "boolean"
        },
        "textContent": {
            "type": "string"
        },
        "title": {
            "type": "string"
        },
        "userEditedTimestampUsec": {
            "type": "number"
        },
        "createdTimestampUsec": {
            "type": "number"
        },
        "textContentHtml": {
            "type": "string"
        }
    },
    "required": [
        "color",
        "isTrashed",
        "isPinned",
        "isArchived",
        "textContent",
        "title",
        "userEditedTimestampUsec",
        "createdTimestampUsec",
        "textContentHtml"
    ]
}

bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
my_dataset.raw_keep_entry \
"gs://y-tsuzaki-analyze-keep/Keep/*.json"

tsuzaki92@cloudshell:~ (flowing-capsule-445508-d1)$ bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
my_dataset.raw_keep_entry \
"gs://y-tsuzaki-analyze-keep/Keep/*.json"
Waiting on bqjob_r39990fa72f6e7b7c_00000193ed9fad63_1 ... (0s) Current status: DONE   
BigQuery error in load operation: Error processing job 'flowing-capsule-445508-d1:bqjob_r39990fa72f6e7b7c_00000193ed9fad63_1': Error while
reading data, error message: JSON table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[]
collection for more details. File: gs://y-tsuzaki-analyze-keep/Keep/2023-10-31T14_42_19.705+09_00.json
Failure details:
- Error while reading data, error message: JSON processing
encountered too many errors, giving up. Rows: 64; errors: 1; max
bad: 0; error percent: 0
- gs://y-tsuzaki-analyze-
keep/Keep/2023-10-31T14_42_19.705+09_00.json: Error while reading
data, error message: JSON parsing error in row starting at position
0: No such field: labels. File: gs://y-tsuzaki-analyze-
keep/Keep/2023-10-31T14_42_19.705+09_00.json

tsuzaki92@cloudshell:~ (flowing-capsule-445508-d1)$ mkdir json_files
tsuzaki92@cloudshell:~ (flowing-capsule-445508-d1)$ gsutil cp gs://y-tsuzaki-analyze-keep/Keep/*.json ./json_files/
tsuzaki92@cloudshell:~ (flowing-capsule-445508-d1)$ cat ./json_files/*.json | jq -c '.' > combined.ndjson
tsuzaki92@cloudshell:~ (flowing-capsule-445508-d1)$ gsutil cp combined.ndjson gs://y-tsuzaki-analyze-keep/Keep_combined/combined.ndjson

bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
my_dataset.raw_keep_entry \
"gs://y-tsuzaki-analyze-keep/Keep_combined/combined.ndjson"

with import_entry as (
  SELECT
    createdTimestampUsec,
    TIMESTAMP_MICROS(createdTimestampUsec) AS created_at,
    title,
    textContent,
    isTrashed
  FROM
    `flowing-capsule-445508-d1.my_dataset.raw_keep_entry`
  ORDER BY
    createdTimestampUsec
), import_label_data as (
  select 
    createdTimestampUsec,
    is_daiary
  from 
    `flowing-capsule-445508-d1.my_dataset.label_data`
  where is_daiary is not null
),
filter_by_daiary_label as (
  select
    import_entry.*
  from
    import_entry
  inner join
    import_label_data
    on import_entry.createdTimestampUsec = import_label_data.createdTimestampUsec
)
select * from filter_by_daiary_label order by createdTimestampUsec

CREATE OR REPLACE MODEL `flowing-capsule-445508-d1.my_dataset.my_gemini15_flash`
REMOTE WITH CONNECTION `flowing-capsule-445508-d1.us-west1.ml_connection`
OPTIONS (ENDPOINT = 'gemini-1.5-flash');

SELECT *
FROM
 ML.GENERATE_TEXT(
   MODEL my_dataset.my_gemini15_flash,
   (select "Hello Gemini!" AS prompt),
   STRUCT(0.8 AS temperature));

You are an AI analyst responsible for analyzing emotions in diary entries.

Evaluate the following diary entry for the presence of the following seven emotions:
- Sadness
- Happiness
- Anger
- Contempt
- Disgust
- Fear
- Surprise

Evaluation Criteria:  
Each emotion is rated on the following three levels:  
- 0: Barely present  
- 1: Slightly present  
- 2: Strongly present  

Output Format:  
Return the result strictly in the following JSON format:

{
  "sadness": 0,
  "happiness": 1,
  "anger": 0,
  "contempt": 0,
  "disgust": 0,
  "fear": 1,
  "surprise": 2
}

- Keys must be in lowercase English.  
- Values must be integers: 0, 1, or 2.  
- Do not include any additional text or comments outside the JSON format.

Diary Entry:  
{diary_entry}

CREATE OR REPLACE TABLE `flowing-capsule-445508-d1`.`my_dataset`.`int_keep_daiary_with_llm` AS
WITH
  BASE_PROMPT AS (
  SELECT
    'You are an AI analyst responsible for analyzing emotions in diary entries.\n\nEvaluate the following diary entry for the presence of the following seven emotions:\n- Sadness\n- Happiness\n- Anger\n- Contempt\n- Disgust\n- Fear\n- Surprise\n\nEvaluation Criteria:  \nEach emotion is rated on the following three levels:  \n- 0: Barely present  \n- 1: Slightly present  \n- 2: Strongly present  \n\nOutput Format:  \nReturn the result strictly in the following JSON format:\n\n{\n  "sadness": 0,\n  "happiness": 1,\n  "anger": 0,\n  "contempt": 0,\n  "disgust": 0,\n  "fear": 1,\n  "surprise": 2\n}\n\n- Keys must be in lowercase English.  \n- Values must be integers: 0, 1, or 2.  \n- Do not include any additional text or comments outside the JSON format.\n\n## Diary Entry:  \n' AS prompt_text ),
  import_daiary AS (
  SELECT
    *,
  FROM
    `flowing-capsule-445508-d1.my_dataset.int_keep_daiary` ),
  cut_content AS (
  SELECT
    import_daiary.*,
    LENGTH(textContent) AS len,
    SUBSTRING(textContent, 1, 400) AS textContent_cut,
  FROM
    import_daiary ),
  add_prompt AS (
  SELECT
    source.*,
    CONCAT(BASE_PROMPT.prompt_text, textContent_cut) AS prompt,
  FROM
    cut_content AS SOURCE,
    BASE_PROMPT
    -- NOTE: SAFE RUN
    -- LIMIT 2
  ),
  generate_text as (
    SELECT *
    FROM
    ML.GENERATE_TEXT(
      MODEL my_dataset.my_gemini15_flash,
      (select 
        createdTimestampUsec,
        title,
        textContent,
        prompt from add_prompt),
      STRUCT(0.8 AS temperature, true as flatten_json_output))
  )


SELECT
  *
FROM
  generate_text

{
  "sadness": 0,
  "happiness": 1,
  "anger": 0,
  "contempt": 0,
  "disgust": 0,
  "fear": 1,
  "surprise": 2
}

with base as (
SELECT 
  createdTimestampUsec,
  TIMESTAMP_TRUNC(timestamp_micros(createdTimestampUsec), MINUTE) as created_at,
  title,
  textContent,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.sadness') as INT64) AS sadness,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.happiness') as INT64) AS happiness,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.anger') as INT64) AS anger,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.contempt') as INT64) AS contempt,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.disgust') as INT64) AS disgust,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.fear') as INT64) AS fear,
  cast(JSON_VALUE(ml_generate_text_llm_result, '$.surprise') as INT64) AS surprise,

FROM
  `flowing-capsule-445508-d1.my_dataset.int_keep_daiary_with_llm`
),
add_flag as (
  select base.*,
  (sadness > 0) as is_sad,
  (happiness > 0) as is_happy,
  (anger > 0) as is_angry,
  (contempt > 0) as is_contempt,
  (disgust > 0) as is_disgusted,
  (fear > 0) as is_afraid,
  (surprise > 0) as is_surprised,
  from base
)
select * from add_flag