"""
BizNode Sample Data Seeder
==========================
Populates the database with realistic demo data for all 5 business scenarios
described in the cookbook. Safe to re-run — clears existing seed data first.

Usage:
    python scripts/seed_data.py

Scenarios covered:
  1. Dr. Kavitha's Family Clinic (Bengaluru)
  2. Priya's Design Studio (Freelance Graphic Designer)
  3. Sharma's Tiffin Service (Cloud Kitchen, Hyderabad)
  4. Excel Coaching Centre (Mumbai)
  5. TechEase IT Services (Pune)
"""

import sys
import os
import json
import uuid
from datetime import datetime, timedelta

# Add project root so memory/database.py can be imported
_PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, _PROJECT_ROOT)

import importlib.util

def _load_db():
    spec = importlib.util.spec_from_file_location(
        "_db", os.path.join(_PROJECT_ROOT, "memory", "database.py")
    )
    mod = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(mod)
    return mod

db = _load_db()

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def _now(offset_days=0, offset_hours=0):
    dt = datetime.now() + timedelta(days=offset_days, hours=offset_hours)
    return dt.strftime("%Y-%m-%d %H:%M:%S")


def _run(conn, sql, params=()):
    conn.execute(sql, params)


# ---------------------------------------------------------------------------
# Clear only tables that contain seed data
# ---------------------------------------------------------------------------

_SEED_TABLES = [
    "notes", "leads", "menu_items",
    "workflows", "workflow_steps",
    "query_logs", "scheduled_tasks",
    "associates",
]


def clear_seed_data(conn):
    print("Clearing existing seed data...")
    for tbl in _SEED_TABLES:
        conn.execute(f"DELETE FROM {tbl}")
        conn.execute("DELETE FROM sqlite_sequence WHERE name = ?", (tbl,))
    conn.commit()
    print("  Done.")


# ---------------------------------------------------------------------------
# Scenario 1 — Dr. Kavitha's Family Clinic, Bengaluru
# ---------------------------------------------------------------------------

def seed_clinic(conn):
    print("Seeding Scenario 1: Dr. Kavitha's Family Clinic...")

    # Knowledge notes
    notes = [
        ("About the Clinic",
         "Dr. Kavitha's Family Clinic is a trusted family healthcare centre in Bengaluru, "
         "serving patients for over 10 years. Led by Dr. Kavitha Nair (MBBS, MD), we provide "
         "general, paediatric, and chronic disease consultations.",
         "Family clinic, Bengaluru, Dr Kavitha Nair"),

        ("Consultation Fees",
         "General consultation: Rs 500\n"
         "Follow-up visit (within 15 days of last visit): Rs 300\n"
         "Paediatric consultation (children under 12): Rs 500\n"
         "Diabetes / Thyroid review: Rs 600\n"
         "Medical certificate / fitness letter: Rs 200 additional charge",
         "fees, consultation, charges"),

        ("Clinic Timings",
         "Monday to Saturday: 9 AM to 1 PM, 4 PM to 8 PM\n"
         "Sunday: 10 AM to 12 PM (emergency and fever cases only)\n"
         "Public holidays: Closed. Emergency contact: 9876543210",
         "timings, hours, open, sunday, holiday"),

        ("Location and Parking",
         "Address: 14, Gandhi Nagar, 2nd Cross, Bengaluru 560040\n"
         "Near Indiranagar Metro Station — 5-minute walk from Exit 2.\n"
         "Free parking available on the side street (approx 15 spots).\n"
         "Auto and cab drop-off at clinic entrance.",
         "location, address, parking, metro, how to reach"),

        ("Insurance and Cashless",
         "We accept cashless claims for: Star Health Insurance, ICICI Lombard, Niva Bupa.\n"
         "For all other insurance providers: pay at clinic and claim reimbursement.\n"
         "Please carry your insurance card and government photo ID.",
         "insurance, cashless, star health, ICICI, claim"),

        ("Lab Tests",
         "In-clinic lab for basic diagnostic tests.\n"
         "Blood sugar (fasting/PP): report in 1 hour.\n"
         "Complete Blood Count (CBC): report in 2 hours.\n"
         "Thyroid (TSH, T3, T4): report same day.\n"
         "Lipid profile, urine routine: report same day.\n"
         "All lab tests require a doctor's prescription.",
         "lab, tests, blood sugar, thyroid, CBC, reports"),

        ("Frequently Asked Questions",
         "Q: Do I need an appointment?\n"
         "A: Walk-ins are welcome. For specific time slots, book via this bot.\n\n"
         "Q: Is there a waiting area?\n"
         "A: Yes, air-conditioned waiting area with seating for 20 patients.\n\n"
         "Q: Can I get a prescription refill without visiting?\n"
         "A: For chronic conditions (BP, diabetes), prescription renewal every 3 months requires a visit.\n\n"
         "Q: Do you provide home visits?\n"
         "A: Home visits are available for senior citizens (65+) within 5 km. Call 9876543210.",
         "FAQ, appointment, walk-in, home visit, prescription"),
    ]
    for title, content, tags in notes:
        _run(conn,
             "INSERT INTO notes (node_id, title, content, tags) VALUES (?, ?, ?, ?)",
             ("clinic_kavitha", title, content, tags))

    # Menu items
    menus = [
        ("Book Appointment", "task", None, "/book", 1),
        ("Clinic Timings & Fees", "task", None, "/ask", 2),
        ("Find Us on Maps", "web_link", "https://maps.google.com/?q=Gandhi+Nagar+Bengaluru", None, 3),
        ("Call the Clinic", "web_link", "tel:+919876543210", None, 4),
    ]
    for name, mtype, link, task_code, order in menus:
        _run(conn,
             "INSERT INTO menu_items (menu_name, menu_type, link, task_code, sort_order, is_active) "
             "VALUES (?, ?, ?, ?, ?, 1)",
             (name, mtype, link, task_code, order))

    # Appointment workflow
    _run(conn,
         "INSERT INTO workflows (workflow_code, description, trigger_command, is_active) "
         "VALUES (?, ?, ?, 1)",
         ("CLINIC_APPOINTMENT", "Patient appointment booking with email verification",
          "/book"))
    steps = [
        (1, "collect_input", "What is your full name?", "name"),
        (2, "collect_input", "Your mobile number:", "phone"),
        (3, "collect_input",
         "Reason for visit? (e.g. fever, diabetes review, general checkup, paediatric)", "reason"),
        (4, "collect_input",
         "Preferred date and time slot (e.g. Mon 10 Mar, morning):", "date"),
        (5, "collect_input", "Your email address for confirmation:", "email"),
        (6, "send_otp",      None, None),
        (7, "validate_otp",  "Enter the 6-digit code sent to your email:", None),
        (8, "show_message",
         "Appointment request received! Dr. Kavitha's team will call {name} at {phone} "
         "to confirm your visit. Thank you!", None),
    ]
    for order, stype, prompt, field in steps:
        _run(conn,
             "INSERT INTO workflow_steps (workflow_code, step_order, step_type, step_prompt, field_name) "
             "VALUES (?, ?, ?, ?, ?)",
             ("CLINIC_APPOINTMENT", order, stype, prompt, field))

    # Sample query logs (simulated patient queries)
    queries = [
        ("111111111", "patient_raj", "What are the consultation fees?",
         "General consultation is Rs 500. Follow-up within 15 days is Rs 300."),
        ("111111112", "meena_k", "Is the clinic open on Sunday?",
         "Yes, Sunday 10 AM to 12 PM for emergencies and fever cases only."),
        ("111111113", "ajay_v", "Do you accept Star Health cashless?",
         "Yes, we accept cashless claims for Star Health Insurance. Please carry your insurance card."),
        ("111111114", "preethi_s", "How long does a CBC test report take?",
         "CBC reports are ready in approximately 2 hours."),
        ("111111115", "ravi_m", "Can I book an appointment for tomorrow morning?",
         "Certainly! Please use /book to submit your appointment request."),
        ("111111116", "sunita_d", "Is there parking near the clinic?",
         "Yes, free parking is available on the side street with about 15 spots."),
        ("111111117", "arun_t", "What is the fee for a diabetes review?",
         "Diabetes and thyroid review consultations are Rs 600."),
        ("111111118", "kavya_n", "Do you do home visits?",
         "Home visits are available for senior citizens (65+) within 5 km. Call 9876543210."),
    ]
    for tg_id, username, query, response in queries:
        _run(conn,
             "INSERT INTO query_logs (telegram_id, username, query, response, source) "
             "VALUES (?, ?, ?, ?, 'telegram')",
             (tg_id, username, query, response))

    # Leads
    leads = [
        ("Rajesh Kumar", "Self-employed", "@rajesh_k / 9000000001",
         "Enquired about monthly diabetes review package. Interested in 3-month package.",
         "telegram", "new"),
        ("Meena Sharma", "Homemaker", "@meena_s / 9000000002",
         "Asking about paediatric consultation for 4-year-old. Requested Saturday morning slot.",
         "telegram", "contacted"),
    ]
    for name, biz, contact, summary, source, status in leads:
        _run(conn,
             "INSERT INTO leads (name, business, contact_info, summary, source, status) "
             "VALUES (?, ?, ?, ?, ?, ?)",
             (name, biz, contact, summary, source, status))

    conn.commit()
    print("  Clinic: 7 notes, 4 menus, 1 workflow (8 steps), 8 query logs, 2 leads.")


# ---------------------------------------------------------------------------
# Scenario 2 — Priya's Design Studio (Freelance)
# ---------------------------------------------------------------------------

def seed_designer(conn):
    print("Seeding Scenario 2: Priya's Design Studio...")

    notes = [
        ("About Priya's Design Studio",
         "Priya's Design Studio offers professional graphic design services for startups, "
         "SMEs, and personal brands. Based in Mumbai, serving clients pan-India. "
         "Led by Priya Menon (10+ years experience, ex-JWT Mumbai).",
         "about, design studio, branding, Mumbai"),

        ("Service Packages and Pricing",
         "LOGO DESIGN — Rs 5,000\n"
         "  3 initial concepts, 2 rounds of revisions\n"
         "  Final files: PNG, SVG, PDF\n"
         "  Delivery: 5 working days\n\n"
         "BRAND IDENTITY PACK — Rs 15,000\n"
         "  Logo + business card + letterhead + social media kit\n"
         "  Delivery: 7-10 working days\n"
         "  30-day unlimited revision support after delivery\n\n"
         "SOCIAL MEDIA MONTHLY PACK — Rs 3,000/month\n"
         "  12 designed posts per month (Instagram/Facebook)\n"
         "  4 story templates per month\n"
         "  Turnaround: 48 hours per batch",
         "packages, pricing, logo, branding, social media, cost"),

        ("Revision Policy",
         "Logo Design: 2 revision rounds included. Additional rounds: Rs 500 each.\n"
         "Brand Identity: Unlimited revisions for 30 days after final delivery.\n"
         "Social Media Pack: Unlimited minor text changes within the monthly subscription.",
         "revisions, changes, policy"),

        ("Payment Terms",
         "50% advance payment required before work starts.\n"
         "Remaining 50% to be paid before final files are shared.\n"
         "Payment via UPI: priya@ybl or Razorpay link (shared on request).\n"
         "International clients: PayPal or Wise accepted.",
         "payment, advance, UPI, PayPal, terms"),

        ("Turnaround Times and Rush Orders",
         "Logo Design: 5 working days\n"
         "Full Brand Identity: 7-10 working days\n"
         "Social Media Posts: 48 hours per batch\n\n"
         "Rush orders available (50% surcharge on package price):\n"
         "  Logo in 48 hours, branding in 4 days",
         "turnaround, delivery time, rush, urgent, deadline"),

        ("File Formats Delivered",
         "All projects include: PNG, JPG, PDF, SVG (vector format).\n"
         "Editable source files (Adobe Illustrator .AI or Photoshop .PSD) included in all packages.\n"
         "Files delivered via Google Drive shared folder or WeTransfer.",
         "files, formats, AI, PSD, SVG, source file, editable"),
    ]
    for title, content, tags in notes:
        _run(conn,
             "INSERT INTO notes (node_id, title, content, tags) VALUES (?, ?, ?, ?)",
             ("designer_priya", title, content, tags))

    menus = [
        ("View Portfolio", "web_link", "https://behance.net/priyadesigns", None, 1),
        ("Get a Quote", "task", None, "/quote", 2),
        ("Packages & Pricing", "task", None, "/ask", 3),
        ("Contact Priya", "bot_link", "https://t.me/priya_designs", None, 4),
    ]
    for name, mtype, link, task_code, order in menus:
        _run(conn,
             "INSERT INTO menu_items (menu_name, menu_type, link, task_code, sort_order, is_active) "
             "VALUES (?, ?, ?, ?, ?, 1)",
             (name, mtype, link, task_code, order))

    _run(conn,
         "INSERT INTO workflows (workflow_code, description, trigger_command, is_active) "
         "VALUES (?, ?, ?, 1)",
         ("DESIGN_QUOTE", "Project quote request with email verification", "/quote"))
    steps = [
        (1, "collect_input",
         "What type of project do you need? (Logo / Brand Identity / Social Media / Other)", "project_type"),
        (2, "collect_input", "Tell me about your business and what you're looking for:", "description"),
        (3, "collect_input", "What is your approximate budget range?", "budget"),
        (4, "collect_input", "What is your deadline or expected start date?", "deadline"),
        (5, "collect_input", "Your email address (for the detailed quote):", "email"),
        (6, "send_otp",      None, None),
        (7, "validate_otp",  "Enter the 6-digit verification code from your email:", None),
        (8, "show_message",
         "Project brief received! Priya will review your {project_type} request "
         "and reply within 24 hours. Watch your inbox!", None),
    ]
    for order, stype, prompt, field in steps:
        _run(conn,
             "INSERT INTO workflow_steps (workflow_code, step_order, step_type, step_prompt, field_name) "
             "VALUES (?, ?, ?, ?, ?)",
             ("DESIGN_QUOTE", order, stype, prompt, field))

    queries = [
        ("222222221", "startup_rohan", "How much for a logo?",
         "Our Logo Design package is Rs 5,000 and includes 3 concepts, 2 revision rounds, and all file formats."),
        ("222222222", "nisha_boutique", "Do you do social media graphics?",
         "Yes! Our Social Media Monthly Pack is Rs 3,000/month for 12 posts and 4 story templates."),
        ("222222223", "tech_startup_vik", "What file formats do you provide?",
         "All projects include PNG, JPG, PDF, SVG, and editable AI/PSD source files."),
        ("222222224", "wedding_planner", "I need a full branding kit urgently in 4 days. Is that possible?",
         "Yes, rush orders are available with a 50% surcharge. Full brand identity in 4 days would be Rs 22,500."),
        ("222222225", "cafe_owner_dm", "Can I pay in instalments?",
         "We require 50% advance before starting and the remaining 50% before delivery of final files."),
    ]
    for tg_id, username, query, response in queries:
        _run(conn,
             "INSERT INTO query_logs (telegram_id, username, query, response, source) "
             "VALUES (?, ?, ?, ?, 'telegram')",
             (tg_id, username, query, response))

    leads = [
        ("Rohan Mehta", "Tech Startup, Pune", "@startup_rohan / rohan@techco.in",
         "Needs full brand identity (logo + business card + social kit). Budget: Rs 12-15K. Deadline: 2 weeks.",
         "telegram", "new"),
        ("Divya Nair", "Wedding Planning Co.", "@divya_events / divya@events.com",
         "Requires a complete visual identity for new business launch. Interested in Brand Identity Pack.",
         "telegram", "new"),
    ]
    for name, biz, contact, summary, source, status in leads:
        _run(conn,
             "INSERT INTO leads (name, business, contact_info, summary, source, status) "
             "VALUES (?, ?, ?, ?, ?, ?)",
             (name, biz, contact, summary, source, status))

    # Automated task — daily digest
    task_id = str(uuid.uuid4())
    _run(conn,
         "INSERT INTO scheduled_tasks (id, name, type, schedule_cron, action_json, status) "
         "VALUES (?, ?, ?, ?, ?, 'active')",
         (task_id, "Daily Business Digest", "http",
          "0 21 * * *",
          json.dumps({"url": "http://localhost:7777/api/digest/send", "method": "POST"})))

    conn.commit()
    print("  Designer: 6 notes, 4 menus, 1 workflow (8 steps), 5 query logs, 2 leads, 1 task.")


# ---------------------------------------------------------------------------
# Scenario 3 — Sharma's Tiffin Service, Hyderabad
# ---------------------------------------------------------------------------

def seed_tiffin(conn):
    print("Seeding Scenario 3: Sharma's Tiffin Service, Hyderabad...")

    notes = [
        ("About Sharma's Tiffin Service",
         "Sharma's Tiffin Service is a home-style cloud kitchen in Hyderabad offering fresh, "
         "vegetarian tiffin meals. Run by Mrs. Annapurna Sharma since 2018. "
         "We serve 50+ daily customers across Ameerpet, SR Nagar, and Punjagutta.",
         "about, tiffin, cloud kitchen, Hyderabad, vegetarian"),

        ("Today's Menu",
         "LUNCH BOX — Rs 120\n"
         "  Rice, Dal Tadka, Aloo Matar, Palak Paneer, Chapati x3, Papad, Pickle\n\n"
         "SPECIAL BOX — Rs 150\n"
         "  Everything in Lunch Box + Dessert (today: Gulab Jamun 2 pcs)\n\n"
         "MINI BOX — Rs 90\n"
         "  Rice, Dal, 1 Sabzi, Chapati x2\n\n"
         "All meals are freshly cooked, no preservatives, pure vegetarian.",
         "menu, today, lunch, tiffin, price"),

        ("Delivery Areas and Charges",
         "FREE delivery (within 3 km):\n"
         "  Ameerpet, SR Nagar, Punjagutta, Begumpet (main areas)\n\n"
         "Rs 30 delivery charge:\n"
         "  Banjara Hills, Jubilee Hills, Madhapur\n\n"
         "Minimum order for delivery: Rs 200 (2 boxes)\n"
         "Delivery time: Lunch 1 PM, Dinner 7:30 PM",
         "delivery, areas, charges, free delivery, Ameerpet"),

        ("Order Cutoff Times",
         "Lunch orders: must be placed by 11 AM for 1 PM delivery\n"
         "Dinner orders: must be placed by 5 PM for 7:30 PM delivery\n"
         "No orders accepted after cutoff. Plan ahead!\n"
         "To place an order: type /order or click the Order button.",
         "order, cutoff, timing, lunch, dinner"),

        ("Monthly Subscription Plans",
         "MONTHLY LUNCH PLAN — Rs 3,000/month\n"
         "  Lunch box daily, Monday to Saturday (26 deliveries/month)\n"
         "  Saturday: 1 extra dish added\n\n"
         "MONTHLY COMBO (Lunch + Dinner) — Rs 5,500/month\n"
         "  Both meals daily, Mon-Sat\n\n"
         "To subscribe: message us with your address and plan choice.\n"
         "Advance payment required for monthly plans.",
         "subscription, monthly, plan, combo, Rs 3000, Rs 5500"),

        ("Allergy and Dietary Info",
         "All food is 100% vegetarian. No eggs.\n"
         "Jain-friendly (no onion/garlic) available every Tuesday automatically.\n"
         "No onion/garlic option on any day: available with 1-day advance notice (same price).\n"
         "Nut allergy warning: desserts may contain nuts (check daily menu).\n"
         "No MSG or artificial colours used.",
         "allergy, vegetarian, Jain, no onion, nuts, dietary"),
    ]
    for title, content, tags in notes:
        _run(conn,
             "INSERT INTO notes (node_id, title, content, tags) VALUES (?, ?, ?, ?)",
             ("tiffin_sharma", title, content, tags))

    menus = [
        ("Order Now", "task", None, "/order", 1),
        ("Today's Menu", "task", None, "/ask", 2),
        ("Subscribe Monthly", "task", None, "/ask", 3),
        ("Delivery Areas", "task", None, "/ask", 4),
    ]
    for name, mtype, link, task_code, order in menus:
        _run(conn,
             "INSERT INTO menu_items (menu_name, menu_type, link, task_code, sort_order, is_active) "
             "VALUES (?, ?, ?, ?, ?, 1)",
             (name, mtype, link, task_code, order))

    _run(conn,
         "INSERT INTO workflows (workflow_code, description, trigger_command, is_active) "
         "VALUES (?, ?, ?, 1)",
         ("TIFFIN_ORDER", "Pre-order placement (no OTP needed for food speed)", "/order"))
    steps = [
        (1, "collect_input",
         "Which box would you like?\n  Lunch Box Rs 120 / Special Box Rs 150 / Mini Box Rs 90",
         "box_type"),
        (2, "collect_input", "How many boxes?", "quantity"),
        (3, "collect_input", "Delivery or pickup?", "delivery_type"),
        (4, "collect_input", "Your delivery address (or type 'pickup' if collecting):", "address"),
        (5, "collect_input", "Your name and mobile number:", "contact"),
        (6, "show_message",
         "Order noted! {quantity} x {box_type} for {delivery_type}. "
         "We will confirm via call. Delivery at 1 PM. Thank you!", None),
    ]
    for order, stype, prompt, field in steps:
        _run(conn,
             "INSERT INTO workflow_steps (workflow_code, step_order, step_type, step_prompt, field_name) "
             "VALUES (?, ?, ?, ?, ?)",
             ("TIFFIN_ORDER", order, stype, prompt, field))

    queries = [
        ("333333331", "rk_ameerpet", "What is in today's lunch?",
         "Today's Lunch Box (Rs 120): Rice, Dal Tadka, Aloo Matar, Palak Paneer, Chapati x3, Papad and Pickle."),
        ("333333332", "office_guy_sr", "Do you deliver to SR Nagar?",
         "Yes! SR Nagar is in our free delivery zone. No extra charge."),
        ("333333333", "jain_family", "Is Jain food available?",
         "Yes! Every Tuesday we automatically serve Jain-friendly (no onion/garlic) meals. "
         "For other days, request 1 day in advance at the same price."),
        ("333333334", "monthly_sub_q", "How much is the monthly plan?",
         "Monthly Lunch Plan: Rs 3,000 for 26 deliveries (Mon-Sat). Combo (lunch+dinner): Rs 5,500/month."),
        ("333333335", "late_order_q", "Can I order for today's lunch now? It is 11:30 AM.",
         "Lunch orders must be placed by 11 AM for 1 PM delivery. Today's lunch is now closed. "
         "You can order for dinner by 5 PM."),
        ("333333336", "banjara_q", "Do you deliver to Banjara Hills?",
         "Yes, we deliver to Banjara Hills with a Rs 30 delivery charge. Minimum order Rs 200."),
    ]
    for tg_id, username, query, response in queries:
        _run(conn,
             "INSERT INTO query_logs (telegram_id, username, query, response, source) "
             "VALUES (?, ?, ?, ?, 'telegram')",
             (tg_id, username, query, response))

    # Associates (monthly subscribers treated as key customers / associates)
    associates = [
        ("SUBS-001", "Rajendra Office Group", "333333337",
         "office_raj", "subscriber", "corporate"),
        ("SUBS-002", "Meena's Family Plan", "333333338",
         "meena_family", "subscriber", "residential"),
    ]
    for nid, name, tg_id, tg_user, role, btype in associates:
        _run(conn,
             "INSERT INTO associates (network_id, name, telegram_id, role, business_type, status) "
             "VALUES (?, ?, ?, ?, ?, 'active')",
             (nid, name, tg_id, role, btype))

    conn.commit()
    print("  Tiffin: 6 notes, 4 menus, 1 workflow (6 steps), 6 query logs, 2 associates.")


# ---------------------------------------------------------------------------
# Scenario 4 — Excel Coaching Centre, Mumbai
# ---------------------------------------------------------------------------

def seed_coaching(conn):
    print("Seeding Scenario 4: Excel Coaching Centre, Mumbai...")

    notes = [
        ("About Excel Coaching Centre",
         "Excel Coaching Centre, Mumbai provides quality coaching for students in Classes 6 to 10. "
         "Founded in 2015 by Prof. Suresh Iyer (M.Sc. Maths, 20 years teaching experience). "
         "Our students consistently score 90%+ in board exams.",
         "about, coaching, Maths, Science, Mumbai, Class 10"),

        ("Courses and Fees",
         "CLASS 6-8 (Foundation Level)\n"
         "  Maths only: Rs 1,500/month\n"
         "  Science only: Rs 1,500/month\n"
         "  Maths + Science: Rs 2,500/month\n\n"
         "CLASS 9-10 (Board Exam Preparation)\n"
         "  Maths: Rs 2,000/month\n"
         "  Science: Rs 2,000/month\n"
         "  Full Package (Maths + Science + English): Rs 4,500/month\n\n"
         "First month fee is payable after the free demo class.",
         "fees, price, courses, Maths, Science, class 9, class 10"),

        ("Batch Timings",
         "Morning Batch (Class 9 & 10 only): 7 AM to 9 AM, Mon-Sat\n"
         "Afternoon Batch (Class 6, 7, 8): 2 PM to 4 PM, Mon-Sat\n"
         "Evening Batch (all classes): 5 PM to 7 PM, Mon-Sat\n"
         "Weekend Special (all classes): Saturday 10 AM to 1 PM (doubt-clearing + revision)\n\n"
         "Online batch also available for outstation students.",
         "batch, timings, morning, afternoon, evening, Saturday, online"),

        ("Admission Process",
         "Step 1: Register your interest via /register (takes 2 minutes)\n"
         "Step 2: Attend a FREE demo class (every Saturday 10 AM)\n"
         "Step 3: Pay first month fee to confirm seat\n\n"
         "Demo classes run every Saturday. No commitment required.\n"
         "Seats are limited — register early.",
         "admission, register, demo class, join, enrol"),

        ("Our Results 2024",
         "Board Exam 2024 highlights:\n"
         "  12 students scored above 90% in Maths (Class 10 CBSE)\n"
         "  4 students scored 100/100 in Maths\n"
         "  Average Maths improvement for new students: +40 marks in 6 months\n"
         "  Science: 8 students scored above 85%\n\n"
         "Students who join in Class 6 typically rank in the top 10% of their school by Class 8.",
         "results, scores, 2024, board exam, 100 marks, rank"),

        ("Frequently Asked Questions",
         "Q: Can my child join mid-year?\n"
         "A: Yes. We assess the student's current level and assign them appropriately.\n\n"
         "Q: Are study materials provided?\n"
         "A: Yes. All printed notes and practice sets are included in the fee.\n\n"
         "Q: Is there a test series before board exams?\n"
         "A: Yes. Monthly tests, half-yearly mock exams, and full board-pattern mock exams in Feb-March.\n\n"
         "Q: What if my child misses a class?\n"
         "A: Recorded sessions are shared for all missed classes. Make-up classes on Saturdays.",
         "FAQ, questions, materials, tests, mid-year, missed class"),
    ]
    for title, content, tags in notes:
        _run(conn,
             "INSERT INTO notes (node_id, title, content, tags) VALUES (?, ?, ?, ?)",
             ("coaching_excel", title, content, tags))

    menus = [
        ("Register for Demo Class", "task", None, "/register", 1),
        ("Courses & Fees", "task", None, "/ask", 2),
        ("Batch Timings", "task", None, "/ask", 3),
        ("Our 2024 Results", "task", None, "/ask", 4),
    ]
    for name, mtype, link, task_code, order in menus:
        _run(conn,
             "INSERT INTO menu_items (menu_name, menu_type, link, task_code, sort_order, is_active) "
             "VALUES (?, ?, ?, ?, ?, 1)",
             (name, mtype, link, task_code, order))

    _run(conn,
         "INSERT INTO workflows (workflow_code, description, trigger_command, is_active) "
         "VALUES (?, ?, ?, 1)",
         ("STUDENT_REGISTER", "Student registration with parent email verification", "/register"))
    steps = [
        (1, "collect_input", "Parent's name:", "parent_name"),
        (2, "collect_input", "Student's name:", "student_name"),
        (3, "collect_input", "Which class is your child currently in? (6, 7, 8, 9, or 10)", "student_class"),
        (4, "collect_input",
         "Which subject(s) are you interested in?\n"
         "  Maths / Science / Maths+Science / Full Package (9-10 only)", "subjects"),
        (5, "collect_input",
         "Preferred batch timing:\n  Morning (7-9 AM) / Afternoon (2-4 PM) / Evening (5-7 PM) / Weekend",
         "timing"),
        (6, "collect_input", "Parent's email address for confirmation:", "email"),
        (7, "send_otp",      None, None),
        (8, "validate_otp",  "Enter the 6-digit code sent to your email:", None),
        (9, "show_message",
         "Registration complete for {student_name} (Class {student_class})! "
         "Our team will call {parent_name} within 24 hours to schedule a FREE demo class. "
         "Demo classes run every Saturday at 10 AM.", None),
    ]
    for order, stype, prompt, field in steps:
        _run(conn,
             "INSERT INTO workflow_steps (workflow_code, step_order, step_type, step_prompt, field_name) "
             "VALUES (?, ?, ?, ?, ?)",
             ("STUDENT_REGISTER", order, stype, prompt, field))

    queries = [
        ("444444441", "parent_ravi", "How much for Class 10 Maths coaching?",
         "Class 10 Maths coaching is Rs 2,000/month. Full Package (Maths + Science + English) is Rs 4,500/month."),
        ("444444442", "mom_priya", "When is the next demo class?",
         "Demo classes are held every Saturday at 10 AM. Register via /register to book your spot."),
        ("444444443", "worried_parent", "My daughter is weak in Maths. Can she join Class 9?",
         "Absolutely. We assess students at the time of joining and provide extra support. "
         "Many students improve by 40+ marks in 6 months."),
        ("444444444", "results_query", "What were your 2024 board results?",
         "12 students scored above 90% in Maths (Class 10 CBSE) and 4 students scored 100/100!"),
        ("444444445", "timing_q", "Are evening batches available for Class 8?",
         "Yes! Evening batch (5 PM to 7 PM, Mon-Sat) is available for all classes including Class 8."),
        ("444444446", "online_q", "Do you have online classes?",
         "Yes, online batches are available for outstation students. Contact us for details."),
    ]
    for tg_id, username, query, response in queries:
        _run(conn,
             "INSERT INTO query_logs (telegram_id, username, query, response, source) "
             "VALUES (?, ?, ?, ?, 'telegram')",
             (tg_id, username, query, response))

    leads = [
        ("Pradeep Sharma", "Parent (Child: Class 9)", "@parent_pradeep / 9111000001",
         "Interested in Class 9 Full Package. Wants evening batch. Needs demo class first.",
         "telegram", "new"),
        ("Sunita Rao", "Parent (Child: Class 7)", "@sunita_rao / 9111000002",
         "Asking about Maths+Science combo for Class 7. Preferred timing: afternoon.",
         "telegram", "contacted"),
    ]
    for name, biz, contact, summary, source, status in leads:
        _run(conn,
             "INSERT INTO leads (name, business, contact_info, summary, source, status) "
             "VALUES (?, ?, ?, ?, ?, ?)",
             (name, biz, contact, summary, source, status))

    # Scheduled tasks
    task_id = str(uuid.uuid4())
    _run(conn,
         "INSERT INTO scheduled_tasks (id, name, type, schedule_cron, action_json, status) "
         "VALUES (?, ?, ?, ?, ?, 'active')",
         (task_id, "Monthly Result Newsletter", "http",
          "0 9 1 * *",
          json.dumps({"url": "http://localhost:7777/api/digest/send", "method": "POST",
                      "note": "Send monthly performance summary to parents"})))

    conn.commit()
    print("  Coaching: 6 notes, 4 menus, 1 workflow (9 steps), 6 query logs, 2 leads, 1 task.")


# ---------------------------------------------------------------------------
# Scenario 5 — TechEase IT Services, Pune
# ---------------------------------------------------------------------------

def seed_itservices(conn):
    print("Seeding Scenario 5: TechEase IT Services, Pune...")

    notes = [
        ("About TechEase IT Services",
         "TechEase IT Services is a managed IT support company based in Pune. "
         "Founded by Vikrant Desai in 2017. We serve 30+ SME clients with "
         "on-site and remote IT support, network setup, CCTV installation, and cloud solutions.",
         "about, IT support, Pune, TechEase, managed services"),

        ("Services and Pricing",
         "COMPUTER / LAPTOP REPAIR\n"
         "  Rs 300 visiting charge + cost of parts\n"
         "  Screen replacement: Rs 2,500-4,500 (model-dependent)\n"
         "  Battery replacement: Rs 1,800\n"
         "  Keyboard replacement: Rs 1,200\n\n"
         "NETWORKING SETUP (up to 5 workstations)\n"
         "  Rs 2,500 + Rs 300 per additional workstation\n\n"
         "CCTV INSTALLATION\n"
         "  Rs 1,500 per camera (labour) + camera cost\n"
         "  DVR setup and configuration: Rs 1,500\n\n"
         "REMOTE SUPPORT (TeamViewer / AnyDesk)\n"
         "  Rs 500 per hour\n\n"
         "ANNUAL MAINTENANCE CONTRACT (AMC)\n"
         "  Rs 8,000/year — unlimited on-site visits\n"
         "  Includes: priority response, preventive maintenance, hardware check quarterly",
         "services, pricing, repair, network, CCTV, AMC, remote support"),

        ("Response Time and SLA",
         "Emergency (server down, network failure, cannot work): 2-hour response\n"
         "Normal service request (non-urgent): next business day\n"
         "AMC clients: same-day priority for all requests\n"
         "Remote support: within 30 minutes during business hours\n\n"
         "Business hours: Monday to Saturday, 9 AM to 7 PM\n"
         "Emergency after-hours line (AMC clients only): 9988776655",
         "response time, SLA, emergency, AMC, hours"),

        ("Self-Help Guide — Common Issues",
         "SLOW COMPUTER\n"
         "  1. Restart the computer first.\n"
         "  2. Check disk space: go to This PC → right-click C: drive → Properties.\n"
         "     If free space is below 5 GB, disk is full. Call us.\n"
         "  3. Check for Windows updates running in background.\n\n"
         "WI-FI DROPS FREQUENTLY\n"
         "  1. Restart your router: unplug power for 30 seconds, plug back in.\n"
         "  2. Still dropping? Check if other devices also drop — if yes, issue is with router/ISP.\n"
         "  3. If only one computer drops: the Wi-Fi adapter may need replacement.\n\n"
         "PRINTER NOT FOUND\n"
         "  1. Turn off printer and computer. Wait 30 seconds. Turn both on again.\n"
         "  2. Ensure both are on the same Wi-Fi network.\n"
         "  3. Check printer has paper and ink/toner.\n"
         "  4. Still not found? Call us.",
         "self-help, troubleshoot, slow computer, WiFi, printer, common issues"),

        ("Annual Maintenance Contract (AMC) Details",
         "AMC Plan — Rs 8,000 per year per business location\n\n"
         "What is included:\n"
         "  - Unlimited on-site visits during business hours\n"
         "  - Same-day priority response\n"
         "  - Quarterly preventive maintenance (hardware check, cleaning, software updates)\n"
         "  - Free remote support (unlimited hours)\n"
         "  - 10% discount on all parts and upgrades\n\n"
         "What is not included:\n"
         "  - Cost of replacement hardware/parts\n"
         "  - After-hours emergency visits (Rs 1,000 surcharge)\n\n"
         "Payment: annual advance. Renewal reminder sent 30 days before expiry.",
         "AMC, maintenance contract, unlimited visits, pricing, plan"),

        ("Who We Serve",
         "TechEase provides IT support for registered business clients.\n"
         "We do not serve individual home users directly.\n\n"
         "Our typical clients: retail shops, CA firms, clinics, small manufacturing units, "
         "educational institutions, restaurants with POS systems.\n\n"
         "New business enquiries: call +91-9988776655 (Mon-Sat, 9 AM to 7 PM) "
         "or raise a service request via /service",
         "who we serve, clients, businesses, new client, enquiry"),
    ]
    for title, content, tags in notes:
        _run(conn,
             "INSERT INTO notes (node_id, title, content, tags) VALUES (?, ?, ?, ?)",
             ("it_techease", title, content, tags))

    menus = [
        ("Raise Service Request", "task", None, "/service", 1),
        ("Self-Help Troubleshooting", "task", None, "/ask", 2),
        ("AMC Plans", "task", None, "/ask", 3),
        ("Emergency Line", "web_link", "tel:+919988776655", None, 4),
    ]
    for name, mtype, link, task_code, order in menus:
        _run(conn,
             "INSERT INTO menu_items (menu_name, menu_type, link, task_code, sort_order, is_active) "
             "VALUES (?, ?, ?, ?, ?, 1)",
             (name, mtype, link, task_code, order))

    _run(conn,
         "INSERT INTO workflows (workflow_code, description, trigger_command, is_active) "
         "VALUES (?, ?, ?, 1)",
         ("IT_SERVICE_REQUEST",
          "Client IT service request with verified email for ticket confirmation",
          "/service"))
    steps = [
        (1, "collect_input", "Your name and company name:", "contact"),
        (2, "collect_input", "Describe the problem in detail:", "problem"),
        (3, "collect_input",
         "How urgent is this?\n  Critical (work is completely stopped) / Normal (can wait until tomorrow)",
         "urgency"),
        (4, "collect_input", "Your office address:", "address"),
        (5, "collect_input", "Best phone number to call:", "phone"),
        (6, "collect_input", "Your email for ticket confirmation:", "email"),
        (7, "send_otp",      None, None),
        (8, "validate_otp",  "Enter the 6-digit code from your email:", None),
        (9, "show_message",
         "Service request logged! A TechEase technician will contact {contact} at {phone}. "
         "Critical (work-stopped) requests are attended within 2 hours.", None),
    ]
    for order, stype, prompt, field in steps:
        _run(conn,
             "INSERT INTO workflow_steps (workflow_code, step_order, step_type, step_prompt, field_name) "
             "VALUES (?, ?, ?, ?, ?)",
             ("IT_SERVICE_REQUEST", order, stype, prompt, field))

    queries = [
        ("555555551", "ca_firm_rajesh", "My computer is very slow today. What should I do?",
         "First try restarting the computer. Then check if your C: drive has less than 5 GB free space. "
         "If still slow, raise a service request with /service."),
        ("555555552", "shop_owner_v", "The Wi-Fi keeps disconnecting every few minutes.",
         "Try restarting your router: unplug for 30 seconds, then plug back in. "
         "If multiple devices are affected, the issue may be with your ISP."),
        ("555555553", "clinic_admin", "How much is your AMC plan?",
         "Our Annual Maintenance Contract is Rs 8,000/year per location. "
         "It includes unlimited on-site visits, same-day priority, and quarterly maintenance."),
        ("555555554", "new_client_q", "We are a new restaurant wanting IT support. Can you help?",
         "We work with restaurants and POS systems. Please call +91-9988776655 or raise a request via /service."),
        ("555555555", "amc_client_urgent", "Server is down! Entire office cannot work!",
         "This is critical. Please raise an emergency service request immediately via /service. "
         "AMC clients get 2-hour emergency response."),
        ("555555556", "printer_q", "Network printer not found on one computer.",
         "Try: restart both the printer and the computer. Ensure both are on the same Wi-Fi/LAN. "
         "If the problem persists, raise a service request."),
    ]
    for tg_id, username, query, response in queries:
        _run(conn,
             "INSERT INTO query_logs (telegram_id, username, query, response, source) "
             "VALUES (?, ?, ?, ?, 'telegram')",
             (tg_id, username, query, response))

    leads = [
        ("Anita Kulkarni", "Kulkarni CA Associates", "@anita_ca / anita@kulkarnica.com",
         "Interested in AMC plan for 5-computer office. Wants quarterly check included. Budget: Rs 8-10K.",
         "telegram", "new"),
        ("Sanjay Ghodke", "Ghodke Retail Store", "@sanjay_retail / 9222000003",
         "Needs CCTV installation for 4 cameras + network setup. Requesting site visit.",
         "telegram", "contacted"),
    ]
    for name, biz, contact, summary, source, status in leads:
        _run(conn,
             "INSERT INTO leads (name, business, contact_info, summary, source, status) "
             "VALUES (?, ?, ?, ?, ?, ?)",
             (name, biz, contact, summary, source, status))

    # Scheduled tasks
    health_id = str(uuid.uuid4())
    _run(conn,
         "INSERT INTO scheduled_tasks (id, name, type, schedule_cron, action_json, status) "
         "VALUES (?, ?, ?, ?, ?, 'active')",
         (health_id, "TechEase Website - Health Check", "http",
          "*/15 * * * *",
          json.dumps({"url": "https://techease.in/health", "method": "GET"})))

    weekly_id = str(uuid.uuid4())
    _run(conn,
         "INSERT INTO scheduled_tasks (id, name, type, schedule_cron, action_json, status) "
         "VALUES (?, ?, ?, ?, ?, 'active')",
         (weekly_id, "Weekly Service Summary to Owner", "http",
          "0 18 * * 5",
          json.dumps({"url": "http://localhost:7777/api/digest/send", "method": "POST",
                      "note": "Friday 6 PM — weekly service ticket summary"})))

    amc_id = str(uuid.uuid4())
    _run(conn,
         "INSERT INTO scheduled_tasks (id, name, type, schedule_cron, action_json, status) "
         "VALUES (?, ?, ?, ?, ?, 'active')",
         (amc_id, "AMC Renewal Reminder (1st of Month)", "http",
          "0 9 1 * *",
          json.dumps({"url": "http://localhost:7777/api/digest/send", "method": "POST",
                      "note": "Check AMC clients due for renewal this month"})))

    conn.commit()
    print("  IT Services: 6 notes, 4 menus, 1 workflow (9 steps), 6 query logs, 2 leads, 3 tasks.")


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def main():
    print("=" * 60)
    print("BizNode Sample Data Seeder")
    print("=" * 60)

    # Initialise DB (creates tables if missing)
    db.init_db()

    conn = db.get_connection()
    try:
        clear_seed_data(conn)
        print()
        seed_clinic(conn)
        seed_designer(conn)
        seed_tiffin(conn)
        seed_coaching(conn)
        seed_itservices(conn)

        # Summary
        print()
        print("=" * 60)
        cursor = conn.cursor()
        for tbl in ["notes", "menu_items", "workflows", "workflow_steps",
                    "query_logs", "scheduled_tasks", "leads", "associates"]:
            cursor.execute(f"SELECT COUNT(*) FROM {tbl}")
            count = cursor.fetchone()[0]
            print(f"  {tbl:<20} {count:>4} rows")
        print("=" * 60)
        print("Seeding complete! Start the dashboard and explore the data.")
        print("  python run_dashboard.py  ->  http://localhost:7777")
        print("=" * 60)
    finally:
        conn.close()


if __name__ == "__main__":
    main()
