{{
    config(
        materialized='table',
        tags=["dbt"]
    )
}}

with date_spine as (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="dateadd(year, 3, current_date())"
    ) }}
),

date_attributes as (
    select
        date_day,

        -- date identifiers
        to_char(date_day, 'YYYYMMDD')::int as date_key,
        date_day as date_actual,

        -- day attributes
        dayname(date_day) as day_name,
        dayofweek(date_day) as day_of_week,
        dayofweekiso(date_day) as day_of_week_iso,
        dayofmonth(date_day) as day_of_month,
        dayofyear(date_day) as day_of_year,

        -- week attributes
        date_trunc('week', date_day) as week_start_date,
        week(date_day) as week_of_year,
        weekiso(date_day) as week_of_year_iso,

        -- month attributes
        date_trunc('month', date_day) as month_start_date,
        last_day(date_day) as month_end_date,
        monthname(date_day) as month_name,
        month(date_day) as month_of_year,

        -- quarter attributes
        date_trunc('quarter', date_day) as quarter_start_date,
        concat('Q', quarter(date_day)) as quarter_name,
        quarter(date_day) as quarter_of_year,

        -- year attributes
        date_trunc('year', date_day) as year_start_date,
        year(date_day) as year_actual,

        -- combined period identifiers
        to_char(date_day, 'YYYYMM')::int as year_month_key,
        concat(year(date_day), '-Q', quarter(date_day)) as year_quarter,
        concat(year(date_day), '-W', lpad(weekiso(date_day)::varchar, 2, '0')) as year_week_iso,

        -- flags
        case when dayofweek(date_day) in (0, 6) then true else false end as is_weekend,
        case when dayofweek(date_day) not in (0, 6) then true else false end as is_weekday,
        case when month(date_day) = month(current_date()) and year(date_day) = year(current_date()) then true else false end as is_current_month,
        case when quarter(date_day) = quarter(current_date()) and year(date_day) = year(current_date()) then true else false end as is_current_quarter,
        case when year(date_day) = year(current_date()) then true else false end as is_current_year,

        -- relative periods
        datediff('day', date_day, current_date()) as days_from_today,
        datediff('week', date_day, current_date()) as weeks_from_today,
        datediff('month', date_day, current_date()) as months_from_today,
        datediff('quarter', date_day, current_date()) as quarters_from_today,
        datediff('year', date_day, current_date()) as years_from_today,

        -- same day last periods
        dateadd('week', -1, date_day) as same_day_last_week,
        dateadd('month', -1, date_day) as same_day_last_month,
        dateadd('year', -1, date_day) as same_day_last_year

    from date_spine
)

select * from date_attributes
