{% comment %}
  save-plan v2  (Daily Word)  -  pairs with app v5.0
  Endpoint: POST /api/v2/lava-app/1/daily-word/save-plan
  Settings unchanged from v1: Sql command enabled, Application View security,
  client sends X-Helix-CSRF-Protection: true.

  v2 does three things v1 did not:
  1. UNION readDates server-side (incoming + stored) before writing the plan
     blob, and derives the day counts from the MERGED set - a stale tab or
     second device can never erase a recorded day, and counts never shrink.
  2. Upserts the seven summary person attributes alongside the plan (eight
     writes total). Attributes that do not exist yet simply match no row and
     are skipped - create them at any time and they light up on next save.
       DailyWordShare        Boolean  from the consent checkbox
       DailyWordStreak       Integer  client-computed current streak
       DailyWordBestStreak   Integer  monotonic high-water mark
       DailyWordDaysRead     Integer  lifetime days with any reading
       DailyWordDaysReadYear Integer  days read in the current calendar year
       DailyWordDaysReadYearOf Integer  which year that count belongs to
       DailyWordLastRead     Date     most recent recorded reading day
  3. Returns the merged readDates so the client adopts the authoritative set.

  Expected POST body: { "plan": "<json string>", "streak": <int> }
{% endcomment %}
{% if CurrentPerson %}
{% assign todayLocal = 'Now' | Date:'yyyy-MM-dd' %}
{% assign clientStreak = Body.streak | AsInteger | Default:0 %}
{% sql pid:'{{ CurrentPerson.Id }}' incoming:'{{ Body.plan }}' streak:'{{ clientStreak }}' today:'{{ todayLocal }}' %}
  SET NOCOUNT ON;

  DECLARE @petid int = (SELECT Id FROM EntityType WHERE [Name] = 'Rock.Model.Person');
  DECLARE @existing nvarchar(max) = (
    SELECT av.Value FROM AttributeValue av
    JOIN Attribute a ON a.Id = av.AttributeId
    WHERE a.[Key] = 'DailyWordPlan' AND a.EntityTypeId = @petid AND av.EntityId = @pid);

  -- union of stored + incoming readDates, sorted, deduplicated
  DECLARE @mergedDates nvarchar(max);
  SELECT @mergedDates = '[' + ISNULL(
      STRING_AGG(CAST('"' + v + '"' AS nvarchar(max)), ',') WITHIN GROUP (ORDER BY v), '') + ']'
  FROM (
    SELECT DISTINCT [value] AS v FROM (
      SELECT [value] FROM OPENJSON(ISNULL(JSON_QUERY(@existing, '$.readDates'), '[]'))
      UNION
      SELECT [value] FROM OPENJSON(ISNULL(JSON_QUERY(@incoming, '$.readDates'), '[]'))
    ) u
    WHERE [value] IS NOT NULL AND LEN([value]) = 10
  ) x;

  DECLARE @newPlan nvarchar(max) = JSON_MODIFY(@incoming, '$.readDates', JSON_QUERY(@mergedDates));

  -- metrics derive from the MERGED set: they can only grow
  DECLARE @daysRead int = (SELECT COUNT(*) FROM OPENJSON(@mergedDates));
  DECLARE @yr char(4) = LEFT(@today, 4);
  DECLARE @daysYear int = (SELECT COUNT(*) FROM OPENJSON(@mergedDates) WHERE [value] LIKE @yr + '-%');
  DECLARE @lastRead nvarchar(10) = (SELECT MAX([value]) FROM OPENJSON(@mergedDates));
  DECLARE @share nvarchar(5) =
    CASE WHEN JSON_VALUE(@incoming, '$.sharePublicly') = 'true' THEN 'True' ELSE 'False' END;

  -- best streak is monotonic: never lowered by a comeback
  DECLARE @bestExisting int = TRY_CAST((
    SELECT av.Value FROM AttributeValue av
    JOIN Attribute a ON a.Id = av.AttributeId
    WHERE a.[Key] = 'DailyWordBestStreak' AND a.EntityTypeId = @petid AND av.EntityId = @pid) AS int);
  DECLARE @best int = CASE WHEN ISNULL(@bestExisting, 0) > @streak
                           THEN @bestExisting ELSE @streak END;

  DECLARE @vals TABLE ([Key] nvarchar(60), Val nvarchar(max));
  INSERT INTO @vals VALUES
    ('DailyWordPlan',            @newPlan),
    ('DailyWordShare',           @share),
    ('DailyWordStreak',          CAST(@streak AS nvarchar(20))),
    ('DailyWordBestStreak',      CAST(@best AS nvarchar(20))),
    ('DailyWordDaysRead',        CAST(@daysRead AS nvarchar(20))),
    ('DailyWordDaysReadYear',    CAST(@daysYear AS nvarchar(20))),
    ('DailyWordDaysReadYearOf',  @yr),
    ('DailyWordLastRead',        ISNULL(@lastRead, ''));

  -- upsert: update where a value row exists, insert where it does not.
  -- Attributes not yet created match no Attribute row and are skipped.
  UPDATE av SET Value = v.Val, IsPersistedValueDirty = 1, ModifiedDateTime = GETDATE()
  FROM AttributeValue av
  JOIN Attribute a ON a.Id = av.AttributeId
  JOIN @vals v ON v.[Key] = a.[Key]
  WHERE a.EntityTypeId = @petid AND av.EntityId = @pid;

  INSERT INTO AttributeValue (IsSystem, AttributeId, EntityId, Value, [Guid], IsPersistedValueDirty, CreatedDateTime)
  SELECT 0, a.Id, @pid, v.Val, NEWID(), 1, GETDATE()
  FROM Attribute a
  JOIN @vals v ON v.[Key] = a.[Key]
  WHERE a.EntityTypeId = @petid
    AND NOT EXISTS (SELECT 1 FROM AttributeValue av
                    WHERE av.AttributeId = a.Id AND av.EntityId = @pid);

  SELECT @mergedDates AS mergedDates;
{% endsql %}
{% assign row = results | First %}
{"ok":true,"readDates":{{ row.mergedDates }}}
{% else %}
{"ok":false,"error":"not signed in"}
{% endif %}
