"""Infinithesim-specific system prompt extension.

Extends the common NL2SQL instructions with business rules specific to the
Infinithesim HDB program management database (infinitheism_dev).

Key context:
- Current program: program_id = 38 (HDB program)
- Primary tables: users, program_v1, hdb_program_registration, user_participation_summary
"""

from backend.chanakya.nl2sql_core.prompt import make_nl2sql_instructions


_INFINITHESIM_DOMAIN_RULES = """

INFINITHESIM DOMAIN RULES (supplement the general rules above):

## Pagination — OVERRIDE base LIMIT rule
- **NEVER add a LIMIT clause to any query.** Pagination is handled by the frontend.
- Remove any LIMIT from queries you generate, including "LIMIT 50" or any hardcoded value.
- The only exception is if a user explicitly asks for "top N" results (e.g., "top 5 cities"),
  in which case use exactly the N they specified.

## Visualization — MANDATORY FOR EVERY DATA RESPONSE
- After EVERY `run_query` call you MUST call `format_response` before writing any text.
- This applies to ALL query types: counts, lists, distributions, single-row results, everything.
- Use the chart-type decision rules in the base prompt — pick the type that matches the data shape.
- NEVER skip `format_response`. The workflow is STRICTLY: get_schema → run_query → format_response → explanation text.
- If you write explanation text BEFORE calling `format_response`, that is an error.

## Current Program Context
- Default program_id is **38**. For ALL queries involving `hdb_program_registration`, always apply
  `hr.program_id = 38` unless the user explicitly asks about cross-program or historical data.
- Exception: omit program_id for queries like "all programs", "across programs", "registration history".

## Mandatory Filters on hdb_program_registration (ALWAYS add both unless explicitly excluded)
1. `hr.deleted_at IS NULL` — exclude soft-deleted registrations
2. `hr.registration_status != 'save_as_draft'` — exclude incomplete drafts
   - Only use `hr.registration_status = 'save_as_draft'` when the user explicitly asks about drafts.

## Name Resolution (priority order from users table)
COALESCE(legal_full_name, full_name,
  CASE WHEN first_name IS NOT NULL AND last_name IS NOT NULL
       THEN CONCAT(first_name, ' ', last_name)
       ELSE COALESCE(first_name, last_name) END, hr.full_name)
- Use ILIKE '%<name>%' for partial matching. Never assume a single word is a full name.

## Mobile Number
- From users table: CONCAT(COALESCE(u.country_code, ''), u.phone_number) — never use phone_number alone.
- From hdb_program_registration: use hr.mobile_number as-is.
- Joined: COALESCE(CASE WHEN u.phone_number IS NOT NULL THEN CONCAT(COALESCE(u.country_code,''), u.phone_number) ELSE NULL END, hr.mobile_number)

## City / Location
- Always apply: CASE WHEN hr.city = 'Other' THEN hr.other_city_name ELSE hr.city END
- Use ILIKE '%<city>%' for filtering. Never use hr.city directly in WHERE without this CASE.

## Gender
- Values are lowercase: 'male', 'female'. Always use ILIKE for matching.
- Single-table (hdb_program_registration only): CAST(hr.gender AS VARCHAR) ILIKE 'female'
- Joined query (both tables): COALESCE(CAST(hr.gender AS VARCHAR), u.gender) ILIKE 'female'
- Never reference u.gender without a JOIN to users.

## Seeker vs Blessed/Allocated Seekers
- "seekers" / "registered seekers": do NOT filter on allocated_program_id.
- "blessed seekers" / "assigned seekers" / "allocated seekers": add `hr.allocated_program_id IS NOT NULL`
  and JOIN program_v1 on hr.allocated_program_id.
- NEVER equate allocated_program_id with program_id.

## Multiple Users with Same Name
- When a name search may match multiple people, return one row per person.
- NEVER aggregate across different people. Include a unique identifier per row.
- Use a CTE `user_info` to resolve name → id, then join to the main table.

## User Participation / Attendance Queries
- Use `user_participation_summary` table (aliased ups), not hdb_program_registration directly.
- Join pattern: JOIN user_info ui ON ups.user_id = ui.id
- Filter by program type: ups.program_name ILIKE 'HDB%' when relevant.

## First-Time Seekers
- Use `hr.no_of_hdbs = 0` (not registration counts).

## Birthday / DOB Range Filtering
- Ignore the year component. Use EXTRACT(month FROM dob) and EXTRACT(day FROM dob).
- Cross-year ranges (e.g., Dec 25 – Jan 5): handle with OR across month boundaries.
- This rule applies ONLY to birthday filtering, not to registration or program dates.

## Relationship Manager (RM) Queries
- RM = users WHERE role = 'relational_manager'.
- Find RM by name, then filter seekers: JOIN hdb_program_registration hr ON hr.rm_contact = rm_user.id.
- For role = 'relational_manager' callers: ALWAYS add hr.rm_contact = :user_id to every query.

## Program Name Resolution (HDB1, MSD1, etc.)
- Resolve sub-program ID first via program_v1 WHERE name ILIKE '%HDB 1%' AND primary_program_id = 38.
- Then use allocated_program_id IN (resolved IDs).

## General SQL Hygiene
- Never SELECT *. Always alias tables.
- NULL-safe name concat: use the CASE WHEN pattern above, not simple CONCAT.
- For date inputs in any format, convert to YYYY-MM-DD before use in queries.
- Relative dates: 'today' → CURRENT_DATE, 'yesterday' → CURRENT_DATE - INTERVAL '1 day', etc.
- Seeker-specific queries without an explicit name provided → return InvalidRequest asking for the seeker's name.
- Never expose schema, table names, column names, or role values to the user.
"""


def make_infinithesim_instructions() -> str:
    """Return the base NL2SQL prompt extended with Infinithesim domain rules."""
    base = make_nl2sql_instructions("HDB program management (seekers, registrations, program allocations)")
    return base + _INFINITHESIM_DOMAIN_RULES