o hcjD@s dZddlZddlZddlZddlZddlZddlZddlZddlZddl Z ddl Z ddl Z ddl mZm Z mZddlmZddlZddlmZmZmZmZmZddlmZddlmZmZddlmZm Z m!Z!m"Z"m#Z#m$Z$m%Z%m&Z&m'Z'm(Z(m)Z)m*Z*m+Z+m,Z,m-Z-e.e/Z0ed e/Z1e&e(e)e'e%ej2j3d Z4hd Z5hd Z6d dhZ7hdZ8da9e:Z;e<Z=e:Z>e<Z?iZ@dZAdZBdZCddZDddZEddZFddZGddZHddZId d!ZJdd"d#ZKd$d%ZLd&ZMd'd(ZNd)d*ZOdd+d,ZPd-d.ZQdd/d0ZRd1d2ZSd3d4ZTd5d6ZUd7d8ZVd9d:ZWd;d<ZXd=d>ZYd?d@ZZdAdBZ[ddDdEZ\dFdGZ]dHdIZ^dJZ_e`dKdLhZae`dMhZbdNZcdOZde`hdPZedQdRdSdTdUdVdWZfdXdYZgdZd[Zhdd]d^Zid_d`ZjdadbZkdcddZlddedfZmdgdhZndidjZodkdlZpdmdnZqdodpZrdqdrZsdsdtZtdudvZudwdxZvdydzZwd{d|Zxdd~dZyddZzdddZ{ddZ|ddZ}dddZ~dddZdddZddZddZdddZdddZddZdddZdeBfddZdddZddZdZddZddZddZddZddZddZddZddZddZddZddZddZddZddZddZddZddÄZdddńZddDŽZdddɄZddd˄Zdd̈́ZddτZdddфZddӄZeAfddՄZddׄZddلZddۄZddd݄Zdd߄ZddZddZddZddZddZddZdddZddZdddZddZddZddZddZddZddZdddZddZddZddZe1jddZe1dd d Ze1jd d gd ddZe1jd dgd ddZe1jdd gd ddZe1jdd gd ddZe1jddgd ddZe1jddgd ddZe1jddgd d d!Ze1jd"d gd d#d$Ze1jd%d gd d&d'Ze1jd(d gd d)d*Ze1jd"dgd d+d,Ze1jd(dgd d-d.Ze1jd/dgd d0d1Ze1jd(dgd d2d3Ze1jd4d gd d5d6Ze1jd4dgd d7d8Ze1jd9dgd d:d;Ze1jd9dgd d<d=Ze1d>d?d@Ze1dAdBdCZe1jdDd gd dEdFZe1jdDdgd dGdHZe1jdId gd dJdKZe1jdLdgd dMdNZe1jdOdgd dPdQZe1jdRdgd dSdTZe1jdRdgd dUdVZe1jdWdXdgd dYdZZe1jdIdgd d[d\Ze1jdIdgd d]d^Ze1jd_dXgd d`daZe1jdbd gd dcddZe1jdbdgd dedfZe1jdgdgd dhdiZe1jdgdgd djdkZe1jdld gd dmdnZe1jdldgd dodpZe1jdqd gd drdsZe1jdqdgd dtduZe1jdvdgd dwdxZe1jdvdgd dydzZe1jd{dgd d|d}Ze1d~ddZe1dddZe1dddZe1dddZe1dddZe1jdddide1dddZdS(zFGarden Tracker: plants, seeds, schedules, hydro tanks, planting guide.N)datedatetime timedelta)Decimal) BlueprintResponsejsonifyrequestsend_from_directory)secure_filename)ensure_alert_state_tablesend_sms_alert_detailed)GARDEN_CLIENT_DIST GARDEN_LAT GARDEN_LONGARDEN_LOCATION_LABELGARDEN_REMINDER_CHECK_MINUTES GARDEN_ZIPMYSQL_DATABASE MYSQL_HOSTMYSQL_PASSWORD MYSQL_PORT MYSQL_USERPARTSINV_IMAGE_SEARCH_API_KEYPARTSINV_IMAGE_SEARCH_CX WEATHER_LAT WEATHER_LON gardentracker)hostportuserpassworddatabase cursorclass>prunewatercustom fertilize test_tank harvest_check>diedactivearchived harvestedseed store_bought> r*noter&prunedseededwateredr- fertilized germinated seed_start true_leaves transplantedF gardentracker_seed_uploadscCstjditS)N)pymysqlconnect DB_CONFIGr<r<r<0/var/www/html/Server/blueprints/gardentracker.pyget_dbQsrAcCsRt|tr |St|tr|St|trt|St|tr'|jdddS|S)Nutf-8replaceerrors) isinstancer isoformatrrfloatbytesdecodevaluer<r<r@ _serializeUs    rMcCs|s|Sdd|DS)NcSsi|] \}}|t|qSr<)rM).0kvr<r<r@ dz"_serialize_row..)items)rowr<r<r@_serialize_rowasrUcCsdd|DS)NcSsg|]}t|qSr<)rUrNrr<r<r@ hz#_serialize_rows..r<)rowsr<r<r@_serialize_rowsgsr[cCsT|dus|dkr dSt|tr|Sztt|ddWSty)YdSwNZz+00:00)rFrr fromisoformatstrrC ValueErrorrKr<r<r@ _parse_dateks  rbcCsP|dus|dkr dSt|tr|Sz tt|ddWSty'YdSwr\)rFrr_r`rCrarKr<r<r@_parse_datetimevs  rcc Cs|}|d|ddD]}z||Wqtjy$Yqwz|dWn tjy6Ynw|ddD]}z||Wq>tjyQYq>w|d|d|d |d |d |d |d dD]}z||WqwtjyYqww|d|dt|pidpddkrttD]\}\}}}|d||||fq|d|s|dt t t fWdn1swY| t |t|dS)Na CREATE TABLE IF NOT EXISTS GardenSettings ( id INT PRIMARY KEY DEFAULT 1, zip_code VARCHAR(10) NOT NULL DEFAULT '39429', lat DECIMAL(10, 6) DEFAULT NULL, lng DECIMAL(10, 6) DEFAULT NULL, notifications_enabled TINYINT(1) NOT NULL DEFAULT 1, low_seed_threshold INT NOT NULL DEFAULT 5, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) a+ CREATE TABLE IF NOT EXISTS GardenPlant ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, variety VARCHAR(120) DEFAULT NULL, location VARCHAR(120) DEFAULT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', planted_date DATE DEFAULT NULL, moisture_device_id VARCHAR(16) DEFAULT NULL, seed_id INT DEFAULT NULL, source_type VARCHAR(20) NOT NULL DEFAULT 'store_bought', label_id VARCHAR(16) DEFAULT NULL, notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) )z;ALTER TABLE GardenPlant ADD COLUMN seed_id INT DEFAULT NULLzZALTER TABLE GardenPlant ADD COLUMN source_type VARCHAR(20) NOT NULL DEFAULT 'store_bought'zDALTER TABLE GardenPlant ADD COLUMN label_id VARCHAR(16) DEFAULT NULLzGCREATE UNIQUE INDEX idx_garden_plant_label_id ON GardenPlant (label_id)aS CREATE TABLE IF NOT EXISTS GardenPlantEvent ( id INT AUTO_INCREMENT PRIMARY KEY, plant_id INT NOT NULL, event_type VARCHAR(32) NOT NULL, event_date DATE NOT NULL, notes TEXT DEFAULT NULL, qty DECIMAL(10, 2) DEFAULT NULL, unit VARCHAR(32) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_plant (plant_id), FOREIGN KEY (plant_id) REFERENCES GardenPlant(id) ON DELETE CASCADE ) )zIALTER TABLE GardenPlantEvent ADD COLUMN days_since_start INT DEFAULT NULLzRALTER TABLE GardenPlantEvent ADD COLUMN days_since_anchor VARCHAR(16) DEFAULT NULLzJALTER TABLE GardenPlantEvent ADD COLUMN location VARCHAR(120) DEFAULT NULLzJALTER TABLE GardenPlantEvent ADD COLUMN height DECIMAL(10, 2) DEFAULT NULLaF CREATE TABLE IF NOT EXISTS GardenSchedule ( id INT AUTO_INCREMENT PRIMARY KEY, plant_id INT DEFAULT NULL, tank_id INT DEFAULT NULL, task_type VARCHAR(32) NOT NULL, title VARCHAR(120) NOT NULL, interval_days INT NOT NULL DEFAULT 14, next_due DATETIME NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_next_due (next_due), INDEX idx_plant (plant_id), FOREIGN KEY (plant_id) REFERENCES GardenPlant(id) ON DELETE CASCADE ) a CREATE TABLE IF NOT EXISTS GardenTaskCompletion ( id INT AUTO_INCREMENT PRIMARY KEY, schedule_id INT NOT NULL, completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, notes TEXT DEFAULT NULL, INDEX idx_schedule (schedule_id), FOREIGN KEY (schedule_id) REFERENCES GardenSchedule(id) ON DELETE CASCADE ) a! CREATE TABLE IF NOT EXISTS GardenSeedInventory ( id INT AUTO_INCREMENT PRIMARY KEY, crop_name VARCHAR(120) NOT NULL, variety VARCHAR(120) DEFAULT NULL, qty DECIMAL(10, 2) NOT NULL DEFAULT 0, unit VARCHAR(32) NOT NULL DEFAULT 'seeds', source VARCHAR(120) DEFAULT NULL, purchase_date DATE DEFAULT NULL, expiry_date DATE DEFAULT NULL, location VARCHAR(120) DEFAULT NULL, low_stock_threshold INT DEFAULT NULL, notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS GardenTank ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, volume_gal DECIMAL(10, 2) DEFAULT NULL, solution_type VARCHAR(120) DEFAULT NULL, target_ph_min DECIMAL(4, 2) DEFAULT NULL, target_ph_max DECIMAL(4, 2) DEFAULT NULL, target_ec_min DECIMAL(6, 2) DEFAULT NULL, target_ec_max DECIMAL(6, 2) DEFAULT NULL, test_interval_days INT NOT NULL DEFAULT 7, notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) af CREATE TABLE IF NOT EXISTS GardenTankReading ( id INT AUTO_INCREMENT PRIMARY KEY, tank_id INT NOT NULL, ph DECIMAL(4, 2) DEFAULT NULL, ec DECIMAL(6, 2) DEFAULT NULL, temp_f DECIMAL(5, 2) DEFAULT NULL, volume_gal DECIMAL(10, 2) DEFAULT NULL, notes TEXT DEFAULT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_tank (tank_id), FOREIGN KEY (tank_id) REFERENCES GardenTank(id) ON DELETE CASCADE ) a! CREATE TABLE IF NOT EXISTS GardenExternalCache ( cache_key VARCHAR(128) PRIMARY KEY, payload JSON NOT NULL, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL ) a CREATE TABLE IF NOT EXISTS GardenSeedTypeInfo ( cache_key VARCHAR(256) PRIMARY KEY, crop_name VARCHAR(120) NOT NULL, variety VARCHAR(120) DEFAULT NULL, summary TEXT DEFAULT NULL, wikipedia_title VARCHAR(256) DEFAULT NULL, wikipedia_url VARCHAR(512) DEFAULT NULL, info_source VARCHAR(32) DEFAULT NULL, source_url VARCHAR(512) DEFAULT NULL, source_name VARCHAR(120) DEFAULT NULL, images JSON DEFAULT NULL, planting JSON DEFAULT NULL, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) )zNALTER TABLE GardenSeedTypeInfo ADD COLUMN info_source VARCHAR(32) DEFAULT NULLzNALTER TABLE GardenSeedTypeInfo ADD COLUMN source_url VARCHAR(512) DEFAULT NULLzOALTER TABLE GardenSeedTypeInfo ADD COLUMN source_name VARCHAR(120) DEFAULT NULLz[ALTER TABLE GardenSeedTypeInfo ADD COLUMN images_user_managed TINYINT(1) NOT NULL DEFAULT 0a) CREATE TABLE IF NOT EXISTS GardenSeedCompany ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, base_url VARCHAR(512) NOT NULL, search_url_template VARCHAR(512) DEFAULT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) z-SELECT COUNT(*) AS cnt FROM GardenSeedCompanycntrz INSERT INTO GardenSeedCompany (name, base_url, search_url_template, enabled, sort_order) VALUES (%s, %s, %s, 1, %s) z*SELECT id FROM GardenSettings WHERE id = 1z| INSERT INTO GardenSettings (id, zip_code, lat, lng) VALUES (1, %s, %s, %s) )cursorexecuter=OperationalErrorintfetchoneget enumerateDEFAULT_SEED_COMPANIESrrrcommitr _backfill_plant_label_ids)conncurcol_sqlidxnamebase_urltemplater<r<r@ensure_garden_tabless       e rvcCs>|d|}t|dpdd}ddt|||DS)Nz SELECT MAX(CAST(SUBSTRING(label_id, 3) AS UNSIGNED)) AS n FROM GardenPlant WHERE label_id LIKE 'P-%' nrr:cSsg|]}d|dqS)zP-05dr<)rNir<r<r@rXuz-_allocate_plant_label_ids..)rfrirhrange)rpcountrTstartr<r<r@_allocate_plant_label_idsks r~cCs|7}|d|}|s WddSt|t|}t||D]\}}|d||dfq%Wdn1s>wY|dS)NzNSELECT id FROM GardenPlant WHERE label_id IS NULL OR label_id = '' ORDER BY idz2UPDATE GardenPlant SET label_id = %s WHERE id = %sid)rerffetchallr~lenziprm)rorprZlabelsrTlabelr<r<r@rnxs  rna SELECT p.*, s.crop_name AS seed_crop_name, s.variety AS seed_variety, s.location AS seed_bin, s.source AS seed_source, EXISTS ( SELECT 1 FROM GardenPlantEvent dead_event WHERE dead_event.plant_id = p.id AND dead_event.event_type = 'died' ) AS has_died_event FROM GardenPlant p LEFT JOIN GardenSeedInventory s ON s.id = p.seed_id cCsP|sdS|dp|dpd}|dp|dpdp!d}|p%d|fS)NNNseed_crop_namersr] seed_varietyvarietyrjstrip)rTcroprr<r<r@_plant_type_crop_varietys   rcCs.t|||}|p idpg}|r|dSdS)Nimagesr)_get_seed_type_inforj)ro crop_namer type_inforr<r<r@_get_primary_type_images rcCsd|s|St|}|ddkr|drt||d<|r0|dur0t|\}}|r0t||||d<|S)N source_typer.seed_id seed_label primary_image)rUrj_seed_display_labelrr)rTroinclude_primary_imageoutrrr<r<r@_serialize_plants   rcCsxg}|dr|d|d|dp|d}|r"|||dp+|d}|r3|||r:d|SdS)Nseed_binzbin #rrrru · )rjappendjoin)rTpartsrrr<r<r@rs   rcsfdd|DS)Ncsg|] }t|dqS)ror)rrVrr<r@rXrRz%_serialize_plants..r<)rZrorr<rr@_serialize_plantssrcCs4|durdSt|tr|St|tr|St|SN)rFrrrbrKr<r<r@_as_dates  rc Cst|}|sdSg}t|r|dnd}|r ||kr |||d||f|D]}t|d}|r<||q,|r\t|}||j}|pKiddkrSdnd } td || fSdS) z/Days from seed start or planting to event_date.r planted_dateNz SELECT event_date FROM GardenPlantEvent WHERE plant_id = %s AND event_type IN ('seed_start', 'seeded', 'transplanted') AND event_date <= %s ORDER BY event_date ASC, id ASC event_daterr.r6plantingr)rrjrrfrmindaysmax) rpplant_idplantranchorsplantedrTanchorr anchor_typer<r<r@_compute_days_since_starts,     rcCs|s|St|}|ddur(|ds&t||||d\}}|r&||d<|St||||d\}}|dur@||d<||d<|S)Ndays_since_startdays_since_anchorr)rUrjr)rprreventr_rrr<r<r@ _enrich_events rcsfdd|DS)Ncsg|] }t|qSr<)r)rNevrprrr<r@rXrRz"_enrich_events..r<)rprreventsr<rr@_enrich_eventsrcCs |durdSt|}|pdSr)r`r)rLlocr<r<r@_normalize_locations rc Cs8|dus|dkr dSzt|WSttfyYdSwNr])rH TypeErrorrarKr<r<r@_optional_floats rcCs$|dkrdS|dt||fdS)Nr8z2UPDATE GardenPlant SET location = %s WHERE id = %s)rfr)rpr event_typelocationr<r<r@_sync_transplant_locations  rcCs>|}|d|WdS1swYdS)Nz)SELECT * FROM GardenSettings WHERE id = 1)rerfrirorpr<r<r@ _get_settingss  $rc st}|d|dtd|d}|ddd|D}|D]}}|d|d | }|rF| d nd}|sKq0|d |d |d |d||d|d|d |}|suq0t |d }d dd|D} |D]d fdd|D} |d|d| d| dq|d q0Wdn1swY|d|S)Nz-- Garden Tracker backup z-- Generated at z zSET FOREIGN_KEY_CHECKS = 0; z SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'Garden%' ORDER BY TABLE_NAME cSsg|]}|dqS) TABLE_NAMEr<)rNrTr<r<r@rX1rYz+_sql_dump_garden_tables..zSHOW CREATE TABLE ``z Create Tablez -- Table:  zDROP TABLE IF EXISTS `z`; z; zSELECT * FROM `rz, cSsg|]}d|dqS)rr<rNcr<r<r@rXBrzcsg|] }|qSr<)literalrjrrorTr<r@rXDz INSERT INTO `z` (z ) VALUES (z); zSET FOREIGN_KEY_CHECKS = 1; )ioStringIOwriternowrGrerfrrirjlistkeysrgetvalue) rorrp table_names table_name create_row create_sqlrZcolumnsrqvalsr<rr@_sql_dump_garden_tables s@       "rcCsZtjj|ddid}tjj||d}t|WdS1s&wYdS)N User-AgentzGardenTracker/1.0headerstimeout)urllibr RequesturlopenjsonloadsreadrJurlrreqrespr<r<r@_fetch_json_urlLs$rcCs|Q}|d|f|}|s WddS|drE|dtkrM|d}t|trnon gmonon-gmoopen pollinatedopen-pollinatedf1f2opgmomixrawblendmixedcommonhybridgenericorganicregulartreatedunknownrheirloomimprovedpelletedstandard untreated traditional)erucarocketroquette)maizezzea mayszea) auberginezsolanum melongena) coriander coriandrum)z green onionzallium fistulosum) courgette cucurbita)arugulacorneggplantcilantroscallionzucchinicCsD|pd}|s dS|drd|S|dr d|ddS|S)Nr]z//zhttps:http://https://)r startswith)rur<r<r@_normalize_image_urls   r0cCst|}|sdStj|j}d|vr1|ddd}|d}t|dkr1d|ddSdD]}||vrE||ddSq3|S) zKCanonical identity for deduping the same file at different thumbnail sizes.r]z/thumb/r:/N)z/wikipedia/commons/z/wikipedia/en/z /commons/) r0rparseurlparsepathsplitrrlower)rr6restrmarkerr<r<r@_wikimedia_image_identitys  r;cCsr|rt||kr dSt|d}|sdSt|p|}||vr!dS|||||d|dp3dddS)Nrtitlesource wikipediarr=r>)rr0rjr;addr)rseen_identitiesimglimitridentr<r<r@_append_unique_images    rFcCs*tjtjtjt}tj|tSr)osr6dirnameabspath__file__rSEED_IMAGE_UPLOAD_DIR)baser<r<r@_seed_upload_rootsrMcCs"tdd|pddd}|pdS)Nz [^\w\-|]+rrP)resub) cache_keysafer<r<r@_seed_upload_subdirsrScCs|sdSdtjj|ddS)Nz$/gardentracker/api/seed-type-upload/r1rR)rr4quote file_pathr<r<r@_seed_upload_display_urlsrXcCs2|rt|St|}|sdSdtjj|ddS)Nz"/gardentracker/api/seed-image?url=r]rT)rXr0rr4rU)rrW normalizedr<r<r@_seed_image_display_urls rZcCs|durgSt|ttfr|jddd}t|tr-zt|}Wn tjy,gYSwt|ts4gSg}|D] }t|trD|dd}t|t rX| dsS| drX| |q8|S)NrBrCrDr?rr>rrW) rFrI bytearrayrJr`rrJSONDecodeErrorrdictrjr)rrrCr<r<r@_parse_images_raws(     r_cCsHt|tsdS|drd|dSt|d}|sdSt|p#|S)NrWzupload:r)rFr^rjr0r;)rCrr<r<r@_image_list_identitys   r`cCs0tjdddpi}tjdp|dpdS)NTforcesilentrr])r get_jsonargsrjr)datar<r<r@_requested_image_urlsrgc Cs^tjdddpi}tjd|d}|dus|dkrdSzt|WSttfy.YdSw)NTraindexr])r rdrerjrhrra)rfr r<r<r@_requested_image_indexs ricCsxt|}|r t|ts dSt|d}|r||krdS|dp"d}|r2|d||fvr2dSt|td|ikS)NFrTrWr]seed-upload://)r0rFr^rjrr`) stored_img requested_urlr stored_normrWr<r<r@_image_matches_requests rncCs\t|sdS|}|d|f|}Wdn1s wYt|o,|dS)NFzGSELECT images_user_managed FROM GardenSeedTypeInfo WHERE cache_key = %simages_user_managed)_has_images_user_managed_columnrerfriboolrjrorQrprTr<r<r@_type_info_images_user_manageds  rscCsF|durgSt|ttfr|jddd}t|tr-zt|}Wn tjy,gYSwt|ts4gSg}t }|D]e}t|trG|dd}t|t sMq;| dpSd }t | d}|sb|sbq;t|}|rl||vrmq;|||pxd || d | d pdt||pdd d }|r||d<||t|tkr|Sq;|S)NrBrCrDr?r[rWr]rrjr=r>rV)rr=r> display_url)rFrIr\rJr`rrr]rsetr^rjrr0r`rArZrrMAX_SEED_TYPE_IMAGES)rrseenrCrWrrEentryr<r<r@_normalize_seed_imagessP           rycCs.|pd}|p d}|d|S)Nr]|)rr8)rrrvarr<r<r@_seed_type_cache_keyFsr|c CsR|d|d|d|d|d|d|dpg|d|d d S) Nslug commonNamescientificNamecategoryseason daysToHarvestwindowsnotes zoneRange) r} common_namescientific_namerrdays_to_harvestrr zone_rangerj) crop_datar<r<r@_map_cropgraph_guideLs rc Cs|pd}|s dS|dd}d|}t||}|r |Sz td|}WnQtjjtjjt j t fyzz*tdtj |}|dpKg}|sRWYdS|dd pZ|}td|}Wntjjtjjt j t fywYYdSwYnw|d p|} t| } t||| d | S) Nr] -z crop_detail:z#https://api.cropgraph.com/api/crop/'https://api.cropgraph.com/api/search?q=resultsrr}r)rr8rCrrrerrorURLError HTTPErrorrr]OSErrorr4rUrjrr) rorrr}rQcachedrfsearchrrguider<r<r@_fetch_cropgraph_plantingZs:    rc Cs`tjj|pddddd}|sdSz ttd|WStjjtjjt j t fy/YdSw)Nr]rrrTz/api/rest_v1/page/summary/) rr4rUrCr WIKIPEDIA_APIrrrrr]r)r=encodedr<r<r@_wiki_page_summarywsrc Csx|pd}|s gSzttdtj|d|}dd|dp$gDWStjjtjj t j t fy;gYSw)Nr]z/w/rest.php/v1/search/title?q=z&limit=cS g|] }|dr|dqSr=rrNpr<r<r@rX z'_wiki_search_titles..pages) rrrrr4rUrjrrrrr]r)queryrDqrfr<r<r@_wiki_search_titless rcCsV|sdS|dp i}|dp|dpid}t|}|s"dS||dddS)N thumbnailr> originalimager=r?r@)rjr0)summarythumbrr<r<r@_image_from_wiki_summarysrc Cs|pd}|s gStjd|ddddd}z ttd|}Wntjjtjjt j t fy6gYSwg}| dp?i d i D]"}| d rNqFt| d pUi d }|rh||| d ddqF|d|S)Nr]r pageimagesrzthumbnail|name)actiontitlespropformat pithumbsizepiprop /w/api.php?rmissingrr>r=r?r@)rrr4 urlencoderrrrrrr]rrjvaluesr0r)r=rDparamsrfrpagerr<r<r@_wiki_page_imagess<    rcCs,|pd}|rt|dkrdS|tvS)Nr]r<T)rr8rGENERIC_VARIETY_TERMS)rrPr<r<r@_is_generic_varietysrcCsl|pd}t}|r4|||D] }t|dkr#||qt|dD] }||q*|S)Nr]r2r<)rr8rurAr7rCROP_WIKI_ALIASESrj)rrtermswordaliasr<r<r@_crop_match_termss    rcCs\|pd}|s dSt|D] }||vrdSq|pd}|r,t|s,||vr,dSdS)Nr]FT)r8rrr)r=rrtitle_ltermr<r<r@_wiki_title_matches_crops  rcCs|pd}|s gSt}g}t||dD]}|r+||vr+||||qt|dkrOt||dD]}|rN||vrN||||q8|S)Nr]rDr2)rrurr8rArr_wiki_fulltext_titles)rrDrwrr=r<r<r@_wiki_crop_title_candidatess"    rcsHfdd|p gD}|r|SddtDfdd|p!gDS)Ncsg|] }t|r|qSr<)rrNtrrr<r@rXrz+_filter_titles_for_crop..cSsh|]}|qSr<r8rr<r<r@ rYz*_filter_titles_for_crop..cs g|] }|pdvr|qS)r]rr) allowlistr<r@rXr)r)rrrfilteredr<)rrrr@_filter_titles_for_crops rcCs|pd}t|r dS|Srrrrr<r<r@_variety_for_wiki_searchs rcCs|pd}t|}g}|r"||d|||d||r)||t}g}|D]}|}|rF||vrF||||q0|SNr]r)rrrrur8rA)rrqueriesrwrrrr<r<r@_wiki_search_queriess"     rcCs\|}t||D]}t||d}t|||}|r||fSqt||d}|r*||fSgdfS)Nr)rrrr)rrrDvariety_for_matchrrrelevant crop_titlesr<r<r@_wiki_resolve_titless    rc Cs|pd}|s gStjdd||dd}z ttd|}Wntjjtjjt j t fy5gYSwdd| dp?i dgDS) Nr]rrr)rrsrsearchsrlimitrrcSrrr)rNhitr<r<r@rX0rz)_wiki_fulltext_titles..) rrr4rrrrrrrr]rrj)rrDrrrfr<r<r@rs"   rcsP|pdds dStfdddDsdSd}tfdd|D S) Nr]zfile:Fc3|]}|vVqdSrr<)rNextrsr<r@ 7z&_wiki_file_is_photo..).jpg.jpeg.png.webp) z commons-logoz edit-clearsymbolzicon-flagmapr pictogramambox wikimedia question_bookcrystalc3rrr<rNtokenrr<r@r=r)r8r.any) file_title skip_tokensr<rr@_wiki_file_is_photo3s  rc Cs|pd}|r |dkrgSt|pg}tjd|dddd}z ttd|}Wntjjtjj t j t fy?gYSwg}| dpHi d iD]}| dpWgD]}| d }t|rh||qXqO|sngSg} d } tdtt|d | D]} || | | } tjdd | ddddd} z ttd| }Wntjjtjj t j t fyYq}w| dpi d iD]D}| dpigd}t| dp| d}|sqt|p|}||vrq||| || d ddt| |kr| Sqq}| S)Nr]rrrr)rrrimlimitrrrr=rrrz imageinforr)rrriiprop iiurlwidthrthumburlr?r@)rrurr4rrrrrrrr]rrjrrrr{rrrr0r;rA)r=skip_identitiesrDrrf file_titlesritemrr batch_sizer}batch info_params info_datainforrEr<r<r@_wiki_extra_page_images@s          rc Csg}t}t|||D]R}t||krnIt|dd}|r't|||d|n t|}t||t||t||kr<n"|dkr]t|||t|dD]} t||| |t||kr\nqKq |d|S)Nr:rr)rrD)rurrrrFrrr) rrrrDrrBr= page_imgsprimaryextrar<r<r@_collect_wiki_imagess0       rcCsV|}d}|r |d7}|d7}|||WdS1s$wYdS)NzSELECT * FROM GardenSeedCompanyz WHERE enabled = 1z ORDER BY sort_order, name)rerfr)ro enabled_onlyrpsqlr<r<r@_get_seed_companiess  $r cCstj|jpdSr)rr4r5hostnamer8)rtr<r<r@ _company_hostrr 2cCsptj|}|jp d}|r|jdvrdSt|ddD]}t|d}|r5||ks2| d|r5dSqdS)Nr])httphttpsFTrrt.) rr4r5r r8schemer r rjendswith)rorparsedrcompany company_hostr<r<r@_is_allowed_product_urls rcCs>t|}t|ddD]}t|d|kr|dSq dS)NTrrtrs)r r rj)rorrrr<r<r@_source_name_for_urls rcCsBttttB}t|ddD]}t|d}|r||q|S)NTrrt)ruWIKIMEDIA_IMAGE_HOSTSSEED_COMPANY_CDN_HOSTSr r rjrA)rohostsrrr<r<r@_allowed_image_hostss rcCsJ|pd}|s dS||vrdS|D]}||ks|d|r"dSqdS)Nr]FTr)r8r)r allowed_hostsallowedr<r<r@_image_host_alloweds rcCs.|pd}t|}|r|d|S|Sr)rrrr<r<r@_build_seed_search_querys r cCstdd|pdd}|s|S|dr|S|dr4t|dkr4|ddvr4|dd d S|d r=|d S|dS) N [^a-z0-9]+rr]syr:aeiour3ies)chshxzes)rOrPrr8rr)rr}r<r<r@_crop_plural_slugs  " r,c Cs|pd}g}t|}|r|dddd}|r5||kr5||d|||td|tjrtdd|}||d||dddd}|rr||krr||d|tjdd|tjd }|r||kr||d||||dddd}|r||kr||d|||||d||||r||t } g} |D]} | } | r| | vr| | | | q| S) Nr]'u’r\bog\bz\bOG\bOrganicz \s+OG\s*$)flags) r_effective_varietyrCr8rrOrIrPrurA) rrreffapostrophe_free organic_var organic_freestripped stripped_freerwrrrr<r<r@_seed_company_search_queriessF         r9cs^pdtfdddDrdSdvrttdSdvs+d vs+d vr-d SdS) Nr]c3rrr<rNskipr6r<r@r  z+_is_company_product_path..)z/gift-certificatez '+produrl+'produrlz /viewcartz/create_reviewFz /product/z/product/[^/]+(?:/\d+)?/?$z.htmlz/herbs/ /vegetables/T)r8rrqrOrr<r<r<r@_is_company_product_paths  r@cs^|pds dStfddtDrdSdvsdvr!dSdvr-td r-dSdS) Nr]Fc3rrr<r:r/r<r@r"rz-_is_seed_company_image_url..z/images/popup/z/images/products/Trz//images/[^/]*\d{3,}[^/]*\.(?:jpg|jpeg|png|webp))r8rSEED_COMPANY_IMAGE_SKIP_TOKENSrOrrr<rAr@_is_seed_company_image_urls  rDc Cst|d}|dkr gS|dpdd}g}|D]^}zLtjdd|d}tjj|d|d d d |dd d d}tjj |dd}| j ddd} Wdn1s[wY| t | |WqtjjtjjtfyyYqwtt|S)Nrtwww.totallytomato.comr]r1Searchprodcat)r search_typekeywordz /index.php.addr]rt)ru_fetch_html_url_company_search_urlrrrrrrU_company_browse_linksrZr rj_google_site_search_links)rrrrWrA search_queryrYdomainr<r]r@_collect_company_product_linksQs"rdc Cst|d}|dp dd}|pd}|r|r|sgSg}|dkr5t|}|r4|d|dn|dkrHt|}|rH|d|dg}|D]#}zt||}Wntjj tjj t fyfYqLw| t ||qLtt|S) Nrtr]r1zwww.johnnyseeds.comr?rEz/vegetables/vegetable-seeds/z-seeds)r rjrRrr,rr^rrrrrrTrUrr^rV) rrrrtpathspluralrXr6 browse_htmlr<r<r@r`ls0  r`cCst|}|g}td|r|tdd||dd}|r$||td|}|r;||d|dtt |S)Nr.rrr]^([a-z])\s+(\d+)\br:r<) _normalize_match_textrOrrrPrCmatchgrouprr^rV)rnorm_varformscompact code_matchr<r<r@_variety_match_formss    rpcCs |pd}|r t|rdS|Srrrr<r<r@r1s  r1cCs<|pd}|dddd}tdd|}d|S)Nr]rrrr!)r8rCrOrPrr7)textr<r<r@ris ric s\t|}|sdStd|}|sdS|dd}t|D]}||vr&dS|dd}t|dkr9||vr9dSqt|}g}|D]}t|dkrR||qD|dvr[||qDt d |} | rs|| d | d |s|dd|vrdSdS|d |vrd 7t fdd|D} | t|} | dkrdS| dkrdS| rdSdS)Ndrrr]br2)ogrrrrhr:r<ruz organicc3|] }|vrdVqdSr:Nr<rexpandedr<r@rz'_variety_match_score..g?_g?A#) r1rirrCrprr7rrOrjrksum) rtextscombined compact_textform compact_formrl var_tokensrromatchedratior<rxr@_variety_match_scoresN         rcs|pd}|s dSt|}td|sdS|vrdSdd|D}|s6|ddvr4dSdStfdd|D}td|t|S) Nr]rrrrcSsg|] }t|dkr|qS)r2)rrr<r<r@rXrz%_crop_match_score..c3rvrwr<rrr<r@rrzz$_crop_match_score..)rrirr7rCr~rhr)rr norm_crop crop_tokensrr<rr@_crop_match_scores rc Cs|pd|pd|p dg}t|g|R}|dkrdSt|g|R}t|r+|dkr+dSt|r8|d|d}nt|}dt|vrF|d7}|S) Nr](rr g?g?r.r)rrr1rHri) rrrr=rr crop_score variety_scorescorer<r<r@_score_seed_candidates rcCs>|dpdd}|dpd}tj|}|j|||dS)Nrtr]r1search_url_templater)rtr encoded_query)rjrRrr4rUr)rrrtrurr<r<r@r_s   r_cCs^tjj|ddddd}tjj||d}|jddd WdS1s(wYdS) NrJrKzen-US,en;q=0.9)rrMzAccept-LanguagerrrBrCrD)rr rrrrJrr<r<r@r^s $r^cCs\|r d|d}nd|d}tt|d|tjtjB}|s$dSt|dS)Nz property=""zname="z\s+content=(['\"])(.*?)\1r<) rOrescaper2Shtmlunescaperkr) page_htmlrsrattrrjr<r<r@_html_meta_contents  rcCs t|||Sr)r)rrrr<r<r@_score_seed_link rcs|dpdd}t|}g}td|pdD]P}|dd}|dr)||}|ds/qtj |}|r@|j r@||j vr@q|j pDd t fdd d DrSqtrh||jd |j|j dqtt|S) Nrtr]r1zhref="([^"]+)"z&&rc3rrr<r:r<r<r@r&r=z1_extract_company_product_links..) z/searchz /customer-z/growers-libraryz/shop-by-colorz/cartz/accountz/loginz /shippingz/privacyz://)rjrRr rOfindallrCr.rr4r5r r6r8rr@rrnetlocrr^rV)rrrt base_hostrXhrefrr<r<r@rUs*     "rUc CstrtsgStjttd|d|t|dd}z td|dd}Wntjjtjj t j t fy9gYSwg}| dpBgD]}| d }|rV||d d qCtt|d|S) Nzsite:r )rcxrnumz+https://www.googleapis.com/customsearch/v1?r9rrSr\?r)rrrr4rrrrrrrr]rrjrr7rr^rV)rcrrDrrfrXrr\r<r<r@ra3s0  rac szt|WntjjtjjtfyYdSwfdd}fdd}|dp+|dsKtdtj}|rKt | d t d d  sntd tjtjB}|rnt t d d | dd |dpu|d}|stdtjtjB}|rt t d d | d}d |}gtfdd}tdtjD]}||qtdtjD]}||qt|d} | r|| tdtjD]o} z t| d} Wn tjyYqwt| tr| n| g} | D]K} t| tr | ddkr q|s| d}s| d| d}t|tr0|r.|dnd}t|tr;|d}t|}|rF||qq|sPrP}|sXsXdSrgt d |rvt |}d |}|dd|dS)Nc t|dS)Nrrrrr<r@ meta_propertySrz1_extract_product_page_info..meta_propertycr)Nrrrrr<r@ meta_nameVrz-_extract_product_page_info..meta_namezog:titler=z([^<]+)r:z\s*\|.*$r]z]*>(.*?)z<[^>]+>rzog:description descriptionzC]*>\s*((?:A [^<]{40,600}|(?:A blend of|This)[^<]{20,600}))

csPt|}|r t|s dSt|p|}|vrdS||dddS)N seed_companyr@)r0rDr;rAr)rrYrE)rrwr=r<r@ add_imageqs    z-_extract_product_page_info..add_imagez>https://[^"\']+/images/products/[^"\']+\.(?:jpg|jpeg|png|webp)z;https://cdn\.commercev3\.net/[^"\']+\.(?:jpg|jpeg|png|webp)zog:imagez2z@typeProductrsimagerrr<)r=rr source_url)r^rrrrrrOrr2rrrkrrPrrr7rurr0finditerrrr]rFrr^rj)page_urlrr title_matchh1_matchr lead_matchrrog_imagerjrrSrrr<)rrrwr=r@_extract_product_page_infoMs                  rcCsxt|pg}dd|D}|d|pgD]}t|}|r"||vr#q||||t||kr5nq|d|S)NcSsh|]}t|qSr<)r`rNrCr<r<r@rrYz%_merge_seed_images..)rdiscardr`rArr)existing new_imagesrDmergedrwrCrEr<r<r@_merge_seed_imagess        rc Csz|+}|d|f|r WddS|d|||p!dtgfWdn1s2wY|dS)Nz=SELECT cache_key FROM GardenSeedTypeInfo WHERE cache_key = %sz INSERT INTO GardenSeedTypeInfo (cache_key, crop_name, variety, images) VALUES (%s, %s, %s, %s) )rerfrirrrm)rorQrrrpr<r<r@_ensure_seed_type_info_rows  rcCsT|}|dt|pidpddkWdS1s#wYdS)Nz SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'GardenSeedTypeInfo' AND COLUMN_NAME = 'images_user_managed' rdr)rerfrhrirjrr<r<r@rps  $rpcCsV|}|d|f|}Wdn1swYt|r(|dSdS)Nz:SELECT images FROM GardenSeedTypeInfo WHERE cache_key = %sr)rerfrir_rjrrr<r<r@_load_type_info_imagess  rcCst|||||(}t|dt}|r$t|r$|d||fn|d||fWdn1s6wY|dS)Nz UPDATE GardenSeedTypeInfo SET images = %s, images_user_managed = 1 WHERE cache_key = %s z>UPDATE GardenSeedTypeInfo SET images = %s WHERE cache_key = %s)rrerrrvrprfrm)rorQrrr user_managedrprr<r<r@_save_type_info_imagess    rcCsp|r d|vs |dr dSt}tj||}tj|r6zt|WdSty5t d|YdSwdS)N..r1zCould not delete seed upload %s) r.rMrGr6risfileremoverloggerwarning)rW upload_rootfullr<r<r@_delete_seed_upload_files  rc Cszt|dd}|sdgddddSt||}|sdgddddSd}d}t||}|D]}t||||} g} t| D]} t| ||} | dkrGq:| | | fq:| jdd| ddD]c\} } t | } | sfq[t| ||| d| d d }|dkrzq[|| d rd ndt | d pg}||kr|}| d | d pgd | d| dd}|r| d rt | d pgdkrnq[|r| d rt | d pgdkrnq*|rg}dd| d pgD}|D]}t | d pgdkrnz t t ||}WntjjtjjtfyYqwt||} t| D]} | | dkr'qt| ||} | dkr4qt | } | s=qt| ||| d| d d }|dkrSq| d pZgD]5}t| dpi| d}|r||vr||||t | d pgt |dkrnq[t | d pgt |dkrnqqt| d |dd|d <|SdgddddS)NTr)rr info_sourcer source_namer3r)reverser=rr=rrsrrrrsr<cSs$h|]}t|dp|dqSrC)r;rjrr<r<r@rRsz+_fetch_seed_company_info..rr)r r r9rdr^rVrrsortrrjrr^r_rrrrrrUr;rAr)rorr companiesrbest best_scorerWrrX candidatesr\ url_scorer full_score total_score extra_imagesrwrYrCrEr<r<r@_fetch_seed_company_infos     ( $$       ""rc Cs|pd}|p d}|s|sdddgdSt||\}}|s(|r(t|}|}g}d}d}d}|rZt|d}|rR|d}|d}|dpJidid}t|||td }||||dtdS) Nr])rwikipedia_title wikipedia_urlrrextractr= content_urlsdesktoprr)rrrrrjrWIKI_IMAGE_LIMIT) rrr matched_queryr summary_text wiki_titlewiki_urlrr<r<r@_fetch_wikipedia_seed_infos8      rc Cs|sdSt|d}|d}t|ttfr|jddd}t|tr(t|}|d|d|d|d |d |d ||t |d d S)NrrrBrCrDrrrrrr fetched_at) rrrrrrrrr) ryrjrFrIr\rJr`rrrM)rTrrr<r<r@_serialize_seed_type_infos$    rcCsVt||}|}|d|f|}Wdt|S1s"wYt|S)N5SELECT * FROM GardenSeedTypeInfo WHERE cache_key = %s)r|rerfrir)rorrrQrprTr<r<r@rs    rcCsL|sdS|dp drdS|drdS|drdS|dr$dSdS)NFrr]Trrrr)rr<r<r@_seed_type_info_has_contents   rc Cs.t||}t|||||}|d|f|pi}Wdn1s(wYt|d}t||r<|}n t||dpDg}|d} |dpT|d} t || } |'}|d|||pgd| |d|dd | | t ||d f Wdn1swY| t |||S) Nrrrrr=a INSERT INTO GardenSeedTypeInfo (cache_key, crop_name, variety, summary, wikipedia_title, wikipedia_url, info_source, source_url, source_name, images, planting) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE crop_name = VALUES(crop_name), variety = VALUES(variety), summary = VALUES(summary), info_source = VALUES(info_source), source_url = VALUES(source_url), source_name = VALUES(source_name), images = VALUES(images), fetched_at = CURRENT_TIMESTAMP rrrr)r|rrerfriryrjrsrrrrrmr) rorr page_inforQrprexisting_imagesrrrrr<r<r@_apply_product_page_infos@       rcCst||}|durt|}nt||}t||}t|||}|d}t|dp*g} |d} |d} |d} d} d}| }t| dk}|sL|rt||}|rk|drk|d}|d} |d}| skd } |r}t| |dpugdt t d } | s|ds|drd } |d} |d}t ||rt||} nt|| t d } | %}| d |||pd|| || | | t| |rt|ndf Wdn1swY|dS) Nrrrrrr<rrr?raQ INSERT INTO GardenSeedTypeInfo (cache_key, crop_name, variety, summary, wikipedia_title, wikipedia_url, info_source, source_url, source_name, images, planting) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE crop_name = VALUES(crop_name), variety = VALUES(variety), summary = VALUES(summary), wikipedia_title = VALUES(wikipedia_title), wikipedia_url = VALUES(wikipedia_url), info_source = VALUES(info_source), source_url = VALUES(source_url), source_name = VALUES(source_name), images = VALUES(images), planting = VALUES(planting), fetched_at = CURRENT_TIMESTAMP )r|rrrrrjrrrrrvrsrerfrrrm)rorrpreserved_imagesrQrrrrrrrrrr need_summary need_imageswikirpr<r<r@_fetch_and_store_seed_type_info sl                    "rcCs0t |tvWdS1swYdSr)_seed_type_fetch_lock_seed_type_fetch_inflight)rQr<r<r@_is_seed_type_fetch_inflightZs$rcstt"|dur|t<tvr WddStWdn1s,wYfdd}tj|ddddddS)Ncsd}z`z5ttd}Wdn1swYt}zt|t||dW|n|wWntyGt dYnwWtt WddS1s\wYdSt t Wdw1suwYw)Nrz"Seed type enrichment failed for %s) r_seed_type_preserved_imagespoprArvrclose Exceptionr exceptionrr) preservedrorQrrr<r@workeris0  ( z&_queue_seed_type_fetch..workerTz seed-type- targetdaemonrs)r|rrrrA threadingThreadr})rrrrr<rr@_queue_seed_type_fetch_s   rcCsPt|pi}t|dp d}t|}|dp|}t|dt|k|d<|S)Nlow_seed_thresholdrlow_stock_thresholdqty low_stock)rrhrjrUrH)rorTsettings thresholdrrDr<r<r@_serialize_seed_with_stocks r cCsHt|pi}|dp t}|dpt}|dpt}t|t||fS)Nlatlngzip_code)rrjrrrrH)rorr r r r<r<r@ _get_coordss rcCs|s|St|}|d}t|dkr/tdd|Dr/t|dddt|ddSt|d krOtd d|DrOt|dddt|ddS|S) z+Format MM-DD or date-like strings as MM/DD.rr<cs|]}|VqdSrisdigitrr<r<r@rrz_format_mmdd..r02dr1r:r2csrrrrr<r<r@rr)r`rr7rallrh)rLr"rr<r<r@ _format_mmdds  ""rc Cs:t|d}|r |St|pi}|dpt}t|\}}}z td|d}Wntjjtjj t j t fy;i}Ynwz td|d|}Wntjjtjj t j t fy\i}Ynw|dpci} |dpji} || dpu|d|d t | d t | d |d |d ||dd} t|d| d| S)Nz zone_info:v2r zhttps://phzmapi.org/z.jsonz'https://api.cropgraph.com/api/zone?lat=&lng=zone frostDatestemperature_range lastSpring firstFall seasonDays climateType)r r )rrrlast_spring_frostfirst_fall_frost season_days climate_type coordinatesr)rrrjrrrrrrrrr]rrr) rorrr r r rphzm cropgraphrfrostresultr<r<r@_get_zone_infos>     r&c Cst}t}d|d|d}z0t|}|dpi}|dpi}|d|d|dp/dgd |d p8dgd d WStjjtjjtj t fyPYdSw) Nz0https://api.open-meteo.com/v1/forecast?latitude=z &longitude=z¤t=temperature_2m,relative_humidity_2m&daily=temperature_2m_max,temperature_2m_min&temperature_unit=fahrenheit&timezone=auto&forecast_days=2currentdailytemperature_2mrelative_humidity_2mtemperature_2m_maxrtemperature_2m_min)temp_fhumidity today_max today_min) rrrrjrrrrrr]r)r r rrfr'r(r<r<r@ _get_weathers(r1cCs$t|dpd}tt|dS)N interval_daysr)rhrjrrr)scheduleintervalr<r<r@_advance_schedule_next_duesr7cCsB|dp |dd}|r|d|dS|r|d|dS|S)Nr= task_typeTask ()r)r5 plant_name tank_namer=r<r<r@_schedule_labels r>c Csft}zzt|t|pi}|ddsWW|dSt|dp&d}t}tt tj }| F}|d|f|}|D]S}t|d}|d|f|} | rc| dnd} | rl| |krlqJ|d |d|f|r{qJt||d |d } d | d } t| \} }| r|d||fqJ|d|f|D]T}d|d}|d|f|} | r| dnd} | r| |krqd|d|drd|ddndd|dd|dd} t| \} }| r|d||fq|d|}|D]q}|d|df|}t|d p d!}d"}|s*d#}n |d$t|d%}||k}|s<q t|d}|d&|f|} | rT| dnd} | r`| |kr`q d'|d(d)} t| \} }| ry|d*||fq Wdn 1swY|Wntytd+YnwW|dSW|dS|w),Nnotifications_enabledr:rra. SELECT s.*, p.name AS plant_name, t.name AS tank_name FROM GardenSchedule s LEFT JOIN GardenPlant p ON p.id = s.plant_id LEFT JOIN GardenTank t ON t.id = s.tank_id WHERE s.enabled = 1 AND s.next_due <= %s rz SELECT last_notified_at FROM alert_state WHERE source_type = 'garden_schedule' AND source_id = %s AND metric = 'reminder' last_notified_atz SELECT id FROM GardenTaskCompletion WHERE schedule_id = %s AND completed_at >= %s LIMIT 1 r<r=zGarden Tracker: z is dueaF INSERT INTO alert_state (source_type, source_id, metric, last_state, last_notified_at) VALUES ('garden_schedule', %s, 'reminder', 'due', %s) ON DUPLICATE KEY UPDATE last_state = 'due', last_notified_at = VALUES(last_notified_at) z SELECT s.* FROM GardenSeedInventory s WHERE s.qty <= COALESCE(s.low_stock_threshold, %s) seed_z SELECT last_notified_at FROM alert_state WHERE source_type = 'garden_seed' AND source_id = %s AND metric = 'low_stock' u#Garden Tracker: Low seed stock — rrr:r;r]z: rrunitz remainingaC INSERT INTO alert_state (source_type, source_id, metric, last_state, last_notified_at) VALUES ('garden_seed', %s, 'low_stock', 'low', %s) ON DUPLICATE KEY UPDATE last_state = 'low', last_notified_at = VALUES(last_notified_at) zSELECT * FROM GardenTank SELECT recorded_at FROM GardenTankReading WHERE tank_id = %s ORDER BY recorded_at DESC LIMIT 1 test_interval_daysr-FT recorded_atr4z SELECT last_notified_at FROM alert_state WHERE source_type = 'garden_tank' AND source_id = %s AND metric = 'test_overdue' zGarden Tracker: Tank 'rsz' needs testing (pH/EC)aF INSERT INTO alert_state (source_type, source_id, metric, last_state, last_notified_at) VALUES ('garden_tank', %s, 'test_overdue', 'due', %s) ON DUPLICATE KEY UPDATE last_state = 'due', last_notified_at = VALUES(last_notified_at) zGarden reminder check failed)rArvrrjrrhrrcombinertodayrtimererfrr`rir>r rrmrrr)ror low_thresholdr today_startrp due_schedulesschedsid alert_row last_notifiedrmsgokerrr.rtankstank last_readingr6overduedue_bytidr<r<r@_check_reminderss                    rYcCs,tdtd}td tt|q )N<r)rrrHsleeprY) interval_secsr<r<r@_reminder_loop~ s   r]cCs\t"tr WddStjtddd}|daWddS1s'wYdS)NTzgarden-remindersr)_reminder_lock_reminder_thread_startedrrr]r})rr<r<r@start_reminder_thread s"r`cCs tdSr)r`r<r<r<r@_ensure_reminder_thread s raz /api/healthc Cszt}Wntjy#}ztdt|ddfWYd}~Sd}~wwz5zt|tdddWW|StjyY}ztdt|ddfWYd}~W|Sd}~ww|w)NFrQrrTr)rQservice)rAr=Errorrr`rvr)roexcr<r<r@health s  " "  rfz /api/settingsGET)methodscCs:t}zt|tdtt|dW|S|w)NTrQr)rArvrrUrrror<r<r@ get_settings rkPUTc Cstjdddpi}t}zct||"}|d|d|d|d|d|dfWdn1s:wY|| }|d Wdn1sWwY|tdt t |d W| S| w) NTraa UPDATE GardenSettings SET zip_code = COALESCE(%s, zip_code), lat = COALESCE(%s, lat), lng = COALESCE(%s, lng), notifications_enabled = COALESCE(%s, notifications_enabled), low_seed_threshold = COALESCE(%s, low_seed_threshold) WHERE id = 1 r r r r?rzPDELETE FROM GardenExternalCache WHERE cache_key IN ('zone_info', 'zone_info:v2')ri) r rdrArvrerfrjrmrrUrr)rfrorpr<r<r@update_settings s0   rnz/api/settings/backup/fullc Cst}zIz&t|t|}dtdd}t|ddd|didWW|StyL}zt d t |d d fWYd}~W|Sd}~ww|w) Ngarden_tracker_backup_z %Y%m%d_%H%M%Sz.sqlzapplication/sqlzContent-Dispositionzattachment; filename="rmimetyperFrbr) rArvrrrstrftimerrrrr`)rodump_sqlfilenamerer<r<r@settings_backup_full s" "  ruz/api/seed-companiescCs>t}zt|tt|}td|dW|S|w)NT)rQr)rArvr[r rr)rorr<r<r@list_seed_companies s  rvrOc Cs(tjdddpi}|dpd}|dpdd}|r"|s+tddd d fS|d s4d |}t}zWt|| 3}| d |||d|ddrRdndt |dpZdf|j }| d|f| }Wdn1svwY|tdt|ddfW|S|w)NTrarsr]rtr1Fzname and base_url requiredrbrr,z INSERT INTO GardenSeedCompany (name, base_url, search_url_template, enabled, sort_order) VALUES (%s, %s, %s, %s, %s) renabledr:r sort_order-SELECT * FROM GardenSeedCompany WHERE id = %srQr)r rdrjrrRrr.rArvrerfrh lastrowidrirmrUr)rfrsrtrorp company_idrr<r<r@create_seed_company s6   rz$/api/seed-companies/c Cs6tjdddpi}t}zt||g}|d|f|s6tddddfWdW|S| d}t |t rR| d }|rR|d sRd |}|d | d || d| d| d|f|d|f|}Wdn1swY|tdt|dW|S|w)NTraz.SELECT id FROM GardenSeedCompany WHERE id = %sF Not foundrbrtr1rr,a~ UPDATE GardenSeedCompany SET name = COALESCE(%s, name), base_url = COALESCE(%s, base_url), search_url_template = COALESCE(%s, search_url_template), enabled = COALESCE(%s, enabled), sort_order = COALESCE(%s, sort_order) WHERE id = %s rsrrxryrzr{)r rdrArvrerfrirrrjrFr`rrRr.rmrU)r~rfrorprtrr<r<r@update_seed_company s>  !   rDELETEcCt}zHt||)}|d|f|jdkr.tddddfWdW|SWdn1s8wY|tddiW|S|w) Nz+DELETE FROM GardenSeedCompany WHERE id = %srFrrbrrQTrArvrerfrowcountrrrm)r~rorpr<r<r@delete_seed_company@     rz /api/plantscCstjd}t}z=t||}|r|td|fn|td|}Wdn1s4wYt dt |dW| S| w)Nstatusz$ WHERE p.status = %s ORDER BY p.namez ORDER BY p.nameT)rQplants) r rerjrArvrerf _PLANT_SELECTrrrr)rrorprZr<r<r@ list_plantsR s    rz/api/plants/by-label/cCst}zft||D}|td|f|}|s5tddddfWdW| S|d}|d|ft |||| }Wdn1sSwYtdt ||dd |d W| S| w) Nz WHERE p.label_id = %sFrrbrrTSELECT * FROM GardenPlantEvent WHERE plant_id = %s ORDER BY event_date DESC, id DESCTr)rQrr) rArvrerfrrupperrirrrrr)label_idrorprrrr<r<r@get_plant_by_labelf s&   rz/api/plants/cCst}znt||I}|td|f|}|s1tddddfWdW|S|d|f|}|d|f|}t ||||}Wdn1sXwYtdt ||dd |t |d W|S|w) N WHERE p.id = %sFrrbrrzBSELECT * FROM GardenSchedule WHERE plant_id = %s ORDER BY next_dueTr)rQrr schedules) rArvrerfrrirrrrrr[)rrorprrrr<r<r@ get_plant{ s<    rcCstjdddpi}|dpd}|tvrtddddfS|d p%d }|tvr3tdd ddfSz t|d p;d }Wntt fyRtddddfYSw|d ks[|dkrdtddddfS|d}|dpod}|dpxdp|d}t |dpt }t }z6t||} d} |dkr|stddddfWdW|S| d|f| } | stddddfWdW|S|s| d}|dur| dr| d}n|stddddfWdW|S|dkr dnd} d} |dkrt| } n |dr"|d} g} t| |}|D]7}| d ||||d!|||d"|d|dkrH|nd|f | j}| || d#|| || d$d%fq+|dkrw|d&drw| d'||fg}| D]}| td(|f| }|r|t|q{Wdn 1swY|tdt||r|d$nd|d)d*fW|S|w)+NTrarr/Fzinvalid source_typerbrwrr+invalid statusquantityr:zinvalid quantityrz"quantity must be between 1 and 500rrsr]rrr.z(seed_id required for seed-sourced plants/SELECT * FROM GardenSeedInventory WHERE id = %szSeed not foundrrz%name required for store-bought plantsr6r8ra INSERT INTO GardenPlant (label_id, name, variety, location, status, planted_date, moisture_device_id, notes, seed_id, source_type) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) rmoisture_device_idz INSERT INTO GardenPlantEvent ( plant_id, event_type, event_date, notes, days_since_start, days_since_anchor ) VALUES (%s, %s, %s, %s, %s, %s) rrdecrement_seedzHUPDATE GardenSeedInventory SET qty = GREATEST(0, qty - %s) WHERE id = %sr)rQr|rrr|)r rdrjrPLANT_SOURCE_TYPESrPLANT_STATUSESrhrrarbrrGrArvrerrfrirr~r}rrrrmr)rfrrrrrsrrrorpr.r event_notes created_ids label_idsrrrrTr<r<r@ create_plant s   O O O       E rcCsvtjdddpi}t}zt||}|d|f|s6tddddfWdW|S| d}|durX|t vrXtdd dd fWdW|S|d | d | d | d|d|vrst | dnd| d| d| d| d|f |t d|f|}Wdn1swY| tdt|dW|S|w)NTraz(SELECT id FROM GardenPlant WHERE id = %sFrrbrrrrwaP UPDATE GardenPlant SET name = COALESCE(%s, name), variety = COALESCE(%s, variety), location = COALESCE(%s, location), status = COALESCE(%s, status), planted_date = COALESCE(%s, planted_date), moisture_device_id = COALESCE(%s, moisture_device_id), notes = COALESCE(%s, notes), seed_id = COALESCE(%s, seed_id), source_type = COALESCE(%s, source_type) WHERE id = %s rsrrrrrrrrrQr)r rdrArvrerfrirrrjrrbrrmr)rrfrorprrr<r<r@ update_plant sF  '  ' $rz$/api/plants//duplicatec Cst}zt||}|td|f|}|s1tddddfWdW|St|dd}| dp>d }|d krEd nd }d}|d kre| d re|d|d f|}|ret |}| dpmt } t | trw| } |d||d| d| d| dpd| | d| d|d kr| d nd|f |j} |d| || |ddf|td| f|} Wdn1swY|tdt| ddfW|S|w)NrFrrbrr:rrr/r.r6r8rrrz INSERT INTO GardenPlant (label_id, name, variety, location, status, planted_date, moisture_device_id, notes, seed_id, source_type) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) rsrrrr+rrz INSERT INTO GardenPlantEvent ( plant_id, event_type, event_date, notes, days_since_start, days_since_anchor ) VALUES (%s, %s, %s, %s, %s, %s) rTrr|)rArvrerfrrirrr~rjrrrGrFrr}rmr) rrorprrrrrr.rnew_id new_plantr<r<r@duplicate_plant4 s^  6   3rcCr) Nz%DELETE FROM GardenPlant WHERE id = %srFrrbrrQTr)rrorpr<r<r@ delete_plantr rrz!/api/plants//eventscCst}zZt||>}|td|f|}|s1tddddfWdW|S|d|f|}t ||||}Wdn1sMwYtd|dW|S|w) NrFPlant not foundrbrrT)rQr) rArvrerfrrirrrr)rrorprrZr<r<r@list_plant_events s&   rc Cstjdddpi}|dpd}|tvrtddddfSt|d p't}t}zt || x}| t d |f| }|sYtdd dd fWdW|St|d }t|d}t||||\} } | d||||d|d|d||| | f t|||||j} | d| f| } t|||| } Wdn1swY|td| ddfW|S|w)NTrarr0Finvalid event_typerbrwrrrrrheighta$ INSERT INTO GardenPlantEvent ( plant_id, event_type, event_date, notes, qty, unit, location, height, days_since_start, days_since_anchor ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) rrrB,SELECT * FROM GardenPlantEvent WHERE id = %srQrr|)r rdrj EVENT_TYPESrrbrrGrArvrerfrrirrrrrr}rrm) rrfrrrorprrr days_sincerevent_idrr<r<r@create_plant_event sR  %"rz0/api/plants//events/cCs*tjdddpi}t}zt||`}|td|f|}|sSELECT * FROM GardenPlantEvent WHERE id = %s AND plant_id = %srrrrwrzinvalid event_daterrrBrrzinvalid days_since_startrr.r6rrra UPDATE GardenPlantEvent SET event_type = %s, event_date = %s, notes = %s, qty = %s, unit = %s, location = %s, height = %s, days_since_start = %s, days_since_anchor = %s WHERE id = %s AND plant_id = %s rr)r rdrArvrerfrrirrrjrrbrrrhrrarrrrrrm)rrrfrorprrrrrrrBrrrrr<r<r@update_plant_event s  L L L L L   IrcCst}zIt||*}|d||f|jdkr/tddddfWdW|SWdn1s9wY|tddiW|S|w) NzcCs`|r d|dvrtddddfSt}tj||}tj|s+tddddfSt||S) Nrr1Fz invalid pathrbrwz not foundr)r7rrMrGr6rrr )relrrr<r<r@serve_seed_type_upload2 s  rz/api/seed-imagec Cstjdpd}|stddddfSt|}tj|}|j p#d }t }zt |t |}W|n|wt||sKtddddfS|jdkrYtdd ddfSd }|tvred |d }z8tjj|d |dd}tjj|dd}|} |jdd} Wdn1swYt| | ddidWStjjtjjtfy} ztdt| ddfWYd} ~ Sd} ~ ww)Nrr]Fz url requiredrbrwzhost not allowedrz invalid urlzhttps://en.wikipedia.org/r,r1zMozilla/5.0 GardenTracker/1.0)rrNrrrrLz image/jpegz Cache-Controlzpublic, max-age=86400rp)r rerjrrr0rr4r5r r8rArvrrrrrrrrrrrrrrr`) raw_urlrrrrorrefererrrrf content_typerer<r<r@seed_image_proxy= sL     "rz /api/seedsc Cst}z_t||}|d|}Wdn1s wYt|p*i}t|dp2d}g}|D]}t|}|dpD|}t |dt |k|d<| |q8t d|dW| S| w) Nz=SELECT * FROM GardenSeedInventory ORDER BY crop_name, varietyrrrrrT)rQseeds) rArvrerfrrrhrjrUrHrrr) rorprZrr rrTrrDr<r<r@ list_seedsf s"     rcCs4tjdddpi}|dpd}|stddddfSt}ztt||H}|d ||d |d d |d p=d|dt |dt |d|d|d|df |j }|d|f| }Wdn1stwY| t ||d tdt|ddfW|S|w)NTrarr]Fzcrop_name requiredrbrwa INSERT INTO GardenSeedInventory (crop_name, variety, qty, unit, source, purchase_date, expiry_date, location, low_stock_threshold, notes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) rrrrBrr> purchase_date expiry_daterrrrrQr.r|)r rdrjrrrArvrerfrbr}rirmrrUr)rfrrorprr.r<r<r@ create_seed{ s<      rz/api/seeds/cCsJt}zt||}|d|f|}Wdn1s"wY|s7tddddfW|St||}t||d| d}t |d| d}d}|sht |}|sgt |d| dd}n*| d sgst ||s|tvrt|t |st |d| dd}nt |}td|||d W|S|w) NrFrrbrrrTr)rQr.rtype_info_loading)rArvrerfrirrr rrjr|rrrs_seed_type_image_retry_donerA)rrorprTr.rrQrr<r<r@get_seed sJ       rz$/api/seeds//product-urlcCsptjddpi}|dpd}|stddddfS|d s*|d s*d |}|d d d }t}zt || }| d|f| }Wdn1sVwY|sktddddfW| St||s~tddddfW| St|}|stdddW| S|d}|d} t||| |d|dd} | tk} |d|d|t||t|dpgd} t||| } t| }t|d}t|d}|s | stdd|| rd| nd| | |ddW| Stdd| | |dW| S| s |s tdd ddfW| St||| |}td|d!W| S| w)"NT)rcrr]FzProduct URL is requiredrbrwr+r,rrr1rrrz/refresh-type-infocCst}z~t||}|d|f|}Wdn1s"wY|s7tddddfW|St|d|d}t ||}|}|d|fWdn1s\wY| t |t |d|d|d td d d W|S|w) NrFrrbrrrz3DELETE FROM GardenSeedTypeInfo WHERE cache_key = %srT)rQr)rArvrerfrirrr|rjrrmrrr)rrorprTrQrr<r<r@refresh_seed_type_info! s0      rz$/api/seeds//type-imagesc Cstjd}|stddddfSt}zt||}|d|f|}Wdn1s3wY|sHtddddfW| St |d |d }t ||}t |t krotdd t d ddfW| St|jptd pwd }tj|d}|dvrd}td|} t|d| } tjt| } tjtj| dd|| |d| | |ddt|||d |d |ddt||d |d } td| ddfW| S| w)NrFzNo file uploadedrbrwrrrrrzMaximum z imagesz image.jpgr:)rrrrz.gifrr1T)exist_okrjuploaded)rrWr=r>rrr|)r filesrjrrArvrerfrirr|rrrvr rtrGr6splitextr8urandomhexrSrrMmakedirsrHsaverrr) rrrorprTrQr safe_namerrtrrrr<r<r@upload_seed_type_image; sN        rc Cst}t}|dkr|durtddddfSt}zt||}|d|f|}Wdn1s9wY|sNtddddfW|St |d | d }t ||}d}t |} |dur|d ksq|t |krtdd ddfW|S| |}ng} |D]} t| |r|dur| }q| | q|stdd ddfW|S| d rt|d t|||d | d | ddt||d | d } td| dW|S|w)Nr]Furl or index requiredrbrwrrrrrrImage not foundrWTrr)rgrirrArvrerfrirr|rjrrrrrnrrrr) rrrhrorprTrQrremovedkeptrCrr<r<r@delete_seed_type_imageg sP        rz,/api/seeds//type-images/primaryPATCHc stt}dkr|durtddddfSt}zt||}|d|f|}Wdn1s9wY|sNtddddfW|St |d | d }t ||}|dur||d ksk|t |krytdd ddfW|S|}n t fd dt|Dd}|durtdd ddfW|S|d krt|||d | d |ddt||d | d }td|dW|S||} |d | t|||d | d |ddt||d | d }td|dW|S|w)Nr]Frrbrwrrrrrrrc3s"|] \}}t|r|VqdSr)rn)rNryrCrCr<r@r s z/make_seed_type_image_primary..Trr)rgrirrArvrerfrirr|rjrrnextrkrrrinsert) rrhrorprTrQrrrrchosenr<rCr@make_seed_type_image_primary sV       rcCsLtjdddpi}t}zt||r}|d|f|s6tddddfWdW|S|d| d | d | d | d | d d|vrXt | dndd|vrdt | dnd| d| d| d|f |d|f|}Wdn1swY| tdt |dW|S|w)NTraz0SELECT id FROM GardenSeedInventory WHERE id = %sFrrbra UPDATE GardenSeedInventory SET crop_name = COALESCE(%s, crop_name), variety = COALESCE(%s, variety), qty = COALESCE(%s, qty), unit = COALESCE(%s, unit), source = COALESCE(%s, source), purchase_date = COALESCE(%s, purchase_date), expiry_date = COALESCE(%s, expiry_date), location = COALESCE(%s, location), low_stock_threshold = COALESCE(%s, low_stock_threshold), notes = COALESCE(%s, notes) WHERE id = %s rrrrBr>rrrrrrr) r rdrArvrerfrirrrjrbrmrU)rrfrorpr.r<r<r@ update_seed s>  & #rcCr) Nz-DELETE FROM GardenSeedInventory WHERE id = %srFrrbrrQTr)rrorpr<r<r@ delete_seed rrz#/api/seeds//adjust-qtycCs tjdddpi}|d}|durtddddfSt}zlt||J}|d|f|}|sJtdd dd fWdW| St d t |d t |}|d ||f|d|f|}Wdn1stwY| tdt |dW| S| w)NTradeltaFzdelta requiredrbrwrrrrrz5UPDATE GardenSeedInventory SET qty = %s WHERE id = %sr)r rdrjrrArvrerfrirrrHrmrU)rrfrrorpr.new_qtyr<r<r@adjust_seed_qty s2     rz /api/tanksc Cst}zt||e}|d|}g}t}|D]K}t|}|d|df|}|r7t|nd|d<t | dpBd}|r\|dt |d} | |k|d <t | |d <nd |d <d|d <| |qWdn1stwYtd |d W|S|w) N&SELECT * FROM GardenTank ORDER BY namez SELECT * FROM GardenTankReading WHERE tank_id = %s ORDER BY recorded_at DESC LIMIT 1 rlatest_readingrDr-rEr4 test_overdue next_test_dueT)rQrS)rArvrerfrrrrUrirhrjrrMrrr) rorprSrrrTrlatestr6rWr<r<r@ list_tankss8    rcCstjdddpi}|dpd}|stddddfSt}zbt||>}|d ||d |d |d |d |d|d|dd|df |j }|d|f| }Wdn1sjwY| tdt |ddfW| S| w)NTrarsr]Fz name requiredrbrwa INSERT INTO GardenTank (name, volume_gal, solution_type, target_ph_min, target_ph_max, target_ec_min, target_ec_max, test_interval_days, notes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) volume_gal solution_type target_ph_min target_ph_max target_ec_min target_ec_maxrDr-r&SELECT * FROM GardenTank WHERE id = %srQrTr|)r rdrjrrrArvrerfr}rirmrUr)rfrsrorptank_idrTr<r<r@ create_tank@s8   rz/api/tanks/cCs$tjdddpi}t}zt||^}|d|f|s6tddddfWdW|S|d| d | d | d | d | d | d| d| d| d|f |d|f|}Wdn1svwY| tdt |dW|S|w)NTra'SELECT id FROM GardenTank WHERE id = %sFrrbra UPDATE GardenTank SET name = COALESCE(%s, name), volume_gal = COALESCE(%s, volume_gal), solution_type = COALESCE(%s, solution_type), target_ph_min = COALESCE(%s, target_ph_min), target_ph_max = COALESCE(%s, target_ph_max), target_ec_min = COALESCE(%s, target_ec_min), target_ec_max = COALESCE(%s, target_ec_max), test_interval_days = COALESCE(%s, test_interval_days), notes = COALESCE(%s, notes) WHERE id = %s rsrrrrrrrDrrr) r rdrArvrerfrirrrjrmrU)rrfrorprTr<r<r@ update_tankfs<  $ !rcCr) Nz$DELETE FROM GardenTank WHERE id = %srFrrbrrQTr)rrorpr<r<r@ delete_tankrrz!/api/tanks//readingscCstt}z1t||}|d|f|}Wdn1s"wYtdt|dW|S|w)Nz SELECT * FROM GardenTankReading WHERE tank_id = %s ORDER BY recorded_at DESC LIMIT 100 T)rQreadingsrArvrerfrrr[r)rrorprZr<r<r@list_tank_readingss   rc Cs&tjdddpi}t}zt||]}|d|f|s6tddddfWdW|St | dp@t }|d || d | d | d | d | d|f|j }|d|f|}Wdn1suwY|tdt|ddfW|S|w)NTrarFzTank not foundrbrrEz INSERT INTO GardenTankReading (tank_id, ph, ec, temp_f, volume_gal, notes, recorded_at) VALUES (%s, %s, %s, %s, %s, %s, %s) phecr-rrz-SELECT * FROM GardenTankReading WHERE id = %s)rQreadingr|)r rdrArvrerfrirrrcrjrrr}rmrU)rrfrorprE reading_idrr<r<r@create_tank_readings:   rz/api/schedulescCspt}z/t||}|d|}Wdn1s wYtdt|dW|S|w)Na SELECT s.*, p.name AS plant_name, t.name AS tank_name FROM GardenSchedule s LEFT JOIN GardenPlant p ON p.id = s.plant_id LEFT JOIN GardenTank t ON t.id = s.tank_id ORDER BY s.next_due T)rQrr)rorprZr<r<r@list_scheduless   rc CsDtjdddpi}|dpd}|tvrtddddfS|d p)|d d }t|d p3d }t |dp?t }t }zYt ||5}|d|d|d|||||ddrddnd|df|j}|d|f|}Wdn1swY|tdt|ddfW|S|w)NTrar8r&Finvalid task_typerbrwr=rrr2r3next_duez INSERT INTO GardenSchedule (plant_id, tank_id, task_type, title, interval_days, next_due, enabled, notes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) rrrxr:rr*SELECT * FROM GardenSchedule WHERE id = %srQr5r|)r rdrj TASK_TYPESrrCr=rrhrcrrrArvrerfr}rirmrUr) rfr8r=r2rrorp schedule_idr5r<r<r@create_schedules<  r z /api/schedules/cCstjdddpi}t}zt||}|d|f|s6tddddfWdW|S| d}|durX|t vrXtdd dd fWdW|S| d }|d | d | d|| d| dd|vr|t | dnd|durdn|durdnd| d|f |d|f|}Wdn1swY| tdt |dW|S|w)NTrarFrrbrr8rrwrxa  UPDATE GardenSchedule SET plant_id = COALESCE(%s, plant_id), tank_id = COALESCE(%s, tank_id), task_type = COALESCE(%s, task_type), title = COALESCE(%s, title), interval_days = COALESCE(%s, interval_days), next_due = COALESCE(%s, next_due), enabled = COALESCE(%s, enabled), notes = COALESCE(%s, notes) WHERE id = %s rrr=r2rr:rrr)r rdrArvrerfrirrrjrrcrmrU)r rfrorpr8rxr5r<r<r@update_schedulesF  &  &   #r cCr) Nz(DELETE FROM GardenSchedule WHERE id = %srFrrbrrQTr)r rorpr<r<r@delete_scheduleGrr z)/api/schedules//completec CsLtjdddpi}t}zt||r}|d|f|}|s8tddddfWdW|St }|d||| d ft |}|d ||f| d ru| d d kru|d|d t | d pr| df|d|f|}Wdn1swY|tdt|dW|S|w)NTrarFrrbrz INSERT INTO GardenTaskCompletion (schedule_id, completed_at, notes) VALUES (%s, %s, %s) rz5UPDATE GardenSchedule SET next_due = %s WHERE id = %srr8r'z INSERT INTO GardenPlantEvent (plant_id, event_type, event_date, notes) VALUES (%s, 'fertilized', %s, %s) r=r)r rdrArvrerfrirrrrrjr7rrGrmrU)r rfrorpr5rrr<r<r@complete_scheduleVsF  #  r z/api/tasks/duec Csfttjdpd}t}zt|tt|d}| o}| d|f| }| d| }t}g}|D]J}| d|df| } t|dpRd} | pa| d t| d|k} | r| d |d|d d |d t| r| d t| dn|d dqcCszt}|drtddidfS|r!tjtj||r!t||Stj|d}tj|r3t|dStdd|dd fS) Nzapi/rrrz index.htmlz!Garden Tracker frontend not builtz>Build the React client and place output in GARDEN_CLIENT_DIST.)rrdisti)rr.rrGr6existsrr )r6dist_dir index_pathr<r<r@spa0s     r!)r:)NF)r)r<r)r)r)Nr<)Fr)__doc__rrrloggingrGrOrrH urllib.errorr urllib.parseurllib.requestrrrdecimalrr=flaskrrrr r werkzeug.utilsr blueprints.alertsr r configrrrrrrrrrrrrrrr getLogger__name__rbpcursors DictCursorr?rrrrr_Lockr^rurrrrrvrrKrArMrUr[rbrcrvr~rnrrrrrrrrrrrrrrrrrrr frozensetrrrBrlrrr0r;rFrMrSrXrZr_r`rgrirnrsryr|rrrrrrrrrrrrrrrrrrr r rrrrrr r,r9r@rDrZrdr`rpr1rirrrr_r^rrrUrarrrrprrrrrrrrrrrrr rrr&r1r7r>rYr]r`before_app_requestrarouterfrkrnrurvrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr r r r rrrrrr!r<r<r<r@s  D       k       ,           )       @      %#(    p  t% 4P '  !       # #) g/=1T  (''U++-."%,&&. . E  .