C"jk7 JUddlmZddlmZddlmZddlmZmZmZm Z m Z ddl Z ddl Z ddl Z ddlmZddlZddlZddlZddlZddlmZddlZddlZddlmZmZdd lmZmZmZdd lm Z dd l!m"Z"dd l#m$Z$dd l%m&Z&ddl'm(Z(ddl)m*Z*dZ+dZ,dZ-dZ.dZ/dZ0dZ1dZ2dZ3dZ4dZ5hdZ6ddddZ7dd Z8dd!Z9dd"Z:dd#Z;dd$Zdd'Z?dd(Z@dd)ZAdd*ZBdd+ZCdd,ZDdd-ZEdd.ZF dd/ZGdddd0 dd1ZHdd2ZIdd3ZJdd4ZKdd5ZLdd6ZMdd7ZNdd8ZOdd9ZP dd:ZQdd;ZRGd<d=eZSGd>d?eZTGd@dAeZUGdBdCeZVGdDdEeSZWGdFdGeZXGdHdIeZYGdJdKeZZGdLdMeZ[GdNdOeZ\GdPdQeZ]GdRdSeZ^GdTdUeZ_GdVdWeZ`GdXdYeZaGdZd[eZbGd\d]eZcGd^d_eZdGd`daeZeGdbdceZfGdddeeZgGdfdgeZhGdhdieZiGdjdkeZjGdldmeZkGdndoeZlGdpdqeZmGdrdseZnGdtdueZoGdvdweZpGdxdyeZqGdzd{eZrGd|d}eZsed~GddZted~GddZued~GddZved~GddZwed~GddZxed~GddZyed~GddZzed~GddZ{ed~GddZ|ed~GddZ}ed~GddZ~GddZdaded<ddZy)) annotations)contextmanager) dataclass)datedatetimetime timedeltatimezoneN)Path)Literal)ZoneInfoZoneInfoNotFoundError) BaseModel ConfigDictField) get_settings)MODULE_CATALOG)Tenant) TenantModule)User)Venuetenant_powerup_adminvenue_powerup_adminuser_powerup_adminz PowerUp Adminz powerup-adminzzanchinfede@gmail.comzsuperadmin@powerup.local) assistantordini prenotazioni documentsmenuhomemadehomemade_managefiscal_documents timeclock inventory consumptionsreports tips_salatips_sala_managetips_bartips_bar_manage) rrrrr r"r#r$r'r))rrr pz>barboth restaurantBar RistorantezBar e ristorante)r,r.r-cHtjtjSN)rnowr utc-apps/backend-hub/app/services/tenant_store.py_utcnowr8Os << %%r6c2tjSr2)r8 isoformatr5r6r7_iso_nowr;Ss 9    r6c~ ttjS#ttf$rtdcYSwxYw)Nz Europe/Rome)r rassistant_timezoner ValueErrorr5r6r7_tenant_local_timezoner?Ws9' 99:: !: .' &&'s <<c<tjtSr2)rr3r?r5r6r7 _local_nowrA^s <<.0 11r6c2tjSr2)rAr:r5r6r7_local_iso_nowrCbs < ! ! ##r6c tjd|jddjd}t j dd|j jd}|xsdtjdS)NNFKDasciiignorez [^a-zA-Z0-9]+-ztenant-) unicodedata normalizeencodedecoderesublowerstripsecrets token_hex)value normalizedslugs r7_normalize_slugrWfsr&&vu5<|jjSr2rQrPrTs r7_normalize_lookupr[ls ;;=    r6cf|xsdjj}|dvry|dvry|dvryy)Nr->r. ristoranter.>r,clubr,>allr-entrambeentrambirYrTrUs r7_normalize_homemade_usage_scopercpsC/6((*002J11_$<< r6c&tt|Sr2)HOMEMADE_USAGE_SCOPE_LABELSrcrZs r7_homemade_usage_scope_labelrf{s &'Fu'M NNr6ctjddt|xsdjj S)N\s+)rNrOstrrQrPrZs r7_normalize_inventory_barcoderks0 66&"c%+2.446 7 = = ??r6ct|}|s tS|g}|jrAt|dkDr3|j dr|ddnd|}|r||k7r|j |tt j|S)N0)rktupleisdigitlen startswithappenddictfromkeys)rTrUaliases alternates r7_build_carton_barcode_aliasesrxs-e4J wlGJ! 3&0&;&;C&@JqrN*FV j0 NN9 % w' ((r6cp|xsdjj}|dvry|dvry|dvry|dvry y ) Nri>ggrgrammigrammorz>g/lgr/lg litrogr litro grammi litro g_per_literr>drop/ldrops/lgocce/l gocce litro gocce/litro goccia litrogocce per litrodrops_per_literr>partpartepartipartsrmlrYrbs r7#_normalize_homemade_ingredient_unitrsV+2$$&,,.J44ZZ  !88 r6c|Dchc]@}t|jdxsddvrt|jdxsdB}}|dhk(rdSdScc}w)Nmeasurement_unitr>rzrrrzrjget) ingredientsitemproportional_unitss r7(_derive_homemade_recipe_measurement_unitrsn   txx*+3t 48K K DHH' ( 0D1 %-3747 sAAcd}d}|D]`}tt|jdxsd}t|jdxsd}|dkrN|dvr||z }|dvs\||z }b|dkDr|S|S)Nrr part_amountrrr>rrrrjrfloat)rliquid_reference_totalfallback_proportional_total ingredientrrs r7!_compute_homemade_reference_totalrs "%!2 >s:>>RdCeCmim?noJNN=9@SA !   #E E '; 6 ' ~ - "k 1 "2&rrrrr)rtotalrrrs r7(_compute_homemade_liquid_reference_totalrsq E!! >s:>>RdCeCmim?no > 1 JNN=9@SA ? [ E ! Lr6c t|xsd}t|xsd}tt |xsdj xr |dkDxr|dkDS#ttf$rYywxYw)NrFri)r TypeErrorr>boolrjrQ)yield_ingredient_nameyield_input_amountyield_output_ml input_amount output_mls r7_has_homemade_explicit_yieldrsv /415 /.Q/  )/R0668 9 `lQ>N `S\_`S`` z "sAA&%A&rrrcvt|||rt|xsdSt|}|dkDr|St|S)Nrr)rrrr)rrrrrs r7(_compute_homemade_recipe_reference_totalrsK$3-' _)**EkR!%% ,[ 99r6ch|xsdjj}|dvr td|S)Nri>r,salazArea mance non valida.)rQrPr>rbs r7_normalize_tips_arears7+2$$&,,.J(122 r6ctjd}d}tjd|j d||}d|d|j d|j S)Nisha256utf-8zpbkdf2_sha256$$)osurandomhashlib pbkdf2_hmacrLhex)passwordsalt iterationsdigests r7_hash_passwordrsX ::b>DJ  8??7+CT: VF JintbytesfromhexrrrLrRcompare_digest) r stored_hash algorithmraw_iterationssalt_hex digest_hexrrexpected_digest candidates r7_verify_passwordrs:E:K:KCQR:S7 >8ZO#( }}X&-- 3##Hhoog.FjYI  ! !)_ ==  s"B5B! BB! B-,B-c |s tSg}t}|D]\}t|xsdjj }|t vs||vr;|j ||j|^t|SNri)rosetrjrQrPTENANT_PERMISSION_KEYSrsaddvaluesrUseen raw_valuerTs r7_normalize_permission_valuesr s wJUD IO$**,224 . .%4- %     r6c|dvrtS||dk(rtStSd} tj|}t |tr|Dcgc] }t|}}t|}|S#tj $rd}YLwxYwcc}w)N>owner super_adminstaff) rDEFAULT_STAFF_PERMISSIONSrojsonloadsJSONDecodeError isinstancelistrjr)rolepermissions_jsonraw_permissionsparsedrrUs r7_resolve_role_permissionsrs ''%%,0GO(HH:>O,-&$1783t988-o>J    9sA/B /BBcZ|dvrytjtt|SN>rr)rdumpsrr)r permissionss r7_serialize_role_permissionsr.s( '' ::d7 DE FFr6c |s tSg}t}|D]\}t|xsdjj }|t vs||vr;|j ||j|^t|Sr)rorrjrQrPASSISTANT_SCOPE_KEYSrsrrs r7!_normalize_assistant_scope_valuesr4s wJUD IO$**,224 , , %     r6ct|}g}t}|D]P}d}|tvr|}n|dk(rd}n|dk(rd}n|dk(rd}|s*||vs/|j||j |Rt |S)Nr!r r(r'r*r))rrrrsrro)rnormalized_permissionsscopesr permission mapped_scopes r7"_assistant_scopes_from_permissionsrCs9+FFUD, #  - -%L , ,%L - -&L , ,%L L4 MM, ' HH\ " # =r6c|dvrtSd}|I tj|}t |t r|Dcgc] }t |}}t|St||}t|S#tj$rd}YawxYwcc}wr) rrrrrrrjrrr)rrassistant_scopes_json raw_scopesrrrs r7_resolve_user_assistant_scopesrWs  ''##59J( ZZ 56F fd #067#d)7J70<<+D2BCK -k ::## F 8sA/B /BBcZ|dvrytjtt|Sr)rrrr)rrs r7 _serialize_user_assistant_scopesrms( '' ::dHj>dtK5>dtt`anp9qK6qr6rvc,eZdZUeddZded<y)HomemadePreparationWritePayload r<rj prepared_onN)rrrrrrr5r6r7rrsr:K:r6rcVeZdZUeddZded<edZded <d Zd ed <y )HomemadeStockWritePayloadrIr;r<rj recipe_idrr@rrBrrCrDN)rrrrrrrBrDr5r6r7rr s,aC8Is8qkHe!',I$,r6rcheZdZUeddZded<eddZded<eedZd ed <y) &HomemadeOperationalCalendarRulePayloadNrrFrr2r3r)r=z list[int]weekdays) rrrrr2rr3rrr5r6r7rrs5"4B?J ? "=Hj=CHiCr6rc,eZdZUeedZded<y)"HomemadeOperationalCalendarPayloadrz,list[HomemadeOperationalCalendarRulePayload]rulesN)rrrrrrrr5r6r7rrs:?PTac:dE 7dr6rcHeZdZUeddZded<dZded<dZded <y) HomemadeStockSettingsPayloadrim)r6r7rminimum_stock_daysNz)HomemadeOperationalCalendarPayload | None bar_calendarrestaurant_calendar)rrrrrrrrr5r6r7rrs+ %s 33>BL;BEIBIr6rcHeZdZUeddZded<edZded <y ) TipsRosterEntryPayloadrr;r<rjr'rr@rscoreN)rrrrr'rrr5r6r7rr s!s3D#3A;E5r6rc,eZdZUeedZded<y)TipsRosterWritePayloadr4rzlist[TipsRosterEntryPayload]entriesN)rrrrrrrr5r6r7rr%s,1$SV,WG )Wr6rceZdZUedddZded<edZded <edd Zded <edd Zd ed <ee dZ ded<ee dZ ded<dZ ded<y)TipsRunPreviewPayloadNr)rrr=rtip_daterr@r total_amountrU pos_amountrJpos_effective_amountr4rr* absent_namesr;history_run_idsFrmark_as_delivered) rrrrrrrrrrrrrr5r6r7rr)ss "LHjL1+L%%aA.J.).t)B,B#DSIL)I!&t!LOYL#t#r6rceZdZUdZded<y)TipsRunPayoutStatusPayloadTr deliveredN)rrrrrr5r6r7rr3s Itr6rceZdZUeddZded<eddZded<d Zd ed <ed d Zded<dZ ded<y )ReportCreatePayloadrIrqr<rjtitler rz descriptionproblemzTLiteral['problem', 'damage', 'breakage', 'malfunction', 'purchase_request', 'other']rZNrFrlocationnormalz*Literal['low', 'normal', 'high', 'urgent']priority) rrrrrrrrZrrr5r6r7rr7sK!4E34d;K;enHbn #>Hj>;CH8Cr6rc6eZdZUded<eddZded<y)ReportStatusUpdatePayloadzJLiteral['new', 'reviewed', 'in_progress', 'reported_to_owner', 'resolved']statusNrzrFr admin_note)rrrrrrr5r6r7rr?s VV"4DAJ Ar6rcJeZdZUeddZded<eddZded<y ) PushSubscriptionKeysPayloadr<rjp256dhrauthN)rrrrrrrr5r6r7rrDs$2#6FC6s3D#3r6rcxeZdZUeddZded<edZded <ed d Zd ed<edZ y )PushSubscriptionPayloadrr<rjendpointkeys)aliasrsubscription_keysNrrFr user_agentT)populate_by_name) rrrrrrrrr model_configr5r6r7rrIs?RD9Hc95:5H2H"4C@J @t4Lr6rc,eZdZUeddZded<y)PushSubscriptionDeletePayloadrrr<rjrN)rrrrrrr5r6r7rrQsRD9Hc9r6rT)frozenceZdZUded<ded<ded<ded<ded<ded<ded<ded <ded <d ed <d ed <ded<ded<ded<ded<ded<y)SessionIdentityrjtoken tenant_id tenant_slug tenant_namehome_tenant_idhome_tenant_slughome_tenant_namer1 user_emailrr  user_namertuple[str, ...]rr+ database_path expires_atNrrrrr5r6r7rrUs\ JN LO I  %%Or6rceZdZUded<ded<ded<ded<ded<ded<ded <ded <ded <ded <ded <ded<ded<ded<y)MenuAssetRecordrjidr original_name display_name mime_typekindrfile_size_bytes storage_pathextracted_text analysis_textrr error_detail created_at updated_atNrr5r6r7rrisO GNN I KOOr6rceZdZUded<ded<ded<ded<ded<ded<ded <d ed <ded <ded <d ed<d ed<d ed<ded<ded<ded<ded<ded<d ed<d ed<d ed<ded<ded<ded<d ed<ded<ded<y )!FiscalDocumentRecordrjrrrrrrrrr file_hashr document_typedocument_number document_daterTrJrcurrency summary_textr preview_text drive_file_id drive_web_urldrive_uploaded_atrmatching_status review_statusrrrNrr5r6r7rr{s GNN IM!! KOOr6rceZdZUded<ded<ded<ded<ded<ded <ded <ded <ded <d ed<d ed<d ed<d ed<d ed<d ed<d ed<ded<ded<ded<ded<y)FiscalDocumentLineItemRecordrjrr document_idr line_indexrrOiso_coder category_code unit_coderJ pack_countrBgross_quantity tare_quantity net_quantity unit_price line_totalvat_code raw_row_textrrNrr5r6r7rrsu GNO  OOr6rc,eZdZUded<ded<ded<y)FiscalDocumentSettingsRecordrjrr inbound_emailrNrr5r6r7rrsNr6rceZdZUded<ded<ded<ded<ded<ded<ded <ded <ded <ded <ded <ded<ded<ded<ded<y)FiscalDocumentInboxItemRecordrjrr message_id attachment_idrrsubjectsender received_atattachment_namer sync_statusrrrrNrr5r6r7rrsU GNO L NOOr6rceZdZUded<ded<ded<ded<ded<ded<ded <d ed <ded <ded <ded<ded<ded<y)TimeclockEntryRecordrjrr1rrr r started_atended_at int | Noneduration_secondsstarted_source ended_sourcer\rrNrr5r6r7r r sK G LO   OOr6r ceZdZUded<ded<ded<ded<ded<ded<ded <ded <ded <ded <ded <ded<ded<ded<ded<ded<ded<y)TenantReportRecordrjrreporter_user_idr reporter_namereporter_usernamereporter_emailrrrZrrrrstatus_updated_by_user_idstatus_updated_by_name resolved_atrrNrr5r6r7rrsa G!! JMM K))&&OOr6rc^eZdZUded<ded<ded<ded<ded<ded<ded <ded <y ) AssistantThreadRecordrjrrr1surfacerr state_jsonrrNrr5r6r7rrs+ GN L L KOOr6rcJeZdZUded<ded<ded<ded<ded<ded<y ) AssistantStoredMessagerjr thread_idrcontentrr sort_indexNrr5r6r7r!r!s  GN I LOOr6r!c|eZdZUded<ded<ded<ded<ded<ded<ded<ded <ded <ded <ded <y )AssistantRunRecordrjrr"rr1rroutemodel user_messageassistant_reply trace_jsonrNrr5r6r7r&r&s; GNN L L J JOOr6r&cZeZdZddZd>dZd=dZe dZ e d?dZ d@d Z d=d Z dAd ZdBd ZdCd ZdDdZdEdZdFdZdGdZdHdZdIdZdJdZdKdZdLdZdMdZdNdZdOdZdPdZdQdZdRdZdSdZ dTdZ! dUdZ"dVd Z#dVd!Z$dWd"Z%dWd#Z&dXd$Z'dYd%Z(d&d'dZd(Z)d&d&d&d)d*d+ d[d,Z*d&d- d\d.Z+d]d/Z,d^d0Z-d_d1Z.d`d2Z/dad3Z0dVd4Z1dVd5Z2dVd6Z3dVd7Z4dVd8Z5dVd9Z6dVd:Z7dVd;Z8dVd<Z9dbd=Z:dbd>Z;dcd?Z<ddd@Z=d^dAZ>d^dBZ?d^dCZ@d^dDZAd^dEZB de dfdFZC dgdGZDd^dHZEd^dIZFdhdJZG didKZH djdLZI dkdMZJ dldNZK dmdOZL dndPZMdodQZNdodRZOdodSZPdpdTZQd^dUZRdqdVZSd^dWZT drdXZUd&dY dsdZZVdtd[ZWdtd\ZXdud]ZYdvd^ZZdvd_Z[ dwd`Z\ dwdaZ] dxdbZ^ dydcZ_ dzddZ` d{deZa d|dfZb d}dgZcd&d&dh d~diZd ddjZe ddkZf ddlZg ddmZh ddnZi ddoZj ddpZkd&dY dsdqZld=drZm ddsZn ddtZodduZp ddvZq ddwZr ddxZsddyZt ddzZu dd{Zv dd|Zw dd}Zx dd~Zy ddZz ddZ{ ddZ| ddZ} ddZ~ ddZ ddZddZ ddZddZ ddZddZ ddZddZddZddZ ddZ ddZ ddZ ddZ ddZ ddZ ddZ de ddZ ddZ ddZ ddZd&dd ddZ ddZd&d&d ddZdd ddZ ddZddZ ddZddZ ddZd&d&d ddZddZ ddZ ddZd&dd ddZd&d&dd ddZddZd&dd ddZ ddZdd ddZddd&dd ddZd&d*d ddZ ddZddZd&d ddZd&d ddZ dd„Z ddÄZ ddĄZddńZddƄZ ddDŽZ ddȄZ ddɄZd&d&d&dʜ dd˄Zdd̄Zdd̈́Zdd΄Z ddτZddЄZddфZdd҄ZddӄZddԄZddՄZddքZddׄZd&d&d&d&d؜ ddلZÐddڄZĐddۄZŐdd܄ZƐdd݄Zǐd^dބZȐdd߄ZɐddZʐddZːddZ̐ddZ͐ddZΐddZ ddZАddZѐddZҐd^dZӐd^dZԐd^dZ ddZ ddZ ddZ ddZd&d ddZڐddZ ddZ ddZ ddZ ddZ ddZd&d*d ddZddZddZ ddZddZddZddZd)d dܐdZddZddZddZdݐdZdސdZdߐdZddZdd Zdd Zd d d&d  dd ZddZddZd&d&d&d&d&d ddZddZddZd&d&d&d&d&dd d d dddd&d ddZddZddZddZddZ ddZddZdd Zd&d&d&d&d&d&d&d&d&d&d&d&d&d&d&d&d&d&d! dd"Zdd#Zdd$Z dd%Zd&ddd'Zdd(Z dd)Zd&d&d&d* dd+Zd&d&d&d&d&d&d&d&d&d, dd-Zdd.Z ddd0Z d1ddd2Z ddd3Z d/d&d4 dd5Z dd6Zd/d&d4 dd7Zdd8Zdd9Z dd:Zdddd;Zy&( TenantStorecpt}t|j|_t|j|_t|j |_t|j|_ t|j|_ tj|_|j!y)N)hours)rr tenancy_registry_database_registry_pathtenancy_databases_dir _tenant_dirmenu_asset_storage_dir_menu_assets_dirfiscal_document_storage_dir_fiscal_documents_dirr tenancy_session_duration_hours_session_duration threadingRLock_lock_ensure_storage)selfsettingss r7__init__zTenantStore.__init__s>"8#E#EF > >? $X%D%D E%)(*N*N%O"!*1X1X!Y__&  r6c:|jjjdd|jjdd|jjdd|j jdd|j 5}|jd|j|ddd|j|ddd|j|dd d|j|dd d |j|dd d|j|dd d|j|ddd|j|dd d|j|ddd|jd|jd|j|dddy#1swYyxYw)NTparentsexist_oka4* CREATE TABLE IF NOT EXISTS tenants ( id TEXT PRIMARY KEY, name TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, database_path TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS venues ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, name TEXT NOT NULL, address TEXT NOT NULL DEFAULT '', phone_number TEXT, whatsapp_number TEXT, created_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE TABLE IF NOT EXISTS users ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, name TEXT NOT NULL, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, phone_number TEXT, password_hash TEXT NOT NULL, role TEXT NOT NULL, permissions_json TEXT, assistant_scopes_json TEXT, created_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE TABLE IF NOT EXISTS tenant_modules ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, module_key TEXT NOT NULL, enabled INTEGER NOT NULL, plan_name TEXT NOT NULL, activated_at TEXT, expires_at TEXT, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE UNIQUE INDEX IF NOT EXISTS idx_tenant_modules_unique ON tenant_modules(tenant_id, module_key); CREATE TABLE IF NOT EXISTS sessions ( token TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, user_id TEXT NOT NULL, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE TABLE IF NOT EXISTS tenant_admin_push_recipients ( tenant_id TEXT NOT NULL, user_id TEXT NOT NULL, enabled INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, PRIMARY KEY (tenant_id, user_id), FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE TABLE IF NOT EXISTS tenant_llm_settings ( tenant_id TEXT NOT NULL, scope TEXT NOT NULL, config_json TEXT NOT NULL, updated_at TEXT NOT NULL, PRIMARY KEY (tenant_id, scope), FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE TABLE IF NOT EXISTS tenant_menu_assets ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, original_name TEXT NOT NULL, display_name TEXT NOT NULL, mime_type TEXT NOT NULL, kind TEXT NOT NULL, file_size_bytes INTEGER NOT NULL, storage_path TEXT NOT NULL, extracted_text TEXT NOT NULL DEFAULT '', analysis_text TEXT NOT NULL DEFAULT '', status TEXT NOT NULL, error_detail TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE INDEX IF NOT EXISTS idx_tenant_menu_assets_tenant ON tenant_menu_assets(tenant_id, created_at DESC); CREATE TABLE IF NOT EXISTS tenant_fiscal_documents ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, original_name TEXT NOT NULL, display_name TEXT NOT NULL, mime_type TEXT NOT NULL, kind TEXT NOT NULL, file_size_bytes INTEGER NOT NULL, file_hash TEXT, storage_path TEXT NOT NULL, document_type TEXT NOT NULL, document_number TEXT, document_date TEXT, supplier_name TEXT, total_amount REAL, currency TEXT NOT NULL DEFAULT 'EUR', summary_text TEXT NOT NULL DEFAULT '', extracted_text TEXT NOT NULL DEFAULT '', preview_text TEXT NOT NULL DEFAULT '', drive_file_id TEXT, drive_web_url TEXT, drive_uploaded_at TEXT, status TEXT NOT NULL, matching_status TEXT NOT NULL DEFAULT 'pending', review_status TEXT NOT NULL DEFAULT 'to_review', error_detail TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE INDEX IF NOT EXISTS idx_tenant_fiscal_documents_tenant ON tenant_fiscal_documents(tenant_id, created_at DESC); CREATE TABLE IF NOT EXISTS tenant_fiscal_document_items ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, document_id TEXT NOT NULL, line_index INTEGER NOT NULL, product_code TEXT, iso_code TEXT, description TEXT NOT NULL, category_code TEXT, unit_code TEXT, pack_count REAL, quantity REAL, gross_quantity REAL, tare_quantity REAL, net_quantity REAL, unit_price REAL, line_total REAL, vat_code TEXT, raw_row_text TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(document_id) REFERENCES tenant_fiscal_documents(id) ); CREATE INDEX IF NOT EXISTS idx_tenant_fiscal_document_items_document ON tenant_fiscal_document_items(tenant_id, document_id, line_index ASC); CREATE TABLE IF NOT EXISTS tenant_fiscal_document_settings ( tenant_id TEXT PRIMARY KEY, inbound_email TEXT, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id) ); CREATE TABLE IF NOT EXISTS tenant_fiscal_document_inbox_items ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, message_id TEXT NOT NULL, attachment_id TEXT NOT NULL, file_hash TEXT, subject TEXT NOT NULL DEFAULT '', sender TEXT, received_at TEXT, attachment_name TEXT NOT NULL, mime_type TEXT NOT NULL, sync_status TEXT NOT NULL, document_id TEXT, error_detail TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(document_id) REFERENCES tenant_fiscal_documents(id), UNIQUE(tenant_id, message_id, attachment_id) ); CREATE INDEX IF NOT EXISTS idx_tenant_fiscal_document_inbox_items_tenant ON tenant_fiscal_document_inbox_items(tenant_id, created_at DESC); CREATE TABLE IF NOT EXISTS assistant_threads ( id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, user_id TEXT NOT NULL, surface TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', state_json TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE INDEX IF NOT EXISTS idx_assistant_threads_lookup ON assistant_threads(tenant_id, user_id, surface, status, updated_at DESC); CREATE TABLE IF NOT EXISTS assistant_messages ( id TEXT PRIMARY KEY, thread_id TEXT NOT NULL, role TEXT NOT NULL, content TEXT NOT NULL, created_at TEXT NOT NULL, sort_index INTEGER NOT NULL, FOREIGN KEY(thread_id) REFERENCES assistant_threads(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_assistant_messages_thread ON assistant_messages(thread_id, sort_index ASC); CREATE TABLE IF NOT EXISTS assistant_runs ( id TEXT PRIMARY KEY, thread_id TEXT NOT NULL, tenant_id TEXT NOT NULL, user_id TEXT NOT NULL, surface TEXT NOT NULL, route TEXT NOT NULL, model TEXT NOT NULL, user_message TEXT NOT NULL, assistant_reply TEXT NOT NULL, trace_json TEXT NOT NULL, created_at TEXT NOT NULL, FOREIGN KEY(thread_id) REFERENCES assistant_threads(id) ON DELETE CASCADE, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE INDEX IF NOT EXISTS idx_assistant_runs_thread ON assistant_runs(thread_id, created_at DESC); usersrTEXTrtenant_fiscal_documentsrrzTEXT NOT NULL DEFAULT ''rrr"tenant_fiscal_document_inbox_itemsassistant_threadsrz CREATE INDEX IF NOT EXISTS idx_tenant_fiscal_documents_hash ON tenant_fiscal_documents(tenant_id, file_hash) z CREATE INDEX IF NOT EXISTS idx_tenant_fiscal_document_inbox_items_hash ON tenant_fiscal_document_inbox_items(tenant_id, file_hash) ) r1parentmkdirr3r5r7_connect_registry executescript_ensure_registry_columnexecute_ensure_super_admin_accountr> connections r7r=zTenantStore._ensure_storage%s ""(((E td; ##D4#@ ""(((E  # # %J 9  $ $qs h  ( (W>PRX Y  ( (W>UW] ^  ( (5NP[]c d  ( (5NP^`z {  ( (5NP_ag h  ( (5NP_ag h  ( (5NPcek l  ( (5Y[fhn o  ( (5H,X^ _          , ,Z 8UJ 9J 9J 9s C9FFc`|jd|j|ddd|j|ddd|j|ddd|j|ddd|j|dd d |j|dd d|j|dd d |j|dd d|j|ddd|j|ddd|j|ddd|j|ddd|j|ddd|j|ddd|jdjDchc]}|d }}d|v}|j|ddd|j|ddd|j|ddd|j|ddd|j|dd d|j|dd!d|j|dd"d|jd#|jd$|jd%|s|jd&|jd'|jd(|jd)ttf|jd*ttf|j ||j ycc}w)+Nai CREATE TABLE IF NOT EXISTS tenant_profile ( tenant_id TEXT PRIMARY KEY, tenant_name TEXT NOT NULL, tenant_slug TEXT NOT NULL UNIQUE, venue_name TEXT NOT NULL, address TEXT NOT NULL DEFAULT '', phone_number TEXT, whatsapp_number TEXT, admin_name TEXT NOT NULL, admin_username TEXT NOT NULL, admin_email TEXT NOT NULL, admin_phone_number TEXT, password_hash TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_module_settings ( module_key TEXT PRIMARY KEY, enabled INTEGER NOT NULL, plan_name TEXT NOT NULL, activated_at TEXT, expires_at TEXT ); CREATE TABLE IF NOT EXISTS tenant_timeclock_entries ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, user_name TEXT, username TEXT, user_email TEXT, started_at TEXT NOT NULL, ended_at TEXT, duration_seconds INTEGER, started_source TEXT NOT NULL DEFAULT 'portal', ended_source TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_tenant_timeclock_user_started ON tenant_timeclock_entries(user_id, started_at DESC); CREATE INDEX IF NOT EXISTS idx_tenant_timeclock_active ON tenant_timeclock_entries(ended_at, started_at DESC); CREATE TABLE IF NOT EXISTS tenant_reports ( id TEXT PRIMARY KEY, reporter_user_id TEXT NOT NULL, reporter_name TEXT, reporter_username TEXT, reporter_email TEXT, title TEXT NOT NULL, description TEXT NOT NULL, category TEXT NOT NULL, location TEXT, priority TEXT NOT NULL DEFAULT 'normal', status TEXT NOT NULL DEFAULT 'new', admin_note TEXT, status_updated_by_user_id TEXT, status_updated_by_name TEXT, resolved_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_tenant_reports_created ON tenant_reports(created_at DESC); CREATE INDEX IF NOT EXISTS idx_tenant_reports_status ON tenant_reports(status, updated_at DESC); CREATE INDEX IF NOT EXISTS idx_tenant_reports_reporter ON tenant_reports(reporter_user_id, created_at DESC); CREATE TABLE IF NOT EXISTS tenant_push_subscriptions ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, endpoint TEXT NOT NULL UNIQUE, p256dh TEXT NOT NULL, auth TEXT NOT NULL, user_agent TEXT, enabled INTEGER NOT NULL DEFAULT 1, last_error TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_tenant_push_subscriptions_user ON tenant_push_subscriptions(user_id, enabled, updated_at DESC); CREATE TABLE IF NOT EXISTS tenant_inventory_warehouses ( id TEXT PRIMARY KEY, name TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_inventory_stock_items ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, product_id INTEGER, product_name TEXT NOT NULL, product_lookup TEXT NOT NULL, supplier_name TEXT NOT NULL, supplier_lookup TEXT NOT NULL, total_equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_inventory_warehouses(id) ON DELETE CASCADE, UNIQUE (warehouse_id, product_lookup, supplier_lookup) ); CREATE TABLE IF NOT EXISTS tenant_inventory_stock_lots ( id TEXT PRIMARY KEY, item_id TEXT NOT NULL, lot_code TEXT NOT NULL, lot_lookup TEXT NOT NULL, quantity REAL NOT NULL DEFAULT 0, units_per_pack REAL, equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (item_id) REFERENCES tenant_inventory_stock_items(id) ON DELETE CASCADE, UNIQUE (item_id, lot_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_warehouse_name ON tenant_inventory_warehouses(name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_items_warehouse ON tenant_inventory_stock_items(warehouse_id, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_lots_item ON tenant_inventory_stock_lots(item_id, lot_code ASC); CREATE TABLE IF NOT EXISTS tenant_inventory_sessions ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, warehouse_name TEXT NOT NULL, inventory_date TEXT NOT NULL, label TEXT, created_by_user_id TEXT, created_by_name TEXT, total_products INTEGER NOT NULL DEFAULT 0, total_equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_inventory_warehouses(id) ON DELETE CASCADE, UNIQUE (warehouse_id, inventory_date) ); CREATE TABLE IF NOT EXISTS tenant_inventory_session_items ( id TEXT PRIMARY KEY, session_id TEXT NOT NULL, product_id INTEGER, product_name TEXT NOT NULL, product_lookup TEXT NOT NULL, supplier_name TEXT NOT NULL, supplier_lookup TEXT NOT NULL, total_equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (session_id) REFERENCES tenant_inventory_sessions(id) ON DELETE CASCADE, UNIQUE (session_id, product_lookup, supplier_lookup) ); CREATE TABLE IF NOT EXISTS tenant_inventory_session_lots ( id TEXT PRIMARY KEY, session_item_id TEXT NOT NULL, lot_code TEXT NOT NULL, lot_lookup TEXT NOT NULL, quantity REAL NOT NULL DEFAULT 0, units_per_pack REAL, equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (session_item_id) REFERENCES tenant_inventory_session_items(id) ON DELETE CASCADE, UNIQUE (session_item_id, lot_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_session_warehouse_date ON tenant_inventory_sessions(warehouse_id, inventory_date DESC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_session_items_session ON tenant_inventory_session_items(session_id, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_session_lots_item ON tenant_inventory_session_lots(session_item_id, lot_code ASC); CREATE TABLE IF NOT EXISTS tenant_inventory_movements ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, product_id INTEGER, product_name TEXT NOT NULL, product_lookup TEXT NOT NULL, supplier_name TEXT NOT NULL, supplier_lookup TEXT NOT NULL, lot_code TEXT NOT NULL, lot_lookup TEXT NOT NULL, quantity REAL NOT NULL DEFAULT 0, units_per_pack REAL, equivalent_units REAL NOT NULL DEFAULT 0, movement_kind TEXT NOT NULL DEFAULT 'in', source_type TEXT NOT NULL DEFAULT 'manual_stock_add', source_label TEXT, occurred_at TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_inventory_warehouses(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_movements_warehouse_occurred ON tenant_inventory_movements(warehouse_id, occurred_at DESC); CREATE TABLE IF NOT EXISTS tenant_inventory_consumption_product_stats ( id TEXT PRIMARY KEY, product_id INTEGER, product_lookup TEXT NOT NULL, supplier_lookup TEXT NOT NULL, product_name TEXT NOT NULL, supplier_name TEXT NOT NULL, total_consumed_units REAL NOT NULL DEFAULT 0, workdays_count INTEGER NOT NULL DEFAULT 0, consumed_days_count INTEGER NOT NULL DEFAULT 0, average_daily_consumed_units REAL NOT NULL DEFAULT 0, average_consumed_units_on_consumption_days REAL NOT NULL DEFAULT 0, movement_count INTEGER NOT NULL DEFAULT 0, first_consumption_date TEXT, last_consumption_date TEXT, calculation_source TEXT NOT NULL DEFAULT 'manual_consumption_movements', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE (product_lookup, supplier_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_consumption_product_stats_avg ON tenant_inventory_consumption_product_stats(average_daily_consumed_units DESC, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_consumption_product_stats_last ON tenant_inventory_consumption_product_stats(last_consumption_date DESC, product_name ASC); CREATE TABLE IF NOT EXISTS tenant_inventory_daily_consumptions ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, warehouse_name TEXT NOT NULL, consumption_date TEXT NOT NULL, period_start_date TEXT NOT NULL, period_end_date TEXT NOT NULL, period_days REAL NOT NULL DEFAULT 0, start_session_id TEXT, end_session_id TEXT NOT NULL, product_lookup TEXT NOT NULL, supplier_lookup TEXT NOT NULL, product_name TEXT NOT NULL, supplier_name TEXT NOT NULL, opening_units REAL NOT NULL DEFAULT 0, incoming_units REAL NOT NULL DEFAULT 0, outgoing_transfer_units REAL NOT NULL DEFAULT 0, closing_units REAL NOT NULL DEFAULT 0, consumed_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_inventory_warehouses(id) ON DELETE CASCADE, FOREIGN KEY (start_session_id) REFERENCES tenant_inventory_sessions(id) ON DELETE SET NULL, FOREIGN KEY (end_session_id) REFERENCES tenant_inventory_sessions(id) ON DELETE CASCADE, UNIQUE (warehouse_id, consumption_date, product_lookup, supplier_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_daily_consumptions_date ON tenant_inventory_daily_consumptions(consumption_date DESC, warehouse_name ASC, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_daily_consumptions_warehouse_date ON tenant_inventory_daily_consumptions(warehouse_id, consumption_date DESC); CREATE TABLE IF NOT EXISTS tenant_inventory_estimated_consumptions ( id TEXT PRIMARY KEY, consumption_date TEXT NOT NULL, period_start_date TEXT NOT NULL, period_end_date TEXT NOT NULL, period_days REAL NOT NULL DEFAULT 0, product_id INTEGER, product_lookup TEXT NOT NULL, supplier_lookup TEXT NOT NULL, product_name TEXT NOT NULL, supplier_name TEXT NOT NULL, warehouse_count INTEGER NOT NULL DEFAULT 0, opening_units REAL NOT NULL DEFAULT 0, incoming_units REAL NOT NULL DEFAULT 0, closing_units REAL NOT NULL DEFAULT 0, consumed_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE (consumption_date, product_lookup, supplier_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_estimated_consumptions_date ON tenant_inventory_estimated_consumptions(consumption_date DESC, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_estimated_consumptions_product ON tenant_inventory_estimated_consumptions(product_lookup, supplier_lookup, consumption_date DESC); CREATE TABLE IF NOT EXISTS tenant_inventory_snapshots ( id TEXT PRIMARY KEY, snapshot_date TEXT NOT NULL UNIQUE, label TEXT, created_by_user_id TEXT, created_by_name TEXT, total_warehouses INTEGER NOT NULL DEFAULT 0, total_products INTEGER NOT NULL DEFAULT 0, total_equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_inventory_snapshot_items ( id TEXT PRIMARY KEY, snapshot_id TEXT NOT NULL, warehouse_id TEXT, warehouse_name TEXT NOT NULL, product_lookup TEXT NOT NULL, supplier_lookup TEXT NOT NULL, product_name TEXT NOT NULL, supplier_name TEXT NOT NULL, total_equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (snapshot_id) REFERENCES tenant_inventory_snapshots(id) ON DELETE CASCADE, UNIQUE (snapshot_id, warehouse_id, product_lookup, supplier_lookup) ); CREATE TABLE IF NOT EXISTS tenant_inventory_snapshot_lots ( id TEXT PRIMARY KEY, snapshot_item_id TEXT NOT NULL, lot_code TEXT NOT NULL, lot_lookup TEXT NOT NULL, quantity REAL NOT NULL DEFAULT 0, units_per_pack REAL, equivalent_units REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (snapshot_item_id) REFERENCES tenant_inventory_snapshot_items(id) ON DELETE CASCADE, UNIQUE (snapshot_item_id, lot_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_snapshot_date ON tenant_inventory_snapshots(snapshot_date DESC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_snapshot_items_snapshot ON tenant_inventory_snapshot_items(snapshot_id, warehouse_name ASC, product_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_inventory_snapshot_lots_item ON tenant_inventory_snapshot_lots(snapshot_item_id, lot_code ASC); CREATE TABLE IF NOT EXISTS tenant_homemade_recipes ( id TEXT PRIMARY KEY, name TEXT NOT NULL, name_lookup TEXT NOT NULL UNIQUE, measurement_unit TEXT NOT NULL DEFAULT 'ml', usage_scope TEXT NOT NULL DEFAULT 'both', notes TEXT, yield_ingredient_name TEXT, yield_ingredient_lookup TEXT, yield_input_amount REAL, yield_input_unit TEXT, yield_output_ml REAL, total_parts REAL NOT NULL DEFAULT 0, ingredient_count INTEGER NOT NULL DEFAULT 0, preparation_date TEXT, preparation_drive_file_id TEXT, preparation_drive_web_url TEXT, preparation_drive_updated_at TEXT, created_by_user_id TEXT, created_by_name TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_homemade_recipe_ingredients ( id TEXT PRIMARY KEY, recipe_id TEXT NOT NULL, ingredient_name TEXT NOT NULL, ingredient_lookup TEXT NOT NULL, linked_recipe_id TEXT, part_amount REAL NOT NULL DEFAULT 0, measurement_unit TEXT NOT NULL DEFAULT 'ml', share_ratio REAL NOT NULL DEFAULT 0, percentage REAL NOT NULL DEFAULT 0, sort_order INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (recipe_id) REFERENCES tenant_homemade_recipes(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_recipe_name ON tenant_homemade_recipes(name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_ingredient_recipe ON tenant_homemade_recipe_ingredients(recipe_id, sort_order ASC, ingredient_name ASC); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_warehouses ( id TEXT PRIMARY KEY, name TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_items ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, recipe_id TEXT NOT NULL, recipe_name TEXT NOT NULL, recipe_lookup TEXT NOT NULL, measurement_unit TEXT NOT NULL DEFAULT 'pz', quantity REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_homemade_stock_warehouses(id) ON DELETE CASCADE, FOREIGN KEY (recipe_id) REFERENCES tenant_homemade_recipes(id) ON DELETE CASCADE, UNIQUE (warehouse_id, recipe_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_warehouse_name ON tenant_homemade_stock_warehouses(name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_items_warehouse ON tenant_homemade_stock_items(warehouse_id, recipe_name ASC); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_sessions ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, warehouse_name TEXT NOT NULL, inventory_date TEXT NOT NULL, label TEXT, created_by_user_id TEXT, created_by_name TEXT, total_recipes INTEGER NOT NULL DEFAULT 0, total_quantity REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_homemade_stock_warehouses(id) ON DELETE CASCADE, UNIQUE (warehouse_id, inventory_date) ); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_session_items ( id TEXT PRIMARY KEY, session_id TEXT NOT NULL, recipe_id TEXT NOT NULL, recipe_name TEXT NOT NULL, recipe_lookup TEXT NOT NULL, measurement_unit TEXT NOT NULL DEFAULT 'pz', quantity REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (session_id) REFERENCES tenant_homemade_stock_sessions(id) ON DELETE CASCADE, FOREIGN KEY (recipe_id) REFERENCES tenant_homemade_recipes(id) ON DELETE CASCADE, UNIQUE (session_id, recipe_lookup) ); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_session_warehouse_date ON tenant_homemade_stock_sessions(warehouse_id, inventory_date DESC); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_session_items_session ON tenant_homemade_stock_session_items(session_id, recipe_name ASC); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_settings ( id TEXT PRIMARY KEY, minimum_stock_days REAL NOT NULL DEFAULT 0, bar_calendar_json TEXT, restaurant_calendar_json TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tenant_homemade_stock_movements ( id TEXT PRIMARY KEY, warehouse_id TEXT NOT NULL, warehouse_name TEXT NOT NULL, recipe_id TEXT NOT NULL, recipe_name TEXT NOT NULL, recipe_lookup TEXT NOT NULL, measurement_unit TEXT NOT NULL DEFAULT 'pz', quantity_before REAL NOT NULL DEFAULT 0, quantity_after REAL NOT NULL DEFAULT 0, delta_quantity REAL NOT NULL DEFAULT 0, added_quantity REAL NOT NULL DEFAULT 0, consumed_quantity REAL NOT NULL DEFAULT 0, movement_type TEXT NOT NULL DEFAULT 'stock_set', occurred_at TEXT NOT NULL, created_by_user_id TEXT, created_by_name TEXT, created_at TEXT NOT NULL, FOREIGN KEY (warehouse_id) REFERENCES tenant_homemade_stock_warehouses(id) ON DELETE CASCADE, FOREIGN KEY (recipe_id) REFERENCES tenant_homemade_recipes(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_movements_recipe_date ON tenant_homemade_stock_movements(recipe_lookup, occurred_at DESC); CREATE INDEX IF NOT EXISTS idx_tenant_homemade_stock_movements_warehouse_date ON tenant_homemade_stock_movements(warehouse_id, occurred_at DESC); CREATE TABLE IF NOT EXISTS tenant_tips_roster_entries ( id TEXT PRIMARY KEY, area TEXT NOT NULL, staff_name TEXT NOT NULL, staff_lookup TEXT NOT NULL, score REAL NOT NULL DEFAULT 0, sort_order INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE (area, staff_lookup) ); CREATE TABLE IF NOT EXISTS tenant_tips_runs ( id TEXT PRIMARY KEY, area TEXT NOT NULL, tip_date TEXT NOT NULL, total_tip_amount REAL NOT NULL DEFAULT 0, tip_pos_amount REAL NOT NULL DEFAULT 0, tip_pos_effective_amount REAL NOT NULL DEFAULT 0, tip_cash_amount REAL NOT NULL DEFAULT 0, total_score REAL NOT NULL DEFAULT 0, historical_total_amount REAL NOT NULL DEFAULT 0, payable_total_amount REAL NOT NULL DEFAULT 0, present_staff_count INTEGER NOT NULL DEFAULT 0, absent_staff_count INTEGER NOT NULL DEFAULT 0, payout_status TEXT NOT NULL DEFAULT 'pending', settled_at TEXT, settled_by_user_id TEXT, settled_by_name TEXT, saved_by_user_id TEXT, saved_by_name TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE (area, tip_date) ); CREATE TABLE IF NOT EXISTS tenant_tips_run_entries ( id TEXT PRIMARY KEY, run_id TEXT NOT NULL, area TEXT NOT NULL, staff_name TEXT NOT NULL, staff_lookup TEXT NOT NULL, score REAL NOT NULL DEFAULT 0, is_present INTEGER NOT NULL DEFAULT 0, amount_today REAL NOT NULL DEFAULT 0, historical_amount REAL NOT NULL DEFAULT 0, total_amount REAL NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (run_id) REFERENCES tenant_tips_runs(id) ON DELETE CASCADE, UNIQUE (run_id, staff_lookup) ); CREATE TABLE IF NOT EXISTS tenant_tips_run_history_sources ( id TEXT PRIMARY KEY, run_id TEXT NOT NULL, source_run_id TEXT NOT NULL, source_tip_date TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (run_id) REFERENCES tenant_tips_runs(id) ON DELETE CASCADE, FOREIGN KEY (source_run_id) REFERENCES tenant_tips_runs(id) ON DELETE CASCADE, UNIQUE (run_id, source_run_id) ); CREATE INDEX IF NOT EXISTS idx_tenant_tips_roster_area ON tenant_tips_roster_entries(area, sort_order ASC, staff_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_tips_runs_area_date ON tenant_tips_runs(area, tip_date DESC, updated_at DESC); CREATE INDEX IF NOT EXISTS idx_tenant_tips_run_entries_run ON tenant_tips_run_entries(run_id, staff_name ASC); CREATE INDEX IF NOT EXISTS idx_tenant_tips_history_sources_run ON tenant_tips_run_history_sources(run_id, source_tip_date DESC); tenant_homemade_recipespreparation_daterFryzTEXT NOT NULL DEFAULT 'both'ryield_ingredient_lookuprREALr{rpreparation_drive_file_idpreparation_drive_web_urlpreparation_drive_updated_at"tenant_homemade_recipe_ingredientsrtrzTEXT NOT NULL DEFAULT 'ml'tenant_homemade_stock_settingsbar_calendar_jsonrestaurant_calendar_jsonz#PRAGMA table_info(tenant_tips_runs)r'tip_pos_effective_amounttenant_tips_runstip_pos_amountzREAL NOT NULL DEFAULT 0tip_cash_amount payout_statuszTEXT NOT NULL DEFAULT 'pending' settled_atsettled_by_user_idsettled_by_namez UPDATE tenant_tips_runs SET payout_status = 'pending' WHERE payout_status IS NULL OR trim(payout_status) = '' a UPDATE tenant_tips_runs SET payout_status = 'carried' WHERE payout_status = 'pending' AND id IN ( SELECT DISTINCT source_run_id FROM tenant_tips_run_history_sources ) z UPDATE tenant_tips_runs SET tip_cash_amount = total_tip_amount WHERE COALESCE(tip_pos_amount, 0) = 0 AND COALESCE(tip_cash_amount, 0) = 0 AND COALESCE(total_tip_amount, 0) > 0 z UPDATE tenant_tips_runs SET tip_pos_effective_amount = tip_pos_amount WHERE COALESCE(tip_pos_effective_amount, 0) = 0 AND COALESCE(tip_pos_amount, 0) > 0 a UPDATE tenant_homemade_recipe_ingredients SET measurement_unit = ( SELECT CASE WHEN lower(COALESCE(recipes.measurement_unit, 'ml')) = 'g' THEN 'g' ELSE 'ml' END FROM tenant_homemade_recipes AS recipes WHERE recipes.id = tenant_homemade_recipe_ingredients.recipe_id ) WHERE measurement_unit IS NULL OR trim(measurement_unit) = '' z UPDATE tenant_homemade_recipes SET usage_scope = 'both' WHERE usage_scope IS NULL OR trim(usage_scope) = '' OR lower(trim(usage_scope)) NOT IN ('bar', 'restaurant', 'both') z UPDATE tenant_homemade_stock_items SET measurement_unit = ? WHERE measurement_unit IS NULL OR trim(measurement_unit) != ? z UPDATE tenant_homemade_stock_session_items SET measurement_unit = ? WHERE measurement_unit IS NULL OR trim(measurement_unit) != ? )rM_ensure_tenant_columnrOfetchallHOMEMADE_STOCK_UNIT'_ensure_inventory_catalog_sync_triggerscommit)r>rRrowexisting_tips_run_columnshad_tip_pos_effective_amounts r7_ensure_tenant_database_schemaz*TenantStore._ensure_tenant_database_schema7s  D F N ""  %    ""  %  *  ""  % #   ""  % %   ""  %   ""  %    ""  %    ""  % '   ""  % '   ""  % *   ""  0    ""  0  (  ""  ,    ""  , &   "))*OPYY[%  K% !% (BE^'^$ ""    %  ""   & %  ""    %  ""    -  ""      ""     ""              ,             !"5 6     !"5 6   44Z@w% s J+c|jd|djDchc]}|d }}||vr;|jd|d|d||j|jyycc}wNPRAGMA table_info()r'z ALTER TABLE z ADD COLUMN  rOrhrkr>rR table_name column_name column_sqlrlexisting_columnss r7rNz#TenantStore._ensure_registry_columnPs")),>zl!*LMVVX  K   . .   j\k]RST^S_` a         /  s A4c|jd|djDchc]}|d }}||vr+|jd|d|d||jyycc}wrqrurvs r7rgz!TenantStore._ensure_tenant_columnZs")),>zl!*LMVVX  K   . .   j\k]RST^S_` a     /  s A$c|j|dsy|j|d}d|vsd|vry|jdy)Nordini_productsrSrTa_ CREATE TRIGGER IF NOT EXISTS trg_ordini_products_sync_inventory_names AFTER UPDATE OF product_name, supplier_name ON ordini_products WHEN COALESCE(OLD.product_name, '') <> COALESCE(NEW.product_name, '') OR COALESCE(OLD.supplier_name, '') <> COALESCE(NEW.supplier_name, '') BEGIN UPDATE tenant_inventory_stock_items SET product_name = trim(COALESCE(NEW.product_name, '')), product_lookup = lower(trim(COALESCE(NEW.product_name, ''))), supplier_name = trim(COALESCE(NEW.supplier_name, '')), supplier_lookup = lower(trim(COALESCE(NEW.supplier_name, ''))), updated_at = datetime('now') WHERE product_id = NEW.id; UPDATE tenant_inventory_session_items SET product_name = trim(COALESCE(NEW.product_name, '')), product_lookup = lower(trim(COALESCE(NEW.product_name, ''))), supplier_name = trim(COALESCE(NEW.supplier_name, '')), supplier_lookup = lower(trim(COALESCE(NEW.supplier_name, ''))), updated_at = datetime('now') WHERE product_id = NEW.id; UPDATE tenant_inventory_movements SET product_name = trim(COALESCE(NEW.product_name, '')), product_lookup = lower(trim(COALESCE(NEW.product_name, ''))), supplier_name = trim(COALESCE(NEW.supplier_name, '')), supplier_lookup = lower(trim(COALESCE(NEW.supplier_name, ''))), updated_at = datetime('now') WHERE product_id = NEW.id; UPDATE tenant_inventory_daily_consumptions SET product_name = trim(COALESCE(NEW.product_name, '')), product_lookup = lower(trim(COALESCE(NEW.product_name, ''))), supplier_name = trim(COALESCE(NEW.supplier_name, '')), supplier_lookup = lower(trim(COALESCE(NEW.supplier_name, ''))), updated_at = datetime('now') WHERE product_name = OLD.product_name AND supplier_name = OLD.supplier_name; UPDATE tenant_inventory_snapshot_items SET product_name = trim(COALESCE(NEW.product_name, '')), product_lookup = lower(trim(COALESCE(NEW.product_name, ''))), supplier_name = trim(COALESCE(NEW.supplier_name, '')), supplier_lookup = lower(trim(COALESCE(NEW.supplier_name, ''))), updated_at = datetime('now') WHERE product_name = OLD.product_name AND supplier_name = OLD.supplier_name; END; )_tenant_table_exists_tenant_table_columnsrM)r>rRproduct_columnss r7rjz3TenantStore._ensure_inventory_catalog_sync_triggerscsN((5FG 44ZARS  0O?4Z   5 7 r6c# Ktj|j}tj|_|j d|j d ||j y#|j wxYww)NPRAGMA foreign_keys = ON;zPRAGMA journal_mode = WAL;)sqlite3connectr1Row row_factoryrOcloserQs r7rLzTenantStore._connect_registrysg__T%8%89 !( 6778      J   sABA/B/BBc#HKt|}|jjddtj|}tj |_|jd|j| ||jy#|jwxYww)NTrBr) r rJrKrrrrrOror)r>rpathrRs r7_connect_tenant_databasez$TenantStore._connect_tenant_databasesM" $6__T* !( 67 ++J7      J   sA5B"8B <B" BB"c T|jd|||||||| | | | | |tfy)Na INSERT OR REPLACE INTO tenant_profile ( tenant_id, tenant_name, tenant_slug, venue_name, address, phone_number, whatsapp_number, admin_name, admin_username, admin_email, admin_phone_number, password_hash, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) )rOr;)r>rRrrr venue_namerr r r admin_username admin_emailadmin_phone_number password_hashrs r7_upsert_tenant_profilez"TenantStore._upsert_tenant_profilesJ$  &"  '# r6ct}t|jtdz }|j dt fj }|#|j dt tt||f|j dtfj }|"|j dtt t|f|j dtfj }|.|j dtt dttd |fd }d}n3|d xsd }|d xsd}|j d tttf|j|t tttd dd|ttd|||j|5} | j d| jdddy#1swYyxYw)N.sqlite3+SELECT id FROM tenants WHERE id = ? LIMIT 1z INSERT INTO tenants (id, name, slug, database_path, created_at) VALUES (?, ?, ?, ?, ?) z*SELECT id FROM venues WHERE id = ? LIMIT 1z INSERT INTO venues (id, tenant_id, name, address, phone_number, whatsapp_number, created_at) VALUES (?, ?, ?, '', NULL, NULL, ?) z>SELECT id, password_hash, name FROM users WHERE id = ? LIMIT 1z INSERT INTO users (id, tenant_id, name, username, email, phone_number, password_hash, role, created_at) VALUES (?, ?, ?, ?, ?, NULL, '', ?, ?) Super Adminrrirr'z5UPDATE users SET username = ?, email = ? WHERE id = ?rrrrrrr r r rrrrrz"DELETE FROM tenant_module_settings)r;rjr3SUPER_ADMIN_TENANT_SLUGrOSUPER_ADMIN_TENANT_IDfetchoneSUPER_ADMIN_TENANT_NAMESUPER_ADMIN_VENUE_IDSUPER_ADMIN_USER_IDSUPER_ADMIN_USERNAMESUPER_ADMIN_EMAIL_create_tenant_databaserrk) r>rRrr tenant_exists venue_exists user_existsrr tenant_connections r7rPz'TenantStore._ensure_super_admin_accountsZ D,,2I1J(/SST "** 9 " $  (*      *++!  ")) 8 ! #  (*       ))+  !(( L "  (*       ()!(%!  M&J'8>BM$V,= J   G%'8:MN  $$'+//. !/)#'! %  * *= 9 '=N  % %&J K  $ $ & ' ' 's "F88Gc@|j|5}|j||||||||| | | | | |tD]@}|jdk(rdnd}|rdnd}|j d|j |||r|nddfB|j dddy#1swYyxYw)N rrrrrr r r rrrrractivermrPlatform ActivePlanneda7 INSERT OR REPLACE INTO tenant_module_settings ( module_key, enabled, plan_name, activated_at, expires_at ) VALUES (?, ?, ?, ?, ?) )rrrrrOkeyrk)r>rrrrrrr r r rrrrrrRmoduleenabled plan_names r7rz#TenantStore._create_tenant_databaseKs$ * *= 9( Z  ' '#''%) /%-'#5+% ( ") %}}8!a18-i "" !&- 4  ,    Q( ( ( s A9BBc tdid|dd|dd|dd|dd|dd|dd|dd|dd |d d |d d |d d |d d t|d d|jvr|dnddt|d d|jvr|dndd|jvr|dndd|dd|dS)Nrrrrrrrr1rr rrrrr+rrrr5)rrrrr>rls r7_session_identity_from_rowz&TenantStore._session_identity_from_rows[ g, +& M* M*  /0  !!34  !!34  N <( _ +& V 2#f+ZlpspxpxpzZzsCU?VAEF <F +=+K&'QU0G388:0U+,[_ &o.' (<()  r6ct|d|d|d|d|d|dt|d|d|d xsd |d xsd |d |d |d|dS)Nrrrrrrrrrrirrrrr)rrrrrrrrrrrrrr)rrrs r7_menu_asset_from_rowz TenantStore._menu_asset_from_rows4y+&o.^,+&V$5 67^,/06Bo.4"x=^,<(<(  r6c |d}td id|dd|dd|dd|dd|dd|ddt|dd |d d |d d |d d |d d |d d|dd| t|ndd|dxsdd|dxsdd|dxsdd|dxsdd|dd|dd|dd|dd|dxsdd|dxsdd|dd|dd|dS)!NrrrrrrrrrrrrrrTrEURrrirrrrrrrpendingr to_reviewrrrr5)rrr)r>rlrs r7_fiscal_document_from_rowz%TenantStore._fiscal_document_from_rows>* # 4y +& o. ^,  +&  V   $5 67 +& ^, o.  12 o. o. 1=0H|,d _- ^,2! "/06B# $^,2% &o.' (o.) *""56+ ,x=- . 12?i/ 0o.=+1 2^,3 4<(5 6<(7  r6c dd}tdid|dd|dd|ddt|dd|dd|dd|dxsd d |d d |d d ||d d ||d d||dd||dd||dd||dd||dd|dd|dxsd d|dd|dS)Nc | t|SdSr2rrZs r7_float_or_nonezGTenantStore._fiscal_document_line_item_from_row.._float_or_nones#(#45< >$ >r6rrrrrOrrrirrrrBrrrrrrrrr)rTobjectreturnrJr5)rr)r>rlrs r7#_fiscal_document_line_item_from_rowz/TenantStore._fiscal_document_line_item_from_rowso ?, 4y +& M* 3|,-  ^,  _  M*0b o. +& &c,&78 $C O4 *#.>*?@ )_)=> (N(;< &c,&78 &c,&78! "_# $^,2% &<(' (<()  r6c d|jxsdjj|jxsdjj|jxsdj|j xsdjj|j tt|j dnd|jxsdjjfS)Nsemanticrir) rrQcasefoldrrrTrroundrr)r>records r7_fiscal_document_dedup_keyz&TenantStore._fiscal_document_dedup_keys   ! ! 'R . . 0 9 9 ;  # # )r 0 0 2 ; ; =  ! ! 'R . . 0  ! ! 'R . . 0 9 9 ;4:4G4G4SE%++,a 0Y]  ! ! 'R . . 0 9 9 ;  r6c |jxsdj}|jxsdjj}|r|rd||fS|jxsdj}|rd|fSd|j fS)Nrimessagehashr)rrQr rrr)r>rrr rs r7 _fiscal_document_inbox_dedup_keyz,TenantStore._fiscal_document_inbox_dedup_keys''-2446 !117R>>@IIK /z?; ;%%+224 I& &fii  r6c|jxsdj}|dk(r|jxsdjry|dk(ry|jxsdjry|dk(ry|dk(ry y ) Nriimportedrrmr unsupportedrIerrorr )r rQrr)r>rrs r7%_fiscal_document_inbox_dedup_priorityz1TenantStore._fiscal_document_inbox_dedup_priority sz$$*113 Z V%7%7%=2$D$D$F z !$" + + - } $ w r6c t|d|d|d|d|dd|jvr|dnd|d|d S) Nrrr1rrrrr)rrr1rrrrr)rrrs r7_assistant_thread_from_rowz&TenantStore._assistant_thread_from_row sZ$4y+& N Nx=,8CHHJ,Fs<(D<(<(  r6c Zt|d|d|d|d|dt|dS)Nrr"rr#rr$rr"rr#rr$)r!rrs r7_assistant_message_from_rowz'TenantStore._assistant_message_from_row sA%4y+&V N<(3|,-   r6cpt|d|d|d|d|d|d|d|d|d |d |d  S) Nrr"rr1rr'r(r)r*r+r) rr"rr1rr'r(r)r*r+r)r&rs r7_assistant_run_from_rowz#TenantStore._assistant_run_from_row% sc!4y+&+& N Ng,g,^, 12<(<(  r6c0t|d|d|dS)Nrrrrrr)rrs r7"_fiscal_document_settings_from_rowz.TenantStore._fiscal_document_settings_from_row4 s'++&o.<(  r6ct|d|d|d|d|d|dxsd|d|d |d |d |d |d |d|d|dS)Nrrrrrrrirrr rr rrrr)rrrrrrrrr rr rrrr)rrs r7$_fiscal_document_inbox_item_from_rowz0TenantStore._fiscal_document_inbox_item_from_row; s,4y+&<(o.+& N(bx=M* 12+&M*M*^,<(<(  r6c|d}t|d|d|d|d|d|d|d| t|nd|d xsd |d |d |d |d S)Nrrr1rr rr rrportalrr\rr) rr1rr rr rrrrr\rr)r r)r>rlrs r7_timeclock_entry_from_rowz%TenantStore._timeclock_entry_from_rowN s12#4y N+&_<(<(_6F6RS!12X\/0rRrr1rrrs r7_create_sessionzTenantStore._create_sessionu sv%%b)Z i$"8"88CCE  Iw J ?   &&r6c\t|}|jd||fjS)Nat SELECT users.*, tenants.name AS tenant_name, tenants.slug AS tenant_slug, tenants.database_path AS database_path FROM users JOIN tenants ON tenants.id = users.tenant_id WHERE lower(users.email) = ? OR lower(users.username) = ? LIMIT 1 )r[rOrr>rRrrUs r7_lookup_user_for_loginz"TenantStore._lookup_user_for_login s7&z2 !!  $   (* r6c Vt|}t|jd||||fS)Na SELECT users.*, tenants.name AS tenant_name, tenants.slug AS tenant_slug, tenants.database_path AS database_path FROM users JOIN tenants ON tenants.id = users.tenant_id WHERE lower(users.email) = ? OR lower(users.username) = ? ORDER BY CASE WHEN lower(users.username) = ? THEN 0 WHEN lower(users.email) = ? THEN 1 ELSE 2 END, CASE WHEN users.role = 'super_admin' THEN 0 ELSE 1 END, users.created_at ASC )r[rrOrs r7_lookup_login_candidatesz$TenantStore._lookup_login_candidates s:&z2    $ZZ@'   r6c|jd|fj}|td|dt|jd|f}t|jd|f}t|jd|f}||||fS)Nz"SELECT * FROM tenants WHERE id = ?Tenant non trovatoz@SELECT * FROM venues WHERE tenant_id = ? ORDER BY created_at ASCz?SELECT * FROM users WHERE tenant_id = ? ORDER BY created_at ASCzVSELECT * FROM tenant_modules WHERE tenant_id = ? ORDER BY enabled DESC, module_key ASC)rOrKeyErrorr)r>rRrtenantvenuesrEmoduless r7 _context_rowszTenantStore._context_rows s##$H9,W``b >WYK|<= =j(()knwmyz{Z''(ilukwxy   h    vug--r6c|jj|jj|jr|jjnd|jr|jjnd|j j|j j|jjdS)N)rrr r r r r)rrQrr r r r rr>payloads r7 _normalize_register_like_payloadz,TenantStore._normalize_register_like_payload s #..446,,._c!,,224((..0]]((*  r6c|jj|jj|jj|jr|jjdSddS)N)r'r rr )r'rQr rr rs r7_normalize_tenant_staff_payloadz+TenantStore._normalize_tenant_staff_payload sk LL&&(((..0]]((*rr)rrrr>sessions r7_can_manage_tenant_accountsz'TenantStore._can_manage_tenant_accounts s$    5 5||777r6cn|jtk(ry|jdvxs|j|dS)NF>rrr#)rrrsession_has_permissionrs r7_can_access_timeclockz!TenantStore._can_access_timeclock s8    5 5||77l4;V;VW^`k;llr6c|sy tj|}|j |j t j }|S#t$rYywxYwNtzinfo)r fromisoformatr>rreplacer r4r>rrs r7_parse_iso_datetimezTenantStore._parse_iso_datetime sV ++I6F == ^^8<<^8F   sA AAc\|j|}|y|jtSr2)r astimezoner?rs r7_timeclock_local_datetimez%TenantStore._timeclock_local_datetime s/)))4 >  !7!9::r6cL|j|}||jS|Sr2)r r:r>rlocal_datetimes r7_timeclock_local_isoz TenantStore._timeclock_local_iso s+77 B-;-G~'')VYVr6cL|j|}||jSdSr2)r rr s r7_timeclock_local_datez!TenantStore._timeclock_local_date s+77 B(6(B~""$LLr6N reference_nowc|j|j}|j|j}|xs t}|j}|.|,|xs|}t t ||z jd}|j|j}id|jd|jd|jd|jd|jd|j|jd|j|jd |d t|xsdd z d d |jdud|j d|j"d|j$d|d|j&d|j(S)Nrrr1rr rr rrduration_hoursr is_activerrr\ entry_daterr)rr rrArmaxr total_secondsrrr1rr rr rrrr\rr) r>rrr rr3r end_referencers r7_serialize_timeclock_entryz&TenantStore._serialize_timeclock_entry s--f.?.?@ ++FOO<+z|!22  # (>$OM"3 (B'Q'Q'S#TVWX //0A0AB  &)) v~~  ))    &++  $33F4E4EF   11&//B   0  e%5%:d$BAF  D0  f33  F//  V\\  *  &++ &++!  r6Fr4)r1r2r3include_active_onlyr8cg}g}|r"|jd|j||r"|jd|j||r"|jd|j||r|jd|rddj|nd} tdt|d } |s|r t| d n| } j |j 5} | j d | d g|| j} ddd Dcgc]}j|}}|s|r||rtj|nd}|rtj|nd}g}|D]A}j|j}|!|||kr)|||kDr1|j|C|}|jfd d |d| S#1swYxYwcc}w)Nz user_id = ?z4date(substr(started_at, 1, 10)) >= date(?, '-1 day')z4date(substr(started_at, 1, 10)) <= date(?, '+1 day')zended_at IS NULLWHERE  AND rirmr[zX SELECT * FROM tenant_timeclock_entries zR ORDER BY started_at DESC LIMIT ? cj|jxs.tjj t j Sr)rr rminrr r4)entryr>s r7z5TenantStore._read_timeclock_entries..M s6d66u7G7GHuHLLL`L`hphthtL`Lur6Trreverse)rsjoinrr rrrOrhrrrrr sort)r>rr1r2r3rr8filtersr where_sqlrequested_limit sql_limitrRrowsrlr start_dayend_dayfiltered_entriesr! entry_days` r7_read_timeclock_entriesz#TenantStore._read_timeclock_entries s !  NN= ) MM' "  NNQ R MM* %  NNQ R MM( #  NN- .8?fW\\'234R aUD!122<C.o  * *7+@+@ A Z%% %&$)$ hj  CGG3411#6GG :D**:6$I6>d((2DG;=   / 66u7G7GH $(Y-B&9w+> ''. /'G u  '((?  Hs *G G Gr3c |xs t}i}|D]}|j||}|j|j|j|j|j |j ddddd}t|ddz|d<t|dt|dxsdz|d<|drt|d dz|d <|d t|d t|d kDs|d |d <g}|jD]8} t| dxsd} |ji| d t| d z di:|jd|S)Nrr)r1rr rrractive_entrieslast_started_atrrmrrrr3r4r  total_hoursrrct|jdxs(|jdxs|jdxsdjt|jdxsdfS)Nrr rrir1)rjrrPrs r7r"z6TenantStore._build_timeclock_summary..z s[DHH[)aTXXj-AaTXXlE[a_abhhjDHHY'-2.r6r) rAr setdefaultr1rr rrrjrrsrr&) r>rr3rgroupedr! serializedbucket summariesrrs r7_build_timeclock_summaryz$TenantStore._build_timeclock_summaryR s +z| 02 EE88m8\J'' $}}!& %"'"2"2 %&&''+  F!$F9$5 6 :F9 &)&*A&BSTfIgIlklEm&mF? #+&+.v6F/G+H1+L'('(0C <8P4QTWX^_pXqTr4r,6|,D()) E,.0 NN$ D_ 5 :;M   !5)=q#A     r6cid|jd|jd|jd|jd|jd|j d|j d|jd |jd |jd |jd |jd |jd|jd|jd|j}|r|j |d<|S)NrrrrrrrrZrrrrrrrrr)rrrrrrrrZrrrrrrrrr)r>rinclude_admin_noters r7_serialize_report_recordz$TenantStore._serialize_report_record s3& &))&  7 7&  V11&  !9!9 & f33 & V\\ &  6--&  &  &  &  fmm&  ()I)I&  %f&C&C&  6--&  &++& &++!& $ $*$5$5GL !r6c n|d|d|d|d|dd|dt|d|d |d |d d S) Nrr1rrr)rrrr last_errorrr) rr1rrrrrCrr)rrs r7 _serialize_push_subscription_rowz,TenantStore._serialize_push_subscription_row sbd)9~Jh-F l+C N+l+l+l+  r6cb|jd|fj}| td|S)Nz1SELECT * FROM tenant_reports WHERE id = ? LIMIT 1Segnalazione non trovata.rOrr)r>rR report_idrls r7_read_report_rowzTenantStore._read_report_row s>  ? L  (*  ;67 7 r6cx|jj}|jdvry||jvSN>rrT)rQrPrr)r>rrrUs r7rz"TenantStore.session_has_permission s:%%'--/ <<3 3W0000r6cx|jj}|jdvry||jvSrK)rQrPrr+)r>rscoperUs r7session_has_assistant_scopez'TenantStore.session_has_assistant_scope s7[[]((* <<3 3W5555r6cF|jdvxs|j|dS)N>rrr$rrrs r7_can_access_inventoryz!TenantStore._can_access_inventory s&||77l4;V;VW^`k;llr6cF|jdvxs|j|dS)N>rrr%rPrs r7_can_access_consumptionsz$TenantStore._can_access_consumptions s&||77o4;V;VW^`n;oor6cJ|j|xs|j|Sr2)rQrSrs r7+_can_access_inventory_data_for_consumptionsz7TenantStore._can_access_inventory_data_for_consumptions s$))'2\d6S6ST[6\\r6c |jdk(SNrrrs r7 _can_manage_inventory_warehousesz,TenantStore._can_manage_inventory_warehouses ||},,r6cF|jdvxs|j|dS)N>rrr&rPrs r7_can_access_reportszTenantStore._can_access_reports s&||77j4;V;VW^`i;jjr6c|jdvSrrXrs r7_can_manage_reportszTenantStore._can_manage_reports s||777r6cF|jdvxs|j|dS)N>rrr rPrs r7_can_access_homemadez TenantStore._can_access_homemade s&||77k4;V;VW^`j;kkr6cF|jdvxs|j|dS)N>rrr!rPrs r7_can_manage_homemadez TenantStore._can_manage_homemade s&||77r4;V;VW^`q;rrr6c |jdk(SrWrXrs r7%_can_manage_homemade_stock_warehousesz1TenantStore._can_manage_homemade_stock_warehouses rZr6ct|}d|}d|d}|jdvxs&|j||xs|j||S)Ntips__manage>rrrrr)r>rareanormalized_areaaccess_permissionmanage_permissions r7_can_access_tipszTenantStore._can_access_tips sj.t4#O#45#O#4G< LL4 4 G**74EF G**74EF r6cdt|}|jdvxs|j|d|dS)N>rrrfrgrh)r>rrirjs r7_can_manage_tipszTenantStore._can_manage_tips s@.t4||77 4;V;V O$G ,<  r6cL|jd|fj}|duS)NzHSELECT name FROM sqlite_master WHERE type = 'table' AND name = ? LIMIT 1rOrr>rRrwrls r7r~z TenantStore._tenant_table_exists s2  V M  (* $r6c|j||s tS|jd|djDchc]}t |dc}Scc}w)Nrrrsr')r~rrOrhrjrrs r7rz!TenantStore._tenant_table_columns sb((Z@5L")),>zl!*LMVVX  F    sAc t|dt|dt|dxsdt|dxsdt|dt|ddS) Nrr' product_countrtotal_equivalent_unitsrr)rr'rurvrrrjrrrs r7"_serialize_inventory_warehouse_rowz.TenantStore._serialize_inventory_warehouse_row sbc$i.F $ _!5!:;&+C0H,I,NQ&Oc,/0c,/0   r6c t|dt|dt|dxsdt|dxsdt|dt|ddS) Nrr' recipe_countrtotal_quantityrr)rr'rzr{rrrwrs r7'_serialize_homemade_stock_warehouse_rowz3TenantStore._serialize_homemade_stock_warehouse_row sbc$i.F $N 3 8q9#C(8$9$>Q?c,/0c,/0   r6c 2td|jvrt|dxsdnd}t|dt|dt|dxsd|t|tt |dxsdt|d t|d d S) Nryr-rr recipe_namerirBrrr) rrr~ryusage_scope_labelrrBrr)rcrrjrfrir)r>rlrys r7"_serialize_homemade_stock_item_rowz.TenantStore._serialize_homemade_stock_item_row s51>#((*1LCM",f -RX c$i.S-.s=17R8&!<[!I 3c*o23c,/0c,/0  r6c 4t|dt|dt|dxsdt|dt|dxsdt|dxsdt|dxsd t|d xsd t|d t|d d S)Nrrfwarehouse_namerir`labelcreated_by_name total_recipesrr{rr) rrfrr`rrrr{rrrwrs r7%_serialize_homemade_stock_session_rowz1TenantStore._serialize_homemade_stock_session_row sc$i.N 34!#&6"7"=2>!#&6"78W+,"3'8#9#?R@ _!5!:;#C(8$9$>Q?c,/0c,/0  r6cd|jvr t|dxsdjnd}d|jvr t|dxsdjnd}d|jvrt|dxsdnd}d|jvr t|dxsdjnd}d|jvrt|dxsdnd}|dkDr |dkDr||z nd}t d |jvrt|d xsd nd }id t|d d t|d d t|d xsdd |dt |dt|dxsdxsdd|xsdd|xsdd|dkDr|ndd|xsdd|dkDr|ndd|dt|dxsddt |dxsddt|dxsdxsddt|dxsdxsddt|dxsdxsdt|dxsdxsdt|dxsdt|dt|ddS)NrrirVrrrr{rryr-rr'rrrr\yield_ml_per_input_unit total_partsingredient_countrUrXrYrZrrr)rZrrr)rrjrQrrcrfr) r>rlrrVrr{rrrys r7_serialize_homemade_recipe_rowz*TenantStore._serialize_homemade_recipe_row* s0?VZ]ZbZbZd?dC+,2 3 9 9 ;jl B[^a^f^f^hAhC-.4" 5 ; ; =np G[^a^f^f^hFhU3';#<#ABnqI[_b_g_g_iIi3s#56<"=CCEoq@QUXU]U]U_@_%$5 6 ;!#((*1LCM",f -RX  #c$i. CF $  C(:$;$Ct D  ;  !<[!I  SW+,4   $%:%Bd  &'>'F$  !8JQ8N"4TX   0 8D  /A2E4  &'>  5]!3!8q9  C(:$;$@q A  C(:$;$Ar B Jd (S1L-M-SQS)T)\X\! " (S1L-M-SQS)T)\X\# $-04R0S0YWY,Z,b^b"3'8#9#?R@c,/0c,/0+  r6cttd|jvr|dnd|jvr|dnd}d|jvr t|dxsdjnd}d|jvr t|dxsdjnd}|xst|d}t |dxsd }|d vrd }d } |} n5t |xsd } | d kDr|| z nt |d xsd }|d z} |dz} t|d||rdnd|xsd|xsd|||| | |d vrdndt |dxsd t|dt|ddS)Neffective_measurement_unitrrrtrilinked_recipe_namerrrrrr share_ratiodr5rreciper per_liter proportional sort_orderrr)rrringredient_modertrrrr percentageper_liter_quantitycalculation_moderrr)rrjrrQrr) r>rlreference_totalrrtreffective_ingredient_namerrrrsafe_reference_totals r7)_serialize_homemade_recipe_ingredient_rowz5TenantStore._serialize_homemade_recipe_ingredient_rowT s ? /388:=010Bchhj0PS+,VZ  J\_b_g_g_iIi3s#56<"=CCEoqMaehememeoMoS%9!:!@bAGGIuw$6$U#cBS>T:U!C .3!4 A AKJ!, #()?C#@ (!+223}-45  %s*J!,t!3 c$i.8+;x 0 8D"4"<& 0&$"4/?Ce/e kyc,/415c,/0c,/0  r6c t|d|dt|dndt|dt|dt|dxsdt||t|dt|dd S) NrrArSrTrvrrr rrArSrTrv lot_countlotsrr)rjrrrq)r>rlrs r7#_serialize_inventory_stock_item_rowz/TenantStore._serialize_inventory_stock_item_row s c$i.47 4E4Q#c,/0W[N 34 _!56&+C0H,I,NQ&OTc,/0c,/0  r6cVt|dt|dt|dxsdt|dt|dxsdt|dxsdt|dxsdt|d xsd t|d xsd t|d t|d d S)Nrrfrrir`rcreated_by_user_idrtotal_productsrrvrr) rrfrr`rrrrrvrrrwrs r7 _serialize_inventory_session_rowz,TenantStore._serialize_inventory_session_row sc$i.N 34!#&6"7"=2>!#&6"78W+,"%c*>&?&E2"F"3'8#9#?R@!#&6"7"<1=&+C0H,I,NQ&Oc,/0c,/0  r6c t|dt|dt|dxsdt|dxsdt|dxsdt|dxsdt|d xsdt|d t|d d S) Nrrcrrirtotal_warehousesrrrvrr) rrcrrrrrvrrrwrs r7!_serialize_inventory_snapshot_rowz-TenantStore._serialize_inventory_snapshot_row sc$i. _!56W+,"3'8#9#?R@ #C(:$;$@q A!#&6"7"<1=&+C0H,I,NQ&Oc,/0c,/0  r6c6tt|xsddvS)Nri>ctcassacassecartonecartoni)r[rj)r>rGs r7&_inventory_lot_requires_units_per_packz2TenantStore._inventory_lot_requires_units_per_pack s X^!459gggr6c|r|j|dsiS|j|d}d|vriSi}t}|D]P}t|dxsdt|dxsdf}|ds-t|d}|||<|j |R|siSi} |j dj } | D]}|jt|d xsds%tt|d xsdtt|d xsdf}||vr`t|dxsd} | dkrx| j|} | | | kDs| | |<|jDcic]\}}|| vr|| |c}}Scc}}w) Nr}rKproduct_lookuprisupplier_lookuprrz SELECT product_name, supplier_name, lot_code, units_per_pack FROM ordini_products WHERE active = 1 AND units_per_pack IS NOT NULL AND units_per_pack > 0 rGrSrT) r~rrrjrrOrhrr[rritems) r>rR item_rowsrkeys_by_item_id target_keysrlritem_idcatalog_by_keyr+rKcurrents r7._inventory_catalog_units_per_pack_by_item_rowsz:TenantStore._inventory_catalog_units_per_pack_by_item_rows s  9 9*FW XI44ZARS ? 2I68,/E  !Cs+,23S=N9O9USU5VWCq6#d)nG'*OG $ OOC  !I79!!   (*  5C>>s3z?CXVX?YZ$S^)<)B%CDFWX[\_`o\p\vtvXwFxyC+%"3'7#8#=A>N"$((-G.7":&4s# 5!0 5 5 7 n$ ^C( (   s"E:ct|}t|}|r|sy||k(ry|j|sy|t|vSNFTrkrrx)r> stored_code scanned_coderGnormalized_storednormalized_scanneds r7_inventory_barcode_matchesz&TenantStore._inventory_barcode_matches sR9E9,G (:  2 2::8D $ABT$UUUr6ct|}t|}|r|sy||k(ry|j|r|j|sy|t|vSrr)r> left_code left_lot_code right_coderight_lot_codenormalized_leftnormalized_rights r7_inventory_barcodes_conflictz(TenantStore._inventory_barcodes_conflict s_7yA7 C&6 . .  7 7 F;;NK"?@P"QQQr6ct|}|dkr td|dk(ry|j|r)|t|dkr td|t|zS|S)Nrz$La quantita non puo essere negativa.rzPer questo lotto manca il dato unita per pack nel catalogo prodotti. Completa prima il prodotto e poi aggiungilo in inventario.)rr>r)r>rGrBrK safe_quantitys r7_inventory_effective_unitsz&TenantStore._inventory_effective_units swh 1 CD D A   6 6x @%~)>!)C Q!5#88 8r6c x|jd|fj}|sytfd|Dd}|yt|dxsd}|dkryt |dxsdxsd}t |d xs t ||Dcgc]%}j t |dxsd s|'} }| rt | ddxsd nd } t | ttd |Dd } t| dz|z} t| |zd } t| | z d }tfd|D}tfd|D}tfd|D}t|| z dkr8t|| z dkr't||z dkrtfd|Dryt |d}tfd| Dd}|t |dn dtjj}|jd|||f| dkDr#|jd|t| || ||fn|jd|f|dkDr6||jd||| ||||fn.|jd| ||||fn||jd|f|jd| ||fycc}w)Nz SELECT * FROM tenant_inventory_stock_lots WHERE item_id = ? ORDER BY lower(lot_code) ASC, created_at ASC Fc3K|]A}jt|dxsdr|dt|dxsddkDr|Cyw)rGrirKNr)rrjr).0rlr>s r7 zCTenantStore._normalize_inventory_stock_item_lots..5 sZ >>s3z?CXVX?YZ()5#./4159  sAA rKrrmrGr lot_lookupribtc3@K|]}t|dxsdyw)equivalent_unitsrNrrrls r7rzCTenantStore._normalize_inventory_stock_item_lots..N s!*cSV55G1H1MA+N*crư>c3lK|]+}t|dxsdk(rt|dxsd-yw)rrirBrNrjrrrlpack_lot_lookups r7rzCTenantStore._normalize_inventory_stock_item_lots..S s>$ 3|$*+> #j/&Q '$ 14c3lK|]+}t|dxsdk(rt|dxsd-ywrrirrNrrs r7rzCTenantStore._normalize_inventory_stock_item_lots..X s@, 3|$*+> #().Q /, rc3lK|]+}t|dxsdk(rt|dxsd-ywrrrrlsingle_lot_lookups r7rzCTenantStore._normalize_inventory_stock_item_lots..] sA. 3|$*+/@@ #().Q /. rc3JK|]}t|dxsdhvywrriNrj)rrlrrs r7rzCTenantStore._normalize_inventory_stock_item_lots..f s3C %+,BS0TTs #rc3NK|]}t|dxsdk(s|ywrrrs r7rzCTenantStore._normalize_inventory_stock_item_lots..n s)pc#lBSBYWY>Z^o>op%%inventory_lot_zNDELETE FROM tenant_inventory_stock_lots WHERE item_id = ? AND id NOT IN (?, ?)z UPDATE tenant_inventory_stock_lots SET lot_code = ?, lot_lookup = ?, quantity = ?, units_per_pack = ?, equivalent_units = ?, updated_at = ? WHERE id = ? 4DELETE FROM tenant_inventory_stock_lots WHERE id = ?a  INSERT INTO tenant_inventory_stock_lots ( id, item_id, lot_code, lot_lookup, quantity, units_per_pack, equivalent_units, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, NULL, ?, ?, ?) z UPDATE tenant_inventory_stock_lots SET lot_code = ?, lot_lookup = ?, quantity = ?, units_per_pack = NULL, equivalent_units = ?, updated_at = ? WHERE id = ? z UPDATE tenant_inventory_stock_items SET total_equivalent_units = ?, updated_at = ? WHERE id = ? T)rOrhnextrrjr[rrsumrabsr_uuiduuid4r)r>rRr timestamplot_rowspack_rowrK pack_lot_coderl single_rowssingle_lot_codervnormalized_pack_quantity normalized_pack_equivalent_unitsnormalized_single_quantitycurrent_pack_quantitycurrent_pack_equivalent_unitscurrent_single_equivalent_units keep_pack_idkeep_single_rowkeep_single_idrrs` @@r7$_normalize_inventory_stock_item_lotsz0TenantStore._normalize_inventory_stock_item_lots! s%%  J  (*  #      x(89>Q? Q HZ08D9AT h|4X8I-8XY  >>s3z?CXVX?YZ   FQ#k!nZ8@DAVZ-o>!&s*cZb*c'cef!g#&(>(E.'X#Y +01IN1Z\]+^(%*+ADd+dfg%h" #$ $ !  ),, , ) % +.. . + ' %(@@ AT I14TTUY]]36PPQUYY# 8D>* p{prvw7F7R_T23ZhimisisiuiyiyhzX{ \ lN 3  $a '    "#23"4   "   UXdWf g % ,&"" '')22!!  $"" ()22!&   (   UXfWh i  $Y 8  a s *J7c|jd|fj}d}|D]&}|j|t|d|xs|}(|S)NzBSELECT id FROM tenant_inventory_stock_items WHERE warehouse_id = ?Frr)rOrhrrj)r>rRrfrrchangedrls r7)_normalize_inventory_warehouse_stock_lotsz5TenantStore._normalize_inventory_warehouse_stock_lots sn&& P O  (*  |C?? CPSTXPYNfo?p{t{G |r6c|xsdj}|s&tjjS tj|jS#t $r}t d|d}~wwxYw)Nriz6Data inventario non valida. Usa il formato YYYY-MM-DD.)rQr8rr:rr>)r>rTrexcs r7_normalize_inventory_datez%TenantStore._normalize_inventory_date sp[b'') 9>>#--/ / `%%i0::< < `UV\_ _ `s"A!! A;* A66A;c$|j|Sr2)r)r>rTs r7"_normalize_inventory_snapshot_datez.TenantStore._normalize_inventory_snapshot_date s--e44r6c|xsdj}|s tS|jdr|dddzn|} tj|}|j |jdjS|jtjjdjS#t $r}t d|d}~wwxYw)NriZ+00:00z;Data consumo non valida. Usa un formato data/ora leggibile.r) microsecond) rQr;endswithrrr>rrr:rr r4)r>rTrnormalized_raw_valuerrs r7'_normalize_inventory_movement_timestampz3TenantStore._normalize_inventory_movement_timestamp s[b'') : >a>0::< <  .6616EOOQQ  eZ[ad d esB;; C CCct|xsdj}|sy|g}|jdr|j|ddd|D]d} t j |}|j |jtj}|jtjcSy#t $rYswxYw)Nrir r r r) rjrQr rsrrr>rrr r4r)r>rTr candidatesrrs r7_parse_inventory_datetimez%TenantStore._parse_inventory_datetime s $**, [   c "   3B 07 8# 3I !// :}}$x||<$$X\\2 2 3   sB66 CCcd} |d}idt|ddt|dxsddt|xsdd|dt|dnddt|dxsddt|dxsddt|dxsdd t |d xsd d |d t |d ndd t |d xsd d t|d xsddt|dxsddt|dxsddt|dxsddt|dxsddt|dxsdS#ttf$rd}YEwxYw)NrrrfrirArSrTrGrBrrKr movement_kind source_type source_labelrhrr)r IndexErrorrjrr)r>rlrs r7!_serialize_inventory_movement_rowz-TenantStore._serialize_inventory_movement_row s " !12N #c$i. CN 3 9r:  c."6B7  C 4E4Q#c,/0W[  CN 3 9r:  S_!5!;<   C O1r2  c*o23  cBR>S>_eC(8$9:ei  c*<&=&B C  S_!5!;<  3s=17R8  CN 3 9r:  3s=17R8  #c,/526 #c,/526!  *% "!N "sD::EEcztj|d|jdj}d|S)Nrinventory_consumption_stat_)rsha1rL hexdigest)r>rrrs r7_inventory_consumption_stat_idz*TenantStore._inventory_consumption_stat_id s> 0?2CDKKGTU__a,VH55r6ct|d|dt|dndt|dxsdt|dxsdt|dxsdt|dxsdt|d xsdt|d xsdt|d xsdt|d xsdt|d xsdxsdt|dxsdxsdt|dxsdt|dxsdt|dxsddS)NrrArSrirTtotal_consumed_unitsrworkdays_countconsumed_days_countaverage_daily_consumed_units*average_consumed_units_on_consumption_daysmovement_countfirst_consumption_datelast_consumption_datecalculation_sourcemanual_consumption_movementsrr)rrArSrTr r!r"r#r$r%r&r'r(rrrwrs r71_serialize_inventory_consumption_product_stat_rowz=TenantStore._serialize_inventory_consumption_product_stat_row s3c$i.47 4E4Q#c,/0W[N 3 9r: _!5!;<$)#.D*E*J$K!#&6"7"<1=#&s+@'A'FQ#G,1#6T2U2ZYZ,[:?Dp@q@vuv:w!#&6"7"<1=&)#.F*G*M2&N&VRV%(-D)E)K%L%TPT"%c*>&?&aCa"bc,/526c,/526  r6cF|jd||fjS)Nz SELECT * FROM tenant_inventory_consumption_product_stats WHERE product_lookup = ? AND supplier_lookup = ? LIMIT 1 rq)r>rRrrs r7,_read_inventory_consumption_product_stat_rowz8TenantStore._read_inventory_consumption_product_stat_row( s/!!  _ -  (* r6rcv|xs t}|j|||jdj}|j |}i}g}|D]|}t |dxsdt |dxsdf} t |dxsdt |dxsdf} |j | gj| |j| ~t|j|||} |jd | d kry|jd j} | D]} t | dxsdj}t | dxsdj}|r|sItt| d xsd d }|d krktt| dxsd d}t|j||j||fg|}|d kDrt|t|z d nd}t|t|z d }|jd|j!||| dt| dnd||t | dxs|t | dxs||||||t| dxsd t | dxsdxsdt | dxsdxsdd||fy)Nra] SELECT DISTINCT product_lookup, supplier_lookup, period_start_date, period_end_date FROM tenant_inventory_estimated_consumptions WHERE consumed_units > 0 AND period_start_date <> '' AND period_end_date <> '' period_start_dateriperiod_end_daterrr?z6DELETE FROM tenant_inventory_consumption_product_statsra? SELECT MIN(product_id) AS product_id, product_lookup, supplier_lookup, MAX(product_name) AS product_name, MAX(supplier_name) AS supplier_name, ROUND(SUM(consumed_units), 6) AS total_consumed_units, COUNT(DISTINCT consumption_date) AS consumed_days_count, COUNT(*) AS movement_count, MIN(consumption_date) AS first_consumption_date, MAX(consumption_date) AS last_consumption_date FROM tenant_inventory_estimated_consumptions WHERE consumed_units > 0 AND product_lookup <> '' AND supplier_lookup <> '' AND consumption_date <> '' GROUP BY product_lookup, supplier_lookup r rr"rmra8 INSERT INTO tenant_inventory_consumption_product_stats ( id, product_id, product_lookup, supplier_lookup, product_name, supplier_name, total_consumed_units, workdays_count, consumed_days_count, average_daily_consumed_units, average_consumed_units_on_consumption_days, movement_count, first_consumption_date, last_consumption_date, calculation_source, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) rArSrTr%r&r':estimated_from_inventory_confirmed_orders_and_bar_calendar)r;-_refresh_all_inventory_estimated_consumptionsrOrh_read_homemade_stock_settingsrjr9rsrq+_inventory_bar_operational_days_for_periodsrQrrrrrr)r>rRrr period_rowsr?periods_by_product all_periods period_rowperiodrr!r+rlrrr r"product_workdays_count average_dailyaverage_on_consumption_dayss r7,_refresh_inventory_consumption_product_statsz8TenantStore._refresh_inventory_consumption_product_stats9 s9 ,(*  :::Q[:\ ((    (* 55jAKM-/ % 'J*%89?R@#jQbFcFigiBjkFz"239r:C K\@]@cacB?EEGO!#(s3I/J/Oa)PRS#T #q("%c#.C*D*I&JA"N %(@@&**NO+LbQ%A& "*A-*U3I-JJAN  +00DuM`Ga0acd*e '   ,77X.1,.?.KCL)*QU"#N+=~>O,?@(*'!/,-2345;<D34:;CtP#-) -? r6cb|jd|fj}| td|S)Na SELECT warehouses.*, COUNT(items.id) AS product_count, COALESCE(SUM(items.total_equivalent_units), 0) AS total_equivalent_units FROM tenant_inventory_warehouses AS warehouses LEFT JOIN tenant_inventory_stock_items AS items ON items.warehouse_id = warehouses.id WHERE warehouses.id = ? GROUP BY warehouses.id LIMIT 1 zMagazzino non trovato.rGr>rRrfrls r7_read_inventory_warehouse_rowz)TenantStore._read_inventory_warehouse_row sB   O  (*  ;34 4 r6cb|jd|fj}| td|S)Na- SELECT warehouses.*, ( SELECT COUNT(*) FROM tenant_homemade_recipes ) AS recipe_count, ( SELECT COALESCE(SUM(items.quantity), 0) FROM tenant_homemade_stock_items AS items WHERE items.warehouse_id = warehouses.id ) AS total_quantity FROM tenant_homemade_stock_warehouses AS warehouses WHERE warehouses.id = ? LIMIT 1 z"Magazzino/frigo stock non trovato.rGr?s r7"_read_homemade_stock_warehouse_rowz.TenantStore._read_homemade_stock_warehouse_row sB    O# $ (*% & ;?@ @ r6cb|jd|fj}| td|S)Nz=SELECT * FROM tenant_inventory_snapshots WHERE id = ? LIMIT 1z"Fotografia inventario non trovata.rG)r>rR snapshot_idrls r7_read_inventory_snapshot_rowz(TenantStore._read_inventory_snapshot_row s>  K N  (*  ;?@ @ r6cb|jd|fj}| td|S)Nz