How to add date rows for messages query?

I got a Messages table.

id | sender_id | message | date
1  | 1         | Cya     | 10/10/2020
2  | 2         | Bye     | 10/10/2020
3  | 1         | Heya    | 10/11/2020  

I want to insert date rows and a type column based on the date, so it looks like this.

id | sender_id | message | date       | type
1  | null      | null    | 10/10/2020 | date
1  | 1         | Cya     | 10/10/2020 | message
2  | 2         | Bye     | 10/10/2020 | message
2  | null      | null    | 10/11/2020 | date
3  | 1         | Heya    | 10/11/2020 | message  
3  | null      | null    | 10/11/2020 | date

When ordering by date, type, the first and the last rows are dates. And there is a date row between every two messages with different dates having the later date’s value.

I got no idea how to tackle this one. Please tell me if you got any ideas on how to approach this.

Answer

This is quite complicated, because you want the new rows to contain the next date but the previous max id (if it exists) and also 1 row at the end.
So you can use UNION ALL for 3 separate cases:

select id, sender_id, message, date, type 
from (
  select id, sender_id, message, date, 'message' as type, 2 sort
  from Messages
  union all
  select lag(max(id), 1, min(id)) over (order by date), null, null, date, 'date', 1
  from Messages
  group by date
  union all
  select * from (
    select id, null, null, date, 'date', 3
    from Messages
    order by date desc, id desc limit 1
  )  
)
order by date, sort, id

Note that this will work only if your dates are in the format YYYY-MM-DD which is comparable and the only valid date format for SQLite.

See the demo.
Results:

> id | sender_id | message | date       | type   
> -: | :-------- | :------ | :--------- | :------
>  1 | null      | null    | 2020-10-10 | date   
>  1 | 1         | Cya     | 2020-10-10 | message
>  2 | 2         | Bye     | 2020-10-10 | message
>  2 | null      | null    | 2020-10-11 | date   
>  3 | 1         | Heya    | 2020-10-11 | message
>  3 | null      | null    | 2020-10-11 | date