drop index weightDateIdx;
drop index weightDailyIdx;
drop index weightWeeklyIdx;
drop index weightMonthlyIdx;
drop index weightYearlyIdx;

create table weight2 (
	id integer primary key,
	date datetime default (datetime('now')),
	weight real not null
);
insert into weight2 select * from weight;
drop table weight;

drop view if exists weightView;
drop view if exists weightDaily;
drop view if exists weightWeekly;
drop view if exists weightMonthly;
drop view if exists weightYearly;

alter table weight2 rename to weight;

create index weightDateIdx on weight(date);
create index weightDailyIdx on weight(strftime('%Y-%m-%d', date));
create index weightWeeklyIdx on weight(strftime('%Y-%W', date));
create index weightMonthlyIdx on weight(strftime('%Y-%m', date));
create index weightYearlyIdx on weight(strftime('%Y', date));

create view weightDaily as
select strftime('%Y-%m-%d', date) as period,
	round(avg(weight), 2) as amount
from weight
group by strftime('%Y-%m-%d', date)
order by date desc;

create view weightWeekly as
select strftime('%Y-%W', date) as period,
	round(avg(weight), 2) as amount
from weight
group by strftime('%Y-%W', date)
order by date desc;

create view weightMonthly as
select strftime('%Y-%m', date) as period,
	round(avg(weight), 2) as amount
from weight
group by strftime('%Y-%m', date)
order by date desc;

create view weightYearly as
select strftime('%Y', date) as period,
	round(avg(weight), 2) as amount
from weight
group by strftime('%Y', date)
order by date desc;


create table food2 (
	id integer primary key,
	date datetime default (datetime('now')),
	food varchar not null,
	description varchar,
	amount real not null,
	per100 real default 0,
	energy generated always as (coalesce(amount, 0) * coalesce(per100, 0) / 100) stored
);

drop index dailyIdx;
drop index weeklyIdx;
drop index monthlyIdx;
drop index yearlyIdx;
drop index dateIdx;
drop index foodIdx;

drop view if exists foodView;
drop view if exists foodDaily;
drop view if exists foodWeekly;
drop view if exists foodMonthly;
drop view if exists foodYearly;
drop view if exists foodRecent;

insert into food2(date, food, description, amount) select date, food, description, amount from food;
drop table food;
alter table food2 rename to food;

create index dailyIdx on food(strftime('%Y-%m-%d', date));
create index weeklyIdx on food(strftime('%Y-%W', date));
create index monthlyIdx on food(strftime('%Y-%m', date));
create index yearlyIdx on food(strftime('%Y', date));
create index dateIdx on food(date);
create index foodIdx on food(food);

create view foodView as
select id,
	date,
	food,
	description,
	round(amount, 2) as amount,
	round(per100, 2) as per100,
	round(energy, 2) as energy
from food;

create view foodDaily as
select strftime('%Y-%m-%d', date) as period,
	round(sum(amount), 2) as amount,
	round(avg(per100), 2) as avgPer100,
	round(sum(energy), 2) as energy
from food
group by strftime('%Y-%m-%d', date)
order by date desc;

create view foodWeekly as
select strftime('%Y-%W', date) as period,
	round(sum(amount), 2) as amount,
	round(avg(per100), 2) as avgPer100,
	round(sum(energy), 2) as energy
from food
group by strftime('%Y-%W', date)
order by date desc;

create view foodMonthly as
select strftime('%Y-%m', date) as period,
	round(sum(amount), 2) as amount,
	round(avg(per100), 2) as avgPer100,
	round(sum(energy), 2) as energy
from food
group by strftime('%Y-%m', date)
order by date desc;

create view foodYearly as
select strftime('%Y', date) as period,
	round(sum(amount), 2) as amount,
	round(avg(per100), 2) as avgPer100,
	round(sum(energy), 2) as energy
from food
group by strftime('%Y', date)
order by date desc;

create view foodRecent as
select *
from food
order by date desc
limit 10;

drop trigger if exists food_foodfix_insert;
create trigger food_foodfix_insert AFTER
insert on food for EACH row
begin
update foodfix
set rank = rank + 1
where word = new.food;
insert into foodfix (word, rank)
select new.food,
	1
where not exists (
		select 1
		from foodfix
		where word = new.food
	);
end;

drop trigger if exists food_foodfix_delete;
create trigger food_foodfix_delete AFTER
delete on food for EACH row
begin
update foodfix
set rank = rank - 1
where word = old.food;

delete from foodfix
where word = old.food
	and rank <= 0;
end;

drop trigger if exists food_foodfix_update;
create trigger food_foodfix_update AFTER
update on food for EACH row
begin
update foodfix
set rank = rank - 1
where word = old.food;
end;

delete from foodfix;
insert into foodfix (word, rank)
select food as word,
	count(*) as rank
from food
group by food;