create table rights(
id integer primary key auto_increment,
description varchar(100) not null,
notes varchar(100) not null
);




create table province(
id integer primary key auto_increment,
names varchar(100) not null
);



create table seasons(
id integer primary key auto_increment,
name varchar(100) not null unique,
active integer not null default 1,
created_at varchar(100) not null
);




create table users(

id integer primary key auto_increment,
name varchar(100) not null,
surname varchar(100) not null,
username varchar(100) not null unique,
hash varchar(100) not null,
rightsid integer not null,
active integer not null default 1,
access_code integer not null,
created_at varchar(100) not null,
foreign key(rightsid) references rights(id)

);





create table user_home(
id integer primary key auto_increment,
userid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id)
);




create table products(
id integer primary key auto_increment,
name varchar(100) not null,
units varchar(100) not null
);



create table prices(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
amount double(100,2) not null default 0,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id)
);


create table growers(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
grower_num varchar(100) unique,
area varchar(100) not null,
province varchar(100),
phone varchar(100),
id_num varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table grower_profile(
id integer primary key auto_increment,
growerid integer not null,
image blob not null,
sync integer not null default 0,
created_at varchar(100) not null
);



create table assessment_type(
id integer primary key auto_increment,
name varchar(100) not null
);


create table assessments(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
assessment_typeid integer not null default 1,
number integer not null default 1,
foreign key(userid) references users(id),
foreign key(assessment_typeid) references assessment_type(id)
);





create table lat_long(
id integer primary key auto_increment,
userid integer not null ,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
hectares varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);





create table barn_location(
id integer primary key auto_increment,
userid integer not null ,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);




create table no_grower_location(
id integer primary key auto_increment,
userid integer not null ,
growerid integer not null,
description varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);




create table grower_farm(
id integer primary key auto_increment,
userid integer not null ,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);





create table if not exists assessment_images(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
image varchar(1000000) not null,
description varchar(100) not null,
conditions varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);





create table grower_visits(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
description varchar(100) not null,
conditions varchar(100) not null,
other varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);


create table quantitative_assessments(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
description varchar(100) not null,
number varchar(100) not null,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id)
);




create table loans(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
productid integer not null,
quantity integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
verified integer default 0,
processed integer default 0,
processed_by integer default 0,
processed_at varchar(100),
verified_by integer default 0,
hectares varchar(100) default 0,
verified_at varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id),
foreign key(productid) references products(id)
);


create table contracted_hectares(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
hectares varchar(100) not null,
sync integer not null default 0,
created varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id),
);



create table con_loan(
id integer primary key auto_increment,
loanid integer not null,
sync integer not null default 0,
foreign key(loanid) references loans(id)
);




create table confirm_user(
id integer primary key auto_increment,
userid integer not null,
confirmid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(confirmid) references con_loan(id)
);







create table sod(
id integer primary key auto_increment,
userid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
time varchar(100) not null,
eod integer not null default 0,
created_at varchar(100) not null,
sync integer not null default 0,
eod_created_at TEXT not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);





create table exemptions(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
exemption_date varchar(100) not null,
description varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id)
);



create table if not exists recovery(
id integer primary key auto_increment,
grower_num varchar(100) not null,
seasonid integer not null,
userid integer not null,
bales integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table road_blocks(
id integer primary key auto_increment,
userid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
time varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table eod(
id integer primary key auto_increment,
userid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
time varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table off_route(

id integer primary key auto_increment,
userid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
time varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table parameters(
id integer primary key auto_increment,
name varchar(100) not null unique
);



create table charges(
id integer primary key auto_increment,
name varchar(100) not null unique
);



create table charges_amount(
id integer primary key auto_increment,
chargeid integer not null,
seasonid integer not null,
parameterid integer not null,
userid integer not null,
sync integer not null default 0,
value double(100,2) not null,
created_at varchar(100) not null
);



create table loan_payments(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
amount double(100,2) not null,
mass double(100,2) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table loan_balances(
id integer primary key auto_increment,
loanid integer not null,
amount double(100,2) default 0,
created_at varchar(100) not null
);






create table seed_beds(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
no_of_irr_beds integer not null default 0,
no_of_dry_beds integer not null default 0,
buying_seedlings_for varchar(100),
varieties_irr varchar(100),
varieties_dry varchar(100),
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);








create table seedling_quality(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
excellent integer not null default 0,
standard integer not null default 0,
average integer not null default 0,
poor integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);






create table plant_irrigated(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
ha_planted_to_date varchar(100),
ha varchar(100),
date_of_plant varchar(100),
crop_stand_perc varchar(100),
crop_unifomity_perc varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);




create table planting_dryLand(

id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
ha_planted_to_date varchar(100),
ha varchar(100),
date_of_plant varchar(100),
crop_stand_perc varchar(100),
crop_unifomity_perc varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);








create table crop_growth(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
quarter_grown_5_7 integer not null default 0,
half_grown_8_12 integer not null default 0,
three_quarters_13_17 integer not null default 0,
full_grown_18_22 integer not null default 0,
fully_developed integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);











create table crop_development(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
light_yellow integer default 0,
light_green integer default 0,
medium integer default 0,
heavy integer default 0,
heavy_and_dark integer default 0,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);









create table cultural_practices(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
weed_infestation_level_perc varchar(100),
weed_control_method varchar(100),
topping_level varchar(100),
basal_plant_fertilisation_kg_ha varchar(100),
suckering integer not null default 0,
post_topping_unifomity_perc varchar(100),
pets_and_disease_management varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);






create table barn_repair_and_maintenance(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
barn_not_repaired integer not null default 0,
barn_under_repair integer not null default 0,
finished_repaired integer not null default 0,
barn_working_well integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);







create table hail_strike(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
percentage_strike varchar(100) not null,
strike_date varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





DELIMITER $$
create trigger on_loan_verification
after insert on con_loan
for each row
BEGIN
 UPDATE loans SET verified = 1  WHERE id = NEW.loanid;
END;
$$
DELIMITER ;




insert into assessment_type(name) value("Qualitative");
insert into assessment_type(name) value("Quantitative");


insert into rights(description,notes) value("System administrator","System administrator");
insert into rights(description,notes) value("I.T Assistant","I.T Assistant");
insert into rights(description,notes) value("Accountant","Accountant");
insert into rights(description,notes) value("Account Clerk","Account Clerk");
insert into rights(description,notes) value("Hr Admin","Account Clerk");
insert into rights(description,notes) value("Hr Assistant","Hr Assistant");
insert into rights(description,notes) value("Area Manager","Area Manager");
insert into rights(description,notes) value("Field Officer","Field Officer");
insert into rights(description,notes) value("Field Officer(Loans Access)","Field Officer");



insert into users(name,surname,username,hash,rightsid,active,access_code,created_at) value("sysadmin","sysadmin","sysadmin","sysadmin",1,1,0000,"2022-09-12");


insert into province(names) value("Harare");
insert into province(names) value("Mashonaland East");
insert into province(names) value("Mashonaland West");
insert into province(names) value("Mashonaland Central");
insert into province(names) value("Manicaland");
insert into province(names) value("Matebeleland North");
insert into province(names) value("Matebeleland South");
insert into province(names) value("Masvingo");
insert into province(names) value("Midlands");
insert into province(names) value("Bulawayo");


insert into parameters(name) value("Amount");
insert into parameters(name) value("Percentage");

insert into charges(name) value("Interest");
insert into charges(name) value("Adminstration");



