create view aggregate_rooms_view as (
  select day,
         tenant_id,
         property_id,
         room_type,
         sum(on_the_market::int) as "total",
         sum(available::int) as "available",
         sum(on_the_market::int) - sum(available::int) as "unavailable",
         sum(off_the_market::int) as "off_the_market",
         sum(walk_in::int) as "walk_in",
         sum(out_of_order::int) as "out_of_order",
         sum(occupied::int) as "occupied",
         sum(occupied::int) * 100 / sum(on_the_market::int) as "percentage_occupied"
  from historic_rooms
  group by day, tenant_id, property_id, room_type
);


create view aggregate_transactions_view as (
  select day,
         tenant_id,
         property_id,
         transaction_type,
         room_revenue,
         category,
         sub_category,
         count(id) as "transactions",
         sum(amount) as "amount",
         sum(tax) as "tax",
         avg(amount) as "average_daily_rate"
  from raw_transactions
  group by day, tenant_id, property_id, transaction_type, room_revenue, category, sub_category
);



create view aggregate_reservations_view as (
  select day,
         tenant_id,
         property_id,
         rate_plan,
         rate,
         room_type,
         sum(number_of_adults)              as "adults",
         sum(number_of_children)            as "children",
         sum(number_of_pets)                as "pets",
         sum(total_guests)                  as "guests",
         sum(case loyalty_member_ids when '{}' then 0 else 1 end) as "members",
         sum(daily_account_revenue)         as daly_account_revenue,
         sum(daily_account_tax)             as daily_account_tax,
         sum(daily_account_revenue_and_tax) as daily_account_revenue_and_tax,
         sum(room_revenue)                  as room_revenue,
         sum(room_tax)                      as room_tax,
         sum(room_revenue_and_tax)          as room_revenue_and_tax,
         sum(arrived_today::int)            as arrival_rooms,
         sum(depart_today::int)             as departure_rooms,
         sum(no_show::int)                  as no_show,
         sum(canceled::int)                 as cancelled,
         sum(late_checkout::int)            as late_checkout,
         sum(early_arrival::int)            as early_arrival,
         sum(case date_trunc('day', reservation_created_at) when day then 1 else 0 end) as "reservations_made_today",
         sum(case date_trunc('day', cancelation_at) when day then 1 else 0 end) as "cancelation_made_today"

  from historic_reservations
  group by day, tenant_id, property_id, rate_plan, rate, room_type
);