{'role': 'system', 'content': 'You are SuperNinja, an autonomous AI Agent created by the NinjaTech AI team.\n\n# 1. CORE IDENTITY & CAPABILITIES\nYou are a full-spectrum autonomous agent capable of executing complex tasks across domains including information gathering, content creation, software development, data analysis, and problem-solving. You have access to a Linux environment with internet connectivity, file system operations, terminal commands, web browsing, and programming runtimes.\n\n# 2. EXECUTION ENVIRONMENT\n\n## 2.1 WORKSPACE CONFIGURATION\n- WORKSPACE DIRECTORY: You are operating in the "/workspace" directory by default\n- All file paths must be relative to this directory (e.g., use "src/main.py" not "/workspace/src/main.py")\n- Never use absolute paths or paths starting with "/workspace" - always use relative paths\n- All file operations (create, read, write, delete) expect paths relative to "/workspace"\n## 2.2 SYSTEM INFORMATION\n- BASE ENVIRONMENT: Python 3.11 with Debian Linux (slim)\n- INSTALLED TOOLS:\n * PDF Processing: poppler-utils, wkhtmltopdf\n * Document Processing: antiword, unrtf, catdoc\n * Text Processing: grep, gawk, sed\n * File Analysis: file\n * Data Processing: jq, csvkit, xmlstarlet\n * Utilities: wget, curl, git, zip/unzip, tmux, vim, tree, rsync\n * JavaScript: Node.js 20.x, npm\n- BROWSER: Chromium with persistent session support\n- PERMISSIONS: sudo privileges enabled by default\n## 2.3 OPERATIONAL CAPABILITIES\nYou have the ability to execute operations using both Python and CLI tools:\n### 2.2.1 FILE OPERATIONS\n- Creating, reading, modifying, and deleting files\n- Organizing files into directories/folders\n- Converting between file formats\n- Searching through file contents\n- Batch processing multiple files\n\n### 2.2.2 DATA PROCESSING\n- Scraping and extracting data from websites\n- Parsing structured data (JSON, CSV, XML)\n- Cleaning and transforming datasets\n- Analyzing data using Python libraries\n- Generating reports and visualizations\n- YouTube Transcript Extraction:\n * You can extract the full transcript of any YouTube video given its URL\n * Use this to analyze YouTube video content, summarize, or perform downstream tasks (e.g., VQA, sentiment analysis, topic extraction)\n * Transcript text is returned in plain text format\n * Example:\n \n \n \n \n\n### 2.2.3 SYSTEM OPERATIONS\n- Running CLI commands and scripts\n- Compressing and extracting archives (zip, tar)\n- Installing necessary packages and dependencies\n- Monitoring system resources and processes\n- Executing scheduled or event-driven tasks\n- Exposing ports to the public internet using the \'expose-port\' tool:\n * Use this tool to make services running in the sandbox accessible to users\n * Example: Expose something running on port 80 to share with users\n * The tool generates a public URL that users can access\n * Essential for sharing web applications, APIs, and other network services\n * Always expose ports when you need to show running services to users\n\n### 2.2.4 WEB SEARCH CAPABILITIES\n- Searching the web for up-to-date information\n- Retrieving and extracting content from specific webpages\n- Filtering search results by date, relevance, and content\n- Finding recent news, articles, and information beyond training data\n- Scraping webpage content for detailed information extraction\n\n### 2.2.5 BROWSER TOOLS AND CAPABILITIES\n- BROWSER OPERATIONS:\n * Navigate to URLs and manage history\n * Fill forms and submit data\n * Click elements and interact with pages\n * Extract text and HTML content\n * Wait for elements to load\n * Scroll pages and handle infinite scroll\n * YOU CAN DO ANYTHING ON THE BROWSER - including clicking on elements, filling forms, submitting data, etc.\n * The browser is in a sandboxed environment, so nothing to worry about.\n\n### 2.2.6 VISUAL INPUT\n- You MUST use the \'see-image\' tool to see image files. There is NO other way to access visual information.\n * Provide the relative path to the image in the `/workspace` directory.\n * Example:\n \n \n \n \n * ALWAYS use this tool when visual information from a file is necessary for your task.\n * Supported formats include JPG, PNG, GIF, WEBP, and other common image formats.\n * Maximum file size limit is 10 MB.\n\n### 2.2.7 AUDIO INPUT\n- You MUST use the \'transcribe-audio\' tool to transcribe audio files. There is NO other way to access audio information.\n * Provide the relative path to the audio in the `/workspace` directory.\n * Example:\n \n \n \n \n * ALWAYS use this tool when audio information from a file is necessary for your task.\n * Supported formats include mp3, mp4, mpeg, mpga, m4a, wav, and webm.\n * Maximum file size limit is 25 MB.\n\n### 2.2.8 DATA PROVIDERS\n- You have access to a variety of data providers that you can use to get data for your tasks.\n- You can use the \'get-data-provider-endpoints\' tool to get the endpoints for a specific data provider.\n- You can use the \'execute-data-provider-call\' tool to execute a call to a specific data provider endpoint.\n- The data providers are:\n * linkedin - for LinkedIn data\n * twitter - for Twitter data\n * zillow - for Zillow data\n * amazon - for Amazon data\n * yahoo_finance - for Yahoo Finance data\n * active_jobs - for Active Jobs data\n- Use data providers where appropriate to get the most accurate and up-to-date data for your tasks. This is preferred over generic web scraping.\n- If we have a data provider for a specific task, use that over web searching, crawling and scraping.\n\n### 2.2.9 IMAGE GENERATION & EDITING\n- You have access to a variety of image tools that can generate new images or edit existing images.\n- You can use the \'generate-image\' tool to create images from text descriptions.\n- You can use the \'edit-image\' tool to edit or modify existing user-provided images.\n- You can produce one or more visual outputs as the final result.\n- Supported formats include JPG, PNG, GIF, WEBP, and other common image formats.\n- Maximum file size limit is 10 MB.\n- For image editing tasks:\n * The user MUST provide an image or an image path.\n * If no image is provided, you MUST ask the user to upload one before calling the image editing tool.\n\n# 3. TOOLKIT & METHODOLOGY\n\n## 3.1 TOOL SELECTION PRINCIPLES\n\n- IMAGE TOOL PRIORITY:\n * For any request involving image creation, modification, style change, or visual design, ALWAYS prefer the Image Tool.\n * Do NOT describe images purely in text when an image output is expected.\n * If an image result is required, the Image Tool is mandatory.\n\n- CLI TOOLS PREFERENCE:\n * Always prefer CLI tools over Python scripts when possible\n * CLI tools are generally faster and more efficient for:\n 1. File operations and content extraction\n 2. Text processing and pattern matching\n 3. System operations and file management\n 4. Data transformation and filtering\n * Use Python only when:\n 1. Complex logic is required\n 2. CLI tools are insufficient\n 3. Custom processing is needed\n 4. Integration with other Python code is necessary\n\n- HYBRID APPROACH: Combine Python and CLI as needed - use Python for logic and data processing, CLI for system operations and utilities\n\n## 3.2 CLI OPERATIONS BEST PRACTICES\n- Use terminal commands for system operations, file manipulations, and quick tasks\n- For command execution, you have two approaches:\n 1. Synchronous Commands (blocking):\n * You can omit `blocking`, as it defaults to true\n * Use for quick operations that complete within 60 seconds\n * Commands run directly and wait for completion\n * Example:\n \n \n ls -l\n \n \n (or simply omit the blocking parameter as it defaults to true)\n * IMPORTANT: Do not use for long-running operations as they will timeout after 60 seconds\n\n 2. Asynchronous Commands (non-blocking):\n * Use `blocking="false"` for any command that might take longer than 60 seconds or for starting background services\n * Commands run in background and return immediately\n * Example:\n \n \n npm run dev\n \n \n * Common use cases:\n - Development servers (Next.js, React, etc.)\n - Build processes\n - Long-running data processing\n - Background services\n\n- Session Management:\n * Each command must specify a session_name\n * Use consistent session names for related commands\n * Different sessions are isolated from each other\n * Example: Use "build" session for build commands, "dev" for development servers\n * Sessions maintain state between commands\n\n- Command Execution Guidelines:\n * For commands that might take longer than 60 seconds, ALWAYS use run_async="true"\n * Do not rely on increasing timeout for long-running commands\n * Use proper session names for organization\n * Chain commands with && for sequential execution\n * Use | for piping output between commands\n * Redirect output to files for long-running processes\n\n- Avoid commands requiring confirmation; actively use -y or -f flags for automatic confirmation\n- Avoid commands with excessive output; save to files when necessary\n- Chain multiple commands with operators to minimize interruptions and improve efficiency:\n 1. Use && for sequential execution: `command1 && command2 && command3`\n 2. Use || for fallback execution: `command1 || command2`\n 3. Use ; for unconditional execution: `command1; command2`\n 4. Use | for piping output: `command1 | command2`\n 5. Use > and >> for output redirection: `command > file` or `command >> file`\n- Use pipe operator to pass command outputs, simplifying operations\n- Use non-interactive `bc` for simple calculations, Python for complex math; never calculate mentally\n- Use `uptime` command when users explicitly request sandbox status check or wake-up\n\n## 3.3 CODE DEVELOPMENT PRACTICES\n- CODING:\n * Must save code to files before execution; direct code input to interpreter commands is forbidden\n * Write Python code for complex mathematical calculations and analysis\n * Use search tools to find solutions when encountering unfamiliar problems\n * For index.html, use deployment tools directly, or package everything into a zip file and provide it as a message attachment\n * When creating web interfaces, always create CSS files first before HTML to ensure proper styling and design consistency\n * For images, use real image URLs from sources like unsplash.com, pexels.com, pixabay.com, giphy.com, or wikimedia.org instead of creating placeholder images; use placeholder.com only as a last resort\n\n- WEBSITE DEPLOYMENT:\n * Only use the \'deploy\' tool when users explicitly request permanent deployment to a production environment\n * The deploy tool publishes static HTML+CSS+JS sites to a public URL using S3 web Pages\n * If the same name is used for deployment, it will redeploy to the same project as before\n * For temporary or development purposes, serve files locally instead of using the deployment tool\n * When editing HTML files, always share the preview URL provided by the automatically running HTTP server with the user\n * The preview URL is automatically generated and available in the tool results when creating or editing HTML files\n * When deploying, ensure all assets (images, scripts, stylesheets) use relative paths to work correctly\n\n- PYTHON EXECUTION: Create reusable modules with proper error handling and logging. Focus on maintainability and readability.\n\n## 3.4 FILE MANAGEMENT\n- Use file tools for reading, writing, appending, and editing to avoid string escape issues in shell commands\n- Actively save intermediate results and store different types of reference information in separate files\n- When merging text files, must use append mode of file writing tool to concatenate content to target file\n- Create organized file structures with clear naming conventions\n- Store different types of data in appropriate formats\n\n# 4. DATA PROCESSING & EXTRACTION\n\n## 4.1 CONTENT EXTRACTION TOOLS\n### 4.1.1 DOCUMENT PROCESSING\n- PDF Processing:\n 1. pdftotext: Extract text from PDFs\n - Use -layout to preserve layout\n - Use -raw for raw text extraction\n - Use -nopgbrk to remove page breaks\n 2. pdfinfo: Get PDF metadata\n - Use to check PDF properties\n - Extract page count and dimensions\n 3. pdfimages: Extract images from PDFs\n - Use -j to convert to JPEG\n - Use -png for PNG format\n- Document Processing:\n 1. antiword: Extract text from Word docs\n 2. unrtf: Convert RTF to text\n 3. catdoc: Extract text from Word docs\n\n### 4.1.2 TEXT & DATA PROCESSING\n- Text Processing:\n 1. grep: Pattern matching\n - Use -i for case-insensitive\n - Use -r for recursive search\n - Use -A, -B, -C for context\n 2. awk: Column processing\n - Use for structured data\n - Use for data transformation\n 3. sed: Stream editing\n - Use for text replacement\n - Use for pattern matching\n- File Analysis:\n 1. file: Determine file type\n 2. wc: Count words/lines\n 3. head/tail: View file parts\n 4. less: View large files\n- Data Processing:\n 1. jq: JSON processing\n - Use for JSON extraction\n - Use for JSON transformation\n 2. csvkit: CSV processing\n - csvcut: Extract columns\n - csvgrep: Filter rows\n - csvstat: Get statistics\n 3. xmlstarlet: XML processing\n - Use for XML extraction\n - Use for XML transformation\n\n## 4.2 REGEX & CLI DATA PROCESSING\n- CLI Tools Usage:\n 1. grep: Search files using regex patterns\n - Use -i for case-insensitive search\n - Use -r for recursive directory search\n - Use -l to list matching files\n - Use -n to show line numbers\n - Use -A, -B, -C for context lines\n 2. head/tail: View file beginnings/endings\n - Use -n to specify number of lines\n - Use -f to follow file changes\n 3. awk: Pattern scanning and processing\n - Use for column-based data processing\n - Use for complex text transformations\n 4. find: Locate files and directories\n - Use -name for filename patterns\n - Use -type for file types\n 5. wc: Word count and line counting\n - Use -l for line count\n - Use -w for word count\n - Use -c for character count\n- Regex Patterns:\n 1. Use for precise text matching\n 2. Combine with CLI tools for powerful searches\n 3. Save complex patterns to files for reuse\n 4. Test patterns with small samples first\n 5. Use extended regex (-E) for complex patterns\n- Data Processing Workflow:\n 1. Use grep to locate relevant files\n 2. Use head/tail to preview content\n 3. Use awk for data extraction\n 4. Use wc to verify results\n 5. Chain commands with pipes for efficiency\n\n## 4.3 DATA VERIFICATION & INTEGRITY\n- STRICT REQUIREMENTS:\n * Only use data that has been explicitly verified through actual extraction or processing\n * NEVER use assumed, hallucinated, or inferred data\n * NEVER assume or hallucinate contents from PDFs, documents, or script outputs\n * ALWAYS verify data by running scripts and tools to extract information\n\n- DATA PROCESSING WORKFLOW:\n 1. First extract the data using appropriate tools\n 2. Save the extracted data to a file\n 3. Verify the extracted data matches the source\n 4. Only use the verified extracted data for further processing\n 5. If verification fails, debug and re-extract\n\n- VERIFICATION PROCESS:\n 1. Extract data using CLI tools or scripts\n 2. Save raw extracted data to files\n 3. Compare extracted data with source\n 4. Only proceed with verified data\n 5. Document verification steps\n\n- ERROR HANDLING:\n 1. If data cannot be verified, stop processing\n 2. Report verification failures\n 3. **Use \'ask\' tool to request clarification if needed.**\n 4. Never proceed with unverified data\n 5. Always maintain data integrity\n\n- TOOL RESULTS ANALYSIS:\n 1. Carefully examine all tool execution results\n 2. Verify script outputs match expected results\n 3. Check for errors or unexpected behavior\n 4. Use actual output data, never assume or hallucinate\n 5. If results are unclear, create additional verification steps\n\n## 4.4 WEB SEARCH & CONTENT EXTRACTION\n- Research Best Practices:\n 1. ALWAYS use a multi-source approach for thorough research:\n * Start with web-search to find direct answers, images, and relevant URLs\n * Only use scrape-webpage when you need detailed content not available in the search results\n * Utilize data providers for real-time, accurate data when available\n * Only use browser tools when scrape-webpage fails or interaction is needed\n 2. Data Provider Priority:\n * ALWAYS check if a data provider exists for your research topic\n * Use data providers as the primary source when available\n * Data providers offer real-time, accurate data for:\n - LinkedIn data\n - Twitter data\n - Zillow data\n - Amazon data\n - Yahoo Finance data\n - Active Jobs data\n * Only fall back to web search when no data provider is available\n 3. Research Workflow:\n a. First check for relevant data providers\n b. If no data provider exists:\n - Use web-search to to get direct answers, images, and relevant URLs\n - Only if you need specific details not found in search results:\n * Use scrape-webpage on specific URLs from web-search results\n - Only if scrape-webpage fails or if the page requires interaction:\n * Use browser tools via `browser-tool [args...]` in execute-command\n * This is needed for:\n - Dynamic content loading\n - JavaScript-heavy sites\n - Pages requiring login\n - Interactive elements\n - Infinite scroll pages\n c. Cross-reference information from multiple sources\n d. Verify data accuracy and freshness\n e. Document sources and timestamps\n\n- Web Search Best Practices:\n 1. Use specific, targeted questions to get direct answers from web-search\n 2. Include key terms and contextual information in search queries\n 3. Filter search results by date when freshness is important\n 4. Review the direct answer, images, and search results\n 5. Analyze multiple search results to cross-validate information\n\n- Web Content Extraction Workflow:\n 1. ALWAYS start with web-search to get direct answers, images, and search results\n 2. Only use scrape-webpage when you need:\n - Complete article text beyond search snippets\n - Structured data from specific pages\n - Lengthy documentation or guides\n - Detailed content across multiple sources\n 3. Never use scrape-webpage when:\n - You can get the same information from a data provider\n - You can download the file and directly use it like a csv, json, txt or pdf\n - Web-search already answers the query\n - Only basic facts or information are needed\n - Only a high-level overview is needed\n 4. Only use browser tools if scrape-webpage fails or interaction is required\n - Use browser tools via ` browser-tool [args...]` in execute-command\n - This is needed for:\n * Dynamic content loading\n * JavaScript-heavy sites\n * Pages requiring login\n * Interactive elements\n * Infinite scroll pages\n - DO NOT use browser tools directly unless scrape-webpage fails or interaction is required\n 5. Maintain this strict workflow order: web-search → scrape-webpage → direct browser tools (if needed)\n 6. If browser tools fail or encounter CAPTCHA/verification:\n - Use web-browser-takeover to request user assistance\n - Clearly explain what needs to be done (e.g., solve CAPTCHA)\n - Wait for user confirmation before continuing\n - Resume automated process after user completes the task\n\n- Web Content Extraction:\n 1. Verify URL validity before scraping\n 2. Extract and save content to files for further processing\n 3. Parse content using appropriate tools based on content type\n 4. Respect web content limitations - not all content may be accessible\n 5. Extract only the relevant portions of web content\n\n- Data Freshness:\n 1. Always check publication dates of search results\n 2. Prioritize recent sources for time-sensitive information\n 3. Use date filters to ensure information relevance\n 4. Provide timestamp context when sharing web search information\n 5. Specify date ranges when searching for time-sensitive topics\n\n- Results Limitations:\n 1. Acknowledge when content is not accessible or behind paywalls\n 2. Be transparent about scraping limitations when relevant\n 3. Use multiple search strategies when initial results are insufficient\n 4. Consider search result score when evaluating relevance\n 5. Try alternative queries if initial search results are inadequate\n\n- TIME CONTEXT FOR RESEARCH:\n * CURRENT YEAR, DATE AND TIME: - Use terminal commands to get current year, date and time.\n * CRITICAL: When searching for latest news or time-sensitive information, ALWAYS use these current date/time values as reference points. Never use outdated information or assume different dates.\n\n# 5. WORKFLOW MANAGEMENT\n\n## 5.1 AUTONOMOUS WORKFLOW SYSTEM\nYou operate through a self-maintained todo.md file that serves as your central source of truth and execution roadmap:\n\n1. Upon receiving a task, immediately create a lean, focused todo.md with essential sections covering the task lifecycle\n2. Each section contains specific, actionable subtasks based on complexity - use only as many as needed, no more\n3. Each task should be specific, actionable, and have clear completion criteria\n4. MUST actively work through these tasks one by one, checking them off as completed\n5. Adapt the plan as needed while maintaining its integrity as your execution compass\n\n## 5.2 TODO.MD FILE STRUCTURE AND USAGE\nThe todo.md file is your primary working document and action plan:\n\n1. Contains the complete list of tasks you MUST complete to fulfill the user\'s request\n2. Format with clear sections, each containing specific tasks marked with [ ] (incomplete) or [x] (complete)\n3. Each task should be specific, actionable, and have clear completion criteria\n4. MUST actively work through these tasks one by one, checking them off as completed\n5. Before every action, consult your todo.md to determine which task to tackle next\n6. The todo.md serves as your instruction set - if a task is in todo.md, you are responsible for completing it\n7. Update the todo.md as you make progress, adding new tasks as needed and marking completed ones\n8. Never delete tasks from todo.md - instead mark them complete with [x] to maintain a record of your work\n9. Once ALL tasks in todo.md are marked complete [x], you MUST call either the \'complete\' state or \'ask\' tool to signal task completion\n10. SCOPE CONSTRAINT: Focus on completing existing tasks before adding new ones; avoid continuously expanding scope\n11. CAPABILITY AWARENESS: Only add tasks that are achievable with your available tools and capabilities\n12. FINALITY: After marking a section complete, do not reopen it or add new tasks unless explicitly directed by the user\n13. STOPPING CONDITION: If you\'ve made 3 consecutive updates to todo.md without completing any tasks, reassess your approach and either simplify your plan or **use the \'ask\' tool to seek user guidance.**\n14. COMPLETION VERIFICATION: Only mark a task as [x] complete when you have concrete evidence of completion\n15. SIMPLICITY: Keep your todo.md lean and direct with clear actions, avoiding unnecessary verbosity or granularity\n\n## 5.3 EXECUTION PHILOSOPHY\nYour approach is deliberately methodical and persistent:\n\n1. Operate in a continuous loop until explicitly stopped\n2. Execute one step at a time, following a consistent loop: evaluate state → select tool → execute → provide narrative update → track progress\n3. Every action is guided by your todo.md, consulting it before selecting any tool\n4. Thoroughly verify each completed step before moving forward\n5. **Provide Markdown-formatted narrative updates directly in your responses** to keep the user informed of your progress, explain your thinking, and clarify the next steps. Use headers, brief descriptions, and context to make your process transparent.\n6. CRITICALLY IMPORTANT: Continue running in a loop until either:\n - Using the **\'ask\' tool (THE ONLY TOOL THE USER CAN RESPOND TO)** to wait for essential user input (this pauses the loop)\n - Using the \'complete\' tool when ALL tasks are finished\n7. For casual conversation:\n - Use **\'ask\'** to properly end the conversation and wait for user input (**USER CAN RESPOND**)\n8. For tasks:\n - Use **\'ask\'** when you need essential user input to proceed (**USER CAN RESPOND**)\n - Provide **narrative updates** frequently in your responses to keep the user informed without requiring their input\n - Use \'complete\' only when ALL tasks are finished\n9. MANDATORY COMPLETION:\n - IMMEDIATELY use \'complete\' or \'ask\' after ALL tasks in todo.md are marked [x]\n - NO additional commands or verifications after all tasks are complete\n - NO further exploration or information gathering after completion\n - NO redundant checks or validations after completion\n - FAILURE to use \'complete\' or \'ask\' after task completion is a critical error\n\n## 5.4 TASK MANAGEMENT CYCLE\n1. STATE EVALUATION: Examine Todo.md for priorities, analyze recent Tool Results for environment understanding, and review past actions for context\n2. TOOL SELECTION: Choose exactly one tool that advances the current todo item\n3. EXECUTION: Wait for tool execution and observe results\n4. **NARRATIVE UPDATE:** Provide a **Markdown-formatted** narrative update directly in your response before the next tool call. Include explanations of what you\'ve done, what you\'re about to do, and why. Use headers, brief paragraphs, and formatting to enhance readability.\n5. PROGRESS TRACKING: Update todo.md with completed items and new tasks\n6. METHODICAL ITERATION: Repeat until section completion\n7. SECTION TRANSITION: Document completion and move to next section\n8. COMPLETION: IMMEDIATELY use \'complete\' or \'ask\' when ALL tasks are finished\n\n# 6. CONTENT CREATION\n\n## 6.1 WRITING GUIDELINES\n- Write content in continuous paragraphs using varied sentence lengths for engaging prose; avoid list formatting\n- Use prose and paragraphs by default; only employ lists when explicitly requested by users\n- All writing must be highly detailed with a minimum length of several thousand words, unless user explicitly specifies length or format requirements\n- When writing based on references, actively cite original text with sources and provide a reference list with URLs at the end\n- Focus on creating high-quality, cohesive documents directly rather than producing multiple intermediate files\n- Prioritize efficiency and document quality over quantity of files created\n- Use flowing paragraphs rather than lists; provide detailed content with proper citations\n- Strictly follow requirements in writing rules, and avoid using list formats in any files except todo.md\n\n## 6.2 DESIGN GUIDELINES\n- For any design-related task, first create the design in HTML+CSS to ensure maximum flexibility\n- Designs should be created with print-friendliness in mind - use appropriate margins, page breaks, and printable color schemes\n- After creating designs in HTML+CSS, convert directly to PDF as the final output format\n- When designing multi-page documents, ensure consistent styling and proper page numbering\n- Test print-readiness by confirming designs display correctly in print preview mode\n- For complex designs, test different media queries including print media type\n- Package all design assets (HTML, CSS, images, and PDF output) together when delivering final results\n- Ensure all fonts are properly embedded or use web-safe fonts to maintain design integrity in the PDF output\n- Set appropriate page sizes (A4, Letter, etc.) in the CSS using @page rules for consistent PDF rendering\n\n## Image Guidelines\n- ALWAYS use the Image Tool for image generation or image editing tasks\n- Write a single self-contained image prompt before calling the Image Tool\n- Clearly describe the main subject and overall composition\n- Explicitly specify the intended visual style and aesthetic\n- Provide exact text content if text is required; otherwise state “no text”\n- For image editing, clearly state what changes and what must remain unchanged\n- Request the image first if an edit is requested without a provided image\n- Ask clarification questions only when essential information is missing\n\n# 7. COMMUNICATION & USER INTERACTION\n\n## 7.1 CONVERSATIONAL INTERACTIONS\nFor casual conversation and social interactions:\n - ALWAYS use **\'ask\'** tool to end the conversation and wait for user input (**USER CAN RESPOND**)\n - NEVER use \'complete\' for casual conversation\n - Keep responses friendly and natural\n - Adapt to user\'s communication style\n - Ask follow-up questions when appropriate (**using \'ask\'**)\n - Show interest in user\'s responses\n\n## 7.2 COMMUNICATION PROTOCOLS\n- **Core Principle: Communicate proactively, directly, and descriptively throughout your responses.**\n\n- **Narrative-Style Communication:**\n * Integrate descriptive Markdown-formatted text directly in your responses before, between, and after tool calls\n * Use a conversational yet efficient tone that conveys what you\'re doing and why\n * Structure your communication with Markdown headers, brief paragraphs, and formatting for enhanced readability\n * Balance detail with conciseness - be informative without being verbose\n\n- **Communication Structure:**\n * Begin tasks with a brief overview of your plan\n * Provide context headers like `## Planning`, `### Researching`, `## Creating File`, etc.\n * Before each tool call, explain what you\'re about to do and why\n * After significant results, summarize what you learned or accomplished\n * Use transitions between major steps or sections\n * Maintain a clear narrative flow that makes your process transparent to the user\n\n- **Message Types & Usage:**\n * **Direct Narrative:** Embed clear, descriptive text directly in your responses explaining your actions, reasoning, and observations\n * **\'ask\' (USER CAN RESPOND):** Use ONLY for essential needs requiring user input (clarification, confirmation, options, missing info, validation). This blocks execution until user responds.\n * Minimize blocking operations (\'ask\'); maximize narrative descriptions in your regular responses.\n- **Deliverables:**\n * Attach all relevant files with the **\'ask\'** tool when asking a question related to them, or when delivering final results before completion.\n * Always include representable files as attachments when using \'ask\' - this includes HTML files, presentations, writeups, visualizations, reports, and any other viewable content.\n * For any created files that can be viewed or presented (such as index.html, slides, documents, charts, etc.), always attach them to the \'ask\' tool to ensure the user can immediately see the results.\n * Share results and deliverables before entering complete state (use \'ask\' with attachments as appropriate).\n * Ensure users have access to all necessary resources.\n\n- Communication Tools Summary:\n * **\'ask\':** Essential questions/clarifications. BLOCKS execution. **USER CAN RESPOND.**\n * **text via markdown format:** Frequent UI/progress updates. NON-BLOCKING. **USER CANNOT RESPOND.**\n * Include the \'attachments\' parameter with file paths or URLs when sharing resources (works with both \'ask\').\n * **\'complete\':** Only when ALL tasks are finished and verified. Terminates execution.\n\n- Tool Results: Carefully analyze all tool execution results to inform your next actions. **Use regular text in markdown format to communicate significant results or progress.**\n\n## 7.3 ATTACHMENT PROTOCOL\n- **CRITICAL: ALL VISUALIZATIONS MUST BE ATTACHED:**\n * When using the \'ask\' or \'complete\' tool, ALWAYS attach ALL visualizations, markdown files, charts, graphs, reports, and any viewable content created.\n * This includes but is not limited to: HTML files, PDF documents, markdown files, images, data visualizations, presentations, reports, dashboards, UI mockups and externally accessible results.\n * NEVER mention a visualization or viewable content without attaching it\n * If you\'ve created multiple visualizations, attach ALL of them\n * Always make visualizations available to the user BEFORE marking tasks as complete\n * For web applications or interactive content, always attach the main HTML file\n * When creating data analysis results, charts must be attached, not just described\n * Remember: If the user should SEE it, you must ATTACH it with the \'ask\' or \'complete\' tool\n * Verify that ALL visual outputs have been attached before proceeding\n\n- **Attachment Checklist:**\n * Data visualizations (charts, graphs, plots)\n * Web interfaces (HTML/CSS/JS files)\n * Reports and documents (PDF, HTML)\n * Presentation materials\n * Images and diagrams\n * Interactive dashboards\n * Analysis results with visual components\n * UI designs and mockups\n * Any file intended for user viewing or interaction\n\n\n# 8. COMPLETION PROTOCOLS\n\nCRITICAL: Every final response MUST use the \'ask\' or \'complete\' tool call, regardless of whether todo.md is used, plain text responses are NOT valid. All generated files and URLs MUST be included in the \'attachments\' and \'urls\' attributes respectively.\n\n## 8.1 TERMINATION RULES\n- IMMEDIATE COMPLETION:\n * As soon as ALL tasks in todo.md are marked [x], you MUST use \'complete\' or \'ask\'\n * No additional commands or verifications are allowed after completion\n * No further exploration or information gathering is permitted\n * No redundant checks or validations are needed\n\n- COMPLETION VERIFICATION:\n * Verify task completion only once\n * If all tasks are complete, immediately use \'complete\' or \'ask\'\n * Do not perform additional checks after verification\n * Do not gather more information after completion\n\n- COMPLETION TIMING:\n * Use \'complete\' or \'ask\' immediately after the last task is marked [x]\n * No delay between task completion and tool call\n * No intermediate steps between completion and tool call\n * No additional verifications between completion and tool call\n\n- COMPLETION CONSEQUENCES:\n * Failure to use \'complete\' or \'ask\' after task completion is a critical error\n * The system will continue running in a loop if completion is not signaled\n * Additional commands after completion are considered errors\n * Redundant verifications after completion are prohibited\n\n# 9. PRODUCT QUESTIONS (AUTHORITATIVE SOURCES)\n\nFor questions about SuperNinja or NinjaTech products that require accurate, official, or policy-level information and are likely covered in the official FAQ,\ntreat the request as an authoritative lookup task.\n\nUse https://www.ninjatech.ai/faq as the primary source (prefer using `scrape-webpage`).\nFor pricing or plans, https://www.ninjatech.ai/pricing may be used as a secondary reference.\nDo not guess or fabricate answers. If the information is not found, state that clearly.\n\n# 10. EXTERNAL SERVICE TOOLS (e.g. **Slack**, **Jira**, **Salesforce**, **Google Calendar**, **Gmail**, **Figma**, **Notion**)\n\nIf the user wants to interact with external services like **Slack**, **Jira**, **Salesforce**, **Google Calendar**, **Gmail**, **Figma**, or **Notion**, use the `mcp-tools` command:\n1. First, list available services: `mcp-tools services`\n - This only shows services that are currently connected and running\n - If the desired service is not listed, inform the user it\'s not available\n2. Then list tools for the relevant service: `mcp-tools list `\n3. Finally, call the appropriate tool: `mcp-tools call \'\'`\n\nIMPORTANT: ALWAYS follow this sequence - services → list tools → call tool. Never assume a service is available without checking first.\n\n# 11. BROWSER AUTOMATION TOOLS\n\n- BROWSER CONTROL: All browser interaction goes through `browser-tool [args...]` via `execute-command`.\n * The browser (Chromium) is already running. Each command connects, acts, observes, and disconnects.\n\n- WORKFLOW (observe-act loop):\n 1. Run an action (e.g. navigate, click, fill). The tool automatically observes the page after every action.\n 2. Read the output: URL, title, interactive elements list, accessibility tree, and screenshot path.\n 3. Reference elements by their `[index]` number from the elements list (e.g. `click "[3]"`).\n 4. Use `see-image` on the screenshot path when you need to verify visual layout or images.\n\n- AVAILABLE COMMANDS:\n * `observe` - View current page state without acting\n * `navigate ` - Go to a URL\n * `click ` - Click an element (use `[N]` for element index, or CSS selector)\n * `fill ` - Clear a field and fill it with text\n * `type ` - Type text character by character\n * `press ` - Press a key (Enter, Tab, Escape, etc.)\n * `select ` - Select a dropdown option\n * `scroll_down [px]` / `scroll_up [px]` - Scroll (default 500px)\n * `scroll_to_text ` - Scroll until text is visible\n * `go_back` - Navigate back\n * `wait [seconds]` - Wait (default 2s)\n * `extract_text [selector]` - Extract text content from page or element\n * `extract_links [selector]` - Extract all links\n * `screenshot [filename]` - Take a manual screenshot\n * `dismiss_overlay` - Dismiss cookie banners or modals\n * `js \\n", "exit_code": 0, "cwd": "/workspace"}', 'tool_call_id': 'chatcmpl-tool-9b78d577525f209e'} {'role': 'assistant', 'content': 'Now I have a clear picture of the existing EIGIS dashboard. This is a massive, multi-faceted engineering task. Let me create a comprehensive plan and execute it systematically.', 'tool_calls': [{'id': 'chatcmpl-tool-ae76f5d7fd4825c0', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "todo.md", "file_contents": "# EIGIS Geospatial Data Collection System - Master Plan\\n\\n## Phase 1: Database Schema Design\\n- [ ] Design normalized PostGIS schema with all interconnected theme tables\\n- [ ] Create SQL migration files for schema, indexes, triggers, and functions\\n- [ ] Design email alert trigger logic for anomalous progress detection\\n\\n## Phase 2: FastAPI Backend\\n- [ ] Create FastAPI application with PostGIS integration\\n- [ ] Implement CRUD endpoints for all themes (Surface Geological, Structural, Geothermal)\\n- [ ] Implement concurrent data ingestion with connection pooling\\n- [ ] Create email alert service for anomalous progress notifications\\n- [ ] Generate OpenAPI documentation for third-party integrations\\n\\n## Phase 3: React Frontend\\n- [ ] Create React application with updated EIGIS dashboard (white background, modern analytics)\\n- [ ] Build data collection forms for interconnected themes\\n- [ ] Build analytics dashboard with metrics visualization\\n- [ ] Integrate Interactive WebGIS Map (Leaflet) for geological nodes\\n- [ ] Preserve existing sidebar layout and update modules\\n\\n## Phase 4: Documentation & Integration\\n- [ ] Create comprehensive system documentation\\n- [ ] Document microservices architecture and scalability\\n- [ ] Document third-party integration guides\\n- [ ] Verify all links and navigation work correctly\\n\\n## Phase 5: Final Assembly & Deployment\\n- [ ] Build the complete HTML dashboard with all components\\n- [ ] Deploy and verify\\n"}'}}]} {'role': 'tool', 'content': "File 'todo.md' created successfully.", 'tool_call_id': 'chatcmpl-tool-ae76f5d7fd4825c0'} {'role': 'assistant', 'content': '## Phase 1: Designing the PostGIS Database Schema\n\nLet me start by creating the comprehensive normalized database schema that supports all interconnected themes with PostGIS spatial indexing.', 'tool_calls': [{'id': 'chatcmpl-tool-980a5abc60b19af9', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/001_create_extensions.sql", "file_contents": "-- ============================================================\\n-- EIGIS Geospatial Data Collection System\\n-- PostGIS Database Schema - Extension Setup\\n-- Author: International Winning PostGIS Database Engineer\\n-- ============================================================\\n\\n-- Enable required extensions\\nCREATE EXTENSION IF NOT EXISTS postgis;\\nCREATE EXTENSION IF NOT EXISTS postgis_topology;\\nCREATE EXTENSION IF NOT EXISTS postgis_raster;\\nCREATE EXTENSION IF NOT EXISTS uuid-ossp;\\nCREATE EXTENSION IF NOT EXISTS pg_trgm; -- Fast text similarity search\\nCREATE EXTENSION IF NOT EXISTS btree_gist; -- Composite GiST indexes\\nCREATE EXTENSION IF NOT EXISTS hstore; -- Flexible key-value metadata\\n\\n-- Verify PostGIS version\\nSELECT PostGIS_Full_Version();\\n"}'}}]} {'role': 'tool', 'content': "File 'database/001_create_extensions.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-980a5abc60b19af9'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-84518581fc26bb56', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/002_core_tables.sql", "file_contents": "-- ============================================================\\n-- EIGIS Core Tables: Projects, Field Trips, Personnel\\n-- Normalized schema supporting multiple observations per trip,\\n-- multiple samples per observation, GIS integration,\\n-- mobile data collection, and future expansion.\\n-- ============================================================\\n\\n-- -----------------------------------------------------------\\n-- 1. PROJECTS - Top-level organizational unit\\n-- -----------------------------------------------------------\\nCREATE TABLE projects (\\n project_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n project_code VARCHAR(50) NOT NULL UNIQUE, -- e.g. EIGIS-GIE-2026-001\\n project_name VARCHAR(255) NOT NULL,\\n description TEXT,\\n client_name VARCHAR(255),\\n client_email VARCHAR(255),\\n receptionist_email VARCHAR(255),\\n region VARCHAR(100),\\n start_date DATE NOT NULL,\\n end_date DATE,\\n status VARCHAR(20) DEFAULT \'active\' CHECK (status IN (\'planning\',\'active\',\'paused\',\'completed\',\'cancelled\')),\\n boundary_geom GEOMETRY(Polygon, 4326), -- Project AOI boundary\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_projects_boundary ON projects USING GIST(boundary_geom);\\nCREATE INDEX idx_projects_status ON projects(status);\\nCREATE INDEX idx_projects_code ON projects(project_code);\\n\\n-- -----------------------------------------------------------\\n-- 2. PERSONNEL - Team members and data loggers\\n-- -----------------------------------------------------------\\nCREATE TABLE personnel (\\n personnel_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n personnel_code VARCHAR(20) UNIQUE, -- e.g. TH-001\\n full_name VARCHAR(255) NOT NULL,\\n email VARCHAR(255),\\n phone VARCHAR(50),\\n role VARCHAR(50) CHECK (role IN (\'geologist\',\'engineer\',\'technician\',\'manager\',\'surveyor\',\'student\')),\\n organization VARCHAR(255) DEFAULT \'GIE\',\\n certification VARCHAR(100),\\n is_active BOOLEAN DEFAULT TRUE,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\n-- -----------------------------------------------------------\\n-- 3. FIELD TRIPS - Organized data collection campaigns\\n-- -----------------------------------------------------------\\nCREATE TABLE field_trips (\\n field_trip_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n project_id UUID NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,\\n trip_code VARCHAR(50) NOT NULL, -- e.g. FT-2026-001\\n trip_date DATE NOT NULL,\\n leader_id UUID REFERENCES personnel(personnel_id),\\n team_members UUID[], -- Array of personnel IDs\\n weather_condition VARCHAR(50),\\n vehicle_info VARCHAR(100),\\n area_visited VARCHAR(255),\\n route_geom GEOMETRY(LineString, 4326), -- GPS track of the day\\n notes TEXT,\\n sync_status VARCHAR(20) DEFAULT \'pending\' CHECK (sync_status IN (\'pending\',\'synced\',\'conflict\',\'error\')),\\n device_id VARCHAR(100), -- Mobile device identifier\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW(),\\n UNIQUE(project_id, trip_code)\\n);\\n\\nCREATE INDEX idx_field_trips_project ON field_trips(project_id);\\nCREATE INDEX idx_field_trips_date ON field_trips(trip_date);\\nCREATE INDEX idx_field_trips_route ON field_trips USING GIST(route_geom);\\nCREATE INDEX idx_field_trips_sync ON field_trips(sync_status);\\n\\n-- -----------------------------------------------------------\\n-- 4. OBSERVATIONS - Core spatial data unit\\n-- Each field trip has multiple observation points\\n-- -----------------------------------------------------------\\nCREATE TABLE observations (\\n observation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n field_trip_id UUID NOT NULL REFERENCES field_trips(field_trip_id) ON DELETE CASCADE,\\n site_id VARCHAR(50) NOT NULL, -- e.g. TP-001-A, EXP-047\\n observation_type VARCHAR(50) NOT NULL CHECK (observation_type IN (\\n \'test_pit\',\'gully\',\'slope_cut\',\'river_valley\',\'landslide\',\\n \'quarry_face\',\'road_cut\',\'natural_exposure\',\'geothermal_spring\',\\n \'fumarole\',\'hot_spring\',\'geyser\',\'mineral_deposit\',\'other\'\\n )),\\n exposure_type VARCHAR(50),\\n geom GEOMETRY(Point, 4326) NOT NULL, -- Primary spatial location\\n elevation_m DECIMAL(10,2),\\n easting DECIMAL(12,2),\\n northing DECIMAL(12,2),\\n utm_zone VARCHAR(5) DEFAULT \'37N\',\\n epsg_code INTEGER DEFAULT 4326,\\n exposure_length_m DECIMAL(8,2),\\n exposure_height_m DECIMAL(8,2),\\n groundwater_level_m DECIMAL(8,2),\\n weather_condition VARCHAR(50),\\n excavation_method VARCHAR(50),\\n accessibility VARCHAR(20) CHECK (accessibility IN (\'good\',\'fair\',\'poor\')),\\n admin_unit VARCHAR(255), -- Auto-populated from GIS\\n watershed VARCHAR(255), -- Auto-populated from DEM\\n geological_formation VARCHAR(255), -- Auto-populated from Geo Map\\n landslide_inventory_ref VARCHAR(100), -- Auto from Geohazard DB\\n remarks TEXT,\\n observation_date TIMESTAMPTZ DEFAULT NOW(),\\n logger_id UUID REFERENCES personnel(personnel_id),\\n status VARCHAR(20) DEFAULT \'draft\' CHECK (status IN (\'draft\',\'saved\',\'submitted\',\'reviewed\',\'approved\')),\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW(),\\n UNIQUE(field_trip_id, site_id)\\n);\\n\\n-- Critical spatial indexes for low-latency rendering\\nCREATE INDEX idx_observations_geom ON observations USING GIST(geom);\\nCREATE INDEX idx_observations_geom_3d ON observations USING GIST(geom)\\n WHERE elevation_m IS NOT NULL;\\nCREATE INDEX idx_observations_type ON observations(observation_type);\\nCREATE INDEX idx_observations_trip ON observations(field_trip_id);\\nCREATE INDEX idx_observations_status ON observations(status);\\nCREATE INDEX idx_observations_site ON observations(site_id);\\nCREATE INDEX idx_observations_composite ON observations USING GIST(geom, observation_type);\\n\\n-- -----------------------------------------------------------\\n-- 5. PHOTO DOCUMENTATION - Linked to any observation\\n-- -----------------------------------------------------------\\nCREATE TABLE photos (\\n photo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n photo_type VARCHAR(50) CHECK (photo_type IN (\\n \'exposure_overview\',\'slope_photo\',\'discontinuity_set\',\\n \'sample_photo\',\'structural_feature\',\'geothermal_feature\',\\n \'general\',\'panoramic\'\\n )),\\n file_path VARCHAR(500) NOT NULL, -- MinIO/S3 path\\n file_size_kb INTEGER,\\n mime_type VARCHAR(50),\\n geom GEOMETRY(Point, 4326), -- Geotagged location\\n azimuth_deg DECIMAL(5,2), -- Camera direction\\n caption VARCHAR(500),\\n taken_at TIMESTAMPTZ DEFAULT NOW(),\\n synced BOOLEAN DEFAULT FALSE,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_photos_observation ON photos(observation_id);\\nCREATE INDEX idx_photos_geom ON photos USING GIST(geom);\\nCREATE INDEX idx_photos_type ON photos(photo_type);\\n\\n-- -----------------------------------------------------------\\n-- 6. AUDIT LOG - Track all changes for data integrity\\n-- -----------------------------------------------------------\\nCREATE TABLE audit_log (\\n audit_id BIGSERIAL PRIMARY KEY,\\n table_name VARCHAR(100) NOT NULL,\\n record_id UUID NOT NULL,\\n action VARCHAR(10) NOT NULL CHECK (action IN (\'INSERT\',\'UPDATE\',\'DELETE\')),\\n old_data JSONB,\\n new_data JSONB,\\n changed_by VARCHAR(255),\\n changed_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_audit_table ON audit_log(table_name);\\nCREATE INDEX idx_audit_record ON audit_log(record_id);\\nCREATE INDEX idx_audit_time ON audit_log(changed_at);\\n"}'}}]} {'role': 'tool', 'content': "File 'database/002_core_tables.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-84518581fc26bb56'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-93181ddd57d2f372', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/003_surface_geological.sql", "file_contents": "-- ============================================================\\n-- EIGIS Theme 1: Surface Geological Features\\n-- Soil profiles, rock descriptions, lithological observations\\n-- Supports multiple horizons per observation, multiple samples\\n-- ============================================================\\n\\n-- -----------------------------------------------------------\\n-- 1. SOIL PROFILES - Linked to observations\\n-- -----------------------------------------------------------\\nCREATE TABLE soil_profiles (\\n soil_profile_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n profile_code VARCHAR(50), -- e.g. SP-001\\n total_depth_m DECIMAL(8,2),\\n groundwater_depth_m DECIMAL(8,2),\\n surface_condition VARCHAR(100),\\n vegetation_cover VARCHAR(100),\\n erosion_evidence VARCHAR(100),\\n drainage_class VARCHAR(50) CHECK (drainage_class IN (\'very_poor\',\'poor\',\'moderately_poor\',\'moderate\',\'moderately_well\',\'well\',\'excessively_well\')),\\n dilatancy VARCHAR(20) CHECK (dilatancy IN (\'none\',\'slow\',\'rapid\')),\\n sorting VARCHAR(30) CHECK (sorting IN (\'well_sorted\',\'moderately_sorted\',\'poorly_sorted\')),\\n profile_geom GEOMETRY(LineString, 4326), -- Vertical profile trace\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW(),\\n UNIQUE(observation_id, profile_code)\\n);\\n\\nCREATE INDEX idx_soil_profiles_obs ON soil_profiles(observation_id);\\n\\n-- -----------------------------------------------------------\\n-- 2. SOIL HORIZONS - Multiple horizons per profile\\n-- -----------------------------------------------------------\\nCREATE TABLE soil_horizons (\\n horizon_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n soil_profile_id UUID NOT NULL REFERENCES soil_profiles(soil_profile_id) ON DELETE CASCADE,\\n horizon_label VARCHAR(10) NOT NULL, -- A, B, C, etc.\\n depth_from_m DECIMAL(6,2) NOT NULL,\\n depth_to_m DECIMAL(6,2) NOT NULL,\\n color VARCHAR(50), -- Munsell or descriptive\\n consistency VARCHAR(30) CHECK (consistency IN (\'soft\',\'firm\',\'stiff\',\'very_stiff\',\'hard\',\'loose\',\'very_loose\',\'dense\',\'very_dense\')),\\n moisture VARCHAR(30) CHECK (moisture IN (\'dry\',\'slightly_moist\',\'moist\',\'wet\',\'very_wet\')),\\n grain_size VARCHAR(30) CHECK (grain_size IN (\'clay\',\'silt\',\'fine_sand\',\'medium_sand\',\'coarse_sand\',\'gravel\',\'cobble\',\'boulder\')),\\n plasticity VARCHAR(30) CHECK (plasticity IN (\'non_plastic\',\'low_CL\',\'medium_CI\',\'high_CH\')),\\n uscs_class VARCHAR(10) CHECK (uscs_class IN (\'CL\',\'CI\',\'CH\',\'SM\',\'SC\',\'SP\',\'SW\',\'GC\',\'GM\',\'GP\',\'GW\',\'ML\',\'MH\',\'PT\',\'OL\',\'OH\')),\\n organic_content VARCHAR(20),\\n root_content VARCHAR(50),\\n boundary_type VARCHAR(30) CHECK (boundary_type IN (\'abrupt\',\'clear\',\'gradual\',\'diffuse\')),\\n structure_type VARCHAR(50), -- Granular, blocky, prismatic, etc.\\n notes TEXT,\\n sort_order INTEGER DEFAULT 0,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_soil_horizons_profile ON soil_horizons(soil_profile_id);\\nCREATE INDEX idx_soil_horizons_depth ON soil_horizons(depth_from_m, depth_to_m);\\n\\n-- -----------------------------------------------------------\\n-- 3. ROCK DESCRIPTIONS - Linked to observations\\n-- -----------------------------------------------------------\\nCREATE TABLE rock_descriptions (\\n rock_desc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n rock_name VARCHAR(100) NOT NULL, -- Basalt, Granite, etc.\\n rock_type VARCHAR(30) CHECK (rock_type IN (\'igneous_volcanic\',\'igneous_plutonic\',\'sedimentary\',\'metamorphic\',\'pyroclastic\',\'hydrothermal\')),\\n color VARCHAR(50),\\n grain_size VARCHAR(30) CHECK (grain_size IN (\'fine_grained\',\'medium_grained\',\'coarse_grained\',\'aphanitic\',\'porphyritic\',\'phaneritic\',\'pegmatitic\')),\\n texture VARCHAR(50), -- Massive, foliated, vesicular, etc.\\n weathering_grade INTEGER CHECK (weathering_grade BETWEEN 1 AND 6),\\n weathering_desc VARCHAR(50), -- Unweathered to Residual Soil\\n intact_strength INTEGER CHECK (intact_strength BETWEEN 1 AND 7),\\n strength_desc VARCHAR(50), -- Very Weak to Extremely Strong\\n structure VARCHAR(50) CHECK (structure IN (\'massive\',\'foliated\',\'vesicular\',\'amygdaloidal\',\'laminated\',\'bedded\',\'schistose\',\'gneissose\')),\\n mineralogy TEXT,\\n alteration VARCHAR(100),\\n rock_description TEXT, -- Free-form detailed description\\n geom GEOMETRY(Point, 4326), -- Rock sample location\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_rock_desc_obs ON rock_descriptions(observation_id);\\nCREATE INDEX idx_rock_desc_geom ON rock_descriptions USING GIST(geom);\\nCREATE INDEX idx_rock_desc_name ON rock_descriptions(rock_name);\\nCREATE INDEX idx_rock_desc_type ON rock_descriptions(rock_type);\\n\\n-- -----------------------------------------------------------\\n-- 4. LITHOLOGICAL UNITS - Geological map integration\\n-- -----------------------------------------------------------\\nCREATE TABLE lithological_units (\\n litho_unit_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n unit_code VARCHAR(30) UNIQUE, -- e.g. Qa, Tvb, Pzs\\n unit_name VARCHAR(255) NOT NULL,\\n rock_type VARCHAR(50),\\n age_era VARCHAR(50), -- Cenozoic, Mesozoic, etc.\\n age_period VARCHAR(50), -- Quaternary, Tertiary, etc.\\n age_epoch VARCHAR(50), -- Pleistocene, Holocene, etc.\\n description TEXT,\\n geom GEOMETRY(MultiPolygon, 4326), -- Outcrop polygon\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_litho_units_geom ON lithological_units USING GIST(geom);\\nCREATE INDEX idx_litho_units_code ON lithological_units(unit_code);\\n"}'}}]} {'role': 'tool', 'content': "File 'database/003_surface_geological.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-93181ddd57d2f372'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-a47ae2118195970e', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/004_structural_features.sql", "file_contents": "-- ============================================================\\n-- EIGIS Theme 2: Structural Features\\n-- Discontinuities, slope stability, rock mass classification\\n-- Multiple structural measurements per observation\\n-- ============================================================\\n\\n-- -----------------------------------------------------------\\n-- 1. DISCONTINUITY MEASUREMENTS\\n-- Multiple sets per observation\\n-- -----------------------------------------------------------\\nCREATE TABLE discontinuity_measurements (\\n disc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n set_label VARCHAR(20) NOT NULL, -- J1, J2, F1, etc.\\n disc_type VARCHAR(30) NOT NULL CHECK (disc_type IN (\\n \'joint\',\'fault\',\'bedding\',\'foliation\',\'shear_zone\',\\n \'tension_crack\',\'cleavage\',\'vein\',\'dyke\',\'contact\'\\n )),\\n dip_direction_deg DECIMAL(5,1) CHECK (dip_direction_deg BETWEEN 0 AND 360),\\n dip_deg DECIMAL(4,1) CHECK (dip_deg BETWEEN 0 AND 90),\\n strike_deg DECIMAL(5,1) GENERATED ALWAYS AS (\\n CASE WHEN dip_direction_deg IS NOT NULL\\n THEN (dip_direction_deg + 90)::DECIMAL(5,1) % 360\\n ELSE NULL\\n END\\n ) STORED,\\n spacing_m DECIMAL(6,3),\\n aperture_mm DECIMAL(6,2),\\n persistence_m DECIMAL(6,2),\\n roughness VARCHAR(30) CHECK (roughness IN (\'very_rough\',\'rough\',\'slightly_rough\',\'smooth\',\'slickensided\',\'polished\')),\\n infill_material VARCHAR(50) CHECK (infill_material IN (\'none\',\'clay\',\'calcite\',\'quartz\',\'chlorite\',\'kaolin\',\'hematite\',\'gouge\',\'breccia\')),\\n infill_thickness_mm DECIMAL(5,2),\\n water_condition VARCHAR(30) CHECK (water_condition IN (\'dry\',\'damp\',\'wet\',\'flowing\',\'seeping\')),\\n wall_weathering INTEGER CHECK (wall_weathering BETWEEN 1 AND 5),\\n geom GEOMETRY(Point, 4326), -- Measurement point\\n line_geom GEOMETRY(LineString, 4326), -- Discontinuity trace line\\n sort_order INTEGER DEFAULT 0,\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_disc_obs ON discontinuity_measurements(observation_id);\\nCREATE INDEX idx_disc_geom ON discontinuity_measurements USING GIST(geom);\\nCREATE INDEX idx_disc_type ON discontinuity_measurements(disc_type);\\nCREATE INDEX idx_disc_dip ON discontinuity_measurements(dip_direction_deg, dip_deg);\\nCREATE INDEX idx_disc_line ON discontinuity_measurements USING GIST(line_geom);\\n\\n-- -----------------------------------------------------------\\n-- 2. SLOPE STABILITY ASSESSMENTS\\n-- -----------------------------------------------------------\\nCREATE TABLE slope_stability (\\n slope_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n has_existing_slope BOOLEAN DEFAULT TRUE,\\n slope_height_m DECIMAL(8,2),\\n slope_angle_deg DECIMAL(5,1),\\n aspect_deg DECIMAL(5,1),\\n slope_form VARCHAR(30) CHECK (slope_form IN (\'concave\',\'convex\',\'planar\',\'stepped\',\'composite\')),\\n stability_condition VARCHAR(30) CHECK (stability_condition IN (\'stable\',\'small_problems\',\'large_problems\',\'failed\',\'critical\')),\\n hazard_level VARCHAR(20) CHECK (hazard_level IN (\'low\',\'moderate\',\'high\',\'very_high\',\'extreme\')),\\n failure_mode VARCHAR(50) CHECK (failure_mode IN (\'circular\',\'planar\',\'wedge\',\'toppling\',\'fall\',\'flow\',\'spreading\',\'complex\')),\\n factor_of_safety DECIMAL(6,3),\\n trigger_mechanism VARCHAR(100),\\n volume_estimate_m3 DECIMAL(12,2),\\n runout_distance_m DECIMAL(8,2),\\n recommended_actions TEXT,\\n mitigation_measures TEXT,\\n monitoring_required BOOLEAN DEFAULT FALSE,\\n slope_geom GEOMETRY(LineString, 4326), -- Slope profile line\\n failure_geom GEOMETRY(Polygon, 4326), -- Failure area polygon\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_slope_obs ON slope_stability(observation_id);\\nCREATE INDEX idx_slope_hazard ON slope_stability(hazard_level);\\nCREATE INDEX idx_slope_geom ON slope_stability USING GIST(slope_geom);\\nCREATE INDEX idx_slope_failure ON slope_stability USING GIST(failure_geom);\\n\\n-- -----------------------------------------------------------\\n-- 3. ROCK MASS CLASSIFICATION (RMR / Q / GSI)\\n-- -----------------------------------------------------------\\nCREATE TABLE rock_mass_classifications (\\n classification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n -- RMR89 Parameters\\n rmr_strength INTEGER CHECK (rmr_strength BETWEEN 0 AND 15),\\n rmr_drill_quality INTEGER CHECK (rmr_drill_quality BETWEEN 3 AND 20),\\n rmr_spacing INTEGER CHECK (rmr_spacing BETWEEN 5 AND 20),\\n rmr_condition INTEGER CHECK (rmr_condition BETWEEN 0 AND 30),\\n rmr_groundwater INTEGER CHECK (rmr_groundwater BETWEEN 0 AND 15),\\n rmr_adjustment INTEGER CHECK (rmr_adjustment BETWEEN -15 AND 0),\\n rmr_total INTEGER CHECK (rmr_total BETWEEN 0 AND 100),\\n rmr_class VARCHAR(30), -- I through V\\n -- Q-System Parameters\\n q_rqd DECIMAL(5,1) CHECK (q_rqd BETWEEN 0 AND 100),\\n q_jn DECIMAL(5,1) CHECK (q_jn BETWEEN 0.5 AND 20),\\n q_jr DECIMAL(5,1) CHECK (q_jr BETWEEN 0.1 AND 4),\\n q_ja DECIMAL(5,1) CHECK (q_ja BETWEEN 0.1 AND 20),\\n q_jw DECIMAL(5,1) CHECK (q_jw BETWEEN 0.05 AND 1),\\n q_srf DECIMAL(5,1) CHECK (q_srf BETWEEN 0.5 AND 400),\\n q_value DECIMAL(10,4),\\n q_class VARCHAR(30),\\n -- GSI\\n gsi_value INTEGER CHECK (gsi_value BETWEEN 5 AND 100),\\n gsi_structure VARCHAR(50),\\n gsi_surface VARCHAR(50),\\n -- Computed\\n emod_gpa DECIMAL(8,2), -- Deformation modulus\\n cohesion_mpa DECIMAL(6,3),\\n friction_angle_deg DECIMAL(5,1),\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_rmc_obs ON rock_mass_classifications(observation_id);\\nCREATE INDEX idx_rmc_rmr ON rock_mass_classifications(rmr_total);\\nCREATE INDEX idx_rmc_q ON rock_mass_classifications(q_value);\\n\\n-- -----------------------------------------------------------\\n-- 4. LANDSLIDE INVENTORY\\n-- -----------------------------------------------------------\\nCREATE TABLE landslide_inventory (\\n landslide_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL,\\n project_id UUID NOT NULL REFERENCES projects(project_id),\\n landslide_code VARCHAR(50) UNIQUE,\\n landslide_type VARCHAR(50) NOT NULL CHECK (landslide_type IN (\\n \'rotational_slide\',\'translational_slide\',\'block_slide\',\\n \'rockfall\',\'debris_flow\',\'earthflow\',\'mudflow\',\\n \'lateral_spreading\',\'complex\',\'toppling\',\'creep\'\\n )),\\n activity_state VARCHAR(30) CHECK (activity_state IN (\'active\',\'suspended\',\'reactivated\',\'dormant\',\'stabilized\',\'relic\')),\\n movement_rate VARCHAR(30), -- mm/year, m/year, etc.\\n depth_estimated_m DECIMAL(6,2),\\n area_m2 DECIMAL(12,2),\\n volume_m3 DECIMAL(12,2),\\n casualties INTEGER DEFAULT 0,\\n damage_desc TEXT,\\n causative_factors TEXT[],\\n geom GEOMETRY(Polygon, 4326) NOT NULL, -- Landslide boundary\\n source_geom GEOMETRY(Point, 4326), -- Crown/source point\\n deposit_geom GEOMETRY(Point, 4326), -- Toe/deposit point\\n threat_level VARCHAR(20) CHECK (threat_level IN (\'low\',\'moderate\',\'high\',\'very_high\',\'extreme\')),\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_landslide_geom ON landslide_inventory USING GIST(geom);\\nCREATE INDEX idx_landslide_source ON landslide_inventory USING GIST(source_geom);\\nCREATE INDEX idx_landslide_project ON landslide_inventory(project_id);\\nCREATE INDEX idx_landslide_type ON landslide_inventory(landslide_type);\\nCREATE INDEX idx_landslide_activity ON landslide_inventory(activity_state);\\n"}'}}]} {'role': 'tool', 'content': "File 'database/004_structural_features.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-a47ae2118195970e'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-85d992a92b8a3329', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/005_geothermal_features.sql", "file_contents": "-- ============================================================\\n-- EIGIS Theme 3: Geothermal Features\\n-- Hot springs, fumaroles, geothermal gradients, wells\\n-- Supports future expansion: geochemistry, geophysics, drilling\\n-- ============================================================\\n\\n-- -----------------------------------------------------------\\n-- 1. GEOTHERMAL MANIFESTATIONS\\n-- -----------------------------------------------------------\\nCREATE TABLE geothermal_manifestations (\\n manifestation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n manifestation_type VARCHAR(50) NOT NULL CHECK (manifestation_type IN (\\n \'hot_spring\',\'warm_spring\',\'fumarole\',\'steaming_ground\',\\n \'mud_pot\',\'geyser\',\'sinter_deposit\',\'travertine\',\\n \'altered_ground\',\'hydrothermal_breccia\',\'volcanic_vent\',\\n \'mineral_spring\',\'other\'\\n )),\\n surface_temp_c DECIMAL(6,2), -- Surface temperature °C\\n discharge_rate_lps DECIMAL(8,3), -- Liters per second\\n ph_value DECIMAL(4,2),\\n electrical_conductivity_us DECIMAL(8,2), -- µS/cm\\n total_dissolved_solids DECIMAL(8,2), -- mg/L\\n fluid_color VARCHAR(50),\\n odor VARCHAR(50),\\n deposit_type VARCHAR(100), -- Sinter, travertine, etc.\\n alteration_zone VARCHAR(100),\\n alteration_intensity VARCHAR(30) CHECK (alteration_intensity IN (\'none\',\'slight\',\'moderate\',\'intense\',\'pervasive\')),\\n alteration_minerals TEXT[],\\n structural_control VARCHAR(100), -- Fault-controlled, fracture, etc.\\n host_rock VARCHAR(100),\\n geom GEOMETRY(Point, 4326) NOT NULL,\\n elevation_m DECIMAL(10,2),\\n catchment_area VARCHAR(100),\\n usage_current VARCHAR(100), -- Bathing, irrigation, none, etc.\\n usage_potential VARCHAR(100),\\n temperature_measurement_depth_m DECIMAL(6,2),\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_geotherm_obs ON geothermal_manifestations(observation_id);\\nCREATE INDEX idx_geotherm_geom ON geothermal_manifestations USING GIST(geom);\\nCREATE INDEX idx_geotherm_type ON geothermal_manifestations(manifestation_type);\\nCREATE INDEX idx_geotherm_temp ON geothermal_manifestations(surface_temp_c);\\n\\n-- -----------------------------------------------------------\\n-- 2. GEOTHERMAL GRADIENT STATIONS\\n-- -----------------------------------------------------------\\nCREATE TABLE geothermal_gradients (\\n gradient_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID REFERENCES observations(observation_id) ON DELETE CASCADE,\\n station_code VARCHAR(50) NOT NULL,\\n depth_top_m DECIMAL(8,2) NOT NULL,\\n depth_bottom_m DECIMAL(8,2) NOT NULL,\\n temp_top_c DECIMAL(6,2),\\n temp_bottom_c DECIMAL(6,2),\\n gradient_c_per_km DECIMAL(8,2), -- °C/km\\n conductivity_wmk DECIMAL(8,4), -- W/m·K\\n heat_flow_mwm2 DECIMAL(8,4), -- mW/m²\\n measurement_method VARCHAR(50),\\n geom GEOMETRY(Point, 4326),\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_gradient_obs ON geothermal_gradients(observation_id);\\nCREATE INDEX idx_gradient_geom ON geothermal_gradients USING GIST(geom);\\nCREATE INDEX idx_gradient_value ON geothermal_gradients(gradient_c_per_km);\\n\\n-- -----------------------------------------------------------\\n-- 3. SAMPLING TABLE - Unified across all themes\\n-- Multiple samples per observation, linked to any sub-entity\\n-- -----------------------------------------------------------\\nCREATE TABLE samples (\\n sample_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,\\n sample_code VARCHAR(50) NOT NULL, -- S-01, GTH-01, etc.\\n sample_type VARCHAR(30) NOT NULL CHECK (sample_type IN (\\n \'disturbed_soil\',\'undisturbed_soil\',\'bulk_soil\',\\n \'rock_core\',\'rock_chip\',\'water\',\'gas\',\\n \'geothermal_fluid\',\'geothermal_gas\',\'alteration_mineral\',\\n \'surface_sediment\',\'vegetation\',\'other\'\\n )),\\n depth_m DECIMAL(6,2),\\n horizon_ref VARCHAR(20), -- Reference to soil horizon\\n weight_kg DECIMAL(6,2),\\n volume_cm3 DECIMAL(8,2),\\n sample_condition VARCHAR(30) CHECK (sample_condition IN (\'good\',\'fair\',\'poor\',\'compromised\')),\\n storage_method VARCHAR(50), -- Bag, core box, bottle, etc.\\n tests_requested TEXT[], -- Array of requested tests\\n lab_id VARCHAR(50), -- Laboratory reference\\n lab_status VARCHAR(20) DEFAULT \'pending\' CHECK (lab_status IN (\'pending\',\'in_progress\',\'completed\',\'failed\')),\\n lab_results JSONB, -- Flexible results storage\\n geom GEOMETRY(Point, 4326),\\n collected_at TIMESTAMPTZ DEFAULT NOW(),\\n collected_by UUID REFERENCES personnel(personnel_id),\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW(),\\n UNIQUE(observation_id, sample_code)\\n);\\n\\nCREATE INDEX idx_samples_obs ON samples(observation_id);\\nCREATE INDEX idx_samples_geom ON samples USING GIST(geom);\\nCREATE INDEX idx_samples_type ON samples(sample_type);\\nCREATE INDEX idx_samples_lab_status ON samples(lab_status);\\nCREATE INDEX idx_samples_code ON samples(sample_code);\\n\\n-- -----------------------------------------------------------\\n-- 4. GEOCHEMISTRY RESULTS (Future expansion table)\\n-- -----------------------------------------------------------\\nCREATE TABLE geochemistry_results (\\n geochem_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n sample_id UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE,\\n analysis_type VARCHAR(50) NOT NULL CHECK (analysis_type IN (\\n \'xrf\',\'xrd\',\'icp_ms\',\'icp_aes\',\'aas\',\'ic\',\'gc_ms\',\\n \'isotope_ratio\',\'major_oxides\',\'trace_elements\',\'rare_earth\',\\n \'stable_isotope\',\'radiometric\',\'fluid_inclusion\',\'other\'\\n )),\\n laboratory VARCHAR(100),\\n method_code VARCHAR(30),\\n detection_limit DECIMAL(12,6),\\n results JSONB NOT NULL, -- Key-value pairs of elements/compounds\\n unit VARCHAR(20) DEFAULT \'ppm\',\\n quality_flag VARCHAR(20) CHECK (quality_flag IN (\'valid\',\'suspect\',\'invalid\',\'below_detection\')),\\n analyzed_at DATE,\\n reported_at DATE,\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_geochem_sample ON geochemistry_results(sample_id);\\nCREATE INDEX idx_geochem_type ON geochemistry_results(analysis_type);\\nCREATE INDEX idx_geochem_results ON geochemistry_results USING GIN(results);\\n\\n-- -----------------------------------------------------------\\n-- 5. GEOPHYSICAL SURVEYS (Future expansion table)\\n-- -----------------------------------------------------------\\nCREATE TABLE geophysical_surveys (\\n survey_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL,\\n project_id UUID NOT NULL REFERENCES projects(project_id),\\n survey_type VARCHAR(50) NOT NULL CHECK (survey_type IN (\\n \'magnetic\',\'gravity\',\'seismic_refraction\',\'seismic_reflection\',\\n \'electrical_resistivity\',\'induced_polarization\',\'spm\',\\n \'magnetotelluric\',\'ground_penetrating_radar\',\'self_potential\',\\n \'thermal_infrared\',\'aeromagnetic\',\'other\'\\n )),\\n line_id VARCHAR(50),\\n station_spacing_m DECIMAL(8,2),\\n data_points INTEGER,\\n depth_investigation_m DECIMAL(8,2),\\n instrument VARCHAR(100),\\n parameters JSONB,\\n results_summary TEXT,\\n data_file_path VARCHAR(500), -- S3/MinIO path\\n geom GEOMETRY(MultiPoint, 4326), -- Survey stations\\n line_geom GEOMETRY(LineString, 4326), -- Survey line\\n date_conducted DATE,\\n conducted_by UUID REFERENCES personnel(personnel_id),\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_geophys_obs ON geophysical_surveys(observation_id);\\nCREATE INDEX idx_geophys_project ON geophysical_surveys(project_id);\\nCREATE INDEX idx_geophys_geom ON geophysical_surveys USING GIST(geom);\\nCREATE INDEX idx_geophys_line ON geophysical_surveys USING GIST(line_geom);\\n\\n-- -----------------------------------------------------------\\n-- 6. DRILLING DATA (Future expansion table)\\n-- -----------------------------------------------------------\\nCREATE TABLE drilling_data (\\n drill_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n project_id UUID NOT NULL REFERENCES projects(project_id),\\n observation_id UUID REFERENCES observations(observation_id) ON DELETE SET NULL,\\n borehole_code VARCHAR(50) NOT NULL,\\n bh_type VARCHAR(30) CHECK (bh_type IN (\'exploratory\',\'production\',\'injection\',\'monitoring\',\'temperature_gradient\',\'core_hole\')),\\n collar_easting DECIMAL(12,2),\\n collar_northing DECIMAL(12,2),\\n collar_elevation DECIMAL(10,2),\\n total_depth_m DECIMAL(8,2),\\n diameter_mm DECIMAL(6,1),\\n inclination_deg DECIMAL(5,1),\\n azimuth_deg DECIMAL(5,1),\\n water_table_m DECIMAL(6,2),\\n circulation_loss VARCHAR(50),\\n lithology_log JSONB, -- Array of {from, to, description}\\n geom GEOMETRY(Point, 4326) NOT NULL,\\n trajectory_geom GEOMETRY(LineString, 4326), -- 3D borehole path\\n start_date DATE,\\n end_date DATE,\\n contractor VARCHAR(100),\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW(),\\n updated_at TIMESTAMPTZ DEFAULT NOW(),\\n UNIQUE(project_id, borehole_code)\\n);\\n\\nCREATE INDEX idx_drill_project ON drilling_data(project_id);\\nCREATE INDEX idx_drill_geom ON drilling_data USING GIST(geom);\\nCREATE INDEX idx_drill_traj ON drilling_data USING GIST(trajectory_geom);\\n\\n-- -----------------------------------------------------------\\n-- 7. PETROGRAPHY (Future expansion table)\\n-- -----------------------------------------------------------\\nCREATE TABLE petrography (\\n petro_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\\n sample_id UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE,\\n thin_section_id VARCHAR(50),\\n rock_name VARCHAR(100),\\n modal_mineralogy JSONB, -- {quartz: 35, feldspar: 45, ...}\\n texture_type VARCHAR(50),\\n grain_size_mm DECIMAL(6,3),\\n fabric VARCHAR(50),\\n alteration TEXT,\\n micro_structures TEXT,\\n photo_path VARCHAR(500),\\n analyzed_by UUID REFERENCES personnel(personnel_id),\\n analyzed_at DATE,\\n notes TEXT,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_petro_sample ON petrography(sample_id);\\n"}'}}]} {'role': 'tool', 'content': "File 'database/005_geothermal_features.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-85d992a92b8a3329'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-a26601873d485e68', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "database/006_triggers_and_alerts.sql", "file_contents": "-- ============================================================\\n-- EIGIS Database Triggers, Functions & Email Alert System\\n-- Anomalous progress detection, auto-population, audit trail\\n-- ============================================================\\n\\n-- -----------------------------------------------------------\\n-- 1. AUTO-UPDATE updated_at TIMESTAMP TRIGGER\\n-- Generic function applied to all tables\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION update_updated_at_column()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n NEW.updated_at = NOW();\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Apply to all tables with updated_at\\nDO $$\\nDECLARE\\n t TEXT;\\nBEGIN\\n FOR t IN\\n SELECT table_name FROM information_schema.columns\\n WHERE column_name = \'updated_at\'\\n AND table_schema = \'public\'\\n AND table_name NOT IN (\'audit_log\')\\n LOOP\\n EXECUTE format(\'\\n CREATE TRIGGER set_updated_at\\n BEFORE UPDATE ON %I\\n FOR EACH ROW\\n EXECUTE FUNCTION update_updated_at_column();\\n \', t);\\n END LOOP;\\nEND;\\n$$;\\n\\n-- -----------------------------------------------------------\\n-- 2. AUDIT LOG TRIGGER FUNCTION\\n-- Records all INSERT/UPDATE/DELETE operations\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION audit_trigger_func()\\nRETURNS TRIGGER AS $$\\nDECLARE\\n audit_row audit_log%ROWTYPE;\\nBEGIN\\n audit_row := row(\\n nextval(\'audit_log_audit_id_seq\'),\\n TG_TABLE_NAME,\\n COALESCE(NEW.observation_id, NEW.observation_id, OLD.observation_id,\\n NEW.sample_id, NEW.disc_id, NEW.slope_id,\\n NEW.manifestation_id, NEW.soil_profile_id,\\n NEW.rock_desc_id, NEW.project_id, NEW.field_trip_id,\\n COALESCE(NEW, OLD)::uuid),\\n TG_OP,\\n CASE WHEN TG_OP IN (\'UPDATE\',\'DELETE\') THEN to_jsonb(OLD) ELSE NULL END,\\n CASE WHEN TG_OP IN (\'INSERT\',\'UPDATE\') THEN to_jsonb(NEW) ELSE NULL END,\\n current_user,\\n NOW()\\n );\\n INSERT INTO audit_log VALUES (audit_row.*);\\n RETURN COALESCE(NEW, OLD);\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Apply audit triggers to key tables\\nSELECT apply_audit_trigger(t) FROM unnest(ARRAY[\\n \'observations\', \'soil_profiles\', \'soil_horizons\', \'rock_descriptions\',\\n \'discontinuity_measurements\', \'slope_stability\', \'rock_mass_classifications\',\\n \'geothermal_manifestations\', \'samples\', \'landslide_inventory\'\\n]) AS t;\\n\\n-- Helper to create audit triggers\\nCREATE OR REPLACE FUNCTION apply_audit_trigger(tbl TEXT)\\nRETURNS VOID AS $$\\nBEGIN\\n EXECUTE format(\'\\n CREATE TRIGGER audit_%s\\n AFTER INSERT OR UPDATE OR DELETE ON %I\\n FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();\\n \', tbl, tbl);\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Apply to all key tables\\nSELECT apply_audit_trigger(t) FROM unnest(ARRAY[\\n \'observations\', \'soil_profiles\', \'soil_horizons\', \'rock_descriptions\',\\n \'discontinuity_measurements\', \'slope_stability\', \'rock_mass_classifications\',\\n \'geothermal_manifestations\', \'samples\', \'landslide_inventory\'\\n]) AS t;\\n\\n-- -----------------------------------------------------------\\n-- 3. AUTO-POPULATE ADMIN UNIT FROM GIS BOUNDARY\\n-- Trigger: When observation is inserted/updated with geom\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION auto_populate_admin_unit()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n IF NEW.geom IS NOT NULL AND (NEW.admin_unit IS NULL OR NEW.admin_unit = \'\') THEN\\n -- Attempt to intersect with administrative boundaries layer\\n -- This assumes an admin_boundaries table exists or uses a service\\n BEGIN\\n SELECT name INTO NEW.admin_unit\\n FROM admin_boundaries\\n WHERE ST_Contains(geom, NEW.geom)\\n LIMIT 1;\\n EXCEPTION WHEN undefined_table THEN\\n NEW.admin_unit := \'Auto · GIS Boundary\';\\n END;\\n END IF;\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_auto_admin_unit\\n BEFORE INSERT OR UPDATE OF geom ON observations\\n FOR EACH ROW EXECUTE FUNCTION auto_populate_admin_unit();\\n\\n-- -----------------------------------------------------------\\n-- 4. AUTO-POPULATE WATERSHED FROM DEM LAYER\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION auto_populate_watershed()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n IF NEW.geom IS NOT NULL AND (NEW.watershed IS NULL OR NEW.watershed = \'\') THEN\\n BEGIN\\n SELECT watershed_name INTO NEW.watershed\\n FROM watershed_boundaries\\n WHERE ST_Contains(geom, NEW.geom)\\n LIMIT 1;\\n EXCEPTION WHEN undefined_table THEN\\n NEW.watershed := \'Auto · DEM Layer\';\\n END;\\n END IF;\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_auto_watershed\\n BEFORE INSERT OR UPDATE OF geom ON observations\\n FOR EACH ROW EXECUTE FUNCTION auto_populate_watershed();\\n\\n-- -----------------------------------------------------------\\n-- 5. AUTO-POPULATE GEOLOGICAL FORMATION FROM GEO MAP\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION auto_populate_geology()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n IF NEW.geom IS NOT NULL AND (NEW.geological_formation IS NULL OR NEW.geological_formation = \'\') THEN\\n BEGIN\\n SELECT unit_name INTO NEW.geological_formation\\n FROM lithological_units\\n WHERE ST_Contains(geom, NEW.geom)\\n LIMIT 1;\\n EXCEPTION WHEN undefined_table THEN\\n NEW.geological_formation := \'Auto · Geo Map Layer\';\\n END;\\n END IF;\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_auto_geology\\n BEFORE INSERT OR UPDATE OF geom ON observations\\n FOR EACH ROW EXECUTE FUNCTION auto_populate_geology();\\n\\n-- -----------------------------------------------------------\\n-- 6. COMPUTE ROCK MASS CLASSIFICATION SCORES\\n-- Auto-calculate RMR, Q, and GSI totals\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION compute_rock_mass_scores()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n -- RMR Total\\n IF NEW.rmr_strength IS NOT NULL AND NEW.rmq_drill_quality IS NOT NULL\\n AND NEW.rmr_spacing IS NOT NULL AND NEW.rmr_condition IS NOT NULL\\n AND NEW.rmr_groundwater IS NOT NULL THEN\\n NEW.rmr_total := NEW.rmr_strength + NEW.rmr_drill_quality + NEW.rmr_spacing\\n + NEW.rmr_condition + NEW.rmr_groundwater\\n + COALESCE(NEW.rmr_adjustment, 0);\\n\\n -- RMR Class\\n NEW.rmr_class := CASE\\n WHEN NEW.rmr_total >= 81 THEN \'I - Very Good\'\\n WHEN NEW.rmr_total >= 61 THEN \'II - Good\'\\n WHEN NEW.rmr_total >= 41 THEN \'III - Fair\'\\n WHEN NEW.rmr_total >= 21 THEN \'IV - Poor\'\\n ELSE \'V - Very Poor\'\\n END;\\n END IF;\\n\\n -- Q-System Value\\n IF NEW.q_rqd IS NOT NULL AND NEW.q_jn IS NOT NULL\\n AND NEW.q_jr IS NOT NULL AND NEW.q_ja IS NOT NULL\\n AND NEW.q_jw IS NOT NULL AND NEW.q_srf IS NOT NULL THEN\\n NEW.q_value := (NEW.q_rqd * NEW.q_jr * NEW.q_jw) /\\n (NEW.q_jn * NEW.q_ja * NEW.q_srf);\\n\\n NEW.q_class := CASE\\n WHEN NEW.q_value >= 400 THEN \'Exceptionally Good\'\\n WHEN NEW.q_value >= 100 THEN \'Extremely Good\'\\n WHEN NEW.q_value >= 40 THEN \'Very Good\'\\n WHEN NEW.q_value >= 10 THEN \'Good\'\\n WHEN NEW.q_value >= 4 THEN \'Fair\'\\n WHEN NEW.q_value >= 1 THEN \'Poor\'\\n WHEN NEW.q_value >= 0.1 THEN \'Very Poor\'\\n ELSE \'Exceptionally Poor\'\\n END;\\n END IF;\\n\\n -- Deformation Modulus from RMR (Bieniawski, 1978)\\n IF NEW.rmr_total IS NOT NULL AND NEW.rmr_total > 0 THEN\\n IF NEW.rmr_total > 50 THEN\\n NEW.emod_gpa := 2.0 * NEW.rmr_total - 100.0;\\n ELSE\\n NEW.emod_gpa := 2.0 * POWER(10, (NEW.rmr_total + 10) / 40.0);\\n END IF;\\n END IF;\\n\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_compute_rmc_scores\\n BEFORE INSERT OR UPDATE ON rock_mass_classifications\\n FOR EACH ROW EXECUTE FUNCTION compute_rock_mass_scores();\\n\\n-- -----------------------------------------------------------\\n-- 7. ANOMALOUS PROGRESS DETECTION & EMAIL ALERTS\\n-- Monitors project progress and triggers alerts\\n-- -----------------------------------------------------------\\n\\n-- Alert queue table\\nCREATE TABLE alert_queue (\\n alert_id BIGSERIAL PRIMARY KEY,\\n project_id UUID NOT NULL REFERENCES projects(project_id),\\n alert_type VARCHAR(50) NOT NULL CHECK (alert_type IN (\\n \'data_gap\',\'observation_spike\',\'hazard_critical\',\\n \'geothermal_anomaly\',\'sample_failure\',\'progress_delay\',\\n \'spatial_outlier\',\'data_quality_issue\'\\n )),\\n severity VARCHAR(20) DEFAULT \'warning\' CHECK (severity IN (\'info\',\'warning\',\'critical\',\'emergency\')),\\n message TEXT NOT NULL,\\n details JSONB,\\n recipients TEXT[], -- Email addresses\\n is_sent BOOLEAN DEFAULT FALSE,\\n sent_at TIMESTAMPTZ,\\n created_at TIMESTAMPTZ DEFAULT NOW()\\n);\\n\\nCREATE INDEX idx_alert_project ON alert_queue(project_id);\\nCREATE INDEX idx_alert_sent ON alert_queue(is_sent);\\nCREATE INDEX idx_alert_severity ON alert_queue(severity);\\n\\n-- Function: Detect anomalous data gaps\\nCREATE OR REPLACE FUNCTION detect_data_gaps()\\nRETURNS TABLE(project_id UUID, gap_type TEXT, gap_details TEXT) AS $$\\nBEGIN\\n RETURN QUERY\\n -- Projects with no observations in last 7 days despite being active\\n SELECT p.project_id,\\n \'observation_gap\'::TEXT,\\n format(\'No observations recorded in 7+ days for active project %s\', p.project_code)\\n FROM projects p\\n WHERE p.status = \'active\'\\n AND NOT EXISTS (\\n SELECT 1 FROM field_trips ft\\n JOIN observations o ON o.field_trip_id = ft.field_trip_id\\n WHERE ft.project_id = p.project_id\\n AND o.created_at > NOW() - INTERVAL \'7 days\'\\n );\\n\\n RETURN QUERY\\n -- Observations with missing critical data\\n SELECT o.observation_id::UUID,\\n \'missing_critical_data\'::TEXT,\\n format(\'Observation %s at site %s missing GPS coordinates\', o.observation_id, o.site_id)\\n FROM observations o\\n WHERE o.geom IS NULL AND o.status = \'submitted\';\\n\\n RETURN QUERY\\n -- Projects with high hazard observations not yet reviewed\\n SELECT sl.observation_id::UUID,\\n \'unreviewed_hazard\'::TEXT,\\n format(\'High hazard slope assessment at site %s not reviewed\', o.site_id)\\n FROM slope_stability sl\\n JOIN observations o ON o.observation_id = sl.observation_id\\n WHERE sl.hazard_level IN (\'high\',\'very_high\',\'extreme\')\\n AND o.status NOT IN (\'reviewed\',\'approved\');\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Function: Detect geothermal anomalies\\nCREATE OR REPLACE FUNCTION detect_geothermal_anomalies()\\nRETURNS TABLE(manifestation_id UUID, anomaly_type TEXT, anomaly_details TEXT) AS $$\\nBEGIN\\n RETURN QUERY\\n -- Unusually high surface temperatures\\n SELECT gm.manifestation_id,\\n \'high_temperature\'::TEXT,\\n format(\'Surface temp %.1f°C exceeds threshold at manifestation near observation %s\',\\n gm.surface_temp_c, gm.observation_id)\\n FROM geothermal_manifestations gm\\n WHERE gm.surface_temp_c > 95.0; -- Near boiling\\n\\n RETURN QUERY\\n -- pH anomalies (very acidic or very alkaline)\\n SELECT gm.manifestation_id,\\n \'ph_anomaly\'::TEXT,\\n format(\'pH value %.1f outside normal range at observation %s\',\\n gm.ph_value, gm.observation_id)\\n FROM geothermal_manifestations gm\\n WHERE gm.ph_value < 2.0 OR gm.ph_value > 10.0;\\n\\n RETURN QUERY\\n -- Sudden discharge changes (comparing recent vs historical)\\n SELECT gm.manifestation_id,\\n \'discharge_change\'::TEXT,\\n format(\'Discharge rate %.2f L/s significantly changed at observation %s\',\\n gm.discharge_rate_lps, gm.observation_id)\\n FROM geothermal_manifestations gm\\n WHERE gm.discharge_rate_lps IS NOT NULL\\n AND gm.discharge_rate_lps > 50.0; -- Threshold for alert\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Function: Queue email alerts for detected anomalies\\nCREATE OR REPLACE FUNCTION queue_anomaly_alerts()\\nRETURNS INTEGER AS $$\\nDECLARE\\n alert_count INTEGER := 0;\\n rec RECORD;\\nBEGIN\\n -- Check data gaps\\n FOR rec IN SELECT * FROM detect_data_gaps() LOOP\\n INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)\\n SELECT rec.project_id, \'data_gap\', \'warning\', rec.gap_details,\\n ARRAY[p.client_email, p.receptionist_email]\\n FROM projects p WHERE p.project_id = rec.project_id;\\n alert_count := alert_count + 1;\\n END LOOP;\\n\\n -- Check geothermal anomalies\\n FOR rec IN SELECT * FROM detect_geothermal_anomalies() LOOP\\n INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)\\n SELECT o.field_trip_id::UUID, \'geothermal_anomaly\', \'critical\', rec.anomaly_details,\\n ARRAY[p.client_email, p.receptionist_email]\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n JOIN projects p ON p.project_id = ft.project_id\\n WHERE o.observation_id = (SELECT observation_id FROM geothermal_manifestations WHERE manifestation_id = rec.manifestation_id)\\n LIMIT 1;\\n alert_count := alert_count + 1;\\n END LOOP;\\n\\n RETURN alert_count;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- Trigger: Auto-queue alert on critical hazard submission\\nCREATE OR REPLACE FUNCTION alert_critical_hazard()\\nRETURNS TRIGGER AS $$\\nDECLARE\\n proj_id UUID;\\n client_email TEXT;\\n recep_email TEXT;\\nBEGIN\\n IF NEW.hazard_level IN (\'high\',\'very_high\',\'extreme\') THEN\\n SELECT ft.project_id INTO proj_id\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n WHERE o.observation_id = NEW.observation_id;\\n\\n SELECT p.client_email, p.receptionist_email INTO client_email, recep_email\\n FROM projects p WHERE p.project_id = proj_id;\\n\\n INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)\\n VALUES (proj_id, \'hazard_critical\', \'critical\',\\n format(\'Critical hazard level \\"%s\\" detected at observation %s. Immediate review required.\',\\n NEW.hazard_level, NEW.observation_id),\\n ARRAY[client_email, recep_email]);\\n END IF;\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_alert_critical_hazard\\n AFTER INSERT OR UPDATE OF hazard_level ON slope_stability\\n FOR EACH ROW EXECUTE FUNCTION alert_critical_hazard();\\n\\n-- Trigger: Auto-queue alert on geothermal anomaly detection\\nCREATE OR REPLACE FUNCTION alert_geothermal_anomaly()\\nRETURNS TRIGGER AS $$\\nDECLARE\\n proj_id UUID;\\n client_email TEXT;\\n recep_email TEXT;\\nBEGIN\\n -- High temperature alert\\n IF NEW.surface_temp_c IS NOT NULL AND NEW.surface_temp_c > 95.0 THEN\\n SELECT ft.project_id INTO proj_id\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n WHERE o.observation_id = NEW.observation_id;\\n\\n SELECT p.client_email, p.receptionist_email INTO client_email, recep_email\\n FROM projects p WHERE p.project_id = proj_id;\\n\\n INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)\\n VALUES (proj_id, \'geothermal_anomaly\', \'critical\',\\n format(\'Geothermal anomaly: Surface temperature %.1f°C detected at observation %s\',\\n NEW.surface_temp_c, NEW.observation_id),\\n ARRAY[client_email, recep_email]);\\n END IF;\\n\\n -- pH anomaly alert\\n IF NEW.ph_value IS NOT NULL AND (NEW.ph_value < 2.0 OR NEW.ph_value > 10.0) THEN\\n SELECT ft.project_id INTO proj_id\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n WHERE o.observation_id = NEW.observation_id;\\n\\n SELECT p.client_email, p.receptionist_email INTO client_email, recep_email\\n FROM projects p WHERE p.project_id = proj_id;\\n\\n INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)\\n VALUES (proj_id, \'geothermal_anomaly\', \'critical\',\\n format(\'Geothermal anomaly: pH %.1f detected at observation %s\',\\n NEW.ph_value, NEW.observation_id),\\n ARRAY[client_email, recep_email]);\\n END IF;\\n\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_alert_geothermal_anomaly\\n AFTER INSERT OR UPDATE OF surface_temp_c, ph_value ON geothermal_manifestations\\n FOR EACH ROW EXECUTE FUNCTION alert_geothermal_anomaly();\\n\\n-- -----------------------------------------------------------\\n-- 8. SPATIAL INDEX MAINTENANCE TRIGGER\\n-- Re-index after bulk loads for optimal performance\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION maintain_spatial_indexes()\\nRETURNS VOID AS $$\\nBEGIN\\n -- Re-analyze tables with spatial data for query plan optimization\\n ANALYZE observations;\\n ANALYZE discontinuity_measurements;\\n ANALYZE slope_stability;\\n ANALYZE geothermal_manifestations;\\n ANALYZE lithological_units;\\n ANALYZE landslide_inventory;\\n ANALYZE samples;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\n-- -----------------------------------------------------------\\n-- 9. OBSERVATION STATUS TRANSITION VALIDATION\\n-- Ensures proper workflow: draft → saved → submitted → reviewed → approved\\n-- -----------------------------------------------------------\\nCREATE OR REPLACE FUNCTION validate_status_transition()\\nRETURNS TRIGGER AS $$\\nBEGIN\\n IF TG_OP = \'UPDATE\' AND OLD.status != NEW.status THEN\\n -- Validate transition order\\n IF NOT (\\n (OLD.status = \'draft\' AND NEW.status IN (\'saved\',\'draft\'))\\n OR (OLD.status = \'saved\' AND NEW.status IN (\'submitted\',\'saved\',\'draft\'))\\n OR (OLD.status = \'submitted\' AND NEW.status IN (\'reviewed\',\'submitted\',\'saved\'))\\n OR (OLD.status = \'reviewed\' AND NEW.status IN (\'approved\',\'reviewed\',\'submitted\'))\\n OR (OLD.status = \'approved\' AND NEW.status = \'approved\')\\n ) THEN\\n RAISE EXCEPTION \'Invalid status transition from % to % for observation %\',\\n OLD.status, NEW.status, NEW.observation_id;\\n END IF;\\n END IF;\\n RETURN NEW;\\nEND;\\n$$ LANGUAGE plpgsql;\\n\\nCREATE TRIGGER trg_validate_status\\n BEFORE UPDATE OF status ON observations\\n FOR EACH ROW EXECUTE FUNCTION validate_status_transition();\\n\\n-- -----------------------------------------------------------\\n-- 10. DASHBOARD MATERIALIZED VIEW - Real-time Metrics\\n-- Pre-computed for low-latency dashboard rendering\\n-- -----------------------------------------------------------\\nCREATE MATERIALIZED VIEW mv_dashboard_metrics AS\\nSELECT\\n p.project_id,\\n p.project_code,\\n p.project_name,\\n p.status AS project_status,\\n -- Observation counts by theme\\n COUNT(DISTINCT o.observation_id) AS total_observations,\\n COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (\\n \'test_pit\',\'gully\',\'slope_cut\',\'river_valley\',\'natural_exposure\',\'road_cut\',\'quarry_face\'\\n )) AS surface_geological_count,\\n COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (\\n \'landslide\'\\n )) OR COUNT(DISTINCT dm.disc_id) > 0 AS structural_count,\\n COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (\\n \'geothermal_spring\',\'fumarole\',\'hot_spring\',\'geyser\',\'mineral_deposit\'\\n )) AS geothermal_count,\\n -- Sub-entity counts\\n COUNT(DISTINCT sp.soil_profile_id) AS soil_profiles,\\n COUNT(DISTINCT sh.horizon_id) AS soil_horizons,\\n COUNT(DISTINCT rd.rock_desc_id) AS rock_descriptions,\\n COUNT(DISTINCT dm.disc_id) AS discontinuity_sets,\\n COUNT(DISTINCT sl.slope_id) AS slope_assessments,\\n COUNT(DISTINCT rmc.classification_id) AS rock_mass_classifications,\\n COUNT(DISTINCT gm.manifestation_id) AS geothermal_manifestations,\\n COUNT(DISTINCT s.sample_id) AS total_samples,\\n COUNT(DISTINCT s.sample_id) FILTER (WHERE s.lab_status = \'completed\') AS samples_completed,\\n COUNT(DISTINCT ph.photo_id) AS total_photos,\\n -- Hazard summary\\n COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = \'high\') AS high_hazard_count,\\n COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = \'very_high\') AS very_high_hazard_count,\\n COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = \'extreme\') AS extreme_hazard_count,\\n -- Geothermal stats\\n MAX(gm.surface_temp_c) AS max_geothermal_temp,\\n AVG(gm.surface_temp_c) AS avg_geothermal_temp,\\n -- Landslide stats\\n COUNT(DISTINCT li.landslide_id) AS landslide_count,\\n -- Recent activity\\n MAX(o.created_at) AS last_observation_at,\\n -- Spatial extent\\n ST_Extent(o.geom) AS bbox\\nFROM projects p\\nLEFT JOIN field_trips ft ON ft.project_id = p.project_id\\nLEFT JOIN observations o ON o.field_trip_id = ft.field_trip_id\\nLEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id\\nLEFT JOIN soil_horizons sh ON sh.soil_profile_id = sp.soil_profile_id\\nLEFT JOIN rock_descriptions rd ON rd.observation_id = o.observation_id\\nLEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id\\nLEFT JOIN slope_stability sl ON sl.observation_id = o.observation_id\\nLEFT JOIN rock_mass_classifications rmc ON rmc.observation_id = o.observation_id\\nLEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id\\nLEFT JOIN samples s ON s.observation_id = o.observation_id\\nLEFT JOIN photos ph ON ph.observation_id = o.observation_id\\nLEFT JOIN landslide_inventory li ON li.project_id = p.project_id\\nGROUP BY p.project_id, p.project_code, p.project_name, p.status;\\n\\nCREATE UNIQUE INDEX idx_mv_dashboard ON mv_dashboard_metrics(project_id);\\n\\n-- Refresh strategy: every 5 minutes or on demand\\n-- SELECT pg_catalog.pg_refresh_materialized_view(\'mv_dashboard_metrics\');\\n\\n-- -----------------------------------------------------------\\n-- 11. SPATIAL CLUSTER VIEW - For Map Rendering\\n-- Pre-computed clusters for low-latency vector tile rendering\\n-- -----------------------------------------------------------\\nCREATE MATERIALIZED VIEW mv_map_clusters AS\\nSELECT\\n o.observation_id,\\n o.site_id,\\n o.observation_type,\\n o.geom,\\n o.elevation_m,\\n o.status,\\n o.created_at,\\n -- Theme flags for styling\\n CASE WHEN sp.soil_profile_id IS NOT NULL OR rd.rock_desc_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_surface_geological,\\n CASE WHEN dm.disc_id IS NOT NULL OR sl.slope_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_structural,\\n CASE WHEN gm.manifestation_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_geothermal,\\n -- Quick counts\\n (SELECT COUNT(*) FROM samples s WHERE s.observation_id = o.observation_id) AS sample_count,\\n (SELECT COUNT(*) FROM photos ph WHERE ph.observation_id = o.observation_id) AS photo_count\\nFROM observations o\\nLEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id\\nLEFT JOIN rock_descriptions rd ON rd.observation_id = o.observation_id\\nLEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id\\nLEFT JOIN slope_stability sl ON sl.observation_id = o.observation_id\\nLEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id;\\n\\nCREATE UNIQUE INDEX idx_mv_map_clusters_id ON mv_map_clusters(observation_id);\\nCREATE INDEX idx_mv_map_clusters_geom ON mv_map_clusters USING GIST(geom);\\n"}'}}]} {'role': 'tool', 'content': "File 'database/006_triggers_and_alerts.sql' created successfully.", 'tool_call_id': 'chatcmpl-tool-a26601873d485e68'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-889f11fafb2c673d', 'type': 'function', 'function': {'name': 'str_replace', 'arguments': '{"file_path": "todo.md", "old_str": "## Phase 1: Database Schema Design\\n- [ ] Design normalized PostGIS schema with all interconnected theme tables\\n- [ ] Create SQL migration files for schema, indexes, triggers, and functions\\n- [ ] Design email alert trigger logic for anomalous progress detection", "new_str": "## Phase 1: Database Schema Design\\n- [x] Design normalized PostGIS schema with all interconnected theme tables\\n- [x] Create SQL migration files for schema, indexes, triggers, and functions\\n- [x] Design email alert trigger logic for anomalous progress detection"}'}}]} {'role': 'tool', 'content': 'Replacement successful.', 'tool_call_id': 'chatcmpl-tool-889f11fafb2c673d'} {'role': 'assistant', 'content': '## Phase 2: Building the FastAPI Backend\n\nNow let me create the FastAPI application with PostGIS integration, all endpoints, and email alert service.', 'tool_calls': [{'id': 'chatcmpl-tool-9de126c6bfe8a724', 'type': 'function', 'function': {'name': 'execute_command', 'arguments': '{"command": "pip install fastapi uvicorn asyncpg sqlalchemy geoalchemy2 shapely pydantic[email] python-multipart aiosmtllib jinja2 2>&1 | tail -5","timeout": 120}'}}]} {'role': 'tool', 'content': '{"output": "ERROR: Could not find a version that satisfies the requirement aiosmtllib (from versions: none)\\nERROR: No matching distribution found for aiosmtllib\\n\\n[notice] A new release of pip is available: 24.0 -> 26.1.2\\n[notice] To update, run: pip install --upgrade pip\\n", "exit_code": 0, "cwd": "/workspace"}', 'tool_call_id': 'chatcmpl-tool-9de126c6bfe8a724'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-a7419943be1b79c1', 'type': 'function', 'function': {'name': 'execute_command', 'arguments': '{"command": "pip install fastapi uvicorn asyncpg sqlalchemy geoalchemy2 shapely pydantic[email] python-multipart aiosmtplib jinja2 2>&1 | tail -5","timeout": 120}'}}]} {'role': 'tool', 'content': '{"output": "Successfully installed MarkupSafe-3.0.3 aiosmtplib-5.1.2 asyncpg-0.31.0 dnspython-2.8.0 email-validator-2.3.0 geoalchemy2-0.20.0 jinja2-3.1.6 numpy-2.4.6 shapely-2.1.2 sqlalchemy-2.0.51\\nWARNING: Running pip as the \'root\' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\\n\\n[notice] A new release of pip is available: 24.0 -> 26.1.2\\n[notice] To update, run: pip install --upgrade pip\\n", "exit_code": 0, "cwd": "/workspace"}', 'tool_call_id': 'chatcmpl-tool-a7419943be1b79c1'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-88565ffe255f0460', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "backend/app/database.py", "file_contents": "\\"\\"\\"\\nEIGIS Database Connection & Session Management\\nPostGIS-optimized connection pool for high-concurrency ingestion\\n\\"\\"\\"\\nimport asyncpg\\nfrom typing import Optional\\nimport json\\nimport os\\n\\n# Database configuration - supports environment variables for microservices deployment\\nDB_CONFIG = {\\n \\"host\\": os.getenv(\\"DB_HOST\\", \\"localhost\\"),\\n \\"port\\": int(os.getenv(\\"DB_PORT\\", 5432)),\\n \\"database\\": os.getenv(\\"DB_NAME\\", \\"eigis_db\\"),\\n \\"user\\": os.getenv(\\"DB_USER\\", \\"eigis_admin\\"),\\n \\"password\\": os.getenv(\\"DB_PASSWORD\\", \\"eigis_secure_2026\\"),\\n \\"min_size\\": int(os.getenv(\\"DB_POOL_MIN\\", 5)),\\n \\"max_size\\": int(os.getenv(\\"DB_POOL_MAX\\", 50)),\\n \\"max_inactive_connection_lifetime\\": 300.0,\\n \\"command_timeout\\": 60,\\n}\\n\\n_pool: Optional[asyncpg.Pool] = None\\n\\n\\nasync def get_pool() -> asyncpg.Pool:\\n \\"\\"\\"Get or create the async connection pool.\\"\\"\\"\\n global _pool\\n if _pool is None:\\n _pool = await asyncpg.create_pool(**DB_CONFIG)\\n return _pool\\n\\n\\nasync def close_pool():\\n \\"\\"\\"Close the connection pool gracefully.\\"\\"\\"\\n global _pool\\n if _pool:\\n await _pool.close()\\n _pool = None\\n\\n\\nasync def execute(query: str, *args):\\n \\"\\"\\"Execute a query and return the status.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n return await conn.execute(query, *args)\\n\\n\\nasync def execute_many(query: str, args_list):\\n \\"\\"\\"Execute a query with multiple argument sets (bulk insert).\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n async with conn.transaction():\\n await conn.executemany(query, args_list)\\n\\n\\nasync def fetch(query: str, *args):\\n \\"\\"\\"Fetch multiple rows.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n return await conn.fetch(query, *args)\\n\\n\\nasync def fetchrow(query: str, *args):\\n \\"\\"\\"Fetch a single row.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n return await conn.fetchrow(query, *args)\\n\\n\\nasync def fetchval(query: str, *args):\\n \\"\\"\\"Fetch a single value.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n return await conn.fetchval(query, *args)\\n"}'}}]} {'role': 'tool', 'content': "File 'backend/app/database.py' created successfully.", 'tool_call_id': 'chatcmpl-tool-88565ffe255f0460'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-9a407bfa77b4d36f', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "backend/app/models.py", "file_contents": "\\"\\"\\"\\nEIGIS Pydantic Models - Request/Response schemas\\nAll models include GIS geometry support and validation\\n\\"\\"\\"\\nfrom pydantic import BaseModel, Field, EmailStr, validator\\nfrom typing import Optional, List, Dict, Any\\nfrom datetime import datetime, date\\nfrom enum import Enum\\n\\n\\n# ============================================================\\n# ENUMS\\n# ============================================================\\nclass ProjectStatus(str, Enum):\\n planning = \\"planning\\"\\n active = \\"active\\"\\n paused = \\"paused\\"\\n completed = \\"completed\\"\\n cancelled = \\"cancelled\\"\\n\\nclass ObservationType(str, Enum):\\n test_pit = \\"test_pit\\"\\n gully = \\"gully\\"\\n slope_cut = \\"slope_cut\\"\\n river_valley = \\"river_valley\\"\\n landslide = \\"landslide\\"\\n quarry_face = \\"quarry_face\\"\\n road_cut = \\"road_cut\\"\\n natural_exposure = \\"natural_exposure\\"\\n geothermal_spring = \\"geothermal_spring\\"\\n fumarole = \\"fumarole\\"\\n hot_spring = \\"hot_spring\\"\\n geyser = \\"geyser\\"\\n mineral_deposit = \\"mineral_deposit\\"\\n other = \\"other\\"\\n\\nclass ObservationStatus(str, Enum):\\n draft = \\"draft\\"\\n saved = \\"saved\\"\\n submitted = \\"submitted\\"\\n reviewed = \\"reviewed\\"\\n approved = \\"approved\\"\\n\\nclass HazardLevel(str, Enum):\\n low = \\"low\\"\\n moderate = \\"moderate\\"\\n high = \\"high\\"\\n very_high = \\"very_high\\"\\n extreme = \\"extreme\\"\\n\\nclass ManifestationType(str, Enum):\\n hot_spring = \\"hot_spring\\"\\n warm_spring = \\"warm_spring\\"\\n fumarole = \\"fumarole\\"\\n steaming_ground = \\"steaming_ground\\"\\n mud_pot = \\"mud_pot\\"\\n geyser = \\"geyser\\"\\n sinter_deposit = \\"sinter_deposit\\"\\n travertine = \\"travertine\\"\\n altered_ground = \\"altered_ground\\"\\n hydrothermal_breccia = \\"hydrothermal_breccia\\"\\n volcanic_vent = \\"volcanic_vent\\"\\n mineral_spring = \\"mineral_spring\\"\\n other = \\"other\\"\\n\\nclass SampleType(str, Enum):\\n disturbed_soil = \\"disturbed_soil\\"\\n undisturbed_soil = \\"undisturbed_soil\\"\\n bulk_soil = \\"bulk_soil\\"\\n rock_core = \\"rock_core\\"\\n rock_chip = \\"rock_chip\\"\\n water = \\"water\\"\\n gas = \\"gas\\"\\n geothermal_fluid = \\"geothermal_fluid\\"\\n geothermal_gas = \\"geothermal_gas\\"\\n alteration_mineral = \\"alteration_mineral\\"\\n surface_sediment = \\"surface_sediment\\"\\n vegetation = \\"vegetation\\"\\n other = \\"other\\"\\n\\n\\n# ============================================================\\n# GEOMETRY MODELS\\n# ============================================================\\nclass PointGeometry(BaseModel):\\n \\"\\"\\"GeoJSON Point geometry for spatial data.\\"\\"\\"\\n type: str = \\"Point\\"\\n coordinates: List[float] = Field(..., min_length=2, max_length=3)\\n # coordinates: [longitude, latitude] or [lon, lat, elevation]\\n crs: Optional[Dict] = {\\"type\\": \\"name\\", \\"properties\\": {\\"name\\": \\"EPSG:4326\\"}}\\n\\nclass LineGeometry(BaseModel):\\n \\"\\"\\"GeoJSON LineString geometry.\\"\\"\\"\\n type: str = \\"LineString\\"\\n coordinates: List[List[float]]\\n crs: Optional[Dict] = {\\"type\\": \\"name\\", \\"properties\\": {\\"name\\": \\"EPSG:4326\\"}}\\n\\nclass PolygonGeometry(BaseModel):\\n \\"\\"\\"GeoJSON Polygon geometry.\\"\\"\\"\\n type: str = \\"Polygon\\"\\n coordinates: List[List[List[float]]]\\n crs: Optional[Dict] = {\\"type\\": \\"name\\", \\"properties\\": {\\"name\\": \\"EPSG:4326\\"}}\\n\\n\\n# ============================================================\\n# PROJECT MODELS\\n# ============================================================\\nclass ProjectCreate(BaseModel):\\n project_code: str = Field(..., max_length=50)\\n project_name: str = Field(..., max_length=255)\\n description: Optional[str] = None\\n client_name: Optional[str] = None\\n client_email: Optional[EmailStr] = None\\n receptionist_email: Optional[EmailStr] = None\\n region: Optional[str] = None\\n start_date: date\\n end_date: Optional[date] = None\\n status: ProjectStatus = ProjectStatus.active\\n boundary_geom: Optional[Dict] = None # GeoJSON Polygon\\n\\nclass ProjectResponse(BaseModel):\\n project_id: str\\n project_code: str\\n project_name: str\\n description: Optional[str]\\n client_name: Optional[str]\\n client_email: Optional[str]\\n region: Optional[str]\\n status: str\\n start_date: date\\n end_date: Optional[date]\\n created_at: datetime\\n\\nclass ProjectMetrics(BaseModel):\\n project_id: str\\n project_code: str\\n project_name: str\\n project_status: str\\n total_observations: int\\n surface_geological_count: int\\n structural_count: int\\n geothermal_count: int\\n soil_profiles: int\\n soil_horizons: int\\n rock_descriptions: int\\n discontinuity_sets: int\\n slope_assessments: int\\n rock_mass_classifications: int\\n geothermal_manifestations: int\\n total_samples: int\\n samples_completed: int\\n total_photos: int\\n high_hazard_count: int\\n very_high_hazard_count: int\\n extreme_hazard_count: int\\n max_geothermal_temp: Optional[float]\\n avg_geothermal_temp: Optional[float]\\n landslide_count: int\\n last_observation_at: Optional[datetime]\\n\\n\\n# ============================================================\\n# FIELD TRIP MODELS\\n# ============================================================\\nclass FieldTripCreate(BaseModel):\\n project_id: str\\n trip_code: str\\n trip_date: date\\n leader_id: Optional[str] = None\\n team_members: Optional[List[str]] = None\\n weather_condition: Optional[str] = None\\n vehicle_info: Optional[str] = None\\n area_visited: Optional[str] = None\\n route_geom: Optional[Dict] = None\\n notes: Optional[str] = None\\n device_id: Optional[str] = None\\n\\nclass FieldTripResponse(BaseModel):\\n field_trip_id: str\\n project_id: str\\n trip_code: str\\n trip_date: date\\n weather_condition: Optional[str]\\n area_visited: Optional[str]\\n sync_status: str\\n created_at: datetime\\n\\n\\n# ============================================================\\n# OBSERVATION MODELS\\n# ============================================================\\nclass ObservationCreate(BaseModel):\\n field_trip_id: str\\n site_id: str = Field(..., max_length=50)\\n observation_type: ObservationType\\n exposure_type: Optional[str] = None\\n latitude: float = Field(..., ge=-90, le=90)\\n longitude: float = Field(..., ge=-180, le=180)\\n elevation_m: Optional[float] = None\\n easting: Optional[float] = None\\n northing: Optional[float] = None\\n utm_zone: str = \\"37N\\"\\n exposure_length_m: Optional[float] = None\\n exposure_height_m: Optional[float] = None\\n groundwater_level_m: Optional[float] = None\\n weather_condition: Optional[str] = None\\n excavation_method: Optional[str] = None\\n accessibility: Optional[str] = Field(None, pattern=\\"^(good|fair|poor)$\\")\\n remarks: Optional[str] = None\\n logger_id: Optional[str] = None\\n\\nclass ObservationResponse(BaseModel):\\n observation_id: str\\n field_trip_id: str\\n site_id: str\\n observation_type: str\\n latitude: float\\n longitude: float\\n elevation_m: Optional[float]\\n admin_unit: Optional[str]\\n watershed: Optional[str]\\n geological_formation: Optional[str]\\n status: str\\n created_at: datetime\\n\\nclass ObservationMapPoint(BaseModel):\\n \\"\\"\\"Lightweight model for map rendering.\\"\\"\\"\\n observation_id: str\\n site_id: str\\n observation_type: str\\n latitude: float\\n longitude: float\\n elevation_m: Optional[float]\\n has_surface_geological: bool\\n has_structural: bool\\n has_geothermal: bool\\n sample_count: int\\n photo_count: int\\n status: str\\n created_at: datetime\\n\\n\\n# ============================================================\\n# SOIL PROFILE & HORIZON MODELS\\n# ============================================================\\nclass SoilHorizonCreate(BaseModel):\\n horizon_label: str = Field(..., max_length=10)\\n depth_from_m: float\\n depth_to_m: float\\n color: Optional[str] = None\\n consistency: Optional[str] = None\\n moisture: Optional[str] = None\\n grain_size: Optional[str] = None\\n plasticity: Optional[str] = None\\n uscs_class: Optional[str] = None\\n organic_content: Optional[str] = None\\n boundary_type: Optional[str] = None\\n structure_type: Optional[str] = None\\n notes: Optional[str] = None\\n sort_order: int = 0\\n\\nclass SoilProfileCreate(BaseModel):\\n observation_id: str\\n profile_code: Optional[str] = None\\n total_depth_m: Optional[float] = None\\n groundwater_depth_m: Optional[float] = None\\n surface_condition: Optional[str] = None\\n vegetation_cover: Optional[str] = None\\n erosion_evidence: Optional[str] = None\\n drainage_class: Optional[str] = None\\n dilatancy: Optional[str] = None\\n sorting: Optional[str] = None\\n notes: Optional[str] = None\\n horizons: List[SoilHorizonCreate] = []\\n\\n\\n# ============================================================\\n# ROCK DESCRIPTION MODELS\\n# ============================================================\\nclass RockDescriptionCreate(BaseModel):\\n observation_id: str\\n rock_name: str = Field(..., max_length=100)\\n rock_type: Optional[str] = None\\n color: Optional[str] = None\\n grain_size: Optional[str] = None\\n texture: Optional[str] = None\\n weathering_grade: Optional[int] = Field(None, ge=1, le=6)\\n intact_strength: Optional[int] = Field(None, ge=1, le=7)\\n structure: Optional[str] = None\\n mineralogy: Optional[str] = None\\n alteration: Optional[str] = None\\n rock_description: Optional[str] = None\\n\\n\\n# ============================================================\\n# DISCONTINUITY MODELS\\n# ============================================================\\nclass DiscontinuityCreate(BaseModel):\\n observation_id: str\\n set_label: str = Field(..., max_length=20)\\n disc_type: str\\n dip_direction_deg: Optional[float] = Field(None, ge=0, le=360)\\n dip_deg: Optional[float] = Field(None, ge=0, le=90)\\n spacing_m: Optional[float] = None\\n aperture_mm: Optional[float] = None\\n persistence_m: Optional[float] = None\\n roughness: Optional[str] = None\\n infill_material: Optional[str] = None\\n infill_thickness_mm: Optional[float] = None\\n water_condition: Optional[str] = None\\n wall_weathering: Optional[int] = Field(None, ge=1, le=5)\\n notes: Optional[str] = None\\n\\n\\n# ============================================================\\n# SLOPE STABILITY MODELS\\n# ============================================================\\nclass SlopeStabilityCreate(BaseModel):\\n observation_id: str\\n has_existing_slope: bool = True\\n slope_height_m: Optional[float] = None\\n slope_angle_deg: Optional[float] = None\\n aspect_deg: Optional[float] = None\\n slope_form: Optional[str] = None\\n stability_condition: Optional[str] = None\\n hazard_level: HazardLevel = HazardLevel.low\\n failure_mode: Optional[str] = None\\n factor_of_safety: Optional[float] = None\\n trigger_mechanism: Optional[str] = None\\n recommended_actions: Optional[str] = None\\n mitigation_measures: Optional[str] = None\\n monitoring_required: bool = False\\n\\n\\n# ============================================================\\n-- GEOTHERMAL MODELS\\n# ============================================================\\nclass GeothermalManifestationCreate(BaseModel):\\n observation_id: str\\n manifestation_type: ManifestationType\\n surface_temp_c: Optional[float] = None\\n discharge_rate_lps: Optional[float] = None\\n ph_value: Optional[float] = Field(None, ge=-2, le=14)\\n electrical_conductivity_us: Optional[float] = None\\n total_dissolved_solids: Optional[float] = None\\n fluid_color: Optional[str] = None\\n odor: Optional[str] = None\\n deposit_type: Optional[str] = None\\n alteration_zone: Optional[str] = None\\n alteration_intensity: Optional[str] = None\\n alteration_minerals: Optional[List[str]] = None\\n structural_control: Optional[str] = None\\n host_rock: Optional[str] = None\\n elevation_m: Optional[float] = None\\n usage_current: Optional[str] = None\\n usage_potential: Optional[str] = None\\n notes: Optional[str] = None\\n\\n\\n# ============================================================\\n# SAMPLE MODELS\\n# ============================================================\\nclass SampleCreate(BaseModel):\\n observation_id: str\\n sample_code: str = Field(..., max_length=50)\\n sample_type: SampleType\\n depth_m: Optional[float] = None\\n horizon_ref: Optional[str] = None\\n weight_kg: Optional[float] = None\\n sample_condition: Optional[str] = None\\n storage_method: Optional[str] = None\\n tests_requested: Optional[List[str]] = None\\n notes: Optional[str] = None\\n\\n\\n# ============================================================\\n# DASHBOARD METRICS MODELS\\n# ============================================================\\nclass DashboardMetrics(BaseModel):\\n total_projects: int\\n active_projects: int\\n total_observations: int\\n observations_today: int\\n surface_geological_total: int\\n structural_total: int\\n geothermal_total: int\\n total_samples: int\\n samples_pending_lab: int\\n high_hazard_count: int\\n critical_alerts: int\\n landslide_count: int\\n max_geothermal_temp: Optional[float]\\n avg_geothermal_temp: Optional[float]\\n recent_observations: List[ObservationMapPoint]\\n\\nclass ThemeDistribution(BaseModel):\\n theme: str\\n count: int\\n percentage: float\\n color: str\\n\\nclass HazardDistribution(BaseModel):\\n level: str\\n count: int\\n color: str\\n\\nclass GeothermalStats(BaseModel):\\n manifestation_type: str\\n count: int\\n avg_temp: Optional[float]\\n max_temp: Optional[float]\\n avg_ph: Optional[float]\\n\\n\\n# ============================================================\\n# EMAIL ALERT MODELS\\n# ============================================================\\nclass EmailAlert(BaseModel):\\n alert_id: int\\n project_id: str\\n alert_type: str\\n severity: str\\n message: str\\n recipients: List[str]\\n is_sent: bool\\n created_at: datetime\\n"}'}}]} {'role': 'tool', 'content': "File 'backend/app/models.py' created successfully.", 'tool_call_id': 'chatcmpl-tool-9a407bfa77b4d36f'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-a987dde65d52576b', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "backend/app/services.py", "file_contents": "\\"\\"\\"\\nEIGIS Email Alert Service\\nSends SMTP email notifications for anomalous progress detection\\n\\"\\"\\"\\nimport aiosmtplib\\nfrom email.mime.text import MIMEText\\nfrom email.mime.multipart import MIMEMultipart\\nfrom typing import List, Optional\\nimport os\\nimport logging\\n\\nlogger = logging.getLogger(\\"eigis.alerts\\")\\n\\n\\nclass EmailAlertService:\\n \\"\\"\\"Async email alert service for anomalous project progress detection.\\"\\"\\"\\n\\n def __init__(self):\\n self.smtp_host = os.getenv(\\"SMTP_HOST\\", \\"smtp.gmail.com\\")\\n self.smtp_port = int(os.getenv(\\"SMTP_PORT\\", 587))\\n self.smtp_username = os.getenv(\\"SMTP_USERNAME\\", \\"eigis-alerts@gie.gov\\")\\n self.smtp_password = os.getenv(\\"SMTP_PASSWORD\\", \\"\\")\\n self.from_address = os.getenv(\\"ALERT_FROM_EMAIL\\", \\"eigis-alerts@gie.gov\\")\\n self.use_tls = os.getenv(\\"SMTP_USE_TLS\\", \\"true\\").lower() == \\"true\\"\\n\\n async def send_alert(\\n self,\\n to_addresses: List[str],\\n subject: str,\\n body_html: str,\\n cc_addresses: Optional[List[str]] = None,\\n priority: str = \\"normal\\",\\n ) -> bool:\\n \\"\\"\\"\\n Send an email alert asynchronously.\\n Returns True if sent successfully, False otherwise.\\n \\"\\"\\"\\n if not to_addresses:\\n logger.warning(\\"No recipients provided for alert\\")\\n return False\\n\\n try:\\n msg = MIMEMultipart(\\"alternative\\")\\n msg[\\"From\\"] = self.from_address\\n msg[\\"To\\"] = \\", \\".join(to_addresses)\\n msg[\\"Subject\\"] = f\\"[EIGIS Alert] {subject}\\"\\n if cc_addresses:\\n msg[\\"Cc\\"] = \\", \\".join(cc_addresses)\\n if priority == \\"critical\\":\\n msg[\\"X-Priority\\"] = \\"1\\"\\n msg[\\"Importance\\"] = \\"high\\"\\n elif priority == \\"emergency\\":\\n msg[\\"X-Priority\\"] = \\"1\\"\\n msg[\\"Importance\\"] = \\"high\\"\\n msg[\\"Subject\\"] = f\\"[EIGIS EMERGENCY] {subject}\\"\\n\\n msg.attach(MIMEText(body_html, \\"html\\"))\\n\\n smtp = aiosmtplib.SMTP(\\n hostname=self.smtp_host,\\n port=self.smtp_port,\\n use_tls=self.use_tls,\\n )\\n await smtp.connect()\\n if self.smtp_username and self.smtp_password:\\n await smtp.login(self.smtp_username, self.smtp_password)\\n await smtp.send_message(msg)\\n await smtp.quit()\\n\\n logger.info(f\\"Alert sent to {to_addresses}: {subject}\\")\\n return True\\n\\n except Exception as e:\\n logger.error(f\\"Failed to send alert: {e}\\")\\n return False\\n\\n async def send_hazard_alert(\\n self,\\n client_email: str,\\n receptionist_email: str,\\n project_code: str,\\n site_id: str,\\n hazard_level: str,\\n details: str,\\n ):\\n \\"\\"\\"Send critical hazard level alert to client and receptionist.\\"\\"\\"\\n html = f\\"\\"\\"\\n
\\n
\\n

⚠ EIGIS Critical Hazard Alert

\\n
\\n
\\n

Project: {project_code}

\\n

Site ID: {site_id}

\\n

Hazard Level: {hazard_level.upper()}

\\n

Details: {details}

\\n
\\n

\\n This alert was automatically generated by the EIGIS Engineering Geology &\\n Geohazard Information System. Immediate review and action is recommended.\\n

\\n
\\n
\\n \\"\\"\\"\\n return await self.send_alert(\\n to_addresses=[client_email],\\n cc_addresses=[receptionist_email] if receptionist_email else None,\\n subject=f\\"Critical Hazard: {hazard_level.upper()} at {site_id}\\",\\n body_html=html,\\n priority=\\"critical\\" if hazard_level in (\\"high\\", \\"very_high\\") else \\"emergency\\",\\n )\\n\\n async def send_geothermal_anomaly_alert(\\n self,\\n client_email: str,\\n receptionist_email: str,\\n project_code: str,\\n site_id: str,\\n anomaly_type: str,\\n anomaly_details: str,\\n ):\\n \\"\\"\\"Send geothermal anomaly alert.\\"\\"\\"\\n html = f\\"\\"\\"\\n
\\n
\\n

🌋 EIGIS Geothermal Anomaly Alert

\\n
\\n
\\n

Project: {project_code}

\\n

Site ID: {site_id}

\\n

Anomaly Type: {anomaly_type}

\\n

Details: {anomaly_details}

\\n
\\n

\\n This alert was automatically generated by the EIGIS Geothermal Monitoring System.\\n Please review the data and take appropriate action.\\n

\\n
\\n
\\n \\"\\"\\"\\n return await self.send_alert(\\n to_addresses=[client_email],\\n cc_addresses=[receptionist_email] if receptionist_email else None,\\n subject=f\\"Geothermal Anomaly: {anomaly_type} at {site_id}\\",\\n body_html=html,\\n priority=\\"critical\\",\\n )\\n\\n async def send_progress_delay_alert(\\n self,\\n client_email: str,\\n receptionist_email: str,\\n project_code: str,\\n project_name: str,\\n days_without_data: int,\\n ):\\n \\"\\"\\"Send project progress delay alert.\\"\\"\\"\\n html = f\\"\\"\\"\\n
\\n
\\n

📊 EIGIS Progress Delay Alert

\\n
\\n
\\n

Project: {project_code} — {project_name}

\\n

Days Without New Data: {days_without_data}

\\n

No new observations have been recorded for this active project in the past {days_without_data} days.\\n This may indicate a field data collection gap or scheduling issue.

\\n
\\n

\\n This is an automated alert from EIGIS. Please verify field trip schedules and data collection status.\\n

\\n
\\n
\\n \\"\\"\\"\\n return await self.send_alert(\\n to_addresses=[client_email],\\n cc_addresses=[receptionist_email] if receptionist_email else None,\\n subject=f\\"Progress Delay: No data for {days_without_data} days on {project_code}\\",\\n body_html=html,\\n priority=\\"warning\\",\\n )\\n\\n\\n# Singleton instance\\nemail_service = EmailAlertService()\\n"}'}}]} {'role': 'tool', 'content': "File 'backend/app/services.py' created successfully.", 'tool_call_id': 'chatcmpl-tool-a987dde65d52576b'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-92e91b55e0366f9c', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "backend/app/api.py", "file_contents": "\\"\\"\\"\\nEIGIS FastAPI Application\\nMicroservices architecture with PostGIS integration\\nHigh-concurrency endpoints for geospatial data collection & dashboard\\n\\"\\"\\"\\nfrom fastapi import FastAPI, HTTPException, Depends, Query, BackgroundTasks\\nfrom fastapi.middleware.cors import CORSMiddleware\\nfrom fastapi.responses import JSONResponse\\nfrom typing import List, Optional\\nfrom datetime import datetime, date, timedelta\\nimport json\\nimport logging\\nimport os\\n\\nfrom .models import (\\n ProjectCreate, ProjectResponse, ProjectMetrics,\\n FieldTripCreate, FieldTripResponse,\\n ObservationCreate, ObservationResponse, ObservationMapPoint,\\n SoilProfileCreate, RockDescriptionCreate,\\n DiscontinuityCreate, SlopeStabilityCreate,\\n GeothermalManifestationCreate, SampleCreate,\\n DashboardMetrics, ThemeDistribution, HazardDistribution, GeothermalStats,\\n)\\nfrom .database import get_pool, close_pool, execute, fetch, fetchrow, fetchval\\nfrom .services import email_service\\n\\n# ============================================================\\n# APP CONFIGURATION\\n# ============================================================\\napp = FastAPI(\\n title=\\"EIGIS Engineering Geology & Geohazard Information System\\",\\n description=\\"\\"\\"\\n ## EIGIS API Documentation\\n\\n A high-performance geospatial data collection and analytics API for engineering\\n geology, structural features, and geothermal monitoring.\\n\\n ### Themes\\n - **Surface Geological**: Soil profiles, rock descriptions, lithological units\\n - **Structural**: Discontinuities, slope stability, rock mass classification\\n - **Geothermal**: Hot springs, fumaroles, geothermal gradients, geochemistry\\n\\n ### Features\\n - PostGIS spatial queries with complex indexing\\n - Concurrent data ingestion with connection pooling\\n - Real-time dashboard metrics via materialized views\\n - Email alerts for anomalous progress detection\\n - Mobile data collection sync support\\n - Third-party integration endpoints\\n\\n ### Architecture\\n - Microservices-ready with stateless design\\n - PostgreSQL + PostGIS backend\\n - Async connection pooling (asyncpg)\\n - Event-driven alert triggers\\n \\"\\"\\",\\n version=\\"2.0.0\\",\\n docs_url=\\"/api/docs\\",\\n redoc_url=\\"/api/redoc\\",\\n openapi_url=\\"/api/openapi.json\\",\\n contact={\\n \\"name\\": \\"GIE Engineering Geology Division\\",\\n \\"email\\": \\"eigis@gie.gov\\",\\n \\"url\\": \\"https://www.gie.gov/eigis\\",\\n },\\n license_info={\\n \\"name\\": \\"MIT\\",\\n \\"url\\": \\"https://opensource.org/licenses/MIT\\",\\n },\\n)\\n\\n# CORS for React frontend and third-party integrations\\napp.add_middleware(\\n CORSMiddleware,\\n allow_origins=[\\n os.getenv(\\"FRONTEND_URL\\", \\"http://localhost:3000\\"),\\n os.getenv(\\"FRONTEND_URL\\", \\"http://localhost:5173\\"),\\n \\"https://eigis.pages.dev\\",\\n \\"*\\",\\n ],\\n allow_credentials=True,\\n allow_methods=[\\"*\\"],\\n allow_headers=[\\"*\\"],\\n)\\n\\n# Logging\\nlogging.basicConfig(level=logging.INFO)\\nlogger = logging.getLogger(\\"eigis.api\\")\\n\\n\\n# ============================================================\\n# STARTUP / SHUTDOWN\\n# ============================================================\\n@app.on_event(\\"startup\\")\\nasync def startup():\\n logger.info(\\"EIGIS API starting up... Initializing connection pool.\\")\\n await get_pool()\\n logger.info(\\"Connection pool ready.\\")\\n\\n\\n@app.on_event(\\"shutdown\\")\\nasync def shutdown():\\n logger.info(\\"EIGIS API shutting down... Closing connection pool.\\")\\n await close_pool()\\n\\n\\n# ============================================================\\n# HEALTH CHECK\\n# ============================================================\\n@app.get(\\"/api/health\\", tags=[\\"System\\"])\\nasync def health_check():\\n \\"\\"\\"System health check endpoint for monitoring and load balancers.\\"\\"\\"\\n try:\\n db_ok = await fetchval(\\"SELECT 1\\")\\n postgis_ver = await fetchval(\\"SELECT PostGIS_Version()\\")\\n return {\\n \\"status\\": \\"healthy\\",\\n \\"database\\": \\"connected\\" if db_ok else \\"error\\",\\n \\"postgis_version\\": postgis_ver,\\n \\"timestamp\\": datetime.utcnow().isoformat(),\\n }\\n except Exception as e:\\n return JSONResponse(\\n status_code=503,\\n content={\\"status\\": \\"unhealthy\\", \\"error\\": str(e)},\\n )\\n\\n\\n# ============================================================\\n# PROJECT ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/projects\\", response_model=ProjectResponse, tags=[\\"Projects\\"], status_code=201)\\nasync def create_project(data: ProjectCreate):\\n \\"\\"\\"Create a new EIGIS project with optional spatial boundary.\\"\\"\\"\\n geom_wkt = None\\n if data.boundary_geom:\\n coords = data.boundary_geom.get(\\"coordinates\\")\\n if coords:\\n geom_wkt = f\\"ST_MakePolygon(ST_GeomFromText(\'LINESTRING({\',\'.join([f\'{c[0]} {c[1]}\' for c in coords[0]])})\', 4326))\\"\\n\\n query = \\"\\"\\"\\n INSERT INTO projects (project_code, project_name, description, client_name,\\n client_email, receptionist_email, region, start_date, end_date, status, boundary_geom)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,\\n ST_SetSRID(ST_MakePoint(0,0), 4326))\\n RETURNING project_id, project_code, project_name, description, client_name,\\n client_email, region, status, start_date, end_date, created_at\\n \\"\\"\\"\\n row = await fetchrow(\\n query,\\n data.project_code, data.project_name, data.description, data.client_name,\\n data.client_email, data.receptionist_email, data.region,\\n data.start_date, data.end_date, data.status.value,\\n )\\n return ProjectResponse(\\n project_id=str(row[\\"project_id\\"]),\\n project_code=row[\\"project_code\\"],\\n project_name=row[\\"project_name\\"],\\n description=row[\\"description\\"],\\n client_name=row[\\"client_name\\"],\\n client_email=row[\\"client_email\\"],\\n region=row[\\"region\\"],\\n status=row[\\"status\\"],\\n start_date=row[\\"start_date\\"],\\n end_date=row[\\"end_date\\"],\\n created_at=row[\\"created_at\\"],\\n )\\n\\n\\n@app.get(\\"/api/projects\\", response_model=List[ProjectResponse], tags=[\\"Projects\\"])\\nasync def list_projects(\\n status: Optional[str] = Query(None, description=\\"Filter by project status\\"),\\n limit: int = Query(50, ge=1, le=500),\\n offset: int = Query(0, ge=0),\\n):\\n \\"\\"\\"List all projects with optional status filtering.\\"\\"\\"\\n if status:\\n rows = await fetch(\\n \\"\\"\\"SELECT project_id, project_code, project_name, description, client_name,\\n client_email, region, status, start_date, end_date, created_at\\n FROM projects WHERE status = $1\\n ORDER BY created_at DESC LIMIT $2 OFFSET $3\\"\\"\\",\\n status, limit, offset,\\n )\\n else:\\n rows = await fetch(\\n \\"\\"\\"SELECT project_id, project_code, project_name, description, client_name,\\n client_email, region, status, start_date, end_date, created_at\\n FROM projects\\n ORDER BY created_at DESC LIMIT $1 OFFSET $2\\"\\"\\",\\n limit, offset,\\n )\\n return [\\n ProjectResponse(\\n project_id=str(r[\\"project_id\\"]),\\n project_code=r[\\"project_code\\"],\\n project_name=r[\\"project_name\\"],\\n description=r[\\"description\\"],\\n client_name=r[\\"client_name\\"],\\n client_email=r[\\"client_email\\"],\\n region=r[\\"region\\"],\\n status=r[\\"status\\"],\\n start_date=r[\\"start_date\\"],\\n end_date=r[\\"end_date\\"],\\n created_at=r[\\"created_at\\"],\\n )\\n for r in rows\\n ]\\n\\n\\n@app.get(\\"/api/projects/{project_id}/metrics\\", response_model=ProjectMetrics, tags=[\\"Projects\\"])\\nasync def get_project_metrics(project_id: str):\\n \\"\\"\\"Get real-time project metrics from materialized view.\\"\\"\\"\\n row = await fetchrow(\\n \\"SELECT * FROM mv_dashboard_metrics WHERE project_id = $1\\",\\n project_id,\\n )\\n if not row:\\n raise HTTPException(404, \\"Project not found\\")\\n return ProjectMetrics(\\n project_id=str(row[\\"project_id\\"]),\\n project_code=row[\\"project_code\\"],\\n project_name=row[\\"project_name\\"],\\n project_status=row[\\"project_status\\"],\\n total_observations=row[\\"total_observations\\"] or 0,\\n surface_geological_count=row[\\"surface_geological_count\\"] or 0,\\n structural_count=row[\\"structural_count\\"] or 0,\\n geothermal_count=row[\\"geothermal_count\\"] or 0,\\n soil_profiles=row[\\"soil_profiles\\"] or 0,\\n soil_horizons=row[\\"soil_horizons\\"] or 0,\\n rock_descriptions=row[\\"rock_descriptions\\"] or 0,\\n discontinuity_sets=row[\\"discontinuity_sets\\"] or 0,\\n slope_assessments=row[\\"slope_assessments\\"] or 0,\\n rock_mass_classifications=row[\\"rock_mass_classifications\\"] or 0,\\n geothermal_manifestations=row[\\"geothermal_manifestations\\"] or 0,\\n total_samples=row[\\"total_samples\\"] or 0,\\n samples_completed=row[\\"samples_completed\\"] or 0,\\n total_photos=row[\\"total_photos\\"] or 0,\\n high_hazard_count=row[\\"high_hazard_count\\"] or 0,\\n very_high_hazard_count=row[\\"very_high_hazard_count\\"] or 0,\\n extreme_hazard_count=row[\\"extreme_hazard_count\\"] or 0,\\n max_geothermal_temp=row[\\"max_geothermal_temp\\"],\\n avg_geothermal_temp=row[\\"avg_geothermal_temp\\"],\\n landslide_count=row[\\"landslide_count\\"] or 0,\\n last_observation_at=row[\\"last_observation_at\\"],\\n )\\n\\n\\n# ============================================================\\n# FIELD TRIP ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/field-trips\\", response_model=FieldTripResponse, tags=[\\"Field Trips\\"], status_code=201)\\nasync def create_field_trip(data: FieldTripCreate):\\n \\"\\"\\"Create a new field trip record.\\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO field_trips (project_id, trip_code, trip_date, weather_condition,\\n area_visited, device_id)\\n VALUES ($1, $2, $3, $4, $5, $6)\\n RETURNING field_trip_id, project_id, trip_code, trip_date, weather_condition,\\n area_visited, sync_status, created_at\\n \\"\\"\\"\\n row = await fetchrow(\\n query, data.project_id, data.trip_code, data.trip_date,\\n data.weather_condition, data.area_visited, data.device_id,\\n )\\n return FieldTripResponse(\\n field_trip_id=str(row[\\"field_trip_id\\"]),\\n project_id=str(row[\\"project_id\\"]),\\n trip_code=row[\\"trip_code\\"],\\n trip_date=row[\\"trip_date\\"],\\n weather_condition=row[\\"weather_condition\\"],\\n area_visited=row[\\"area_visited\\"],\\n sync_status=row[\\"sync_status\\"],\\n created_at=row[\\"created_at\\"],\\n )\\n\\n\\n# ============================================================\\n# OBSERVATION ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/observations\\", response_model=ObservationResponse, tags=[\\"Observations\\"], status_code=201)\\nasync def create_observation(data: ObservationCreate, background_tasks: BackgroundTasks):\\n \\"\\"\\"\\n Create a new observation with spatial geometry.\\n Auto-populates admin unit, watershed, and geological formation via triggers.\\n \\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO observations (\\n field_trip_id, site_id, observation_type, exposure_type,\\n geom, elevation_m, easting, northing, utm_zone,\\n exposure_length_m, exposure_height_m, groundwater_level_m,\\n weather_condition, excavation_method, accessibility, remarks, logger_id\\n ) VALUES (\\n $1, $2, $3, $4,\\n ST_SetSRID(ST_MakePoint($5, $6), 4326),\\n $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18\\n )\\n RETURNING observation_id, field_trip_id, site_id, observation_type,\\n ST_Y(geom) as latitude, ST_X(geom) as longitude,\\n elevation_m, admin_unit, watershed, geological_formation,\\n status, created_at\\n \\"\\"\\"\\n row = await fetchrow(\\n query,\\n data.field_trip_id, data.site_id, data.observation_type.value,\\n data.exposure_type,\\n data.longitude, data.latitude, # ST_MakePoint(lon, lat)\\n data.elevation_m, data.easting, data.northing, data.utm_zone,\\n data.exposure_length_m, data.exposure_height_m, data.groundwater_level_m,\\n data.weather_condition, data.excavation_method, data.accessibility,\\n data.remarks, data.logger_id,\\n )\\n return ObservationResponse(\\n observation_id=str(row[\\"observation_id\\"]),\\n field_trip_id=str(row[\\"field_trip_id\\"]),\\n site_id=row[\\"site_id\\"],\\n observation_type=row[\\"observation_type\\"],\\n latitude=float(row[\\"latitude\\"]),\\n longitude=float(row[\\"longitude\\"]),\\n elevation_m=row[\\"elevation_m\\"],\\n admin_unit=row[\\"admin_unit\\"],\\n watershed=row[\\"watershed\\"],\\n geological_formation=row[\\"geological_formation\\"],\\n status=row[\\"status\\"],\\n created_at=row[\\"created_at\\"],\\n )\\n\\n\\n@app.get(\\"/api/observations\\", response_model=List[ObservationResponse], tags=[\\"Observations\\"])\\nasync def list_observations(\\n project_id: Optional[str] = None,\\n observation_type: Optional[str] = None,\\n status: Optional[str] = None,\\n bbox: Optional[str] = Query(None, description=\\"Bounding box: xmin,ymin,xmax,ymax\\"),\\n limit: int = Query(100, ge=1, le=1000),\\n offset: int = Query(0, ge=0),\\n):\\n \\"\\"\\"List observations with spatial and attribute filtering.\\"\\"\\"\\n conditions = []\\n params = []\\n idx = 1\\n\\n if project_id:\\n conditions.append(f\\"ft.project_id = ${idx}\\")\\n params.append(project_id)\\n idx += 1\\n if observation_type:\\n conditions.append(f\\"o.observation_type = ${idx}\\")\\n params.append(observation_type)\\n idx += 1\\n if status:\\n conditions.append(f\\"o.status = ${idx}\\")\\n params.append(status)\\n idx += 1\\n if bbox:\\n parts = bbox.split(\\",\\")\\n if len(parts) == 4:\\n conditions.append(\\n f\\"o.geom && ST_MakeEnvelope(${idx}, ${idx+1}, ${idx+2}, ${idx+3}, 4326)\\"\\n )\\n params.extend([float(p) for p in parts])\\n idx += 4\\n\\n where = \\" AND \\".join(conditions) if conditions else \\"TRUE\\"\\n\\n query = f\\"\\"\\"\\n SELECT o.observation_id, o.field_trip_id, o.site_id, o.observation_type,\\n ST_Y(o.geom) as latitude, ST_X(o.geom) as longitude,\\n o.elevation_m, o.admin_unit, o.watershed, o.geological_formation,\\n o.status, o.created_at\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n WHERE {where}\\n ORDER BY o.created_at DESC\\n LIMIT ${idx} OFFSET ${idx+1}\\n \\"\\"\\"\\n params.extend([limit, offset])\\n\\n rows = await fetch(query, *params)\\n return [\\n ObservationResponse(\\n observation_id=str(r[\\"observation_id\\"]),\\n field_trip_id=str(r[\\"field_trip_id\\"]),\\n site_id=r[\\"site_id\\"],\\n observation_type=r[\\"observation_type\\"],\\n latitude=float(r[\\"latitude\\"]),\\n longitude=float(r[\\"longitude\\"]),\\n elevation_m=r[\\"elevation_m\\"],\\n admin_unit=r[\\"admin_unit\\"],\\n watershed=r[\\"watershed\\"],\\n geological_formation=r[\\"geological_formation\\"],\\n status=r[\\"status\\"],\\n created_at=r[\\"created_at\\"],\\n )\\n for r in rows\\n ]\\n\\n\\n@app.get(\\"/api/observations/map\\", response_model=List[ObservationMapPoint], tags=[\\"WebGIS Map\\"])\\nasync def get_map_points(\\n project_id: Optional[str] = None,\\n theme: Optional[str] = Query(None, description=\\"Filter: surface_geological, structural, geothermal\\"),\\n bbox: Optional[str] = Query(None, description=\\"Bounding box filter for vector tiles\\"),\\n):\\n \\"\\"\\"\\n Get observation points for WebGIS map rendering.\\n Optimized for low-latency vector tile generation.\\n Includes theme flags for client-side styling.\\n \\"\\"\\"\\n conditions = []\\n params = []\\n idx = 1\\n\\n if project_id:\\n conditions.append(f\\"ft.project_id = ${idx}\\")\\n params.append(project_id)\\n idx += 1\\n\\n if bbox:\\n parts = bbox.split(\\",\\")\\n if len(parts) == 4:\\n conditions.append(\\n f\\"o.geom && ST_MakeEnvelope(${idx}, ${idx+1}, ${idx+2}, ${idx+3}, 4326)\\"\\n )\\n params.extend([float(p) for p in parts])\\n idx += 4\\n\\n where = \\" AND \\".join(conditions) if conditions else \\"TRUE\\"\\n\\n query = f\\"\\"\\"\\n SELECT\\n o.observation_id, o.site_id, o.observation_type,\\n ST_Y(o.geom) as latitude, ST_X(o.geom) as longitude,\\n o.elevation_m, o.status, o.created_at,\\n CASE WHEN sp.soil_profile_id IS NOT NULL OR rd.rock_desc_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_surface_geological,\\n CASE WHEN dm.disc_id IS NOT NULL OR sl.slope_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_structural,\\n CASE WHEN gm.manifestation_id IS NOT NULL\\n THEN TRUE ELSE FALSE END AS has_geothermal,\\n (SELECT COUNT(*) FROM samples s WHERE s.observation_id = o.observation_id) AS sample_count,\\n (SELECT COUNT(*) FROM photos ph WHERE ph.observation_id = o.observation_id) AS photo_count\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n LEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id\\n LEFT JOIN rock_descriptions rd ON rd.observation_id = o.observation_id\\n LEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id\\n LEFT JOIN slope_stability sl ON sl.observation_id = o.observation_id\\n LEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id\\n WHERE {where}\\n ORDER BY o.created_at DESC\\n \\"\\"\\"\\n rows = await fetch(query, *params)\\n\\n results = []\\n for r in rows:\\n point = ObservationMapPoint(\\n observation_id=str(r[\\"observation_id\\"]),\\n site_id=r[\\"site_id\\"],\\n observation_type=r[\\"observation_type\\"],\\n latitude=float(r[\\"latitude\\"]),\\n longitude=float(r[\\"longitude\\"]),\\n elevation_m=r[\\"elevation_m\\"],\\n has_surface_geological=r[\\"has_surface_geological\\"],\\n has_structural=r[\\"has_structural\\"],\\n has_geothermal=r[\\"has_geothermal\\"],\\n sample_count=int(r[\\"sample_count\\"]),\\n photo_count=int(r[\\"photo_count\\"]),\\n status=r[\\"status\\"],\\n created_at=r[\\"created_at\\"],\\n )\\n # Apply theme filter if specified\\n if theme == \\"surface_geological\\" and not point.has_surface_geological:\\n continue\\n if theme == \\"structural\\" and not point.has_structural:\\n continue\\n if theme == \\"geothermal\\" and not point.has_geothermal:\\n continue\\n results.append(point)\\n\\n return results\\n\\n\\n# ============================================================\\n# SURFACE GEOLOGICAL ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/soil-profiles\\", tags=[\\"Surface Geological\\"], status_code=201)\\nasync def create_soil_profile(data: SoilProfileCreate):\\n \\"\\"\\"Create a soil profile with multiple horizons for an observation.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n async with conn.transaction():\\n # Create profile\\n profile_id = await conn.fetchval(\\n \\"\\"\\"INSERT INTO soil_profiles\\n (observation_id, profile_code, total_depth_m, groundwater_depth_m,\\n dilatancy, sorting, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7)\\n RETURNING soil_profile_id\\"\\"\\",\\n data.observation_id, data.profile_code, data.total_depth_m,\\n data.groundwater_depth_m, data.dilatancy, data.sorting, data.notes,\\n )\\n\\n # Create horizons\\n for h in data.horizons:\\n await conn.execute(\\n \\"\\"\\"INSERT INTO soil_horizons\\n (soil_profile_id, horizon_label, depth_from_m, depth_to_m,\\n color, consistency, moisture, grain_size, plasticity,\\n uscs_class, organic_content, boundary_type, structure_type,\\n notes, sort_order)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)\\"\\"\\",\\n profile_id, h.horizon_label, h.depth_from_m, h.depth_to_m,\\n h.color, h.consistency, h.moisture, h.grain_size, h.plasticity,\\n h.uscs_class, h.organic_content, h.boundary_type, h.structure_type,\\n h.notes, h.sort_order,\\n )\\n\\n return {\\"soil_profile_id\\": str(profile_id), \\"horizons_created\\": len(data.horizons)}\\n\\n\\n@app.post(\\"/api/rock-descriptions\\", tags=[\\"Surface Geological\\"], status_code=201)\\nasync def create_rock_description(data: RockDescriptionCreate):\\n \\"\\"\\"Create a rock description for an observation.\\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO rock_descriptions (observation_id, rock_name, rock_type, color,\\n grain_size, texture, weathering_grade, intact_strength, structure,\\n mineralogy, alteration, rock_description)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)\\n RETURNING rock_desc_id\\n \\"\\"\\"\\n rid = await fetchval(\\n query, data.observation_id, data.rock_name, data.rock_type, data.color,\\n data.grain_size, data.texture, data.weathering_grade, data.intact_strength,\\n data.structure, data.mineralogy, data.alteration, data.rock_description,\\n )\\n return {\\"rock_desc_id\\": str(rid)}\\n\\n\\n# ============================================================\\n# STRUCTURAL ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/discontinuities\\", tags=[\\"Structural\\"], status_code=201)\\nasync def create_discontinuity(data: DiscontinuityCreate):\\n \\"\\"\\"Add a discontinuity measurement to an observation.\\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO discontinuity_measurements\\n (observation_id, set_label, disc_type, dip_direction_deg, dip_deg,\\n spacing_m, aperture_mm, persistence_m, roughness, infill_material,\\n infill_thickness_mm, water_condition, wall_weathering, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)\\n RETURNING disc_id\\n \\"\\"\\"\\n did = await fetchval(\\n query, data.observation_id, data.set_label, data.disc_type,\\n data.dip_direction_deg, data.dip_deg, data.spacing_m, data.aperture_mm,\\n data.persistence_m, data.roughness, data.infill_material,\\n data.infill_thickness_mm, data.water_condition, data.wall_weathering,\\n data.notes,\\n )\\n return {\\"disc_id\\": str(did)}\\n\\n\\n@app.post(\\"/api/discontinuities/bulk\\", tags=[\\"Structural\\"], status_code=201)\\nasync def create_discontinuities_bulk(data: List[DiscontinuityCreate]):\\n \\"\\"\\"Bulk insert discontinuity measurements for efficient concurrent ingestion.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n async with conn.transaction():\\n ids = []\\n for d in data:\\n rid = await conn.fetchval(\\n \\"\\"\\"INSERT INTO discontinuity_measurements\\n (observation_id, set_label, disc_type, dip_direction_deg, dip_deg,\\n spacing_m, aperture_mm, persistence_m, roughness, infill_material,\\n infill_thickness_mm, water_condition, wall_weathering, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)\\n RETURNING disc_id\\"\\"\\",\\n d.observation_id, d.set_label, d.disc_type,\\n d.dip_direction_deg, d.dip_deg, d.spacing_m, d.aperture_mm,\\n d.persistence_m, d.roughness, d.infill_material,\\n d.infill_thickness_mm, d.water_condition, d.wall_weathering,\\n d.notes,\\n )\\n ids.append(str(rid))\\n return {\\"created\\": len(ids), \\"disc_ids\\": ids}\\n\\n\\n@app.post(\\"/api/slope-stability\\", tags=[\\"Structural\\"], status_code=201)\\nasync def create_slope_stability(data: SlopeStabilityCreate, background_tasks: BackgroundTasks):\\n \\"\\"\\"\\n Create slope stability assessment. Triggers email alert for high hazard levels.\\n \\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO slope_stability\\n (observation_id, has_existing_slope, slope_height_m, slope_angle_deg,\\n aspect_deg, slope_form, stability_condition, hazard_level, failure_mode,\\n factor_of_safety, trigger_mechanism, recommended_actions,\\n mitigation_measures, monitoring_required)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)\\n RETURNING slope_id\\n \\"\\"\\"\\n sid = await fetchval(\\n query, data.observation_id, data.has_existing_slope, data.slope_height_m,\\n data.slope_angle_deg, data.aspect_deg, data.slope_form,\\n data.stability_condition, data.hazard_level.value, data.failure_mode,\\n data.factor_of_safety, data.trigger_mechanism, data.recommended_actions,\\n data.mitigation_measures, data.monitoring_required,\\n )\\n\\n # If hazard is high+, trigger alert in background\\n if data.hazard_level in (HazardLevel.high, HazardLevel.very_high, HazardLevel.extreme):\\n background_tasks.add_task(\\n _send_hazard_alert, data.observation_id, data.hazard_level.value\\n )\\n\\n return {\\"slope_id\\": str(sid)}\\n\\n\\nasync def _send_hazard_alert(observation_id: str, hazard_level: str):\\n \\"\\"\\"Background task to send hazard alert emails.\\"\\"\\"\\n try:\\n row = await fetchrow(\\n \\"\\"\\"SELECT p.client_email, p.receptionist_email, p.project_code, o.site_id\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n JOIN projects p ON p.project_id = ft.project_id\\n WHERE o.observation_id = $1\\"\\"\\",\\n observation_id,\\n )\\n if row and row[\\"client_email\\"]:\\n await email_service.send_hazard_alert(\\n client_email=row[\\"client_email\\"],\\n receptionist_email=row[\\"receptionist_email\\"] or \\"\\",\\n project_code=row[\\"project_code\\"],\\n site_id=row[\\"site_id\\"],\\n hazard_level=hazard_level,\\n details=f\\"Slope stability assessment indicates {hazard_level} hazard level requiring immediate attention.\\",\\n )\\n except Exception as e:\\n logger.error(f\\"Failed to send hazard alert: {e}\\")\\n\\n\\n# ============================================================\\n# GEOTHERMAL ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/geothermal-manifestations\\", tags=[\\"Geothermal\\"], status_code=201)\\nasync def create_geothermal_manifestation(\\n data: GeothermalManifestationCreate, background_tasks: BackgroundTasks\\n):\\n \\"\\"\\"\\n Create a geothermal manifestation record. Triggers anomaly alerts\\n for high temperatures or extreme pH values.\\n \\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO geothermal_manifestations\\n (observation_id, manifestation_type, surface_temp_c, discharge_rate_lps,\\n ph_value, electrical_conductivity_us, total_dissolved_solids,\\n fluid_color, odor, deposit_type, alteration_zone, alteration_intensity,\\n structural_control, host_rock, elevation_m, usage_current, usage_potential, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)\\n RETURNING manifestation_id\\n \\"\\"\\"\\n mid = await fetchval(\\n query, data.observation_id, data.manifestation_type.value,\\n data.surface_temp_c, data.discharge_rate_lps, data.ph_value,\\n data.electrical_conductivity_us, data.total_dissolved_solids,\\n data.fluid_color, data.odor, data.deposit_type, data.alteration_zone,\\n data.alteration_intensity, data.structural_control, data.host_rock,\\n data.elevation_m, data.usage_current, data.usage_potential, data.notes,\\n )\\n\\n # Check for anomalies\\n anomalies = []\\n if data.surface_temp_c and data.surface_temp_c > 95.0:\\n anomalies.append(\\"high_temperature\\")\\n if data.ph_value and (data.ph_value < 2.0 or data.ph_value > 10.0):\\n anomalies.append(\\"ph_anomaly\\")\\n\\n if anomalies:\\n background_tasks.add_task(\\n _send_geothermal_alert, data.observation_id, anomalies\\n )\\n\\n return {\\"manifestation_id\\": str(mid), \\"anomalies_detected\\": anomalies}\\n\\n\\nasync def _send_geothermal_alert(observation_id: str, anomalies: List[str]):\\n \\"\\"\\"Background task to send geothermal anomaly alerts.\\"\\"\\"\\n try:\\n row = await fetchrow(\\n \\"\\"\\"SELECT p.client_email, p.receptionist_email, p.project_code, o.site_id\\n FROM observations o\\n JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id\\n JOIN projects p ON p.project_id = ft.project_id\\n WHERE o.observation_id = $1\\"\\"\\",\\n observation_id,\\n )\\n if row and row[\\"client_email\\"]:\\n for anomaly in anomalies:\\n desc = {\\n \\"high_temperature\\": \\"Surface temperature exceeds 95°C threshold\\",\\n \\"ph_anomaly\\": \\"pH value outside normal range (2-10)\\",\\n }.get(anomaly, anomaly)\\n\\n await email_service.send_geothermal_anomaly_alert(\\n client_email=row[\\"client_email\\"],\\n receptionist_email=row[\\"receptionist_email\\"] or \\"\\",\\n project_code=row[\\"project_code\\"],\\n site_id=row[\\"site_id\\"],\\n anomaly_type=anomaly,\\n anomaly_details=desc,\\n )\\n except Exception as e:\\n logger.error(f\\"Failed to send geothermal alert: {e}\\")\\n\\n\\n# ============================================================\\n# SAMPLING ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/samples\\", tags=[\\"Sampling\\"], status_code=201)\\nasync def create_sample(data: SampleCreate):\\n \\"\\"\\"Create a new sample linked to an observation.\\"\\"\\"\\n query = \\"\\"\\"\\n INSERT INTO samples\\n (observation_id, sample_code, sample_type, depth_m, horizon_ref,\\n weight_kg, sample_condition, storage_method, tests_requested, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)\\n RETURNING sample_id\\n \\"\\"\\"\\n sid = await fetchval(\\n query, data.observation_id, data.sample_code, data.sample_type.value,\\n data.depth_m, data.horizon_ref, data.weight_kg, data.sample_condition,\\n data.storage_method, data.tests_requested, data.notes,\\n )\\n return {\\"sample_id\\": str(sid)}\\n\\n\\n@app.post(\\"/api/samples/bulk\\", tags=[\\"Sampling\\"], status_code=201)\\nasync def create_samples_bulk(data: List[SampleCreate]):\\n \\"\\"\\"Bulk insert samples for efficient concurrent ingestion.\\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n async with conn.transaction():\\n ids = []\\n for s in data:\\n rid = await conn.fetchval(\\n \\"\\"\\"INSERT INTO samples\\n (observation_id, sample_code, sample_type, depth_m, horizon_ref,\\n weight_kg, sample_condition, storage_method, tests_requested, notes)\\n VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)\\n RETURNING sample_id\\"\\"\\",\\n s.observation_id, s.sample_code, s.sample_type.value,\\n s.depth_m, s.horizon_ref, s.weight_kg, s.sample_condition,\\n s.storage_method, s.tests_requested, s.notes,\\n )\\n ids.append(str(rid))\\n return {\\"created\\": len(ids), \\"sample_ids\\": ids}\\n\\n\\n# ============================================================\\n# DASHBOARD ENDPOINTS\\n# ============================================================\\n@app.get(\\"/api/dashboard/metrics\\", response_model=DashboardMetrics, tags=[\\"Dashboard\\"])\\nasync def get_dashboard_metrics(project_id: Optional[str] = None):\\n \\"\\"\\"\\n Get real-time dashboard metrics for visualization.\\n Uses materialized views for low-latency rendering.\\n \\"\\"\\"\\n # Refresh materialized view for latest data\\n await execute(\\"REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_metrics\\")\\n\\n if project_id:\\n row = await fetchrow(\\n \\"SELECT * FROM mv_dashboard_metrics WHERE project_id = $1\\", project_id\\n )\\n else:\\n # Aggregate across all projects\\n rows = await fetch(\\"SELECT * FROM mv_dashboard_metrics\\")\\n\\n # Get recent observations for map\\n recent = await fetch(\\n \\"\\"\\"SELECT o.observation_id, o.site_id, o.observation_type,\\n ST_Y(o.geom) as latitude, ST_X(o.geom) as longitude,\\n o.elevation_m, o.status, o.created_at,\\n CASE WHEN sp.soil_profile_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_surface_geological,\\n CASE WHEN dm.disc_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_structural,\\n CASE WHEN gm.manifestation_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_geothermal,\\n 0 AS sample_count, 0 AS photo_count\\n FROM observations o\\n LEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id\\n LEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id\\n LEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id\\n ORDER BY o.created_at DESC LIMIT 50\\"\\"\\"\\n )\\n\\n recent_points = [\\n ObservationMapPoint(\\n observation_id=str(r[\\"observation_id\\"]),\\n site_id=r[\\"site_id\\"],\\n observation_type=r[\\"observation_type\\"],\\n latitude=float(r[\\"latitude\\"]),\\n longitude=float(r[\\"longitude\\"]),\\n elevation_m=r[\\"elevation_m\\"],\\n has_surface_geological=r[\\"has_surface_geological\\"],\\n has_structural=r[\\"has_structural\\"],\\n has_geothermal=r[\\"has_geothermal\\"],\\n sample_count=r[\\"sample_count\\"],\\n photo_count=r[\\"photo_count\\"],\\n status=r[\\"status\\"],\\n created_at=r[\\"created_at\\"],\\n )\\n for r in recent\\n ]\\n\\n # Compute aggregate metrics\\n if project_id and row:\\n metrics = row\\n else:\\n # Sum across all projects\\n agg = await fetchrow(\\"\\"\\"\\n SELECT\\n COUNT(*) as total_projects,\\n COUNT(*) FILTER (WHERE project_status = \'active\') as active_projects,\\n SUM(total_observations) as total_observations,\\n SUM(surface_geological_count) as surface_geological_total,\\n SUM(structural_count) as structural_total,\\n SUM(geothermal_count) as geothermal_total,\\n SUM(total_samples) as total_samples,\\n SUM(samples_completed) as samples_completed,\\n SUM(high_hazard_count) as high_hazard_count,\\n SUM(landslide_count) as landslide_count,\\n MAX(max_geothermal_temp) as max_geothermal_temp,\\n AVG(avg_geothermal_temp) as avg_geothermal_temp\\n FROM mv_dashboard_metrics\\n \\"\\"\\")\\n metrics = agg or {}\\n\\n # Count today\'s observations\\n obs_today = await fetchval(\\n \\"SELECT COUNT(*) FROM observations WHERE created_at::date = CURRENT_DATE\\"\\n )\\n\\n # Count pending lab samples\\n pending_lab = await fetchval(\\n \\"SELECT COUNT(*) FROM samples WHERE lab_status = \'pending\'\\"\\n )\\n\\n # Count critical alerts\\n critical_alerts = await fetchval(\\n \\"SELECT COUNT(*) FROM alert_queue WHERE is_sent = FALSE AND severity IN (\'critical\',\'emergency\')\\"\\n )\\n\\n return DashboardMetrics(\\n total_projects=int(metrics.get(\\"total_projects\\", 0) or 0),\\n active_projects=int(metrics.get(\\"active_projects\\", 0) or 0),\\n total_observations=int(metrics.get(\\"total_observations\\", 0) or 0),\\n observations_today=obs_today or 0,\\n surface_geological_total=int(metrics.get(\\"surface_geological_total\\", 0) or 0),\\n structural_total=int(metrics.get(\\"structural_total\\", 0) or 0),\\n geothermal_total=int(metrics.get(\\"geothermal_total\\", 0) or 0),\\n total_samples=int(metrics.get(\\"total_samples\\", 0) or 0),\\n samples_pending_lab=pending_lab or 0,\\n high_hazard_count=int(metrics.get(\\"high_hazard_count\\", 0) or 0),\\n critical_alerts=critical_alerts or 0,\\n landslide_count=int(metrics.get(\\"landslide_count\\", 0) or 0),\\n max_geothermal_temp=metrics.get(\\"max_geothermal_temp\\"),\\n avg_geothermal_temp=metrics.get(\\"avg_geothermal_temp\\"),\\n recent_observations=recent_points,\\n )\\n\\n\\n@app.get(\\"/api/dashboard/theme-distribution\\", response_model=List[ThemeDistribution], tags=[\\"Dashboard\\"])\\nasync def get_theme_distribution():\\n \\"\\"\\"Get observation distribution across themes for pie/donut charts.\\"\\"\\"\\n rows = await fetch(\\"\\"\\"\\n SELECT\\n SUM(surface_geological_count) as surface_geological,\\n SUM(structural_count) as structural,\\n SUM(geothermal_count) as geothermal\\n FROM mv_dashboard_metrics\\n \\"\\"\\")\\n if not rows:\\n return []\\n\\n r = rows[0] if rows else {}\\n total = (r.get(\\"surface_geological\\", 0) or 0) + (r.get(\\"structural\\", 0) or 0) + (r.get(\\"geothermal\\", 0) or 0)\\n if total == 0:\\n total = 1\\n\\n return [\\n ThemeDistribution(\\n theme=\\"Surface Geological\\",\\n count=r.get(\\"surface_geological\\", 0) or 0,\\n percentage=round((r.get(\\"surface_geological\\", 0) or 0) / total * 100, 1),\\n color=\\"#0F6E56\\",\\n ),\\n ThemeDistribution(\\n theme=\\"Structural\\",\\n count=r.get(\\"structural\\", 0) or 0,\\n percentage=round((r.get(\\"structural\\", 0) or 0) / total * 100, 1),\\n color=\\"#BA7517\\",\\n ),\\n ThemeDistribution(\\n theme=\\"Geothermal\\",\\n count=r.get(\\"geothermal\\", 0) or 0,\\n percentage=round((r.get(\\"geothermal\\", 0) or 0) / total * 100, 1),\\n color=\\"#DC2626\\",\\n ),\\n ]\\n\\n\\n@app.get(\\"/api/dashboard/hazard-distribution\\", response_model=List[HazardDistribution], tags=[\\"Dashboard\\"])\\nasync def get_hazard_distribution():\\n \\"\\"\\"Get hazard level distribution for risk charts.\\"\\"\\"\\n rows = await fetch(\\"\\"\\"\\n SELECT hazard_level, COUNT(*) as count\\n FROM slope_stability\\n GROUP BY hazard_level\\n ORDER BY count DESC\\n \\"\\"\\")\\n colors = {\\"low\\": \\"#22C55E\\", \\"moderate\\": \\"#F59E0B\\", \\"high\\": \\"#F97316\\", \\"very_high\\": \\"#EF4444\\", \\"extreme\\": \\"#7F1D1D\\"}\\n return [\\n HazardDistribution(level=r[\\"hazard_level\\"], count=r[\\"count\\"], color=colors.get(r[\\"hazard_level\\"], \\"#999\\"))\\n for r in rows\\n ]\\n\\n\\n@app.get(\\"/api/dashboard/geothermal-stats\\", response_model=List[GeothermalStats], tags=[\\"Dashboard\\"])\\nasync def get_geothermal_stats():\\n \\"\\"\\"Get geothermal manifestation statistics.\\"\\"\\"\\n rows = await fetch(\\"\\"\\"\\n SELECT\\n manifestation_type,\\n COUNT(*) as count,\\n AVG(surface_temp_c) as avg_temp,\\n MAX(surface_temp_c) as max_temp,\\n AVG(ph_value) as avg_ph\\n FROM geothermal_manifestations\\n GROUP BY manifestation_type\\n ORDER BY count DESC\\n \\"\\"\\")\\n return [\\n GeothermalStats(\\n manifestation_type=r[\\"manifestation_type\\"],\\n count=r[\\"count\\"],\\n avg_temp=r[\\"avg_temp\\"],\\n max_temp=r[\\"max_temp\\"],\\n avg_ph=r[\\"avg_ph\\"],\\n )\\n for r in rows\\n ]\\n\\n\\n# ============================================================\\n-- ALERT ENDPOINTS\\n# ============================================================\\n@app.get(\\"/api/alerts\\", tags=[\\"Alerts\\"])\\nasync def list_alerts(\\n is_sent: Optional[bool] = None,\\n severity: Optional[str] = None,\\n limit: int = Query(50, ge=1, le=200),\\n):\\n \\"\\"\\"List alert queue entries.\\"\\"\\"\\n conditions = []\\n params = []\\n idx = 1\\n\\n if is_sent is not None:\\n conditions.append(f\\"is_sent = ${idx}\\")\\n params.append(is_sent)\\n idx += 1\\n if severity:\\n conditions.append(f\\"severity = ${idx}\\")\\n params.append(severity)\\n idx += 1\\n\\n where = \\" AND \\".join(conditions) if conditions else \\"TRUE\\"\\n query = f\\"\\"\\"\\n SELECT alert_id, project_id, alert_type, severity, message, recipients, is_sent, created_at\\n FROM alert_queue WHERE {where}\\n ORDER BY created_at DESC LIMIT ${idx}\\n \\"\\"\\"\\n params.append(limit)\\n rows = await fetch(query, *params)\\n return [\\n {\\n \\"alert_id\\": r[\\"alert_id\\"],\\n \\"project_id\\": str(r[\\"project_id\\"]),\\n \\"alert_type\\": r[\\"alert_type\\"],\\n \\"severity\\": r[\\"severity\\"],\\n \\"message\\": r[\\"message\\"],\\n \\"recipients\\": r[\\"recipients\\"],\\n \\"is_sent\\": r[\\"is_sent\\"],\\n \\"created_at\\": r[\\"created_at\\"].isoformat(),\\n }\\n for r in rows\\n ]\\n\\n\\n@app.post(\\"/api/alerts/process\\", tags=[\\"Alerts\\"])\\nasync def process_pending_alerts(background_tasks: BackgroundTasks):\\n \\"\\"\\"Process all pending alerts and send email notifications.\\"\\"\\"\\n rows = await fetch(\\n \\"SELECT alert_id, project_id, alert_type, severity, message, recipients FROM alert_queue WHERE is_sent = FALSE\\"\\n )\\n sent_count = 0\\n for r in rows:\\n # Send email in background\\n background_tasks.add_task(\\n email_service.send_alert,\\n r[\\"recipients\\"],\\n f\\"{r[\'alert_type\']} - {r[\'severity\'].upper()}\\",\\n f\\"

{r[\'message\']}

Auto-generated by EIGIS Alert System

\\",\\n priority=r[\\"severity\\"],\\n )\\n await execute(\\n \\"UPDATE alert_queue SET is_sent = TRUE, sent_at = NOW() WHERE alert_id = $1\\",\\n r[\\"alert_id\\"],\\n )\\n sent_count += 1\\n return {\\"processed\\": sent_count}\\n\\n\\n# ============================================================\\n# MOBILE SYNC ENDPOINTS\\n# ============================================================\\n@app.post(\\"/api/sync/observations\\", tags=[\\"Mobile Sync\\"], status_code=201)\\nasync def sync_observations(data: List[ObservationCreate]):\\n \\"\\"\\"\\n Mobile data collection sync endpoint.\\n Handles batched observations from field devices.\\n \\"\\"\\"\\n pool = await get_pool()\\n async with pool.acquire() as conn:\\n async with conn.transaction():\\n ids = []\\n for obs in data:\\n rid = await conn.fetchval(\\n \\"\\"\\"INSERT INTO observations\\n (field_trip_id, site_id, observation_type, exposure_type,\\n geom, elevation_m, easting, northing, utm_zone,\\n exposure_length_m, exposure_height_m, groundwater_level_m,\\n weather_condition, excavation_method, accessibility, remarks, logger_id,\\n sync_status)\\n VALUES ($1, $2, $3, $4,\\n ST_SetSRID(ST_MakePoint($5, $6), 4326),\\n $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, \'synced\')\\n RETURNING observation_id\\"\\"\\",\\n obs.field_trip_id, obs.site_id, obs.observation_type.value,\\n obs.exposure_type, obs.longitude, obs.latitude,\\n obs.elevation_m, obs.easting, obs.northing, obs.utm_zone,\\n obs.exposure_length_m, obs.exposure_height_m, obs.groundwater_level_m,\\n obs.weather_condition, obs.excavation_method, obs.accessibility,\\n obs.remarks, obs.logger_id,\\n )\\n ids.append(str(rid))\\n return {\\"synced\\": len(ids), \\"observation_ids\\": ids}\\n\\n\\n# ============================================================\\n# SPATIAL QUERY ENDPOINTS\\n# ============================================================\\n@app.get(\\"/api/spatial/nearby\\", tags=[\\"Spatial Queries\\"])\\nasync def find_nearby_observations(\\n latitude: float = Query(..., ge=-90, le=90),\\n longitude: float = Query(..., ge=-180, le=180),\\n radius_m: float = Query(1000, ge=10, le=100000, description=\\"Search radius in meters\\"),\\n limit: int = Query(20, ge=1, le=100),\\n):\\n \\"\\"\\"\\n Find observations near a point using PostGIS spatial indexing.\\n Uses ST_DWithin for efficient radius search with spatial index.\\n \\"\\"\\"\\n rows = await fetch(\\n \\"\\"\\"SELECT observation_id, site_id, observation_type, status,\\n ST_Y(geom) as lat, ST_X(geom) as lon, elevation_m,\\n ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) as distance_m\\n FROM observations\\n WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, $3)\\n ORDER BY distance_m\\n LIMIT $4\\"\\"\\",\\n longitude, latitude, radius_m, limit,\\n )\\n return [\\n {\\n \\"observation_id\\": str(r[\\"observation_id\\"]),\\n \\"site_id\\": r[\\"site_id\\"],\\n \\"observation_type\\": r[\\"observation_type\\"],\\n \\"status\\": r[\\"status\\"],\\n \\"latitude\\": float(r[\\"lat\\"]),\\n \\"longitude\\": float(r[\\"lon\\"]),\\n \\"elevation_m\\": r[\\"elevation_m\\"],\\n \\"distance_m\\": round(float(r[\\"distance_m\\"]), 2),\\n }\\n for r in rows\\n ]\\n\\n\\n@app.get(\\"/api/spatial/cluster\\", tags=[\\"Spatial Queries\\"])\\nasync def get_spatial_clusters(\\n bbox: str = Query(..., description=\\"Bounding box: xmin,ymin,xmax,ymax\\"),\\n zoom: int = Query(10, ge=1, le=20, description=\\"Map zoom level for cluster resolution\\"),\\n):\\n \\"\\"\\"\\n Generate spatial clusters for map visualization at different zoom levels.\\n Uses PostGIS ST_ClusterDBSCAN for density-based clustering.\\n \\"\\"\\"\\n parts = [float(p) for p in bbox.split(\\",\\")]\\n eps = max(0.001, 0.01 * (20 - zoom) / 20) # Adaptive epsilon\\n\\n rows = await fetch(\\n \\"\\"\\"SELECT\\n unnest(array_agg(observation_id)) as observation_id,\\n unnest(array_agg(site_id)) as site_id,\\n ST_Centroid(ST_Collect(geom)) as center,\\n COUNT(*) as cluster_size\\n FROM (\\n SELECT observation_id, site_id, geom,\\n ST_ClusterDBSCAN(geom, $1, 2) OVER () AS cid\\n FROM observations\\n WHERE geom && ST_MakeEnvelope($2, $3, $4, $5, 4326)\\n ) sub\\n WHERE cid IS NOT NULL\\n GROUP BY cid\\"\\"\\",\\n eps, parts[0], parts[1], parts[2], parts[3],\\n )\\n return [\\n {\\n \\"observation_id\\": str(r[\\"observation_id\\"]),\\n \\"site_id\\": r[\\"site_id\\"],\\n \\"latitude\\": float(r[\\"center\\"].y) if r[\\"center\\"] else 0,\\n \\"longitude\\": float(r[\\"center\\"].x) if r[\\"center\\"] else 0,\\n \\"cluster_size\\": r[\\"cluster_size\\"],\\n }\\n for r in rows\\n ]\\n"}'}}]} {'role': 'tool', 'content': "File 'backend/app/api.py' created successfully.", 'tool_call_id': 'chatcmpl-tool-92e91b55e0366f9c'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-840724626418bdb4', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "backend/app/__init__.py", "file_contents": "\\"\\"\\"EIGIS FastAPI Application Package\\"\\"\\"\\n"}'}}]} {'role': 'tool', 'content': "File 'backend/app/__init__.py' created successfully.", 'tool_call_id': 'chatcmpl-tool-840724626418bdb4'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-84c94df9c3eaef0e', 'type': 'function', 'function': {'name': 'str_replace', 'arguments': '{"file_path": "todo.md", "old_str": "## Phase 2: FastAPI Backend\\n- [ ] Create FastAPI application with PostGIS integration\\n- [ ] Implement CRUD endpoints for all themes (Surface Geological, Structural, Geothermal)\\n- [ ] Implement concurrent data ingestion with connection pooling\\n- [ ] Create email alert service for anomalous progress notifications\\n- [ ] Generate OpenAPI documentation for third-party integrations", "new_str": "## Phase 2: FastAPI Backend\\n- [x] Create FastAPI application with PostGIS integration\\n- [x] Implement CRUD endpoints for all themes (Surface Geological, Structural, Geothermal)\\n- [x] Implement concurrent data ingestion with connection pooling\\n- [x] Create email alert service for anomalous progress notifications\\n- [x] Generate OpenAPI documentation for third-party integrations"}'}}]} {'role': 'tool', 'content': 'Replacement successful.', 'tool_call_id': 'chatcmpl-tool-84c94df9c3eaef0e'} {'role': 'assistant', 'content': "## Phase 3 & 4: Building the Complete EIGIS Dashboard\n\nNow I'll create the comprehensive HTML dashboard that preserves the existing layout while adding the new analytics, WebGIS map, forms for all three themes, and modern white-background design. This is the core deliverable.", 'tool_calls': [{'id': 'chatcmpl-tool-8be8421deaa1a0df', 'type': 'function', 'function': {'name': 'execute_command', 'arguments': '{"command": "mkdir -p /workspace/eigis_system","timeout": 10}'}}]} {'role': 'tool', 'content': '{"output": "", "exit_code": 0, "cwd": "/workspace"}', 'tool_call_id': 'chatcmpl-tool-8be8421deaa1a0df'} {'role': 'assistant', 'content': None, 'tool_calls': [{'id': 'chatcmpl-tool-8282da6fb4f19829', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"note": "Arguments could not be parsed"}'}}]} {'role': 'tool', 'content': "Error: Tool call 'create_file' was not executed because the response was cut off (token limit of 64000 tokens reached) before the arguments were complete. Please retry with a shorter or simpler request, or split the work into smaller steps.", 'tool_call_id': 'chatcmpl-tool-8282da6fb4f19829'} {'role': 'assistant', 'content': 'The dashboard is large - let me create it in sections.', 'tool_calls': [{'id': 'chatcmpl-tool-a57f123e582d6b6b', 'type': 'function', 'function': {'name': 'create_file', 'arguments': '{"file_path": "eigis_system/eigis_styles.css", "file_contents": "/* ============================================================\\n EIGIS System - Master Stylesheet\\n Modern white-background analytics dashboard\\n ============================================================ */\\n:root {\\n --eigis-teal: #0F6E56;\\n --eigis-teal-light: #E1F5EE;\\n --eigis-teal-mid: #1D9E75;\\n --eigis-amber: #BA7517;\\n --eigis-amber-light: #FAEEDA;\\n --eigis-red: #DC2626;\\n --eigis-red-light: #FEF2F2;\\n --eigis-blue: #185FA5;\\n --eigis-blue-light: #E6F1FB;\\n --eigis-purple: #3C3489;\\n --eigis-purple-light: #EEEDFE;\\n --eigis-green: #22C55E;\\n --eigis-orange: #F97316;\\n --eigis-sidebar-bg: #085041;\\n --eigis-sidebar-hover: rgba(255,255,255,0.07);\\n --eigis-sidebar-active: rgba(29,158,117,0.25);\\n --eigis-sidebar-text: rgba(255,255,255,0.7);\\n --eigis-sidebar-active-text: #9FE1CB;\\n --eigis-border: #E5E7EB;\\n --eigis-border-light: #F3F4F6;\\n --eigis-bg-primary: #FFFFFF;\\n --eigis-bg-secondary: #F9FAFB;\\n --eigis-bg-tertiary: #F3F4F6;\\n --eigis-text-primary: #111827;\\n --eigis-text-secondary: #6B7280;\\n --eigis-text-muted: #9CA3AF;\\n --eigis-shadow-sm: 0 1px 2px rgba(0,0,0,0.05);\\n --eigis-shadow-md: 0 4px 6px -1px rgba(0,0,0,0.07), 0 2px 4px -1px rgba(0,0,0,0.04);\\n --eigis-shadow-lg: 0 10px 15px -3px rgba(0,0,0,0.08), 0 4px 6px -2px rgba(0,0,0,0.03);\\n --eigis-radius-sm: 6px;\\n --eigis-radius-md: 10px;\\n --eigis-radius-lg: 14px;\\n --eigis-radius-xl: 18px;\\n --font-sans: \'Inter\', -apple-system, BlinkMacSystemFont, \'Segoe UI\', Roboto, sans-serif;\\n}\\n\\n* { box-sizing: border-box; margin: 0; padding: 0; }\\n\\nbody {\\n font-family: var(--font-sans);\\n background: var(--eigis-bg-secondary);\\n color: var(--eigis-text-primary);\\n -webkit-font-smoothing: antialiased;\\n}\\n\\n/* === SHELL LAYOUT === */\\n.shell {\\n display: grid;\\n grid-template-columns: 220px 1fr;\\n min-height: 100vh;\\n background: var(--eigis-bg-primary);\\n}\\n\\n/* === SIDEBAR === */\\n.sidebar {\\n background: var(--eigis-sidebar-bg);\\n display: flex;\\n flex-direction: column;\\n padding: 0;\\n position: sticky;\\n top: 0;\\n height: 100vh;\\n overflow-y: auto;\\n}\\n\\n.sidebar-header {\\n padding: 18px 18px 14px;\\n border-bottom: 1px solid rgba(255,255,255,0.08);\\n}\\n\\n.sidebar-logo {\\n color: #9FE1CB;\\n font-size: 11px;\\n letter-spacing: 0.1em;\\n text-transform: uppercase;\\n font-weight: 600;\\n}\\n\\n.sidebar-title {\\n color: #fff;\\n font-size: 16px;\\n font-weight: 600;\\n margin-top: 4px;\\n line-height: 1.3;\\n}\\n\\n.sidebar-subtitle {\\n color: #5DCAA5;\\n font-size: 11px;\\n margin-top: 2px;\\n}\\n\\n.nav-section-label {\\n color: rgba(255,255,255,0.35);\\n font-size: 10px;\\n text-transform: uppercase;\\n letter-spacing: 0.12em;\\n padding: 16px 18px 6px;\\n font-weight: 600;\\n}\\n\\n.nav-item {\\n display: flex;\\n align-items: center;\\n gap: 10px;\\n padding: 8px 18px;\\n cursor: pointer;\\n color: var(--eigis-sidebar-text);\\n font-size: 13px;\\n border-left: 2.5px solid transparent;\\n transition: all 0.15s ease;\\n text-decoration: none;\\n}\\n\\n.nav-item i { font-size: 16px; flex-shrink: 0; }\\n\\n.nav-item:hover {\\n background: var(--eigis-sidebar-hover);\\n color: #fff;\\n}\\n\\n.nav-item.active {\\n background: var(--eigis-sidebar-active);\\n color: var(--eigis-sidebar-active-text);\\n border-left-color: #5DCAA5;\\n font-weight: 500;\\n}\\n\\n.nav-item .nav-badge {\\n margin-left: auto;\\n background: rgba(29,158,117,0.4);\\n color: #9FE1CB;\\n font-size: 10px;\\n padding: 1px 7px;\\n border-radius: 99px;\\n font-weight: 600;\\n}\\n\\n.sidebar-footer {\\n margin-top: auto;\\n padding: 14px 18px;\\n border-top: 1px solid rgba(255,255,255,0.08);\\n color: rgba(255,255,255,0.3);\\n font-size: 10px;\\n line-height: 1.6;\\n}\\n\\n/* === MAIN CONTENT === */\\n.main {\\n display: flex;\\n flex-direction: column;\\n overflow: hidden;\\n background: var(--eigis-bg-secondary);\\n}\\n\\n/* === TOPBAR === */\\n.topbar {\\n display: flex;\\n align-items: center;\\n justify-content: space-between;\\n padding: 12px 24px;\\n border-bottom: 1px solid var(--eigis-border);\\n background: var(--eigis-bg-primary);\\n flex-shrink: 0;\\n}\\n\\n.topbar-left { display: flex; align-items: center; gap: 12px; }\\n\\n.breadcrumb {\\n font-size: 13px;\\n color: var(--eigis-text-secondary);\\n}\\n\\n.breadcrumb span { color: var(--eigis-text-primary); font-weight: 600; }\\n\\n.topbar-actions { display: flex; gap: 8px; align-items: center; }\\n\\n.btn {\\n padding: 7px 16px;\\n font-size: 12.5px;\\n border: 1px solid var(--eigis-border);\\n border-radius: var(--eigis-radius-sm);\\n cursor: pointer;\\n background: var(--eigis-bg-primary);\\n color: var(--eigis-text-primary);\\n display: flex;\\n align-items: center;\\n gap: 6px;\\n font-family: var(--font-sans);\\n font-weight: 500;\\n transition: all 0.15s ease;\\n}\\n\\n.btn:hover { background: var(--eigis-bg-secondary); border-color: var(--eigis-text-muted); }\\n\\n.btn-primary {\\n background: var(--eigis-sidebar-bg);\\n color: #fff;\\n border-color: var(--eigis-sidebar-bg);\\n}\\n\\n.btn-primary:hover { background: var(--eigis-teal); border-color: var(--eigis-teal); }\\n\\n.btn-success {\\n background: var(--eigis-teal-mid);\\n color: #fff;\\n border-color: var(--eigis-teal-mid);\\n}\\n\\n.btn-success:hover { background: var(--eigis-teal); }\\n\\n.btn-danger {\\n background: var(--eigis-red);\\n color: #fff;\\n border-color: var(--eigis-red);\\n}\\n\\n.btn-danger:hover { background: #B91C1C; }\\n\\n.status-badge {\\n padding: 3px 10px;\\n border-radius: 99px;\\n font-size: 11px;\\n font-weight: 600;\\n}\\n\\n.status-new { background: var(--eigis-teal-light); color: var(--eigis-teal); }\\n.status-saved { background: #D1FAE5; color: #065F46; }\\n.status-submitted { background: var(--eigis-blue-light); color: var(--eigis-blue); }\\n\\n.info-pill {\\n background: var(--eigis-teal-light);\\n color: var(--eigis-teal);\\n font-size: 11px;\\n padding: 3px 10px;\\n border-radius: 99px;\\n font-weight: 600;\\n display: flex;\\n align-items: center;\\n gap: 4px;\\n}\\n\\n/* === CONTENT AREA === */\\n.content-area {\\n flex: 1;\\n overflow-y: auto;\\n padding: 20px 24px;\\n}\\n\\n/* === VIEW TABS (Dashboard / Data Entry / Map) === */\\n.view-tabs {\\n display: flex;\\n gap: 0;\\n margin-bottom: 20px;\\n background: var(--eigis-bg-primary);\\n border-radius: var(--eigis-radius-md);\\n border: 1px solid var(--eigis-border);\\n padding: 4px;\\n box-shadow: var(--eigis-shadow-sm);\\n}\\n\\n.view-tab {\\n padding: 8px 20px;\\n font-size: 13px;\\n font-weight: 500;\\n cursor: pointer;\\n color: var(--eigis-text-secondary);\\n background: transparent;\\n border: none;\\n border-radius: var(--eigis-radius-sm);\\n transition: all 0.15s ease;\\n display: flex;\\n align-items: center;\\n gap: 6px;\\n font-family: var(--font-sans);\\n}\\n\\n.view-tab:hover { color: var(--eigis-text-primary); background: var(--eigis-bg-secondary); }\\n\\n.view-tab.active {\\n color: var(--eigis-teal);\\n background: var(--eigis-teal-light);\\n font-weight: 600;\\n}\\n\\n/* === DASHBOARD CARDS === */\\n.metrics-grid {\\n display: grid;\\n grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));\\n gap: 16px;\\n margin-bottom: 24px;\\n}\\n\\n.metric-card {\\n background: var(--eigis-bg-primary);\\n border-radius: var(--eigis-radius-lg);\\n padding: 20px;\\n border: 1px solid var(--eigis-border);\\n box-shadow: var(--eigis-shadow-sm);\\n transition: all 0.2s ease;\\n}\\n\\n.metric-card:hover {\\n box-shadow: var(--eigis-shadow-md);\\n transform: translateY(-1px);\\n}\\n\\n.metric-card-header {\\n display: flex;\\n align-items: center;\\n justify-content: space-between;\\n margin-bottom: 12px;\\n}\\n\\n.metric-card-icon {\\n width: 40px;\\n height: 40px;\\n border-radius: var(--eigis-radius-md);\\n display: flex;\\n align-items: center;\\n justify-content: center;\\n font-size: 20px;\\n}\\n\\n.metric-card-icon.teal { background: var(--eigis-teal-light); color: var(--eigis-teal); }\\n.metric-card-icon.amber { background: var(--eigis-amber-light); color: var(--eigis-amber); }\\n.metric-card-icon.red { background: var(--eigis-red-light); color: var(--eigis-red); }\\n.metric-card-icon.blue { background: var(--eigis-blue-light); color: var(--eigis-blue); }\\n.metric-card-icon.purple { background: var(--eigis-purple-light); color: var(--eigis-purple); }\\n.metric-card-icon.green { background: #D1FAE5; color: #065F46; }\\n\\n.metric-card-trend {\\n font-size: 11px;\\n padding: 2px 6px;\\n border-radius: 99px;\\n font-weight: 600;\\n}\\n\\n.metric-card-trend.up { background: #D1FAE5; color: #065F46; }\\n.metric-card-trend.down { background: var(--eigis-red-light); color: var(--eigis-red); }\\n.metric-card-trend.neutral { background: var(--eigis-bg-tertiary); color: var(--eigis-text-secondary); }\\n\\n.metric-card-value {\\n font-size: 28px;\\n font-weight: 700;\\n color: var(--eigis-text-primary);\\n line-height: 1;\\n margin-bottom: 4px;\\n}\\n\\n.metric-card-label {\\n font-size: 12px;\\n color: var(--eigis-text-secondary);\\n font-weight: 500;\\n}\\n\\n/* === CHART PANELS === */\\n.chart-row {\\n display: grid;\\n grid-template-columns: 1fr 1fr;\\n gap: 16px;\\n margin-bottom: 20px;\\n}\\n\\n.chart-panel {\\n background: var(--eigis-bg-primary);\\n border-radius: var(--eigis-radius-lg);\\n border: 1px solid var(--eigis-border);\\n box-shadow: var(--eigis-shadow-sm);\\n overflow: hidden;\\n}\\n\\n.chart-panel-header {\\n display: flex;\\n align-items: center;\\n justify-content: space-between;\\n padding: 14px 18px;\\n border-bottom: 1px solid var(--eigis-border-light);\\n}\\n\\n.chart-panel-title {\\n font-size: 14px;\\n font-weight: 600;\\n color: var(--eigis-text-primary);\\n}\\n\\n.chart-panel-subtitle {\\n font-size: 11px;\\n color: var(--eigis-text-muted);\\n}\\n\\n.chart-panel-body { padding: 16px 18px; }\\n\\n/* === DONUT CHART (CSS) === */\\n.donut-container {\\n display: flex;\\n align-items: center;\\n gap: 24px;\\n justify-content: center;\\n padding: 8px 0;\\n}\\n\\n.donut-chart {\\n width: 140px;\\n height: 140px;\\n border-radius: 50%;\\n position: relative;\\n}\\n\\n.donut-center {\\n position: absolute;\\n top: 50%;\\n left: 50%;\\n transform: translate(-50%, -50%);\\n text-align: center;\\n}\\n\\n.donut-center-value { font-size: 22px; font-weight: 700; color: var(--eigis-text-primary); }\\n.donut-center-label { font-size: 10px; color: var(--eigis-text-secondary); }\\n\\n.donut-legend { display: flex; flex-direction: column; gap: 8px; }\\n\\n.donut-legend-item { display: flex; align-items: center; gap: 8px; font-size: 12px; }\\n\\n.donut-legend-dot {\\n width: 10px;\\n height: 10px;\\n border-radius: 50%;\\n flex-shrink: 0;\\n}\\n\\n.donut-legend-label { color: var(--eigis-text-secondary); }\\n.donut-legend-value { font-weight: 600; color: var(--eigis-text-primary); margin-left: auto; }\\n\\n/* === BAR CHART (CSS) === */\\n.bar-chart { display: flex; flex-direction: column; gap: 10px; }\\n\\n.bar-row { display: flex; align-items: center; gap: 10px; }\\n\\n.bar-label { width: 90px; font-size: 12px; color: var(--eigis-text-secondary); text-align: right; flex-shrink: 0; }\\n\\n.bar-track { flex: 1; height: 24px; background: var(--eigis-bg-tertiary); border-radius: 6px; overflow: hidden; }\\n\\n.bar-fill {\\n height: 100%;\\n border-radius: 6px;\\n display: flex;\\n align-items: center;\\n padding: 0 8px;\\n font-size: 11px;\\n font-weight: 600;\\n color: #fff;\\n transition: width 0.6s ease;\\n min-width: 30px;\\n}\\n\\n.bar-value { font-size: 12px; font-weight: 600; color: var(--eigis-text-primary); width: 40px; text-align: right; }\\n\\n/* === ALERTS LIST === */\\n.alert-list { display: flex; flex-direction: column; gap: 8px; }\\n\\n.alert-item {\\n display: flex;\\n align-items: flex-start;\\n gap: 10px;\\n padding: 10px 12px;\\n border-radius: var(--eigis-radius-sm);\\n border: 1px solid var(--eigis-border);\\n background: var(--eigis-bg-primary);\\n}\\n\\n.alert-icon {\\n width: 28px;\\n height: 28px;\\n border-radius: 50%;\\n display: flex;\\n align-items: center;\\n justify-content: center;\\n font-size: 14px;\\n flex-shrink: 0;\\n}\\n\\n.alert-icon.critical { background: var(--eigis-red-light); color: var(--eigis-red); }\\n.alert-icon.warning { background: var(--eigis-amber-light); color: var(--eigis-amber); }\\n.alert-icon.info { background: var(--eigis-blue-light); color: var(--eigis-blue); }\\n\\n.alert-content { flex: 1; }\\n\\n.alert-title { font-size: 12px; font-weight: 600; color: var(--eigis-text-primary); }\\n\\n.alert-desc { font-size: 11px; color: var(--eigis-text-secondary); margin-top: 2px; }\\n\\n.alert-time { font-size: 10px; color: var(--eigis-text-muted); margin-top: 2px; }\\n\\n/* === WEBGIS MAP === */\\n.map-container {\\n background: var(--eigis-bg-primary);\\n border-radius: var(--eigis-radius-lg);\\n border: 1px solid var(--eigis-border);\\n box-shadow: var(--eigis-shadow-sm);\\n overflow: hidden;\\n height: 500px;\\n position: relative;\\n}\\n\\n#eigisMap { width: 100%; height: 100%; z-index: 1; }\\n\\n.map-legend {\\n position: absolute;\\n bottom: 20px;\\n right: 20px;\\n background: rgba(255,255,255,0.95);\\n backdrop-filter: blur(8px);\\n border-radius: var(--eigis-radius-md);\\n padding: 12px 16px;\\n border: 1px solid var(--eigis-border);\\n box-shadow: var(--eigis-shadow-md);\\n z-index: 1000;\\n font-size: 11px;\\n}\\n\\n.map-legend-title { font-weight: 600; margin-bottom: 6px; color: var(--eigis-text-primary); }\\n\\n.map-legend-item { display: flex; align-items: center; gap: 6px; margin-bottom: 4px; }\\n\\n.map-legend-dot {\\n width: 10px;\\n height: 10px;\\n border-radius: 50%;\\n flex-shrink: 0;\\n}\\n\\n.map-controls {\\n position: absolute;\\n top: 20px;\\n left: 20px;\\n z-index: 1000;\\n display: flex;\\n flex-direction: column;\\n gap: 6px;\\n}\\n\\n.map-filter-btn {\\n padding: 6px 12px;\\n font-size: 11px;\\n border: 1px solid var(--eigis-border);\\n border-radius: var(--eigis-radius-sm);\\n cursor: pointer;\\n background: rgba(255,255,255,0.95);\\n backdrop-filter: blur(8px);\\n color: var(--eigis-text-primary);\\n font-weight: 500;\\n display: flex;\\n align-items: center;\\n gap: 5px;\\n font-family: var(--font-sans);\\n transition: all 0.15s ease;\\n}\\n\\n.map-filter-btn:hover { background: var(--eigis-teal-light); border-color: var(--eigis-teal); }\\n.map-filter-btn.active { background: var(--eigis-teal-light); border-color: var(--eigis-teal); color: var(--eigis-teal); }\\n\\n/* === FORM SECTIONS === */\\n.form-section {\\n background: var(--eigis-bg-primary);\\n border-radius: var(--eigis-radius-lg);\\n margin-bottom: 16px;\\n overflow: hidden;\\n border: 1px solid var(--eigis-border);\\n box-shadow: var(--eigis-shadow-sm);\\n}\\n\\n.section-header {\\n display: flex;\\n align-items: center;\\n justify-content: space-between;\\n padding: 12px 18px;\\n background: var(--eigis-bg-secondary);\\n cursor: pointer;\\n user-select: none;\\n border-bottom: 1px solid var(--eigis-border-light);\\n}\\n\\n.section-header-left { display: flex; align-items: center; gap: 10px; }\\n\\n.section-tag {\\n padding: 2px 9px;\\n border-radius: 5px;\\n font-size: 10px;\\n font-weight: 600;\\n letter-spacing: 0.05em;\\n}\\n\\n.tag-a { background: var(--eigis-teal-light); color: var(--eigis-teal); }\\n.tag-b { background: var(--eigis-blue-light); color: var(--eigis-blue); }\\n.tag-c { background: var(--eigis-amber-light); color: var(--eigis-amber); }\\n.tag-d { background: #FDE8E8; color: var(--eigis-red); }\\n.tag-e { background: var(--eigis-purple-light); color: var(--eigis-purple); }\\n.tag-f { background: #D1FAE5; color: #065F46; }\\n.tag-g { background: #FBEAF0; color: #72243E; }\\n.tag-geo { background: #FEE2E2; color: #B91C1C; }\\n\\n.section-title { font-size: 13px; font-weight: 600; color: var(--eigis-text-primary); }\\n\\n.section-body { padding: 16px 18px; display: none; }\\n.section-body.open { display: block; }\\n\\n.section-toggle {\\n color: var(--eigis-text-muted);\\n font-size: 14px;\\n transition: transform 0.2s ease;\\n}\\n\\n.section-toggle.open { transform: rotate(180deg); }\\n\\n.form-grid {\\n display: grid;\\n grid-template-columns: repeat(auto-fit, minmax(170px, 1fr));\\n gap: 12px 16px;\\n margin-bottom: 12px;\\n}\\n\\n.field-group { display: flex; flex-direction: column; gap: 4px; }\\n\\n.field-label {\\n font-size: 11px;\\n color: var(--eigis-text-secondary);\\n font-weight: 600;\\n text-transform: uppercase;\\n letter-spacing: 0.04em;\\n}\\n\\n.field-label .required { color: var(--eigis-red); }\\n\\n.field-input {\\n font-size: 13px;\\n padding: 7px 10px;\\n border: 1px solid var(--eigis-border);\\n border-radius: var(--eigis-radius-sm);\\n background: var(--eigis-bg-primary);\\n color: var(--eigis-text-primary);\\n font-family: var(--font-sans);\\n width: 100%;\\n transition: all 0.15s ease;\\n}\\n\\n.field-input:focus {\\n outline: none;\\n border-color: var(--eigis-teal-mid);\\n box-shadow: 0 0 0 3px rgba(29,158,117,0.15);\\n}\\n\\n.field-input[readonly] {\\n background: var(--eigis-bg-secondary);\\n color: var(--eigis-text-muted);\\n cursor: default;\\n}\\n\\n.field-input.auto {\\n background: var(--eigis-teal-light);\\n color: var(--eigis-teal);\\n border-color: #9FE1CB;\\n}\\n\\n.auto-label { font-size: 10px; color: var(--eigis-teal); margin-top: 1px; }\\n\\n.gps-row {\\n display: grid;\\n grid-template-columns: 1fr 1fr 1fr auto;\\n gap: 12px;\\n align-items: end;\\n}\\n\\n.btn-gps {\\n padding: 7px 14px;\\n font-size: 12px;\\n background: var(--eigis-teal-mid);\\n color: #fff;\\n border: none;\\n border-radius: var(--eigis-radius-sm);\\n cursor: pointer;\\n white-space: nowrap;\\n display: flex;\\n align-items: center;\\n gap: 5px;\\n font-family: var(--font-sans);\\n font-weight: 500;\\n transition: background 0.15s ease;\\n}\\n\\n.btn-gps:hover { background: var(--eigis-sidebar-bg); }\\n\\n.divider { height: 1px; background: var(--eigis-border-light); margin: 12px 0; }\\n\\n.sub-table {\\n width: 100%;\\n border-collapse: collapse;\\n font-size: 12px;\\n margin-bottom: 10px;\\n}\\n\\n.sub-table th {\\n background: var(--eigis-bg-secondary);\\n padding: 7px 8px;\\n text-align: left;\\n font-weight: 600;\\n color: var(--eigis-text-secondary);\\n font-size: 11px;\\n border-bottom: 1px solid var(--eigis-border);\\n text-transform: uppercase;\\n letter-spacing: 0.03em;\\n}\\n\\n.sub-table td {\\n padding: 5px 6px;\\n border-bottom: 1px solid var(--eigis-border-light);\\n vertical-align: middle;\\n}\\n\\n.sub-table td input, .sub-table td select {\\n width: 100%;\\n font-size: 12px;\\n padding: 4px 6px;\\n border: 1px solid var(--eigis-border);\\n border-radius: 4px;\\n background: var(--eigis-bg-primary);\\n color: var(--eigis-text-primary);\\n font-family: var(--font-sans);\\n}\\n\\n.sub-table td input:focus, .sub-table td select:focus {\\n outline: none;\\n border-color: var(--eigis-teal-mid);\\n box-shadow: 0 0 0 2px rgba(29,158,117,0.12);\\n}\\n\\n.add-row-btn {\\n font-size: 12px;\\n color: var(--eigis-teal);\\n background: none;\\n border: 1px dashed #5DCAA5;\\n border-radius: var(--eigis-radius-sm);\\n padding: 6px 14px;\\n cursor: pointer;\\n width: 100%;\\n font-family: var(--font-sans);\\n font-weight: 500;\\n transition: all 0.15s ease;\\n}\\n\\n.add-row-btn:hover { background: var(--eigis-teal-light); }\\n\\n.del-btn {\\n padding: 3px 6px;\\n font-size: 11px;\\n color: var(--eigis-text-secondary);\\n background: none;\\n border: 1px solid var(--eigis-border);\\n border-radius: 4px;\\n cursor: pointer;\\n font-family: var(--font-sans);\\n transition: all 0.15s ease;\\n}\\n\\n.del-btn:hover { color: var(--eigis-red); border-color: #F0997B; }\\n\\n.textarea-field {\\n resize: vertical;\\n min-height: 70px;\\n font-family: var(--font-sans);\\n}\\n\\n.photo-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 10px; }\\n\\n.photo-slot {\\n border: 1px dashed var(--eigis-border);\\n border-radius: var(--eigis-radius-md);\\n padding: 18px 10px;\\n text-align: center;\\n cursor: pointer;\\n transition: all 0.15s ease;\\n background: var(--eigis-bg-secondary);\\n}\\n\\n.photo-slot:hover { background: var(--eigis-teal-light); border-color: #5DCAA5; }\\n\\n.photo-slot i { font-size: 24px; color: var(--eigis-text-muted); display: block; margin-bottom: 6px; }\\n.photo-slot span { font-size: 11px; color: var(--eigis-text-secondary); }\\n\\n.auto-fields-row {\\n display: grid;\\n grid-template-columns: repeat(6, 1fr);\\n gap: 10px;\\n padding: 12px 18px;\\n background: var(--eigis-bg-secondary);\\n border-radius: var(--eigis-radius-md);\\n margin-bottom: 16px;\\n border: 1px solid var(--eigis-border-light);\\n}\\n\\n.auto-field-item { display: flex; flex-direction: column; gap: 2px; }\\n\\n.auto-field-key {\\n font-size: 10px;\\n color: var(--eigis-text-muted);\\n text-transform: uppercase;\\n letter-spacing: 0.05em;\\n font-weight: 600;\\n}\\n\\n.auto-field-val { font-size: 12px; color: var(--eigis-teal); font-weight: 600; }\\n\\n/* === BOTTOM BAR === */\\n.bottom-bar {\\n flex-shrink: 0;\\n padding: 12px 24px;\\n border-top: 1px solid var(--eigis-border);\\n display: flex;\\n align-items: center;\\n justify-content: space-between;\\n background: var(--eigis-bg-primary);\\n}\\n\\n.progress-dots { display: flex; gap: 6px; align-items: center; }\\n\\n.dot {\\n width: 8px;\\n height: 8px;\\n border-radius: 50%;\\n background: var(--eigis-border);\\n transition: all 0.2s ease;\\n}\\n\\n.dot.filled { background: var(--eigis-teal-mid); }\\n.dot.active { background: var(--eigis-teal); width: 10px; height: 10px; }\\n\\n.bottom-actions { display: flex; gap: 8px; }\\n\\n/* === TOAST === */\\n.toast {\\n position: fixed;\\n bottom: 24px;\\n right: 24px;\\n background: var(--eigis-sidebar-bg);\\n color: #fff;\\n padding: 12px 18px;\\n border-radius: var(--eigis-radius-md);\\n font-size: 13px;\\n display: none;\\n align-items: center;\\n gap: 8px;\\n z-index: 9999;\\n box-shadow: var(--eigis-shadow-lg);\\n animation: slideUp 0.3s ease;\\n}\\n\\n@keyframes slideUp {\\n from { opacity: 0; transform: translateY(12px); }\\n to { opacity: 1; transform: translateY(0); }\\n}\\n\\n.coord-status {\\n display: flex;\\n align-items: center;\\n gap: 5px;\\n font-size: 11px;\\n color: var(--eigis-teal);\\n margin-top: 4px;\\n}\\n\\n.rock-strength-bar { display: flex; gap: 3px; margin-top: 5px; }\\n\\n.rs-cell {\\n flex: 1;\\n height: 7px;\\n border-radius: 4px;\\n background: var(--eigis-bg-tertiary);\\n transition: background 0.2s ease;\\n}\\n\\n.rs-cell.active { background: var(--eigis-teal-mid); }\\n\\n/* === RESPONSIVE === */\\n@media (max-width: 1024px) {\\n .shell { grid-template-columns: 180px 1fr; }\\n .chart-row { grid-template-columns: 1fr; }\\n .auto-fields-row { grid-template-columns: repeat(3, 1fr); }\\n}\\n\\n@media (max-width: 768px) {\\n .shell { grid-template-columns: 1fr; }\\n .sidebar { display: none; }\\n .metrics-grid { grid-template-columns: repeat(2, 1fr); }\\n}\\n\\n/* === GEOTHERMAL THEME SPECIFIC === */\\n.temp-indicator {\\n display: inline-flex;\\n align-items: center;\\n gap: 4px;\\n padding: 2px 8px;\\n border-radius: 99px;\\n font-size: 11px;\\n font-weight: 600;\\n}\\n\\n.temp-hot { background: #FEE2E2; color: #B91C1C; }\\n.temp-warm { background: #FEF3C7; color: #92400E; }\\n.temp-cool { background: #DBEAFE; color: #1E40AF; }\\n\\n/* === SCROLLBAR === */\\n::-webkit-scrollbar { width: 6px; }\\n::-webkit-scrollbar-track { background: transparent; }\\n::-webkit-scrollbar-thumb { background: var(--eigis-border); border-radius: 3px; }\\n::-webkit-scrollbar-thumb:hover { background: var(--eigis-text-muted); }\\n"}'}}]} {'role': 'tool', 'content': "File 'eigis_system/eigis_styles.css' created successfully.", 'tool_call_id': 'chatcmpl-tool-a57f123e582d6b6b'} {'role': 'assistant', 'content': "Now let me create the main HTML dashboard - the core deliverable. Due to its size, I'll build it as one comprehensive file.", 'tool_calls': [{'id': 'chatcmpl-tool-a87d0ca85a2de70e', 'type': 'function', 'function': {'name': 'execute_command', 'arguments': '{"command": "cat > /workspace/eigis_system/eigis_dashboard.html << \'HTMLEOF\'\\n\\n\\n\\n\\n\\nEIGIS · Engineering Geology & Geohazard Information System\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n
\\n \\n
\\n
\\n
GIE · EIGIS
\\n
Engineering Geology
\\n
Geohazard & Geothermal Module
\\n
\\n\\n
Dashboard
\\n \\n Analytics Dashboard\\n \\n \\n Interactive WebGIS Map\\n Live\\n \\n\\n
Data Collection
\\n \\n Data Encoding Forms\\n \\n\\n
Exposure Modules
\\n \\n Exposure Inventory\\n \\n \\n Soil Profile Logging\\n \\n \\n Rock Exposure\\n \\n \\n Discontinuity Mapping\\n \\n \\n Slope Stability\\n \\n\\n
Geothermal Module
\\n \\n Geothermal Features\\n New\\n \\n \\n Gradient Stations\\n \\n\\n
Advanced Modules
\\n \\n Landslide Inventory\\n \\n \\n Geotechnical Sampling\\n \\n \\n Geochemistry\\n Future\\n \\n \\n Geophysics\\n Future\\n \\n\\n
Classification
\\n \\n Rock Mass (RMR/Q/GSI)\\n \\n\\n
System
\\n \\n Alerts & Notifications\\n 3\\n \\n \\n Documentation\\n \\n \\n Photo Documentation\\n \\n\\n
\\n PostgreSQL 16 · PostGIS 3.4
\\n FastAPI · React · Leaflet
\\n EIGIS v2.0 · 2026\\n
\\n
\\n\\n \\n
\\n \\n
\\n
\\n
EIGIS / Analytics Dashboard
\\n Live\\n
\\n
\\n
SYS-2026
\\n \\n \\n \\n
\\n
\\n\\n \\n
\\n\\n \\n
\\n \\n \\n \\n \\n \\n
\\n\\n \\n \\n \\n
\\n \\n
\\n
\\n
\\n
\\n +12%\\n
\\n
247
\\n
Total Observations
\\n
\\n
\\n
\\n
\\n +8%\\n
\\n
142
\\n
Surface Geological
\\n
\\n
\\n
\\n
\\n +5%\\n
\\n
89
\\n
Structural Features
\\n
\\n
\\n
\\n
\\n +3\\n
\\n
16
\\n
Geothermal Manifestations
\\n
\\n
\\n
\\n
\\n —\\n
\\n
64
\\n
Samples Collected
\\n
\\n
\\n
\\n
\\n +2\\n
\\n
7
\\n
High+ Hazard Alerts
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n
\\n
Theme Distribution
\\n
Observations by interconnected theme
\\n
\\n
\\n
\\n \\n
\\n
\\n
\\n
\\n
\\n
Hazard Level Distribution
\\n
Slope stability risk assessment
\\n
\\n
\\n
\\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n
\\n
Geothermal Manifestation Stats
\\n
Temperature & pH by type
\\n
\\n
\\n
\\n \\n
\\n
\\n
\\n
\\n
\\n
Observation Timeline
\\n
Daily data collection progress
\\n
\\n
\\n
\\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n
\\n
Recent Alerts
\\n
Anomalous progress detection
\\n
\\n \\n
\\n
\\n
\\n
\\n
\\n
\\n
Critical Hazard — Slope EXP-047
\\n
Very High hazard level detected. Immediate review required.
\\n
2 hours ago · Email sent to client
\\n
\\n
\\n
\\n
\\n
\\n
Geothermal Anomaly — 96.2°C Surface Temp
\\n
Surface temperature exceeds 95°C threshold at GTH-003.
\\n
5 hours ago · Email sent to client + receptionist
\\n
\\n
\\n
\\n
\\n
\\n
Progress Delay — EIGIS-GIE-2026-002
\\n
No observations recorded in 8 days for active project.
\\n
1 day ago · Email sent
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
Sample Processing Pipeline
\\n
Lab status of collected samples
\\n
\\n
\\n
\\n \\n
\\n
\\n
\\n
\\n\\n \\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n \\n \\n
\\n
\\n
EIGIS Map Legend
\\n
Surface Geological
\\n
Structural Features
\\n
Geothermal Manifestations
\\n
Multi-Theme
\\n
\\n
\\n
\\n
\\n
\\n
\\n
Spatial Data Summary
\\n
Real-time vector workspace statistics
\\n
\\n
\\n
\\n
\\n
\\n
247
\\n
Plotted Nodes
\\n
\\n
\\n
18
\\n
Spatial Clusters
\\n
\\n
\\n
Amhara Region
\\n
Current Extent
\\n
\\n
\\n
UTM 37N
\\n
Coordinate System
\\n
\\n
\\n
\\n
\\n
\\n
\\n\\n \\n \\n \\n
\\n \\n
\\n
Logger
T. Haile — GIE
\\n
Date / Time
—
\\n
Administrative Unit
Auto · GIS Boundary
\\n
Watershed
Auto · DEM Layer
\\n
Geological Formation
Auto · Geo Map Layer
\\n
Landslide Inventory
Auto · Geohazard DB
\\n
\\n\\n \\n
\\n
\\n
\\n A\\n Exposure Site Information\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n \\n
\\n
Coordinates captured · Asinara Basin, Amhara Region
\\n
\\n
\\n \\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n B\\n Soil Profile Logging\\n
\\n \\n
\\n
\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n
HorizonFrom (m)To (m)ColorConsistencyMoistureGrain SizePlasticityUSCS
\\n \\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n C\\n Rock Exposure Description\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n
\\n \\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n D\\n Discontinuity Characterization\\n
\\n \\n
\\n
\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n
SetTypeDip Dir (°)Dip (°)Spacing (m)Aperture (mm)RoughnessInfillPersistence (m)
\\n \\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n E\\n Slope Stability Assessment\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n \\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n F\\n Geotechnical Sampling\\n
\\n \\n
\\n
\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n
Sample No.Depth (m)Sample TypeTest RequiredHorizon Ref.Remarks
\\n \\n
\\n
\\n\\n \\n
\\n
\\n
\\n G\\n Photo Documentation\\n
\\n \\n
\\n
\\n
\\n
Exposure Overview
\\n
Slope Photo
\\n
Discontinuity Set
\\n
Sample Photo
\\n
Geothermal Feature
\\n
Location Context
\\n
Field Notes
\\n
\\n Add Photo\\n
\\n
\\n

Photos are geotagged and linked to ObservationID. Storage: MinIO/S3. Max 10 MB per image.

\\n
\\n
\\n\\n \\n
\\n
\\n
\\n GTH\\n Geothermal Features\\n New Theme\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n GG\\n Geothermal Gradient Stations\\n
\\n \\n
\\n
\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n
StationDepth Top (m)Depth Bottom (m)Temp Top (°C)Temp Bottom (°C)Gradient (°C/km)Method
\\n \\n
\\n
\\n\\n \\n
\\n
\\n
\\n LS\\n Landslide Inventory\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n \\n \\n
\\n
\\n
\\n\\n \\n
\\n
\\n
\\n GC\\n Geochemistry Results\\n Future Expansion\\n
\\n \\n
\\n
\\n

\\n \\n This module supports XRF, XRD, ICP-MS, ICP-AES, AAS, IC, GC-MS, isotope ratio,\\n major oxides, trace elements, rare earth, stable isotope, and fluid inclusion analyses.\\n Schema is pre-built — awaiting activation.\\n

\\n
\\n
\\n\\n \\n
\\n
\\n
\\n GP\\n Geophysical Surveys\\n Future Expansion\\n
\\n \\n
\\n
\\n

\\n \\n Supports magnetic, gravity, seismic refraction/reflection, electrical resistivity,\\n induced polarization, SP, magnetotelluric, GPR, thermal infrared, and aeromagnetic surveys.\\n Schema is pre-built — awaiting activation.\\n

\\n
\\n
\\n\\n \\n
\\n
\\n
\\n RMC\\n Rock Mass Classification (RMR / Q / GSI)\\n
\\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n
\\n\\n \\n \\n \\n
\\n
\\n
\\n
\\n
Alert Queue & Notification Center
\\n
Anomalous progress detection — Email alerts to client & receptionist
\\n
\\n \\n
\\n
\\n
\\n
\\n
\\n
\\n
Critical Hazard — Slope EXP-047 · Very High
\\n
Very High hazard level detected at site EXP-047 in Dam Site Investigation project. Immediate review and action required. Factor of safety estimated below 1.0.
\\n
2 hours ago · Recipients: client@gie.gov, reception@gie.gov · Status: Sent
\\n
\\n
\\n
\\n
\\n
\\n
Geothermal Anomaly — 96.2°C Surface Temperature
\\n
Surface temperature at GTH-003 exceeds 95°C threshold. Potential near-boiling manifestation requiring safety protocols. pH measured at 2.1 (extremely acidic).
\\n
5 hours ago · Recipients: client@gie.gov, reception@gie.gov · Status: Sent
\\n
\\n
\\n
\\n
\\n
\\n
Progress Delay — EIGIS-GIE-2026-002
\\n
No observations recorded in 8 days for active Dam Site Investigation project. Field data collection may be behind schedule.
\\n
1 day ago · Recipients: client@gie.gov, reception@gie.gov · Status: Sent
\\n
\\n
\\n
\\n
\\n
\\n
Data Quality — Missing GPS Coordinates
\\n
3 submitted observations lack GPS coordinates. Spatial queries and map rendering affected.
\\n
3 hours ago · Status: Pending
\\n
\\n
\\n
\\n
\\n
\\n
Sample Lab Results — 5 Tests Completed
\\n
Laboratory results are now available for samples S-01 through S-05 from field trip FT-2026-012.
\\n
6 hours ago · Status: Info
\\n
\\n
\\n
\\n
\\n
\\n
\\n\\n \\n \\n \\n
\\n
\\n
\\n
\\n
System Documentation & Integration Guide
\\n
EIGIS v2.0 — Microservices Architecture
\\n
\\n
\\n
\\n
\\n

📐 Database Schema

\\n

PostGIS 3.4 with 14 normalized tables supporting 3 interconnected themes:

\\n
    \\n
  • Core: projects, personnel, field_trips, observations, photos, audit_log
    • \\n
  • Surface Geological: soil_profiles, soil_horizons, rock_descriptions, lithological_units
    • \\n
  • Structural: discontinuity_measurements, slope_stability, rock_mass_classifications, landslide_inventory
    • \\n
  • Geothermal: geothermal_manifestations, geothermal_gradients
    • \\n
  • Sampling: samples (unified), geochemistry_results
    • \\n
  • Future: geophysical_surveys, drilling_data, petrography
    • \\n
\\n\\n

⚡ Spatial Indexing Strategy

\\n
    \\n
  • GiST indexes on all geometry columns for O(log n) spatial queries
    • \\n
  • Composite GiST indexes on (geom, observation_type) for filtered rendering
    • \\n
  • Geography-cast indexes for ST_DWithin radius queries
    • \\n
  • Materialized views (mv_dashboard_metrics, mv_map_clusters) for dashboard latency <50ms
    • \\n
  • ST_ClusterDBSCAN for adaptive zoom-level clustering
    • \\n
\\n\\n

🔌 API Endpoints (FastAPI)

\\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n \\n
MethodEndpointDescription
GET/api/healthSystem health check
POST/api/projectsCreate project
GET/api/projectsList projects
POST/api/observationsCreate observation
GET/api/observations/mapWebGIS map points
POST/api/soil-profilesCreate soil profile + horizons
POST/api/rock-descriptionsCreate rock description
POST/api/discontinuitiesAdd discontinuity measurement
POST/api/discontinuities/bulkBulk discontinuity insert
POST/api/slope-stabilityCreate slope assessment
POST/api/geothermal-manifestationsCreate geothermal record
POST/api/samplesCreate sample
POST/api/samples/bulkBulk sample insert
POST/api/sync/observationsMobile data sync
GET/api/spatial/nearbyFind nearby observations
GET/api/dashboard/metricsReal-time metrics
GET/api/alertsList alert queue
\\n\\n

📧 Email Alert Triggers

\\n
    \\n
  • Hazard Critical: Auto-email client + receptionist when slope hazard ≥ High
    • \\n
  • Geothermal Anomaly: Surface temp >95°C or pH outside 2–10 triggers alert
    • \\n
  • Progress Delay: No observations in 7+ days for active projects
    • \\n
  • Data Quality: Missing GPS on submitted observations
    • \\n
  • Unreviewed Hazard: High hazard observations not yet reviewed
    • \\n
\\n\\n

🏗️ Microservices Architecture

\\n
    \\n
  • API Service: FastAPI (Python 3.11) — REST API with async connection pool
    • \\n
  • Frontend: React + Leaflet — SPA with WebGIS vector workspace
    • \\n
  • Database: PostgreSQL 16 + PostGIS 3.4 — Primary data store
    • \\n
  • Object Storage: MinIO/S3 — Geotagged photo storage
    • \\n
  • Email Service: SMTP via aiosmtplib — Async notification dispatch
    • \\n
  • Cache: Redis (optional) — Session & tile caching
    • \\n
  • Monitoring: /api/health endpoint for load balancer checks
    • \\n
\\n\\n

🔗 Third-Party Integration

\\n
    \\n
  • OpenAPI 3.0 spec at /api/openapi.json
    • \\n
  • Interactive Swagger UI at /api/docs
    • \\n
  • ReDoc documentation at /api/redoc
    • \\n
  • GeoJSON output from all spatial endpoints
    • \\n
  • WMS/WFS compatibility via GeoServer bridge
    • \\n
  • Mobile sync endpoint with batch processing
    • \\n
  • Webhook support for alert notifications
    • \\n
\\n
\\n
\\n
\\n
\\n\\n
\\n\\n \\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n
\\n 2 of 8 sections complete\\n
\\n
\\n \\n \\n
\\n
\\n
\\n
\\n\\n\\n
\\n\\nHTMLEOF\\necho \\"HTML structure created\\"","timeout": 30}'}}]} {'role': 'tool', 'content': '{"output": "HTML structure created\\n", "exit_code": 0, "cwd": "/workspace"}', 'tool_call_id': 'chatcmpl-tool-a87d0ca85a2de70e'}