polojuan/agentic-workflows
0
1�
2 <~�h- � �0 � S SK r S SKrSrS rS rS rS rg)� Nzdata/rca_data.dbc �x � [ R " [ 5 n U R 5 nU R 5 g )N)�sqlite3�connect�PATH�cursor�close)�connr s �LC:\Users\61422\Desktop\OMSCS\projects\LLM\agentic-workflow\utils\database.py� create_dbr s$ � � �?�?�4� �D�
�[�[�]�F��J�J�L� c �� � [ R " [ 5 n U R 5 n[ R3 " S5 nUR
SU SSS9 U R 5 U R 5 g )Nz data/equipment_failure_data.csv�rca_data�replaceF)� if_exists�index) r r r r �pd�read_csv�to_sql�commitr )r r �dfs r4 �
create_tablesr sP � � �?�?�4� �D�
�[�[�]�F� ���6� 7�B��I�I�j�$�)�5�I�A��K�K�M��J�J�Lr c �� � [ R " [ 5 n U R 5 nUR S5 / SQnUR SU5 U R
5 U R 5 g )NaS 5 CREATE TABLE IF NOT EXISTS column_metadata (6 table_name TEXT NOT NULL,7 column_name TEXT NOT NULL,8 description TEXT,9 data_type TEXT,10 constraints TEXT,11 example_value TEXT,12 notes TEXT,13 PRIMARY KEY (table_name, column_name)14 )15 )16)r �Assetz0Mine or facility name where the failure occurred�TEXT�NOT NULLzMine A, Mine BzVIdentifies the specific mine/site asset; used for filtering investigations by location)r �Areaz/Operational area or department within the assetr r zOProcessing Plant, Rail Loading, Underground Operations, Tailings Dam, Haul RoadzHCategorizes failures by operational zone; helps identify high-risk areas)r � Equipmentz?Specific equipment name and identifier that experienced failurer r z9Crusher 1, Pump Station 2, Conveyor Belt 8, Dump Truck 15zfEquipment type and number for precise identification; includes both equipment category and unit number)r �RCA_IDz3Root Cause Analysis investigation unique identifierr r z RCA 1, RCA 2, RCA 10, RCA 50z�Groups all root causes for a single failure event; multiple rows may share the same RCA_ID if the investigation identified multiple contributing root causes)r �
Failure_Eventz;Comprehensive narrative description of the failure incidentr r zMAt 1831H on 30th October, 2025, Equipment 1 started to trip on high torque...z�Includes timestamp (date and time), symptoms, sequence of events, investigation findings, and any secondary damage; provides complete context for understanding the failure)r �ImpactzOTotal financial amount in Australian Dollars (AUD) of the impact of the failure�INTEGER�Nonez 1000000, 2500000, 850000, 420000z�Aggregate cost including production losses, repair costs, labor, parts, and any secondary damages; used for prioritizing corrective actions and calculating ROI)r �Downtimez*Total equipment downtime duration in hoursr! r" z32, 96, 48, 18z�Measures operational impact; from failure initiation to equipment return to service; used for availability and reliability metrics)r �17Root_Causez*Identified underlying cause of the failurer r zJMisalignment, Overloading, Inadequate lubrication schedule, Operator errorz�Specific root cause identified through investigation; one failure event may have multiple root causes, each stored as a separate row with the same RCA ID)r �Actionz+Recommended corrective or preventive actionr r zSImplement routine maintenance, Install monitoring system, Enhance operator trainingz�Specific action designed to address the identified root cause and prevent recurrence; each root cause has its own corresponding action)r �
Action_Statusz6Current implementation status of the corrective actionr zDEFAULT In ProgresszCompleted, In ProgresszjTracks whether the corrective action has been fully implemented; defaults to "In Progress" for new entriesz�18 INSERT OR REPLACE INTO column_metadata 19 (table_name, column_name, description, data_type, constraints, example_value, notes)20 VALUES (?, ?, ?, ?, ?, ?, ?)21 )r r r r �execute� executemanyr r )r r �metadatas r22 �create_metadatar* sc � � �?�?�4� �D�
�[�[�]�F�23�N�N� � �F�H�P ��� � 24� �25 �K�K�M��J�J�Lr c �8 � [ R " [ 5 n U R 5 nU R S5 R 5 nSSR
U Vs/ s H o3S SUS SUS S US26 3PM sn5 - nU R 5 U$ s snf ) Nz SELECT * FROM column_metadataztable name: rca_data27�28� z, Description: � z, Type: � z , Notes: � )r r r r r' �fetchall�joinr )r r �rows�r� meta_schemas r29 �get_metaschemar6 v s� � � �?�?�4� �D�
�[�[�]�F� �<�<�7� 8� A� A� C�D�*�T�Y�Y� ~B� 8C� ~B�xy�Q�4�&��PQ�RS�PT�v�U]�^_�`a�^b�]c�cl�mn�op�mq�lr�8s� ~B� 8C� .D� D�K��J�J�L� ��� 8Cs �"B30)r �pandasr r r r r* r6 � r r31 �<module>r9 s& �� � ���� �_�Dr