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


create table if not exists company_name(
id integer not null,
active integer not null default 1,
company_name varchar(100) not null
);



create table if not exists regulator_ip(
id integer primary key auto_increment,
url varchar(100) not null,
created_at varchar(100) not null
);





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



create table if not exists 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,
datetime TIMESTAMP not null
);



create table if not exists currency(
id integer primary key auto_increment,
description varchar(100) not null,
datetime TIMESTAMP not null
);



create table if not exists users(

id integer primary key auto_increment,
name varchar(100) not null,
surname varchar(100) not null,
username varchar(100) not null,
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,
datetime TIMESTAMP not null,
foreign key(rightsid) references rights(id)

);





create table if not exists bales_rights(
id integer primary key auto_increment,
userid integer not null,
receiverid integer not null,
companyid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at integer not null,
datetime TIMESTAMP not null,
foreign key(receiverid) references users(id),
foreign key(companyid) references users(id),
foreign key(userid) references users(id)
);







create table if not exists bale_tracking_rights(
id integer primary key auto_increment,
userid integer not null,
receiving_userid integer not null,
delivering_userid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at integer not null,
datetime TIMESTAMP not null,
foreign key(receiving_userid) references users(id),
foreign key(delivering_userid) references users(id),
foreign key(userid) references users(id)
);






create table if not exists rejected_bales_rights(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
useridrights integer not null,
seasonid integer not null,
active integer not null default 1,
created_at integer not null,
datetime TIMESTAMP not null,
foreign key(companyid) references users(id),
foreign key(useridrights) references users(id),
foreign key(userid) references users(id)
);






create table if not exists auction_rights(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at integer not null,
datetime TIMESTAMP not null,
foreign key(companyid) references users(id),
foreign key(userid) references users(id)
);





create table if not exists regulator_sync_status(
id integer primary key auto_increment,
userid integer not null,
status integer not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id)
);



create table if not exists supervisors(
id integer primary key auto_increment,
supervisorid integer not null,
leaf_techid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(supervisorid) references users(id),
foreign key(leaf_techid) references users(id),
foreign key(seasonid) references seasons(id)

);





create table if not exists 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,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);


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


create table if not exists products(
id integer primary key auto_increment,
name varchar(100) not null,
units varchar(100) not null,
package_units double(100,2) not null default 50,
product_typeid integer not null ,
datetime TIMESTAMP not null,
foreign key(product_typeid) references product_type(id)
);







create table if not exists supplier(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
location varchar(100) not null,
email varchar(100) not null,
phone  varchar(100) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);




create table if not exists store(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
location varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);




create table if not exists store_items(
id integer primary key auto_increment,
userid integer not null,
storeid integer not null,
productid integer not null,
quantity integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeid) references store(id),
foreign key(productid) references products(id)
);










create table if not exists product_split(
id integer primary key auto_increment,
userid integer not null,
storeid integer not null,
fromproductid integer not null,
toproductid integer not null,
quantity integer not null ,
divide_by integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeid) references store(id),
foreign key(fromproductid) references products(id),
foreign key(toproductid) references products(id)
);





create table if not exists product_barcodes(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
barcode varchar(100) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id)
);






create table if not exists arc_products(
id integer primary key auto_increment,
userid integer not null,
storeitemid integer not null ,
old_quantity integer not null,
new_quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeitemid) references store_items(id)
);



create table if not exists arc_store_items(
id integer primary key auto_increment,
userid integer not null,
storeid integer not null,
arc_productid integer not null,
productid integer not null,
quantity integer not null default 0,
quantity_balance integer not null default 0,
description varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeid) references store(id),
foreign key(productid) references products(id),
foreign key(arc_productid) references arc_products(id)
);



create table if not exists arc_store_items_invoice(
id integer primary key auto_increment,
userid integer not null,
arc_store_itemsid integer not null,
seasonid integer not null,
supplierid integer not null,
currencyid integer not null,
invoice_number varchar(100) not null,
unit_price double(200,2) not null,
datetime TIMESTAMP not null,
foreign key(arc_store_itemsid) references arc_store_items(id),
foreign key(supplierid) references supplier(id),
foreign key(userid) references users(id),
foreign key(currencyid) references currency(id)
);







create table if not exists truck_destination(
id integer primary key auto_increment,
userid integer not null,
trucknumber varchar(100) not null,
driver_name varchar(100) not null,
driver_surname varchar(100) not null,
destination varchar(100) not null,
close_open integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);







create table if not exists disbursement(
id integer primary key auto_increment,
disbursement_trucksid integer not null,
userid integer not null,
productid integer not null,
storeid integer not null,
quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(storeid) references store(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);





create table if not exists total_disbursement(
id integer primary key auto_increment,
disbursement_trucksid integer not null,
disbursementid integer not null,
userid integer not null,
productid integer not null,
storeid integer not null,
quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(storeid) references store(id),
foreign key(disbursement_trucksid) references truck_destination(id),
foreign key(disbursementid) references disbursement(id)
);





create table if not exists returned_stock(
id integer primary key auto_increment,
disbursement_trucksid integer not null,
userid integer not null,
productid integer not null,
storeid integer not null,
quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(storeid) references store(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);






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





create table if not exists 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 1,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table  if not exists truck_to_grower(
id integer primary key auto_increment,
userid integer not null,
loanid integer not null default 0,
growerid integer not null,
disbursement_trucksid integer not null,
productid integer not null,
quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(disbursement_trucksid) references truck_destination(id),
foreign key(growerid) references growers(id)
);





create table if not exists 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,
datetime TIMESTAMP not null
);




create table if not exists grower_field_officer(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
field_officerid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(field_officerid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




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


create table if not exists 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 if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 TEXT 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 grower_image(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
image TEXT not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 loans(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
productid integer not null,
quantity integer not null,
receipt_number varchar(100) not null,
product_amount double(100,2) not null default 0,
product_total_cost double(100,2) not null default 0,
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),
android_captureid integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 if not exists invoice_amount(
id integer primary key auto_increment,
growerid integer not null,
seasonid integer not null,
amount double(100,2) not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null
);




create table if not exists 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,
surrogate integer not null default 0,
created varchar(100) not null,
datetime TIMESTAMP 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 con_loan(
id integer primary key auto_increment,
loanid integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
foreign key(loanid) references loans(id)
);




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



create table if not exists loan_deduction_point(
id integer primary key auto_increment,
point integer not null default 0
);



create table if not exists 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,
surrogate integer not null default 0,
eod_created_at TEXT not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);





create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






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



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



create table if not exists 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,
surrogate integer not null default 0,
value double(100,2) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(chargeid) references charges(id),
foreign key(parameterid) references parameters(id)
);







create table if not exists working_capital(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
receipt_number varchar(100) ,
amount double(100,2) not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists working_capital_total(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
amount double(100,2) not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists inputs_total(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
amount double(100,2) not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists rollover(
id integer primary key auto_increment,
userid integer not null,
rollover_seasonid integer not null,
seasonid integer not null,
growerid integer not null,
amount double(100,2) not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(rollover_seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




create table if not exists rollover_total(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
amount double(100,2) not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists download_growers_rule(
id integer primary key auto_increment,
rule integer not null default 0
);




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





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









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






create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);







create table if not exists ploughing(
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,
no_of_plants integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);






create table if not exists fertilization_ammonium(
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,
kg_per_ha varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);








create table if not exists reaping(
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,
top_leaf integer not null default 0,
lugs integer not null default 0,
cutters integer not null default 0,
prime integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);










create table if not exists curing(
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,
yellowing integer not null default 0,
leaf_drying integer not null default 0,
stem_drying integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);









create table if not exists fertilization_potassium(

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,
kg_per_ha varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);









create table if not exists farm_mapping(

id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
first_lat varchar(100) not null,
first_long varchar(100) not null,
second_lat varchar(100) not null,
second_long varchar(100) not null,
third_lat varchar(100) not null,
third_long varchar(100) not null,
forth_lat varchar(100) not null,
forth_long varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);






create table if not exists  mapped_hectares(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
ha varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
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 if not exists  mapped_hail_hectares(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
ha varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
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 if not exists estimates(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
varieties varchar(100) not null,
estimate varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
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 if not exists data_collection(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
grower_age integer not null,
grower_sex integer not null ,
number_of_works integer not null,
income_per_month double(100,2) not null,
number_of_kids integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
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 if not exists kids_age(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
kid_age integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
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 if not exists education_level(
id integer primary key auto_increment,
description varchar(100) not null
);


create table if not exists grower_level_of_education(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
education_level_id integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
created_at varchar(100) not null,
foreign key(education_level_id) references education_level(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);



create table if not exists health_conditions(
id integer primary key auto_increment,
description varchar(100) not null
);



create table if not exists grower_health(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
health_conditionid integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
datetime TIMESTAMP not null,
created_at varchar(100) not null,
foreign key(health_conditionid) references health_conditions(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);






create table if not exists hail_strike_mapping(

id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
first_lat varchar(100) not null,
first_long varchar(100) not null,
second_lat varchar(100) not null,
second_long varchar(100) not null,
third_lat varchar(100) not null,
third_long varchar(100) not null,
forth_lat varchar(100) not null,
forth_long varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);













create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);










create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);







create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);







create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);








create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);










create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);







create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)

);





create table if not exists 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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists system_estimate_prediction(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
estimate varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists grower_number_of_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
bales integer not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);






create table if not exists ready_for_booking(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
grower_number_of_balesid 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,
sell_date varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(grower_number_of_balesid) references grower_number_of_bales(id),
foreign key(company_userid) references users(id)
);



create table if not exists rejected_bales(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
ready_for_bookingid integer not null,
bales integer not null default 0,
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
sell_date varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(ready_for_bookingid) references ready_for_booking(id),
foreign key(company_userid) references users(id)
); 




create table if not exists grower_number_of_bales_total(
id integer primary key auto_increment,
grower_number_of_balesid integer not null,
bales integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(grower_number_of_balesid) references grower_number_of_bales(id)
);


create table if not exists contracted_to(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists grower_number_sale_point(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
floor_code varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




create table if not exists insurance(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id)
); 





create table if not exists insurance_users(
id integer primary key auto_increment,
userid integer not null,
insuranceid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(insuranceid) references insurance(id)
);






create table if not exists insurance_growers(
id integer primary key auto_increment,
userid integer not null,
insuranceid integer not null,
growerid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(insuranceid) references insurance(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists insurance_grower_visits(
id integer primary key auto_increment,
userid integer not null,
insuranceid integer not null,
growerid integer not null,
seasonid integer not null


);



create table if not exists visits(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
description varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);


    


create table if not exists grower_payment_import_dates(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists system_estimate_prediction(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
estimate varchar(100) not null,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists grower_number_of_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
bales integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);






create table if not exists ready_for_booking(
id integer primary key auto_increment,
userid integer not null,
grower_number_of_balesid 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,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(grower_number_of_balesid) references grower_number_of_bales(id)
);




create table if not exists grower_number_of_bales_total(
id integer primary key auto_increment,
grower_number_of_balesid integer not null,
bales integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(grower_number_of_balesid) references grower_number_of_bales(id)
);


create table if not exists selling_points(
id integer primary key auto_increment,
name varchar(200) not null,
datetime TIMESTAMP not null
);




create table if not exists company_to_selling_point(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
selling_pointid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(companyid) references users(id),
foreign key(selling_pointid) references selling_points(id)
);



create table if not exists company_users_to_selling_points(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
company_userid integer not null,
company_to_selling_pointid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(companyid) references users(id),
foreign key(company_userid) references users(id),
foreign key(company_to_selling_pointid) references company_to_selling_point(id)
);






create table if not exists sold_bales(

id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
company_to_selling_pointid integer not null,
seasonid integer not null,
growerid integer not null,
temp_barcode varchar(100) not null,
barcode varchar(100) not null,
mass varchar(100) not null,
price varchar(100) not null,
lot varchar(100) not null,
grp varchar(100) not null,
buyer varchar(100) not null,
buyer_mark varchar(100) not null,
grade 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,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id),
foreign key(company_userid) references users(id),
foreign key(company_to_selling_pointid) references company_to_selling_point(id)

);






create table if not exists total_sold_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
quantity integer not null,
mass integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table if not exists dispatch_note(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
company_to_selling_pointid integer not null,
receiverid integer not null,
seasonid integer not null,
note varchar(100) not null,
driver_name varchar(100) not null,
driver_surname varchar(100) not null,
horse_num varchar(100) not null,
trailer_num varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
open_close integer not null default 0,
receiver_close integer not null default 0,
foreign key(userid) references users(id),
foreign key(receiverid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(company_userid) references users(id),
foreign key(company_to_selling_pointid) references company_to_selling_point(id)
);





create table if not exists dispatch_note_total_dispatched(
id integer primary key auto_increment,
dispatch_noteid integer not null,
quantity integer not null default 0,
mass integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(dispatch_noteid) references dispatch_note(id)
);





create table if not exists dispatch_note_total_received(
id integer primary key auto_increment,
dispatch_noteid integer not null,
quantity integer not null,
mass integer not null default 0,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(dispatch_noteid) references dispatch_note(id)
);




create table if not exists dispatch_note_check_point(
id integer primary key auto_increment,
userid integer not null,
dispatch_noteid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(dispatch_noteid) references dispatch_note(id)
);




create table if not exists dispatch(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
dispatch_noteid integer not null,
sold_balesid 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,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(sold_balesid) references sold_bales(id),
foreign key(dispatch_noteid) references dispatch_note(id),
foreign key(company_userid) references users(id)
);


create table if not exists total_dispatch(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
quantity integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists received_bales_principal(
id integer primary key auto_increment,
dispatchid integer not null,
userid integer not null,
actionby integer not null default 0,  
latitude varchar(100) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(dispatchid) references dispatch(id)
);







create table if not exists total_received_bales_principal(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
quantity integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists bale_tags(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
grower_number_of_balesid integer not null,
code varchar(100) not null,
sync integer not null default 0,
used integer not null default 0,
booked integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(grower_number_of_balesid) references grower_number_of_bales(id)
);






create table if not exists bale_receiver(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
company_to_selling_pointid integer not null,
actionby integer not null default 0,
bale_tagid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(bale_tagid) references bale_tags(id),
foreign key(userid) references users(id),
foreign key(company_userid) references users(id),
foreign key(company_to_selling_pointid) references company_to_selling_point(id)
);



create table if not exists bale_booked(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null default 0,
bale_tagid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(bale_tagid) references bale_tags(id),
foreign key(userid) references users(id),
foreign key(company_userid) references users(id)
);




create table if not exists bale_rejected(
id integer primary key auto_increment,
userid integer not null,
company_userid integer not null,
bale_tagid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(bale_tagid) references bale_tags(id),
foreign key(userid) references users(id),
foreign key(company_userid) references users(id)
);




create table if not exists bale_tag_to_sold_bale(
id integer primary key auto_increment,
userid integer not null,
bale_tagid integer not null,
sold_balesid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(bale_tagid) references bale_tags(id),
foreign key(sold_balesid) references sold_bales(id),
foreign key(userid) references users(id)
);




create table if not exists tags_total(
id integer primary key auto_increment,
userid integer not null,
grower_number_of_balesid integer not null,
tags_total integer not null default 0,
tags_generated integer not null default 0,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(grower_number_of_balesid) references grower_number_of_bales(id),
foreign key(userid) references users(id)
);




create table if not exists auction_growers(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
created_at integer not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(growerid) references growers(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists grower_total_received_kgs(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
mass double(100,2) not null default 0,
foreign key(growerid) references growers(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);





create table if not exists weather(
id integer primary key auto_increment,
seasonid integer not null,
growerid integer not null,
temp double(100,2) not null default 0,
temp_min double(100,2) not null default 0,
temp_max double(100,2) not null default 0,
pressure double(100,2) not null default 0,
humidity double(100,2) not null default 0,
rain double(100,2) not null default 0,
clouds double(100,2) not null default 0,
wind_speed double(100,2) not null default 0,
created_at varchar(100) not null,
dt varchar(100) not null ,
city varchar(100) not null,
count integer not null default 0,
datetime TIMESTAMP not null,
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists grower_weather_total(
id integer primary key auto_increment,
seasonid integer not null,
growerid integer not null,
temp double(100,2) not null default 0,
temp_min double(100,2) not null default 0,
temp_max double(100,2) not null default 0,
pressure double(100,2) not null default 0,
humidity double(100,2) not null default 0,
rain double(100,2) not null default 0,
clouds double(100,2) not null default 0,
wind_speed double(100,2) not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists machine_learning_product_variables(
id integer primary key auto_increment,
description varchar(100) not null unique
);


create table if not exists machine_learning_products(
id integer primary key auto_increment,
productid integer not null,
machine_learning_product_variablesid integer not null,
foreign key(productid) references products(id),
foreign key(machine_learning_product_variablesid) references machine_learning_product_variables(id)
);






create table if not exists grower_loan_verified(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
sync integer not null default 0,
datetime TIMESTAMP 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 buyer(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
description varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(companyid) references users(id)
);


create table if not exists buyer_mark(
id integer primary key auto_increment,
userid integer not null,
description varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);



create table if not exists company_store(
id integer primary key auto_increment,
userid integer not null,
storeid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeid) references store(id)
);



create table if not exists developer(
id integer primary key auto_increment,
userid integer not null,
company_code varchar(100) not null,
warehouse_code varchar(100) not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);





create table if not exists developer_product_codes(
id integer primary key auto_increment,
productid integer not null,
product_code varchar(100) not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(productid) references products(id)
);



create table if not exists arc_product_grower(
id integer primary key auto_increment,
arc_productid integer not null,
loanid integer not null,
datetime TIMESTAMP not null,
foreign key(arc_productid) references arc_products(id),
foreign key(loanid) references loans(id)
);



create table if not exists arc_product_truck(
id integer primary key auto_increment,
arc_productid integer not null,
disbursementid integer not null,
quantity integer not null,
datetime TIMESTAMP not null,
foreign key(disbursementid) references disbursement(id),
foreign key(arc_productid) references arc_products(id)
);



create table if not exists selling_points(
id integer primary key auto_increment,
userid integer not null default 1,
name varchar(200) not null unique,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);




create table if not exists company_to_selling_point(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
selling_pointid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(companyid) references users(id),
foreign key(selling_pointid) references selling_points(id)
);



create table if not exists company_users_to_selling_points(
id integer primary key auto_increment,
userid integer not null,
companyid integer not null,
company_userid integer not null,
company_to_selling_pointid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(companyid) references users(id),
foreign key(company_userid) references users(id),
foreign key(company_to_selling_pointid) references company_to_selling_point(id)
);



create table if not exists user_to_store(
id integer primary key auto_increment,
userid integer not null,
loan_userid integer not null,
storeid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(storeid) references store(id),
foreign key(loan_userid) references users(id)
);





create table if not exists transfer_products(
id integer primary key auto_increment,
userid integer not null,
fromStoreid integer not null,
toStoreid integer not null,
quantity integer not null,
arc_store_itemid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(fromStoreid) references store(id),
foreign key(toStoreid) references store(id),
foreign key(arc_store_itemid) references arc_store_items(id)
);



create table if not exists store_hold_area(
id integer primary key auto_increment,
userid integer not null,
store_itemid integer not null,
quantity integer not null DEFAULT 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(store_itemid) references store_items(id)
);





create table if not exists arc_store_hold_area(
id integer primary key auto_increment,
userid integer not null,
store_hold_areaid integer not null,
old_quantity integer not null,
new_quantity integer not null,
quantity integer not null DEFAULT 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(store_hold_areaid) references store_hold_area(id)
);







create table if not exists active_growers(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer 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 user_to_truck_disbursment(
id integer primary key auto_increment,
userid integer not null,
disbursement_trucksid integer not null,
truck_userid integer not null,
seasonid integer not null,
active integer not null default 1,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(truck_userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);





create table if not exists truck_disbursment_sync_active(
id integer primary key auto_increment,
userid integer not null,
disbursement_trucksid integer not null,
active integer not null default 0,
seasonid integer not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);



create table if not exists reg_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,
hectares varchar(100),
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 distance(
id integer primary key auto_increment,
userid integer not null ,
distance double(100,2) not null default 0,
seasonid integer not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists transfer_grower_loan(
id integer primary key auto_increment,
userid integer not null ,
from_growerid integer not null,
to_growerid integer not null,
loanid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(from_growerid) references growers(id),
foreign key(to_growerid) references growers(id),
foreign key(loanid) references loans(id)
);





create table if not exists chairman(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);






create table if not exists grower_chairman(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
chairmanid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(chairmanid) references chairman(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists chairman_field_officer(
id integer primary key auto_increment,
userid integer not null,
fieldofficerid integer not null,
chairmanid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(chairmanid) references chairman(id),
foreign key(seasonid) references seasons(id),
foreign key(fieldofficerid) references users(id)
);





create table if not exists field_officer_area_manager(
id integer primary key auto_increment,
userid integer not null,
area_managerid integer not null,
fieldofficerid integer not null,
seasonid integer not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(fieldofficerid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(area_managerid) references users(id)
);







create table if not exists grower_managers(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
chairman varchar(100) not null,
fieldOfficer varchar(100) not null,
area_manager varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);





create table if not exists loan_adjustments(
id integer primary key auto_increment,
userid integer not null,
loanid integer not null,
old_quantity integer not null,
new_quantity integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(loanid) references loans(id)
);





create table if not exists fieldoffice(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);




create table if not exists areaManager(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);





create table if not exists truck_recon(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
disbursement_trucksid integer not null,
quantity varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);











create table if not exists balancing_side(
id integer primary key auto_increment,
description varchar(100) unique not null,
datetime TIMESTAMP not null
);


create table if not exists main_accounts(
id integer primary key auto_increment,
description varchar(100) unique not null,
datetime TIMESTAMP not null
);



create table if not exists main_account_balancing_side(
id integer primary key auto_increment,
main_accountid integer not null,
balancing_sideid integer not null,
datetime TIMESTAMP not null,
foreign key(main_accountid) references main_accounts(id),
foreign key(balancing_sideid) references balancing_side(id)
);




create table if not exists sub_accounts(
id integer primary key auto_increment,
description varchar(100) not null,
main_accountid integer not null,
datetime TIMESTAMP not null,
foreign key(main_accountid) references main_accounts(id)
);



create table if not exists assets(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
serial_number varchar(100) not null,
location varchar(100) not null,
description varchar(100) not null,
active integer not null default 1,
sub_accountid integer not null,
datetime TIMESTAMP not null,
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists assets_purchase_price(
id integer primary key auto_increment,
userid integer not null,
assetid integer not null,
currencyid integer not null,
sub_accountid integer not null,
payment_typeid integer not null,
amount double(100,2) not null,
receipt_num varchar(100) not null,
description varchar(100) not null,
transaction_at varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(assetid) references assets(id),
foreign key(currencyid) references currency(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(payment_typeid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists assets_value_movement(
id integer primary key auto_increment,
userid integer not null,
assetid integer not null,
old_value double(100,2) not null default 0,
new_value double(100,2) not null default 0,
description varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(assetid) references assets(id),
foreign key(userid) references users(id)
);





create table if not exists expenses(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
currencyid integer not null,
sub_accountid integer not null,
payment_typeid integer not null,
amount double(100,2) not null,
receipt_num varchar(100) not null,
description varchar(100) not null,
transaction_at varchar(100) not null,
created_at varchar(100) not null,
legder integer not null default 0,
journal integer not null default 0,
datetime TIMESTAMP not null,
foreign key(currencyid) references currency(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(payment_typeid) references sub_accounts(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table if not exists revenue(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
currencyid integer not null,
sub_accountid integer not null,
payment_typeid integer not null,
amount double(100,2) not null,
receipt_num varchar(100) not null,
description varchar(100) not null,
transaction_at varchar(100) not null,
created_at varchar(100) not null,
legder integer not null default 0,
journal integer not null default 0,
datetime TIMESTAMP not null,
foreign key(currencyid) references currency(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(payment_typeid) references sub_accounts(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists assets_debit(
id integer primary key auto_increment,
userid integer not null,
assets_purchase_priceid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(assets_purchase_priceid) references assets_purchase_price(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists assets_credit(
id integer primary key auto_increment,
userid integer not null,
assets_purchase_priceid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(assets_purchase_priceid) references assets_purchase_price(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);




create table if not exists revenue_debit(
id integer primary key auto_increment,
userid integer not null,
revenueid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(revenueid) references revenue(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists revenue_credit(
id integer primary key auto_increment,
userid integer not null,
revenueid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(revenueid) references revenue(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists expenses_debit(
id integer primary key auto_increment,
userid integer not null,
expensesid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(expensesid) references expenses(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);


create table if not exists expenses_credit(
id integer primary key auto_increment,
userid integer not null,
expensesid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(expensesid) references expenses(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);



create table if not exists exempt_user(
id integer primary key auto_increment,
userid integer not null,
fieldOfficerid integer not null,
used integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(fieldOfficerid) references users(id)
);



create table if not exists fixed_assets(
id integer primary key auto_increment,
userid integer not null,
sub_accountid integer not null,
foreign key(userid) references users(id),
foreign key(sub_accountid) references sub_accounts(id)
);




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




create table if not exists creditors_debtors(
id integer primary key auto_increment,
userid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
company_name varchar(100) not null,
location varchar(100) not null,
phone varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);




create table if not exists notes_payable(
id integer primary key auto_increment,
userid integer not null,
creditors_debtorid integer not null,
currencyid integer not null,
sub_accountid integer not null,
payment_typeid integer not null,
receipt_num varchar(100) not null,
principal_value double(100,2) not null,
face_value double(100,2) not null,
interest double(100,2) not null default 0,
time_months integer not null,
promissory_note_typeid integer not null,
due_date varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(currencyid) references currency(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(payment_typeid) references sub_accounts(id),
foreign key(creditors_debtorid) references creditors_debtors(id),
foreign key(promissory_note_typeid) references promissory_note_type(id)
);






create table if not exists notes_receivable(
id integer primary key auto_increment,
userid integer not null,
creditors_debtorid integer not null,
currencyid integer not null,
sub_accountid integer not null,
payment_typeid integer not null,
receipt_num varchar(100) not null,
principal_value double(100,2) not null,
face_value double(100,2) not null,
interest double(100,2) not null default 0,
time_months integer not null,
promissory_note_typeid integer not null,
due_date varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null, 
foreign key(userid) references users(id),
foreign key(currencyid) references currency(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(payment_typeid) references sub_accounts(id),
foreign key(creditors_debtorid) references creditors_debtors(id),
foreign key(promissory_note_typeid) references promissory_note_type(id)
);




create table if not exists notes_receivable_debit(
id integer primary key auto_increment,
userid integer not null,
notes_receivableid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(notes_receivableid) references notes_receivable(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);




create table if not exists notes_receivable_credit(
id integer primary key auto_increment,
userid integer not null,
notes_receivableid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(notes_receivableid) references notes_receivable(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);





create table if not exists notes_payable_debit(
id integer primary key auto_increment,
userid integer not null,
notes_payableid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(notes_payableid) references notes_payable(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);




create table if not exists notes_payable_credit(
id integer primary key auto_increment,
userid integer not null,
notes_payableid integer not null,
sub_accountid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(notes_payableid) references notes_payable(id),
foreign key(sub_accountid) references sub_accounts(id),
foreign key(userid) references users(id)
);



create table if not exists create_general_legder_accounts(
id integer primary key auto_increment,
userid integer not null,
sub_accountid integer not null,
account_name varchar(100) not null,
explanation varchar(100) not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(sub_accountid) references sub_accounts(id)
);


create table if not exists post_to_general_legder(
id integer primary key auto_increment,
userid integer not null,
create_general_legder_accountid integer not null,
post_ref integer not null,
explanation varchar(100),
debit_value double(100,2) not null default 0,
credit_value double(100,2) not null default 0,
balance_debit double(100,2) not null default 0,
balance_credit double(100,2) not null default 0,
balanced integer not null default 0,
transaction_date varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(create_general_legder_accountid) references create_general_legder_accounts(id)
);



create table if not exists balance_ledger(
id integer primary key auto_increment,
userid integer not null,
create_general_legder_accountid integer not null,
amount double(100,2) not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(create_general_legder_accountid) references create_general_legder_accounts(id)
);



create table if not exists number_of_bales_collected(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
quantity integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists fuel_allocation(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
fuel_userid integer not null,
quantity double(100,2) not null default 0,
allocation_date varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(fuel_userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists grower_new_image(
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,
image_location varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 grower_farm_image(
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,
image_location varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 grower_signatures(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 grower_id_image(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 hours_worked(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
hours double(100,2) not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists product_items(
id integer primary key auto_increment,
userid integer not null,
description varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);



create table if not exists itemized_product(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
productid integer not null,
product_itemid integer not null,
quantity integer not null ,
price double(100,2) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(productid) references products(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(product_itemid) references product_items(id)
);




create table if not exists assign_bale_tags(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
bale_tag varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




create table if not exists bale_collector(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
name varchar(100) not null,
surname varchar(100) not null,
id_num varchar(100) not null,
transporter_number varchar(100) not null,
number_plate varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists bale_collector_delivery_note(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collectorid integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
created_at varchar(100) not null,
open_close integer not null default 0,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(bale_collectorid) references bale_collector(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists collector_id_num(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists collector_signature(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists bales_collected(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
growerid integer not null,
bale_tag varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id),
foreign key(growerid) references growers(id)
);




create table if not exists bales_collected_total(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
growerid integer not null,
tags_total integer not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id),
foreign key(growerid) references growers(id)
);



create table if not exists grower_bale_signature(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
growerid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);








create table if not exists bales_received(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_collector_delivery_noteid integer not null,
bale_tag varchar(100) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bale_collector_delivery_noteid) references bale_collector_delivery_note(id)
);




create table if not exists tickets(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
image_location varchar(100) not null,
description varchar(300) not null,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists field_officer_task(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
task_url varchar(100) not null,
description varchar(100) not null,
duration_days integer not null,
task_min_growers integer not null default 20,
created_at varchar(100) not null,
end_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists task_viewer(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
task_url varchar(100) not null,
duration_days integer not null,
created_by_id integer not null,
task_min_growers integer not null default 20,
created_at varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(created_by_id) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists task_grower_data(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
task_url varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
created_at varchar(100) not null,
time_created varchar(100) not null,
sync integer not null default 0,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists live_location_status(
id integer primary key auto_increment,
active integer not null default 0
);


create table if not exists live_location_userid(
id integer primary key auto_increment,
userid integer not null,
firebaseid varchar(200) not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);



create table if not exists scheme(
id integer primary key auto_increment,
userid integer not null,
description varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id)
);

create table if not exists scheme_hectares(
id integer primary key auto_increment,
userid integer not null,
schemeid integer not null,
seasonid integer not null,
quantity varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(schemeid) references scheme(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists scheme_hectares_products(
id integer primary key auto_increment,
userid integer not null,
scheme_hectaresid integer not null,
productid integer not null,
quantity varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(scheme_hectaresid) references scheme_hectares(id),
foreign key(productid) references products(id)
);


create table if not exists scheme_hectares_growers(
id integer primary key auto_increment,
userid integer not null,
scheme_hectaresid integer not null,
growerid integer not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(scheme_hectaresid) references scheme_hectares(id),
foreign key(growerid) references growers(id)
);



create table if not exists otp (
  id integer primary key auto_increment,
  userid integer not null,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id)
);



create table if not exists disbursement_otp (
  id integer primary key auto_increment,
  storeid integer not null,
  productid integer not null,
  userid integer not null,
  quantity integer not null,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(storeid) references store(id),
  foreign key(productid) references products(id)
);




create table if not exists grower_inputs_distribution_otp (
  id integer primary key auto_increment,
  userid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id)
);




create table if not exists growers_otp (
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  growerid integer not null,
  used integer not null default 0,
  sent integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(growerid) references growers(id)
);



create table if not exists operations_contacts (
  id integer primary key auto_increment,
  email varchar(255) not null,
  phone varchar(255) not null,
  active integer not null default 1
);



create table if not exists battery_level_report(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  battery_level varchar(255) NOT NULL,
  time_created varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id)
);






create table if not exists start_battery_level(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  battery_level varchar(255) NOT NULL,
  time_created varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id)
);



create table if not exists end_battery_level(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  battery_level varchar(255) NOT NULL,
  time_created varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id)
);




create table if not exists leave_form(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  leave_type varchar(100) not null,
  description varchar(255) not null,
  days integer not null default 1,
  state integer not null default 0,
  leave_date varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id)
);



create table if not exists grower_finger_print(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  growerid integer not null,
  feature text not null,
  sync integer not null default 0,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(growerid) references growers(id)
);




create table if not exists grower_bale_booking(
  id integer primary key auto_increment,
  userid integer not null,
  booking_username varchar(100) not null,
  seasonid integer not null,
  growerid integer not null,
  bales integer not null,
  booking_date varchar(100) not null,
  created_time varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  sync integer not null default 0,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(growerid) references growers(id)
);




create table if not exists ministry_of_agricalture_numbers(
  id integer primary key auto_increment,
  userid integer not null,
  description varchar(100) not null,
  seasonid integer not null,
  datetime TIMESTAMP not null,
  used integer not null default 0,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id)
);






create table if not exists grower_to_ministry_of_agricalture_numbers(
  id integer primary key auto_increment,
  userid integer not null,
  growerid integer not null,
  ministry_of_agricalture_numbersid integer not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(ministry_of_agricalture_numbersid) references ministry_of_agricalture_numbers(id),
  foreign key(growerid) references growers(id)
);



create table if not exists company_details_and_contact(
  id integer primary key auto_increment,
  userid integer not null,
  company_name varchar(100) not null,
  address varchar(100) not null,
  phone_1 varchar(100) not null,
  phone_2 varchar(100) not null,
  phone_3 varchar(100) not null,
  email varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id)
);






create table if not exists purchasing_order(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  supplierid integer not null,
  description varchar(100) not null,
  order_number integer not null,
  active integer not null default 0,
  created_date varchar(100) not null,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(supplierid) references supplier(id)
);




create table if not exists purchasing_order_shipment(
  id integer primary key auto_increment,
  userid integer not null,
  purchasing_orderid integer not null,
  address varchar(100) not null,
  phone varchar(100) not null,
  email varchar(100) not null,
  created_date varchar(100) not null,
  foreign key(userid) references users(id),
  foreign key(purchasing_orderid) references purchasing_order(id)
);






   
create table if not exists purchasing_order_products(
  id integer primary key auto_increment,
  userid integer not null,
  purchasing_orderid integer not null,
  productid integer not null,
  quantity integer not null,
  quantity_received integer not null default 0,
  unit_price double(100,2) not null,
  created_date varchar(100) not null,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(purchasing_orderid) references purchasing_order(id),
  foreign key(productid) references products(id)
);







create table if not exists purchasing_order_received_products (
  id integer primary key auto_increment,
  userid integer not null,
  purchasing_orderid integer not null,
  storeid integer not null,
  productid integer not null,
  quantity integer not null,
  created_date varchar(100) not null,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(purchasing_orderid) references purchasing_order(id),
  foreign key(storeid) references store(id),
  foreign key(productid) references products(id)
);







create table if not exists grower_visit_schedule(
  id integer primary key auto_increment,
  userid integer not null,
  growerid integer not null,
  seasonid integer not null,
  end_at varchar(100) not null,
  created_at varchar(100) not null,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(growerid) references growers(id)
);


create table if not exists grower_groups(
id integer primary key auto_increment,
description varchar(100) not null
);





create table if not exists grower_field_officer_groups(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
field_officerid integer not null,
grower_groupid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(field_officerid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id),
foreign key(grower_groupid) references grower_groups(id)
);



create table if not exists seedbed_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,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP 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 system_receipt_number(
id integer primary key auto_increment,
userid integer not null ,
growerid integer not null,
seasonid integer not null,
receipt_number integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 disbursement_products(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id)
);



create table if not exists land_irrigation_growers_type(
id integer primary key auto_increment,
description varchar(100) not null
);



create table if not exists land_irrigation_growers(
id integer primary key auto_increment,
userid integer not null,
growerid integer not null,
seasonid integer not null,
land_irrigation_growers_typeid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(land_irrigation_growers_typeid) references land_irrigation_growers_type(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists performance_types(
id integer primary key auto_increment,
description varchar(200) not null
);




create table if not exists base_pay(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
rightsid integer not null,
amount double(100,4) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(rightsid) references rights(id)
);




create table if not exists performance_payments(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
performance_typesid integer not null,
amount double(100,4) not null,
rate double(100,4) not null default 100,
foreign key(userid) references users(id),
foreign key(performance_typesid) references performance_types(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists bike_maintanance_parts(
id integer primary key auto_increment,
description varchar(200) not null
);


create table if not exists bike_maintanance(
id integer primary key auto_increment,
userid integer not null,
field_officerid integer not null,
seasonid integer not null,
receipt_number varchar(100) not null,
description varchar(100) not null,
amount double(100,4) not null,
created_at varchar(100) not null,
maintanance_date varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(field_officerid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists grower_training(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
number_of_growers integer not null,
description varchar(100) not null,
image varchar(100) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists daily_fieldofficer_report(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
fieldofficerid integer not null,
numberOfVisits integer not null,
created_at varchar(100) not null,
datetime varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(fieldofficerid) references users(id)
);


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


create table if not exists grower_disbursed_products(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
productid integer not null,
quantity double(100,4) not null,
created_at varchar(100) not null,
datetime TIMESTAMP 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 if not exists custom_disbursement_product_quantity(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
quantity double(100,7) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id)
);







create table if not exists questionnaires(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
question varchar(100) not null,
type varchar(100) not null,
access_point varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
datetime_sync TIMESTAMP not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists questionnaires_answers_general(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
question varchar(100) not null,
answer varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
question_created_at varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
datetime_sync TIMESTAMP not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists questionnaires_answers_by_grower(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
question varchar(100) not null,
answer varchar(100) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
question_created_at varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
datetime_sync TIMESTAMP not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




create table if not exists loan_credit_note(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
productid integer not null,
quantity double(100,4) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id),
foreign key(productid) references products(id)
);


create table if not exists roll_over_interest(
id integer primary key auto_increment,
seasonid integer not null,
userid integer not null,
value double(100,4) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists roll_over_grower_amount_interest(
id integer primary key auto_increment,
growerid integer not null,
seasonid integer not null,
userid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);

create table if not exists balanced_finances(
id integer primary key auto_increment,
growerid integer not null,
seasonid integer not null,
userid integer not null,
year varchar(100) not null,
balance_b_f double(100,4) not null default 0,
stop_order double(100,4) not null default 0,
payments double(100,4) not null default 0,
outstanding double(100,4) not null default 0,
interest double(100,4) not null default 0,
balance double(100,4) not null default 0,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists active_balanced_finances(
id integer primary key auto_increment,
growerid integer not null,
seasonid integer not null,
userid integer not null,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);




create table if not exists gift_card(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
total_visits integer not null default 0,
visits_per_day integer not null default 0,
growers_to_visit integer not null default 0,
reward_percentage double(100,2) not null default 0,
reward varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
active integer not null default 1,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);







create table if not exists claim_reward(
id integer primary key auto_increment,
userid integer not null,
claimyid integer not null,
seasonid integer not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
total_visits_percentage double(100,4) not null,
daily_visits_percentage double(100,4) not null,
grower_percentage double(100,4) not null,
reward varchar(100) not null,
gift_card_created_at varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(claimyid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists weekly_plan(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
system_generated_weekly_visits integer not null,
weekly_visits integer not null,
daily_visits integer not null,
no_of_visits_per_grower integer not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists weekly_points_earned(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
points integer not null default 0,
target_points integer not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists total_points_earned(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
points integer not null default 0,
target_points integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);








create table if not exists monthly_salary_claims(
id integer primary key auto_increment,
userid integer not null,
claimyid integer not null,
seasonid integer not null,
month varchar(100) not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
salary double(200,4) not null default 0,
hectares double(200,4) not null default 0,
daily_reports double(200,4) not null default 0,
grower_visits double(200,4) not null default 0,
system_based_tasks double(200,4) not null default 0,
bike_maintenance double(200,4) not null default 0,
ctl_related double(200,4) not null default 0,
training_and_demo double(200,4) not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(claimyid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists basic_salary_amounts(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
rightsid integer not null,
amount double(100,4) not null default 0,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(rightsid) references rights(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists salary_dates_and_months(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
month varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists monthly_salary_claims_performance(
id integer primary key auto_increment,
userid integer not null,
claimyid integer not null,
seasonid integer not null,
month varchar(100) not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
days_worked double(200,4) not null default 0,
hectares double(200,4) not null default 0,
daily_reports double(200,4) not null default 0,
grower_visits double(200,4) not null default 0,
system_based_tasks double(200,4) not null default 0,
bike_maintenance double(200,4) not null default 0,
ctl_related double(200,4) not null default 0,
training_and_demo double(200,4) not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(claimyid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists allow_rollover_on_loan_statement(
id integer primary key auto_increment,
allow integer not null default 1
);




create table if not exists questionnaires_bales_answers_by_grower(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
question varchar(100) not null,
bales integer not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
question_created_at varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
datetime_sync TIMESTAMP not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);










create table if not exists bale_receiving_product(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
productid integer not null,
active integer not null default 1,
created_at varchar(100) not null,
datetime TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(productid) references products(id),
foreign key(seasonid) references seasons(id)
);





create table if not exists field_officer_warehouse(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
field_officerid integer not null,
storeid integer not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(storeid) references store(id),
foreign key(seasonid) references seasons(id)
);









create table if not exists salary_variables(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
hectares double(200,4) not null default 0,
daily_reports double(200,4) not null default 0,
grower_visits double(200,4) not null default 0,
system_based_tasks double(200,4) not null default 0,
bike_maintenance double(200,4) not null default 0,
ctl_related double(200,4) not null default 0,
training_and_demo double(200,4) not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




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


create table if not exists receive_bales_to_warehouse(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
storeid integer not null,
questionnaires_bales_answers_by_growerid integer not null,
quantity integer not null,
created_at varchar(100) not null,
sell_date varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(storeid) references store(id),
foreign key(questionnaires_bales_answers_by_growerid) references questionnaires_bales_answers_by_grower(id)
);


create table if not exists bale_tags_and_lots(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
receive_bales_to_warehouseid integer not null,
lot integer not null,
barcode varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(receive_bales_to_warehouseid) references receive_bales_to_warehouse(id)
);



create table if not exists warehousing_sold_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
grower_num varchar(100) not null,
barcode varchar(100) not null,
lot varchar(100) not null,
buyer_grade varchar(100) not null,
timb_grade varchar(100) not null,
buyer_mark varchar(100) not null,
location varchar(100) not null,
mass double(100,4) not null,
price double(100,4) not null,
sell_date varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists warehousing_grades_batches(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
buyer_grade varchar(100) not null,
bale_batch integer not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists warehousing_sold_bales_reclassification(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bale_batch integer not null default 0,
warehousing_sold_balesid integer not null,
buyer_grade varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(warehousing_sold_balesid) references warehousing_sold_bales(id)
);



create table if not exists floor_dispatched_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
warehousing_sold_balesid integer not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(warehousing_sold_balesid) references warehousing_sold_bales(id)
);




create table if not exists verify_sold_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
barcode varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table if not exists shipment_details(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
shipment varchar(100) not null,
mass double(100,4) not null default 0,
bales integer not null default 0,
location varchar(100) not null,
dispatch_date varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists shipment_file(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
shipment_detailsid integer not null,
grower_num varchar(100) not null,
barcode varchar(100) not null,
lot varchar(100) not null,
location varchar(100) not null,
mass double(100,4) not null,
price double(100,4) not null,
sell_date varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(shipment_detailsid) references shipment_details(id)
);



create table if not exists warehousing_storage_received_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
storeid integer not null,
warehousing_sold_balesid integer not null,
shipment_detailsid integer not null,
shipment_fileid integer not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(storeid) references store(id),
foreign key(warehousing_sold_balesid) references warehousing_sold_bales(id),
foreign key(shipment_detailsid) references shipment_details(id),
foreign key(shipment_fileid) references shipment_file(id)
);



create table if not exists warehousing_storage_received_bale_mass(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
warehousing_sold_balesid integer not null,
mass double(100,4) not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(warehousing_sold_balesid) references warehousing_sold_bales(id)
);


create table if not exists location_sell_date(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
sell_date varchar(100) not null,
location varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists total_sold_received(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
sold_mass double(100,4) not null default 0,
received_mass double(100,4) not null default 0,
sold_bales integer not null default 0,
received_bales integer not null default 0,
sold_total_value double(100,4) not null default 0,
received_total_value double(100,4) not null default 0,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists salary_allocated_hectares(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
min_hectares double(100,4) not null,
max_hectares double(100,4) not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists salary_allocated_hectares_basic_salary(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
salary_allocated_hectaresid integer not null,
amounts double(100,4) not null,
active integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(salary_allocated_hectaresid) references salary_allocated_hectares(id)
);



create table if not exists custom_based_salary(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
active_userid integer not null,
amounts double(100,4) not null,
active integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(active_userid) references users(id)
);



create table if not exists salary_target_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
salary_allocated_hectaresid integer not null,
bales integer not null,
active integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(salary_allocated_hectaresid) references salary_allocated_hectares(id)
);



create table if not exists salary_recovery_target(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
salary_allocated_hectaresid integer not null,
recovery double(100,4) not null,
active integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(salary_allocated_hectaresid) references salary_allocated_hectares(id)
);


create table if not exists field_officer_to_salary_allocated_hectares(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
field_officerid integer not null,
salary_allocated_hectaresid integer not null,
created_at varchar(100) not null,
active integer not null default 0,
foreign key(userid) references users(id),
foreign key(field_officerid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(salary_allocated_hectaresid) references salary_allocated_hectares(id)
);




create table if not exists payroll_structure(
id integer primary key auto_increment,
description varchar(100) not null,
active integer not null default 0
);



create table if not exists bonus_structure(
id integer primary key auto_increment,
description varchar(100) not null,
active integer not null default 0
);





create table if not exists bales_bonus_structures_values(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bonus_structureid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bonus_structureid) references bonus_structure(id)
);



create table if not exists recovery_bonus_structures_values(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bonus_structureid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bonus_structureid) references bonus_structure(id)
);




create table if not exists hectares_bonus_structures_values(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bonus_structureid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bonus_structureid) references bonus_structure(id)
);


create table if not exists target_working_days(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
days integer not null default 15,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists days_worked_penalty_structures_values(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bonus_structureid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bonus_structureid) references bonus_structure(id)
);



create table if not exists monthly_salary_claims_penalty(
id integer primary key auto_increment,
userid integer not null,
claimyid integer not null,
seasonid integer not null,
month varchar(100) not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
days double(200,4) not null default 0 comment 'amount to be deducted in usd',
hectares double(200,4) not null default 0 comment 'amount to be deducted in usd',
bales double(200,4) not null default 0 comment 'amount to be deducted in usd',
recovery double(200,4) not null default 0 comment 'amount to be deducted in usd',
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(claimyid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists processing_dispatch_truck(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
storeid integer not null,
truck_horse varchar(100) not null,
truck_trailer varchar(100) not null,
driver_name varchar(100) not null,
driver_surname varchar(100) not null,
destination varchar(100) not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(storeid) references store(id)
);





create table if not exists processing_dispatch_truck_grades(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
processing_dispatch_truckid integer not null,
grade varchar(100) not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(processing_dispatch_truckid) references processing_dispatch_truck(id)
);



create table if not exists truck_to_processing_bales(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
processing_dispatch_truckid integer not null,
warehousing_sold_bales_reclassificationid integer not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(warehousing_sold_bales_reclassificationid) references warehousing_sold_bales_reclassification(id),
foreign key(processing_dispatch_truckid) references processing_dispatch_truck(id)
);



create table if not exists grower_edit_otp (
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  growerid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(userid) references users(id),
  foreign key(seasonid) references seasons(id),
  foreign key(growerid) references growers(id)
);


create table if not exists grower_bulk_edit_otp (
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id)
);



create table if not exists authorization_email_otp(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  email varchar(100) not null,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id)
);


create table if not exists default_system_emails (
  id integer primary key auto_increment,
  email varchar(255) NOT NULL unique
);



create table if not exists activate_deactivate_loan_capturing(
  id integer primary key auto_increment,
  active integer not null default 1
);


create table if not exists reverse_all_growers_otp(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id)
);



create table if not exists reverse_loan_by_product_otp(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  productid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(productid) references products(id)
);



create table if not exists reverse_loan_by_field_officer_otp(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  field_officerid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(field_officerid) references users(id)
);



create table if not exists purchase_order_otp(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  purchasing_orderid integer not null,
  storeid integer not null,
  blocked integer not null default 0,
  used integer not null default 0,
  otp varchar(255) NOT NULL unique,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(purchasing_orderid) references purchasing_order(id),
  foreign key(storeid) references store(id)
);





create table if not exists grower_payment_schedule(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  growerid integer not null,
  sale_no integer not null,
  payment_batch integer not null,
  payee varchar(255) not null,
  nid varchar(255) not null,
  bank varchar(255) not null,
  branch_code varchar(255) not null,
  zwl_acc varchar(255) not null,
  fca_acc varchar(255) not null,
  payment_ref varchar(255) not null,
  usd_actual_net_paid double(100,4) not null,
  zim_actual_net_paid double(100,4) not null,
  sale_date varchar(255) not null,
  floor_id varchar(255) not null,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(growerid) references growers(id)
);




create table if not exists payment_schedule_grower_details(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  grower_payment_scheduleid  integer not null,
  name varchar(255) NOT NULL,
  surname varchar(255) NOT NULL,
  gender varchar(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(grower_payment_scheduleid) references grower_payment_schedule(id)
);



create table if not exists bank_details(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  name varchar(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id)
);




create table if not exists payment_schedule_grower_bank_details(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  bank_detailsid integer not null,
  currencyid integer not null,
  grower_payment_scheduleid  integer not null,
  account varchar(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(grower_payment_scheduleid) references grower_payment_schedule(id),
  foreign key(currencyid) references currency(id),
  foreign key(bank_detailsid) references bank_details(id)
);




create table if not exists payment_schedule_grower_ecocash_details(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  bank_detailsid integer not null,
  currencyid integer not null,
  grower_payment_scheduleid  integer not null,
  account varchar(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(grower_payment_scheduleid) references grower_payment_schedule(id),
  foreign key(currencyid) references currency(id),
  foreign key(bank_detailsid) references bank_details(id)
);




create table if not exists payment_schedule_grower_mukuru_details(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  bank_detailsid integer not null,
  currencyid integer not null,
  grower_payment_scheduleid  integer not null,
  account varchar(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(grower_payment_scheduleid) references grower_payment_schedule(id),
  foreign key(currencyid) references currency(id),
  foreign key(bank_detailsid) references bank_details(id)
);





create table if not exists payment_schedule_captured(
  id integer primary key auto_increment,
  userid integer not null,
  seasonid integer not null,
  grower_payment_scheduleid  integer not null,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  foreign key(seasonid) references seasons(id),
  foreign key(userid) references users(id),
  foreign key(grower_payment_scheduleid) references grower_payment_schedule(id)
);



create table if not exists grower_field_loans(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
productid integer not null,
quantity double(100,4) not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
hectares varchar(100) not null,
android_captureid integer not null default 0,
farmer_comment varchar(100) not null,
adjustment_quantity double(100,4) not null,
adjust integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetimes 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 if not exists zero_bale_comment(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
comment varchar(100) not null,
next_delivery_date varchar(100) not null ,
question varchar(100) not null,
question_date varchar(100) not null ,
created_at varchar(100) not null,
datetimes varchar(100) not null,
sync integer not null default 0,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(growerid) references growers(id)
);



create table if not exists disbursed_products_grower_truck(
id integer primary key auto_increment,
userid integer not null,
disbursement_trucksid integer not null,
growerid integer not null,
productid integer not null,
quantity double not null,
latitude varchar(100) not null,
longitude varchar(100) not null,
seasonid integer not null,
farmer_comment varchar(100) not null,
adjustment_quantity double not null,
adjust integer not null default 0,
sync integer not null default 0,
surrogate integer not null default 0,
created_at varchar(100) not null,
datetimes 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),
foreign key(disbursement_trucksid) references truck_destination(id)
);





create table if not exists file_manager(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
location_url varchar(200) not null,
description varchar(100) not null,
file_type varchar(100) not null,
storages varchar(100) not null,
created_at varchar(100) not null,
datetimes 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 daily_movements(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
latitude varchar(200) not null,
longitude varchar(100) not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetimes varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists documents_descriptions(
id integer primary key auto_increment,
description varchar(100) not null
);



create table if not exists blende_name(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
description varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);






create table if not exists blende_grades(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
blenderid integer not null,
grade varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(blenderid) references blende_name(id)
);






create table if not exists file_manager_purchase_order(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
purchasing_orderid integer not null,
location_url varchar(200) not null,
description varchar(100) not null,
file_type varchar(100) not null,
storages varchar(100) not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
 foreign key(purchasing_orderid) references purchasing_order(id)
);


create table if not exists sms_config_otp(
id integer primary key auto_increment,
userid integer not null,
otp varchar(100) not null,
foreign key(userid) references users(id)
);





create table if not exists phone_country_code(
id integer primary key auto_increment,
userid integer not null,
text_message_display varchar(100) not null,
country_code varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id)
);


create table if not exists contract_definition_of_terms(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
description varchar(100) not null,
bold integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);

create table if not exists contract_sections(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
have_subtitle integer not null default 0,
description varchar(100) not null,
bold integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists contract_sections_subtitle(
id integer primary key auto_increment,
contract_sectionsid integer not null,
userid integer not null,
seasonid integer not null,
description varchar(100) not null,
bold integer not null default 1,
created_at varchar(100) not null,
foreign key(contract_sectionsid) references contract_sections(id),
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);

create table if not exists contract_sections_subtitle_info(
id integer primary key auto_increment,
contract_sections_subtitleid integer not null,
userid integer not null,
seasonid integer not null,
description varchar(100) not null,
bold integer not null default 1,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(contract_sections_subtitleid) references contract_sections_subtitle(id)
);



create table if not exists warehouse_bale_recount(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
barcode varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);



create table if not exists wrong_grower_capture_position(
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,
distance double(100,4) not null,
accuracy double(100,4) not null,
sync integer not null default 0,
created_at varchar(100) not null,
datetimes 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 wrong_grower_capture_position_penalty_structures_values(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
bonus_structureid integer not null,
amount double(100,4) not null,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(bonus_structureid) references bonus_structure(id)
);



create table if not exists target_wrong_position(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
positions integer not null default 15,
created_at varchar(100) not null,
active integer not null default 1,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists grades_description(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
description varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);


create table if not exists grades_description_categories(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
grades_descriptionid integer not null,
grade varchar(100) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(grades_descriptionid) references grades_description_categories(id)
);




create table if not exists bale_counting_redo(
id integer primary key auto_increment,
userid integer not null,
grade varchar(100) not null,
barcode varchar(100) not null,
mass double(100,4) not null,
price double(100,4) not null,
created_at varchar(100) not null,
foreign key(userid) references users(id)
);




create table if not exists grower_signatures_schemes(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
hectares varchar(100) not null,
datetime TIMESTAMP 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 truck_grower_qrcode_disbursed(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
disbursement_trucksid integer not null,
growerid integer not null,
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(disbursement_trucksid) references truck_destination(id)
);



create table if not exists disburse_products_by_date(
id integer primary key auto_increment,
userid integer not null,
productid integer not null,
seasonid integer not null,
created_at varchar(100) not null,
start_date varchar(100) not null,
end_date varchar(100) not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id),
foreign key(productid) references products(id)
);



 


CREATE TABLE IF NOT EXISTS accounts_branch(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    active integer not null default 1,
    created_at varchar(100) not null,
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id)
);



CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    growerid int default 0,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    created_at varchar(100) not null,
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id)
);





CREATE TABLE IF NOT EXISTS accounts_receivable_notes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    currencyid INT NOT NULL,
    customer_id INT NOT NULL,
    note_date DATE NOT NULL,
    due_date DATE,
    original_amount DECIMAL(18, 4) NOT NULL,
    outstanding_amount DECIMAL(18, 4) NOT NULL,
    description VARCHAR(255),
    status VARCHAR(50) NOT NULL DEFAULT 'Open', -- e.g., 'Open', 'Partially Paid', 'Paid', 'Overdue'
    created_at varchar(100) not null,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id),
    FOREIGN KEY (currencyid) REFERENCES currency(id)
);


CREATE TABLE IF NOT EXISTS accounts_receivable_notes_payments(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    accounts_receivable_notesid integer not null,
    amount DECIMAL(18, 4) NOT NULL,
    created_at varchar(100) not null,
    FOREIGN KEY (accounts_receivable_notesid) REFERENCES accounts_receivable_notes(id),
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id)
);


CREATE TABLE IF NOT EXISTS accounts_payable_notes(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    currencyid INT NOT NULL,
    supplierid INT NOT NULL,
    note_date DATE NOT NULL,
    due_date DATE,
    original_amount DECIMAL(18, 4) NOT NULL,
    outstanding_amount DECIMAL(18, 4) NOT NULL,
    amount DECIMAL(18, 4) NOT NULL,
    description VARCHAR(255),
    status VARCHAR(50) NOT NULL DEFAULT 'Open', -- e.g., 'Open', 'Partially Paid', 'Paid', 'Overdue'
    created_at varchar(100) not null,
    FOREIGN KEY (supplierid) REFERENCES supplier(id),
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id),
    FOREIGN KEY (currencyid) REFERENCES currency(id)
);




CREATE TABLE IF NOT EXISTS accounts_payable_notes_payments(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid integer not null,
    seasonid integer not null,
    accounts_payable_notesid integer not null,
    amount DECIMAL(18, 4) NOT NULL,
    created_at varchar(100) not null,
    FOREIGN KEY (accounts_payable_notesid) REFERENCES accounts_payable_notes(id),
    foreign key(userid) references users(id),
	foreign key(seasonid) references seasons(id)
);




CREATE TABLE IF NOT EXISTS transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userid INT NOT NULL,
    account_branchid integer not null,
    seasonid INT NOT NULL,
    currencyid INT NOT NULL,
    description VARCHAR(100) NOT NULL,
    receipt_num VARCHAR(100),
    amount DECIMAL(18, 4) NOT NULL DEFAULT 0,
    debit_sub_accountsid INT NOT NULL,
    credit_sub_accountsid INT NOT NULL,
    incomeid int COMMENT 'income id for the transaction ',
    expensesid INT COMMENT 'expenses id for the transaction ',
    receivable_note_id INT, -- Nullable: only set if this transaction relates to a receivable note
    payable_note_id INT,    -- Nullable: only set if this transaction relates to a payable note
    receivable_note_paymentsid INT, -- Nullable: only set if this transaction relates to a receivable note
    payable_note_paymentsid INT,    -- Nullable: only set if this transaction relates to a payable note
    created_at varchar(100) not null,
    datetimes TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (seasonid) REFERENCES seasons(id),
    FOREIGN KEY (userid) REFERENCES users(id),
    FOREIGN KEY (currencyid) REFERENCES currency(id),
    FOREIGN KEY (debit_sub_accountsid) REFERENCES sub_accounts(id),
    FOREIGN KEY (credit_sub_accountsid) REFERENCES sub_accounts(id)
);




create table if not exists truck_grower_qrcode_disbursed_mobile(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
disbursement_trucksid integer not null,
growerid integer not null,
receipt_num varchar(100) not null,
hectares varchar(100) not null,
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(disbursement_trucksid) references truck_destination(id)
);



create table if not exists user_finger_print(
  id integer primary key auto_increment,
  userid integer not null,
  userid_verifier integer not null,
  seasonid integer not null,
  feature text not null,
  sync integer not null default 0,
  datetime TIMESTAMP not null,
  foreign key(userid) references users(id),
  foreign key(userid_verifier) references users(id),
  foreign key(seasonid) references seasons(id)
);



create table if not exists officials_signatures(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
image_location varchar(100) not null,
created_at varchar(100) not null,
datetimes TIMESTAMP not null,
foreign key(userid) references users(id),
foreign key(seasonid) references seasons(id)
);




create table if not exists disbursement_finger_print_verification_and_approval(
id integer primary key auto_increment,
userid integer not null,
seasonid integer not null,
growerid integer not null,
disbursement_trucksid integer not null,
userid_verifier integer not null,
created_at varchar(100) not null,
datetimes varchar(100) not null,
foreign key(userid) references users(id),
foreign key(growerid) references growers(id),
foreign key(seasonid) references seasons(id),
foreign key(userid_verifier) references users(id),
foreign key(disbursement_trucksid) references truck_destination(id)
);


ALTER TABLE bale_counting_redo
ADD batch_no integer not null default 0;


ALTER TABLE bale_counting_redo
ADD sell_date text not null;

ALTER TABLE bale_counting_redo
ADD lot text not null;

ALTER TABLE bale_counting_redo
ADD grower_num text not null;


ALTER TABLE bale_counting_redo
ADD new_mass double(100,4) not null;


ALTER TABLE bale_counting_redo
ADD old_barcode text not null;

ALTER TABLE user_finger_print
ADD description text not null;

ALTER TABLE grower_finger_print
ADD description text not null;


insert into documents_descriptions(description)value("Grower Id");
insert into documents_descriptions(description)value("Grower Image");
insert into documents_descriptions(description)value("Grower Contract");
insert into documents_descriptions(description)value("Disbursement Statement");
insert into documents_descriptions(description)value("Loan Statement");
insert into documents_descriptions(description)value("Recovery Statement");



insert into activate_deactivate_loan_capturing(active)value(0);

insert into default_system_emails(email)value("brightkaponda96@gmail.com");

insert into bonus_structure(description)value("Fixed Amount");
insert into bonus_structure(description)value("Multiple Of Extra");


insert into payroll_structure(description)value("Standard");
insert into payroll_structure(description)value("Hectares Based");
insert into payroll_structure(description)value("Customised");

insert into allow_rollover_on_loan_statement(allow)value(1);


ALTER TABLE scheme_hectares_products
ADD active integer default 1;



insert into performance_types(description) value("Hectares");
insert into performance_types(description) value("Daily Reports");
insert into performance_types(description) value("Grower Visits");
insert into performance_types(description) value("System Tasks");
insert into performance_types(description) value("Bike Maintenance");
insert into performance_types(description) value("Training");
insert into performance_types(description) value("Other");




insert into land_irrigation_growers_type(description) value("Irrigation Land");
insert into land_irrigation_growers_type(description) value("Dry Land");


insert into grower_groups(description) value("A");
insert into grower_groups(description) value("B");
insert into grower_groups(description) value("C");
insert into grower_groups(description) value("D");
insert into grower_groups(description) value("E");
insert into grower_groups(description) value("F");
insert into grower_groups(description) value("G");
insert into grower_groups(description) value("H");



insert into machine_learning_product_variables(description) value("Basal");
insert into machine_learning_product_variables(description) value("AN");
insert into machine_learning_product_variables(description) value("Potassium");
insert into machine_learning_product_variables(description) value("Seed");

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



insert into rights(description,notes) value("System administrator","System administrator1");
insert into rights(description,notes) value("I.T Assistant","I.T Assistant2");
insert into rights(description,notes) value("Accountant","Accountant3");
insert into rights(description,notes) value("Account Clerk","Account Clerk4");
insert into rights(description,notes) value("Hr Admin","Hr Admin5");
insert into rights(description,notes) value("Hr Assistant","Hr Assistant6");
insert into rights(description,notes) value("Area Manager","Area Manager7");
insert into rights(description,notes) value("Field Officer","Field Officer8");
insert into rights(description,notes) value("Field Officer(Loans Access)","Field Officer9");
insert into rights(description,notes) value("Management","Management10");
insert into rights(description,notes) value("Registration","Registration11");
insert into rights(description,notes) value("WareHouse Supervisor","WareHouse Supervisor12");
insert into rights(description,notes) value("Insurance","Insurance13");
insert into rights(description,notes) value("Company","Company14");
insert into rights(description,notes) value("Surrogate","Surrogate15");
insert into rights(description,notes) value("Check Point","Check Point16");
insert into rights(description,notes) value("Agronomist","Agronomy17");
insert into rights(description,notes) value("Company Selling Point","SellingPoint18");
insert into rights(description,notes) value("Chairman","Chairman19");
insert into rights(description,notes) value("Dispatch Clerk","Dispatch20");
insert into rights(description,notes) value("WareHouse Clerk","Warehouse21");

insert into users(name,surname,username,hash,rightsid,active,access_code,created_at) value("sysadmin","sysadmin","sysadmin","173309254d744ee11186193a1b1b0394",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");
insert into charges(name) value("Transport");
insert into charges(name) value("Agronomy");


insert into download_growers_rule(rule) value(0);


insert into product_type(name) value("LandPack-Fertilizer");
insert into product_type(name) value("Seedbed Pack");
insert into product_type(name) value("LandPack-Chemicals");
insert into product_type(name) value("Packaging");
insert into product_type(name) value("Other");


insert into selling_points(name) value("Rusape");
insert into selling_points(name) value("Harare");
insert into selling_points(name) value("Karoi");
insert into selling_points(name) value("Mvurwi");
insert into selling_points(name) value("Marondera");
insert into selling_points(name) value("Bindura");

insert into currency(description) value("USD");
insert into currency(description) value("RANDS");
insert into currency(description) value("ZWL");

insert into loan_deduction_point(point) value(0);



insert into balancing_side(description) value("Debit");
insert into balancing_side(description) value("Credit");


insert into main_accounts(description) value("Assets");
insert into main_accounts(description) value("Liabilities");
insert into main_accounts(description) value("Stockholder's Equity");
insert into main_accounts(description) value("Revenue");
insert into main_accounts(description) value("Cost Of Merchandise Sold");
insert into main_accounts(description) value("Expenses");


insert into main_account_balancing_side(main_accountid,balancing_sideid)value(1,1);
insert into main_account_balancing_side(main_accountid,balancing_sideid)value(2,2);
insert into main_account_balancing_side(main_accountid,balancing_sideid)value(3,2);
insert into main_account_balancing_side(main_accountid,balancing_sideid)value(4,2);
insert into main_account_balancing_side(main_accountid,balancing_sideid)value(5,1);
insert into main_account_balancing_side(main_accountid,balancing_sideid)value(6,1);


insert into sub_accounts(description,main_accountid) value("Cash In Bank",1);
insert into sub_accounts(description,main_accountid) value("Cash In Hand",1);
insert into sub_accounts(description,main_accountid) value("Accounts Receivable",1);
insert into sub_accounts(description,main_accountid) value("Merchandise Inventory",1);
insert into sub_accounts(description,main_accountid) value("Office Supplies",1);
insert into sub_accounts(description,main_accountid) value("Store Supplies",1);
insert into sub_accounts(description,main_accountid) value("Prepaid Insurance",1);
insert into sub_accounts(description,main_accountid) value("Store Equipment",1);
insert into sub_accounts(description,main_accountid) value("Office Equipment",1);
insert into sub_accounts(description,main_accountid) value("Computer Equipment",1);
insert into sub_accounts(description,main_accountid) value("Other",1);


insert into sub_accounts(description,main_accountid) value("Accounts Payable",2);
insert into sub_accounts(description,main_accountid) value("Income Tax Payable",2);
insert into sub_accounts(description,main_accountid) value("Sales Tax Payable",2);
insert into sub_accounts(description,main_accountid) value("Other",2);


insert into sub_accounts(description,main_accountid) value("Capital Stock",3);
insert into sub_accounts(description,main_accountid) value("Retained Earnings",3);
insert into sub_accounts(description,main_accountid) value("Income Summary",3);
insert into sub_accounts(description,main_accountid) value("Other",3);


insert into sub_accounts(description,main_accountid) value("Sales",4);
insert into sub_accounts(description,main_accountid) value("Sales Returns And Allowances",4);
insert into sub_accounts(description,main_accountid) value("Sales Discounts",4);
insert into sub_accounts(description,main_accountid) value("Commission Receivable",4);
insert into sub_accounts(description,main_accountid) value("Interest Income",4);
insert into sub_accounts(description,main_accountid) value("Other",4);



insert into sub_accounts(description,main_accountid) value("Purchases",5);
insert into sub_accounts(description,main_accountid) value("Purchases Returns And Allowances",5);
insert into sub_accounts(description,main_accountid) value("Purchases Discounts",5);
insert into sub_accounts(description,main_accountid) value("Transport In",5);
insert into sub_accounts(description,main_accountid) value("Other",5);



insert into sub_accounts(description,main_accountid) value("Bank Card Fees Expenses",6);
insert into sub_accounts(description,main_accountid) value("Insurance Expenses",6);
insert into sub_accounts(description,main_accountid) value("Miscellaneous Expenses",6);
insert into sub_accounts(description,main_accountid) value("Office Supplies Expenses",6);
insert into sub_accounts(description,main_accountid) value("Rent Expenses",6);
insert into sub_accounts(description,main_accountid) value("Salaries Expenses",6);
insert into sub_accounts(description,main_accountid) value("Store Supplies Expenses ",6);
insert into sub_accounts(description,main_accountid) value("Utilities Expenses",6);
insert into sub_accounts(description,main_accountid) value("Income Tax Expenses",6);
insert into sub_accounts(description,main_accountid) value("Advertising Expenses",6);
insert into sub_accounts(description,main_accountid) value("Maintenance Expenses",6);
insert into sub_accounts(description,main_accountid) value("Depreciation expense",6);
insert into sub_accounts(description,main_accountid) value("Commission Payable",6);
insert into sub_accounts(description,main_accountid) value("Interest Expense",6);
insert into sub_accounts(description,main_accountid) value("Other",6);


insert into live_location_status(active) value(0);




ALTER TABLE loans
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE truck_to_grower
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE total_disbursement
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE store_items
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE product_split
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE disbursement
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE arc_products
MODIFY COLUMN old_quantity double(100,3) default 0;

ALTER TABLE arc_products
MODIFY COLUMN new_quantity double(100,3) default 0;


ALTER TABLE arc_store_items
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE arc_store_items
MODIFY COLUMN quantity_balance double(100,3) default 0;


ALTER TABLE returned_stock
MODIFY COLUMN quantity double(100,3) default 0;



ALTER TABLE purchasing_order_products
MODIFY COLUMN quantity double(100,3) default 0;

ALTER TABLE purchasing_order_products
MODIFY COLUMN quantity_received double(100,3) default 0;


ALTER TABLE arc_product_truck
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE disbursement_otp
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE arc_store_hold_area
MODIFY COLUMN new_quantity double(100,3) default 0;


ALTER TABLE arc_store_hold_area
MODIFY COLUMN old_quantity double(100,3) default 0;



ALTER TABLE store_hold_area
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE transfer_products
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE scheme_hectares_products
MODIFY COLUMN quantity double(100,3) default 0;


ALTER TABLE loan_payments
ADD reference_num varchar(255) not null;

ALTER TABLE loan_payments
ADD payment_date varchar(255) not null;


ALTER TABLE loan_payments
MODIFY COLUMN amount double(100,7) default 0;


ALTER TABLE rollover
ADD calculate_interest integer default 0;


ALTER TABLE monthly_salary_claims
ADD bales double(100,4) default 0;


ALTER TABLE monthly_salary_claims
ADD recovery double(100,4) default 0;


ALTER TABLE loan_payments
ADD bales integer not null default 0;


ALTER TABLE loan_payment_total
ADD bales integer not null default 0;



ALTER TABLE floor_dispatched_bales
ADD horse varchar(100) not null;


ALTER TABLE floor_dispatched_bales
ADD trailer varchar(100) not null;


ALTER TABLE floor_dispatched_bales
ADD location varchar(100) not null;


ALTER TABLE loans
ADD comments varchar(100) not null;

ALTER TABLE loans
ADD adjustment_quantity double(100,4) not null;

ALTER TABLE loans
ADD adjust integer not null default 0;



ALTER TABLE shipment_details
ADD supplierid integer not null;



ALTER TABLE truck_to_processing_bales
ADD mass double(100,4) not null default 0;



ALTER TABLE truck_to_processing_bales
ADD lot double(100,4) not null default 0;


ALTER TABLE monthly_salary_claims
ADD positions integer default 0;


ALTER TABLE monthly_salary_claims_penalty
ADD positions integer default 0;


ALTER TABLE loans
ADD qr_code_loan integer not null default 0;





ALTER TABLE bale_counting_redo
ADD swapped integer not null default 0;



ALTER TABLE transactions
ADD purchasing_order_received_productsid int;


ALTER TABLE reverse_all_growers_otp
ADD growerid integer not null;

