begin;

create table weight (
	id integer primary key,
	date datetime default (datetime('now')),
	weight real not null
);

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));

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;

create view weightView as
select id,
	date,
	round(weight, 2) as weight
from weight
order by date desc;

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 food(
	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
);

create virtual table foodfix using spellfix1;
insert into foodfix (word, rank)
select food as word,
	count(*) as rank
from food
group by food;

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;

delete from foodfix
where word = old.food
	and rank <= 0;
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;

with search_results as (
    select word, score, f.rowid, f.*
    from foodfix
	inner join food f on f.food == word
    where word match 'B'
)
select rowid, food, score, date, description, amount, per100, energy 
from search_results
group by food
order by score asc, date desc;

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);

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;

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 getper100;
create trigger getper100 after
insert on food
begin
update food
set per100 = coalesce(
		new.per100,
		(
			select per100
			from food
			where food = new.food
				and per100 is not null
			order by date desc
			limit 1
		)
	)
where id = new.id;
end;

create table settings(
	key text not null primary key,
	value text not null
);

commit;