📂 Corporate Data Manager - Read/Write Layer (Stage 1)

📌 Project Overview

This directory houses the core Read/Write Layer for the Corporate Data Manager system. It acts as the exclusive logical data engine bridging system components with the physical underlying storage structures (storage_data_1). This layer sanitizes transaction payloads, enforces rigorous business boundary rules, handles complex multidimensional filtering, and abstracts low-level SQL execution into clean Python data structures.

🏗️ Architecture Role & Design Patterns

In the project’s N-Tier architecture, this layer serves as the logical access gatekeeper between application logic controllers (like the Task Manager) and the database: * Data Validation: Enforces strict numeric, value, and domain business rules before compiling queries or hitting disk storage. * Query Abstraction: Automatically converts raw relational database tuples into standard, clean JSON-like Python dictionaries (dict) using dynamic structural column mappings. * Security Isolation: Eliminates SQL Injection vulnerabilities project-wide via strictly typed, dynamic parameterized query structures (%s). * Transaction Control: Guarantees database state integrity. Changes are safely isolated, applying automated connection rollback (conn.rollback()) schemes upon structural lockouts or query execution failures.

🧬 Core Technical Performance Architectures

1. Ingestion Performance Optimization (NVMe Speed Tuning)

The layer features specialized, production-ready bulk injection utilities to maximize physical hardware write speeds: * bulk_insert_companies: Accepts list payloads of company variables, routes them through standard validations, and leverages cursor.executemany() bounded within a single database commit sequence to achieve high-speed disk writing. * bulk_insert_employees: Utilizes an Internal Cache-Mapping Pattern. To prevent performance-killing database hits on every record, it aggregates unique corporate records in memory and maps names to Company_id fields using a single comprehensive batch query (SELECT ... WHERE IN (...)) before running structural updates.

2. Elastic Parameterized Search Engines

Retrieval processors (get_employees, search_companies, get_events_filtered, get_financials_filtered) utilize a dynamic text concatenation framework. They programmatically build conditional WHERE ... AND ... logical chains in real-time based on arbitrary user keywords while explicitly wrapping parameters as tuples to uphold security invariants.

3. Temporal Period Resolution

The layer features automated temporal translators (such as in get_events_by_period). It takes abstract fiscal parameters (year, quarter) and converts them programmatically into concrete, zero-padded standard ISO date strings boundary limits for SQL parsing.

🛠️ Functional Modules & API Matrix

1. Data Ingestion & Mutation (CRUD - Write/Update)

Function Signature	Primary Objective / Validation Bounds Enforced
`insert_company_data(...)`	Writes a single corporate profile. Enforces non-empty names/sectors and validates non-negative headcounts and financials.
`insert_event_data(...)`	Maps corporate milestones (`Layoff`, `Expansion`, `Merger`, etc.). Validates that the foreign key `Company_id` exists before running insertion.
`insert_financial_data(...)`	Logs a fiscal snapshot. Restricts `Year` (1900-2100), `Quarter` (1-4), `employee_cost_ratio` (0-1), and `profit_margin` (-1 to 1). Proactively blocks duplicate entries for the same reporting period.
`update_company_data(...)`	Performs dynamic updates on specific corporate values; features short-circuit protections if update arrays are blank.
`update_employee_data(...)`	Modifies team attributes. Enforces explicit personnel bounds (`Age` between 18-65; `Status` limited to Active, Resigned, or Laid-off).
`update_financial_data(...)`	Runs atomic modifications on ledger rows; handles data safety values during mutations.

2. Advanced Retrieval Engines (CRUD - Read)

Function Signature	Technical Operations Performed
`_execute_select(query, params)`	The base execution harness. Handles data mapping, rolls back transactions on locks, and issues infrastructure tracking footprints.
`get_company_by_id(id)` / `get_all_companies(...)`	Returns single or structured lists of company properties.
`search_companies(sector, min_rev, max_rev, ...)`	Allows multi-conditional searches matching corporate traits.
`get_employees(...)` / `get_employees_by_company(...)`	Extracts staff tables matching active roles or department divisions.
`get_events_by_company(...)`	Retrieves structural event histories automatically sorted in descending chronological order (`ORDER BY Event_date DESC`).
`get_financials_by_company(...)`	Pulls fiscal performance metrics sorted via multi-column hierarchies (`ORDER BY Year DESC, Quarter DESC`).

📊 Infrastructure Logging & Error Auditing

Every core function is encapsulated inside robust error boundary wrappers. When execution faults emerge, the layer routes logs to two tracking targets: 1. Log_System\Read_Write_1.log: A dedicated transaction ledger recording SQL strings, parameter arrays, state updates, and standard query performance footprints. 2. Log_System\main_1.log: A centralized high-priority operations log capturing CRITICAL state failures and full database exception tracebacks.

🚀 Quick Start

Environment Setup: Ensure your .env configuration file exists at the root directory with proper values (DB_HOST, DB_USER, DB_PASSWORD).
Dependencies: Verify mysql-connector-python is installed in your python runtime environment.
Database Check: Run the storage.py configuration routine first to ensure underlying physical tables are created.
Execution Test: Execute the script directly to verify performance using the built-in sanity baseline: python # Execution runs an internal test entry automatically: result = insert_company_data("ABCD", "TECH", 50, 1000.0, 10000.0) print(result) # Output: Company data inserted successfully.