Ë
    1¨ŠjNS ã                   ó¢  — d dl Z d dl mZ d dlmZ d dlZd dlmZ d dlZd dlZd dl	m
Z
 d dlZ ej                  ej                  d¬«        ej                  d«      Zd	Zd
Zdedefd„ZdXd„ZdXd„Zdefd„Zdefd„Zdefd„Zdefd„Zdedededefd„Zdededefd„Zdededefd„ZdYdede
e    d e!fd!„Z"dedefd"„Z#ded#efd$„Z$ded#efd%„Z%dedefd&„Z&dZded'ed(efd)„Z'ded(efd*„Z(ded+ed,efd-„Z)ded+ed.efd/„Z*ded+efd0„Z+defd1„Z,ded+ed2ed3efd4„Z-ded+efd5„Z.defd6„Z/ded7efd8„Z0ded9efd:„Z1ded;efd<„Z2ded=edefd>„Z3d[ded?ed@edAe!dBe!dCe!ded(efdD„Z4d\ded e!fdE„Z5dedFefdG„Z6defdH„Z7dedefdI„Z8dJ„ Z9defdK„Z:defdL„Z;dZdefdM„Z<defdN„Z=dede!fdO„Z>dedPede!fdQ„Z?dZdefdR„Z@dZdefdS„ZAg dT¢ZBg dU¢ZCdedefdV„ZDdedefdW„ZEy)]é    N)Úsql)ÚJson)Úsettings)ÚListz4%(asctime)s - %(name)s - %(levelname)s - %(message)s)ÚlevelÚformatÚdatabaseÚAivoraa,  
    (
        s."isActive" = TRUE
        OR LOWER(COALESCE(s."status", '')) IN ('active', 'trial', 'trialing')
        OR (
            s."trialStart" IS NOT NULL
            AND s."trialEnd" IS NOT NULL
            AND NOW() >= s."trialStart"
            AND NOW() <= s."trialEnd"
        )
    )
Úproduct_nameÚreturnc                 óN   ‡— t        | t        «      syt        ˆfd„| D «       «      S )NFc              3   ó�   •K  — | ]=  }t        |t        «      xr' |j                  d «      ‰k(  xr |j                  d«      –— Œ? y­w)ÚproductNameÚfeaturesN)Ú
isinstanceÚdictÚget)Ú.0Úproductr   s     €ú:/var/www/html/strategist-ai/app/services/infra/database.pyú	<genexpr>z$_plan_has_product.<locals>.<genexpr>    sN   øè ø€ ò ð ô 	�7œDÓ!ò 	$Ø�K‰K˜Ó&¨,Ñ6ò	$à�K‰K˜
Ó#ó	$ñùs   ƒAA)r   ÚlistÚany)Úproduct_featuresr   s    `r   Ú_plan_has_productr      s-   ø€ ÜÐ&¬Ô-ØÜó ð (ô	ó ð ó    c                 ó0  — t        | «      D ]g  }	 t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  d¬«      }|c S  y# t        j                  $ rc}t        j                  d|dz   › d|› d|› d�«       || dz
  k  rt        j                  |«       nt        j                  dd	¬
«       |‚Y d}~ŒÜd}~wt        $ r!}t        j                  d|› �d	¬
«       |‚d}~ww xY w)z=
    Establishes a database connection with retry logic.
    é
   ©ÚhostÚportÚuserÚpasswordÚdbnameÚconnect_timeoutzDatabase connection attempt é   ú	 failed: ú. Retrying in ús...z7Max retries reached. Could not connect to the database.T©Úexc_infoNz)Unexpected error connecting to database: )ÚrangeÚpsycopg2Úconnectr   ÚDB_HOSTÚDB_PORTÚDB_USERÚDB_PASSWORDÚDB_NAMEÚOperationalErrorÚloggerÚwarningÚtimeÚsleepÚerrorÚ	Exception©ÚretriesÚdelayÚattemptÚconnÚes        r   Úget_db_connectionrA   '   s  € ô ˜“>ò ˆð	Ü×#Ñ#Ü×%Ñ%Ü×%Ñ%Ü×%Ñ%Ü!×-Ñ-Ü×'Ñ'Ø "ôˆDð ŠKñøô ×(Ñ(ò 	Ü�N‰NÐ9¸'ÀA¹+¸ÀiÐPQÈsÐR`ÐafÐ`gÐgkÐlÔmØ˜ 1™Ò$Ü—
‘
˜5Õ!ä—‘ÐVÐae�ÔfØ�ô "ûô ò 	Ü�L‰LÐDÀQÀCÐHÐSWˆLÔXØˆGûð	úó%   �A"A7Á7DÂ
AC(Ã(DÃ4DÄDc                 ó0  — t        | «      D ]g  }	 t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  d¬«      }|c S  y# t        j                  $ rc}t        j                  d|dz   › d|› d|› d�«       || dz
  k  rt        j                  |«       nt        j                  dd	¬
«       |‚Y d}~ŒÜd}~wt        $ r!}t        j                  d|› �d	¬
«       |‚d}~ww xY w)zK
    Establishes a connection to the master database with retry logic.
    r   r   zMaster DB connection attempt r&   r'   r(   r)   z>Max retries reached. Could not connect to the master database.Tr*   Nz0Unexpected error connecting to master database: )r,   r-   r.   r   r/   r0   r1   r2   ÚMASTER_DB_NAMEr4   r5   r6   r7   r8   r9   r:   r;   s        r   Úget_master_db_connectionrE   A   s  € ô ˜“>ò ˆð	Ü×#Ñ#Ü×%Ñ%Ü×%Ñ%Ü×%Ñ%Ü!×-Ñ-Ü×.Ñ.Ø "ôˆDð ŠKñøô ×(Ñ(ò 	Ü�N‰NÐ:¸7ÀQ¹;¸-ÀyÐQRÐPSÐSaÐbgÐahÐhlÐmÔnØ˜ 1™Ò$Ü—
‘
˜5Õ!ä—‘Ð]Ðhl�ÔmØ�ô "ûô ò 	Ü�L‰LÐKÈAÈ3ÐOÐZ^ˆLÔ_ØˆGûð	úrB   Ú	tenant_idc           
      ó(  — | j                  dd«      j                  dd«      j                  «       s#t        j                  d| › �«       t	        d«      ‚t        j                  d| › �«       t        «       }	 |j                  «       5 }|j                  d«       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d	«      j                  t        j                  | «      «      «       |j                  t        j                  d
«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       t        D ]u  \  }}|j                  t        j                  d«      j                  t        j                  | «      t        j                  |«      «      t        j                  |«      z   «       Œw |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       t        D ]K  \  }}|j                  t        j                  |«      j                  t        j                  | «      «      «       ŒM |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      t        j                  | «      «      «       |j                  t        j                  d «      j                  t        j                  | «      «      «       |j                  t        j                  d!«      j                  t        j                  | «      «      «       |j                  t        j                  d"«      j                  t        j                  | «      «      «       |j                  t        j                  d#«      j                  t        j                  | «      «      «       |j                  t        j                  d$«      j                  t        j                  | «      «      «       |j                  t        j                  d%«      j                  t        j                  | «      «      «       |j                  t        j                  d&«      j                  t        j                  | «      «      «       |j                  t        j                  d'«      j                  t        j                  | «      «      «       |j                  t        j                  d(«      j                  t        j                  | «      «      «       |j                  t        j                  d)«      j                  t        j                  | «      «      «       |j                  t        j                  d*«      j                  t        j                  | «      «      «       |j                  t        j                  d+«      j                  t        j                  | «      «      «       |j                  t        j                  d,«      j                  t        j                  | «      «      «       |j                  t        j                  d-«      j                  t        j                  | «      «      «       |j                  t        j                  d.«      j                  t        j                  | «      «      «       |j                  t        j                  d/«      j                  t        j                  | «      «      «       |j                  t        j                  d0«      j                  t        j                  | «      «      «       |j                  t        j                  d1«      j                  t        j                  | «      «      «       |j                  t        j                  d2«      j                  t        j                  | «      «      «       t        | |«       |j!                  «        t        j                  d3| › �«       	 t#        | «      }|rŒt        j                  d4| › d5�«       |j                  «       5 }t        j                  d6«      j                  t        j                  | «      «      }	|j                  |	|f«       d7d7d7«       |j!                  «        	 d7d7d7«       |j)                  «        y:# 1 sw Y   Œ3xY w# t$        $ r%}
t        j'                  d8| › d9|
› �«       Y d7}
~
ŒOd7}
~
ww xY w# 1 sw Y   nxY wn@# t$        $ r4}|j+                  «        t        j                  d;| › d9|› �d:¬<«       |‚d7}~ww xY w	 |j)                  «        y7# |j)                  «        w xY w)=zw
    Creates the schema for the tenant if it doesn't exist and
    creates the relevant tables within that schema.
    Ú_Ú ú-zInvalid tenant_id format: zFInvalid tenant_id format. Must be alphanumeric, underscore, or hyphen.zBootstrapping tenant: z%CREATE EXTENSION IF NOT EXISTS vectorzCREATE SCHEMA IF NOT EXISTS {}aj  
                CREATE TABLE IF NOT EXISTS {}.strategist_knowledge_base (
                    id SERIAL PRIMARY KEY,
                    content TEXT NOT NULL,
                    embedding vector(3072) NOT NULL,
                    metadata JSONB,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zALTER TABLE {}.strategist_knowledge_base ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMPz^ALTER TABLE {}.strategist_knowledge_base ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMPz_UPDATE {}.strategist_knowledge_base SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULLa>  
                CREATE TABLE IF NOT EXISTS {}.strategist_summaries (
                    id SERIAL PRIMARY KEY,
                    ingestion_type TEXT NOT NULL,
                    summary TEXT NOT NULL,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zzALTER TABLE {}.strategist_summaries ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMPzYALTER TABLE {}.strategist_summaries ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMPzZUPDATE {}.strategist_summaries SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULLa	  
                CREATE TABLE IF NOT EXISTS {}.strategist_settings (
                    key TEXT PRIMARY KEY,
                    value JSONB NOT NULL,
                    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            aÓ  
                CREATE TABLE IF NOT EXISTS {}.strategist_products (
                    id           SERIAL PRIMARY KEY,
                    product_key  TEXT NOT NULL UNIQUE,
                    name         TEXT NOT NULL,
                    description  TEXT,
                    image_url    TEXT,
                    -- Nullable: a source that exposes no product URLs at all
                    -- would otherwise have every product rejected at
                    -- validation, leaving the merchant with an empty catalog
                    -- and no explanation. See migrate_products_table below.
                    product_url  TEXT,
                    category     TEXT,
                    ctas         JSONB NOT NULL DEFAULT '[]',
                    options      JSONB NOT NULL DEFAULT '[]',
                    raw          JSONB NOT NULL DEFAULT '{{}}',
                    related_keys TEXT[] NOT NULL DEFAULT '{{}}',
                    extracted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                    source_kind      TEXT NOT NULL DEFAULT 'crawl',
                    source_ref       TEXT,
                    external_id      TEXT,
                    brand            TEXT,
                    taxonomy_path    TEXT[] NOT NULL DEFAULT '{{}}',
                    taxonomy_source  TEXT,
                    raw_category     TEXT,
                    price_cents      INT,
                    price_max_cents  INT,
                    compare_at_cents INT,
                    currency         TEXT,
                    on_sale          BOOLEAN NOT NULL DEFAULT false,
                    in_stock         BOOLEAN NOT NULL DEFAULT true,
                    status           TEXT,
                    attributes       JSONB NOT NULL DEFAULT '[]',
                    quality_score    REAL,
                    content_hash     TEXT,
                    record_hash      TEXT,
                    synced_at        TIMESTAMP WITH TIME ZONE,
                    -- Recommendation matching spec columns. tenant_relations is
                    -- keyed by tenant so cross-tenant recs never collide;
                    -- missing_fields flags an incomplete product for exclusion
                    -- from serving without overloading status, which carries
                    -- the source's own ACTIVE/DRAFT/ARCHIVED value.
                    tenant_relations JSONB NOT NULL DEFAULT '{{}}',
                    rating           REAL,
                    review_count     INT,
                    featured_rank    INT,
                    missing_fields   TEXT[] NOT NULL DEFAULT '{{}}',
                    price_reference_cents INT,
                    fx_rate_used     REAL,
                    -- Attribute extraction (Phase 1). Nullable: null means
                    -- "not yet extracted", not "extracted, and false".
                    is_accessory     BOOLEAN,
                    price_tier       TEXT,
                    enriched_hash    TEXT
                )
            ú?ALTER TABLE {}.strategist_products ADD COLUMN IF NOT EXISTS {} z^CREATE INDEX IF NOT EXISTS strategist_products_url_idx ON {}.strategist_products (product_url)z`CREATE INDEX IF NOT EXISTS strategist_products_category_idx ON {}.strategist_products (category)úmCREATE INDEX IF NOT EXISTS strategist_products_source_idx ON {}.strategist_products (source_kind, source_ref)úfCREATE INDEX IF NOT EXISTS strategist_products_status_idx ON {}.strategist_products (status, in_stock)úuCREATE INDEX IF NOT EXISTS strategist_neighbors_anchor_idx ON {}.strategist_product_neighbors (anchor_key, pair_type)aÑ  
                CREATE TABLE IF NOT EXISTS {}.strategist_tickets (
                    id SERIAL PRIMARY KEY,
                    ticket_id TEXT UNIQUE NOT NULL,
                    thread_id TEXT,
                    user_name TEXT NOT NULL,
                    heading TEXT NOT NULL,
                    content TEXT NOT NULL,
                    priority TEXT NOT NULL,
                    category TEXT,
                    status TEXT DEFAULT 'Open',
                    email TEXT,
                    contact_no TEXT,
                    contact_medium TEXT,
                    contact_details TEXT,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zxALTER TABLE {}.strategist_tickets ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMPzWALTER TABLE {}.strategist_tickets ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMPzXUPDATE {}.strategist_tickets SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULLzIALTER TABLE {}.strategist_tickets ADD COLUMN IF NOT EXISTS thread_id TEXTzHALTER TABLE {}.strategist_tickets ADD COLUMN IF NOT EXISTS category TEXTa¢  
                CREATE TABLE IF NOT EXISTS {}.strategist_ticket_comments (
                    id SERIAL PRIMARY KEY,
                    ticket_id TEXT NOT NULL REFERENCES {}.strategist_tickets(ticket_id) ON DELETE CASCADE,
                    admin_name TEXT,
                    comment TEXT NOT NULL,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            z€ALTER TABLE {}.strategist_ticket_comments ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMPz_ALTER TABLE {}.strategist_ticket_comments ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMPa‡  
                CREATE TABLE IF NOT EXISTS {}.strategist_feedback (
                    id SERIAL PRIMARY KEY,
                    thread_id TEXT NOT NULL,
                    user_name TEXT,
                    question TEXT NOT NULL,
                    answer TEXT NOT NULL,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zYALTER TABLE {}.strategist_feedback ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{{}}'a¹  
                CREATE TABLE IF NOT EXISTS {}.strategist_llm_usage (
                    id SERIAL PRIMARY KEY,
                    feature_name TEXT NOT NULL,
                    model_name TEXT NOT NULL,
                    prompt_tokens INTEGER NOT NULL,
                    completion_tokens INTEGER NOT NULL,
                    total_tokens INTEGER NOT NULL,
                    thread_id TEXT,
                    cumulative_prompt_tokens INTEGER DEFAULT 0,
                    cumulative_completion_tokens INTEGER DEFAULT 0,
                    metadata JSONB DEFAULT '{{}}',
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zKALTER TABLE {}.strategist_llm_usage ADD COLUMN IF NOT EXISTS thread_id TEXTzgALTER TABLE {}.strategist_llm_usage ADD COLUMN IF NOT EXISTS cumulative_prompt_tokens INTEGER DEFAULT 0zkALTER TABLE {}.strategist_llm_usage ADD COLUMN IF NOT EXISTS cumulative_completion_tokens INTEGER DEFAULT 0zZALTER TABLE {}.strategist_llm_usage ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{{}}'zzALTER TABLE {}.strategist_llm_usage ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMPzYALTER TABLE {}.strategist_llm_usage ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMPzZUPDATE {}.strategist_llm_usage SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULLax  
                CREATE TABLE IF NOT EXISTS {}.strategist_thread_analytics (
                    thread_id TEXT PRIMARY KEY,
                    sentiment_score FLOAT,
                    intent TEXT,
                    is_high_intent BOOLEAN,
                    is_lead_qualified BOOLEAN,
                    is_resolved BOOLEAN,
                    escalation_needed BOOLEAN,
                    positive_points TEXT,
                    key_concerns TEXT,
                    pain_point TEXT,
                    feature_request TEXT,
                    objection TEXT,
                    cta_clicked BOOLEAN,
                    summary TEXT,
                    metadata JSONB,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                    last_analyzed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zXALTER TABLE {}.strategist_thread_analytics ADD COLUMN IF NOT EXISTS positive_points TEXTzUALTER TABLE {}.strategist_thread_analytics ADD COLUMN IF NOT EXISTS key_concerns TEXTa8  
                CREATE TABLE IF NOT EXISTS {}.strategist_crawled_urls (
                    id SERIAL PRIMARY KEY,
                    source_id TEXT NOT NULL,
                    url TEXT NOT NULL,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                )
            zVCREATE INDEX IF NOT EXISTS idx_usage_created_at ON {}.strategist_llm_usage(created_at)zXCREATE INDEX IF NOT EXISTS idx_feedback_created_at ON {}.strategist_feedback(created_at)zQCREATE INDEX IF NOT EXISTS idx_usage_thread ON {}.strategist_llm_usage(thread_id)z"Successfully bootstrapped tenant: zBrand persona found for z". Storing in strategist_summaries.z¬
                            INSERT INTO {}.strategist_summaries (ingestion_type, summary)
                            VALUES ('brand_persona', %s)
                        Nz0Persona aggregation failed during bootstrap for ú: TzError bootstrapping tenant r*   )ÚreplaceÚisalnumr5   r9   Ú
ValueErrorÚinforA   ÚcursorÚexecuter   ÚSQLr   Ú
IdentifierÚ_PRODUCT_COLUMNSÚ_PAIRING_TABLESÚensure_thread_id_columnÚcommitÚfetch_brand_personar:   r6   ÚcloseÚrollback)rF   r?   ÚcurÚ	_col_nameÚ_col_ddlÚ_table_nameÚ_ddlÚpersonaÚpersona_curÚinsert_queryÚper@   s               r   Úbootstrap_tenantrh   [   só  € ð ×Ñ˜S "Ó%×-Ñ-¨c°2Ó6×>Ñ>Ô@Ü�‰Ð1°)°Ð=Ô>ÜÐaÓbÐbä
‡K�KÐ(¨¨Ð4Ô5ÜÓ€DðyØ�[‰[‹]ð r	˜cà�K‰KÐ?Ô@ð �K‰KœŸ™Ð @ÓA×HÑHÌÏÉÐXaÓIbÓcÔdð �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1Ó2ô4ð �K‰KœŸ™ð  !bó  c÷  jñ  jô  kn÷  kyñ  kyð  zCó  kDó  Eô  FØ�K‰KœŸ™ð  !Aó  B÷  Iñ  Iô  JM÷  JXñ  JXð  Ybó  Jcó  dô  eØ�K‰KœŸ™ð  !Bó  C÷  Jñ  Jô  KN÷  KYñ  KYð  Zcó  Kdó  eô  fð �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1Ó2ô4ð �K‰KœŸ™ð  !]ó  ^÷  eñ  eô  fi÷  ftñ  ftð  u~ó  fó  @ô  AØ�K‰KœŸ™Ð {Ó|÷  Dñ  Dô  EH÷  ESñ  ESð  T]ó  E^ó  _ô  `Ø�K‰KœŸ™Ð |Ó}÷  Eñ  Eô  FI÷  FTñ  FTð  U^ó  F_ó  `ô  að �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1Ó2ô4ð& �K‰KœŸ™ð 7!ó 7÷n ‘œŸ™ yÓ1Ó2ôo74ôx (8ò dÑ#�	˜8Ø—‘œCŸG™GØUóç‘&œŸ™¨	Ó2´C·N±NÀ9Ó4MÓNÔQT×QXÑQXÐYaÓQbñcõ dðdð
 �K‰KœŸ™ð:ó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ð7ó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ðFó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ð?ó÷ ‰f”S—^‘^ IÓ.Ó/ô1ô &5ò MÑ!�˜TØ—‘œCŸG™G D›M×0Ñ0´·±À	Ó1JÓKÕLðMà�K‰KœŸ™ðMó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ð !ó ÷" ‘œŸ™ yÓ1Ó2ô#4ð& �K‰KœŸ™ð  ![ó  \÷  cñ  cô  dg÷  drñ  drð  s|ó  d}ó  ~ô  Ø�K‰KœŸ™Ð yÓz÷  Bñ  Bô  CF÷  CQñ  CQð  R[ó  C\ó  ]ô  ^Ø�K‰KœŸ™Ð zÓ{÷  Cñ  Cô  DG÷  DRñ  DRð  S\ó  D]ó  ^ô  _ð �K‰KœŸ™Ð kÓl×sÑsÔtw÷  uCñ  uCð  DMó  uNó  Oô  Pà�K‰KœŸ™Ð jÓk×rÑrÔsv÷  tBñ  tBð  CLó  tMó  Nô  Oð �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1´3·>±>À)Ó3LÓMôOð �K‰KœŸ™ð  !có  d÷  kñ  kô  lo÷  lzñ  lzð  {Dó  lEó  Fô  GØ�K‰KœŸ™ð  !Bó  C÷  Jñ  Jô  KN÷  KYñ  KYð  Zcó  Kdó  eô  fð �K‰KœŸ™ð 	!ó 	÷ ‘œŸ™ yÓ1Ó2ô	4ð �K‰KœŸ™Ð {Ó|÷  Dñ  Dô  EH÷  ESñ  ESð  T]ó  E^ó  _ô  `ð �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1Ó2ô4ð" �K‰KœŸ™Ð mÓn×uÑuÔvy÷  wEñ  wEð  FOó  wPó  Qô  RØ�K‰KœŸ™ð  !Jó  K÷  Rñ  Rô  SV÷  Sañ  Sað  bkó  Sló  mô  nØ�K‰KœŸ™ð  !Nó  O÷  Vñ  Vô  WZ÷  Weñ  Weð  foó  Wpó  qô  rØ�K‰KœŸ™Ð |Ó}÷  Eñ  Eô  FI÷  FTñ  FTð  U^ó  F_ó  `ô  aØ�K‰KœŸ™ð  !]ó  ^÷  eñ  eô  fi÷  ftñ  ftð  u~ó  fó  @ô  AØ�K‰KœŸ™Ð {Ó|÷  Dñ  Dô  EH÷  ESñ  ESð  T]ó  E^ó  _ô  `Ø�K‰KœŸ™Ð |Ó}÷  Eñ  Eô  FI÷  FTñ  FTð  U^ó  F_ó  `ô  að �K‰KœŸ™ð !ó ÷( ‘œŸ™ yÓ1Ó2ô)4ð. �K‰KœŸ™Ð zÓ{÷  Cñ  Cô  DG÷  DRñ  DRð  S\ó  D]ó  ^ô  _Ø�K‰KœŸ™Ð wÓx×Ñô  AD÷  AOñ  AOð  PYó  AZó  [ô  \ð �K‰KœŸ™ð !ó ÷ ‘œŸ™ yÓ1Ó2ô4ð �K‰KœŸ™Ð xÓy÷  Añ  Aô  BE÷  BPñ  BPð  QZó  B[ó  \ô  ]Ø�K‰KœŸ™Ð zÓ{÷  Cñ  Cô  DG÷  DRñ  DRð  S\ó  D]ó  ^ô  _Ø�K‰KœŸ™Ð sÓt×{Ñ{Ô|÷  }Kñ  }Kð  LUó  }Vó  Wô  Xä# I¨sÔ3à�K‰KŒMÜ�K‰KÐ<¸Y¸KÐHÔIðeÜ-¨iÓ8�ÙÜ—K‘KÐ":¸9¸+ÐEgÐ hÔið Ÿ™›ð F¨+Ü'*§w¡wð 0ó (÷ $™V¤C§N¡N°9Ó$=Ó>ð %ð $×+Ñ+¨L¸7¸*ÔE÷Fð —K‘K”Mð ÷e	r	ðp	 	�
‰
�÷!Fð Fûô ò eÜ—‘Ð!QÐR[ÐQ\Ð\^Ð_aÐ^bÐc×dÑdûðeú÷_	r	ð r	úð r	øôf	 ò Ø�‰ŒÜ�‰Ð2°9°+¸RÀ¸sÐCÈdˆÔSØˆûðúðg	r	ðp	 	�
‰
�øˆ�
‰
�ús†   Á7x- Âr(x ô06w/õ&Aw#ö1w/÷	x ÷
x- ÷#w,	÷(w/÷/	x÷8xøx øxøx ø x)ø%x- ø,y? ø-	y*ø6/y%ù%y*ù*y? ù?zc                 óä  — t        «       }	 |j                  «       5 }|j                  d| f«       |j                  «       d   s	 ddd«       |j	                  «        yt        j                  d«      j                  t        j                  | «      «      }|j                  |«       |j                  «       }|r|d   s	 ddd«       |j	                  «        y|d   }|j                  dg «      }|j                  dg «      }|D �cg c]%  }|j                  d«      sŒ|j                  d«      ‘Œ' }	}|D �
cg c]%  }
|
j                  d«      sŒ|
j                  d«      ‘Œ' }}
|	s|s	 ddd«       |j	                  «        yg }|	r#|j                  dd	j                  |	«      › �«       |r#|j                  d
d	j                  |«      › �«       dj                  |«      cddd«       |j	                  «        S c c}w c c}
w # 1 sw Y   nxY wnA# t        $ r5}t        j                  d| › d|› �«       Y d}~|j	                  «        yd}~ww xY w	 |j	                  «        y# |j	                  «        w xY w)zŠ
    Fetches the latest brand analysis result and extracts tone and voice traits.
    Returns a string summarizing the brand persona.
    zâ
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = %s 
                    AND table_name = 'brandforge_analyses'
                )
            r   Nz~
                SELECT full_result FROM {}.brandforge_analyses 
                ORDER BY created_at DESC LIMIT 1
            ÚtoneÚvoiceÚtextzTone: z, zVoice: z | z!Error fetching brand persona for rO   )rA   rT   rU   Úfetchoner]   r   rV   r   rW   r   ÚappendÚjoinr:   r5   r9   )rF   r?   r_   ÚqueryÚrowÚfull_resultÚ	tone_listÚ
voice_listÚtÚextracted_toneÚvÚextracted_voiceÚpersona_partsr@   s                 r   r\   r\   ¢  s*  € ô
 Ó€Dð,Ø�[‰[‹]ð &	-˜cà�K‰Kð ð �ôð —<‘<“> !Ò$Ø÷&	-ðV 	�
‰
�ô= —G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð ð �K‰K˜ÔØ—,‘,“.ˆCÙ˜c !šfØ÷)&	-ðV 	�
‰
�ð+ ˜a™&ˆKØ#Ÿ™¨°Ó3ˆIØ$Ÿ™¨°"Ó5ˆJà5>ÖP°À!Ç%Á%ÈÅ-˜aŸe™e F�mÐPˆNÐPØ6@ÖR°ÀAÇEÁEÈ&ÅM˜qŸu™u V�}ÐRˆOÐRá!©/Ø÷=&	-ðV 	�
‰
�ð ˆMÙØ×$Ñ$ v¨d¯i©i¸Ó.GÐ-HÐ%IÔJÙØ×$Ñ$ w¨t¯y©y¸Ó/IÐ.JÐ%KÔLà—:‘:˜mÓ,÷M&	-ð &	-ðV 	�
‰
�ùò# QùÚR÷7&	-ð &	-úð &	-øôN ò Ü�‰Ð8¸¸À2ÀaÀSÐIÔJÛà�
‰
�ûð	úðO&	-ðV 	�
‰
�øˆ�
‰
�ús–   ŒH
 œ(G=ÁH
 ÁA G=Â=H
 Ã-G=ÄG3ÄG3Ä-G=Ä3G8Å
G8ÅG=Å$H
 Å=AG=Ç	H
 Ç3
G=Ç=HÈH
 È	I È
	IÈIÈ.I ÉIÉI ÉI/c                 ón  — 	 |j                  d| f«       |j                  «       s`t        j                  d| › d�«       |j                  t	        j
                  d«      j                  t	        j                  | «      «      «       yy# t        $ r%}t        j                  d| › d|› �«       Y d}~yd}~ww xY w)z€
    Internal helper to add the thread_id column to the tickets table if missing.
    Used for self-healing legacy tenants.
    zŽSELECT column_name FROM information_schema.columns WHERE table_schema = %s AND table_name = 'strategist_tickets' AND column_name = 'thread_id'z)Self-healing: Adding thread_id column to ú.strategist_ticketsz;ALTER TABLE {}.strategist_tickets ADD COLUMN thread_id TEXTz&Failed to ensure thread_id column for rO   N©
rU   rm   r5   rS   r   rV   r   rW   r:   r6   ©rF   r_   r@   s      r   rZ   rZ   Ö  s³   € ð
RØ�‰ð  eð  irð  htô  	uØ�|‰|Œ~Ü�K‰KÐCÀIÀ;ÐNaÐbÔcØ�K‰KœŸ™Ð ]Ó^×eÑeÔfi×ftÑftÐu~Ófó  Aõ  Bð øô ò RÜ�‰Ð?À	¸{È"ÈQÈCÐP×QÑQûðRúó   ‚BB Â	B4ÂB/Â/B4c                 ón  — 	 |j                  d| f«       |j                  «       s`t        j                  d| › d�«       |j                  t	        j
                  d«      j                  t	        j                  | «      «      «       yy# t        $ r%}t        j                  d| › d|› �«       Y d}~yd}~ww xY w)z
    Internal helper to add the category column to the tickets table if missing.
    Used for self-healing legacy tenants.
    z�SELECT column_name FROM information_schema.columns WHERE table_schema = %s AND table_name = 'strategist_tickets' AND column_name = 'category'z(Self-healing: Adding category column to r{   z:ALTER TABLE {}.strategist_tickets ADD COLUMN category TEXTz%Failed to ensure category column for rO   Nr|   r}   s      r   Úensure_category_columnr€   ã  s°   € ð
QØ�‰ð  dð  hqð  gsô  	tØ�|‰|Œ~Ü�K‰KÐBÀ9À+ÐM`ÐaÔbØ�K‰KœŸ™Ð \Ó]×dÑdÔeh×esÑesÐt}Óe~Óõ  Að øô ò QÜ�‰Ð>¸y¸kÈÈAÈ3ÐO×PÑPûðQúr~   ÚcontentÚ	embeddingÚmetadatac           	      óL  — t         j                  d| › �«       ddlm} t	        |t
        «      r|j                  d«      nd }t        t        j                  |xs i «      j                  d«      «      }t        |xs dj                  d«      «      }t        |xs g «      dz  }t        d||z   |z   «      }	 || d	||	¬
«       t        «       }
	 |
j                  «       5 }t        j                  d«      j!                  t        j"                  | «      «      }|j%                  |||t        j                  |«      f«       |
j'                  «        	 ddlm} t-        | «      } || |«       	 d d d «       |
j3                  «        y# t.        $ r%}t         j1                  d| › d|› �«       Y d }~ŒCd }~ww xY w# 1 sw Y   nxY wn@# t.        $ r4}|
j5                  «        t         j7                  d| › d|› �d¬«       |‚d }~ww xY w	 |
j3                  «        y # |
j3                  «        w xY w)Nz"Inserting vector data for tenant: r   )Úcheck_quotaÚuser_idzutf-8rI   é   r&   Ústorage_vector_capacity)r†   Úrequested_amountz‰
                INSERT INTO {}.strategist_knowledge_base (content, embedding, metadata)
                VALUES (%s, %s, %s)
            ©Úlog_storage_snapshotú#Failed to log storage snapshot for rO   Tz(Failed to insert vector data for tenant r*   )r5   rS   Úapp.services.infra.quotasr…   r   r   r   ÚlenÚjsonÚdumpsÚencodeÚmaxrA   rT   r   rV   r   rW   rU   r[   Ú#app.services.integrations.firestorer‹   Úget_vector_db_sizer:   r6   r]   r^   r9   )rF   r�   r‚   rƒ   r…   r†   Úmetadata_sizeÚcontent_sizeÚembedding_sizeÚprojected_row_sizer?   r_   rf   r‹   Únew_sizeÚser@   s                    r   Úinsert_vector_datar›   ð  sî  € Ü
‡K�KÐ4°Y°KÐ@ÔAõ 6Ü)3°H¼dÔ)Cˆh�l‰l˜9Ô%È€GÜœŸ
™
 8¢>¨rÓ2×9Ñ9¸'ÓBÓC€MÜ˜š 2×-Ñ-¨gÓ6Ó7€LÜ˜š bÓ)¨AÑ-€NÜ˜Q ¨}Ñ <¸~Ñ MÓNÐÙØØ!ØØ+õ	ô Ó€DðØ�[‰[‹]ð 	˜cÜŸ7™7ð $ó ÷ ‘œŸ™ yÓ1Ó2ð ð
 �K‰K˜ w°	¼4¿:¹:ÀhÓ;OÐ&PÔQØ�K‰KŒMðXÝTÜ-¨iÓ8�Ù$ Y°Ô9ð ÷#	ð. 	�
‰
�øô ò XÜ—‘Ð!DÀYÀKÈrÐRTÐQUÐV×WÑWûðXú÷	ð 	úð 	øô$ ò Ø�‰ŒÜ�‰Ð?À	¸{È"ÈQÈCÐPÐ[_ˆÔ`Øˆûðúð%	ð. 	�
‰
�øˆ�
‰
�úsm   ÃF? ÃA0F2ÅFÅ'F2Å(F? Æ	F/Æ
F*Æ%F2Æ*F/Æ/F2Æ2F;Æ7F? Æ>H Æ?	G<Ç/G7Ç7G<Ç<H ÈH#Úingestion_typeÚsummaryc                 óB  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  |||f«       |j                  «        	 d d d «       |j                  «        y# 1 sw Y   nxY wn@# t        $ r4}|j                  «        t         j                  d| › d|› �d¬«       |‚d }~ww xY w	 |j                  «        y # |j                  «        w xY w)NzInserting summary for tenant: ú, type: z{
                INSERT INTO {}.strategist_summaries (ingestion_type, summary)
                VALUES (%s, %s)
            Tz$Failed to insert summary for tenant rO   r*   ©r5   rS   rA   rT   r   rV   r   rW   rU   r[   r]   r:   r^   r9   )rF   rœ   r�   r?   r_   rf   r@   s          r   Úinsert_summaryr¡     sÿ   € Ü
‡K�KÐ0°°¸8ÀNÐCSÐTÔUÜÓ€DðØ�[‰[‹]ð 	˜cÜŸ7™7ð $ó ÷ ‘œŸ™ yÓ1Ó2ð ð
 �K‰K˜ ~°wÐ&?Ô@Ø�K‰KŒMØ÷	ð 	�
‰
�÷	ð 	úð 	øô ò Ø�‰ŒÜ�‰Ð;¸I¸;ÀbÈÈÐLÐW[ˆÔ\Øˆûðúð	ð 	�
‰
�øˆ�
‰
�úsA   §B: ·AB-ÂB: Â-B6Â2B: Â9D Â:	C7Ã/C2Ã2C7Ã7D ÄDÚ	source_idÚurlc                 óh  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  |||f«       |j                  «        	 d d d «       |j                  «        y# 1 sw Y   nxY wnS# t        $ rG}|j                  «        t         j                  d| › d|› �d¬«       Y d }~|j                  «        yd }~ww xY w	 |j                  «        y # |j                  «        w xY w)	Nz"Inserting crawled URL for tenant: z, source_id: zu
                INSERT INTO {}.strategist_crawled_urls (source_id, url)
                VALUES (%s, %s)
            Tz(Failed to insert crawled URL for tenant rO   r*   Fr    )rF   r¢   r£   r?   r_   rf   r@   s          r   Úinsert_crawled_urlr¥   0  s  € Ü
‡K�KÐ4°Y°K¸}ÈYÈKÐXÔYÜÓ€DðØ�[‰[‹]ð 	˜cÜŸ7™7ð $ó ÷ ‘œŸ™ yÓ1Ó2ð ð
 �K‰K˜ y°#Ð&6Ô7Ø�K‰KŒMØ÷	ð 	�
‰
�÷	ð 	úð 	øô ò Ø�‰ŒÜ�‰Ð?À	¸{È"ÈQÈCÐPÐ[_ˆÔ`Ûà�
‰
�ûðúð	ð 	�
‰
�øˆ�
‰
�úsG   §B: ·AB-ÂB: Â-B6Â2B: Â9D Â:	D
Ã-DÃ0D ÄD
Ä
D ÄD1Úquery_embeddingÚlimitc                 ó|  — t         j                  d| › �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       s4t         j                  d| › d�«       g cddd«       |j                  «        S t        j                  d«      j                  t        j                  | «      «      }ddj                  t        t        |«      «      z   d	z   }|j	                  |||f«       |j                  «       }g }|D ],  }	|j!                  |	d
   |	d   dt#        |	d   «      z
  dœ«       Œ. |cddd«       |j                  «        S # 1 sw Y   nxY wn0# t$        $ r$}
t         j'                  d| › d|
› �d¬«       |
‚d}
~
ww xY w	 |j                  «        y# |j                  «        w xY w)zL
    Performs cosine similarity search on the tenant's vector database.
    zSearching vectors for tenant: úy
                SELECT schema_name FROM information_schema.schemata 
                WHERE schema_name = %s
            úTenant schema z does not exist for search.Nz×
                SELECT content, metadata, (embedding <=> CAST(%s AS vector)) as distance
                FROM {}.strategist_knowledge_base
                ORDER BY distance ASC
                LIMIT %s
            ú[ú,ú]r   r&   é   )r�   rƒ   Úscorez$Failed to search vectors for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r6   r]   r   rV   r   rW   ro   ÚmapÚstrÚfetchallrn   Úfloatr:   r9   )rF   r¦   r§   r?   r_   rp   Ú
vector_strÚresultsÚsearch_hitsrq   r@   s              r   Úsearch_vector_datar·   D  s·  € ô ‡K�KÐ0°°Ð<Ô=ÜÓ€Dð$Ø�[‰[‹]ð 	˜cà�K‰Kð ð �ôð —<‘<”>Ü—‘ °	¨{Ð:UÐVÔWØ÷	ð 	ðF 	�
‰
�ô3 —G‘Gð ó ÷
 ‘œŸ™ yÓ1Ó2ð ð ˜sŸx™x¬¬C°Ó(AÓBÑBÀSÑHˆJØ�K‰K˜ 
¨EÐ2Ô3Ø—l‘l“nˆGð ˆKØò �Ø×"Ñ"Ø" 1™vØ # A¡Ø¤ s¨1¡v£Ñ.ñ$õ ðð ÷=	ð 	ðF 	�
‰
�÷G	ð 	úð 	øô> ò Ü�‰Ð;¸I¸;ÀbÈÈÐLÐW[ˆÔ\Øˆûðúð?	ðF 	�
‰
�øˆ�
‰
�úsM   ¤E' ´>EÁ2	E' ÂB4EÅ 	E' ÅE#ÅE' Å&F) Å'	FÅ0FÆFÆF) Æ)F;c                 ó¢  — t        «       }	 |j                  «       5 }|j                  d| f«       |j                  «       d   cddd«       |j	                  «        S # 1 sw Y   nxY wnA# t
        $ r5}t        j                  d| › d|› �«       Y d}~|j	                  «        yd}~ww xY w	 |j	                  «        y# |j	                  «        w xY w)zKWhether this tenant has been bootstrapped since strategist_settings landed.útSELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = %s AND table_name = 'strategist_settings')r   Nz'Could not check the settings table for rO   F)rA   rT   rU   rm   r]   r:   r5   r9   ©rF   r?   r_   r@   s       r   Úsettings_table_existsr»   p  s½   € äÓ€DðØ�[‰[‹]ð 	%˜cØ�K‰KðRà�ôð
 —<‘<“> !Ñ$÷	%ð 	%ð 	�
‰
�÷	%ð 	%úð 	%øô ò Ü�‰Ð>¸y¸kÈÈAÈ3ÐOÔPÛà�
‰
�ûð	úð	%ð 	�
‰
�øˆ�
‰
�úsF   ŒA) œ&AÁ	A) ÁA%Á!A) Á(B< Á)	B'Á2B"ÂB< Â"B'Â'B< Â<CÚkeyc           	      óž  — t        «       }	 |j                  «       5 }|j                  d| f«       |j                  «       d   s	 ddd«       |j	                  «        y|j                  t        j                  d«      j                  t        j                  | «      «      |f«       |j                  «       }|r|d   ndcddd«       |j	                  «        S # 1 sw Y   nxY wnD# t        $ r8}t        j                  d|› d| › d|› �«       Y d}~|j	                  «        yd}~ww xY w	 |j	                  «        y# |j	                  «        w xY w)z>The stored value for a settings key, or None if there is none.r¹   r   Nz7SELECT value FROM {}.strategist_settings WHERE key = %szCould not read setting 'ú' for rO   )rA   rT   rU   rm   r]   r   rV   r   rW   r:   r5   r9   )rF   r¼   r?   r_   rq   r@   s         r   Úget_settingr¿   ‚  s   € äÓ€DðØ�[‰[‹]ð 	+˜cØ�K‰KðRà�ôð
 —<‘<“> !Ò$Ø÷	+ð" 	�
‰
�ð �K‰KÜ—‘ÐQÓRß‘œŸ™ yÓ1Ó2°S°Fô<ð —,‘,“.ˆCÙ �3�q’6 d÷	+ð 	+ð" 	�
‰
�÷#	+ð 	+úð 	+øô ò Ü�‰Ð/°¨u°F¸9¸+ÀRÈÀsÐKÔLÛà�
‰
�ûð	úð	+ð" 	�
‰
�øˆ�
‰
�úsS   ŒC$ œ(CÁC$ ÁA CÂ=	C$ ÃC ÃC$ Ã#D: Ã$	D%Ã-D ÄD: Ä D%Ä%D: Ä:Ec                 ój  — t        «       }	 |j                  «       5 }|j                  t        j                  d«      j                  t        j                  | «      «      |t        i «      f«       |j                  t        j                  d«      j                  t        j                  | «      «      |f«       |j                  «       }|r
|d   r|d   nd} ||«      }|j                  t        j                  d«      j                  t        j                  | «      «      t        |«      |f«       ddd«       |j                  «        |j                  «        S # 1 sw Y   Œ+xY w# t        $ r/ |j                  «        t        j                  d|› d| › �d¬	«       ‚ w xY w# |j                  «        w xY w)
aç  Read-modify-write a settings key under a row lock.

    `merge` receives the currently stored value (or None) and returns the value
    to store. It runs inside the transaction that holds the lock, so two
    concurrent saves cannot each read the old value and overwrite each other --
    the second waits, then merges on top of the first one's result.

    The empty INSERT first is what makes the lock possible: SELECT FOR UPDATE
    locks nothing when the row does not exist yet.
    z\INSERT INTO {}.strategist_settings (key, value) VALUES (%s, %s) ON CONFLICT (key) DO NOTHINGzBSELECT value FROM {}.strategist_settings WHERE key = %s FOR UPDATEr   Nz[UPDATE {}.strategist_settings SET value = %s, updated_at = CURRENT_TIMESTAMP WHERE key = %szCould not save setting 'r¾   Tr*   )rA   rT   rU   r   rV   r   rW   r   rm   r[   r]   r:   r^   r5   r9   )rF   r¼   Úmerger?   r_   rq   ÚcurrentÚmergeds           r   Úupdate_settingrÄ   š  si  € ô Ó€DðØ�[‰[‹]ð 	I˜cØ�K‰KÜ—‘ð 7ó 8ç‘œŸ™ yÓ1Ó2°S¼$¸r»(°OôEð �K‰KÜ—‘Ð\Ó]ß‘œŸ™ yÓ1Ó2°S°Fô<ð —,‘,“.ˆCÙ #¨¨Aª�c˜!’f°DˆGá˜7“^ˆFà�K‰KÜ—‘ð Hó Iç‘œŸ™ yÓ1Ó2´T¸&³\À3Ð4GôI÷	Ið" 	�‰ŒØð 	�
‰
�÷1	Ið 	Iûô& ò Ø�‰ŒÜ�‰Ð/°¨u°F¸9¸+ÐFÐQUˆÔVØðûð
 	�
‰
�ús/   ŒE% œDEÄ/E% ÅE"ÅE% Å%8FÆF  Æ F2c                 ó  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       s3t         j                  d| › d�«       	 d d d «       |j                  «        y t        j                  d«      j                  t        j                  | «      «      }|j	                  ||f«       |j                  «       }|r
|d   |d   fnd	cd d d «       |j                  «        S # 1 sw Y   nxY wn@# t        $ r4}t         j                  d
|› �d¬«       Y d }~|j                  «        y	d }~ww xY w	 |j                  «        y # |j                  «        w xY w)Nz$Fetching latest summary for tenant: rŸ   r©   rª   z does not exist.zÀ
                SELECT summary, created_at FROM {}.strategist_summaries
                WHERE ingestion_type = %s
                ORDER BY created_at DESC
                LIMIT 1
            r   r&   ©NNzError fetching summary: Tr*   )r5   rS   rA   rT   rU   rm   r6   r]   r   rV   r   rW   r:   r9   )rF   rœ   r?   r_   rp   Úresultr@   s          r   Úget_latest_summaryrÈ   Â  sb  € Ü
‡K�KÐ6°y°kÀÈ.ÐIYÐZÔ[ÜÓ€DðØ�[‰[‹]ð 	F˜cà�K‰Kð ð �ôð —<‘<”>Ü—‘ °	¨{Ð:JÐKÔLØ÷	Fð0 	�
‰
�ô —G‘Gð ó ÷
 ‘œŸ™ yÓ1Ó2ð ð �K‰K˜ Ð0Ô1Ø—\‘\“^ˆFÙ-3�F˜1‘I˜v a™yÑ)¸÷'	Fð 	Fð0 	�
‰
�÷1	Fð 	Fúð 	Føô( ò Ü�‰Ð/°¨sÐ3¸dˆÔCÛà�
‰
�ûð	úð)	Fð0 	�
‰
�øˆ�
‰
�úsS   §D ·>DÁ5D ÂA'DÃ5	D ÄDÄD ÄE. Ä	EÄ%EÄ?E. ÅEÅE. Å.F Úticket_dataÚ	thread_idc                 óö  — t         j                  d| › d|› �«       ddlm}m} t        |t        «      r|j                  d«      nd} || d|¬«       t        «       }	 |j                  «       5 }t        j                  d	«      j                  t        j                  | «      «      }	 |j                  ||d
   |d   |d   |d   |d   d|j                  d«      |j                  d«      xs |j                  d«      |j                  d«      xs |j                  d«      ||j                  d«      xs df«       |j/                  «       d   }|j-                  «        |cddd«       |j1                  «        S # t        $ �rj}	dt!        |	«      j#                  «       v �rAdt!        |	«      j#                  «       v sdt!        |	«      j#                  «       v �r
t         j%                  d| › d�«       |j'                  «        |j                  «       5 }
t)        | |
«       t+        | |
«       |j-                  «        ddd«       n# 1 sw Y   nxY w|j                  ||d
   |d   |d   |d   |d   d|j                  d«      |j                  d«      xs |j                  d«      |j                  d«      xs |j                  d«      ||j                  d«      xs df«       n|	‚Y d}	~	�Œ­d}	~	ww xY w# 1 sw Y   nxY wnZ# |$ r ‚ t        $ rI}	|r|j'                  «        t         j3                  d| › d|	› �d¬«       Y d}	~	|j1                  «        yd}	~	ww xY w	 |j1                  «        y# |j1                  «        w xY w)zˆ
    Inserts a new support ticket into the tenant-specific table.
    Self-healing: Automatically adds thread_id column if missing.
    zCreating ticket for tenant: ú
, thread: r   )r…   ÚQuotaExceededErrorr†   NÚtotal_tickets)r†   a  
                INSERT INTO {}.strategist_tickets (ticket_id, user_name, heading, content, priority, status, email, contact_no, contact_medium, thread_id, category)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id
            Ú	ticket_idÚ	user_nameÚheadingr�   ÚpriorityÚOpenÚemailÚ
contact_noÚphone_numberÚcontact_mediumÚany_other_contact_mediumÚcategoryzGeneral enquiriesÚcolumnrÊ   ú*Self-healing: Adding missing column(s) to z during creation.z!Error creating ticket for tenant rO   Tr*   )r5   rS   r�   r…   rÍ   r   r   r   rA   rT   r   rV   r   rW   rU   r:   r±   Úlowerr6   r^   rZ   r€   r[   rm   r]   r9   )rF   rÉ   rÊ   r…   rÍ   r†   r?   r_   rp   r@   Ú
repair_curÚnew_ids               r   Úcreate_ticketrß   à  s1  € ô
 ‡K�KÐ.¨y¨k¸ÀIÀ;ÐOÔP÷ JÜ,6°{ÄDÔ,Iˆk�o‰o˜iÔ(Èt€GÙ�	˜?°GÕ<äÓ€Dð;Ø�[‰[‹]ð 0	˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð%Ø—‘˜EØ Ñ,Ø Ñ,Ø 	Ñ*Ø 	Ñ*Ø 
Ñ+ØØ—O‘O GÓ,Ø—O‘O LÓ1ÒT°[·_±_À^Ó5TØ—O‘OÐ$4Ó5Òd¸¿¹ÐIcÓ9dØØ—O‘O JÓ/ÒFÐ3Fð$ô ðL —\‘\“^ AÑ&ˆFØ�K‰KŒMØ÷a0	ð 0	ðt 	�
‰
�øôK ó Øœs 1›vŸ|™|›~Ò-°;Ä#ÀaÃ&Ç,Á,Ã.Ñ3PÐT^ÔbeÐfgÓbh×bnÑbnÓbpÒTpÜ—N‘NÐ%OÐPYÈ{ÐZkÐ#lÔmØ—M‘M”OØŸ™›ð &¨*Ü/°	¸:ÔFÜ.¨y¸*ÔEØŸ™œ÷&÷ &ñ &úð
 —K‘K Ø# KÑ0Ø# KÑ0Ø# IÑ.Ø# IÑ.Ø# JÑ/ØØ#Ÿ™¨Ó0Ø#Ÿ™¨Ó5ÒX¸¿¹ÈÓ9XØ#Ÿ™Ð(8Ó9Òh¸[¿_¹_ÐMgÓ=hØ!Ø#Ÿ™¨
Ó3ÒJÐ7Jð(õ ð �Gõûðú÷+0	ð 0	úð 0	øðb ò àÜò ÙØ�M‰MŒOÜ�‰Ð8¸¸À2ÀaÀSÐIÐTXˆÔYÛà�
‰
�ûðúði0	ðt 	�
‰
�øˆ�
‰
�ús�   ÁK: Á-8K-Â&BE6Ä8$K-Å	K: Å6
K*Æ BK%È)H>È5	K%È>IÉBK%ËK-Ë%K*Ë*K-Ë-K6Ë2K: Ë9M& Ë:MÌ/MÌ7M& ÍMÍM& Í&M8c           	      ó¦  — t         j                  d|› d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }	 |j                  ||f«       |j#                  «       }|r8|d
   |d   |d   |d   |d   |d   |d   dœcd	d	d	«       |j%                  «        S 	 d	d	d	«       |j%                  «        y	# t        $ ré}dt        |«      j                  «       v rÁdt        |«      j                  «       v sdt        |«      j                  «       v r‹t         j                  d| › d�«       |j                  «        |j                  «       5 }t        | |«       t        | |«       |j!                  «        d	d	d	«       n# 1 sw Y   nxY w|j                  ||f«       n|‚Y d	}~�ŒQd	}~ww xY w# 1 sw Y   nxY wnC# t        $ r7}t         j'                  d|› d|› �d¬«       Y d	}~|j%                  «        y	d	}~ww xY w	 |j%                  «        y	# |j%                  «        w xY w)z…
    Finds an existing ticket for a given conversation thread.
    Self-healing: Automatically adds thread_id column if missing.
    zGetting ticket for thread ú in tenant zzSELECT id, ticket_id, heading, content, priority, status, category FROM {}.strategist_tickets WHERE thread_id = %s LIMIT 1rÚ   rÊ   rÙ   rÛ   z during fetch-by-thread.Nr   r&   r®   é   é   é   é   )ÚidrÏ   rÑ   r�   rÒ   ÚstatusrÙ   z Error fetching ticket by thread rO   Tr*   )r5   rS   rA   rT   r   rV   r   rW   rU   r:   r±   rÜ   r6   r^   rZ   r€   r[   rm   r]   r9   )rF   rÊ   r?   r_   rp   r@   rÝ   rq   s           r   Úget_ticket_by_threadrè   *  s;  € ô
 ‡K�KÐ,¨Y¨K°{À9À+ÐNÔOÜÓ€Dð"Ø�[‰[‹]ð 	˜cÜ—G‘Gð  Yó  Z÷  añ  aô  be÷  bpñ  bpð  qzó  b{ó  |ˆEðØ—‘˜E I <Ô0ð —,‘,“.ˆCÙà˜a™&Ø!$ Q¡Ø" 1™vØ" 1™vØ # A¡Ø! !™fØ # A¡ñ÷'	ð 	ðB 	�
‰
�ð ÷9	ðB 	�
‰
�øô; ò Øœs 1›vŸ|™|›~Ñ-°;Ä#ÀaÃ&Ç,Á,Ã.Ñ3PÐT^ÔbeÐfgÓbh×bnÑbnÓbpÑTpÜ—N‘NÐ%OÐPYÈ{ÐZrÐ#sÔtØ—M‘M”OØŸ™›ð &¨*Ü/°	¸:ÔFÜ.¨y¸*ÔEØŸ™œ÷&÷ &ñ &úð
 —K‘K ¨	 |Õ4à�Gõ 5ûðú÷		ð 	úð 	øô: ò Ü�‰Ð7¸	°{À"ÀQÀCÐHÐSWˆÔXÛà�
‰
�ûð	úð;	ðB 	�
‰
�øˆ�
‰
�ús•   §G) ·8GÁ0C'Â0GÂ3	G) ÃGÃG) Ã'	GÃ0B
GÅ:)F,Æ#	GÆ,F5Æ1GÇGÇGÇGÇG%Ç!G) Ç(H> Ç)	H)Ç2H$ÈH> È$H)È)H> È>IrÏ   Úupdate_datac           
      óž  — t         j                  d|› d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  ||d   |d   |d   |j                  d«      |f«       |j                  «        	 ddd«       |j                  «        y	# 1 sw Y   nxY wnS# t        $ rG}t         j                  d
|› d|› �d	¬«       |j                  «        Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w)z6
    Updates an existing ticket with new details.
    zUpdating ticket ú for tenant: zè
                UPDATE {}.strategist_tickets
                SET heading = %s,
                    content = %s,
                    priority = %s,
                    category = %s
                WHERE ticket_id = %s
            rÑ   r�   rÒ   rÙ   NTzFailed to update ticket rO   r*   F)r5   rS   rA   rT   r   rV   r   rW   rU   r   r[   r]   r:   r9   r^   )rF   rÏ   ré   r?   r_   rp   r@   s          r   Úupdate_ticketrì   U  s/  € ô ‡K�KÐ" 9 +¨]¸9¸+ÐFÔGÜÓ€DðØ�[‰[‹]ð 	˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð ð �K‰K˜Ø˜IÑ&Ø˜IÑ&Ø˜JÑ'Ø—‘ 
Ó+Øð ô ð �K‰KŒMØ÷%	ð0 	�
‰
�÷1	ð 	úð 	øô& ò Ü�‰Ð/°	¨{¸"¸Q¸CÐ@È4ˆÔPØ�‰ŒÛà�
‰
�ûðúð'	ð0 	�
‰
�øˆ�
‰
�úsG   §C ·A8CÂ/C ÃCÃC ÃD: Ã	D%Ã-D ÄD: Ä D%Ä%D: Ä:Erç   c                 óŠ  — t         j                  d|› d|› d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  |||f«       |j                  «        |j                  dkD  cddd«       |j                  «        S # 1 sw Y   nxY wnS# t        $ rG}t         j                  d|› d|› �d	¬
«       |j                  «        Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w)z3
    Updates the status of an existing ticket.
    zUpdating status of ticket z to 'z' for tenant: z
                UPDATE {}.strategist_tickets
                SET status = %s
                WHERE ticket_id = %s
            r   Nz#Failed to update status for ticket rO   Tr*   F)r5   rS   rA   rT   r   rV   r   rW   rU   r[   Úrowcountr]   r:   r9   r^   )rF   rÏ   rç   r?   r_   rp   r@   s          r   Úupdate_ticket_statusrï   v  s  € ô ‡K�KÐ,¨Y¨K°u¸V¸HÀNÐS\ÐR]Ð^Ô_ÜÓ€DðØ�[‰[‹]ð 		$˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð �K‰K˜ ¨	Ð2Ô3Ø�K‰KŒMØ—<‘< !Ñ#÷		$ð 		$ð 	�
‰
�÷		$ð 		$úð 		$øô ò Ü�‰Ð:¸9¸+ÀRÈÀsÐKÐVZˆÔ[Ø�‰ŒÛà�
‰
�ûðúð		$ð 	�
‰
�øˆ�
‰
�úsG   ªC ºA*B>Â$	C Â>CÃC Ã
D0 Ã	DÃ-DÄD0 ÄDÄD0 Ä0Ec                 óˆ  — t         j                  d|› d| › �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       s	 ddd«       |j                  «        yt        j                  d«      j                  t        j                  | «      «      }	 |j	                  ||f«       |j                  «       }|ri|d   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   r|d   j'                  «       nd|d   |d   dœcddd«       |j                  «        S 	 ddd«       |j                  «        y# t        $ ré}dt        |«      j                  «       v rÁdt        |«      j                  «       v sdt        |«      j                  «       v r‹t         j                  d	| › d
�«       |j                  «        |j                  «       5 }t!        | |«       t#        | |«       |j%                  «        ddd«       n# 1 sw Y   nxY w|j	                  ||f«       n|‚Y d}~�Œ‚d}~ww xY w# 1 sw Y   nxY wnF# t        $ r:}t         j)                  d|› d| › d|› �d¬«       Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w)z5
    Retrieves a single ticket by its ticket_id.
    zFetching ticket rë   úJSELECT schema_name FROM information_schema.schemata WHERE schema_name = %sNzÈSELECT id, ticket_id, user_name, heading, content, priority, status, email, contact_no, contact_medium, contact_details, created_at, thread_id, category FROM {}.strategist_tickets WHERE ticket_id = %srÚ   rÊ   rÙ   rÛ   z during ticket fetch.r   r&   r®   râ   rã   rä   rå   é   r‡   é	   r   é   é   é   ©ræ   rÏ   rÐ   rÑ   r�   rÒ   rç   rÔ   rÕ   r×   Úcontact_detailsÚ
created_atrÊ   rÙ   zError fetching ticket ú for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   r:   r±   rÜ   r6   r^   rZ   r€   r[   Ú	isoformatr9   )rF   rÏ   r?   r_   rp   r@   rÝ   rq   s           r   Ú
get_ticketrü   Ž  s¼  € ô ‡K�KÐ" 9 +¨]¸9¸+ÐFÔGÜÓ€Dð/Ø�[‰[‹]ð )	˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷	)	ð\ 	�
‰
�ôQ —G‘Gð  gó  h÷  oñ  oô  ps÷  p~ñ  p~ð  Hó  pIó  JˆEðØ—‘˜E I <Ô0ð —,‘,“.ˆCáà˜a™&Ø!$ Q¡Ø!$ Q¡Ø" 1™vØ" 1™vØ # A¡Ø! !™fØ  ™VØ"% a¡&Ø&)¨!¡fØ'*¨2¡wØ9<¸Rº # b¡'×"3Ñ"3Ô"5ÀdØ!$ R¡Ø # B¡ñ÷3)	ð )	ð\ 	�
‰
�ð ÷S)	ð\ 	�
‰
�øôK ò Øœs 1›vŸ|™|›~Ñ-°;Ä#ÀaÃ&Ç,Á,Ã.Ñ3PÐT^ÔbeÐfgÓbh×bnÑbnÓbpÑTpÜ—N‘NÐ%OÐPYÈ{ÐZoÐ#pÔqØ—M‘M”OØŸ™›ð &¨*Ü/°	¸:ÔFÜ.¨y¸*ÔEØŸ™œ÷&÷ &ñ &úð
 —K‘K ¨	 |Õ4à�Gõ 5ûðú÷)	ð )	úð )	øôT ò Ü�‰Ð-¨i¨[¸ÀYÀKÈrÐRSÐQTÐUÐ`dˆÔeÛà�
‰
�ûð	úðU)	ð\ 	�
‰
�øˆ�
‰
�ús¢   §I ·%I
ÁI Á57I
Â-EÃ A!I
Ä!	I Ä;I
Ä<I Å	IÅB
IÇ()HÈ	IÈH#ÈIÈ<I
ÉIÉI
É
IÉI ÉJ/ É	JÉ  JÊ J/ ÊJÊJ/ Ê/Kc                 óv  — t         j                  d| › �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       sg cddd«       |j                  «        S 	 t        j                  d«      j                  t        j                  | «      «      }|j	                  |«       |j'                  «       }g }|D ]a  }|j)                  |d
   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   r|d   j+                  «       nd|d   |d   dœ«       Œc |cddd«       |j                  «        S # t        $ rç}dt        |«      j                  «       v r¿dt        |«      j                  «       v sdt        |«      j                  «       v r‰t         j                  d| › d	�«       |j                  «        |j                  «       5 }t!        | |«       t#        | |«       |j%                  «        ddd«       n# 1 sw Y   nxY w|j	                  «       n|‚Y d}~�Œ~d}~ww xY w# 1 sw Y   nxY wnE# t        $ r9}t         j-                  d| › d|› �d¬«       g cY d}~|j                  «        S d}~ww xY w	 |j                  «        y# |j                  «        w xY w)z—
    Retrieves all tickets for a specific tenant.
    Self-healing: If thread_id is missing (legacy tenant), it applies the migration and retries.
    z!Fetching all tickets for tenant: rñ   NzÌSELECT id, ticket_id, user_name, heading, content, priority, status, email, contact_no, contact_medium, contact_details, created_at, thread_id, category FROM {}.strategist_tickets ORDER BY created_at DESCrÚ   rÊ   rÙ   zDetected missing column(s) in z. Applying migration...r   r&   r®   râ   rã   rä   rå   rò   r‡   ró   r   rô   rõ   rö   r÷   z"Error fetching tickets for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   r:   r±   rÜ   r6   r^   rZ   r€   r[   r²   rn   rû   r9   )	rF   r?   r_   rp   r@   rÝ   ÚrowsÚticketsrq   s	            r   Úget_ticketsr   Å  s¶  € ô
 ‡K�KÐ3°I°;Ð?Ô@ÜÓ€Dð0Ø�[‰[‹]ð *	˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷	*	ð *	ð^ 	�
‰
�ðSÜŸ™ð  !oó  p÷  wñ  wô  x{÷  xFñ  xFð  GPó  xQó  R�Ø—‘˜EÔ"ð —<‘<“>ˆDØˆGØò �Ø—‘Ø˜a™&Ø!$ Q¡Ø!$ Q¡Ø" 1™vØ" 1™vØ # A¡Ø! !™fØ  ™VØ"% a¡&Ø&)¨!¡fØ'*¨2¡wØ9<¸Rº # b¡'×"3Ñ"3Ô"5ÀdØ!$ R¡Ø # B¡ñ õ ðð" ÷U*	ð *	ð^ 	�
‰
�øôM ò àœs 1›vŸ|™|›~Ñ-°;Ä#ÀaÃ&Ç,Á,Ã.Ñ3PÐT^ÔbeÐfgÓbh×bnÑbnÓbpÑTpÜ—N‘NÐ%CÀIÀ;ÐNeÐ#fÔgØ—M‘M”OØŸ™›ð &¨*Ü/°	¸:ÔFÜ.¨y¸*ÔEØŸ™œ÷&÷ &ñ &úð
 —K‘K Õ&à�Gõ 'ûðú÷*	ð *	úð *	øôV ò Ü�‰Ð9¸)¸ÀBÀqÀcÐJÐUYˆÔZØŒ	à�
‰
�ûð	úðW*	ð^ 	�
‰
�øˆ�
‰
�ús—   ¤I ´%IÁ	I Á4AEÂ<A9IÄ5	I Å	H?ÅB
H:Ç")HÈ	H:ÈHÈH:È4IÈ:H?È?IÉIÉI ÉJ& É	JÉJÉ6JÉ7J& ÊJÊJ& Ê&J8Ú
admin_nameÚcommentc           	      ó®  — t         j                  d|› d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  ||||f«       |j                  «       }|j                  «        |d   ||||d   r|d   j                  «       nddœcddd«       |j                  «        S # 1 sw Y   nxY wnC# t        $ r7}t         j                  d|› d	| › d
|› �d¬«       |j                  «        |‚d}~ww xY w	 |j                  «        y# |j                  «        w xY w)z6
    Inserts a new comment for a specific ticket.
    zAdding comment to ticket rë   zµ
                INSERT INTO {}.strategist_ticket_comments (ticket_id, admin_name, comment)
                VALUES (%s, %s, %s)
                RETURNING id, created_at
            r   r&   N©ræ   rÏ   r  r  rù   z Failed to add comment to ticket rú   rO   Tr*   )r5   rS   rA   rT   r   rV   r   rW   rU   rm   r[   rû   r]   r:   r9   r^   )	rF   rÏ   r  r  r?   r_   rp   rÇ   r@   s	            r   Úadd_ticket_commentr  þ  sE  € ô ‡K�KÐ+¨I¨;°mÀIÀ;ÐOÔPÜÓ€DðØ�[‰[‹]ð 	˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð �K‰K˜ 	¨:°wÐ?Ô@Ø—\‘\“^ˆFØ�K‰KŒMà˜Q‘iØ&Ø(Ø"Ø7=¸a²y˜f Q™i×1Ñ1Ô3Àdñ÷	ð 	ð, 	�
‰
�÷-	ð 	úð 	øô" ò Ü�‰Ð7¸	°{À,ÈyÈkÐY[Ð\]Ð[^Ð_ÐjnˆÔoØ�‰ŒØˆûðúð#	ð, 	�
‰
�øˆ�
‰
�úsA   §C- ·BC Ã	C- Ã C)Ã%C- Ã,E Ã-	D-Ã62D(Ä(D-Ä-E ÅEc           
      óV  — t         j                  d|› d| › �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       sg cddd«       |j                  «        S t        j                  d«      j                  t        j                  | «      «      }|j	                  ||f«       |j                  «       }g }|D ]=  }|j                  |d   |d   |d   |d	   |d
   r|d
   j                  «       nddœ«       Œ? |cddd«       |j                  «        S # 1 sw Y   nxY wnH# t        $ r<}t         j                  d|› d| › d|› �d¬«       g cY d}~|j                  «        S d}~ww xY w	 |j                  «        y# |j                  «        w xY w)z7
    Retrieves all comments for a specific ticket.
    zFetching comments for ticket z in tenant: rñ   Nz…SELECT id, ticket_id, admin_name, comment, created_at FROM {}.strategist_ticket_comments WHERE ticket_id = %s ORDER BY created_at ASCr   r&   r®   râ   rã   r  z#Error fetching comments for ticket rá   rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   r²   rn   rû   r:   r9   )	rF   rÏ   r?   r_   rp   rþ   Úcommentsrq   r@   s	            r   Úget_ticket_commentsr    s½  € ô ‡K�KÐ/°	¨{¸,ÀyÀkÐRÔSÜÓ€DðØ�[‰[‹]ð 	˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷		ð 	ð0 	�
‰
�ô% —G‘Gð  dó  e÷  lñ  lô  mp÷  m{ñ  m{ð  |Eó  mFó  GˆEØ�K‰K˜ 	˜|Ô,Ø—<‘<“>ˆDàˆHØò �Ø—‘Ø˜a™&Ø!$ Q¡Ø"% a¡&Ø" 1™vØ8;¸Aº # a¡&×"2Ñ"2Ô"4ÀDñ!õ ðð ÷'	ð 	ð0 	�
‰
�÷1	ð 	úð 	øô( ò Ü�‰Ð:¸9¸+À[ÐQZÐP[Ð[]Ð^_Ð]`ÐaÐlpˆÔqØŒ	à�
‰
�ûð	úð)	ð0 	�
‰
�øˆ�
‰
�úsY   §D< ·%D/Á	D< Á6BD/Ä	D< Ä/D8Ä4D< Ä;F Ä<	FÅ!E<Å&FÅ'F Å<FÆF ÆF(c                 óú  — t         j                  d| › �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       si cddd«       |j                  «        S t        j                  d«      j                  t        j                  | «      «      }|j	                  |«       |j                  «       }|D �ci c]  }|d   xs d|d   “Œ c}cddd«       |j                  «        S c c}w # 1 sw Y   nxY wnE# t        $ r9}t         j                  d| › d	|› �d
¬«       i cY d}~|j                  «        S d}~ww xY w	 |j                  «        y# |j                  «        w xY w)z=
    Returns a dictionary of status counts for a tenant.
    z*Fetching ticket status counts for tenant: rñ   NzBSELECT status, COUNT(*) FROM {}.strategist_tickets GROUP BY statusr   ÚUnknownr&   z/Error fetching ticket status counts for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   r²   r:   r9   )rF   r?   r_   rp   rþ   rq   r@   s          r   Úget_ticket_status_countsr  >  sd  € ô ‡K�KÐ<¸Y¸KÐHÔIÜÓ€DðØ�[‰[‹]ð 
	A˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷	
	Að 
	Að 	�
‰
�ô —G‘GÐ`Óa×hÑhÔil×iwÑiwð  yBó  jCó  DˆEØ�K‰K˜ÔØ—<‘<“>ˆDà;?Ö@°C�C˜‘FÒ'˜i¨¨Q©Ñ/Ò@÷
	Að 
	Að 	�
‰
�ùò A÷
	Að 
	Aúð 
	Aøô ò Ü�‰ÐFÀyÀkÐQSÐTUÐSVÐWÐbfˆÔgØŒ	à�
‰
�ûð	úð
	Að 	�
‰
�øˆ�
‰
�úsk   ¤D ´%DÁ	D Á3ADÃC?Ã#DÃ%	D Ã?DÄDÄ	D ÄE( Ä	EÄEÄ8EÄ9E( ÅEÅE( Å(E:Úfeedback_datac                 óà  — t         j                  d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  ||d   |j                  d«      |d   |d   t        j                  |j                  di «      «      f«       |j                  «       d   }|j                  «        |cd	d	d	«       |j                  «        S # 1 sw Y   nxY wn@# t        $ r4}t         j!                  d
| › d|› �d¬«       |j#                  «        |‚d	}~ww xY w	 |j                  «        y	# |j                  «        w xY w)zL
    Inserts user feedback into the tenant's strategist_feedback table.
    zInserting feedback for tenant: z¼
                INSERT INTO {}.strategist_feedback (thread_id, user_name, question, answer, metadata)
                VALUES (%s, %s, %s, %s, %s)
                RETURNING id
            rÊ   rÐ   ÚquestionÚanswerrƒ   r   Nz%Failed to insert feedback for tenant rO   Tr*   )r5   rS   rA   rT   r   rV   r   rW   rU   r   r�   r�   rm   r[   r]   r:   r9   r^   )rF   r  r?   r_   rp   Úfeedback_db_idr@   s          r   Úinsert_feedbackr  V  sP  € ô ‡K�KÐ1°)°Ð=Ô>ÜÓ€DðØ�[‰[‹]ð 	"˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð �K‰K˜Ø˜kÑ*Ø×!Ñ! +Ó.Ø˜jÑ)Ø˜hÑ'Ü—
‘
˜=×,Ñ,¨Z¸Ó<Ó=ð ô ð !Ÿ\™\›^¨AÑ.ˆNØ�K‰KŒMØ!÷!	"ð 	"ð, 	�
‰
�÷-	"ð 	"úð 	"øô" ò Ü�‰Ð<¸Y¸KÀrÈ!ÈÐMÐX\ˆÔ]Ø�‰ŒØˆûðúð#	"ð, 	�
‰
�øˆ�
‰
�úsA   ¤D	 ´B.C<Ã"	D	 Ã<DÄD	 ÄE Ä		EÄ/EÅEÅE ÅE-Úsourcec           	      óF  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       s	 ddd«       |j                  «        yt        j                  d«      j                  t        j                  | «      «      }|j	                  ||f«       |j                  }t        j                  d«      j                  t        j                  | «      «      }|j	                  ||f«       |j                  «        t         j                  d|› d	| › d|› �«       |dkD  r	 dd
lm} t        | «      } || |«       |cddd«       |j                  «        S # t         $ r%}	t         j#                  d| › d|	› �«       Y d}	~	ŒDd}	~	ww xY w# 1 sw Y   nxY wn@# t         $ r4}
t         j%                  d| › d|
› �d¬«       |j'                  «        |
‚d}
~
ww xY w	 |j                  «        y# |j                  «        w xY w)zC
    Deletes all vector data for a specific tenant and source.
    z!Deleting vector data for tenant: z
, source: rñ   Nr   zx
                DELETE FROM {}.strategist_knowledge_base
                WHERE metadata->>'source_id' = %s
            zi
                DELETE FROM {}.strategist_crawled_urls
                WHERE source_id = %s
            úDeleted ú) rows from strategist_knowledge_base for rŠ   rŒ   z after deletion: z(Failed to delete vector data for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   rî   r[   r“   r‹   r”   r:   r6   r9   r^   )rF   r  r?   r_   Údelete_queryÚdeleted_countÚdelete_crawled_queryr‹   r™   rš   r@   s              r   Údelete_vector_data_by_sourcer  u  s  € ô ‡K�KÐ3°I°;¸jÈÈÐQÔRÜÓ€Dð)Ø�[‰[‹]ð "	!˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷	"	!ðP 	�
‰
�ôC Ÿ7™7ð $ó ÷ ‘œŸ™ yÓ1Ó2ð ð
 �K‰K˜ v iÔ0ØŸL™LˆMô $'§7¡7ð ,ó $÷ ‘œŸ™ yÓ1Ó2ð !ð �K‰KÐ,¨v¨iÔ8à�K‰KŒMÜ�K‰K˜( = /Ð1ZÐ[dÐZeÐeoÐpvÐowÐxÔyð ˜qÒ ðkÝXÜ1°)Ó<�HÙ(¨°HÔ=ð !÷E"	!ð "	!ðP 	�
‰
�øô !ò kÜ—N‘NÐ%HÈÈÐSdÐegÐdhÐ#i×jÑjûðkú÷?"	!ð "	!úð "	!øôF ò Ü�‰Ð?À	¸{È"ÈQÈCÐPÐ[_ˆÔ`Ø�‰ŒØˆûðúðG"	!ðP 	�
‰
�øˆ�
‰
�úsw   §F< ·%F/ÁF< Á5CF/Å	E>Å#F/Å$	F< Å>	F,ÆF'Æ"F/Æ'F,Æ,F/Æ/F8Æ4F< Æ;H Æ<	G9Ç/G4Ç4G9Ç9H ÈH Úbase_urlc                 ón  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }|j	                  d«      }t        j                  d«      j                  t        j                  | «      «      }|j                  |||dz   f«       |j                  }t        j                  d«      j                  t        j                  | «      «      }|j                  |||dz   f«       |j                  «        t         j                  d|› d|› d	�«       |cd
d
d
«       |r|j                  «        S S # 1 sw Y   nxY wnB# t        $ r6}|r|j                  «        t         j                  d|› d|› �d¬«       |‚d
}~ww xY w	 |r|j                  «        y
y
# |r|j                  «        w w xY w)zš
    Deletes all vector data and crawled URLs for a base URL and its sub-paths.
    This is used to implement 'replacement' logic during re-analysis.
    z2Deleting vector data and crawled URLs for tenant: z, base_url: ú/zÒ
                DELETE FROM {}.strategist_knowledge_base
                WHERE metadata->>'ingestion_type' = 'website'
                AND (metadata->>'source' = %s OR metadata->>'source' LIKE %s)
            z/%zt
                DELETE FROM {}.strategist_crawled_urls
                WHERE (url = %s OR url LIKE %s)
            r  r  z and its sub-paths.Nz Failed to delete by subpath for rO   Tr*   )r5   rS   rA   rT   Úrstripr   rV   r   rW   rU   rî   r[   r]   r:   r^   r9   )	rF   r  r?   r_   Úurl_patternÚdelete_kb_queryÚkb_countÚdelete_urls_queryr@   s	            r   Ú!delete_vector_data_by_url_subpathr"  ¦  sš  € ô
 ‡K�KÐDÀYÀKÈ|Ð\dÐ[eÐfÔgÜÓ€DðØ�[‰[‹]ð 	˜cà"Ÿ/™/¨#Ó.ˆKô "Ÿg™gð 'ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð �K‰K˜¨+°{ÀTÑ7IÐ)JÔKØ—|‘|ˆHô !$§¡ð )ó !÷ ‘œŸ™ yÓ1Ó2ð ð
 �K‰KÐ)¨K¸ÀtÑ9KÐ+LÔMà�K‰KŒMÜ�K‰K˜( 8 *Ð,UÐV^ÐU_Ð_rÐsÔtØ÷1	ð 	ñ< �—‘•ˆ4÷=	ð 	úð 	øô2 ò Ù�—‘”Ü�‰Ð7¸°zÀÀAÀ3ÐGÐRVˆÔWØˆûðúð3	ñ< �—‘•ˆ4ø‰4�—‘•ˆ4úsA   §E ·C'D;Ä	E Ä;EÅ E ÅF Å	FÅ1FÆFÆF ÆF4Úsource_namec                 óÄ  — t        «       }	 |j                  «       5 }|j                  d| f«       |j                  «       sg cddd«       |j	                  «        S t        j                  d«      j                  t        j                  | «      «      }|j                  |||f«       |j                  «       D �cg c]  }|d   sŒ	|d   ‘Œ c}cddd«       |j	                  «        S c c}w # 1 sw Y   nxY wnC# t        $ r7}t        j                  d|› d|› �«       g cY d}~|j	                  «        S d}~ww xY w	 |j	                  «        y# |j	                  «        w xY w)z}
    Returns a list of source_ids matching a specific source name and type.
    Used for implementing replacement logic.
    rñ   NzÊ
                SELECT DISTINCT metadata->>'source_id'
                FROM {}.strategist_knowledge_base
                WHERE metadata->>'source' = %s AND metadata->>'ingestion_type' = %s
            r   z&Error fetching source_ids by name for rO   )rA   rT   rU   rm   r]   r   rV   r   rW   r²   r:   r5   r9   )rF   r#  rœ   r?   r_   rp   rq   r@   s           r   Úget_source_ids_by_namer%  Î  s=  € ô
 Ó€DðØ�[‰[‹]ð 	@˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷		@ð 	@ð" 	�
‰
�ô —G‘Gð ó ÷ ‘œŸ™ yÓ1Ó2ð	 ð
 �K‰K˜ ¨^Ð<Ô=Ø&)§l¡l£nÖ?˜s¸¸A»�C˜“FÒ?÷	@ð 	@ð" 	�
‰
�ùò @÷	@ð 	@úð 	@øô ò Ü�‰Ð=¸k¸]È"ÈQÈCÐPÔQØŒ	à�
‰
�ûð	úð	@ð" 	�
‰
�øˆ�
‰
�úsq   ŒC8 œ%C+Á	C8 ÁAC+Â8
C&ÃC&Ã
C+Ã	C8 Ã&C+Ã+C4Ã0C8 Ã7E Ã8	D8ÄD3ÄD8ÄE Ä3D8Ä8E ÅEÚfeature_nameÚ
model_nameÚprompt_tokensÚcompletion_tokensÚtotal_tokensc                 ó*  — t         j                  d| › d|› d|› �«       |s|r|j                  d«      }t        «       }	 |j	                  «       5 }	|	j                  d| f«       |	j                  «       d   s5t         j                  d| › �«       	 ddd«       |r|j                  «        y	y	d}
d}|rbt        j                  d
«      j                  t        j                  | «      «      }|	j                  |||f«       |	j                  «       }|r|\  }
}||
k  r|}|}n
||
z
  }||z
  }||z   }t        d|«      }t        d|«      }t        d|«      }t        j                  d«      j                  t        j                  | «      «      }|	j                  |||||||||t        j                  |xs i «      f	«       |j!                  «        t         j                  d|› d|› d|› d�«       	 ddd«       |r|j                  «        yy# 1 sw Y   nxY wnV# t"        $ rJ}|r|j%                  «        t         j'                  d| › d|› �«       Y d}~|r|j                  «        y	y	d}~ww xY w	 |r|j                  «        yy# |r|j                  «        w w xY w)z¢
    Persists LLM token usage data into the tenant's strategist_llm_usage table.
    Implements Delta-based tracking to avoid double-counting thread history.
    z#[DB] Logging LLM usage for tenant: z, feature: rÌ   rÊ   zã
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = %s 
                    AND table_name = 'strategist_llm_usage'
                )
            r   zA[DB] Skipping usage log: Table strategist_llm_usage not found in NFa
  
                    SELECT cumulative_prompt_tokens, cumulative_completion_tokens 
                    FROM {}.strategist_llm_usage 
                    WHERE thread_id = %s AND feature_name = %s
                    ORDER BY created_at DESC LIMIT 1
                a—  
                INSERT INTO {}.strategist_llm_usage (
                    feature_name, model_name, 
                    prompt_tokens, completion_tokens, total_tokens,
                    thread_id, cumulative_prompt_tokens, cumulative_completion_tokens,
                    metadata, created_at
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
            z[DB] Logged Delta: z tokens (Thread: z, Feature: ú)Tz#Failed to log LLM usage for tenant rO   )r5   rS   r   rA   rT   rU   rm   r6   r]   r   rV   r   rW   r’   r�   r�   r[   r:   r^   r9   )rF   r&  r'  r(  r)  r*  rƒ   rÊ   r?   r_   Úlast_cumulative_promptÚlast_cumulative_completionÚfetch_queryrq   Údelta_promptÚdelta_completionÚdelta_totalrf   r@   s                      r   Úinsert_llm_usager3  è  s·  € ô
 ‡K�KÐ5°i°[ÀÈLÈ>ÐYcÐdmÐcnÐoÔpñ ™Ø—L‘L Ó-ˆ	äÓ€DðOØ�[‰[‹]ð H	˜cà�K‰Kð ð �ôð —<‘<“> !Ò$Ü—‘Ð!bÐclÐbmÐnÔoØ÷H	ñ\ �—‘•ˆ4ðA &'Ð"Ø)*Ð&ñ Ü!Ÿg™gð 'ó ÷
 ‘VœCŸN™N¨9Ó5Ó6ð ð —‘˜K¨)°\Ð)BÔCØ—l‘l“n�ÙØILÑFÐ*Ð,Fð Ð5Ò5Ø,�Ø#4Ñ à,Ð/EÑE�Ø#4Ð7QÑ#QÐ à&Ð)9Ñ9ˆKô ˜q ,Ó/ˆLÜ" 1Ð&6Ó7ÐÜ˜a Ó-ˆKô Ÿ7™7ð $ó ÷ ‘œŸ™ yÓ1Ó2ð ð �K‰K˜ØØØØ ØØØØ!Ü—
‘
˜8š> rÓ*ð
'ô 
ð �K‰KŒMÜ�K‰KÐ-¨k¨]Ð:KÈIÈ;ÐVaÐbnÐaoÐopÐqÔrØ÷QH	ñ\ �—‘•ˆ4÷]H	ð H	úð H	øôR ò Ù�—‘”Ü�‰Ð:¸9¸+ÀRÈÀsÐKÔLÛá�—‘•ˆ4ûðúðSH	ñ\ �—‘•ˆ4ø‰4�—‘•ˆ4úsU   ¿H ÁA HÂH Â+D>HÇ)H ÈHÈ
H ÈI= È	I%È-I ÉI= É I%É%I= É=Jc                 óÄ  — t         j                  d| › �«       t        «       }	 |j                  «       5 }t	        j
                  d«      j                  t	        j                  | «      «      }|j                  ||f«       |j                  «       }|D �cg c]2  }|d   |d   |d   |d   |d   |d   r|d   j                  «       nd d	œ‘Œ4 c}cd d d «       |j                  «        S c c}w # 1 sw Y   nxY wnC# t        $ r7}t         j                  d
| › d|› �«       g cY d }~|j                  «        S d }~ww xY w	 |j                  «        y # |j                  «        w xY w)NzFetching LLM usage for tenant: zšSELECT feature_name, model_name, prompt_tokens, completion_tokens, total_tokens, created_at FROM {}.strategist_llm_usage ORDER BY created_at DESC LIMIT %sr   r&   r®   râ   rã   rä   )ÚfeatureÚmodelr(  r)  r*  Ú	timestampz Error fetching usage for tenant rO   )r5   rS   rA   rT   r   rV   r   rW   rU   r²   rû   r]   r:   r9   )rF   r§   r?   r_   rp   rþ   Úrr@   s           r   Úget_llm_usager9  E  sk  € Ü
‡K�KÐ1°)°Ð=Ô>ÜÓ€DðØ�[‰[‹]ð 	˜cÜ—G‘Gð  yó  z÷  Añ  Aô  BE÷  BPñ  BPð  QZó  B[ó  \ˆEØ�K‰K˜ ˜xÔ(Ø—<‘<“>ˆDð öð ð ˜Q™4Ø˜1™Ø!" 1¡Ø%& q¡TØ ! !¡Ø12°1²˜Q˜q™TŸ^™^Ô-¸4óò ÷		ð 	ð  	�
‰
�ùò÷		ð 	úð 	øô ò Ü�‰Ð7¸	°{À"ÀQÀCÐHÔIØŒ	à�
‰
�ûð	úð	ð  	�
‰
�øˆ�
‰
�ús_   ¤C8 ´AC+Â7C&Ã
C+Ã	C8 Ã&C+Ã+C4Ã0C8 Ã7E Ã8	D8ÄD3ÄD8ÄE Ä3D8Ä8E ÅEÚdatac                 ól  — t         j                  d|j                  d«      › d| › �«       t        «       }	 |j	                  «       5 }t        j                  d«      j                  t        j                  | «      «      }|j                  ||d   |j                  d«      |j                  d«      |j                  d«      |j                  d«      |j                  d	«      |j                  d
«      |j                  d«      |j                  d«      |j                  d«      |j                  d«      |j                  d«      |j                  d«      |j                  d«      t        j                  |j                  di «      «      f«       |j                  «        	 ddd«       |j                  «        y# 1 sw Y   nxY wnP# t        $ rD}|j                  «        t         j!                  d|› �d¬«       Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w)z=
    Inserts or updates analytics for a specific thread.
    z&Upserting thread analytics for thread rÊ   rá   aŒ  
                INSERT INTO {}.strategist_thread_analytics (
                    thread_id, sentiment_score, intent, is_high_intent, is_lead_qualified,
                    is_resolved, escalation_needed, positive_points, key_concerns,
                    pain_point, feature_request, objection, 
                    cta_clicked, summary, metadata, last_analyzed_at
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
                ON CONFLICT (thread_id) DO UPDATE SET
                    sentiment_score = EXCLUDED.sentiment_score,
                    intent = EXCLUDED.intent,
                    is_high_intent = EXCLUDED.is_high_intent,
                    is_lead_qualified = EXCLUDED.is_lead_qualified,
                    is_resolved = EXCLUDED.is_resolved,
                    escalation_needed = EXCLUDED.escalation_needed,
                    positive_points = EXCLUDED.positive_points,
                    key_concerns = EXCLUDED.key_concerns,
                    pain_point = EXCLUDED.pain_point,
                    feature_request = EXCLUDED.feature_request,
                    objection = EXCLUDED.objection,
                    cta_clicked = EXCLUDED.cta_clicked,
                    summary = EXCLUDED.summary,
                    metadata = EXCLUDED.metadata,
                    last_analyzed_at = CURRENT_TIMESTAMP
            Úsentiment_scoreÚintentÚis_high_intentÚis_lead_qualifiedÚis_resolvedÚescalation_neededÚpositive_pointsÚkey_concernsÚ
pain_pointÚfeature_requestÚ	objectionÚcta_clickedr�   rƒ   NTz#Failed to upsert thread analytics: r*   F)r5   rS   r   rA   rT   r   rV   r   rW   rU   r�   r�   r[   r]   r:   r^   r9   )rF   r:  r?   r_   Úupsert_queryr@   s         r   Úupsert_thread_analyticsrI  [  sÕ  € ô ‡K�KÐ8¸¿¹À+Ó9NÐ8OÈ{Ð[dÐZeÐfÔgÜÓ€Dð4Ø�[‰[‹]ð -	˜cÜŸ7™7ð $ó ÷0 ‘œŸ™ yÓ1Ó2ð1 ð4 �K‰K˜Ø�[Ñ!Ø—‘Ð*Ó+Ø—‘˜Ó"Ø—‘Ð)Ó*Ø—‘Ð,Ó-Ø—‘˜Ó'Ø—‘Ð,Ó-Ø—‘Ð*Ó+Ø—‘˜Ó(Ø—‘˜Ó&Ø—‘Ð*Ó+Ø—‘˜Ó%Ø—‘˜Ó'Ø—‘˜Ó#Ü—
‘
˜4Ÿ8™8 J°Ó3Ó4ð'ô ð" �K‰KŒMØ÷[-	ðf 	�
‰
�÷g-	ð -	úð -	øô\ ò Ø�‰ŒÜ�‰Ð:¸1¸#Ð>ÈˆÔNÛà�
‰
�ûðúð]-	ðf 	�
‰
�øˆ�
‰
�úsH   ¶F? ÁEF2ÆF? Æ2F;Æ7F? Æ>H! Æ?	HÇ*HÇ2H! ÈHÈH! È!H3c                 ób  — t        «       }	 |j                  «       5 }t        j                  d«      j	                  t        j
                  | «      t        j
                  | «      «      }|j                  |«       |j                  «       D �cg c]  }|d   ‘Œ	 c}cddd«       |j                  «        S c c}w # 1 sw Y   nxY wnE# t        $ r9}t        j                  d| › d|› �d¬«       g cY d}~|j                  «        S d}~ww xY w	 |j                  «        y# |j                  «        w xY w)zV
    Returns unique thread IDs that are present in feedback but not in analytics.
    zÃ
                SELECT DISTINCT thread_id 
                FROM {}.strategist_feedback 
                WHERE thread_id NOT IN (SELECT thread_id FROM {}.strategist_thread_analytics)
            r   Nz-Error fetching unanalyzed threads for tenant rO   Tr*   )rA   rT   r   rV   r   rW   rU   r²   r]   r:   r5   r9   )rF   r?   r_   rp   rq   r@   s         r   Úget_unanalyzed_threadsrK  —  s  € ô Ó€DðØ�[‰[‹]ð 	6˜cÜ—G‘Gð ó ÷ ‘œŸ™ yÓ1´3·>±>À)Ó3LÓMð	 ð
 �K‰K˜ÔØ&)§l¡l£nÖ5˜s�C˜“FÒ5÷	6ð 	6ð 	�
‰
�ùò 6÷	6ð 	6úð 	6øô ò Ü�‰ÐDÀYÀKÈrÐRSÐQTÐUÐ`dˆÔeØŒ	à�
‰
�ûð	úð	6ð 	�
‰
�øˆ�
‰
�ús_   ŒC œA/B8ÂB3ÂB8Â	C Â3B8Â8CÂ=C ÃD Ã	DÃDÃ,DÃ-D ÄDÄD ÄD.c                 óÌ  — t         j                  d| › d|› �«       t        «       }	 |j                  «       5 }|j	                  d| f«       |j                  «       sg cddd«       |j                  «        S t        j                  d«      j                  t        j                  | «      «      }|j	                  ||f«       |j                  «       }g }|D ]{  }|d   }|j                  |d   |d   |r|j                  «       j                  «       nd|r|j                  «       j!                  d	«      nd|r|j                  «       nd|d
   dœ«       Œ} |cddd«       |j                  «        S # 1 sw Y   nxY wnE# t"        $ r9}	t         j%                  d| › d|	› �d¬«       g cY d}	~	|j                  «        S d}	~	ww xY w	 |j                  «        y# |j                  «        w xY w)zX
    Returns a list of unique sources (filenames, URLs) for a given ingestion type.
    z-Listing unique knowledge sources for tenant: rŸ   rñ   Na§  
                SELECT 
                    metadata->>'source_id' as source_id,
                    metadata->>'source' as source_name,
                    MIN(created_at) as first_ingested_at,
                    metadata
                FROM {}.strategist_knowledge_base
                WHERE metadata->>'ingestion_type' = %s
                GROUP BY 1, 2, 4
                ORDER BY first_ingested_at DESC
            r®   r   r&   z%H:%M:%Srâ   )r¢   r  Úingested_dateÚingested_timerù   rƒ   z-Failed to fetch knowledge sources for tenant rO   Tr*   )r5   rS   rA   rT   rU   rm   r]   r   rV   r   rW   r²   rn   Údaterû   r7   Ústrftimer:   r9   )
rF   rœ   r?   r_   rp   rþ   Úsourcesrq   Údtr@   s
             r   Úget_knowledge_sourcesrS  ª  sÆ  € ô ‡K�KÐ?À	¸{È(ÐSaÐRbÐcÔdÜÓ€Dð&Ø�[‰[‹]ð  	˜cà�K‰KÐdÐgpÐfrÔsØ—<‘<”>Ø÷	 	ð  	ðJ 	�
‰
�ô? —G‘Gð 
ó 
÷ ‘œŸ™ yÓ1Ó2ð ð �K‰K˜ Ð0Ô1Ø—<‘<“>ˆDàˆGØò 	�Ø˜‘V�Ø—‘Ø!$ Q¡Ø! !™fÙ>@ R§W¡W£Y×%8Ñ%8Ô%:ÀdÙGI R§W¡W£Y×%7Ñ%7¸
Ô%CÈtÙ46 "§,¡,¤.¸DØ # A¡ñ õ ð	ð ÷A 	ð  	ðJ 	�
‰
�÷K 	ð  	úð  	øôB ò Ü�‰ÐDÀYÀKÈrÐRSÐQTÐUÐ`dˆÔeØŒ	à�
‰
�ûð	úðC 	ðJ 	�
‰
�øˆ�
‰
�úsY   §E: ·%E-Á	E: Á6CE-Å	E: Å-E6Å2E: Å9G Å:	F<ÆF7Æ!F<Æ"G Æ7F<Æ<G ÇG#c            	      ó  — t         j                  d«       	 t        «       } 	 | j                  «       5 }dt        › d�}|j                  |«       |j                  «       D ��ch c]6  \  }}t        |t        «      r!t        |«      j                  d«      r|nd|› �’Œ8 }}}|rJt        |«      }t         j                  dt        |«      › d|› �«       |cddd«       | j                  «        S t         j                  d«       ddd«       | j                  «        	 t         j                  d«       t#        «       } 	 | j                  «       5 }d}|j                  |«       |j                  «       D �cg c]  }|d   ‘Œ	 }}t         j                  dt        |«      › d|› �«       |cddd«       | j                  «        S c c}}w # 1 sw Y   ŒÃxY w# | j                  «        w xY w# t        $ r$}t         j!                  d	|› �d
¬«       Y d}~Œòd}~ww xY wc c}w # 1 sw Y   nxY wnB# t        $ r6}t         j!                  d|› �d
¬«       g cY d}~| j                  «        S d}~ww xY w	 | j                  «        y# | j                  «        w xY w)zä
    Returns tenant schema names eligible for scheduled analytics refresh.
    Active Aivora subscriptions from master are authoritative. The legacy
    schema scan remains as a fallback if the master lookup is unavailable.
    z,Fetching list of eligible Strategist tenantsa  
                    SELECT o."tenantId"::text, p."productFeatures"
                    FROM "Organisations" o
                    JOIN "Subscriptions" s ON s."organisationId" = o."id"
                    JOIN "Plans" p ON p."id" = s."planId"
                    WHERE zA
                    ORDER BY o."tenantId"::text
                Úorg_zFound z active/trial Aivora tenants: Nz>No active/trial Aivora subscriptions found in master database.z;Error fetching active Aivora tenants from master database: Tr*   zEFalling back to tenant schemas that contain strategist_knowledge_basea  
                SELECT DISTINCT table_schema 
                FROM information_schema.tables 
                WHERE table_name = 'strategist_knowledge_base'
                AND table_schema NOT IN ('public', 'information_schema', 'pg_catalog')
            r   z eligible tenants: z!Error fetching eligible tenants: )r5   rS   rE   rT   ÚSUBSCRIPTION_ELIGIBILITY_SQLrU   r²   r   ÚAIVORA_PRODUCT_NAMEr±   Ú
startswithÚsortedrŽ   r]   r6   r:   r9   rA   )	r?   r_   rp   rF   r   ÚtenantsÚtenant_listr@   rq   s	            r   Úget_eligible_tenantsr\  Ø  sa  € ô ‡K�KÐ>Ô?ðgÜ'Ó)ˆð	Ø—‘“ð a #ðô
 8Ð8ð 9ð�ð —‘˜EÔ"ð 8;·|±|³~÷á3˜	Ð#3Ü(Ð)9Ô;NÔOô "% Y£×!:Ñ!:¸6Ô!B‘IÈ$ÈyÈkÐHZÑZð�ñ ñ
 Ü"(¨£/�KÜ—K‘K &¬¨[Ó)9Ð(:Ð:XÐYdÐXeÐ fÔgØ&÷%að að* �J‰J�Lô —‘Ð_Ô`÷'að* �J‰J�Lô ‡K�KÐWÔXÜÓ€DðØ�[‰[‹]ð 
	˜cðˆEð �K‰K˜ÔØ),¯©«Ö8 #�s˜1“vÐ8ˆGÐ8Ü�K‰K˜&¤ W£ Ð.AÀ'ÀÐKÔLØ÷
	ð 
	ð 	�
‰
�ùóC÷að aûð* �J‰J�LûÜò gÜ�‰ÐRÐSTÐRUÐVÐaeˆ×fÑfûðgüò 9÷
	ð 
	úð 
	øô ò Ü�‰Ð8¸¸Ð<ÀtˆÔLØŒ	à�
‰
�ûð	úð
	ð 	�
‰
�øˆ�
‰
�úsÉ   —
G# ¢G ²/GÁ!;F<Â5GÃ	G ÃG# Ã+GÄ G ÄG# Ä9H% Å	&HÅ/HÅ;'HÆ"	H% Æ<GÇGÇG ÇG Ç G# Ç#	HÇ,HÈHÈHÈH!ÈH% È$I9 È%	I$È.IÉ	I$É
I9 ÉI$É$I9 É9Jc                 ó´  — t        | «      j                  dd«      }	 t        j                  |«       t        «       }	 |j                  «       5 }|j                  d|f«       |j                  «       }|s	 ddd«       |j                  «        y|d   |d   |d	   |d
   |d   dœcddd«       |j                  «        S # t        $ r t
        j                  d| › d�«       Y yw xY w# 1 sw Y   nxY wnA# t        $ r5}t
        j                  d| › d|› �«       Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w)z™Returns the tenant's Organisation record from the master DB, or None.

    Used for per-tenant email branding (business name, logo, website, email).
    rU  rI   úTenant ID 'z3' is not a valid UUID. Organisation lookup skipped.Nz˜SELECT "name", "businessName", "image", "website", "email" FROM "Organisations" WHERE "tenantId"::text = %s ORDER BY "createdAt" DESC NULLS LAST LIMIT 1r   r&   r®   râ   rã   )ÚnameÚbusinessNameÚimageÚwebsiterÔ   z)Failed to fetch organisation profile for rO   )r±   rP   ÚuuidÚUUIDrR   r5   r6   rE   rT   rU   rm   r]   r:   r9   )rF   Úraw_idr?   r_   rq   r@   s         r   Úfetch_organisation_profilerf    sO  € ô
 �‹^×#Ñ# F¨BÓ/€FðÜ�	‰	�&Ôô
 $Ó%€DðØ�[‰[‹]ð 	8˜cð �K‰Kð?ð �	ô	ð —,‘,“.ˆCÙØ÷	8ð& 	�
‰
�ð   ™F°C¸±FÀSÈÁVØ" 1™v°°A±ñ8÷	8ð 	8ð& 	�
‰
�øô3 ò Ü�‰˜ Y KÐ/bÐcÔdÙðú÷	8ð 	8úð 	8øô ò Ü�‰Ð@ÀÀÈ2ÈaÈSÐQÔRÛà�
‰
�ûð	úð	8ð& 	�
‰
�øˆ�
‰
�úsd   �B= ½C2 Á'C%Á4C2 ÂC%Â#	C2 Â="C"Ã!C"Ã%C.Ã*C2 Ã1E Ã2	D0Ã;D+ÄE Ä+D0Ä0E ÅEc                 ó\  — t         j                  d| › �«       t        | «      j                  dd«      }	 t	        j
                  |«       t        «       }	 |j                  «       5 }d}|j                  ||f«       |j                  «       }|s2t         j                  d| › �«       	 d	d	d	«       |j                  «        y|d
   }dt        › d�}|j                  ||f«       |j                  «       }|s2t         j                  d|› �«       	 d	d	d	«       |j                  «        y|D ]5  \  }	}
}t        |
t         «      sŒ|
|fc cd	d	d	«       |j                  «        S  t         j                  d|› �«       	 d	d	d	«       |j                  «        y# t        $ r t         j                  d| › d�«       Y yw xY w# 1 sw Y   nxY wnA# t"        $ r5}t         j%                  d| › d|› �«       Y d	}~|j                  «        yd	}~ww xY w	 |j                  «        y	# |j                  «        w xY w)zr
    Fetches real-time limits from the galaxiq_master database using the Organisation-Subscription-Plan path.
    z9Fetching dynamic limits from master database for tenant: rU  rI   r^  z4' is not a valid UUID. Master limits lookup skipped.rÆ   zDSELECT "id" FROM "Organisations" WHERE "tenantId"::text = %s LIMIT 1z#No Organisation found for tenantId Nr   zè
                SELECT s."planId", p."productFeatures", p."updatedAt"
                FROM "Subscriptions" s
                JOIN "Plans" p ON p."id" = s."planId"
                WHERE s."organisationId" = %s
                  AND zc
                ORDER BY s."createdAt" DESC NULLS LAST, p."updatedAt" DESC NULLS LAST
            z6No active/trial Subscription found for organisationId z5No active/trial Aivora plan found for organisationId z*Failed to fetch dynamic limits for tenant rO   )r5   rS   r±   rP   rc  rd  rR   r6   rE   rT   rU   rm   r]   rV  r²   r   rW  r:   r9   )rF   re  r?   r_   Ú	org_queryÚorg_rowÚorg_idÚ	sub_queryÚsubscription_rowsÚplan_idr   Ú
updated_atr@   s                r   Úfetch_tenant_limitsro  4  s  € ô ‡K�KÐKÈIÈ;ÐWÔXô �‹^×#Ñ# F¨BÓ/€FðÜ�	‰	�&Ôô
 $Ó%€Dð%Ø�[‰[‹]ð 	˜cà^ˆIØ�K‰K˜	 F 9Ô-Ø—l‘l“nˆGÙÜ—‘Ð!DÀYÀKÐPÔQØ!÷	ðH 	�
‰
�ð9 ˜Q‘ZˆFðô
 4Ð4ð 5ðˆIð �K‰K˜	 F 9Ô-Ø #§¡£ÐÙ$Ü—‘Ð!WÐX^ÐW_Ð`ÔaØ!÷1	ðH 	�
‰
�ð :Kò 8Ñ5�Ð)¨:Ü$Ð%5Ô7JÕKØ+¨ZÐ7Ñ7÷9	ð 	ðH 	�
‰
�ð8ô �N‰NÐRÐSYÐRZÐ[Ô\Ø÷?	ðH 	�
‰
�øôU ò Ü�‰˜ Y KÐ/cÐdÔeÙðú÷	ð 	úð 	øô@ ò Ü�‰ÐAÀ)ÀÈBÈqÈcÐRÔSÛà�
‰
�ûð	úðA	ðH 	�
‰
�øˆ�
‰
�ús…   µF ÁG Á%AF9Â&G Â?AF9ÄG Ä%F9Ä?F9Å	G ÅF9Å8G Æ"F6Æ5F6Æ9GÆ>G ÇH Ç	HÇG?Ç*H Ç?HÈH ÈH+c                 óz  — t        «       }	 |j                  «       5 }|rKt        j                  d«      j	                  t        j
                  | «      «      }|j                  ||f«       nF|j                  t        j                  d«      j	                  t        j
                  | «      «      «       |j                  «       d   cddd«       |j                  «        S # 1 sw Y   nxY wn-# t        $ r!}t        j                  d| › d|› �«       ‚ d}~ww xY w	 |j                  «        y# |j                  «        w xY w)zTReturns the number of tickets created for the tenant, optionally since a start date.zASELECT COUNT(*) FROM {}.strategist_tickets WHERE created_at >= %sz*SELECT COUNT(*) FROM {}.strategist_ticketsr   NzError getting ticket count for rO   ©rA   rT   r   rV   r   rW   rU   rm   r]   r:   r5   r9   ©rF   Ú
start_dater?   r_   rp   r@   s         r   Úget_ticket_countrt  j  s  € äÓ€DðØ�[‰[‹]ð 	%˜cÙÜŸ™Ð cÓd×kÑkÔlo×lzÑlzð  |Eó  mFó  G�Ø—‘˜E J =Õ1à—‘œCŸG™GÐ$PÓQ×XÑXÔY\×YgÑYgÐhqÓYrÓsÔtØ—<‘<“> !Ñ$÷	%ð 	%ð 	�
‰
�÷	%ð 	%úð 	%øô ò Ü�‰Ð6°y°kÀÀAÀ3ÐGÔHØûðúð	%ð 	�
‰
�øˆ�
‰
�úóA   ŒC) œB&CÃ	C) ÃC%Ã!C) Ã(D( Ã)	DÃ2DÄDÄD( Ä(D:c                 óà  — t        «       }	 |j                  «       5 }|j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  «       d   cddd«       |j                  «        S # 1 sw Y   nxY wn-# t        $ r!}t        j                  d| › d|› �«       ‚ d}~ww xY w	 |j                  «        y# |j                  «        w xY w)z<Returns the total size of the knowledge base table in bytes.z>SELECT pg_total_relation_size('{0}.strategist_knowledge_base')r   Nz!Error getting vector db size for rO   )rA   rT   rU   r   rV   r   rW   rm   r]   r:   r5   r9   rº   s       r   r”   r”   {  s×   € äÓ€Dð	Ø�[‰[‹]ð 	%˜cà�K‰KœŸ™Ð `Óa×hÑhÔil×iwÑiwð  yBó  jCó  Dô  EØ—<‘<“> !Ñ$÷	%ð 	%ð 	�
‰
�÷	%ð 	%úð 	%øô ò Ü�‰Ð8¸¸À2ÀaÀSÐIÔJØûðúð		%ð 	�
‰
�øˆ�
‰
�úsA   ŒB œABÁ5	B ÂBÂB ÂC Â	CÂ%CÃCÃC ÃC-c                 ó  — t        «       }	 |j                  «       5 }|j                  d| › d�f«       |j                  «       d   s	 ddd«       |j	                  «        y|j                  t        j                  d«      j                  t        j                  | «      «      «       t        |j                  «       d   «      cddd«       |j	                  «        S # 1 sw Y   nxY w	 |j	                  «        y# |j	                  «        w xY w)aŠ  How many products this tenant's catalogue holds, across every source.

    The BrandForge plan caps catalogue size, and a merchant thinks of that as
    one number rather than one per connected source -- so this counts the whole
    table rather than filtering by source_kind.

    A tenant who has never ingested anything has no table yet, which is zero
    products rather than an error.
    úSELECT to_regclass(%s)ú.strategist_productsr   Nz+SELECT count(*) FROM {}.strategist_products)
rA   rT   rU   rm   r]   r   rV   r   rW   Úint)rF   r?   r_   s      r   Úget_catalogue_product_countr{  ‰  sÙ   € ô Ó€Dð
Ø�[‰[‹]ð 	*˜cØ�K‰KÐ0Ø%˜;Ð&:Ð;Ð=ô?à—<‘<“> !Ò$Ø÷		*ð 	�
‰
�ð	 �K‰KœŸ™Ð MÓNß™¤§¡¨yÓ 9Ó:ô<ä�s—|‘|“~ aÑ(Ó)÷	*ð 	*ð 	�
‰
�÷	*ð 	*úð 	*ð 	�
‰
�øˆ�
‰
�ús/   ŒC9 œ+CÁC9 Á A!CÃ	C9 ÃC$Ã C9 Ã9DÚproduct_keysc                 óh  — |syt        «       }	 |j                  «       5 }|j                  d| › d�f«       |j                  «       d   s$t	        |«      cddd«       |j                  «        S |j                  t        j                  d«      j                  t        j                  | «      «      t        |«      f«       t        |j                  «       d   «      }t	        |«      |z
  cddd«       |j                  «        S # 1 sw Y   nxY w	 |j                  «        y# |j                  «        w xY w)ai  How many of these products the catalogue does not already hold.

    A re-sync or a re-uploaded file carries the same products every time. Those
    update rows rather than adding them, so counting the whole batch against a
    plan limit would refuse a merchant who is adding nothing at all -- and would
    refuse them harder the more often they refresh.
    r   rx  ry  NzGSELECT count(*) FROM {}.strategist_products WHERE product_key = ANY(%s))rA   rT   rU   rm   rŽ   r]   r   rV   r   rW   r   rz  )rF   r|  r?   r_   Úalready_helds        r   Úcount_new_productsr  ¡  s  € ñ ØäÓ€DðØ�[‰[‹]ð 
	4˜cØ�K‰KÐ0Ø%˜;Ð&:Ð;Ð=ô?à—<‘<“> !Ò$Ü˜<Ó(÷	
	4ð 
	4ð 	�
‰
�ð �K‰KœŸ™ð.ó÷ ‰f”S—^‘^ IÓ.Ó/´$°|Ó2DÐ1FôHô ˜sŸ|™|›~¨aÑ0Ó1ˆLÜ�|Ó$ |Ñ3÷
	4ð 
	4ð 	�
‰
�÷
	4ð 
	4úð 
	4ð 	�
‰
�øˆ�
‰
�ús/   �D Ÿ4DÁ	D Á-A:DÃ'	D ÄD
ÄD ÄD1c                 ó�  — t        «       }	 |j                  «       5 }|rKt        j                  d«      j	                  t        j
                  | «      «      }|j                  ||f«       nF|j                  t        j                  d«      j	                  t        j
                  | «      «      «       |j                  «       }|r
|d   r|d   ndcddd«       |j                  «        S # 1 sw Y   nxY wn-# t        $ r!}t        j                  d| › d|› �«       ‚ d}~ww xY w	 |j                  «        y# |j                  «        w xY w)zNReturns the sum of total_tokens for the tenant, optionally since a start date.zLSELECT SUM(total_tokens) FROM {}.strategist_llm_usage WHERE created_at >= %sz5SELECT SUM(total_tokens) FROM {}.strategist_llm_usager   NzError getting token usage for rO   rq  )rF   rs  r?   r_   rp   rq   r@   s          r   Úget_total_token_usager�  ½  s$  € äÓ€DðØ�[‰[‹]ð 	3˜cÙÜŸ™Ð nÓo×vÑvÔwz÷  xFñ  xFð  GPó  xQó  R�Ø—‘˜E J =Õ1à—‘œCŸG™GÐ$[Ó\×cÑcÔdg×drÑdrÐs|Ód}Ó~ÔØ—,‘,“.ˆCÙ  S¨¢V�3�q’6°÷	3ð 	3ð 	�
‰
�÷	3ð 	3úð 	3øô ò Ü�‰Ð5°i°[ÀÀ1À#ÐFÔGØûðúð	3ð 	�
‰
�øˆ�
‰
�úsA   ŒC4 œB1C'Ã	C4 Ã'C0Ã,C4 Ã3D3 Ã4	DÃ=DÄDÄD3 Ä3Ec                 óz  — t        «       }	 |j                  «       5 }|rKt        j                  d«      j	                  t        j
                  | «      «      }|j                  ||f«       nF|j                  t        j                  d«      j	                  t        j
                  | «      «      «       |j                  «       d   cddd«       |j                  «        S # 1 sw Y   nxY wn-# t        $ r!}t        j                  d| › d|› �«       ‚ d}~ww xY w	 |j                  «        y# |j                  «        w xY w)zRReturns the number of unique threads that had activity [usage] since a start date.zTSELECT COUNT(DISTINCT thread_id) FROM {}.strategist_llm_usage WHERE created_at >= %sz=SELECT COUNT(DISTINCT thread_id) FROM {}.strategist_llm_usager   Nz%Error getting conversation count for rO   rq  rr  s         r   Úget_conversation_countrƒ  Ï  s(  € äÓ€DðØ�[‰[‹]ð 	%˜cÙÜŸ™Ð vÓw×~Ñ~ô  @C÷  @Nñ  @Nð  OXó  @Yó  Z�Ø—‘˜E J =Õ1à—‘œCŸG™GÐ$cÓd×kÑkÔlo×lzÑlzð  |Eó  mFó  Gô  HØ—<‘<“> !Ñ$÷	%ð 	%ð 	�
‰
�÷	%ð 	%úð 	%øô ò Ü�‰Ð<¸Y¸KÀrÈ!ÈÐMÔNØûðúð	%ð 	�
‰
�øˆ�
‰
�úru  ))Ústrategist_product_neighborsa¨  
        CREATE TABLE IF NOT EXISTS {}.strategist_product_neighbors (
            anchor_key   TEXT NOT NULL,
            neighbor_key TEXT NOT NULL,
            pair_type    TEXT NOT NULL,
            score        REAL NOT NULL,
            -- Separate from score on purpose: a pair can be a strong
            -- relationship derived by a weak method. The approval queue
            -- gates on how much the derivation is trusted, not on how
            -- good the pair is, so collapsing these would make it
            -- incoherent.
            confidence   REAL NOT NULL,
            source       TEXT NOT NULL,
            -- What the merchant is shown when asked to approve. A bare
            -- 0.79 is not reviewable.
            reasons      JSONB NOT NULL DEFAULT '[]',
            computed_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (anchor_key, neighbor_key, pair_type)
        )
    )Ústrategist_pairing_decisionsaž  
        CREATE TABLE IF NOT EXISTS {}.strategist_pairing_decisions (
            anchor_key   TEXT NOT NULL,
            neighbor_key TEXT NOT NULL,
            pair_type    TEXT NOT NULL,
            decision     TEXT NOT NULL,
            decided_by   TEXT,
            decided_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (anchor_key, neighbor_key, pair_type)
        )
    )Ústrategist_product_embeddingsa  
        CREATE TABLE IF NOT EXISTS {}.strategist_product_embeddings (
            product_key  TEXT PRIMARY KEY,
            content_hash TEXT,
            vector       REAL[] NOT NULL,
            computed_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )
    ))Úsource_kindzTEXT NOT NULL DEFAULT 'crawl')Ú
source_refÚTEXT)Úexternal_idr‰  )Úbrandr‰  )Útaxonomy_pathúTEXT[] NOT NULL DEFAULT '{}')Útaxonomy_sourcer‰  )Úraw_categoryr‰  )Úprice_centsÚINT)Úprice_max_centsr‘  )Úcompare_at_centsr‘  )Úcurrencyr‰  )Úon_salezBOOLEAN NOT NULL DEFAULT false)Úin_stockzBOOLEAN NOT NULL DEFAULT true)rç   r‰  )Ú
attributeszJSONB NOT NULL DEFAULT '[]')Úquality_scoreÚREAL)Úcontent_hashr‰  )Úrecord_hashr‰  )Ú	synced_atzTIMESTAMP WITH TIME ZONE)Útenant_relationszJSONB NOT NULL DEFAULT '{}')Úratingr™  )Úreview_countr‘  )Úfeatured_rankr‘  )Úmissing_fieldsr�  )Úprice_reference_centsr‘  )Úfx_rate_usedr™  )Úis_accessoryÚBOOLEAN)Ú
price_tierr‰  )Úenriched_hashr‰  c                 óø  — g }t        «       }	 |j                  «       5 }|j                  t        j                  d«      | f«       |j                  «       D �ch c]  }|d   ’Œ	 }}t        D ]�  \  }}t        j                  d«      j                  t        j                  | «      t        j                  |«      «      t        j                  |«      z   }|j                  |«       ||vsŒ}|j                  |«       Œ� |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  t        j                  d«      j                  t        j                  | «      «      «       ddd«       |j                  «        t        j                  d| › d	t        |«      › d
�«       d|i|j                  «        S c c}w # 1 sw Y   ŒWxY w# t        $ r, |j!                  «        t        j#                  d| › �d¬«       ‚ w xY w# |j                  «        w xY w)a_  Bring an existing tenant's products table up to the catalog-sync shape.

    bootstrap_tenant uses CREATE TABLE IF NOT EXISTS, which will not alter a
    table that already holds crawled rows. source_kind defaults to 'crawl' so
    every pre-existing row is labelled correctly -- any other default would let
    a later sync delete them as stale.
    zqSELECT column_name FROM information_schema.columns WHERE table_schema = %s AND table_name = 'strategist_products'r   rK   zIALTER TABLE {}.strategist_products ALTER COLUMN product_url DROP NOT NULLrL   rM   NzMigrated products table for rO   z columnsÚaddedz%Could not migrate products table for Tr*   )rA   rT   rU   r   rV   r²   rX   r   rW   rn   r[   r5   rS   rŽ   r]   r:   r^   r9   )	rF   r©  r?   r_   rq   Úexistingr_  ÚddlÚstmts	            r   Úmigrate_products_tabler­  8  s  € ð €EÜÓ€Dð.Ø�[‰[‹]ð $	1˜cð
 �K‰KœŸ™ðQóð ˆ|ôð +.¯,©,«.Ö9 3˜˜A›Ð9ˆHÐ9Ü-ò 
'‘	��cô
 —w‘wØUóç‘&œŸ™¨	Ó2´C·N±NÀ4Ó4HÓIÌCÏGÉGÐTWËLñY�ð —‘˜DÔ!Ø˜xÒ'Ø—L‘L Õ&ð
'ð �K‰KœŸ™ð9ó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ðFó÷ ‰f”S—^‘^ IÓ.Ó/ô1ð �K‰KœŸ™ð?ó÷ ‰f”S—^‘^ IÓ.Ó/ô1÷C$	1ðJ 	�‰ŒÜ�‰Ð2°9°+¸RÄÀEÃ
¸|È8ÐTÔUØ˜Ðð 	�
‰
�ùòI :÷$	1ð $	1ûôP ò Ø�‰ŒÜ�‰Ð<¸Y¸KÐHÐSWˆÔXØðûð
 	�
‰
�úsI   ŽH/ ž9H#ÁHÁ#BH#Ã(C%H#ÇA H/ ÈH#È#H,È(H/ È/5I$É$I' É'I9c           	      ób  — g }t        «       }	 |j                  «       5 }|j                  d| f«       |j                  «       D �ch c]  }|d   ’Œ	 }}t        D ]a  \  }}|j                  t        j                  |«      j                  t        j                  | «      «      «       ||vsŒQ|j                  |«       Œc |j                  t        j                  d«      j                  t        j                  | «      «      «       ddd«       |j                  «        t        j                  d| › dt        |«      › d�«       d|i|j                  «        S c c}w # 1 sw Y   ŒWxY w# t        $ r, |j!                  «        t        j#                  d	| › �d
¬«       ‚ w xY w# |j                  «        w xY w)ah  Add the pairing graph tables to a tenant whose schema predates them.

    bootstrap_tenant uses CREATE TABLE IF NOT EXISTS, so a tenant provisioned
    before this phase never gets strategist_product_neighbors,
    strategist_pairing_decisions, or strategist_product_embeddings just by
    logging in again. Same reasoning as migrate_products_table above.
    zHSELECT table_name FROM information_schema.tables WHERE table_schema = %sr   rN   NzMigrated pairing tables for rO   z createdÚcreatedz%Could not migrate pairing tables for Tr*   )rA   rT   rU   r²   rY   r   rV   r   rW   rn   r[   r5   rS   rŽ   r]   r:   r^   r9   )rF   r¯  r?   r_   rq   rª  Ú
table_namer«  s           r   Úmigrate_pairing_tablesr±  s  st  € ð €GÜÓ€DðØ�[‰[‹]ð 	1˜cð �K‰Kð*à�ôð
 +.¯,©,«.Ö9 3˜˜A›Ð9ˆHÐ9Ü#2ò /‘�
˜CØ—‘œCŸG™G C›L×/Ñ/´·±¸yÓ0IÓJÔKØ XÒ-Ø—N‘N :Õ.ð/ð �K‰KœŸ™ðMó÷ ‰f”S—^‘^ IÓ.Ó/ô1÷!	1ð( 	�‰ŒÜ�‰Ð2°9°+¸RÄÀGÃ¸~ÈXÐVÔWØ˜7Ð#ð 	�
‰
�ùò# :÷	1ð 	1ûô. ò Ø�‰ŒÜ�‰Ð<¸Y¸KÐHÐSWˆÔXØðûð
 	�
‰
�úsI   ŽE$ ž&EÁEÁAEÂ)AEÄA E$ ÅEÅE!ÅE$ Å$5FÆF ÆF.)râ   r®   )rä   )NrÆ   )éd   )Fr-   r   Úpsycopg2.extrasr   r�   Úapp.core.configr   Úloggingr7   Útypingr   rc  ÚbasicConfigÚINFOÚ	getLoggerr5   rW  rV  r±   Úboolr   rA   rE   rh   r\   rZ   r€   r   r   r›   r¡   r¥   r³   rz  r·   r»   r¿   rÄ   rÈ   rß   rè   rì   rï   rü   r   r  r  r  r  r  r"  r%  r3  r9  rI  rK  rS  r\  rf  ro  rt  r”   r{  r  r�  rƒ  rY   rX   r­  r±  © r   r   ú<module>r¼     så  ðÛ Ý Ý  Û Ý $Û Û Ý Û ð €× Ñ ˜'Ÿ,™,Ð/eÕ fØ	ˆ×	Ñ	˜:Ó	&€àÐ ð Ð ð°cð ¸dó óó4ð4E ó EðN
2 3ó 2ðhR só RðQ có Qð* #ð *°ð *Àð *ÐPTó *ðX˜cð °3ð Àó ð( #ð °#ð ¸Có ñ(* #ð *¸¸U¹ð *ÈCó *ðX Sð ¨Tó ð$˜3ð  Só ð0%˜cð %¨ó %ðP #ð °só ñ<H˜Sð H¨tð HÀó HðT) Cð )°Có )ðV˜Sð ¨Sð ¸tó ðB Cð °Cð Àó ð05˜#ð 5¨#ó 5ðn7˜3ó 7ðr #ð °#ð À3ð ÐQTó ð> 3ð °3ó ðB¨ó ð0˜sð °4ó ð>/¨Cð /¸ó /ðb&°ð &Àó &ðP cð ¸ð ÈSó ñ4[ ð [°3ð [ÀCð [ÐX[ð [Ðpsð [ð  DGð [ð  SWð [ð  knó [ñz˜Sð ¨ó ð,: sð :°$ó :ðx có ð&, Sð ,¸#ó ,ò\6ðp!¨#ó !ðH4 3ó 4ñl ó ð" #ó ð¨3ð °3ó ð0 #ð °Tð ¸có ñ8 Só ñ$ có ò,(€òV%Ð ðP8 cð 8¨dó 8ðv' cð '¨dô 'r   