#!/usr/bin/env python3 import os, re, json, sqlite3, hashlib, secrets, mimetypes, io, csv, datetime from http.server import ThreadingHTTPServer, BaseHTTPRequestHandler from urllib.parse import urlparse, parse_qs from email.parser import BytesParser from email.policy import default from ai_worker import PriscaAIWorker, PackageGenerationEngine from content_worker import ContentWorker from wordpress_connector import WordPressConnector from integrations import IntegrationManager, MetaAdapter, PinterestAdapter, SearchConsoleAdapter, BingWebmasterAdapter, APIError from cpanel_deployer import CPanelClient, CPanelError, deploy as cpanel_deploy try: from cryptography.fernet import Fernet except Exception: Fernet=None BASE=os.path.dirname(os.path.abspath(__file__)) DB=os.path.join(BASE,'data','prisca.db') UPLOADS=os.path.join(BASE,'uploads') os.makedirs(os.path.dirname(DB),exist_ok=True); os.makedirs(UPLOADS,exist_ok=True) KEYFILE=os.path.join(BASE,'data','.privacy.key') def privacy_encrypt(value): if not value: return '' if Fernet is None: return value if not os.path.exists(KEYFILE): with open(KEYFILE,'wb') as f: f.write(Fernet.generate_key()) return Fernet(open(KEYFILE,'rb').read()).encrypt(value.encode()).decode() def privacy_decrypt(value): if not value: return '' if Fernet is None: return value if value != '[protected]' else '' try: return Fernet(open(KEYFILE,'rb').read()).decrypt(value.encode()).decode() except Exception: return '' SECRET_SETTING_KEYS={'wp_app_password','woo_consumer_key','woo_consumer_secret','meta_access_token','youtube_access_token','google_access_token','pinterest_access_token','bing_api_key','cpanel_token','cpanel_password'} def integration_config(include_secrets=False): s=settings(); out={} for k,v in s.items(): if k.startswith('int_'): key=k[4:] if key in SECRET_SETTING_KEYS: if include_secrets: out[key]=privacy_decrypt(v) else: out[key]='' if not v else '••••••••' else: out[key]=v return out def load_integration_env(): s=settings() if os.path.exists(DB) else {} mapping={ 'wp_url':'PRISCA_WP_URL','wp_user':'PRISCA_WP_USER','wp_app_password':'PRISCA_WP_APP_PASSWORD','woo_consumer_key':'PRISCA_WOO_CONSUMER_KEY','woo_consumer_secret':'PRISCA_WOO_CONSUMER_SECRET', 'meta_access_token':'PRISCA_META_ACCESS_TOKEN','facebook_page_id':'PRISCA_FACEBOOK_PAGE_ID','instagram_business_id':'PRISCA_INSTAGRAM_BUSINESS_ID', 'youtube_access_token':'PRISCA_YOUTUBE_ACCESS_TOKEN','pinterest_access_token':'PRISCA_PINTEREST_ACCESS_TOKEN','pinterest_board_id':'PRISCA_PINTEREST_BOARD_ID', 'google_access_token':'PRISCA_GOOGLE_ACCESS_TOKEN','gsc_site_url':'PRISCA_GSC_SITE_URL','bing_api_key':'PRISCA_BING_API_KEY','bing_site_url':'PRISCA_BING_SITE_URL' } for key,envkey in mapping.items(): val=s.get('int_'+key,'') if key in SECRET_SETTING_KEYS: val=privacy_decrypt(val) if val: os.environ[envkey]=val if s.get('int_cpanel_token'): os.environ['PRISCA_CPANEL_TOKEN']=privacy_decrypt(s['int_cpanel_token']) SCHEMA=''' CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'agent', active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY, value TEXT NOT NULL); CREATE TABLE IF NOT EXISTS states(id INTEGER PRIMARY KEY AUTOINCREMENT, state TEXT NOT NULL, destination TEXT NOT NULL, UNIQUE(state,destination)); CREATE TABLE IF NOT EXISTS hotels(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT, hotel_name TEXT NOT NULL, city TEXT, category TEXT, rating REAL, address TEXT, source_file TEXT, source_sheet TEXT, room_type TEXT, occupancy TEXT, meal_plan TEXT, rate REAL, extra_adult REAL, extra_child REAL, rate_type TEXT, valid_from TEXT, valid_to TEXT, season_type TEXT, notes TEXT, tac_percent REAL DEFAULT 0, gst_percent REAL DEFAULT 0, gst_included INTEGER DEFAULT 0, published_rate REAL DEFAULT 0, tax_status TEXT DEFAULT 'unknown', room_features TEXT DEFAULT '', blackout_dates TEXT DEFAULT '', price_basis TEXT DEFAULT '', extra_bed_basis TEXT DEFAULT '', active INTEGER DEFAULT 1); CREATE TABLE IF NOT EXISTS cabs(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT, route TEXT, duration TEXT, km INTEGER, vehicle TEXT, rate REAL, valid_from TEXT, valid_to TEXT, season_type TEXT, notes TEXT, active INTEGER DEFAULT 1); CREATE TABLE IF NOT EXISTS imports(id INTEGER PRIMARY KEY AUTOINCREMENT, filename TEXT, file_type TEXT, uploaded_by INTEGER, uploaded_at TEXT, extracted_text TEXT, status TEXT DEFAULT 'review'); CREATE TABLE IF NOT EXISTS packages(id INTEGER PRIMARY KEY AUTOINCREMENT, agent_id INTEGER, query_json TEXT, option_json TEXT, created_at TEXT); CREATE TABLE IF NOT EXISTS customers(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, email TEXT, travellers INTEGER, travel_date TEXT, destination TEXT, notes TEXT, created_at TEXT); CREATE TABLE IF NOT EXISTS bookings(id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER, agent_id INTEGER, package_id INTEGER, sale_value REAL, our_price REAL, supplier_cost REAL, amount_received REAL DEFAULT 0, pending_amount REAL DEFAULT 0, payment_due_date TEXT, status TEXT DEFAULT 'confirmed', created_at TEXT); CREATE TABLE IF NOT EXISTS payments(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER, amount REAL, payment_date TEXT, note TEXT); CREATE TABLE IF NOT EXISTS sessions(token TEXT PRIMARY KEY, user_id INTEGER, expires_at TEXT); CREATE TABLE IF NOT EXISTS houseboats(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT, name TEXT, destination TEXT, category TEXT, bedrooms INTEGER, pax INTEGER, rate REAL, rate_type TEXT, meal_plan TEXT, gst_percent REAL DEFAULT 0, valid_from TEXT, valid_to TEXT, extra_mattress REAL DEFAULT 0, notes TEXT, active INTEGER DEFAULT 1); CREATE TABLE IF NOT EXISTS activities(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT, name TEXT NOT NULL, destination TEXT, category TEXT, adult_rate REAL DEFAULT 0, child_rate REAL DEFAULT 0, rate_type TEXT DEFAULT 'per person', gst_percent REAL DEFAULT 0, gst_included INTEGER DEFAULT 0, valid_from TEXT, valid_to TEXT, notes TEXT, active INTEGER DEFAULT 1); CREATE TABLE IF NOT EXISTS tasks(id INTEGER PRIMARY KEY AUTOINCREMENT, agent_id INTEGER, booking_id INTEGER, title TEXT, detail TEXT, due_date TEXT, status TEXT DEFAULT 'pending', created_at TEXT); CREATE TABLE IF NOT EXISTS notifications(id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, title TEXT, body TEXT, severity TEXT DEFAULT 'info', read_at TEXT, created_at TEXT); CREATE TABLE IF NOT EXISTS travellers(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER, full_name TEXT, age INTEGER, gender TEXT, phone TEXT, email TEXT, aadhaar_encrypted TEXT, pan_encrypted TEXT); CREATE TABLE IF NOT EXISTS booking_services(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER, service_type TEXT, destination TEXT, provider_name TEXT, provider_address TEXT, service_name TEXT, room_or_vehicle TEXT, meal_plan TEXT, checkin TEXT, checkout TEXT, guest_count TEXT, confirmation_id TEXT, vendor_reference TEXT, supplier_payable REAL DEFAULT 0, supplier_paid REAL DEFAULT 0, supplier_due REAL DEFAULT 0, status TEXT DEFAULT 'pending', notes TEXT); CREATE TABLE IF NOT EXISTS service_payments(id INTEGER PRIMARY KEY AUTOINCREMENT, service_id INTEGER, amount REAL, payment_date TEXT, screenshot_path TEXT, note TEXT); CREATE TABLE IF NOT EXISTS voucher_logs(id INTEGER PRIMARY KEY AUTOINCREMENT, service_id INTEGER, generated_by INTEGER, generated_at TEXT, filename TEXT); CREATE TABLE IF NOT EXISTS supplier_profiles(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT UNIQUE NOT NULL, match_pattern TEXT, parser_version TEXT, commercial_rules TEXT, notes TEXT, updated_at TEXT); CREATE TABLE IF NOT EXISTS import_rows(id INTEGER PRIMARY KEY AUTOINCREMENT, import_id INTEGER, data_type TEXT, supplier TEXT, hotel_name TEXT, destination TEXT, category TEXT, rating REAL, room_type TEXT, occupancy TEXT, meal_plan TEXT, rate REAL, rate_type TEXT, tac_percent REAL DEFAULT 0, gst_percent REAL DEFAULT 0, gst_included INTEGER DEFAULT 0, extra_adult REAL DEFAULT 0, extra_child REAL DEFAULT 0, valid_from TEXT, valid_to TEXT, notes TEXT, confidence REAL DEFAULT 0, warnings TEXT, room_features TEXT DEFAULT '', blackout_dates TEXT DEFAULT '', price_basis TEXT DEFAULT '', extra_bed_basis TEXT DEFAULT '', tax_status TEXT DEFAULT 'unknown', published_rate REAL DEFAULT 0, effective_cost REAL DEFAULT 0, duplicate_key TEXT DEFAULT '', approved INTEGER DEFAULT 0, published INTEGER DEFAULT 0); CREATE TABLE IF NOT EXISTS package_approvals(id INTEGER PRIMARY KEY AUTOINCREMENT, package_json TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending', created_by TEXT, created_at TEXT NOT NULL, reviewed_by INTEGER, reviewed_at TEXT, review_note TEXT, wp_product_id INTEGER DEFAULT NULL); CREATE TABLE IF NOT EXISTS content_assets(id INTEGER PRIMARY KEY AUTOINCREMENT, package_name TEXT, destination TEXT, platform TEXT, asset_type TEXT, title TEXT, body TEXT, cta TEXT, status TEXT DEFAULT 'draft', publish_blocked INTEGER DEFAULT 1, scheduled_for TEXT, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS content_calendars(id INTEGER PRIMARY KEY AUTOINCREMENT, destination TEXT, calendar_json TEXT NOT NULL, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS seo_audits(id INTEGER PRIMARY KEY AUTOINCREMENT, destination TEXT, keyword_plan_json TEXT NOT NULL, status TEXT DEFAULT 'draft', created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS integration_logs(id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, action TEXT NOT NULL, asset_id INTEGER, status TEXT NOT NULL, response_json TEXT, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS social_metrics(id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, asset_id INTEGER, metric_date TEXT NOT NULL, impressions REAL DEFAULT 0, reach REAL DEFAULT 0, views REAL DEFAULT 0, likes REAL DEFAULT 0, comments REAL DEFAULT 0, shares REAL DEFAULT 0, clicks REAL DEFAULT 0, saves REAL DEFAULT 0, leads REAL DEFAULT 0, revenue REAL DEFAULT 0, raw_json TEXT, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS seo_metrics(id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT NOT NULL, metric_date TEXT NOT NULL, query_text TEXT, page_url TEXT, clicks REAL DEFAULT 0, impressions REAL DEFAULT 0, ctr REAL DEFAULT 0, position REAL DEFAULT 0, raw_json TEXT, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS scheduled_jobs(id INTEGER PRIMARY KEY AUTOINCREMENT, job_type TEXT NOT NULL, payload_json TEXT NOT NULL, run_at TEXT NOT NULL, status TEXT DEFAULT 'scheduled', attempts INTEGER DEFAULT 0, last_error TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS worker_runs(id INTEGER PRIMARY KEY AUTOINCREMENT, worker TEXT NOT NULL, action TEXT NOT NULL, status TEXT NOT NULL, summary_json TEXT, started_at TEXT NOT NULL, finished_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS seo_actions(id INTEGER PRIMARY KEY AUTOINCREMENT, action_type TEXT NOT NULL, target TEXT, status TEXT DEFAULT 'planned', details_json TEXT, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS leads(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, email TEXT, source TEXT, destination TEXT, travel_date TEXT, travellers INTEGER DEFAULT 1, budget REAL DEFAULT 0, message TEXT, stage TEXT DEFAULT 'new', priority TEXT DEFAULT 'normal', owner_id INTEGER, last_contacted_at TEXT, next_followup_at TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS lead_events(id INTEGER PRIMARY KEY AUTOINCREMENT, lead_id INTEGER NOT NULL, event_type TEXT NOT NULL, note TEXT, created_by INTEGER, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS sales_tasks(id INTEGER PRIMARY KEY AUTOINCREMENT, lead_id INTEGER, owner_id INTEGER, title TEXT NOT NULL, detail TEXT, due_at TEXT, status TEXT DEFAULT 'pending', created_at TEXT NOT NULL, completed_at TEXT); CREATE TABLE IF NOT EXISTS lead_sources(id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT UNIQUE NOT NULL, active INTEGER DEFAULT 1); CREATE TABLE IF NOT EXISTS sales_settings(key TEXT PRIMARY KEY, value TEXT NOT NULL); CREATE TABLE IF NOT EXISTS lead_ingest_events(id INTEGER PRIMARY KEY AUTOINCREMENT, fingerprint TEXT UNIQUE NOT NULL, source TEXT, payload_json TEXT, lead_id INTEGER, created_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS vendors(id INTEGER PRIMARY KEY AUTOINCREMENT, supplier TEXT UNIQUE NOT NULL, contact_name TEXT, phone TEXT, email TEXT, whatsapp TEXT, address TEXT, active INTEGER DEFAULT 1, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS booking_workflows(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER UNIQUE NOT NULL, status TEXT DEFAULT 'ops_pending', current_step TEXT DEFAULT 'supplier_confirmation', package_snapshot TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, completed_at TEXT); CREATE TABLE IF NOT EXISTS supplier_requests(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER NOT NULL, service_id INTEGER, vendor_id INTEGER, request_type TEXT NOT NULL, status TEXT DEFAULT 'draft', requested_at TEXT, requested_by INTEGER, confirmation_id TEXT, response_note TEXT, due_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL); CREATE TABLE IF NOT EXISTS booking_milestones(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER NOT NULL, milestone TEXT NOT NULL, status TEXT DEFAULT 'pending', due_at TEXT, completed_at TEXT, note TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE(booking_id,milestone)); CREATE TABLE IF NOT EXISTS customer_comms(id INTEGER PRIMARY KEY AUTOINCREMENT, booking_id INTEGER NOT NULL, channel TEXT NOT NULL, message_type TEXT NOT NULL, recipient TEXT, subject TEXT, body TEXT, status TEXT DEFAULT 'pending_approval', approved_by INTEGER, approved_at TEXT, sent_at TEXT, provider_message_id TEXT, created_at TEXT NOT NULL); ''' def now_iso(): return datetime.datetime.now().isoformat(timespec='seconds') def worker_log(worker, action, status, summary, started): execsql('INSERT INTO worker_runs(worker,action,status,summary_json,started_at,finished_at) VALUES(?,?,?,?,?,?)',(worker,action,status,json.dumps(summary,ensure_ascii=False),started,now_iso())) def run_scheduled_jobs(limit=20): started=now_iso(); rows=q("SELECT * FROM scheduled_jobs WHERE status='scheduled' AND run_at<=? ORDER BY run_at,id LIMIT ?",(started,int(limit))) results=[] for r in rows: execsql("UPDATE scheduled_jobs SET status='running',attempts=attempts+1,updated_at=? WHERE id=?",(now_iso(),r['id'])) try: payload=json.loads(r['payload_json']); jt=r['job_type']; out={} if jt=='content_asset_publish': aid=int(payload['asset_id']); out=publish_content_asset(aid, allow_scheduled=True) elif jt=='seo_sync': # Connector credentials may be absent; fail safely and leave an actionable log. out={'ok':False,'note':'SEO sync requires configured Search Console credentials.'} if os.getenv('PRISCA_GOOGLE_ACCESS_TOKEN'): end=datetime.date.today().isoformat(); start=(datetime.date.today()-datetime.timedelta(days=7)).isoformat() res=SearchConsoleAdapter().query(start,end,['query','page'],1000); out={'ok':True,'rows':len(((res.get('data') or {}).get('rows') or []))} elif jt=='daily_content_queue': dest=payload.get('destination',''); out=ContentWorker().calendar(dest,payload.get('start_date'),payload.get('days',7)) else: raise ValueError('Unknown job type: '+jt) status='done' if out.get('ok',True) else 'blocked' execsql('UPDATE scheduled_jobs SET status=?,last_error=?,updated_at=? WHERE id=?',(status,'' if status=='done' else json.dumps(out),now_iso(),r['id'])) results.append({'id':r['id'],'job_type':jt,'status':status,'result':out}) except Exception as e: execsql("UPDATE scheduled_jobs SET status='failed',last_error=?,updated_at=? WHERE id=?",(str(e),now_iso(),r['id'])) results.append({'id':r['id'],'job_type':r['job_type'],'status':'failed','error':str(e)}) worker_log('scheduler','run_due_jobs','success',{'processed':len(results),'results':results},started) return {'ok':True,'processed':len(results),'results':results} def publish_content_asset(asset_id, allow_scheduled=False): row=q('SELECT * FROM content_assets WHERE id=?',(int(asset_id),),one=True) if not row: raise ValueError('content asset not found') if row['status']!='approved': raise ValueError('asset must be approved before publishing') platform=row['platform']; result={} if platform in ('instagram','facebook'): result=MetaAdapter().publish(row) elif platform=='pinterest': result=PinterestAdapter().publish(row) elif platform=='youtube': result={'ok':False,'note':'YouTube upload adapter requires OAuth upload implementation; no silent publish.'} elif platform=='google_business': result={'ok':False,'note':'Google Business publishing requires a supported Business Profile integration.'} elif platform=='website': result={'ok':False,'note':'Website publishing remains approval-gated.'} else: raise ValueError('unsupported platform: '+platform) if not result.get('ok',False): raise APIError(result.get('error') or result.get('note') or 'provider refused publish') now=now_iso(); execsql("UPDATE content_assets SET status='published',publish_blocked=0 WHERE id=?",(int(asset_id),)); execsql('INSERT INTO integration_logs(platform,action,asset_id,status,response_json,created_at) VALUES(?,?,?,?,?,?)',(platform,'scheduled_publish',int(asset_id),'success',json.dumps(result,ensure_ascii=False),now)); return {'ok':True,'asset_id':int(asset_id),'status':'published','result':result} def db(): c=sqlite3.connect(DB); c.row_factory=sqlite3.Row; return c def init_db(): c=db(); c.executescript(SCHEMA) # Lightweight migrations for existing V5 databases. for table,col,decl in [('hotels','gst_included','INTEGER DEFAULT 0'),('hotels','published_rate','REAL DEFAULT 0'),('hotels','tax_status',"TEXT DEFAULT 'unknown'"),('hotels','room_features',"TEXT DEFAULT ''"),('hotels','blackout_dates',"TEXT DEFAULT ''"),('hotels','price_basis',"TEXT DEFAULT ''"),('hotels','extra_bed_basis',"TEXT DEFAULT ''"),('houseboats','gst_included','INTEGER DEFAULT 0'),('import_rows','room_features',"TEXT DEFAULT ''"),('import_rows','blackout_dates',"TEXT DEFAULT ''"),('import_rows','price_basis',"TEXT DEFAULT ''"),('import_rows','extra_bed_basis',"TEXT DEFAULT ''"),('import_rows','tax_status',"TEXT DEFAULT 'unknown'"),('import_rows','published_rate','REAL DEFAULT 0'),('import_rows','effective_cost','REAL DEFAULT 0'),('import_rows','duplicate_key',"TEXT DEFAULT ''")]: cols=[r['name'] for r in c.execute(f'PRAGMA table_info({table})').fetchall()] if col not in cols: c.execute(f'ALTER TABLE {table} ADD COLUMN {col} {decl}') defaults={'budget_markup':'10','luxury_markup':'15','standard_markup':'10','premium_markup':'12','custom_markup':'12','online_package_margin':'15','min_margin':'7','internal_api_key':'','db_version':'1','supplier_ai_version':'1.0','expiry_alert_days':'30'} for k,v in defaults.items(): c.execute('INSERT OR IGNORE INTO settings(key,value) VALUES(?,?)',(k,v)) now=datetime.datetime.now().isoformat(timespec='seconds') profiles=[ ('FabHotels','FabHotels|With_TAC|Without_TAC','fabhotels-v2','TAC is commercial commission, not GST. Preserve published/base and selling columns separately; do not auto-publish ambiguous TAC rows.','Known supplier format: With_TAC and Without_TAC sheets.'), ('Leisure Cities','Leisure Cities|Off-season|Season','leisure-v2','Treat Off-season and Season as separate rate seasons; preserve meal supplements and blackout-date source text.','Known leisure-city rate sheet pattern.'), ('Rainwood','Rainwood|Rate 2025|Published|TA Rate','rainwood-v2','TA Rate is preferred supplier cost when explicitly present; published/rack rate retained for reference. GST treatment must be explicit.','Known room/rate-sheet pattern.'), ('Houseboat','Houseboat|House Boat|Deluxe|Premium|Luxury','houseboat-v2','Category, bedrooms, occupancy and extra mattress are separate dimensions; GST remains separate from TAC.','Houseboat-specific occupancy/category parser.'), ('LEDD Cabs','LEDD|Cabs|Vehicle|KM','cab-v2','Cab is Prisca luxury transport by default; match only valid route/KM/vehicle capacity; no approximate rate.','Known vehicle/season/KM block pattern.')] for prof in profiles: c.execute('INSERT OR IGNORE INTO supplier_profiles(supplier,match_pattern,parser_version,commercial_rules,notes,updated_at) VALUES(?,?,?,?,?,?)',prof+(now,)) for st,dests in {'Andhra Pradesh': ['Visakhapatnam', 'Vijayawada', 'Tirupati'], 'Arunachal Pradesh': ['Tawang', 'Itanagar'], 'Assam': ['Guwahati', 'Kaziranga'], 'Bihar': ['Patna', 'Gaya'], 'Chhattisgarh': ['Raipur'], 'Goa': ['Goa', 'Panaji'], 'Gujarat': ['Ahmedabad', 'Surat', 'Vadodara', 'Rajkot'], 'Haryana': ['Gurgaon', 'Faridabad'], 'Himachal Pradesh': ['Shimla', 'Manali', 'Kasol', 'Dharamshala', 'Dalhousie'], 'Jharkhand': ['Ranchi'], 'Karnataka': ['Bangalore', 'Coorg', 'Mysore', 'Hampi', 'Chikmagalur'], 'Kerala': ['Kochi', 'Munnar', 'Thekkady', 'Alleppey', 'Alappuzha', 'Kumarakom', 'Kovalam', 'Varkala', 'Wayanad', 'Trivandrum'], 'Madhya Pradesh': ['Bhopal', 'Indore', 'Ujjain', 'Khajuraho', 'Gwalior'], 'Maharashtra': ['Mumbai', 'Pune', 'Mahabaleshwar', 'Lonavala', 'Nashik', 'Aurangabad'], 'Manipur': ['Imphal'], 'Meghalaya': ['Shillong', 'Cherrapunji'], 'Mizoram': ['Aizawl'], 'Nagaland': ['Kohima'], 'Odisha': ['Bhubaneswar', 'Puri'], 'Punjab': ['Amritsar', 'Ludhiana'], 'Rajasthan': ['Jaipur', 'Udaipur', 'Jodhpur', 'Jaisalmer', 'Pushkar', 'Ajmer'], 'Sikkim': ['Gangtok', 'Pelling'], 'Tamil Nadu': ['Chennai', 'Coimbatore', 'Madurai', 'Ooty', 'Kodaikanal', 'Rameswaram', 'Kanyakumari'], 'Telangana': ['Hyderabad'], 'Tripura': ['Agartala'], 'Uttar Pradesh': ['Lucknow', 'Varanasi', 'Ayodhya', 'Agra', 'Prayagraj', 'Kanpur'], 'Uttarakhand': ['Dehradun', 'Mussoorie', 'Rishikesh', 'Nainital', 'Jim Corbett'], 'West Bengal': ['Kolkata', 'Darjeeling'], 'Delhi': ['New Delhi'], 'Jammu & Kashmir': ['Srinagar', 'Gulmarg', 'Pahalgam'], 'Ladakh': ['Leh']}.items(): for dest in dests: c.execute('INSERT OR IGNORE INTO states(state,destination) VALUES(?,?)',(st,dest)) if c.execute('SELECT COUNT(*) n FROM users').fetchone()['n']==0: ph=hashlib.sha256(b'admin123').hexdigest(); c.execute('INSERT INTO users(name,email,password_hash,role,created_at) VALUES(?,?,?,?,?)',('Administrator','admin@priscaholidays.local',ph,'admin',now)) ph2=hashlib.sha256(b'agent123').hexdigest(); c.execute('INSERT INTO users(name,email,password_hash,role,created_at) VALUES(?,?,?,?,?)',('Demo Agent','agent@priscaholidays.local',ph2,'agent',now)) for src in ('website','woocommerce','wpforms','facebook','instagram','google','whatsapp','manual','other'): c.execute('INSERT OR IGNORE INTO lead_sources(source,active) VALUES(?,1)',(src,)) c.execute("INSERT OR IGNORE INTO sales_settings(key,value) VALUES('auto_assign','round_robin')") c.execute("INSERT OR IGNORE INTO sales_settings(key,value) VALUES('auto_followup_minutes','60')") for vn in ('FabHotels','Leisure Cities','Rainwood','Houseboat','LEDD Cabs'): c.execute('INSERT OR IGNORE INTO vendors(supplier,created_at,updated_at) VALUES(?,?,?)',(vn,now,now)) if c.execute('SELECT COUNT(*) n FROM houseboats').fetchone()['n']==0: hb=[('Supplier Sample','Deluxe Houseboat','Alleppey','Deluxe',1,2,8500,'per couple','AP',5,None,None,0,'1 bedroom / 2 pax'),('Supplier Sample','Premium Houseboat','Alleppey','Premium',1,2,12000,'per couple','AP',5,None,None,0,'1 bedroom / 2 pax'),('Supplier Sample','Luxury Houseboat','Alleppey','Luxury',1,2,17000,'per couple','AP',5,None,None,0,'1 bedroom / 2 pax'),('Supplier Sample','Deluxe Houseboat','Alleppey','Deluxe',2,4,13000,'per boat','AP',5,None,None,0,'2 bedrooms / 4 pax'),('Supplier Sample','Premium Houseboat','Alleppey','Premium',2,4,17000,'per boat','AP',5,None,None,0,'2 bedrooms / 4 pax'),('Supplier Sample','Luxury Houseboat','Alleppey','Luxury',2,4,23000,'per boat','AP',5,None,None,0,'2 bedrooms / 4 pax'),('Supplier Sample','Deluxe Houseboat','Alleppey','Deluxe',3,6,17500,'per boat','AP',5,None,None,0,'3 bedrooms / 6 pax'),('Supplier Sample','Premium Houseboat','Alleppey','Premium',3,6,22000,'per boat','AP',5,None,None,0,'3 bedrooms / 6 pax'),('Supplier Sample','Luxury Houseboat','Alleppey','Luxury',3,6,29000,'per boat','AP',5,None,None,0,'3 bedrooms / 6 pax'),('Supplier Sample','Deluxe Houseboat','Alleppey','Deluxe',4,8,22000,'per boat','AP',5,None,None,0,'4 bedrooms / 8 pax'),('Supplier Sample','Premium Houseboat','Alleppey','Premium',4,8,27000,'per boat','AP',5,None,None,0,'4 bedrooms / 8 pax'),('Supplier Sample','Luxury Houseboat','Alleppey','Luxury',4,8,36000,'per boat','AP',5,None,None,0,'4 bedrooms / 8 pax')] c.executemany('INSERT INTO houseboats(supplier,name,destination,category,bedrooms,pax,rate,rate_type,meal_plan,gst_percent,valid_from,valid_to,extra_mattress,notes,gst_included) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[x+(0,) for x in hb]) c.commit(); c.close(); load_integration_env() def q(sql,args=(),one=False): c=db(); cur=c.execute(sql,args); rows=cur.fetchall(); c.close(); return (rows[0] if rows else None) if one else rows def execsql(sql,args=()): c=db(); cur=c.execute(sql,args); c.commit(); rid=cur.lastrowid; c.close(); return rid def settings(): return {r['key']:r['value'] for r in q('SELECT key,value FROM settings')} def hashpw(p): return hashlib.sha256(p.encode()).hexdigest() def current_user(h): token=h.get('Cookie','').replace('session=','').split(';')[0] if not token:return None return q('SELECT u.* FROM sessions s JOIN users u ON u.id=s.user_id WHERE s.token=? AND s.expires_at>? AND u.active=1',(token,datetime.datetime.now().isoformat()),one=True) def json_out(handler,obj,status=200): b=json.dumps(obj,ensure_ascii=False,default=str).encode(); handler.send_response(status); handler.send_header('Content-Type','application/json; charset=utf-8'); handler.send_header('Content-Length',str(len(b))); handler.end_headers(); handler.wfile.write(b) def parse_json(h): n=int(h.headers.get('Content-Length','0')); return json.loads(h.rfile.read(n) or b'{}') def parse_date(s): if not s:return None for fmt in ('%Y-%m-%d','%d-%m-%Y','%d/%m/%Y','%d-%m-%y'): try:return datetime.datetime.strptime(s.strip(),fmt).date() except:pass return None def normalize_date(s): d=parse_date(s); return d.isoformat() if d else None def extract_date_range(text): m=re.search(r'(?:from|validity|effective)\s*[:\-]?\s*(\d{1,2}[\-/]\d{1,2}[\-/]\d{2,4})\s*(?:to|till|until|-)\s*(\d{1,2}[\-/]\d{1,2}[\-/]\d{2,4})',text,re.I) if m:return normalize_date(m.group(1)),normalize_date(m.group(2)) return None,None def import_text(path, filename): ext=os.path.splitext(filename)[1].lower() text='' try: if ext=='.pdf': from pypdf import PdfReader r=PdfReader(path); text='\n'.join((p.extract_text() or '') for p in r.pages) if len(text.strip())<200: try: import fitz, pytesseract from PIL import Image doc=fitz.open(path); chunks=[] for i,p in enumerate(doc): pix=p.get_pixmap(matrix=fitz.Matrix(1.5,1.5),alpha=False) im=Image.open(io.BytesIO(pix.tobytes('png'))) chunks.append(pytesseract.image_to_string(im)) if i>=5: break text='\n'.join(chunks) except Exception as e: text += f'\n[OCR unavailable: {e}]' elif ext in ('.xlsx','.xls'): from openpyxl import load_workbook wb=load_workbook(path,read_only=True,data_only=True) parts=[] for ws in wb.worksheets: parts.append(f'--- SHEET: {ws.title} ---') for row in ws.iter_rows(values_only=True): vals=[str(v) if v is not None else '' for v in row] if any(vals): parts.append(' | '.join(vals)) text='\n'.join(parts) elif ext=='.docx': from docx import Document d=Document(path); text='\n'.join(p.text for p in d.paragraphs) for t in d.tables: for r in t.rows:text+='\n'+' | '.join(c.text for c in r.cells) elif ext=='.csv': with open(path,'r',encoding='utf-8-sig',errors='ignore') as f:text=f.read() elif ext in ('.png','.jpg','.jpeg','.webp'): import pytesseract from PIL import Image text=pytesseract.image_to_string(Image.open(path)) else: with open(path,'r',encoding='utf-8',errors='ignore') as f:text=f.read() except Exception as e: text=f'[Extraction error: {e}]' return text def calculate_margin_price(cost, margin_percent): """Return selling price required to achieve a gross margin percentage.""" cost=float(cost or 0); margin=float(margin_percent or 0) if cost < 0: raise ValueError('cost cannot be negative') if margin < 0 or margin >= 100: raise ValueError('margin must be >= 0 and < 100') return cost/(1-(margin/100)) def round_commercial_price(value, mode='nearest_99'): """Conservative commercial rounding; never rounds below the calculated price.""" value=float(value or 0) if value <= 0: return 0.0 if mode == 'nearest_99': import math base=math.floor(value/1000)*1000 candidates=[base+999, base+1999] for c in sorted(candidates): if c >= value: return float(c) return float(base+1999) return round(value,2) def price_breakdown(cost_components, margin_percent=15, commercial_rounding=False): """Central pricing contract used by the AI worker and the legacy package builder.""" clean=[]; total=0.0 for item in cost_components or []: amount=float(item.get('amount') or 0) if amount < 0: raise ValueError('component amount cannot be negative') row={k:item[k] for k in item if k != 'amount'}; row['amount']=round(amount,2) clean.append(row); total += amount standard=calculate_margin_price(total, margin_percent) recommended=round_commercial_price(standard) if commercial_rounding else round(standard,2) # Never present a rounded price as if it were the exact target-margin calculation. actual_margin=(recommended-total)/recommended*100 if recommended else 0 return {'components':clean,'supplier_cost':round(total,2),'target_margin_percent':float(margin_percent), 'calculated_selling_price':round(standard,2),'recommended_selling_price':round(recommended,2), 'actual_margin_percent':round(actual_margin,4)} def price_category(rate): # Commercial package band based on supplier base rate before GST/TAC. # <= 2500: Budget (2-3 star band) # > 2500 to <= 5000: Mid Budget (3-4 star band) # > 5000: Luxury try: r = float(rate or 0) except Exception: r = 0 if r <= 2500: return 'Budget' if r <= 5000: return 'Mid Budget' return 'Luxury' def normalize_price_categories(): c = db() c.execute("""UPDATE hotels SET category = CASE WHEN COALESCE(rate,0) <= 2500 THEN 'Budget' WHEN COALESCE(rate,0) <= 5000 THEN 'Mid Budget' ELSE 'Luxury' END WHERE active=1""") c.commit() c.close() def guess_category(rating, name=''): if rating is None:return 'Standard' try:r=float(rating) except:return 'Standard' if r>=4.5:return 'Luxury' if r>=4.0:return 'Premium' if r>=3.5:return 'Standard' return 'Budget' def seed_cabs(): c=db() if c.execute('SELECT COUNT(*) n FROM cabs').fetchone()['n']>0:return vehicles=['Sedan','Ertiga','Innova','Crysta','Luxury 9-Seat TT','12-Seat TT','17-Seat TT','21-Seat Coach','26-Seat TT','27-Seat Coach'] blocks=[ ('Cochin-Alleppey(1N)-Cochin','1N/2D',300,[5500,6500,7500,9000,16500,10000,11500,17000,16500,21000]), ('Cochin-Munnar(2N)-Cochin','2N/3D',400,[7800,8900,10000,12500,23500,14000,16000,24500,23500,29500]), ('Cochin-Munnar(2N)-Alleppey(1N)-Cochin','3N/4D',550,[10650,12000,13500,16600,31750,18800,21000,33500,32000,40000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Cochin','4N/5D',650,[11500,14000,16000,19800,38500,22500,25500,40000,38000,48500]), ('Cochin-Munnar(2N)-Alleppey(1N)-Varkala(1N)-Cochin drop','4N/5D',900,[16800,19200,21500,25800,48000,28500,32000,48000,46000,58500]), ('Cochin-Munnar(2N)-Alleppey(1N)-Varkala(1N)-Trivandrum drop','4N/5D',1000,[18500,21000,23500,28000,51500,31000,35000,52000,49000,62500]), ('Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Cochin','5N/6D',680,[12800,15200,17300,21500,42500,24500,28000,45000,42500,54500]), ('1N Cochin-Munnar(2N)-Thekkady(1N)-Alleppey-Cochin','5N/6D',730,[13500,16000,18000,22500,44000,24500,29000,47000,44000,56000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Varkala(1N)-Cochin drop','5N/6D',930,[17500,20000,22600,27500,52000,31000,35000,53500,50500,65000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Varkala(1N)-Trivandrum drop','5N/6D',1030,[19200,22000,24600,30000,55000,33000,37500,56500,53500,68000]), ('Cochin-Munnar(2N)-Alleppey Houseboat(1N)-Kovalam(2N)-Trivandrum','5N/6D',1000,[18500,21500,24000,29000,54000,32000,36500,55000,52000,66000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Kovalam(2N)-Cochin/Trivandrum','6N/7D',1050,[19500,22700,25700,31000,59000,34600,39000,60500,57000,73000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Kovalam(2N)-Kanyakumari Day Trip-Trivandrum','6N/7D',1250,[23450,26500,30000,35000,66500,40000,45000,68000,64500,82000]), ('Cochin-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Kovalam(2N)-Kanyakumari(1N)-Trivandrum','7N/8D',1250,[24000,27200,30700,37000,70500,42000,47500,72000,69000,86500]), ('Cochin(1N)-Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Kovalam(2N)-Kanyakumari(1N)-Trivandrum','8N/9D',1350,[25500,29500,33500,40000,77000,45000,51500,79000,75500,95000]), ('Munnar(2N)-Thekkady(1N)-Alleppey(1N)-Kovalam(2N)-Kanyakumari(1N)-Rameswaram(1N)-Madurai(1N)','9N/10D',1800,[34000,39000,44000,52500,96000,58000,65000,97500,94000,116500])] for route,dur,km,rates in blocks: for v,rate in zip(vehicles,rates): c.execute('INSERT INTO cabs(supplier,route,duration,km,vehicle,rate,valid_from,valid_to,season_type,notes) VALUES(?,?,?,?,?,?,?,?,?,?)',('LEDD Cabs',route,dur,km,v,rate,'2025-10-01','2026-05-31','Season','Not valid on long weekends/peak seasons; GST 5% extra; airport-to-airport KM basis')) c.commit();c.close() def seed_rainwood(): # Load the supplied Rainwood Kerala rate sheets into the master hotel database. c=db() if c.execute("SELECT 1 FROM hotels WHERE source_file LIKE 'Rainwood %' LIMIT 1").fetchone(): c.close(); return seasons=[ ('Rainwood Summer 2025','2025-04-01','2025-09-30',[ ('Eve Munnar','Munnar','Luxury',4,[('Premium Lake View',5250,6550),('Jacuzzi Suite with Lake View',9000,10300)]), ('Heaven Inn Resort Munnar','Munnar','Luxury',4,[('Queen Suites AC',4000,5200),('Kings Suites AC',5000,6200),('Royal Suites AC',6000,7200)]), ('Rainwood Aurum Munnar','Munnar','Budget',3,[('Deluxe AC',3000,4000),('Garden Cottage AC',4000,5000)]), ('Arbour Resort Munnar','Munnar','Budget',3,[('Club Rooms Non AC',2700,3700),('Club Rooms AC',3000,4000),('Club with Valley View AC',4000,5000),('Garden Cottage AC',5000,6000),('Jacuzzi Suite AC',6000,7000)]), ('TuCasa Resort Munnar','Munnar','Budget',None,[('Deluxe Room',2500,3500),('Super Deluxe',3000,4000),('Suite Room',4500,5500)]), ('Casa Bella Thekkady','Thekkady','Luxury',4,[('Premium Cottage',5000,6500),('Duplex Cottage',9000,12000)]), ('Spices Lap Resort','Thekkady','Luxury',4,[('Heritage Deluxe AC',4000,5200),('Garden Villa AC',5500,6700),('Premium Heritage AC',6500,7700),('Jacuzzi Villa AC',8000,9200),('Tree House AC',9000,10200)]), ('The Patio Thekkady','Thekkady','Budget',3,[('Deluxe Room',2200,3200),('Deluxe AC Room',2700,3700),('Suite Room AC',3500,4500)]), ('The Serene Horizon','Thekkady','Luxury',5,[('Imperial Deluxe',5750,7350),('Grandeur Cottage',7250,8850),('Elite Premium Jacuzzi Suite',9000,10600),('Serenity Suite with Jacuzzi & Tub',11000,12600)]), ('Aadisaktthi Resort Kovalam','Kovalam','Luxury',4,[('Deluxe AC',4250,5450),('Super Deluxe AC',5250,6450),('Premium AC',6250,7450)]), ('Lakeshore Resort & Houseboat','Alleppey','Budget',3,[('Deluxe Lake View Room',4500,6000),('Premium Lake View Room',5500,7000),('Island Cottages',6500,8000)]), ('The Classik Fort Inn & Suite','Kochi','Luxury',4,[('Deluxe Room',2800,4100),('Suite Room',4000,5300)]) ]), ('Rainwood Winter 2025','2025-10-01','2026-03-31',[ ('Heaven Inn Resort Munnar','Munnar','Luxury',4,[('Queen Suites AC',5000,6200),('Kings Suites AC',6000,7200),('Royal Suites AC',7000,8200)]), ('Rainwood Aurum Munnar','Munnar','Budget',3,[('Deluxe AC',4000,5000),('Garden Cottage AC',4500,5500)]), ('Arbour Resort Munnar','Munnar','Budget',3,[('Club Rooms Non AC',3500,4500),('Club Rooms AC',3800,4800),('Club with Valley View AC',4500,5500),('Garden Cottage AC',5500,6500),('Jacuzzi Suite AC',7500,8500)]), ('TuCasa Resort Munnar','Munnar','Budget',None,[('Deluxe Room',2600,3600),('Super Deluxe',3100,4100),('Suite Room',4600,5600)]), ('Casa Bella Thekkady','Thekkady','Luxury',4,[('Premium Cottage',5000,6500),('Duplex Cottage',9500,12500)]), ('Spices Lap Resort','Thekkady','Luxury',4,[('Heritage Deluxe AC',4500,5700),('Garden Villa AC',6000,7200),('Premium Heritage AC',6500,7700),('Jacuzzi Villa AC',8000,9200),('Tree house AC',9000,10200)]), ('The Patio Thekkady','Thekkady','Budget',3,[('Deluxe Room',2600,3600),('Deluxe AC Room',3100,4100),('Suite Room AC',4300,5300)]), ('The Serene Horizon','Thekkady','Luxury',5,[('Imperial Deluxe',6500,7900),('Grandeur Cottage',8000,9600),('Elite Premium Jacuzzi Suite',10000,11600),('Serenity Suite with Jacuzzi & Tub',12000,13600)]), ('Aadisaktthi Resort Kovalam','Kovalam','Luxury',4,[('Deluxe AC',5000,6200),('Super Deluxe AC',6000,7200),('Premium AC',7000,8200)]), ('Lakeshore Resort & Houseboat','Alleppey','Luxury',4,[('Deluxe Lake View Room',5000,6500),('Premium Lake View Room',6000,7500),('Island Cottages',7000,8500)]), ('The Classik Fort Inn & Suite','Kochi','Luxury',4,[('Deluxe Room',3000,4300),('Suite Room',4300,5600)]) ])] for src,vf,vt,props in seasons: for hotel,city,packcat,rating,rooms in props: for room,cpai,maprate in rooms: low=room.lower(); features=[k for k in ['jacuzzi','bathtub','tub','villa','cottage','tree house','lake view','valley view','premium','suite'] if k in low] notes=f'Rainwood supplied rate sheet. Supplier MAP/reference rate INR {maprate}. Star/category from supplier cover sheet.' c.execute("INSERT INTO hotels(supplier,hotel_name,city,category,rating,source_file,room_type,occupancy,meal_plan,rate,extra_adult,extra_child,rate_type,valid_from,valid_to,season_type,notes,room_features,price_basis,extra_bed_basis,gst_included,tax_status) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",('Rainwood',hotel,city,packcat,rating,src,room,'2','CPAI',cpai,0,0,'CPAI',vf,vt,'Summer' if 'Summer' in src else 'Winter',notes,', '.join(features),'per room per night','supplier-specific extra bed applies',0,'unknown')) hb=[('Rainwood','Premium Glass Covered Houseboat','Alleppey','Premium',1,2,11500,'per boat','APAI',0,None,None,0,'Rainwood summer 2025',0),('Rainwood','Premium 2 Bed with Upper Deck','Alleppey','Premium',2,4,15000,'per boat','APAI',0,None,None,0,'Rainwood summer 2025',0)] for row in hb: c.execute('INSERT INTO houseboats(supplier,name,destination,category,bedrooms,pax,rate,rate_type,meal_plan,gst_percent,valid_from,valid_to,extra_mattress,notes,gst_included) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',row) c.commit(); c.close() def seed_excel(): # Seed structured sources supplied in this conversation. Idempotent by source_file. c=db() def already(src):return c.execute('SELECT 1 FROM hotels WHERE source_file=? LIMIT 1',(src,)).fetchone() is not None # Rate 2025 f='/mnt/data/Rate 2025.xlsx' if os.path.exists(f) and not already('Rate 2025.xlsx'): from openpyxl import load_workbook wb=load_workbook(f,read_only=True,data_only=True) for ws in wb.worksheets: rows=list(ws.iter_rows(values_only=True)); hotel=str(rows[0][0]); city='Munnar'; vf=vt=None m=re.search(r'(\d{1,2}-\d{2}-\d{4})\s+to\s+(\d{1,2}-\d{2}-\d{4})',str(rows[3][0])) if m: vf=normalize_date(m.group(1)); vt=normalize_date(m.group(2)) for r in rows[5:]: if r[0] and isinstance(r[3],(int,float)): c.execute('INSERT INTO hotels(supplier,hotel_name,city,category,source_file,source_sheet,room_type,occupancy,meal_plan,rate,extra_adult,rate_type,valid_from,valid_to,season_type,notes) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',('Rainwood',hotel,city,'Standard','Rate 2025.xlsx',ws.title,str(r[0]),str(r[1] or ''),str(r[2] or ''),float(r[4] or r[3]),float(r[5] or 0),'TA/Net',vf,vt,'Peak' if 'Peak' in ws.title else 'Regular','Published rate '+str(r[3]))) # Leisure Cities f='/mnt/data/Leisure Cities Rate sheet (2).xlsx' if os.path.exists(f) and not already('Leisure Cities Rate sheet (2).xlsx'): from openpyxl import load_workbook ws=load_workbook(f,read_only=True,data_only=True).active for row in ws.iter_rows(min_row=3,values_only=True): if not row[0] or not row[1]:continue def num(x): try:return float(x) except:return None off,sea=num(row[5]),num(row[6]); cat='Standard' for rt,rate,season in [('Double',off,'Off-season'),('Double',sea,'Season')]: if rate is not None:c.execute('INSERT INTO hotels(supplier,hotel_name,city,category,address,source_file,room_type,occupancy,meal_plan,rate,extra_adult,valid_from,valid_to,season_type,notes) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',('Leisure Cities',str(row[0]),str(row[1]),cat,str(row[3] or ''),'Leisure Cities Rate sheet (2).xlsx',rt,'2','CPAI',rate,num(row[10]),None,None,season,f'Veg meal {row[8]}; Non-veg meal {row[9]}; blackout field source={row[7]}')) # FabHotels f='/mnt/data/FabHotels_ PRICE SHEET PANINIDA -3000+HOTELS (1).xlsx' if os.path.exists(f) and not already('FabHotels master.xlsx'): from openpyxl import load_workbook wb=load_workbook(f,read_only=True,data_only=True); ws=wb['With_TAC_10%'] for row in ws.iter_rows(min_row=2,values_only=True): if not row[0] or not row[1]:continue city,name=str(row[0]),str(row[1]); rating=row[15] if len(row)>15 else None cat=str(row[13] or guess_category(rating,name)) if len(row)>13 else guess_category(rating,name) for occ,idx in [('Single',8),('Double',9),('Triple',10)]: rate=row[idx] if len(row)>idx and isinstance(row[idx],(int,float)) else None if rate is not None:c.execute('INSERT INTO hotels(supplier,hotel_name,city,category,rating,address,source_file,room_type,occupancy,meal_plan,rate,tac_percent,notes) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)',('FabHotels',name,city,cat,rating,str(row[12] or ''),'FabHotels master.xlsx','Standard',occ,'Room Only',float(rate),float(row[7] or 0),f'Base rates from With_TAC_10%; link={row[11] if len(row)>11 else ""}')) # Byke unit details + fact sheet as hotel master f='/mnt/data/THE BYKE FactSheet.xlsx' if os.path.exists(f) and not already('THE BYKE FactSheet.xlsx'): from openpyxl import load_workbook wb=load_workbook(f,read_only=True,data_only=True); ws=wb['Unit details'] for row in ws.iter_rows(min_row=2,values_only=True): if not row[1]:continue c.execute('INSERT INTO hotels(supplier,hotel_name,city,category,source_file,notes) VALUES(?,?,?,?,?,?)',('The Byke',str(row[1]).strip(),str(row[9] or ''),'Standard','THE BYKE FactSheet.xlsx',f'Rooms: {row[2]}; facilities: {row[6]}; address: {row[8]}; check-in/out: {row[3]}')) c.commit(); c.close() RATE_WORDS = re.compile(r'(rack|rack rate|published|bar rate|maximum selling)', re.I) NET_WORDS = re.compile(r'(net rate|net\b|ta rate|travel agent rate|after tac)', re.I) TAC_RE = re.compile(r'(?:tac|commission|comm\.?)[^0-9]{0,15}(\d+(?:\.\d+)?)\s*%', re.I) GST_RE = re.compile(r'(?:gst|tax)[^0-9]{0,15}(\d+(?:\.\d+)?)\s*%', re.I) DATE_RANGE_RE = re.compile(r'(\d{1,2}[/-]\d{1,2}[/-]\d{2,4})\s*(?:to|-|–|—)\s*(\d{1,2}[/-]\d{1,2}[/-]\d{2,4})', re.I) MONEY_RE = re.compile(r'(?:₹|rs\.?|inr)?\s*([0-9]{1,3}(?:[, ][0-9]{3})+|[0-9]{3,7})(?:\.\d+)?', re.I) def norm_money(v): if v is None: return None try: x=str(v).replace('₹','').replace('Rs','').replace('rs','').replace(',','').strip() return float(x) except: return None def infer_rate_type(text): t=(text or '').lower() if 'rack rate' in t or 'rack' in t: return 'Rack' if 'net rate' in t or re.search(r'\bta rate\b',t) or 'travel agent rate' in t: return 'Net/TA' if 'contract rate' in t: return 'Contract' return 'Unclassified' def infer_gst(text): t=(text or '') m=GST_RE.search(t) pct=float(m.group(1)) if m else 0 inc=bool(re.search(r'(gst|tax)[^\n]{0,30}(included|inclusive|incl\.)',t,re.I)) exc=bool(re.search(r'(gst|tax)[^\n]{0,30}(extra|additional|excluded|exclusive)',t,re.I)) return pct, inc, exc def infer_tac(text): m=TAC_RE.search(text or '') return float(m.group(1)) if m else 0 def infer_category(text): t=(text or '').lower() for x in ('luxury','premium','deluxe','standard','budget'): if x in t: return x.title() m=re.search(r'(\d)\s*star',t) if m: return f"{m.group(1)} Star" return 'Unclassified' def infer_room_features(text): t=(text or '').lower(); feats=[] for x in ['jacuzzi','bathtub','private pool','pool villa','villa','cottage','tree house','beach house','beachfront','lake view','valley view','mountain view','river view','honeymoon suite','suite']: if x in t: feats.append(x.title()) return ', '.join(feats) def classify_tax_status(text, pct=0, inc=False, exc=False): t=(text or '').lower() if inc or re.search(r'(gst|tax)[^\n]{0,35}(included|inclusive|incl\.)',t,re.I): return 'included' if exc or re.search(r'(gst|tax)[^\n]{0,35}(extra|additional|excluded|exclusive)',t,re.I): return 'extra' return 'unknown' if pct else 'not_stated' def infer_price_basis(text): t=(text or '').lower() if re.search(r'per\s*(room|night)|room/night',t): return 'per room per night' if re.search(r'per\s*couple|couple',t): return 'per couple' if re.search(r'per\s*person|pax|pp',t): return 'per person' if re.search(r'per\s*boat',t): return 'per boat' if re.search(r'per\s*vehicle|per\s*cab',t): return 'per vehicle' return '' def infer_extra_bed_basis(text): t=(text or '').lower() if 'extra bed' in t or 'extra mattress' in t: if 'with meal' in t or 'meal' in t: return 'supplier mentions extra bed/mattress with meal context; review' return 'extra bed/mattress mentioned; review supplier rule' return '' def compute_effective_cost(rate, rate_type, tac_percent, gst_percent, gst_included): if rate is None: return 0 r=float(rate) rt=(rate_type or '').lower() # Rack rates can yield a net cost only when TAC is explicitly supplied. if rt=='rack' and tac_percent: r=r*(1-float(tac_percent)/100.0) # Net/TA rates are already net; never subtract TAC again. if gst_percent and not gst_included: r=r*(1+float(gst_percent)/100.0) return round(r,2) def duplicate_key_for_row(r): return '|'.join(str(r.get(k) or '').strip().lower() for k in ['supplier','hotel_name','destination','room_type','occupancy','meal_plan','rate_type','valid_from','valid_to','rate']) def enrich_row(r, raw_text=''): text=' '.join(str(r.get(k) or '') for k in ['hotel_name','room_type','meal_plan','notes'])+' '+(raw_text or '') r['room_features']=r.get('room_features') or infer_room_features(text) r['price_basis']=r.get('price_basis') or infer_price_basis(text) r['extra_bed_basis']=r.get('extra_bed_basis') or infer_extra_bed_basis(text) r['tax_status']=r.get('tax_status') or classify_tax_status(text,r.get('gst_percent') or 0,bool(r.get('gst_included')),False) r['published_rate']=r.get('published_rate') or (r.get('rate') or 0) r['effective_cost']=compute_effective_cost(r.get('rate'),r.get('rate_type'),r.get('tac_percent') or 0,r.get('gst_percent') or 0,bool(r.get('gst_included'))) r['duplicate_key']=duplicate_key_for_row(r) warns=[x for x in [r.get('warnings','')] if x] if (r.get('gst_percent') or 0) and r.get('tax_status')=='unknown': warns.append('GST percentage found but included/extra treatment is unclear.') if (r.get('rate_type') or '')=='Rack' and not (r.get('tac_percent') or 0): warns.append('Rack rate has no explicit TAC; do not assume commission.') if (r.get('rate_type') or '')=='Net/TA' and (r.get('tac_percent') or 0): warns.append('Net/TA and TAC both detected; TAC will NOT be subtracted again. Review supplier rule.') if r.get('extra_bed_basis'): warns.append(r['extra_bed_basis']) if not r.get('valid_to'): warns.append('Rate validity end date not detected.') r['warnings']='; '.join(dict.fromkeys(warns)) return r def supplier_profile_for(filename, text=''): t=(os.path.basename(filename)+' '+(text or '')).lower() profiles=q('SELECT * FROM supplier_profiles ORDER BY id') for pr in profiles: pats=[x.strip().lower() for x in (pr['match_pattern'] or '').split('|') if x.strip()] if pats and sum(1 for x in pats if x in t)>=1: return dict(pr) return {'supplier':'Generic','parser_version':'generic-v1','commercial_rules':'Unknown supplier format; Admin review required.'} def normalize_rate_text(text, filename): """Deterministic supplier-rate normalizer. It deliberately stages uncertain rows for Admin review.""" rows=[]; lines=[x.strip() for x in (text or '').splitlines() if x.strip()] source=os.path.basename(filename); supplier=''; profile=supplier_profile_for(filename, text) # supplier/company hints for line in lines[:80]: if re.search(r'(hotel|resort|cabs|holidays|travel|tour|houseboat|hospitality)',line,re.I) and len(line)<120: supplier=line[:120]; break vf=vt=None for line in lines[:100]: m=DATE_RANGE_RE.search(line) if m: vf=normalize_date(m.group(1)); vt=normalize_date(m.group(2)); break tac=infer_tac('\n'.join(lines[:250])); gst,gstinc,gstexc=infer_gst('\n'.join(lines[:250])); rate_type=infer_rate_type('\n'.join(lines[:120])) current_context={'hotel':'','destination':'','category':'Unclassified','room':'','meal':'','occupancy':'','notes':[]} for i,line in enumerate(lines): # pipe/tab tables: strongest signal parts=[p.strip() for p in re.split(r'\s*\|\s*|\t+',line) if p.strip()] money=[] for p in parts: n=norm_money(p) if n is not None and n>=100: money.append(n) if len(parts)>=3 and money: name=parts[0] # avoid headers if re.search(r'(hotel|property|name|city|destination|rate|room)',name,re.I) and not money: continue dest=parts[1] if len(parts)>1 else '' room=parts[2] if len(parts)>2 else '' meal=parts[3] if len(parts)>3 else '' rate=money[-1] rows.append({'data_type':'hotel','supplier':supplier,'hotel_name':name,'destination':dest,'category':infer_category(line),'rating':None,'room_type':room,'occupancy':'','meal_plan':meal,'rate':rate,'rate_type':rate_type,'tac_percent':tac,'gst_percent':gst,'gst_included':1 if gstinc else 0,'extra_adult':0,'extra_child':0,'valid_from':vf,'valid_to':vt,'notes':line,'confidence':0.82,'warnings':('GST treatment inferred; review' if gst and not (gstinc or gstexc) else '')}) continue # common hotel rate line: Hotel - Room - Meal - ₹rate mm=list(MONEY_RE.finditer(line)) if mm: rate=norm_money(mm[-1].group(1)); before=line[:mm[-1].start()].strip(' -:') if rate and rate>=500: room=infer_room_features(before) cat=infer_category(before) meal='' for mplan in ['APAI','MAPI','CPAI','MAP','CP','AP','EP']: if re.search(r'\b'+re.escape(mplan)+r'\b',before,re.I): meal=mplan; break # destination from explicit known phrase if present dest='' for st in ['Munnar','Thekkady','Alleppey','Alappuzha','Kovalam','Varkala','Kochi','Kumarakom','Wayanad','Kanyakumari','Trivandrum']: if re.search(r'\b'+re.escape(st)+r'\b',before,re.I): dest=st; break rows.append({'data_type':'hotel','supplier':supplier,'hotel_name':before[:120],'destination':dest,'category':cat,'rating':None,'room_type':room or 'Unclassified','occupancy':'','meal_plan':meal,'rate':rate,'rate_type':rate_type,'tac_percent':tac,'gst_percent':gst,'gst_included':1 if gstinc else 0,'extra_adult':0,'extra_child':0,'valid_from':vf,'valid_to':vt,'notes':line,'confidence':0.58,'warnings':'Parsed from unstructured text; Admin review required'}) # Detect houseboat-specific rows for r in rows: t=(r['hotel_name']+' '+r['room_type']+' '+r['notes']).lower() if 'houseboat' in t: r['data_type']='houseboat'; r['category']=infer_category(t) if not rows: # create one analysis row so admin knows why publication is blocked rows.append({'data_type':'unknown','supplier':supplier,'hotel_name':'','destination':'','category':'Unclassified','rating':None,'room_type':'','occupancy':'','meal_plan':'','rate':None,'rate_type':rate_type,'tac_percent':tac,'gst_percent':gst,'gst_included':1 if gstinc else 0,'extra_adult':0,'extra_child':0,'valid_from':vf,'valid_to':vt,'notes':'No reliable tabular rate rows detected. Upload a clearer supplier sheet or review manually.','confidence':0.05,'warnings':'No rate rows detected; cannot auto-publish'}) for r in rows: if profile.get('supplier') and profile.get('supplier')!='Generic': r['supplier']=r.get('supplier') or profile.get('supplier') r['notes']=((r.get('notes') or '')+' | Parser: '+profile.get('parser_version','')) return [enrich_row(r, '\n'.join(lines[:120])) for r in rows] def known_destination(text): t=str(text or '') names=['Munnar','Thekkady','Alleppey','Alappuzha','Kovalam','Varkala','Kochi','Kumarakom','Wayanad','Kanyakumari','Trivandrum','Goa','Shimla','Manali','Kasol','Darjeeling','Agra','Jaipur','Udaipur','Jodhpur','Jaisalmer','Ooty','Kodaikanal','Coorg','Mysore','Pondicherry','Puducherry','Dehradun','Rishikesh','Nainital'] for x in names: if re.search(r'\b'+re.escape(x)+r'\b',t,re.I): return x return '' def parse_structured_xlsx(path, filename): from openpyxl import load_workbook wb=load_workbook(path,read_only=True,data_only=True) profile=supplier_profile_for(filename, ' '.join(wb.sheetnames)) out=[] for ws in wb.worksheets: rows=list(ws.iter_rows(values_only=True)) if not rows: continue # Find the most likely header row in the first 4 rows. header_idx=0; head=[str(x).strip().lower() if x is not None else '' for x in rows[0]] for hi,hr in enumerate(rows[:4]): hh=[str(x).strip().lower() if x is not None else '' for x in hr] if any('hotel name' in x or 'room type' in x or 'published rates' in x or 'ta rates' in x for x in hh): header_idx=hi; head=hh; break if 'without_tac' in ws.title.lower() and any('tac' in str(x).lower() for x in rows[0] if x is not None)==False and any('with_tac' in w.title.lower() for w in wb.worksheets): continue # Heaven Inn / room-sheet pattern: metadata in first 4 rows, headers row 5. if len(rows)>=6 and any('room type' in str(x).lower() for x in rows[4] if x is not None): hotel=str(rows[0][0] or '').strip(); city=known_destination(' '.join(str(x or '') for rr in rows[:4] for x in rr)); address=str(rows[2][0] or '').strip(); vf=vt=None m=DATE_RANGE_RE.search(str(rows[3][0] or '')) if m: vf=normalize_date(m.group(1)); vt=normalize_date(m.group(2)) for rr in rows[5:]: if not rr[0] or not isinstance(rr[3],(int,float)): continue published=float(rr[3]); ta=float(rr[4]) if isinstance(rr[4],(int,float)) else None; extra=float(rr[5]) if isinstance(rr[5],(int,float)) else 0 out.append({'data_type':'hotel','supplier':filename,'hotel_name':hotel,'destination':city or 'Munnar','category':infer_category(str(rr[0])),'rating':None,'room_type':str(rr[0]),'occupancy':'Double Occupancy','meal_plan':str(rr[2] or ''),'rate':ta if ta is not None else published,'rate_type':'Net/TA' if ta is not None else 'Rack','tac_percent':0,'gst_percent':0,'gst_included':0,'extra_adult':extra,'extra_child':0,'valid_from':vf,'valid_to':vt,'notes':f'Published/Rack: ₹{published:,.0f}; TA Rate: ₹{ta:,.0f}' if ta is not None else f'Published/Rack: ₹{published:,.0f}','confidence':0.97,'warnings':'TA rate selected as supplier cost; published/rack rate retained in notes. Verify supplier tax treatment.'}) continue # FabHotels With_TAC sheet if head and 'hotel name' in head and 'tac %' in head: idx={h:i for i,h in enumerate(head)} for rr in rows[header_idx+1:]: if len(rr)<=max(idx.get('hotel name',1),idx.get('city',0)) or not rr[idx.get('hotel name',1)]: continue city=str(rr[idx.get('city',0)] or ''); name=str(rr[idx.get('hotel name',1)] or ''); tac=float(rr[idx['tac %']]) if isinstance(rr[idx['tac %']],(int,float)) else 0 cat=str(rr[idx.get('category',13)] or 'Unclassified'); rating=rr[idx.get('rating',15)] if idx.get('rating',15)=len(rr) or not isinstance(rr[col],(int,float)): continue sell_col={'Single':8,'Double':9,'Triple':10}[occ]; sell=rr[sell_col] if sell_col'' ORDER BY id DESC",(),one=True) if not existing and email: existing=q('SELECT * FROM leads WHERE lower(email)=lower(?) ORDER BY id DESC LIMIT 1',(email,),one=True) owner=choose_lead_owner() if existing: lid=existing['id'] execsql('UPDATE leads SET name=COALESCE(NULLIF(?,\'\'),name),email=COALESCE(NULLIF(?,\'\'),email),destination=COALESCE(NULLIF(?,\'\'),destination),travel_date=COALESCE(NULLIF(?,\'\'),travel_date),message=COALESCE(NULLIF(?,\'\'),message),updated_at=? WHERE id=?',(name,email,d.get('destination',''),d.get('travel_date'),d.get('message',''),now,lid)) execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'duplicate_ingest',f'New enquiry received from {source}; merged into existing lead',None,now)) status='merged' else: lid=execsql('INSERT INTO leads(name,phone,email,source,destination,travel_date,travellers,budget,message,stage,priority,owner_id,next_followup_at,notes,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(name,phone,email,source,d.get('destination',''),d.get('travel_date'),int(d.get('travellers') or 1),float(d.get('budget') or 0),d.get('message',''),'new','high' if d.get('destination') and d.get('travel_date') else 'normal',owner,None,d.get('notes',''),now,now)) execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'created',f'Lead captured from {source}',None,now)) if owner: execsql('INSERT INTO sales_tasks(lead_id,owner_id,title,detail,due_at,status,created_at) VALUES(?,?,?,?,?,?,?)',(lid,owner,'First lead follow-up','Review new website enquiry and contact the customer.',(datetime.datetime.now()+datetime.timedelta(minutes=int(settings().get('auto_followup_minutes','60')))).isoformat(),'pending',now)) status='created' execsql('INSERT INTO lead_ingest_events(fingerprint,source,payload_json,lead_id,created_at) VALUES(?,?,?,?,?)',(fp,source,json.dumps(d,ensure_ascii=False),lid,now)) return {'ok':True,'duplicate':status!='created','lead_id':lid,'status':status,'owner_id':owner} def ensure_ops_for_booking(booking_id): now=now_iso(); b=q('SELECT * FROM bookings WHERE id=?',(int(booking_id),),one=True) if not b: raise ValueError('booking not found') existing=q('SELECT id FROM booking_workflows WHERE booking_id=?',(int(booking_id),),one=True) if not existing: snap=json.dumps(dict(b),ensure_ascii=False) wid=execsql('INSERT INTO booking_workflows(booking_id,status,current_step,package_snapshot,created_at,updated_at) VALUES(?,?,?,?,?,?)',(int(booking_id),'ops_pending','supplier_confirmation',snap,now,now)) else: wid=existing['id'] milestones=[('customer_payment','Track customer payment'),('supplier_confirmation','Confirm all suppliers'),('supplier_payment','Track supplier payables'),('voucher_qa','Generate and QA vouchers'),('customer_communication','Prepare customer communication'),('trip_ready','Trip ready')] for m,note in milestones: execsql('INSERT OR IGNORE INTO booking_milestones(booking_id,milestone,status,note,created_at,updated_at) VALUES(?,?,?,?,?,?)',(int(booking_id),m,'pending',note,now,now)) return wid def create_booking_from_lead(lead_id, actor_id=None, package_id=None, sale_value=None): lead=q('SELECT * FROM leads WHERE id=?',(int(lead_id),),one=True) if not lead: raise ValueError('lead not found') existing=q("SELECT b.* FROM bookings b JOIN customers c ON c.id=b.customer_id WHERE c.phone=? AND c.travel_date=? ORDER BY b.id DESC LIMIT 1",(lead['phone'],lead['travel_date']),one=True) if lead['phone'] else None if existing: ensure_ops_for_booking(existing['id']); return {'ok':True,'booking_id':existing['id'],'duplicate':True} now=now_iso(); cid=execsql('INSERT INTO customers(name,phone,email,travellers,travel_date,destination,notes,created_at) VALUES(?,?,?,?,?,?,?,?)',(lead['name'],lead['phone'],lead['email'],lead['travellers'] or 1,lead['travel_date'],lead['destination'],lead['message'] or lead['notes'] or '',now)) sv=float(lead['budget'] or 0) if sale_value is None else float(sale_value) bid=execsql('INSERT INTO bookings(customer_id,agent_id,package_id,sale_value,our_price,supplier_cost,amount_received,pending_amount,payment_due_date,status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)',(cid,lead['owner_id'] or actor_id,package_id,sv,sv,0,0,sv,None,'won_pending_ops',now)) ensure_ops_for_booking(bid) execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lead_id,'operations_created',f'Booking #{bid} created from WON lead',actor_id,now)) execsql("INSERT INTO tasks(agent_id,booking_id,title,detail,due_date,status,created_at) VALUES(?,?,?,?,?,?,?)",(lead['owner_id'] or actor_id,bid,'Operations kickoff','Review package snapshot, add supplier services and request confirmations.',now,'pending',now)) return {'ok':True,'booking_id':bid,'customer_id':cid,'duplicate':False} def update_ops_status(booking_id): services=q('SELECT * FROM booking_services WHERE booking_id=?',(booking_id,)) if not services: status='ops_pending'; step='supplier_confirmation' elif any((r['status'] not in ('confirmed','completed')) for r in services): status='supplier_pending'; step='supplier_confirmation' elif any((r['supplier_due'] or 0)>0 for r in services): status='supplier_payment_pending'; step='supplier_payment' else: status='voucher_ready'; step='voucher_qa' execsql('UPDATE booking_workflows SET status=?,current_step=?,updated_at=? WHERE booking_id=?',(status,step,now_iso(),booking_id)) return status class H(BaseHTTPRequestHandler): def log_message(self,*a): pass def send_file(self,path,ctype=None): if not os.path.exists(path):self.send_error(404);return data=open(path,'rb').read(); self.send_response(200); self.send_header('Content-Type',ctype or mimetypes.guess_type(path)[0] or 'application/octet-stream'); self.send_header('Content-Length',str(len(data))); self.end_headers(); self.wfile.write(data) def do_GET(self): user=current_user(self.headers) u=urlparse(self.path); p=u.path if p=='/': return self.send_file(os.path.join(BASE,'static','index.html'),'text/html; charset=utf-8') if p.startswith('/static/'): return self.send_file(os.path.join(BASE,p.lstrip('/'))) if p=='/api/health': return json_out(self,{'ok':True,'service':'prisca-smart-package-builder','version':'v27-admin-integrations-cpanel-safe-update'}) if p=='/api/dashboard/operations' and service_user(self.headers): assets=q("SELECT status,COUNT(*) n FROM content_assets GROUP BY status"); jobs=q("SELECT status,COUNT(*) n FROM scheduled_jobs GROUP BY status"); runs=q("SELECT worker,action,status,started_at,finished_at,summary_json FROM worker_runs ORDER BY id DESC LIMIT 10") return json_out(self,{'ok':True,'content':{r['status']:r['n'] for r in assets},'jobs':{r['status']:r['n'] for r in jobs},'recent_runs':[dict(r) for r in runs]}) if p=='/api/cron/run' and service_user(self.headers): d=parse_json(self) if self.headers.get('Content-Length') else {}; return json_out(self,run_scheduled_jobs(int((d or {}).get('limit',20)))) if p=='/api/leads/intake-status' and user and user['role']=='admin': configured=bool(os.environ.get('PRISCA_LEAD_WEBHOOK_SECRET','').strip()) return json_out(self,{'ok':True,'webhook_configured':configured,'endpoint':'/api/leads/webhook','auto_assign':settings().get('auto_assign','round_robin'),'auto_followup_minutes':int(settings().get('auto_followup_minutes','60'))}) if p=='/api/leads' and user: d=parse_json(self); now=now_iso() name=(d.get('name') or '').strip() if not name and not (d.get('phone') or d.get('email')): return json_out(self,{'ok':False,'error':'name or phone/email is required'},400) stage=d.get('stage') or 'new'; priority=d.get('priority') or 'normal' lid=execsql('INSERT INTO leads(name,phone,email,source,destination,travel_date,travellers,budget,message,stage,priority,owner_id,last_contacted_at,next_followup_at,notes,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(name,d.get('phone',''),d.get('email',''),d.get('source','website'),d.get('destination',''),d.get('travel_date'),int(d.get('travellers') or 1),float(d.get('budget') or 0),d.get('message',''),stage,priority,d.get('owner_id'),d.get('last_contacted_at'),d.get('next_followup_at'),d.get('notes',''),now,now)) execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'created','Lead created',user['id'],now)) return json_out(self,{'ok':True,'lead_id':lid,'stage':stage}) if p=='/api/leads/update' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0); row=q('SELECT * FROM leads WHERE id=?',(lid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Lead not found'},404) allowed={'stage','priority','owner_id','next_followup_at','last_contacted_at','notes','destination','travel_date','travellers','budget','message','name','phone','email'} vals={k:d[k] for k in allowed if k in d}; vals['updated_at']=now_iso() sets=', '.join(f'{k}=?' for k in vals); execsql(f'UPDATE leads SET {sets} WHERE id=?',tuple(vals.values())+(lid,)) if 'stage' in d: execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'stage_change',str(d.get('stage')),user['id'],now_iso())) if d.get('stage')=='won': try: create_booking_from_lead(lid,user['id']) except Exception as e: execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'operations_error',str(e),user['id'],now_iso())) return json_out(self,{'ok':True,'lead_id':lid}) if p=='/api/leads/event' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0); row=q('SELECT id FROM leads WHERE id=?',(lid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Lead not found'},404) eid=execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,d.get('event_type','note'),d.get('note',''),user['id'],now_iso())) if d.get('next_followup_at'): execsql('UPDATE leads SET next_followup_at=?,last_contacted_at=?,updated_at=? WHERE id=?',(d['next_followup_at'],now_iso(),now_iso(),lid)) return json_out(self,{'ok':True,'event_id':eid}) if p=='/api/sales/tasks' and user: d=parse_json(self); title=(d.get('title') or '').strip() if not title:return json_out(self,{'ok':False,'error':'title is required'},400) tid=execsql('INSERT INTO sales_tasks(lead_id,owner_id,title,detail,due_at,status,created_at) VALUES(?,?,?,?,?,?,?)',(d.get('lead_id'),d.get('owner_id') or user['id'],title,d.get('detail',''),d.get('due_at'),'pending',now_iso())) if d.get('lead_id'): execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(int(d['lead_id']),'task_created',title,user['id'],now_iso())) return json_out(self,{'ok':True,'task_id':tid}) if p=='/api/sales/tasks/complete' and user: d=parse_json(self); tid=int(d.get('task_id') or 0); execsql("UPDATE sales_tasks SET status='completed',completed_at=? WHERE id=?",(now_iso(),tid)); return json_out(self,{'ok':True,'task_id':tid,'status':'completed'}) if p=='/api/operations/create-booking' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0) try: return json_out(self,create_booking_from_lead(lid,user['id'],d.get('package_id'),d.get('sale_value'))) except ValueError as e:return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/operations/services' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0) if not q('SELECT id FROM bookings WHERE id=?',(bid,),one=True): return json_out(self,{'ok':False,'error':'Booking not found'},404) sid=execsql('INSERT INTO booking_services(booking_id,service_type,destination,provider_name,provider_address,service_name,room_or_vehicle,meal_plan,checkin,checkout,guest_count,supplier_payable,supplier_due,status,notes) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(bid,d.get('service_type','other'),d.get('destination',''),'','',''.join([]) or d.get('service_name',''),d.get('room_or_vehicle',''),d.get('meal_plan',''),d.get('checkin'),d.get('checkout'),str(d.get('guest_count') or ''),float(d.get('supplier_payable') or 0),float(d.get('supplier_payable') or 0),'pending',d.get('notes',''))) ensure_ops_for_booking(bid); update_ops_status(bid); return json_out(self,{'ok':True,'service_id':sid}) if p=='/api/operations/supplier-request' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0); sid=d.get('service_id'); vendor=d.get('vendor_id') rid=execsql('INSERT INTO supplier_requests(booking_id,service_id,vendor_id,request_type,status,requested_at,requested_by,due_at,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?)',(bid,sid,vendor,d.get('request_type','confirmation'),'requested',now_iso(),user['id'],d.get('due_at'),now_iso(),now_iso())) if sid: execsql("UPDATE booking_services SET status='requested' WHERE id=?",(int(sid),)) update_ops_status(bid); return json_out(self,{'ok':True,'request_id':rid,'status':'requested','external_send':'not_configured'}) if p=='/api/operations/supplier-confirm' and user: d=parse_json(self); rid=int(d.get('request_id') or 0); row=q('SELECT * FROM supplier_requests WHERE id=?',(rid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Request not found'},404) execsql("UPDATE supplier_requests SET status='confirmed',confirmation_id=?,response_note=?,updated_at=? WHERE id=?",(d.get('confirmation_id',''),d.get('response_note',''),now_iso(),rid)) if row['service_id']: execsql("UPDATE booking_services SET status='confirmed',confirmation_id=?,vendor_reference=?,notes=COALESCE(notes,'')||? WHERE id=?",(d.get('confirmation_id',''),d.get('vendor_reference',''),('\n'+d.get('response_note','')) if d.get('response_note') else '',int(row['service_id']))) update_ops_status(row['booking_id']); return json_out(self,{'ok':True,'request_id':rid}) if p=='/api/operations/payment' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0); amount=float(d.get('amount') or 0); ptype=d.get('type','customer') if amount<=0:return json_out(self,{'ok':False,'error':'amount must be > 0'},400) if ptype=='customer': execsql('INSERT INTO payments(booking_id,amount,payment_date,note) VALUES(?,?,?,?)',(bid,amount,d.get('payment_date') or now_iso(),d.get('note',''))) b=q('SELECT sale_value,amount_received FROM bookings WHERE id=?',(bid,),one=True); rec=float(b['amount_received'] or 0)+amount; pend=max(0,float(b['sale_value'] or 0)-rec); execsql('UPDATE bookings SET amount_received=?,pending_amount=? WHERE id=?',(rec,pend,bid)) else: sid=int(d.get('service_id') or 0); execsql('INSERT INTO service_payments(service_id,amount,payment_date,screenshot_path,note) VALUES(?,?,?,?,?)',(sid,amount,d.get('payment_date') or now_iso(),d.get('screenshot_path',''),d.get('note',''))); sr=q('SELECT supplier_payable FROM booking_services WHERE id=?',(sid,),one=True); paid=float(q('SELECT COALESCE(SUM(amount),0) n FROM service_payments WHERE service_id=?',(sid,),one=True)['n'] or 0); due=max(0,float(sr['supplier_payable'] or 0)-paid); execsql('UPDATE booking_services SET supplier_paid=?,supplier_due=? WHERE id=?',(paid,due,sid)) update_ops_status(bid); return json_out(self,{'ok':True,'booking_id':bid,'type':ptype,'amount':amount}) if p=='/api/operations/communication' and user: d=parse_json(self); cid=execsql('INSERT INTO customer_comms(booking_id,channel,message_type,recipient,subject,body,status,created_at) VALUES(?,?,?,?,?,?,?,?)',(int(d.get('booking_id')),d.get('channel','email'),d.get('message_type','booking_update'),d.get('recipient',''),d.get('subject','Prisca Holidays Booking Update'),d.get('body',''),'pending_approval',now_iso())); return json_out(self,{'ok':True,'communication_id':cid,'status':'pending_approval'}) if p=='/api/operations/communication/approve' and user and user['role']=='admin': d=parse_json(self); cid=int(d.get('communication_id') or 0); execsql("UPDATE customer_comms SET status='approved',approved_by=?,approved_at=? WHERE id=? AND status='pending_approval'",(user['id'],now_iso(),cid)); return json_out(self,{'ok':True,'communication_id':cid,'external_send':'blocked_until_connector'}) if p=='/api/operations/voucher' and user: d=parse_json(self); sid=int(d.get('service_id') or 0); svc=q('SELECT bs.*,b.id booking_id,c.name customer,c.phone,c.email,c.destination,c.travel_date FROM booking_services bs JOIN bookings b ON b.id=bs.booking_id JOIN customers c ON c.id=b.customer_id WHERE bs.id=?',(sid,),one=True) if not svc:return json_out(self,{'ok':False,'error':'Service not found'},404) if svc['status'] not in ('confirmed','completed'):return json_out(self,{'ok':False,'error':'Supplier service must be confirmed before voucher generation'},400) fn=f"voucher_booking_{svc['booking_id']}_service_{sid}_{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}.html"; path=os.path.join(UPLOADS,fn) html=f"

Prisca Holidays — Service Voucher

Customer: {svc['customer']}

Destination: {svc['destination']}

Travel date: {svc['travel_date']}

Service: {svc['service_name']}

Provider: {svc['provider_name']}

Confirmation: {svc['confirmation_id']}

Room/Vehicle: {svc['room_or_vehicle']}

Meal: {svc['meal_plan']}

Check-in: {svc['checkin']}

Check-out: {svc['checkout']}

Generated by Prisca Operations Worker. Final customer issue remains approval-controlled.

"; open(path,'w',encoding='utf-8').write(html); vid=execsql('INSERT INTO voucher_logs(service_id,generated_by,generated_at,filename) VALUES(?,?,?,?)',(sid,user['id'],now_iso(),fn)); execsql("UPDATE booking_services SET status='completed' WHERE id=?",(sid,)); update_ops_status(svc['booking_id']); return json_out(self,{'ok':True,'voucher_id':vid,'filename':fn,'path':'/uploads/'+fn}) if p=='/api/scheduler/jobs' and service_user(self.headers): rows=q('SELECT * FROM scheduled_jobs ORDER BY run_at,id LIMIT 200'); return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/cron/run' and service_user(self.headers): return json_out(self,run_scheduled_jobs(int(parse_qs(u.query).get('limit',['20'])[0]))) if p=='/api/ai/wordpress-audit' and service_user(self.headers): try: return json_out(self,PriscaAIWorker().wordpress_audit()) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},502) if p in ('/api/activities','/api/pricing/policy') and service_user(self.headers): if p=='/api/pricing/policy': s=settings(); return json_out(self,{'online_package_margin_percent':float(s.get('online_package_margin','15')),'minimum_margin_percent':float(s.get('min_margin','7')),'pricing_version':'v16','pricing_policy':'gross_margin'}) qs=parse_qs(u.query); dest=qs.get('destination',[''])[0] rows=q('SELECT * FROM activities WHERE active=1 AND (destination LIKE ? OR ?='') ORDER BY destination,name',(f'%{dest}%',dest)) if dest else q('SELECT * FROM activities WHERE active=1 ORDER BY destination,name') return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/integrations/test' and (service_user(self.headers) or (user and user['role']=='admin')): d=parse_json(self); platform=(d.get('platform') or '').lower(); result={'ok':False,'platform':platform} try: if platform=='pinterest': result.update(PinterestAdapter().pins()); result['ok']=True elif platform=='search_console': result.update(SearchConsoleAdapter().query(d['start_date'],d['end_date'],d.get('dimensions'))); result['ok']=True elif platform=='bing_webmaster': result['ok']=True; result['note']='Bing key configured; use /api/integrations/bing-submit for URL submission.' elif platform=='meta': result['ok']=bool(os.getenv('PRISCA_META_ACCESS_TOKEN')); result['note']='Meta credentials detected.' if result['ok'] else 'Meta credentials are not configured.' elif platform=='youtube': result['ok']=bool(os.getenv('PRISCA_YOUTUBE_ACCESS_TOKEN')); result['note']='YouTube OAuth token detected.' if result['ok'] else 'YouTube OAuth token is not configured.' else: result['error']='Unknown platform' except Exception as e: result['error']=str(e) return json_out(self,result,200 if result.get('ok') else 400) if p=='/api/integrations/bing-submit' and service_user(self.headers): d=parse_json(self) try: result=BingWebmasterAdapter().submit_url(d.get('url','')) return json_out(self,{'ok':True,'result':result}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/analytics/gsc-sync' and service_user(self.headers): d=parse_json(self) try: result=SearchConsoleAdapter().query(d['start_date'],d['end_date'],d.get('dimensions') or ['query','page'],d.get('row_limit',1000)) rows=(result.get('data') or {}).get('rows',[]) if isinstance(result.get('data'),dict) else [] now=datetime.datetime.now().isoformat(timespec='seconds'); created=[] for row in rows: keys=row.get('keys') or []; query_text=keys[0] if keys else ''; page_url=keys[1] if len(keys)>1 else '' created.append(execsql('INSERT INTO seo_metrics(source,metric_date,query_text,page_url,clicks,impressions,ctr,position,raw_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?)',('google_search_console',d['end_date'],query_text,page_url,row.get('clicks',0),row.get('impressions',0),row.get('ctr',0),row.get('position',0),json.dumps(row,ensure_ascii=False),now))) return json_out(self,{'ok':True,'rows_ingested':len(created),'result':result}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/analytics/social-metric' and service_user(self.headers): d=parse_json(self); now=datetime.datetime.now().isoformat(timespec='seconds') rid=execsql('INSERT INTO social_metrics(platform,asset_id,metric_date,impressions,reach,views,likes,comments,shares,clicks,saves,leads,revenue,raw_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(d.get('platform',''),d.get('asset_id'),d.get('metric_date') or datetime.date.today().isoformat(),float(d.get('impressions',0)),float(d.get('reach',0)),float(d.get('views',0)),float(d.get('likes',0)),float(d.get('comments',0)),float(d.get('shares',0)),float(d.get('clicks',0)),float(d.get('saves',0)),float(d.get('leads',0)),float(d.get('revenue',0)),json.dumps(d.get('raw') or {},ensure_ascii=False),now)) return json_out(self,{'ok':True,'metric_id':rid}) user=current_user(self.headers) if p=='/api/integrations/config' and user and user['role']=='admin': return json_out(self,{'ok':True,'config':integration_config(False),'admin_only':True}) if p=='/api/integrations/status' and user and user['role']=='admin': s=integration_config(True) providers={ 'wordpress': bool(s.get('wp_url') and s.get('wp_user') and s.get('wp_app_password')), 'woocommerce': bool(s.get('wp_url') and s.get('woo_consumer_key') and s.get('woo_consumer_secret')), 'facebook': bool(s.get('meta_access_token') and s.get('facebook_page_id')), 'instagram': bool(s.get('meta_access_token') and s.get('instagram_business_id')), 'youtube': bool(s.get('youtube_access_token')), 'google_search_console': bool(s.get('google_access_token') and s.get('gsc_site_url')), 'pinterest': bool(s.get('pinterest_access_token') and s.get('pinterest_board_id')), 'bing_webmaster': bool(s.get('bing_api_key') and s.get('bing_site_url')), 'cpanel': bool(s.get('cpanel_url') and s.get('cpanel_user') and (s.get('cpanel_token') or s.get('cpanel_password'))), } return json_out(self,{'ok':True,'providers':providers,'admin_only':True,'publish_requires_approval':True}) if p=='/api/analytics/social' and service_user(self.headers): qs=parse_qs(u.query); platform=qs.get('platform',[''])[0]; limit=min(int(qs.get('limit',['200'])[0]),500) rows=q('SELECT * FROM social_metrics WHERE platform LIKE ? ORDER BY metric_date DESC,id DESC LIMIT ?',(f'%{platform}%' if platform else '%',limit)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/analytics/seo' and service_user(self.headers): qs=parse_qs(u.query); limit=min(int(qs.get('limit',['200'])[0]),500) rows=q('SELECT * FROM seo_metrics ORDER BY metric_date DESC,id DESC LIMIT ?',(limit,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/integration-logs' and service_user(self.headers): qs=parse_qs(u.query); limit=min(int(qs.get('limit',['200'])[0]),500) rows=q('SELECT * FROM integration_logs ORDER BY id DESC LIMIT ?',(limit,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/sales/dashboard' and service_user(self.headers): stages=q("SELECT stage,COUNT(*) n FROM leads GROUP BY stage") tasks=q("SELECT status,COUNT(*) n FROM sales_tasks GROUP BY status") due=q("SELECT COUNT(*) n FROM leads WHERE next_followup_at IS NOT NULL AND next_followup_at<=? AND stage NOT IN ('won','lost')",(now_iso(),),one=True)['n'] return json_out(self,{'ok':True,'leads':{r['stage']:r['n'] for r in stages},'tasks':{r['status']:r['n'] for r in tasks},'followups_due':due}) if p=='/api/leads' and user: qs=parse_qs(u.query); stage=qs.get('stage',[''])[0]; limit=min(int(qs.get('limit',['200'])[0]),500) if stage: rows=q('SELECT * FROM leads WHERE stage=? ORDER BY updated_at DESC LIMIT ?',(stage,limit)) else: rows=q('SELECT * FROM leads ORDER BY updated_at DESC LIMIT ?',(limit,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p.startswith('/api/leads/') and p.endswith('/events') and user: lid=int(p.split('/')[3]); rows=q('SELECT * FROM lead_events WHERE lead_id=? ORDER BY id DESC LIMIT 100',(lid,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/sales/tasks' and user: rows=q('SELECT * FROM sales_tasks ORDER BY CASE WHEN status=\'pending\' THEN 0 ELSE 1 END,due_at,id DESC LIMIT 300') return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/me': return json_out(self,{'user':dict(user) if user else None}) if p=='/api/login': return json_out(self,{'ok':False},401) if not user:return json_out(self,{'error':'Unauthorized'},401) if p=='/api/content-assets/review' and user['role']=='admin': d=parse_json(self); aid=int(d.get('asset_id') or 0); decision=(d.get('decision') or '').lower(); note=d.get('note','') if decision not in ('approve','reject'): return json_out(self,{'ok':False,'error':'decision must be approve or reject'},400) row=q('SELECT * FROM content_assets WHERE id=?',(aid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Content asset not found'},404) new='approved' if decision=='approve' else 'rejected' execsql('UPDATE content_assets SET status=? WHERE id=?',(new,aid)) execsql('INSERT INTO integration_logs(platform,action,asset_id,status,response_json,created_at) VALUES(?,?,?,?,?,?)',(row['platform'],'approval',aid,new,json.dumps({'note':note}),datetime.datetime.now().isoformat(timespec='seconds'))) return json_out(self,{'ok':True,'asset_id':aid,'status':new,'publish_blocked':decision!='approve'}) if p=='/api/content-assets/publish' and user['role']=='admin': d=parse_json(self); aid=int(d.get('asset_id') or 0); row=q('SELECT * FROM content_assets WHERE id=?',(aid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Content asset not found'},404) if row['status']!='approved':return json_out(self,{'ok':False,'error':'Asset must be approved before publishing'},409) platform=row['platform']; result=None try: if platform=='facebook': result=MetaAdapter().page_feed(row['body']) elif platform=='instagram': image_url=d.get('image_url') if not image_url: raise APIError('image_url is required for Instagram image publishing') result=MetaAdapter().instagram_publish_image(image_url,row['body']) elif platform=='pinterest': result=PinterestAdapter().create_pin({'title':row['title'],'body':row['body'],'image_url':d.get('image_url'),'link':d.get('link')}) elif platform=='google_business': raise APIError('Google Business publishing requires a supported Business Profile integration; not enabled in V21.') elif platform=='youtube': raise APIError('YouTube video publishing requires a media upload asset; use the YouTube upload worker once a file path is attached.') elif platform=='website': raise APIError('Website publishing remains routed through the WordPress draft/approval workflow.') else: raise APIError('Unsupported platform: '+platform) now=datetime.datetime.now().isoformat(timespec='seconds'); execsql('UPDATE content_assets SET status=?,publish_blocked=0 WHERE id=?',('published',aid)); execsql('INSERT INTO integration_logs(platform,action,asset_id,status,response_json,created_at) VALUES(?,?,?,?,?,?)',(platform,'publish',aid,'success',json.dumps(result,ensure_ascii=False),now)); return json_out(self,{'ok':True,'asset_id':aid,'status':'published','result':result}) except Exception as e: now=datetime.datetime.now().isoformat(timespec='seconds'); execsql('UPDATE content_assets SET status=? WHERE id=?',('failed',aid)); execsql('INSERT INTO integration_logs(platform,action,asset_id,status,response_json,created_at) VALUES(?,?,?,?,?,?)',(platform,'publish',aid,'failed',json.dumps({'error':str(e)}),now)); return json_out(self,{'ok':False,'asset_id':aid,'status':'failed','error':str(e)},400) if p=='/api/booking_services': qs=parse_qs(u.query); bid=int(qs.get('booking_id',['0'])[0] or 0) if user['role']!='admin': own=q('SELECT id FROM bookings WHERE id=? AND agent_id=?',(bid,user['id']),one=True) if not own:return json_out(self,{'error':'Forbidden'},403) rows=q('SELECT * FROM booking_services WHERE booking_id=? ORDER BY id',(bid,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/travellers': qs=parse_qs(u.query); bid=int(qs.get('booking_id',['0'])[0] or 0) own=q('SELECT id FROM bookings WHERE id=? AND agent_id=?',(bid,user['id']),one=True) if user['role']!='admin' else q('SELECT id FROM bookings WHERE id=?',(bid,),one=True) if not own:return json_out(self,{'error':'Forbidden'},403) rows=q('SELECT id,booking_id,full_name,age,gender,phone,email,aadhaar_encrypted,pan_encrypted FROM travellers WHERE booking_id=?',(bid,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/stats': return json_out(self,{'hotels':q('SELECT COUNT(*) n FROM hotels WHERE active=1',one=True)['n'],'cities':q('SELECT COUNT(DISTINCT city) n FROM hotels WHERE active=1',one=True)['n'],'agents':q("SELECT COUNT(*) n FROM users WHERE role='agent' AND active=1",one=True)['n'],'bookings':q('SELECT COUNT(*) n FROM bookings',one=True)['n'],'revenue':q('SELECT COALESCE(SUM(sale_value),0) n FROM bookings',one=True)['n'],'pending':q('SELECT COALESCE(SUM(pending_amount),0) n FROM bookings',one=True)['n']}) if p=='/api/states': rows=q('SELECT state,destination FROM states ORDER BY state,destination') out={} for r in rows: out.setdefault(r['state'],[]).append(r['destination']) return json_out(self,{'states':out}) if p=='/api/hotels': qs=parse_qs(u.query); dest=qs.get('destination',[''])[0]; limit=min(int(qs.get('limit',['100'])[0]),500) if dest: rows=q('SELECT * FROM hotels WHERE active=1 AND (city LIKE ? OR hotel_name LIKE ?) ORDER BY city,hotel_name LIMIT ?',(f'%{dest}%',f'%{dest}%',limit)) else: rows=q('SELECT * FROM hotels WHERE active=1 ORDER BY id DESC LIMIT ?',(limit,)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/houseboats': return json_out(self,{'rows':[dict(r) for r in q('SELECT * FROM houseboats WHERE active=1 ORDER BY destination,category,pax')]}) if p=='/api/activities': qs=parse_qs(u.query); dest=qs.get('destination',[''])[0]; rows=q('SELECT * FROM activities WHERE active=1 AND (destination LIKE ? OR ?='') ORDER BY destination,name',(f'%{dest}%',dest)) if dest else q('SELECT * FROM activities WHERE active=1 ORDER BY destination,name'); return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/pricing/policy': return json_out(self,{'online_package_margin_percent':float(settings().get('online_package_margin','15')),'minimum_margin_percent':float(settings().get('min_margin','7'))}) if p=='/api/notifications': return json_out(self,{'rows':[dict(r) for r in q('SELECT * FROM notifications WHERE user_id=? ORDER BY id DESC LIMIT 50',(user['id'],))]}) if p=='/api/agents' and user['role']=='admin': return json_out(self,{'rows':[dict(r) for r in q("SELECT id,name,email,active,created_at FROM users WHERE role='agent' ORDER BY name")]}) if p=='/api/supplier_profiles' and user['role']=='admin': return json_out(self,{'rows':[dict(r) for r in q('SELECT * FROM supplier_profiles ORDER BY supplier')]}) if p=='/api/imports' and user['role']=='admin': rows=q('SELECT i.*,COUNT(r.id) row_count,SUM(CASE WHEN r.approved=1 THEN 1 ELSE 0 END) approved_count FROM imports i LEFT JOIN import_rows r ON r.import_id=i.id GROUP BY i.id ORDER BY i.id DESC LIMIT 100') return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/import_quality' and user['role']=='admin': iid=int(parse_qs(u.query).get('import_id',['0'])[0]); rows=q('SELECT * FROM import_rows WHERE import_id=? ORDER BY id',(iid,)); dup=sum(1 for r in rows if 'Potential duplicate' in (r['warnings'] or '')); low=sum(1 for r in rows if (r['confidence'] or 0)<0.75); ambiguous=sum(1 for r in rows if r['tax_status']=='unknown' or r['rate_type']=='Unclassified'); return json_out(self,{'import_id':iid,'rows':len(rows),'potential_duplicates':dup,'low_confidence':low,'ambiguous_commercial_rows':ambiguous}) if p=='/api/import_rows' and user['role']=='admin': qs=parse_qs(u.query); iid=int(qs.get('import_id',['0'])[0] or 0) rows=q('SELECT * FROM import_rows WHERE import_id=? ORDER BY id',(iid,)); return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/operations/dashboard' and user: rows=q("SELECT bw.status,COUNT(*) n FROM booking_workflows bw GROUP BY bw.status") due=q("SELECT COUNT(*) n FROM booking_milestones WHERE status='pending' AND due_at IS NOT NULL AND due_at<=?",(now_iso(),),one=True)['n'] req=q("SELECT COUNT(*) n FROM supplier_requests WHERE status IN ('draft','requested')",one=True)['n'] return json_out(self,{'ok':True,'workflow':{r['status']:r['n'] for r in rows},'supplier_requests_open':req,'milestones_due':due}) if p=='/api/operations/bookings' and user: rows=q('SELECT b.id,b.status,b.sale_value,b.amount_received,b.pending_amount,c.name customer,c.phone,c.destination,c.travel_date,bw.status ops_status,bw.current_step FROM bookings b JOIN customers c ON c.id=b.customer_id LEFT JOIN booking_workflows bw ON bw.booking_id=b.id ORDER BY b.id DESC LIMIT 200') return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/operations/booking' and user: bid=int(parse_qs(u.query).get('booking_id',['0'])[0]); b=q('SELECT b.*,c.name customer,c.phone,c.email,c.destination,c.travel_date,c.travellers FROM bookings b JOIN customers c ON c.id=b.customer_id WHERE b.id=?',(bid,),one=True) if not b:return json_out(self,{'ok':False,'error':'Booking not found'},404) services=q('SELECT * FROM booking_services WHERE booking_id=? ORDER BY id',(bid,)); req=q('SELECT * FROM supplier_requests WHERE booking_id=? ORDER BY id DESC',(bid,)); ms=q('SELECT * FROM booking_milestones WHERE booking_id=? ORDER BY id',(bid,)); comm=q('SELECT * FROM customer_comms WHERE booking_id=? ORDER BY id DESC',(bid,)) return json_out(self,{'booking':dict(b),'services':[dict(x) for x in services],'supplier_requests':[dict(x) for x in req],'milestones':[dict(x) for x in ms],'communications':[dict(x) for x in comm]}) if p=='/api/vendors' and user: return json_out(self,{'rows':[dict(x) for x in q('SELECT * FROM vendors WHERE active=1 ORDER BY supplier')]}) if p=='/api/supplier_requests' and user: bid=int(parse_qs(u.query).get('booking_id',['0'])[0]); return json_out(self,{'rows':[dict(x) for x in q('SELECT sr.*,v.supplier,v.contact_name,v.phone,v.email FROM supplier_requests sr LEFT JOIN vendors v ON v.id=sr.vendor_id WHERE sr.booking_id=? ORDER BY sr.id DESC',(bid,))]}) if p=='/api/bookings': if user['role']=='admin': rows=q('SELECT b.*,u.name agent,c.name customer,c.destination,c.travel_date FROM bookings b JOIN users u ON u.id=b.agent_id JOIN customers c ON c.id=b.customer_id ORDER BY b.id DESC') else: rows=q('SELECT b.*,u.name agent,c.name customer,c.destination,c.travel_date FROM bookings b JOIN users u ON u.id=b.agent_id JOIN customers c ON c.id=b.customer_id WHERE b.agent_id=? ORDER BY b.id DESC',(user['id'],)) return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/content-assets' and user['role']=='admin': rows=q('SELECT * FROM content_assets ORDER BY id DESC LIMIT 200'); return json_out(self,{'rows':[dict(r) for r in rows],'publish_blocked':True}) if p=='/api/content-approvals' and user['role']=='admin': rows=q("SELECT * FROM content_assets WHERE status IN ('pending_approval','approved','published','failed') ORDER BY id DESC LIMIT 200"); return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/content-calendars' and user['role']=='admin': rows=q('SELECT * FROM content_calendars ORDER BY id DESC LIMIT 50'); return json_out(self,{'rows':[dict(r) for r in rows]}) if p=='/api/package-approvals' and user['role']=='admin': rows=q('SELECT id,status,created_by,created_at,reviewed_by,reviewed_at,review_note,wp_product_id,package_json FROM package_approvals ORDER BY id DESC LIMIT 100') out=[] for r in rows: x=dict(r); x['package']=json.loads(x.pop('package_json')); out.append(x) return json_out(self,{'rows':out}) return json_out(self,{'error':'Not found'},404) def do_POST(self): u=urlparse(self.path); p=u.path user=current_user(self.headers) if p=='/api/leads/webhook': raw=self.rfile.read(int(self.headers.get('Content-Length','0')) or 0) if not verify_lead_webhook(self.headers,raw): return json_out(self,{'ok':False,'error':'Unauthorized webhook'},401) try: d=json.loads(raw or b'{}'); result=ingest_public_lead(d); return json_out(self,result,200) except ValueError as e:return json_out(self,{'ok':False,'error':str(e)},400) except Exception as e:return json_out(self,{'ok':False,'error':'Lead ingestion failed'},500) if p=='/api/login': d=parse_json(self); user=q('SELECT * FROM users WHERE email=? AND password_hash=? AND active=1',(d.get('email',''),hashpw(d.get('password',''))),one=True) if not user:return json_out(self,{'error':'Invalid credentials'},401) tok=secrets.token_urlsafe(32); exp=(datetime.datetime.now()+datetime.timedelta(days=7)).isoformat(); execsql('INSERT INTO sessions(token,user_id,expires_at) VALUES(?,?,?)',(tok,user['id'],exp)); self.send_response(200); self.send_header('Set-Cookie',f'session={tok}; Path=/; HttpOnly; SameSite=Lax'); self.send_header('Content-Type','application/json'); u=dict(user); u.pop('password_hash',None); b=json.dumps({'user':u}).encode(); self.send_header('Content-Length',str(len(b))); self.end_headers(); self.wfile.write(b); return if p=='/api/logout': tok=self.headers.get('Cookie','').replace('session=','').split(';')[0]; execsql('DELETE FROM sessions WHERE token=?',(tok,)); self.send_response(200); self.send_header('Set-Cookie','session=; Max-Age=0; Path=/'); self.end_headers(); return if p=='/api/cron/run' and service_user(self.headers): d=parse_json(self) if self.headers.get('Content-Length') else {}; return json_out(self,run_scheduled_jobs(int((d or {}).get('limit',20)))) if p=='/api/leads' and user: d=parse_json(self); now=now_iso() name=(d.get('name') or '').strip() if not name and not (d.get('phone') or d.get('email')): return json_out(self,{'ok':False,'error':'name or phone/email is required'},400) stage=d.get('stage') or 'new'; priority=d.get('priority') or 'normal' lid=execsql('INSERT INTO leads(name,phone,email,source,destination,travel_date,travellers,budget,message,stage,priority,owner_id,last_contacted_at,next_followup_at,notes,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(name,d.get('phone',''),d.get('email',''),d.get('source','website'),d.get('destination',''),d.get('travel_date'),int(d.get('travellers') or 1),float(d.get('budget') or 0),d.get('message',''),stage,priority,d.get('owner_id'),d.get('last_contacted_at'),d.get('next_followup_at'),d.get('notes',''),now,now)) execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'created','Lead created',user['id'],now)) return json_out(self,{'ok':True,'lead_id':lid,'stage':stage}) if p=='/api/leads/update' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0); row=q('SELECT * FROM leads WHERE id=?',(lid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Lead not found'},404) allowed={'stage','priority','owner_id','next_followup_at','last_contacted_at','notes','destination','travel_date','travellers','budget','message','name','phone','email'} vals={k:d[k] for k in allowed if k in d}; vals['updated_at']=now_iso() sets=', '.join(f'{k}=?' for k in vals); execsql(f'UPDATE leads SET {sets} WHERE id=?',tuple(vals.values())+(lid,)) if 'stage' in d: execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'stage_change',str(d.get('stage')),user['id'],now_iso())) if d.get('stage')=='won': try: create_booking_from_lead(lid,user['id']) except Exception as e: execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,'operations_error',str(e),user['id'],now_iso())) return json_out(self,{'ok':True,'lead_id':lid}) if p=='/api/leads/event' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0); row=q('SELECT id FROM leads WHERE id=?',(lid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Lead not found'},404) eid=execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(lid,d.get('event_type','note'),d.get('note',''),user['id'],now_iso())) if d.get('next_followup_at'): execsql('UPDATE leads SET next_followup_at=?,last_contacted_at=?,updated_at=? WHERE id=?',(d['next_followup_at'],now_iso(),now_iso(),lid)) return json_out(self,{'ok':True,'event_id':eid}) if p=='/api/sales/tasks' and user: d=parse_json(self); title=(d.get('title') or '').strip() if not title:return json_out(self,{'ok':False,'error':'title is required'},400) tid=execsql('INSERT INTO sales_tasks(lead_id,owner_id,title,detail,due_at,status,created_at) VALUES(?,?,?,?,?,?,?)',(d.get('lead_id'),d.get('owner_id') or user['id'],title,d.get('detail',''),d.get('due_at'),'pending',now_iso())) if d.get('lead_id'): execsql('INSERT INTO lead_events(lead_id,event_type,note,created_by,created_at) VALUES(?,?,?,?,?)',(int(d['lead_id']),'task_created',title,user['id'],now_iso())) return json_out(self,{'ok':True,'task_id':tid}) if p=='/api/sales/tasks/complete' and user: d=parse_json(self); tid=int(d.get('task_id') or 0); execsql("UPDATE sales_tasks SET status='completed',completed_at=? WHERE id=?",(now_iso(),tid)); return json_out(self,{'ok':True,'task_id':tid,'status':'completed'}) if p=='/api/operations/create-booking' and user: d=parse_json(self); lid=int(d.get('lead_id') or 0) try: return json_out(self,create_booking_from_lead(lid,user['id'],d.get('package_id'),d.get('sale_value'))) except ValueError as e:return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/operations/services' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0) if not q('SELECT id FROM bookings WHERE id=?',(bid,),one=True): return json_out(self,{'ok':False,'error':'Booking not found'},404) sid=execsql('INSERT INTO booking_services(booking_id,service_type,destination,provider_name,provider_address,service_name,room_or_vehicle,meal_plan,checkin,checkout,guest_count,supplier_payable,supplier_due,status,notes) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(bid,d.get('service_type','other'),d.get('destination',''),'','',''.join([]) or d.get('service_name',''),d.get('room_or_vehicle',''),d.get('meal_plan',''),d.get('checkin'),d.get('checkout'),str(d.get('guest_count') or ''),float(d.get('supplier_payable') or 0),float(d.get('supplier_payable') or 0),'pending',d.get('notes',''))) ensure_ops_for_booking(bid); update_ops_status(bid); return json_out(self,{'ok':True,'service_id':sid}) if p=='/api/operations/supplier-request' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0); sid=d.get('service_id'); vendor=d.get('vendor_id') rid=execsql('INSERT INTO supplier_requests(booking_id,service_id,vendor_id,request_type,status,requested_at,requested_by,due_at,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?)',(bid,sid,vendor,d.get('request_type','confirmation'),'requested',now_iso(),user['id'],d.get('due_at'),now_iso(),now_iso())) if sid: execsql("UPDATE booking_services SET status='requested' WHERE id=?",(int(sid),)) update_ops_status(bid); return json_out(self,{'ok':True,'request_id':rid,'status':'requested','external_send':'not_configured'}) if p=='/api/operations/supplier-confirm' and user: d=parse_json(self); rid=int(d.get('request_id') or 0); row=q('SELECT * FROM supplier_requests WHERE id=?',(rid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Request not found'},404) execsql("UPDATE supplier_requests SET status='confirmed',confirmation_id=?,response_note=?,updated_at=? WHERE id=?",(d.get('confirmation_id',''),d.get('response_note',''),now_iso(),rid)) if row['service_id']: execsql("UPDATE booking_services SET status='confirmed',confirmation_id=?,vendor_reference=?,notes=COALESCE(notes,'')||? WHERE id=?",(d.get('confirmation_id',''),d.get('vendor_reference',''),('\n'+d.get('response_note','')) if d.get('response_note') else '',int(row['service_id']))) update_ops_status(row['booking_id']); return json_out(self,{'ok':True,'request_id':rid}) if p=='/api/operations/payment' and user: d=parse_json(self); bid=int(d.get('booking_id') or 0); amount=float(d.get('amount') or 0); ptype=d.get('type','customer') if amount<=0:return json_out(self,{'ok':False,'error':'amount must be > 0'},400) if ptype=='customer': execsql('INSERT INTO payments(booking_id,amount,payment_date,note) VALUES(?,?,?,?)',(bid,amount,d.get('payment_date') or now_iso(),d.get('note',''))) b=q('SELECT sale_value,amount_received FROM bookings WHERE id=?',(bid,),one=True); rec=float(b['amount_received'] or 0)+amount; pend=max(0,float(b['sale_value'] or 0)-rec); execsql('UPDATE bookings SET amount_received=?,pending_amount=? WHERE id=?',(rec,pend,bid)) else: sid=int(d.get('service_id') or 0); execsql('INSERT INTO service_payments(service_id,amount,payment_date,screenshot_path,note) VALUES(?,?,?,?,?)',(sid,amount,d.get('payment_date') or now_iso(),d.get('screenshot_path',''),d.get('note',''))); sr=q('SELECT supplier_payable FROM booking_services WHERE id=?',(sid,),one=True); paid=float(q('SELECT COALESCE(SUM(amount),0) n FROM service_payments WHERE service_id=?',(sid,),one=True)['n'] or 0); due=max(0,float(sr['supplier_payable'] or 0)-paid); execsql('UPDATE booking_services SET supplier_paid=?,supplier_due=? WHERE id=?',(paid,due,sid)) update_ops_status(bid); return json_out(self,{'ok':True,'booking_id':bid,'type':ptype,'amount':amount}) if p=='/api/operations/communication' and user: d=parse_json(self); cid=execsql('INSERT INTO customer_comms(booking_id,channel,message_type,recipient,subject,body,status,created_at) VALUES(?,?,?,?,?,?,?,?)',(int(d.get('booking_id')),d.get('channel','email'),d.get('message_type','booking_update'),d.get('recipient',''),d.get('subject','Prisca Holidays Booking Update'),d.get('body',''),'pending_approval',now_iso())); return json_out(self,{'ok':True,'communication_id':cid,'status':'pending_approval'}) if p=='/api/operations/communication/approve' and user and user['role']=='admin': d=parse_json(self); cid=int(d.get('communication_id') or 0); execsql("UPDATE customer_comms SET status='approved',approved_by=?,approved_at=? WHERE id=? AND status='pending_approval'",(user['id'],now_iso(),cid)); return json_out(self,{'ok':True,'communication_id':cid,'external_send':'blocked_until_connector'}) if p=='/api/operations/voucher' and user: d=parse_json(self); sid=int(d.get('service_id') or 0); svc=q('SELECT bs.*,b.id booking_id,c.name customer,c.phone,c.email,c.destination,c.travel_date FROM booking_services bs JOIN bookings b ON b.id=bs.booking_id JOIN customers c ON c.id=b.customer_id WHERE bs.id=?',(sid,),one=True) if not svc:return json_out(self,{'ok':False,'error':'Service not found'},404) if svc['status'] not in ('confirmed','completed'):return json_out(self,{'ok':False,'error':'Supplier service must be confirmed before voucher generation'},400) fn=f"voucher_booking_{svc['booking_id']}_service_{sid}_{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}.html"; path=os.path.join(UPLOADS,fn) html=f"

Prisca Holidays — Service Voucher

Customer: {svc['customer']}

Destination: {svc['destination']}

Travel date: {svc['travel_date']}

Service: {svc['service_name']}

Provider: {svc['provider_name']}

Confirmation: {svc['confirmation_id']}

Room/Vehicle: {svc['room_or_vehicle']}

Meal: {svc['meal_plan']}

Check-in: {svc['checkin']}

Check-out: {svc['checkout']}

Generated by Prisca Operations Worker. Final customer issue remains approval-controlled.

"; open(path,'w',encoding='utf-8').write(html); vid=execsql('INSERT INTO voucher_logs(service_id,generated_by,generated_at,filename) VALUES(?,?,?,?)',(sid,user['id'],now_iso(),fn)); execsql("UPDATE booking_services SET status='completed' WHERE id=?",(sid,)); update_ops_status(svc['booking_id']); return json_out(self,{'ok':True,'voucher_id':vid,'filename':fn,'path':'/uploads/'+fn}) if p=='/api/scheduler/jobs' and service_user(self.headers): d=parse_json(self); jt=d.get('job_type'); run_at=d.get('run_at'); payload=d.get('payload') or {} if not jt or not run_at: return json_out(self,{'ok':False,'error':'job_type and run_at are required'},400) jid=execsql('INSERT INTO scheduled_jobs(job_type,payload_json,run_at,status,created_at,updated_at) VALUES(?,?,?,?,?,?)',(jt,json.dumps(payload,ensure_ascii=False),run_at,'scheduled',now_iso(),now_iso())) return json_out(self,{'ok':True,'job_id':jid,'status':'scheduled'}) if p=='/api/scheduler/jobs/cancel' and service_user(self.headers): d=parse_json(self); jid=int(d.get('job_id') or 0); execsql("UPDATE scheduled_jobs SET status='cancelled',updated_at=? WHERE id=? AND status='scheduled'",(now_iso(),jid)); return json_out(self,{'ok':True,'job_id':jid}) if p=='/api/ai/command' and user and user['role']=='admin': try: d=parse_json(self); command=(d.get('command') or '').strip() import re as _re m=_re.search(r'(\d+)\s*(?:packages|package)',command,re.I); count=int(m.group(1)) if m else 20 destinations=['Kerala','Rajasthan','Goa','Himachal Pradesh','Dubai','Thailand'] destination=next((x for x in destinations if x.lower() in command.lower()),'Kerala') nm=_re.search(r'(?:for|in)\s+([A-Za-z][A-Za-z &-]{2,40})',command,re.I) if nm: cand=nm.group(1).strip().rstrip('.').split(' using ')[0].strip() if cand and len(cand)<45: destination=cand result=PackageGenerationEngine().generate(destination,count=count,brief={'command':command,'approval_required':True,'pricing_required':True,'research_required':True}) result['message']=f'Created {result["count"]} structured package drafts for {destination}. Research, supplier costing, QA and Admin approval are still required.' return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/generate-packages' and service_user(self.headers): try: d=parse_json(self) result=PackageGenerationEngine().generate(d.get('destination'), d.get('nights',3), d.get('base_route'), d.get('count',20), d.get('brief')) return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/content-assets' and service_user(self.headers): try: d=parse_json(self); result=ContentWorker().create_assets(d.get('package') or d, d.get('channels')) now=datetime.datetime.now().isoformat(timespec='seconds'); ids=[] for a in result['assets']: ids.append(execsql('INSERT INTO content_assets(package_name,destination,platform,asset_type,title,body,cta,status,publish_blocked,created_at) VALUES(?,?,?,?,?,?,?,?,?,?)',(result['package'],result['destination'],a['platform'],a['type'],a['title'],a['body'],a['cta'],'draft',1,now))) result['asset_ids']=ids; return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/content-calendar' and service_user(self.headers): try: d=parse_json(self); result=ContentWorker().calendar(d.get('destination'),d.get('start_date'),d.get('days',7)); now=datetime.datetime.now().isoformat(timespec='seconds') cid=execsql('INSERT INTO content_calendars(destination,calendar_json,created_at) VALUES(?,?,?)',(result['destination'],json.dumps(result,ensure_ascii=False),now)); result['calendar_id']=cid; return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/seo-plan' and service_user(self.headers): try: d=parse_json(self); plan=ContentWorker().keyword_plan(d.get('destination',''),d.get('theme')); now=datetime.datetime.now().isoformat(timespec='seconds') sid=execsql('INSERT INTO seo_audits(destination,keyword_plan_json,created_at) VALUES(?,?,?)',(plan['destination'],json.dumps(plan,ensure_ascii=False),now)); return json_out(self,{'ok':True,'seo':plan,'seo_audit_id':sid,'status':'draft','publishing_blocked':True}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/pricing/calculate' and service_user(self.headers): d=parse_json(self) try: components=d.get('components') or [] margin=float(d.get('target_margin_percent', settings().get('online_package_margin','15'))) result=price_breakdown(components, margin, bool(d.get('commercial_rounding',False))) result['ok']=True; result['pricing_version']='v16'; result['pricing_policy']='gross_margin' return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/package-template' and service_user(self.headers): try: qs=parse_qs(u.query); pid=int(qs.get('product_id',['0'])[0] or 0) if not pid: return json_out(self,{'ok':False,'error':'product_id is required'},400) wp=WordPressConnector(); status,data=wp.request('/wp-json/wc/v3/products/'+str(pid)) return json_out(self,{'ok':True,'status':status,'template':product_template(data),'template_version':'v1'}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},502) if p=='/api/ai/package-prepare' and service_user(self.headers): try: d=normalized_package(parse_json(self)) return json_out(self,PriscaAIWorker().package_workflow(d)) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/approval-request' and service_user(self.headers): try: d=normalized_package(parse_json(self)); qa=PriscaAIWorker().package_workflow(d) failed=[x for x in qa['checks'] if not x['ok']] if failed:return json_out(self,{'ok':False,'error':'QA failed; approval request not created.','failed_checks':failed},400) now=datetime.datetime.now().isoformat(timespec='seconds') rid=execsql('INSERT INTO package_approvals(package_json,status,created_by,created_at) VALUES(?,?,?,?)',(json.dumps(d,ensure_ascii=False), 'pending','AI Worker',now)) return json_out(self,{'ok':True,'approval_id':rid,'status':'pending','qa':qa['checks'],'publish_allowed':False}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/ai/package-draft' and service_user(self.headers): try: d=normalized_package(parse_json(self)); qa=PriscaAIWorker().package_workflow(d) failed=[x for x in qa['checks'] if not x['ok']] if failed:return json_out(self,{'ok':False,'error':'QA failed; draft not created.','failed_checks':failed},400) return json_out(self,PriscaAIWorker().create_draft(d)) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},502) user=current_user(self.headers) if not user:return json_out(self,{'error':'Unauthorized'},401) if p=='/api/package-approvals/review' and user['role']=='admin': d=parse_json(self); aid=int(d.get('approval_id') or 0); decision=(d.get('decision') or '').lower(); note=d.get('note','') if decision not in ('approve','reject'): return json_out(self,{'ok':False,'error':'decision must be approve or reject'},400) row=q('SELECT * FROM package_approvals WHERE id=?',(aid,),one=True) if not row:return json_out(self,{'ok':False,'error':'Approval request not found'},404) if row['status']!='pending':return json_out(self,{'ok':False,'error':'Approval request already reviewed'},409) now=datetime.datetime.now().isoformat(timespec='seconds') if decision=='reject': execsql('UPDATE package_approvals SET status=?,reviewed_by=?,reviewed_at=?,review_note=? WHERE id=?',('rejected',user['id'],now,note,aid)) return json_out(self,{'ok':True,'status':'rejected','approval_id':aid}) package=json.loads(row['package_json']); result=PriscaAIWorker().create_draft(package) if not result.get('ok'): return json_out(self,{'ok':False,'error':'WooCommerce draft creation failed','detail':result},502) wp_id=(result.get('product') or {}).get('id') execsql('UPDATE package_approvals SET status=?,reviewed_by=?,reviewed_at=?,review_note=?,wp_product_id=? WHERE id=?',('approved_draft_created',user['id'],now,note,wp_id,aid)) return json_out(self,{'ok':True,'status':'approved_draft_created','approval_id':aid,'wp_product_id':wp_id,'publish_blocked':True}) if p=='/api/integrations/config' and user['role']=='admin': d=parse_json(self); c=db() secret_keys=SECRET_SETTING_KEYS allowed={'wp_domain','wp_url','wp_user','wp_app_password','woo_consumer_key','woo_consumer_secret','cpanel_domain','cpanel_url','cpanel_user','cpanel_token','cpanel_password','cpanel_app_path','cpanel_app_domain','meta_access_token','facebook_page_id','instagram_business_id','youtube_access_token','google_access_token','gsc_site_url','ga_property_id','pinterest_access_token','pinterest_board_id','bing_api_key','bing_site_url','bing_site_url','meta_graph_version'} for key,val in d.items(): if key not in allowed: continue if key in secret_keys: if val is None or str(val).strip()=='' or str(val).strip()=='••••••••': continue val=privacy_encrypt(str(val)) c.execute('INSERT INTO settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value',('int_'+key,str(val))) c.commit(); c.close(); load_integration_env(); return json_out(self,{'ok':True,'config':integration_config(False)}) if p=='/api/integrations/test-config' and user['role']=='admin': d=parse_json(self); platform=(d.get('platform') or '').lower(); s=integration_config(True) try: if platform in ('wordpress','woocommerce'): wc=WordPressConnector(base_url=s.get('wp_url'),username=s.get('wp_user'),application_password=s.get('wp_app_password')); info=wc.site_info(); prods=wc.products(per_page=1); return json_out(self,{'ok':True,'platform':platform,'site':info,'product_count_sample':len(prods.get('rows',[]))}) if platform=='cpanel': c=CPanelClient(s.get('cpanel_url'),s.get('cpanel_user'),s.get('cpanel_token'),s.get('cpanel_password')); return json_out(self,{'ok':True,'platform':'cpanel','result':c.test()}) if platform in ('facebook','instagram'): if not s.get('meta_access_token'): raise ValueError('Meta access token is not configured') return json_out(self,{'ok':True,'platform':platform,'configured':True,'note':'Credentials are stored. Provider publishing remains approval-gated.'}) if platform=='youtube': return json_out(self,{'ok':bool(s.get('youtube_access_token')),'platform':platform,'note':'YouTube OAuth token is stored server-side.'},200 if s.get('youtube_access_token') else 400) if platform=='google_search_console': return json_out(self,{'ok':bool(s.get('google_access_token') and s.get('gsc_site_url')),'platform':platform}) if platform=='pinterest': return json_out(self,{'ok':bool(s.get('pinterest_access_token') and s.get('pinterest_board_id')),'platform':platform}) if platform=='bing_webmaster': return json_out(self,{'ok':bool(s.get('bing_api_key') and s.get('bing_site_url')),'platform':platform}) raise ValueError('Unknown integration') except Exception as e: return json_out(self,{'ok':False,'platform':platform,'error':str(e)},400) if p=='/api/cpanel/install' and user['role']=='admin': d=parse_json(self); s=integration_config(True) for k,v in d.items(): if k in ('cpanel_url','cpanel_user','cpanel_app_path','cpanel_app_domain','cpanel_token','cpanel_password') and v: s[k]=v # Normalize the UI/database field to the deployer's canonical key. # Keep both keys for backwards compatibility with V26 saved settings. if s.get('cpanel_app_domain') and not s.get('app_domain'): s['app_domain']=s.get('cpanel_app_domain') if s.get('cpanel_app_path') and not s.get('app_path'): s['app_path']=s.get('cpanel_app_path') try: result=cpanel_deploy(BASE,s); return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/settings' and user['role']=='admin': d=parse_json(self); c=db(); for k,v in d.items(): c.execute('INSERT INTO settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value',(k,str(v))) c.commit(); c.close(); return json_out(self,{'ok':True,'settings':settings()}) if p=='/api/houseboats': return json_out(self,{'rows':[dict(r) for r in q('SELECT * FROM houseboats WHERE active=1 ORDER BY destination,category,pax')]}) if p=='/api/activities' and user['role']=='admin': d=parse_json(self) rows=d.get('rows') if isinstance(d.get('rows'),list) else [d] created=[] try: for x in rows: if not (x.get('name') or '').strip(): raise ValueError('activity name is required') rid=execsql('INSERT INTO activities(supplier,name,destination,category,adult_rate,child_rate,rate_type,gst_percent,gst_included,valid_from,valid_to,notes,active) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,1)',(x.get('supplier',''),x.get('name','').strip(),x.get('destination',''),x.get('category',''),float(x.get('adult_rate') or 0),float(x.get('child_rate') or 0),x.get('rate_type','per person'),float(x.get('gst_percent') or 0),1 if x.get('gst_included') else 0,x.get('valid_from'),x.get('valid_to'),x.get('notes',''))) created.append(rid) return json_out(self,{'ok':True,'created_ids':created,'count':len(created)}) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/notifications': return json_out(self,{'rows':[dict(r) for r in q('SELECT * FROM notifications WHERE user_id=? ORDER BY id DESC LIMIT 50',(user['id'],))]}) if p=='/api/agents' and user['role']=='admin': d=parse_json(self); now=datetime.datetime.now().isoformat(timespec='seconds'); rid=execsql('INSERT INTO users(name,email,password_hash,role,created_at) VALUES(?,?,?,?,?)',(d['name'],d['email'],hashpw(d.get('password','agent123')),'agent',now)); return json_out(self,{'ok':True,'id':rid}) if p=='/api/pricing/calculate': d=parse_json(self) try: components=d.get('components') or [] # Support structured components as the preferred API contract. margin=float(d.get('target_margin_percent', settings().get('online_package_margin','15'))) result=price_breakdown(components, margin, bool(d.get('commercial_rounding',False))) result['ok']=True; result['pricing_version']='v16'; result['pricing_policy']='gross_margin' return json_out(self,result) except Exception as e: return json_out(self,{'ok':False,'error':str(e)},400) if p=='/api/package': d=parse_json(self); travellers=int(d.get('travellers') or 2); budget=float(d.get('budget') or 0); meal=(d.get('meal_plan') or 'CP').upper(); cat=(d.get('category') or 'Budget').lower(); travel=d.get('travel_date') or '' legs=d.get('legs') or [] if not legs and d.get('destination'): legs=[{'destination':d.get('destination'),'nights':int(d.get('nights') or 2),'room_filter':d.get('room_filter') or '','star_preference':d.get('star_preference') or ''}] if d.get('custom_destination'): legs.append({'destination':d['custom_destination'].strip(),'nights':1,'room_filter':'','star_preference':''}) if not legs:return json_out(self,{'error':'Add at least one destination'},400) total_nights=sum(int(x.get('nights') or 1) for x in legs); s=settings(); target=float(s.get('online_package_margin','15')) if cat in ('online','website','published') else {'budget':10,'mid budget':10,'mid_budget':10,'custom':12,'luxury':15}.get(cat,12); min_margin=float(s.get('min_margin','7')) leg_candidates=[] for leg in legs: dest=(leg.get('destination') or '').strip(); canon=canonical_destination(dest); room_filter=(leg.get('room_filter') or '').strip().lower(); star_pref=leg.get('star_preference') or ''; stay_type=(leg.get('stay_type') or 'Hotel').strip() if stay_type.lower() == 'houseboat': hbcat=(leg.get('houseboat_category') or '').strip().lower() hb_rows=[] aliases=destination_aliases(dest) for a in aliases: hb_rows.extend(q('SELECT * FROM houseboats WHERE active=1 AND lower(destination)=lower(?) ORDER BY rate ASC',(a,))) cand=[] for r in hb_rows: if hbcat and (r['category'] or '').lower()!=hbcat: continue if travel and (r['valid_from'] or r['valid_to']) and ((r['valid_from'] and travelr['valid_to'])): continue pax=travellers if pax<=2: match=(r['pax'] or 0)>=2 else: match=(r['pax'] or 0)>=pax if not match: continue rate=float(r['rate'] or 0) # Supplier sample rates are per couple/per boat and GST is separate. cost=rate if (r['rate_type'] or '').lower()=='per couple' and pax>2: cost=rate*max(1,(pax+1)//2) if float(r['gst_percent'] or 0)>0 and not int(r['gst_included'] or 0): cost*=1+float(r['gst_percent'] or 0)/100 cand.append({'hotel':r['name'],'city':dest,'category':r['category'],'room_type':f"{r['bedrooms']} Bedroom Houseboat",'meal_plan':r['meal_plan'] or meal,'hotel_cost':round(cost,2),'nights':int(leg.get('nights') or 1),'rooms':1,'source':r['supplier'],'valid_to':r['valid_to'],'notes':r['notes'],'rate_type':r['rate_type'],'tac_percent':0.0,'gst_percent':float(r['gst_percent'] or 0),'gst_included':bool(r['gst_included']),'raw_rate':rate,'stay_type':'Houseboat','houseboat_category':r['category'],'houseboat_pax':r['pax'],'houseboat_bedrooms':r['bedrooms']}) if not cand:return json_out(self,{'query':d,'options':[],'reason':f'No valid {hbcat.title() if hbcat else "Houseboat"} rate found for {dest}. Please create a customised package for the above requirement and send a copy to Admin for future queries. Currently not present in the database.'}) leg_candidates.append(cand[:5]); continue rows=[] for a in destination_aliases(dest): rows.extend(q('SELECT * FROM hotels WHERE active=1 AND (lower(city)=lower(?) OR lower(hotel_name) LIKE ?) ORDER BY rate ASC LIMIT 500',(a,f'%{a.lower()}%'))) cand=[] for r in rows: if travel and (r['valid_from'] or r['valid_to']) and ((r['valid_from'] and travelr['valid_to'])): continue if star_pref and r['rating'] and round(float(r['rating'])) not in [float(x) for x in star_pref.split(',') if x]: continue price_band=price_category(r['rate']) if cat in ('budget','mid budget','mid_budget','luxury') and price_band.lower() != cat.replace('_',' ').lower(): continue if room_filter and room_filter not in ((r['room_type'] or '')+' '+(r['notes'] or '')).lower(): continue nights=int(leg.get('nights') or 1); rooms=max(1,(travellers+1)//2); base_rate=float(r['rate'] or 0) # Commercial logic: Rack + TAC is commission, not tax. Net/TA rates do not get TAC applied. if (r['rate_type'] or '').lower().startswith('rack') and float(r['tac_percent'] or 0)>0: base_rate=base_rate*(1-float(r['tac_percent'] or 0)/100) if float(r['gst_percent'] or 0)>0 and not int(r['gst_included'] or 0): base_rate=base_rate*(1+float(r['gst_percent'] or 0)/100) extra_adult=float(r['extra_adult'] or 0) cost=base_rate*nights*rooms+max(0,travellers-rooms*2)*extra_adult*nights cand.append({'hotel':r['hotel_name'],'city':r['city'],'category':r['category'],'room_type':r['room_type'],'meal_plan':r['meal_plan'] or meal,'hotel_cost':round(cost,2),'nights':nights,'rooms':rooms,'source':r['source_file'],'valid_to':r['valid_to'],'notes':r['notes'],'rate_type':r['rate_type'],'tac_percent':float(r['tac_percent'] or 0),'gst_percent':float(r['gst_percent'] or 0),'gst_included':bool(r['gst_included']),'raw_rate':float(r['rate'] or 0),'stay_type':'Hotel'}) if not cand:return json_out(self,{'query':d,'options':[],'reason':f'No valid hotel/rate found for {dest}. Please create a customised package for the above requirement and send a copy to Admin for future queries. Currently not present in the database.'}) leg_candidates.append(cand[:5]) if travellers<=4: vehicle='Sedan' elif travellers<=6: vehicle='Ertiga' elif travellers<=9: vehicle='Luxury 9-Seat TT' elif travellers<=12: vehicle='12-Seat TT' elif travellers<=17: vehicle='17-Seat TT' elif travellers<=26: vehicle='26-Seat TT' elif travellers<=27: vehicle='27-Seat Coach' else:return json_out(self,{'query':d,'options':[],'reason':'No single vehicle capacity available. Please create a customised package for the above requirement and send a copy to Admin for future queries. Currently not present in the database.'}) # Cab matching is STRICT: never treat a supplier route containing extra # destinations as a match for the agent's query. The supplier route may have # implicit pickup/drop endpoints (e.g. Cochin airport), but those must never # leak into the customer-facing itinerary unless explicitly requested. def norm_place(v): v=(v or '').lower().strip() v=re.sub(r'\([^)]*\)','',v) v=re.sub(r'\b(kochi|cochin)\b','cochin',v) v=re.sub(r'\btrivandrum\b','thiruvananthapuram',v) v=re.sub(r'\s+',' ',v) return v.strip(' -') def route_places(route): raw=re.split(r'\s*-\s*|→|>', route or '') out=[] for part in raw: part=re.sub(r'\([^)]*\)','',part).strip() if not part: continue # remove duration/drop annotations while retaining destination names part=re.sub(r'\b(?:drop|pickup|airport|railway station)\b','',part,flags=re.I) part=re.sub(r'\s+',' ',part).strip() if part: out.append(norm_place(part)) return out requested=[norm_place(x.get('destination')) for x in legs] requested=[x for x in requested if x] cab=None cab_candidates=[] for cr in q('SELECT * FROM cabs WHERE active=1 AND vehicle=? ORDER BY rate',(vehicle,)): rp=route_places(cr['route']) # Match the requested destination sequence as a contiguous sequence. # Extra intermediate destinations are not accepted. Supplier pickup/drop # endpoints are allowed only at the two ends and are hidden from PDF. for start in range(max(0,len(rp)-len(requested)+1)): if rp[start:start+len(requested)]==requested: cab_candidates.append((cr, start, rp)); break if cab_candidates: cab=cab_candidates[0][0] if not cab:return json_out(self,{'query':d,'options':[],'reason':'Cab route/rate currently not present in the database for the exact requested destination sequence. Please create a customised package for the above requirement and send a copy to Admin for future queries. Currently not present in the database.','custom_request':{'agent_id':user['id'],'legs':legs,'travellers':travellers,'travel_date':travel}}) from itertools import product options=[] for combo in list(product(*leg_candidates))[:20]: hotel_total=sum(x['hotel_cost'] for x in combo); activity_total=0.0; activity_items=[] for a in (d.get('activities') or []): amt=float(a.get('amount') or 0); activity_total += max(0,amt); activity_items.append({'name':a.get('name','Activity'),'amount':round(max(0,amt),2)}) total=hotel_total+float(cab['rate'] or 0)+activity_total; sell=calculate_margin_price(total,target); minsell=calculate_margin_price(total,min_margin) if budget and sell>budget:continue options.append({'legs':list(combo),'hotel':' + '.join(x['hotel'] for x in combo),'supplier_cost':round(total,2),'our_price':round(total,2),'standard_selling':round(sell,2),'min_selling':round(minsell,2),'max_discount':round(max(0,sell-minsell),2),'cab_cost':float(cab['rate'] or 0),'activity_cost':round(activity_total,2),'activities':activity_items,'cab_vehicle':vehicle,'cab_route':cab['route'],'customer_route':requested_customer_route(legs), 'itinerary':build_common_itinerary(legs,total_nights),'nights':total_nights,'travellers':travellers,'category':cat.title(),'valid_to':min([x['valid_to'] for x in combo if x['valid_to']] or [''])}) options=sorted(options,key=lambda x:abs(x['standard_selling']-budget) if budget else x['standard_selling'])[:8] created=datetime.datetime.now().isoformat(timespec='seconds'); pid=execsql('INSERT INTO packages(agent_id,query_json,option_json,created_at) VALUES(?,?,?,?)',(user['id'],json.dumps(d),json.dumps(options),created)); return json_out(self,{'id':pid,'query':d,'target_margin_percent':target,'minimum_margin_percent':min_margin,'options':options,'created_at':created}) if p=='/api/package_pdf': d=parse_json(self); opt=d.get('option') or {} try: from reportlab.platypus import SimpleDocTemplate,Paragraph,Spacer,Table,TableStyle,Image from reportlab.lib.pagesizes import A4; from reportlab.lib.styles import getSampleStyleSheet; from reportlab.lib import colors; from reportlab.lib.units import mm from reportlab.pdfbase import pdfmetrics from reportlab.pdfbase.ttfonts import TTFont out=io.BytesIO(); doc=SimpleDocTemplate(out,pagesize=A4,rightMargin=14*mm,leftMargin=14*mm,topMargin=12*mm,bottomMargin=12*mm) st=getSampleStyleSheet() font_path=os.path.join(BASE,'static','fonts','DejaVuSans.ttf') bold_path=os.path.join(BASE,'static','fonts','DejaVuSans-Bold.ttf') if os.path.exists(font_path): try: pdfmetrics.registerFont(TTFont('PriscaSans',font_path)); st['Normal'].fontName='PriscaSans'; st['Title'].fontName='PriscaSans'; st['Heading2'].fontName='PriscaSans'; st['Heading3'].fontName='PriscaSans' except Exception: pass if os.path.exists(bold_path): try: pdfmetrics.registerFont(TTFont('PriscaSansBold',bold_path)) except Exception: pass story=[]; logo=os.path.join(BASE,'logo.png') if os.path.exists(logo): story.append(Image(logo,width=32*mm,height=32*mm)) story += [Paragraph('PRISCA HOLIDAYS LLP',st['Title']),Paragraph('Where Any body can Travel',st['Normal']),Spacer(1,7),Paragraph(f"{safe_pdf_text(d.get('state',''))} Holiday Package",st['Heading2']),Paragraph(f"Travellers: {safe_pdf_text(d.get('travellers',2))} Adults · {safe_pdf_text(d.get('children',0))} Children · {safe_pdf_text(d.get('infants',0))} Infants · Travel: {safe_pdf_text(d.get('travel_date') or 'To be confirmed')}",st['Normal']),Spacer(1,8)] rows=[['Destination / Hotel','Room / Stay','Nights']] for l in opt.get('legs',[]): stay=l.get('stay_type') or 'Hotel'; name=l.get('hotel','') if stay.lower()=='houseboat': name=f"{name} / Similar" else: name=f"{name} / Similar" room=l.get('room_type') or 'Room' if stay.lower()=='houseboat' and l.get('houseboat_category'): room=f"{l.get('houseboat_category')} · {room} · Overnight" rows.append([f"{safe_pdf_text(l.get('city',''))} — {safe_pdf_text(name)}",safe_pdf_text(room),str(l.get('nights',1))]) t=Table(rows,colWidths=[95*mm,60*mm,18*mm]); t.setStyle(TableStyle([('BACKGROUND',(0,0),(-1,0),colors.HexColor('#0d4d63')),('TEXTCOLOR',(0,0),(-1,0),colors.white),('GRID',(0,0),(-1,-1),.3,colors.grey),('VALIGN',(0,0),(-1,-1),'TOP')])); story += [t,Spacer(1,8),Paragraph('Transportation',st['Heading3']),Paragraph(f"Prisca Luxury Cab Service — {safe_pdf_text(opt.get('cab_vehicle',''))}. Route: {safe_pdf_text(opt.get('customer_route',''))}",st['Normal']),Spacer(1,7),Paragraph('Day by Day Plan',st['Heading3'])] for day in opt.get('itinerary',[]): story.append(Paragraph(f"Day {safe_pdf_text(day.get('day'))}: {safe_pdf_text(day.get('title'))}",st['Normal'])) for item in day.get('items',[]): story.append(Paragraph('• '+safe_pdf_text(item),st['Normal'])) story.append(Spacer(1,3)) story += [Spacer(1,5),Paragraph('Package Price',st['Heading3']),Paragraph(f"₹{float(opt.get('standard_selling',0)):,.0f}",st['Title']),Spacer(1,8),Paragraph('Important: Hotel names are subject to availability and may be substituted with a similar standard/category property where applicable. Sightseeing/activity entry charges are excluded unless specifically mentioned. Supplier terms and applicable cancellation conditions apply.',st['Normal']),Spacer(1,10),Paragraph('www.priscaholidays.com · 9910705328 · contact@priscaholidays.com',st['Normal'])] doc.build(story); b=out.getvalue(); self.send_response(200); self.send_header('Content-Type','application/pdf'); self.send_header('Content-Disposition','attachment; filename="Prisca_Package_Quotation.pdf"'); self.send_header('Content-Length',str(len(b))); self.end_headers(); self.wfile.write(b); return except Exception as e: return json_out(self,{'error':'PDF generation failed: '+str(e)},500) if p=='/api/sale': d=parse_json(self); sale=float(d['sale_value']); our=float(d['our_price']); cost=float(d['supplier_cost']); customer=d['customer']; now=datetime.datetime.now().isoformat(timespec='seconds') cid=execsql('INSERT INTO customers(name,phone,email,travellers,travel_date,destination,notes,created_at) VALUES(?,?,?,?,?,?,?,?)',(customer.get('name'),customer.get('phone'),customer.get('email'),customer.get('travellers'),customer.get('travel_date'),customer.get('destination'),customer.get('notes',''),now)); pending=max(0,sale-float(d.get('amount_received') or 0)); margin=max(0,sale-cost); bid=execsql('INSERT INTO bookings(customer_id,agent_id,package_id,sale_value,our_price,supplier_cost,amount_received,pending_amount,payment_due_date,margin_amount,customer_paid,customer_due,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)',(cid,user['id'],d.get('package_id'),sale,our,cost,float(d.get('amount_received') or 0),pending,d.get('payment_due_date'),margin,float(d.get('amount_received') or 0),pending,now)); return json_out(self,{'ok':True,'booking_id':bid,'pending':pending,'margin':margin}) if p=='/api/import_approve' and user['role']=='admin': d=parse_json(self); iid=int(d.get('import_id') or 0); ids=d.get('row_ids') or [] c=db() if ids: for rid in ids: c.execute('UPDATE import_rows SET approved=1 WHERE id=? AND import_id=?',(int(rid),iid)) else: c.execute("UPDATE import_rows SET approved=1 WHERE import_id=? AND confidence>=0.75 AND data_type IN ('hotel','houseboat')",(iid,)) c.commit(); c.close(); return json_out(self,{'ok':True,'approved':q('SELECT COUNT(*) n FROM import_rows WHERE import_id=? AND approved=1',(iid,),one=True)['n']}) if p=='/api/import_publish' and user['role']=='admin': d=parse_json(self); iid=int(d.get('import_id') or 0); n=publish_import(iid); return json_out(self,{'ok':True,'published':n}) if p=='/api/import': # multipart/form-data upload ctype=self.headers.get('Content-Type','') if 'multipart/form-data' not in ctype:return json_out(self,{'error':'Use multipart/form-data'},400) n=int(self.headers.get('Content-Length','0')); raw=self.rfile.read(n); msg=BytesParser(policy=default).parsebytes(b'Content-Type: '+ctype.encode()+b'\r\n\r\n'+raw) part=next((x for x in msg.iter_parts() if x.get_filename()),None) if not part:return json_out(self,{'error':'No file'},400) fn=os.path.basename(part.get_filename()); path=os.path.join(UPLOADS,secrets.token_hex(6)+'_'+fn); open(path,'wb').write(part.get_payload(decode=True)); text=import_text(path,fn); iid=execsql('INSERT INTO imports(filename,file_type,uploaded_by,uploaded_at,extracted_text,status) VALUES(?,?,?,?,?,?)',(fn,os.path.splitext(fn)[1].lower(),user['id'],datetime.datetime.now().isoformat(timespec='seconds'),text[:500000],'review')); rows=stage_import(iid,text,fn,path); return json_out(self,{'ok':True,'import_id':iid,'filename':fn,'row_count':len(rows),'preview':text[:5000],'normalized':rows[:100]}) return json_out(self,{'error':'Not found'},404) if __name__=='__main__': init_db(); seed_excel(); seed_rainwood(); normalize_price_categories(); seed_cabs(); port=int(os.environ.get('PORT','8765')); print(f'Prisca Smart Package Builder running at http://127.0.0.1:{port}'); ThreadingHTTPServer(('127.0.0.1',port),H).serve_forever()