-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `capa`
--

-- --------------------------------------------------------

--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `aid` tinyint(4) NOT NULL,
  `account` varchar(20) NOT NULL,
  `description` text NOT NULL,
  `settings` varchar(50) NOT NULL,
  `user` text NOT NULL,
  `garbage` text NOT NULL,
  `collector` text NOT NULL,
  `account_status` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `account`
--

INSERT INTO `account` (`aid`, `account`, `description`, `settings`, `user`, `garbage`, `collector`, `account_status`) VALUES
(1, 'SUPERADMIN', '<p>Description</p>', 'All', 'Add,Edit,View,List,List_all,Myaccount,Remove,Repor', 'Add,Edit,View,List,List_all,Remove', 'Add,Edit,View,List,List_all,Remove', 'Active'),
(2, 'ADMIN', '<p>Description</p>', '', 'Add,Edit,View,List,List_all,Myaccount,Remove', 'Add,Edit,View,List,Remove', 'Add,Edit,View,List,Remove', 'Active'),
(3, 'USER', '<p>Description</p>', '', 'View,List,Myaccount', '', 'List', 'Active'),
(4, 'COLLECTOR', '<p>Description</p>', '', 'View,Myaccount', 'Add,View,List', 'View', 'Active');

-- --------------------------------------------------------

--
-- Table structure for table `capas`
--

CREATE TABLE `capas` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_no` varchar(40) NOT NULL,
  `title` varchar(255) NOT NULL,
  `problem_statement` text NOT NULL,
  `source` varchar(80) DEFAULT NULL,
  `source_reference` varchar(120) DEFAULT NULL,
  `severity` varchar(30) NOT NULL DEFAULT 'Minor',
  `priority` varchar(30) NOT NULL DEFAULT 'Medium',
  `status` varchar(40) NOT NULL DEFAULT 'Open',
  `workflow_stage` varchar(50) NOT NULL DEFAULT 'Draft',
  `approval_status` varchar(30) NOT NULL DEFAULT 'Not Submitted',
  `submitted_at` datetime DEFAULT NULL,
  `submitted_by` int(10) UNSIGNED DEFAULT NULL,
  `final_approved_at` datetime DEFAULT NULL,
  `final_approved_by` int(10) UNSIGNED DEFAULT NULL,
  `detected_date` date NOT NULL,
  `target_date` date DEFAULT NULL,
  `closed_date` date DEFAULT NULL,
  `owner_id` int(10) UNSIGNED DEFAULT NULL,
  `containment_action` text DEFAULT NULL,
  `root_cause` text DEFAULT NULL,
  `root_cause_source` varchar(30) DEFAULT NULL,
  `root_cause_confidence` decimal(5,2) DEFAULT NULL,
  `corrective_action` text DEFAULT NULL,
  `preventive_action` text DEFAULT NULL,
  `effectiveness_check` text DEFAULT NULL,
  `verification_date` date DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `capas`
--

INSERT INTO `capas` (`id`, `capa_no`, `title`, `problem_statement`, `source`, `source_reference`, `severity`, `priority`, `status`, `workflow_stage`, `approval_status`, `submitted_at`, `submitted_by`, `final_approved_at`, `final_approved_by`, `detected_date`, `target_date`, `closed_date`, `owner_id`, `containment_action`, `root_cause`, `root_cause_source`, `root_cause_confidence`, `corrective_action`, `preventive_action`, `effectiveness_check`, `verification_date`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'CAPA-2026-0001', 'Repeated packaging seal failure', 'Seal failures were observed during final inspection, causing product rejection.', 'Inspection', NULL, 'Major', 'High', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-05', '2026-08-25', NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2026-08-16 21:00:47', '2026-08-16 21:03:01'),
(2, 'CAPA-2026-0002', 'Incorrect product labels on finished cartons', 'Finished cartons were found with an incorrect revision of the product label during final inspection.', 'Final Inspection', 'FI-2026-0811', 'Major', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-07-28', '2026-08-08', '2026-08-07', 2, 'Quarantined affected cartons and stopped dispatch until all labels were verified.', 'The label master file was not linked to the active production revision and the final inspection checklist did not require label revision verification.', NULL, NULL, 'Updated the controlled label master and added mandatory revision verification at packing.', 'Implemented electronic label issue control and quarterly review of controlled documents.', 'No incorrect labels detected in the next 10 production lots and 100% checklist compliance.', '2026-08-14', 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(3, 'CAPA-2026-0003', 'High moisture content in raw material', 'Incoming polymer granules exceeded the specified moisture limit, resulting in unstable processing conditions.', 'Incoming Inspection', 'IQC-2026-0729', 'Major', 'High', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-07-29', '2026-08-22', NULL, 4, 'Blocked the affected material lot and moved it to controlled storage.', 'Supplier drying parameters were not consistently maintained and moisture verification frequency was inadequate.', NULL, NULL, 'Revised supplier incoming inspection criteria and introduced moisture testing for every incoming lot.', 'Added supplier process audit requirement and monthly trend review of incoming moisture results.', NULL, NULL, 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(4, 'CAPA-2026-0004', 'Calibration overdue for production gauge', 'A production dimensional gauge was found in use beyond its calibration due date.', 'Internal Audit', 'IA-2026-0802', 'Minor', 'Medium', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-08-02', '2026-08-10', '2026-08-09', 2, 'Removed the gauge from service and verified recent measurements with a calibrated reference gauge.', 'The calibration reminder was sent to an inactive mailbox and there was no escalation mechanism for overdue instruments.', NULL, NULL, 'Updated the calibration register and assigned active responsible persons.', 'Added automatic escalation for overdue calibration items and weekly dashboard review.', 'All 47 active gauges were checked and 100% were within calibration status.', '2026-08-12', 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(5, 'CAPA-2026-0005', 'Dimensional variation in machined component', 'Customer complaints identified dimensional variation on the shaft diameter of a machined component.', 'Customer Complaint', 'CC-2026-0810', 'Critical', 'High', 'Under Verification', 'Effectiveness Review', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-10', '2026-08-28', NULL, 4, 'Stopped shipment of the affected batch and performed 100% dimensional inspection.', 'Tool wear was not being monitored using a defined replacement limit and process capability was not reviewed by shift.', NULL, NULL, 'Introduced tool-life limits and mandatory first-piece verification after tool change.', 'Added SPC monitoring and weekly review of Cp/Cpk for the critical diameter.', NULL, NULL, 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(6, 'CAPA-2026-0006', 'Packaging seal strength below specification', 'Seal strength failures were observed during final inspection on the packaging line.', 'Inspection', 'FI-2026-0805', 'Major', 'High', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-05', '2026-08-25', NULL, 4, 'Quarantined the affected production window and increased seal-strength sampling to every 30 minutes.', 'Seal contamination and inconsistent sealing temperature were identified as the primary contributors.', NULL, NULL, 'Cleaned the sealing jaws, standardized temperature settings and replaced a worn thermocouple.', 'Introduced preventive cleaning frequency, thermocouple verification and SPC monitoring of seal temperature.', NULL, NULL, 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(7, 'CAPA-2026-0007', 'Incorrect torque on assembly fasteners', 'Torque audit identified multiple fasteners below the specified tightening range.', 'Process Audit', 'PA-2026-0812', 'Major', 'Medium', 'Open', 'Draft', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-12', '2026-08-30', NULL, 3, 'Stopped the affected assembly station and rechecked all units produced during the shift.', 'Torque wrench verification was not performed at the beginning of every shift and the work instruction did not specify a verification frequency.', NULL, NULL, 'Introduced start-of-shift torque verification and revised the assembly work instruction.', 'Added digital torque-tool monitoring and monthly compliance audits.', NULL, NULL, 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(8, 'CAPA-2026-0008', 'Surface scratches after final handling', 'Visible scratches were detected on finished components during final visual inspection.', 'Final Inspection', 'FI-2026-0813', 'Minor', 'Low', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-08-13', '2026-08-15', '2026-08-15', 3, 'Segregated affected units and introduced protective separators at the inspection table.', 'Finished components were placed directly on an unprotected metal surface during transfer.', NULL, NULL, 'Installed polymer work-surface protection and revised handling instructions.', 'Added weekly 5S inspection and visual standard for finished-product handling.', 'Zero scratches found in the next 500 inspected units.', '2026-08-16', 1, '2026-08-16 15:32:21', '2026-08-16 21:03:01'),
(9, 'CAPA-2026-0009', 'Supplier certificate does not match incoming batch', 'The certificate of analysis supplied with an incoming material batch contained a specification revision different from the approved purchase specification.', 'Supplier Inspection', NULL, 'Major', 'High', 'Open', 'Draft', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-14', '2026-08-28', NULL, 3, 'Quarantined the material lot and suspended release until certificate and material identity were verified.', 'Supplier document control did not synchronize the approved specification revision with the certificate template.', NULL, NULL, 'Updated supplier certificate review checklist and required revision verification before material release.', 'Added supplier document revision control to the approved supplier audit checklist.', NULL, NULL, 1, '2026-08-16 21:03:15', '2026-08-16 15:58:06'),
(10, 'CAPA-2026-0010', 'Incorrect torque verification frequency', 'Torque verification records showed that one assembly station did not complete the required start-of-shift verification on two occasions.', 'Process Audit', NULL, 'Major', 'Medium', 'Pending Verification', 'Draft', 'Not Submitted', '2026-08-05 10:30:00', 1, NULL, NULL, '2026-08-04', '2026-08-20', NULL, 4, 'Affected units were identified and 100% torque verification was performed.', 'The work instruction did not clearly identify the start-of-shift verification as a mandatory recorded activity.', NULL, NULL, 'Revised the work instruction and introduced a mandatory digital verification checklist.', 'Added weekly compliance review and escalation for missed torque verification.', 'No missed verifications were recorded for 12 consecutive shifts after implementation.', '2026-08-15', 1, '2026-08-16 21:03:15', '2026-08-16 15:58:06'),
(11, 'CAPA-2026-0011', 'Missing inspection records for batch release', 'Quality event requiring investigation, corrective action and preventive controls for: Missing inspection records for batch release.', 'Internal Audit', 'IA-2026-0328', 'Minor', 'Medium', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-03-28', '2026-04-08', '2026-04-07', 2, 'Batch release was temporarily held pending record reconstruction.', 'Manual filing allowed records to be misplaced.', NULL, NULL, 'Introduced indexed electronic batch records.', 'Added weekly record completeness audit.', '100% of sampled batch files were complete.', '2026-04-15', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(12, 'CAPA-2026-0012', 'Incorrect component orientation during assembly', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect component orientation during assembly.', 'Process Audit', 'PA-2026-0406', 'Major', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-04-06', '2026-04-20', '2026-04-18', 3, 'Stopped the assembly cell and inspected affected units.', 'Fixture allowed the component to be loaded in two orientations.', NULL, NULL, 'Modified fixture with poka-yoke feature.', 'Added fixture verification to start-up checklist.', 'No orientation errors in 2,000 subsequent assemblies.', '2026-04-25', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(13, 'CAPA-2026-0013', 'Excessive vibration in finished motor', 'Quality event requiring investigation, corrective action and preventive controls for: Excessive vibration in finished motor.', 'Customer Complaint', 'CC-2026-0415', 'Critical', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-04-15', '2026-05-02', '2026-04-30', 4, 'Stopped shipment and performed 100% vibration screening.', 'Rotor balancing procedure did not include the revised tolerance.', NULL, NULL, 'Rebalanced affected rotors and revised balancing specification.', 'Added automated vibration monitoring at final test.', 'All subsequent units passed vibration limits.', '2026-05-08', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(14, 'CAPA-2026-0014', 'Wrong revision of work instruction at workstation', 'Quality event requiring investigation, corrective action and preventive controls for: Wrong revision of work instruction at workstation.', 'Document Control', 'DC-2026-0422', 'Major', 'Medium', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-04-22', '2026-05-05', '2026-05-04', 2, 'Removed obsolete instructions from the workstation.', 'Printed controlled copies were not reconciled after document revision.', NULL, NULL, 'Replaced all workstation copies and updated distribution list.', 'Added monthly controlled-document verification.', 'No obsolete copies found in the next audit.', '2026-05-12', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(15, 'CAPA-2026-0015', 'Leak detected during pressure testing', 'Quality event requiring investigation, corrective action and preventive controls for: Leak detected during pressure testing.', 'Final Test', 'FT-2026-0503', 'Critical', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-05-03', '2026-05-18', '2026-05-17', 4, 'Quarantined affected assemblies and increased pressure-test sampling.', 'Seal installation torque was inconsistent.', NULL, NULL, 'Standardized seal installation torque and retrained operators.', 'Introduced torque traceability and layered process audits.', 'Leak rate reduced to zero across 500 tested assemblies.', '2026-05-24', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(16, 'CAPA-2026-0016', 'Barcode unreadable on finished product', 'Quality event requiring investigation, corrective action and preventive controls for: Barcode unreadable on finished product.', 'Packaging Inspection', 'PI-2026-0511', 'Minor', 'Low', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-05-11', '2026-05-20', '2026-05-19', 3, 'Reprinted affected labels and verified scanner readability.', 'Printer head contamination reduced print contrast.', NULL, NULL, 'Cleaned printer head and replaced worn ribbon.', 'Added daily printer verification and cleaning checklist.', 'Barcode readability remained above 99.8%.', '2026-05-27', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(17, 'CAPA-2026-0017', 'Weld penetration below requirement', 'Quality event requiring investigation, corrective action and preventive controls for: Weld penetration below requirement.', 'Process Audit', 'PA-2026-0520', 'Critical', 'High', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-05-20', '2026-06-10', NULL, 4, 'Segregated affected weldments and initiated destructive testing.', 'Welding current drifted outside the validated range.', NULL, NULL, 'Recalibrated welding controller and established parameter limits.', 'Added automatic parameter capture and weekly weld audit.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(18, 'CAPA-2026-0018', 'Incorrect raw material grade received', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect raw material grade received.', 'Incoming Inspection', 'IQC-2026-0602', 'Major', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-06-02', '2026-06-15', '2026-06-14', 2, 'Blocked the material lot and verified supplier certificates.', 'Supplier labeling was not independently checked against purchase order requirements.', NULL, NULL, 'Added dual verification of material grade at receipt.', 'Introduced supplier barcode identification and monthly supplier scorecard.', 'All 12 subsequent receipts matched purchase specifications.', '2026-06-22', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(19, 'CAPA-2026-0019', 'Operator training expired for critical process', 'Quality event requiring investigation, corrective action and preventive controls for: Operator training expired for critical process.', 'Internal Audit', 'IA-2026-0610', 'Major', 'Medium', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-06-10', '2026-06-24', '2026-06-23', 3, 'Removed unqualified operators from the process until retraining.', 'Training matrix did not trigger escalation before expiry.', NULL, NULL, 'Updated training matrix and completed refresher training.', 'Added automated expiry alerts at 30, 15 and 7 days.', '100% of critical-process operators are currently qualified.', '2026-06-30', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(20, 'CAPA-2026-0020', 'Foreign particle contamination in assembly', 'Quality event requiring investigation, corrective action and preventive controls for: Foreign particle contamination in assembly.', 'Customer Complaint', 'CC-2026-0618', 'Critical', 'High', 'Under Verification', 'Effectiveness Review', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-06-18', '2026-07-08', NULL, 2, 'Quarantined affected units and performed controlled cleaning.', 'Cleaning verification was visual only and did not include defined acceptance criteria.', NULL, NULL, 'Introduced particle-count verification after cleaning.', 'Added environmental monitoring and weekly housekeeping audits.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(21, 'CAPA-2026-0021', 'Incorrect dimensions after machine setup', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect dimensions after machine setup.', 'First Article Inspection', 'FAI-2026-0624', 'Major', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-06-24', '2026-07-08', '2026-07-07', 4, 'Stopped production and completed first-piece reinspection.', 'Setup sheet did not identify the critical dimensions requiring independent verification.', NULL, NULL, 'Revised setup sheet and added first-piece sign-off.', 'Introduced digital setup checklist with mandatory critical-dimension confirmation.', 'First-piece compliance reached 100% over four weeks.', '2026-07-15', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(22, 'CAPA-2026-0022', 'Packing quantity mismatch', 'Quality event requiring investigation, corrective action and preventive controls for: Packing quantity mismatch.', 'Final Inspection', 'FI-2026-0701', 'Minor', 'Low', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-07-01', '2026-07-10', '2026-07-09', 3, 'Recounted and corrected all affected cartons.', 'Manual counting was performed without an independent verification step.', NULL, NULL, 'Added two-person quantity verification for selected products.', 'Introduced weight-based quantity check for high-volume packs.', 'No quantity mismatch in 2,500 subsequent cartons.', '2026-07-16', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(23, 'CAPA-2026-0023', 'Surface corrosion after storage', 'Quality event requiring investigation, corrective action and preventive controls for: Surface corrosion after storage.', 'Customer Complaint', 'CC-2026-0705', 'Major', 'Medium', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-07-05', '2026-07-25', NULL, 2, 'Segregated affected stock and inspected storage conditions.', 'Humidity control limits were not defined for the storage area.', NULL, NULL, 'Installed humidity monitoring and revised storage requirements.', 'Added daily humidity review and alarm escalation.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(24, 'CAPA-2026-0024', 'Incorrect software parameter loaded to test station', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect software parameter loaded to test station.', 'Final Test', 'FT-2026-0710', 'Critical', 'High', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-07-10', '2026-07-22', '2026-07-21', 4, 'Locked the test station and revalidated affected test results.', 'Manual parameter selection allowed obsolete test files to be loaded.', NULL, NULL, 'Implemented controlled parameter library with revision lock.', 'Added software checksum verification before test release.', 'All test stations passed parameter verification.', '2026-07-29', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(25, 'CAPA-2026-0025', 'Torque value recorded incorrectly', 'Quality event requiring investigation, corrective action and preventive controls for: Torque value recorded incorrectly.', 'Process Audit', 'PA-2026-0714', 'Major', 'Medium', 'Open', 'Draft', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-07-14', '2026-08-05', NULL, 3, 'Rechecked torque on all units produced since the previous verification.', 'Manual recording caused transcription errors.', NULL, NULL, 'Introduced digital torque capture.', 'Added daily reconciliation between tool output and batch records.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(26, 'CAPA-2026-0026', 'Cracks found in molded housing', 'Quality event requiring investigation, corrective action and preventive controls for: Cracks found in molded housing.', 'Final Inspection', 'FI-2026-0720', 'Critical', 'High', 'Under Verification', 'Effectiveness Review', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-07-20', '2026-08-12', NULL, 4, 'Quarantined the affected lot and completed 100% visual inspection.', 'Mold cooling was uneven and cycle time was not controlled.', NULL, NULL, 'Balanced cooling channels and standardized cycle time.', 'Added mold temperature trend monitoring.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(27, 'CAPA-2026-0027', 'Incorrect dispatch documentation', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect dispatch documentation.', 'Logistics Audit', 'LA-2026-0725', 'Minor', 'Low', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-07-25', '2026-08-04', '2026-08-03', 2, 'Held dispatches until documentation was corrected.', 'Dispatch checklist did not require second-person verification.', NULL, NULL, 'Revised dispatch checklist.', 'Added monthly logistics-document audit.', 'Zero documentation errors in the next 100 dispatches.', '2026-08-10', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(28, 'CAPA-2026-0028', 'Seal leak after environmental exposure', 'Quality event requiring investigation, corrective action and preventive controls for: Seal leak after environmental exposure.', 'Reliability Test', 'RT-2026-0801', 'Major', 'High', 'In Progress', 'Action', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-01', '2026-08-22', NULL, 4, 'Stopped release of affected configuration pending testing.', 'Seal material compatibility was not evaluated for the revised chemical exposure condition.', NULL, NULL, 'Qualified alternate seal material and updated specification.', 'Added environmental compatibility review to engineering change process.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(29, 'CAPA-2026-0029', 'Label print contrast below specification', 'Quality event requiring investigation, corrective action and preventive controls for: Label print contrast below specification.', 'Packaging Inspection', 'PI-2026-0806', 'Minor', 'Medium', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-08-06', '2026-08-14', '2026-08-13', 3, 'Reprinted affected labels and verified scanner performance.', 'Printer density setting drifted after maintenance.', NULL, NULL, 'Standardized printer calibration settings.', 'Added print-quality check at start of each shift.', 'All labels passed contrast and scan verification.', '2026-08-18', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(30, 'CAPA-2026-0030', 'Unexpected downtime of critical inspection equipment', 'Quality event requiring investigation, corrective action and preventive controls for: Unexpected downtime of critical inspection equipment.', 'Equipment Audit', 'EA-2026-0811', 'Major', 'Medium', 'Open', 'Draft', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-11', '2026-08-29', NULL, 4, 'Provided backup inspection equipment and rescheduled affected checks.', 'Preventive maintenance interval was based on calendar time rather than usage hours.', NULL, NULL, 'Revised maintenance interval using equipment usage data.', 'Added condition-based maintenance monitoring.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(31, 'CAPA-2026-0031', 'Incorrect sampling frequency in incoming inspection', 'Quality event requiring investigation, corrective action and preventive controls for: Incorrect sampling frequency in incoming inspection.', 'Quality System Audit', 'QSA-2026-0815', 'Major', 'High', 'Open', 'Draft', 'Not Submitted', NULL, NULL, NULL, NULL, '2026-08-15', '2026-09-02', NULL, 2, 'Increased inspection frequency pending procedure revision.', 'Sampling plan was not updated after supplier risk classification changed.', NULL, NULL, 'Revised sampling plan based on supplier risk.', 'Added automatic review of sampling plans after supplier score changes.', NULL, NULL, 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(32, 'CAPA-2026-0032', 'Minor cosmetic defect on finished housing', 'Quality event requiring investigation, corrective action and preventive controls for: Minor cosmetic defect on finished housing.', 'Final Inspection', 'FI-2026-0816', 'Minor', 'Low', 'Closed', 'Closed', 'Approved', NULL, NULL, NULL, NULL, '2026-08-16', '2026-08-20', '2026-08-19', 3, 'Segregated cosmetic rejects and reviewed visual standards.', 'Lighting level at inspection station was inconsistent.', NULL, NULL, 'Standardized inspection lighting and visual acceptance samples.', 'Added monthly lighting verification and visual-standard review.', 'Reject rate remained below target for four consecutive weeks.', '2026-08-25', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `capa_actions`
--

CREATE TABLE `capa_actions` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED NOT NULL,
  `action_type` varchar(30) NOT NULL,
  `description` text NOT NULL,
  `owner_id` int(10) UNSIGNED DEFAULT NULL,
  `due_date` date NOT NULL,
  `completed_date` date DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'Planned',
  `evidence` text DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `capa_actions`
--

INSERT INTO `capa_actions` (`id`, `capa_id`, `action_type`, `description`, `owner_id`, `due_date`, `completed_date`, `status`, `evidence`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Containment', 'Quarantine all cartons produced during the affected production window.', 2, '2026-08-06', '2026-08-05', 'Completed', 'Quarantine record QR-2026-081', 'All affected stock identified.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 1, 'Corrective', 'Clean sealing jaws and standardize sealing temperature setting.', 4, '2026-08-10', '2026-08-09', 'Completed', 'Maintenance report MR-2026-811', 'Temperature verified with reference thermometer.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(3, 1, 'Preventive', 'Introduce weekly seal-temperature trend review and thermocouple verification.', 2, '2026-08-20', NULL, 'In Progress', NULL, 'First two weeks show stable temperature trend.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(4, 5, 'Corrective', 'Replace worn cutting tool and establish tool-life limit.', 4, '2026-08-15', '2026-08-14', 'Completed', 'Tool change record TCR-2026-0814', 'Replacement completed before restart.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(5, 5, 'Preventive', 'Implement SPC dashboard for critical shaft diameter and weekly Cp/Cpk review.', 2, '2026-08-25', NULL, 'Planned', NULL, 'To be reviewed after four consecutive stable batches.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(6, 7, 'Corrective', 'Calibrate the torque wrench and perform 100% torque verification on affected units.', 3, '2026-08-16', '2026-08-15', 'Completed', 'Torque verification report TVR-2026-0815', 'All affected units passed after rework.', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(7, 10, 'Corrective', 'Revise work instruction and introduce mandatory digital torque verification checklist.', 4, '2026-08-10', '2026-08-09', 'Completed', 'WI revision WI-2026-104', 'Implemented and briefed all operators.', 1, '2026-08-16 21:03:15', '2026-08-16 21:03:15'),
(8, 10, 'Preventive', 'Review torque verification compliance weekly and escalate missed records.', 2, '2026-08-15', '2026-08-15', 'Completed', 'Compliance review CR-2026-0815', '12 consecutive shifts compliant.', 1, '2026-08-16 21:03:15', '2026-08-16 21:03:15'),
(9, 9, 'Corrective', 'Implement corrective action for CAPA-2026-0009: Added enhanced incoming thickness sampling.', 2, '2026-03-25', '2026-03-24', 'Completed', 'Evidence record CAPA-0009-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(10, 9, 'Preventive', 'Implement preventive control for CAPA-2026-0009: Added supplier KPI review and quarterly process audit.', 2, '2026-04-01', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(11, 10, 'Corrective', 'Implement corrective action for CAPA-2026-0010: Reset oven profile and verified calibration.', 4, '2026-04-03', '2026-04-02', 'Completed', 'Evidence record CAPA-0010-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(12, 10, 'Preventive', 'Implement preventive control for CAPA-2026-0010: Added automatic oven parameter alarm and weekly trend review.', 2, '2026-04-10', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(13, 11, 'Corrective', 'Implement corrective action for CAPA-2026-0011: Introduced indexed electronic batch records.', 2, '2026-04-08', '2026-04-07', 'Completed', 'Evidence record CAPA-0011-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(14, 11, 'Preventive', 'Implement preventive control for CAPA-2026-0011: Added weekly record completeness audit.', 2, '2026-04-15', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(15, 12, 'Corrective', 'Implement corrective action for CAPA-2026-0012: Modified fixture with poka-yoke feature.', 3, '2026-04-20', '2026-04-18', 'Completed', 'Evidence record CAPA-0012-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(16, 12, 'Preventive', 'Implement preventive control for CAPA-2026-0012: Added fixture verification to start-up checklist.', 2, '2026-04-27', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(17, 13, 'Corrective', 'Implement corrective action for CAPA-2026-0013: Rebalanced affected rotors and revised balancing specification.', 4, '2026-05-02', '2026-04-30', 'Completed', 'Evidence record CAPA-0013-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(18, 13, 'Preventive', 'Implement preventive control for CAPA-2026-0013: Added automated vibration monitoring at final test.', 2, '2026-05-09', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(19, 14, 'Corrective', 'Implement corrective action for CAPA-2026-0014: Replaced all workstation copies and updated distribution list.', 2, '2026-05-05', '2026-05-04', 'Completed', 'Evidence record CAPA-0014-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(20, 14, 'Preventive', 'Implement preventive control for CAPA-2026-0014: Added monthly controlled-document verification.', 2, '2026-05-12', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(21, 15, 'Corrective', 'Implement corrective action for CAPA-2026-0015: Standardized seal installation torque and retrained operators.', 4, '2026-05-18', '2026-05-17', 'Completed', 'Evidence record CAPA-0015-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(22, 15, 'Preventive', 'Implement preventive control for CAPA-2026-0015: Introduced torque traceability and layered process audits.', 2, '2026-05-25', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(23, 16, 'Corrective', 'Implement corrective action for CAPA-2026-0016: Cleaned printer head and replaced worn ribbon.', 3, '2026-05-20', '2026-05-19', 'Completed', 'Evidence record CAPA-0016-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(24, 16, 'Preventive', 'Implement preventive control for CAPA-2026-0016: Added daily printer verification and cleaning checklist.', 2, '2026-05-27', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(25, 17, 'Corrective', 'Implement corrective action for CAPA-2026-0017: Recalibrated welding controller and established parameter limits.', 4, '2026-06-10', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(26, 17, 'Preventive', 'Implement preventive control for CAPA-2026-0017: Added automatic parameter capture and weekly weld audit.', 2, '2026-06-17', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(27, 18, 'Corrective', 'Implement corrective action for CAPA-2026-0018: Added dual verification of material grade at receipt.', 2, '2026-06-15', '2026-06-14', 'Completed', 'Evidence record CAPA-0018-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(28, 18, 'Preventive', 'Implement preventive control for CAPA-2026-0018: Introduced supplier barcode identification and monthly supplier scorecard.', 2, '2026-06-22', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(29, 19, 'Corrective', 'Implement corrective action for CAPA-2026-0019: Updated training matrix and completed refresher training.', 3, '2026-06-24', '2026-06-23', 'Completed', 'Evidence record CAPA-0019-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(30, 19, 'Preventive', 'Implement preventive control for CAPA-2026-0019: Added automated expiry alerts at 30, 15 and 7 days.', 2, '2026-07-01', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(31, 20, 'Corrective', 'Implement corrective action for CAPA-2026-0020: Introduced particle-count verification after cleaning.', 2, '2026-07-08', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(32, 20, 'Preventive', 'Implement preventive control for CAPA-2026-0020: Added environmental monitoring and weekly housekeeping audits.', 2, '2026-07-15', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(33, 21, 'Corrective', 'Implement corrective action for CAPA-2026-0021: Revised setup sheet and added first-piece sign-off.', 4, '2026-07-08', '2026-07-07', 'Completed', 'Evidence record CAPA-0021-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(34, 21, 'Preventive', 'Implement preventive control for CAPA-2026-0021: Introduced digital setup checklist with mandatory critical-dimension confirmation.', 2, '2026-07-15', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(35, 22, 'Corrective', 'Implement corrective action for CAPA-2026-0022: Added two-person quantity verification for selected products.', 3, '2026-07-10', '2026-07-09', 'Completed', 'Evidence record CAPA-0022-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(36, 22, 'Preventive', 'Implement preventive control for CAPA-2026-0022: Introduced weight-based quantity check for high-volume packs.', 2, '2026-07-17', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(37, 23, 'Corrective', 'Implement corrective action for CAPA-2026-0023: Installed humidity monitoring and revised storage requirements.', 2, '2026-07-25', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(38, 23, 'Preventive', 'Implement preventive control for CAPA-2026-0023: Added daily humidity review and alarm escalation.', 2, '2026-08-01', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(39, 24, 'Corrective', 'Implement corrective action for CAPA-2026-0024: Implemented controlled parameter library with revision lock.', 4, '2026-07-22', '2026-07-21', 'Completed', 'Evidence record CAPA-0024-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(40, 24, 'Preventive', 'Implement preventive control for CAPA-2026-0024: Added software checksum verification before test release.', 2, '2026-07-29', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(41, 25, 'Corrective', 'Implement corrective action for CAPA-2026-0025: Introduced digital torque capture.', 3, '2026-08-05', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(42, 25, 'Preventive', 'Implement preventive control for CAPA-2026-0025: Added daily reconciliation between tool output and batch records.', 2, '2026-08-12', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(43, 26, 'Corrective', 'Implement corrective action for CAPA-2026-0026: Balanced cooling channels and standardized cycle time.', 4, '2026-08-12', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(44, 26, 'Preventive', 'Implement preventive control for CAPA-2026-0026: Added mold temperature trend monitoring.', 2, '2026-08-19', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(45, 27, 'Corrective', 'Implement corrective action for CAPA-2026-0027: Revised dispatch checklist.', 2, '2026-08-04', '2026-08-03', 'Completed', 'Evidence record CAPA-0027-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(46, 27, 'Preventive', 'Implement preventive control for CAPA-2026-0027: Added monthly logistics-document audit.', 2, '2026-08-11', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(47, 28, 'Corrective', 'Implement corrective action for CAPA-2026-0028: Qualified alternate seal material and updated specification.', 4, '2026-08-22', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(48, 28, 'Preventive', 'Implement preventive control for CAPA-2026-0028: Added environmental compatibility review to engineering change process.', 2, '2026-08-29', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(49, 29, 'Corrective', 'Implement corrective action for CAPA-2026-0029: Standardized printer calibration settings.', 3, '2026-08-14', '2026-08-13', 'Completed', 'Evidence record CAPA-0029-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(50, 29, 'Preventive', 'Implement preventive control for CAPA-2026-0029: Added print-quality check at start of each shift.', 2, '2026-08-21', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(51, 30, 'Corrective', 'Implement corrective action for CAPA-2026-0030: Revised maintenance interval using equipment usage data.', 4, '2026-08-29', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(52, 30, 'Preventive', 'Implement preventive control for CAPA-2026-0030: Added condition-based maintenance monitoring.', 2, '2026-09-05', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(53, 31, 'Corrective', 'Implement corrective action for CAPA-2026-0031: Revised sampling plan based on supplier risk.', 2, '2026-09-02', NULL, 'In Progress', NULL, 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(54, 31, 'Preventive', 'Implement preventive control for CAPA-2026-0031: Added automatic review of sampling plans after supplier score changes.', 2, '2026-09-09', NULL, 'Planned', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(55, 32, 'Corrective', 'Implement corrective action for CAPA-2026-0032: Standardized inspection lighting and visual acceptance samples.', 3, '2026-08-20', '2026-08-19', 'Completed', 'Evidence record CAPA-0032-CA01', 'Additional sample corrective action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(56, 32, 'Preventive', 'Implement preventive control for CAPA-2026-0032: Added monthly lighting verification and visual-standard review.', 2, '2026-08-27', NULL, 'Completed', NULL, 'Additional sample preventive action.', 1, '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `capa_approvals`
--

CREATE TABLE `capa_approvals` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED NOT NULL,
  `step_code` varchar(40) NOT NULL,
  `step_name` varchar(120) NOT NULL,
  `sequence_no` int(11) NOT NULL DEFAULT 1,
  `assigned_role` varchar(60) NOT NULL DEFAULT 'Quality Manager',
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'Pending',
  `due_date` date DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `decision_by` int(10) UNSIGNED DEFAULT NULL,
  `decision_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `capa_approvals`
--

INSERT INTO `capa_approvals` (`id`, `capa_id`, `step_code`, `step_name`, `sequence_no`, `assigned_role`, `assigned_to`, `status`, `due_date`, `comments`, `decision_by`, `decision_at`, `created_at`, `updated_at`) VALUES
(1, 9, 'QUALITY_REVIEW', 'Quality Review', 1, 'Quality Manager', NULL, 'Pending', '2026-08-20', NULL, NULL, NULL, '2026-08-16 21:03:15', '2026-08-16 21:03:15'),
(2, 10, 'QUALITY_REVIEW', 'Quality Review', 1, 'Quality Manager', NULL, 'Approved', '2026-08-07', 'Problem definition, containment and root cause are adequate for action.', 2, '2026-08-06 14:00:00', '2026-08-16 21:03:15', '2026-08-16 21:03:15'),
(3, 10, 'EFFECTIVENESS_REVIEW', 'Effectiveness Review', 2, 'Quality Manager', NULL, 'Approved', '2026-08-17', 'Effectiveness evidence demonstrates sustained compliance.', 2, '2026-08-16 09:00:00', '2026-08-16 21:03:15', '2026-08-16 21:03:15'),
(4, 10, 'FINAL_APPROVAL', 'Final Quality Approval', 3, 'Quality Manager', NULL, 'Pending', '2026-08-20', NULL, NULL, NULL, '2026-08-16 21:03:15', '2026-08-16 21:03:15');

-- --------------------------------------------------------

--
-- Table structure for table `capa_approval_history`
--

CREATE TABLE `capa_approval_history` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `approval_id` int(10) UNSIGNED NOT NULL,
  `action` varchar(30) NOT NULL,
  `comments` text DEFAULT NULL,
  `acted_by` int(10) UNSIGNED NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `acted_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `capa_approval_history`
--

INSERT INTO `capa_approval_history` (`id`, `approval_id`, `action`, `comments`, `acted_by`, `ip_address`, `user_agent`, `acted_at`) VALUES
(1, 2, 'Approved', 'Problem definition, containment and root cause are adequate for action.', 2, '127.0.0.1', 'Sample data', '2026-08-06 14:00:00'),
(2, 3, 'Approved', 'Effectiveness evidence demonstrates sustained compliance.', 2, '127.0.0.1', 'Sample data', '2026-08-16 09:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `capa_workflow_events`
--

CREATE TABLE `capa_workflow_events` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED NOT NULL,
  `event_type` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `reference_type` varchar(40) DEFAULT NULL,
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `capa_workflow_events`
--

INSERT INTO `capa_workflow_events` (`id`, `capa_id`, `event_type`, `title`, `description`, `reference_type`, `reference_id`, `created_by`, `created_at`) VALUES
(1, 1, 'CAPA_CREATED', 'CAPA record is active', 'Demo CAPA is ready for the integrated quality workflow.', 'CAPA', 1, 1, '2026-08-16 21:00:47'),
(2, 1, 'WORKFLOW_ENABLED', 'Integrated workflow enabled', 'Fishbone, Pareto, actions, Markov and closure tracking are now connected to this CAPA.', 'CAPA', 1, 1, '2026-08-16 21:01:50'),
(3, 9, 'SUBMITTED_FOR_REVIEW', 'CAPA submitted for quality review', 'Sample CAPA is waiting for independent Quality Manager approval.', 'APPROVAL', 1, 1, '2026-08-16 21:03:15'),
(4, 10, 'QUALITY_REVIEW_APPROVED', 'Quality review approved', 'Sample quality review approval recorded.', 'APPROVAL', 2, 2, '2026-08-16 21:03:15'),
(5, 10, 'EFFECTIVENESS_APPROVED', 'Effectiveness review approved', 'Sample effectiveness approval recorded; final quality approval is pending.', 'APPROVAL', 3, 2, '2026-08-16 21:03:15'),
(6, 1, 'ACTION_COMPLETED', 'Corrective/action item completed', 'Quarantine all cartons produced during the affected production window.', 'ACTION', 1, 1, '2026-08-16 21:18:15'),
(7, 32, 'MARKOV_CREATED', 'Markov lifecycle forecast created', 'A probabilistic lifecycle model was generated for the CAPA.', 'MARKOV', 3, 1, '2026-08-16 21:54:10');

-- --------------------------------------------------------

--
-- Table structure for table `fishbone_analyses`
--

CREATE TABLE `fishbone_analyses` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `problem` text NOT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `fishbone_analyses`
--

INSERT INTO `fishbone_analyses` (`id`, `capa_id`, `title`, `problem`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 6, 'Fishbone Analysis - Packaging Seal Strength', 'Seal strength below specification observed during final inspection.', 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 13, 'Fishbone Analysis - Motor Vibration', 'Excessive vibration detected during final motor testing', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(3, 17, 'Fishbone Analysis - Weld Penetration', 'Weld penetration below the specified requirement', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(4, 20, 'Fishbone Analysis - Foreign Particle Contamination', 'Foreign particles detected in assembled product', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(5, 26, 'Fishbone Analysis - Molded Housing Cracks', 'Cracks observed in molded housing after production', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `fishbone_categories`
--

CREATE TABLE `fishbone_categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `analysis_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `fishbone_categories`
--

INSERT INTO `fishbone_categories` (`id`, `analysis_id`, `name`, `sort_order`) VALUES
(1, 1, 'Man', 1),
(2, 1, 'Machine', 2),
(3, 1, 'Method', 3),
(4, 1, 'Material', 4),
(5, 1, 'Measurement', 5),
(6, 1, 'Environment', 6),
(7, 2, 'Man', 1),
(8, 2, 'Machine', 2),
(9, 2, 'Method', 3),
(10, 2, 'Material', 4),
(11, 2, 'Measurement', 5),
(12, 2, 'Environment', 6),
(13, 3, 'Man', 1),
(14, 3, 'Machine', 2),
(15, 3, 'Method', 3),
(16, 3, 'Material', 4),
(17, 3, 'Measurement', 5),
(18, 3, 'Environment', 6),
(19, 4, 'Man', 1),
(20, 4, 'Machine', 2),
(21, 4, 'Method', 3),
(22, 4, 'Material', 4),
(23, 4, 'Measurement', 5),
(24, 4, 'Environment', 6),
(25, 5, 'Man', 1),
(26, 5, 'Machine', 2),
(27, 5, 'Method', 3),
(28, 5, 'Material', 4),
(29, 5, 'Measurement', 5),
(30, 5, 'Environment', 6);

-- --------------------------------------------------------

--
-- Table structure for table `fishbone_causes`
--

CREATE TABLE `fishbone_causes` (
  `id` int(10) UNSIGNED NOT NULL,
  `analysis_id` int(10) UNSIGNED NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `parent_cause_id` int(10) UNSIGNED DEFAULT NULL,
  `cause_text` varchar(500) NOT NULL,
  `evidence` text DEFAULT NULL,
  `is_root_cause` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `fishbone_causes`
--

INSERT INTO `fishbone_causes` (`id`, `analysis_id`, `category_id`, `parent_cause_id`, `cause_text`, `evidence`, `is_root_cause`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, NULL, 'Operator did not identify early seal degradation', 'Interview showed seal-strength checking was performed only at the defined sampling interval.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 1, 1, NULL, 'Shift handover did not highlight temperature drift', 'Handover sheet contained no abnormality note.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(3, 1, 2, NULL, 'Worn thermocouple', 'Temperature readings differed from a reference thermometer by approximately 7 C.', 1, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(4, 1, 2, NULL, 'Sealing jaw contamination', 'Residue was visible on the jaw surface after line stoppage.', 1, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(5, 1, 3, NULL, 'Cleaning interval not defined by production hours', 'Work instruction specified cleaning but did not define frequency.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(6, 1, 3, NULL, 'Temperature setting changed without documented verification', 'Parameter sheet did not require independent verification after adjustment.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(7, 1, 4, NULL, 'Film surface contamination', 'Incoming material inspection found intermittent dust particles on two rolls.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(8, 1, 4, NULL, 'Film thickness variation', 'Supplier certificate showed thickness variation within specification but close to the upper tolerance.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(9, 1, 5, NULL, 'Seal strength test interval too long', 'Sampling interval was 60 minutes and did not detect short-term drift quickly.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(10, 1, 5, NULL, 'Temperature sensor verification overdue', 'Calibration record showed verification was due within the same week.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(11, 1, 6, NULL, 'High humidity near packing line', 'Humidity was recorded at 78 percent during the affected shift.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(12, 1, 6, NULL, 'Airflow from nearby door affected cooling', 'Door remained open during material movement for approximately 35 minutes.', 0, 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(13, 2, 7, NULL, 'Assembly balancing check was not consistently documented', 'Additional sample evidence for CAPA-2026-0013.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(14, 2, 8, NULL, 'Balancing machine calibration drift', 'Additional sample evidence for CAPA-2026-0013.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(15, 2, 9, NULL, 'Rotor balancing tolerance was outdated', 'Additional sample evidence for CAPA-2026-0013.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(16, 2, 10, NULL, 'Rotor material density variation', 'Additional sample evidence for CAPA-2026-0013.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(17, 2, 11, NULL, 'Vibration sensor verification interval was too long', 'Additional sample evidence for CAPA-2026-0013.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(18, 2, 12, NULL, 'Test bench mounting was not isolated from floor vibration', 'Additional sample evidence for CAPA-2026-0013.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(19, 3, 13, NULL, 'Operator adjusted current without documented escalation', 'Additional sample evidence for CAPA-2026-0017.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(20, 3, 14, NULL, 'Welding controller current drift', 'Additional sample evidence for CAPA-2026-0017.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(21, 3, 15, NULL, 'Parameter window was not locked', 'Additional sample evidence for CAPA-2026-0017.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(22, 3, 16, NULL, 'Material surface condition varied', 'Additional sample evidence for CAPA-2026-0017.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(23, 3, 17, NULL, 'Current verification was performed only weekly', 'Additional sample evidence for CAPA-2026-0017.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(24, 3, 18, NULL, 'Ambient temperature affected preheat consistency', 'Additional sample evidence for CAPA-2026-0017.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(25, 4, 19, NULL, 'Cleaning checklist was not consistently completed', 'Additional sample evidence for CAPA-2026-0020.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(26, 4, 20, NULL, 'Compressed-air line introduced particles', 'Additional sample evidence for CAPA-2026-0020.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(27, 4, 21, NULL, 'Cleaning acceptance criteria were visual only', 'Additional sample evidence for CAPA-2026-0020.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(28, 4, 22, NULL, 'Protective packaging generated fibers', 'Additional sample evidence for CAPA-2026-0020.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(29, 4, 23, NULL, 'Particle count was not routinely measured', 'Additional sample evidence for CAPA-2026-0020.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(30, 4, 24, NULL, 'Open-door airflow introduced dust', 'Additional sample evidence for CAPA-2026-0020.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(31, 5, 25, NULL, 'Setup verification was incomplete after mold change', 'Additional sample evidence for CAPA-2026-0026.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(32, 5, 26, NULL, 'Cooling channels were uneven', 'Additional sample evidence for CAPA-2026-0026.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(33, 5, 27, NULL, 'Cycle-time limits were not enforced', 'Additional sample evidence for CAPA-2026-0026.', 1, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(34, 5, 28, NULL, 'Material moisture was above preferred process range', 'Additional sample evidence for CAPA-2026-0026.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(35, 5, 29, NULL, 'Mold temperature sampling was infrequent', 'Additional sample evidence for CAPA-2026-0026.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(36, 5, 30, NULL, 'Cooling-water temperature fluctuated during shift', 'Additional sample evidence for CAPA-2026-0026.', 0, 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `markov_models`
--

CREATE TABLE `markov_models` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `markov_models`
--

INSERT INTO `markov_models` (`id`, `capa_id`, `name`, `description`, `created_by`, `created_at`, `updated_at`) VALUES
(1, NULL, 'CAPA Lifecycle Markov Model', 'Illustrative transition model showing the probability of a CAPA moving between lifecycle states at each review period.', 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, NULL, 'CAPA Accelerated Closure Model', 'Alternative illustrative transition model for management scenario analysis with a higher probability of progressing to verification.', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(3, 32, 'CAPA Lifecycle - CAPA-2026-0032', 'Integrated lifecycle forecast for CAPA-2026-0032. The model estimates movement from problem identification through investigation, corrective action, verification and closure.', 1, '2026-08-16 21:54:10', '2026-08-16 21:54:10');

-- --------------------------------------------------------

--
-- Table structure for table `markov_states`
--

CREATE TABLE `markov_states` (
  `id` int(10) UNSIGNED NOT NULL,
  `model_id` int(10) UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `markov_states`
--

INSERT INTO `markov_states` (`id`, `model_id`, `code`, `name`, `sort_order`, `created_at`) VALUES
(1, 1, 'OPEN', 'Open', 1, '2026-08-16 15:32:21'),
(2, 1, 'INV', 'Investigation', 2, '2026-08-16 15:32:21'),
(3, 1, 'ACT', 'Corrective Action', 3, '2026-08-16 15:32:21'),
(4, 1, 'VER', 'Verification', 4, '2026-08-16 15:32:21'),
(5, 1, 'CLO', 'Closed', 5, '2026-08-16 15:32:21'),
(6, 2, 'OPEN', 'Open', 1, '2026-08-16 15:58:06'),
(7, 2, 'INV', 'Investigation', 2, '2026-08-16 15:58:06'),
(8, 2, 'ACT', 'Corrective Action', 3, '2026-08-16 15:58:06'),
(9, 2, 'VER', 'Verification', 4, '2026-08-16 15:58:06'),
(10, 2, 'CLO', 'Closed', 5, '2026-08-16 15:58:06'),
(11, 3, 'OPEN', 'Open', 1, '2026-08-16 21:54:10'),
(12, 3, 'INV', 'Investigation', 2, '2026-08-16 21:54:10'),
(13, 3, 'ACT', 'Corrective Action', 3, '2026-08-16 21:54:10'),
(14, 3, 'VER', 'Verification', 4, '2026-08-16 21:54:10'),
(15, 3, 'CLO', 'Closed', 5, '2026-08-16 21:54:10');

-- --------------------------------------------------------

--
-- Table structure for table `markov_transitions`
--

CREATE TABLE `markov_transitions` (
  `id` int(10) UNSIGNED NOT NULL,
  `model_id` int(10) UNSIGNED NOT NULL,
  `from_state_id` int(10) UNSIGNED NOT NULL,
  `to_state_id` int(10) UNSIGNED NOT NULL,
  `probability` decimal(18,12) NOT NULL DEFAULT 0.000000000000,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `markov_transitions`
--

INSERT INTO `markov_transitions` (`id`, `model_id`, `from_state_id`, `to_state_id`, `probability`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 0.200000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 1, 1, 2, 0.800000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(3, 1, 2, 1, 0.100000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(4, 1, 2, 2, 0.200000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(5, 1, 2, 3, 0.700000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(6, 1, 3, 2, 0.100000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(7, 1, 3, 3, 0.150000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(8, 1, 3, 4, 0.750000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(9, 1, 4, 3, 0.100000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(10, 1, 4, 4, 0.100000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(11, 1, 4, 5, 0.800000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(12, 1, 5, 5, 1.000000000000, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(13, 2, 6, 6, 0.100000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(14, 2, 6, 7, 0.900000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(15, 2, 7, 6, 0.050000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(16, 2, 7, 7, 0.100000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(17, 2, 7, 8, 0.850000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(18, 2, 8, 7, 0.050000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(19, 2, 8, 8, 0.100000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(20, 2, 8, 9, 0.850000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(21, 2, 9, 8, 0.050000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(22, 2, 9, 9, 0.050000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(23, 2, 9, 10, 0.900000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(24, 2, 10, 10, 1.000000000000, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(25, 3, 11, 11, 0.200000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(26, 3, 11, 12, 0.800000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(27, 3, 12, 11, 0.100000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(28, 3, 12, 12, 0.200000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(29, 3, 12, 13, 0.700000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(30, 3, 13, 12, 0.100000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(31, 3, 13, 13, 0.150000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(32, 3, 13, 14, 0.750000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(33, 3, 14, 13, 0.100000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(34, 3, 14, 14, 0.100000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(35, 3, 14, 15, 0.800000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10'),
(36, 3, 15, 15, 1.000000000000, '2026-08-16 21:54:10', '2026-08-16 21:54:10');

-- --------------------------------------------------------

--
-- Table structure for table `pareto_analyses`
--

CREATE TABLE `pareto_analyses` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `metric` varchar(120) NOT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pareto_analyses`
--

INSERT INTO `pareto_analyses` (`id`, `capa_id`, `title`, `metric`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 6, 'Pareto Analysis - Packaging Defects', 'Number of defects observed during final inspection, August 2026', 2, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 13, 'Pareto Analysis - Motor Vibration Defects', 'Frequency of observed motor vibration causes, April-May 2026', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(3, 26, 'Pareto Analysis - Molded Housing Cracks', 'Frequency of molded-housing crack observations, July-August 2026', 2, '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `pareto_items`
--

CREATE TABLE `pareto_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `analysis_id` int(10) UNSIGNED NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `frequency` decimal(15,4) NOT NULL DEFAULT 0.0000,
  `remarks` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pareto_items`
--

INSERT INTO `pareto_items` (`id`, `analysis_id`, `item_name`, `frequency`, `remarks`, `created_at`, `updated_at`) VALUES
(1, 1, 'Seal contamination', 42.0000, 'Residue or contamination between sealing surfaces', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(2, 1, 'Incorrect sealing temperature', 31.0000, 'Temperature outside validated operating window', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(3, 1, 'Incorrect sealing pressure', 18.0000, 'Pressure below target range', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(4, 1, 'Worn tooling', 12.0000, 'Sealing jaw or tool wear', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(5, 1, 'Material variation', 8.0000, 'Film thickness or surface variation', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(6, 1, 'Operator handling', 5.0000, 'Incorrect handling during setup or changeover', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(7, 1, 'Other', 4.0000, 'Miscellaneous causes', '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(8, 1, 'Adhesive residue', 22.0000, 'Residue left on the sealing surface after handling', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(9, 1, 'Uneven cooling', 16.0000, 'Cooling variation across the packaging tool', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(10, 1, 'Film alignment', 14.0000, 'Film tracking or alignment deviation', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(11, 1, 'Seal overlap', 10.0000, 'Insufficient overlap during sealing', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(12, 1, 'Damaged edge', 7.0000, 'Mechanical damage to package edge', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(13, 2, 'Rotor imbalance', 34.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(14, 2, 'Bearing preload', 21.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(15, 2, 'Mounting alignment', 17.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(16, 2, 'Sensor variation', 11.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(17, 2, 'Loose fastener', 8.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(18, 2, 'Assembly handling', 5.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(19, 2, 'Other', 4.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(20, 3, 'Uneven cooling', 29.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(21, 3, 'Material moisture', 22.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(22, 3, 'Mold temperature drift', 18.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(23, 3, 'Cycle-time variation', 14.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(24, 3, 'Tool wear', 9.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(25, 3, 'Injection pressure', 6.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06'),
(26, 3, 'Handling damage', 3.0000, 'Additional analytical sample category', '2026-08-16 15:58:06', '2026-08-16 15:58:06');

-- --------------------------------------------------------

--
-- Table structure for table `root_cause_analyses`
--

CREATE TABLE `root_cause_analyses` (
  `id` int(10) UNSIGNED NOT NULL,
  `capa_id` int(10) UNSIGNED NOT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'Suggested',
  `engine_version` varchar(30) NOT NULL DEFAULT '1.0-local',
  `generated_at` datetime NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` datetime DEFAULT NULL,
  `reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `accepted_candidate_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `root_cause_analyses`
--

INSERT INTO `root_cause_analyses` (`id`, `capa_id`, `status`, `engine_version`, `generated_at`, `reviewed_at`, `reviewed_by`, `accepted_candidate_id`, `notes`) VALUES
(1, 32, 'Suggested', '1.0-local', '2026-08-16 21:40:37', NULL, NULL, NULL, NULL),
(2, 32, 'Suggested', '1.0-local', '2026-08-16 22:02:13', NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `root_cause_candidates`
--

CREATE TABLE `root_cause_candidates` (
  `id` int(10) UNSIGNED NOT NULL,
  `analysis_id` int(10) UNSIGNED NOT NULL,
  `cause_text` varchar(500) NOT NULL,
  `category` varchar(80) DEFAULT NULL,
  `score` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `confidence` decimal(5,2) NOT NULL DEFAULT 0.00,
  `similarity_score` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `historical_score` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `fishbone_score` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `pareto_score` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `evidence_count` int(11) NOT NULL DEFAULT 0,
  `source_summary` text DEFAULT NULL,
  `recommended` tinyint(1) NOT NULL DEFAULT 0,
  `decision` varchar(20) DEFAULT NULL,
  `review_comment` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `root_cause_candidates`
--

INSERT INTO `root_cause_candidates` (`id`, `analysis_id`, `cause_text`, `category`, `score`, `confidence`, `similarity_score`, `historical_score`, `fishbone_score`, `pareto_score`, `evidence_count`, `source_summary`, `recommended`, `decision`, `review_comment`, `created_at`) VALUES
(1, 1, 'Finished components were placed directly on an unprotected metal surface during transfer.', 'Historical CAPA', 2.7083, 31.50, 0.0417, 0.0417, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0008', 0, NULL, NULL, '2026-08-16 21:40:37'),
(2, 1, 'Cleaning verification was visual only and did not include defined acceptance criteria.', 'Historical CAPA', 2.7083, 31.50, 0.0417, 0.0417, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0020', 0, NULL, NULL, '2026-08-16 21:40:37'),
(3, 1, 'Preventive maintenance interval was based on calendar time rather than usage hours.', 'Historical CAPA', 2.6000, 31.40, 0.0400, 0.0400, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0030', 0, NULL, NULL, '2026-08-16 21:40:37'),
(4, 1, 'Setup sheet did not identify the critical dimensions requiring independent verification.', 'Historical CAPA', 2.6000, 31.40, 0.0400, 0.0400, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0021', 0, NULL, NULL, '2026-08-16 21:40:37'),
(5, 1, 'Tool wear was not being monitored using a defined replacement limit and process capability was not reviewed by shift.', 'Historical CAPA', 2.5000, 31.40, 0.0385, 0.0385, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0005', 0, NULL, NULL, '2026-08-16 21:40:37'),
(6, 2, 'Finished components were placed directly on an unprotected metal surface during transfer.', 'Historical CAPA', 2.7083, 31.50, 0.0417, 0.0417, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0008', 0, NULL, NULL, '2026-08-16 22:02:13'),
(7, 2, 'Cleaning verification was visual only and did not include defined acceptance criteria.', 'Historical CAPA', 2.7083, 31.50, 0.0417, 0.0417, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0020', 0, NULL, NULL, '2026-08-16 22:02:13'),
(8, 2, 'Preventive maintenance interval was based on calendar time rather than usage hours.', 'Historical CAPA', 2.6000, 31.40, 0.0400, 0.0400, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0030', 0, NULL, NULL, '2026-08-16 22:02:13'),
(9, 2, 'Setup sheet did not identify the critical dimensions requiring independent verification.', 'Historical CAPA', 2.6000, 31.40, 0.0400, 0.0400, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0021', 0, NULL, NULL, '2026-08-16 22:02:13'),
(10, 2, 'Tool wear was not being monitored using a defined replacement limit and process capability was not reviewed by shift.', 'Historical CAPA', 2.5000, 31.40, 0.0385, 0.0385, 0.0000, 0.0000, 1, 'CAPA CAPA-2026-0005', 0, NULL, NULL, '2026-08-16 22:02:13');

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `setting_id` int(11) NOT NULL,
  `setting_name` varchar(1000) NOT NULL,
  `setting_value` varchar(1000) DEFAULT 'true',
  `setting_group_name` varchar(100) NOT NULL DEFAULT 'General',
  `order_by` int(11) NOT NULL,
  `setting_description` varchar(1000) NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`setting_id`, `setting_name`, `setting_value`, `setting_group_name`, `order_by`, `setting_description`, `status`) VALUES
(1, 'App_Name', 'CCIICT', 'General', 1, '', 'Inactive'),
(2, 'App_Title', 'Door to Door Digital Survey & Digital Door Number System all India', 'General', 1, '', 'Active'),
(3, 'Email_protocol', 'smtp', 'Email', 2, '', 'Active'),
(4, 'SMTP_mailtype', 'html', 'Email', 2, '', 'Active'),
(5, 'SMTP_hostname', 'sh005.bigrock.com', 'Email', 2, '', 'Active'),
(6, 'SMTP_username', 'admin@cciict.co.in', 'Email', 2, '', 'Active'),
(7, 'SMTP_password', '@SKdas#5674', 'Email', 2, '', 'Active'),
(8, 'SMTP_port', '465', 'Email', 2, '', 'Active'),
(9, 'SMTP_crypto', 'ssl', 'Email', 1, '', 'Active'),
(10, 'Admin_email', 'admin@cciict.co.in', 'Email', 1, '', 'Active'),
(11, 'Admin_name', 'CCIICT', 'Email', 1, '', 'Active'),
(12, 'Language_direction', 'ltr', 'General', 1, '', 'Active'),
(13, 'Enable_google_chart', 'true', 'General', 1, '', 'Active'),
(14, 'Enable_dompdf', 'true', 'General', 1, '', 'Active'),
(15, 'Enable_user_registration', 'false', 'General', 1, '', 'Active'),
(16, 'Verify_user_email', 'true', 'Email', 2, '', 'Active'),
(17, 'secretid', '2198f0011288666d3694ccf4e7d16c29', 'General', 1, '', 'Active'),
(18, 'secretkey', 'f7115915ae4efc1bdab7ae9fc686348848f8cc2e7bf4a9', 'General', 1, '', 'Active'),
(19, 'No_reply_email', 'no_reply@cciict.co.in', 'Email', 1, '', 'Active'),
(20, 'Activation_email_subject', 'Action required to verify your account', 'Template', 2, '', 'Active'),
(21, 'Activation_email_message', 'Hi, \\r\\n Thank you for registering with us. Please click below link to verify your email address.\\r\\n <a href=\'[verilink]\'>[verilink]</a> \\r\\n or \\r\\n Copy below link and visit in browser \\r\\n [verilink] \\r\\n \\r\\n Thanks', 'Template', 2, '', 'Inactive'),
(22, 'Password_change_subject', 'Reset Password', 'Template', 1, '', 'Active'),
(23, 'Password_change_message', 'Hi [name], \\r\\n Your password reset link is: [resetlink] \\r\\n Or <a href=[resetlink]>Click Here</a><br><br>Thanks.<br><br>CCI India Digital Survey', 'Template', 1, '', 'Active'),
(24, 'Tinymce_editor', 'true', 'Editor', 3, '', 'Active'),
(25, 'Master_password', 'techno', 'General', 1, '', 'Active'),
(26, 'Default_account_id', '2', 'General', 1, '', 'Active'),
(27, 'Pimage_width', '300', 'General', 1, 'Width of profile image in pixels', 'Active'),
(28, 'Logout_timer', '120', 'General', 1, 'Time to automatically logout for inactivity (in minutes)', 'Active');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` varchar(40) NOT NULL DEFAULT 'Quality User',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password_hash`, `role`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'System Administrator', 'admin@example.com', '$2y$12$cf3fm1iDFfS8.y8ofDT8ie0nstSiMyT7AbouEA57dO6f1VGlUA5uu', 'Administrator', 1, '2026-08-16 20:28:40', '2026-08-16 20:28:40'),
(2, 'Quality Manager', 'quality.manager@example.com', '$2y$12$cf3fm1iDFfS8.y8ofDT8ie0nstSiMyT7AbouEA57dO6f1VGlUA5uu', 'Quality Manager', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(3, 'Production Manager', 'production.manager@example.com', '$2y$12$cf3fm1iDFfS8.y8ofDT8ie0nstSiMyT7AbouEA57dO6f1VGlUA5uu', 'Production Manager', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21'),
(4, 'Process Engineer', 'process.engineer@example.com', '$2y$12$cf3fm1iDFfS8.y8ofDT8ie0nstSiMyT7AbouEA57dO6f1VGlUA5uu', 'Process Engineer', 1, '2026-08-16 15:32:21', '2026-08-16 15:32:21');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account`
--
ALTER TABLE `account`
  ADD PRIMARY KEY (`aid`);

--
-- Indexes for table `capas`
--
ALTER TABLE `capas`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_capas_no` (`capa_no`),
  ADD KEY `idx_capas_status` (`status`),
  ADD KEY `idx_capas_priority` (`priority`),
  ADD KEY `idx_capas_owner` (`owner_id`),
  ADD KEY `fk_capas_created_by` (`created_by`),
  ADD KEY `fk_capas_submitted_by` (`submitted_by`),
  ADD KEY `fk_capas_final_approved_by` (`final_approved_by`);

--
-- Indexes for table `capa_actions`
--
ALTER TABLE `capa_actions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_actions_capa` (`capa_id`),
  ADD KEY `fk_actions_owner` (`owner_id`),
  ADD KEY `fk_actions_created_by` (`created_by`);

--
-- Indexes for table `capa_approvals`
--
ALTER TABLE `capa_approvals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_capa_approval_step` (`capa_id`,`step_code`),
  ADD KEY `idx_approval_status` (`status`),
  ADD KEY `idx_approval_assigned` (`assigned_to`),
  ADD KEY `idx_approval_role` (`assigned_role`),
  ADD KEY `idx_approval_due` (`due_date`),
  ADD KEY `fk_approval_decision_by` (`decision_by`);

--
-- Indexes for table `capa_approval_history`
--
ALTER TABLE `capa_approval_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_approval_history_approval` (`approval_id`),
  ADD KEY `idx_approval_history_actor` (`acted_by`),
  ADD KEY `idx_approval_history_time` (`acted_at`);

--
-- Indexes for table `capa_workflow_events`
--
ALTER TABLE `capa_workflow_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_workflow_capa` (`capa_id`),
  ADD KEY `idx_workflow_created` (`created_at`),
  ADD KEY `fk_workflow_created_by` (`created_by`);

--
-- Indexes for table `fishbone_analyses`
--
ALTER TABLE `fishbone_analyses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_fishbone_capa` (`capa_id`),
  ADD KEY `fk_fishbone_created_by` (`created_by`);

--
-- Indexes for table `fishbone_categories`
--
ALTER TABLE `fishbone_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_fishbone_cat_analysis` (`analysis_id`);

--
-- Indexes for table `fishbone_causes`
--
ALTER TABLE `fishbone_causes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_fishbone_cause_analysis` (`analysis_id`),
  ADD KEY `idx_fishbone_cause_category` (`category_id`),
  ADD KEY `fk_fishbone_parent` (`parent_cause_id`),
  ADD KEY `fk_fishbone_cause_created_by` (`created_by`);

--
-- Indexes for table `markov_models`
--
ALTER TABLE `markov_models`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_markov_capa` (`capa_id`),
  ADD KEY `fk_markov_created_by` (`created_by`);

--
-- Indexes for table `markov_states`
--
ALTER TABLE `markov_states`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_markov_state_code` (`model_id`,`code`),
  ADD KEY `idx_markov_states_model` (`model_id`);

--
-- Indexes for table `markov_transitions`
--
ALTER TABLE `markov_transitions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_markov_transition` (`model_id`,`from_state_id`,`to_state_id`),
  ADD KEY `idx_markov_transitions_model` (`model_id`),
  ADD KEY `fk_markov_transitions_from` (`from_state_id`),
  ADD KEY `fk_markov_transitions_to` (`to_state_id`);

--
-- Indexes for table `pareto_analyses`
--
ALTER TABLE `pareto_analyses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_pareto_capa` (`capa_id`),
  ADD KEY `fk_pareto_created_by` (`created_by`);

--
-- Indexes for table `pareto_items`
--
ALTER TABLE `pareto_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_pareto_items_analysis` (`analysis_id`);

--
-- Indexes for table `root_cause_analyses`
--
ALTER TABLE `root_cause_analyses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_rca_capa` (`capa_id`),
  ADD KEY `idx_rca_status` (`status`),
  ADD KEY `fk_rca_reviewed_by` (`reviewed_by`);

--
-- Indexes for table `root_cause_candidates`
--
ALTER TABLE `root_cause_candidates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_rcc_analysis` (`analysis_id`),
  ADD KEY `idx_rcc_recommended` (`recommended`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_users_email` (`email`),
  ADD KEY `idx_users_active` (`is_active`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `account`
--
ALTER TABLE `account`
  MODIFY `aid` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `capas`
--
ALTER TABLE `capas`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- AUTO_INCREMENT for table `capa_actions`
--
ALTER TABLE `capa_actions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;

--
-- AUTO_INCREMENT for table `capa_approvals`
--
ALTER TABLE `capa_approvals`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `capa_approval_history`
--
ALTER TABLE `capa_approval_history`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `capa_workflow_events`
--
ALTER TABLE `capa_workflow_events`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `fishbone_analyses`
--
ALTER TABLE `fishbone_analyses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `fishbone_categories`
--
ALTER TABLE `fishbone_categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `fishbone_causes`
--
ALTER TABLE `fishbone_causes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `markov_models`
--
ALTER TABLE `markov_models`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `markov_states`
--
ALTER TABLE `markov_states`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `markov_transitions`
--
ALTER TABLE `markov_transitions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `pareto_analyses`
--
ALTER TABLE `pareto_analyses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `pareto_items`
--
ALTER TABLE `pareto_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=27;

--
-- AUTO_INCREMENT for table `root_cause_analyses`
--
ALTER TABLE `root_cause_analyses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `root_cause_candidates`
--
ALTER TABLE `root_cause_candidates`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `capas`
--
ALTER TABLE `capas`
  ADD CONSTRAINT `fk_capas_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_capas_final_approved_by` FOREIGN KEY (`final_approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_capas_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_capas_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `capa_actions`
--
ALTER TABLE `capa_actions`
  ADD CONSTRAINT `fk_actions_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_actions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_actions_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `capa_approvals`
--
ALTER TABLE `capa_approvals`
  ADD CONSTRAINT `fk_approval_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_approval_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_approval_decision_by` FOREIGN KEY (`decision_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `capa_approval_history`
--
ALTER TABLE `capa_approval_history`
  ADD CONSTRAINT `fk_approval_history_actor` FOREIGN KEY (`acted_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_approval_history_approval` FOREIGN KEY (`approval_id`) REFERENCES `capa_approvals` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `capa_workflow_events`
--
ALTER TABLE `capa_workflow_events`
  ADD CONSTRAINT `fk_workflow_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_workflow_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `fishbone_analyses`
--
ALTER TABLE `fishbone_analyses`
  ADD CONSTRAINT `fk_fishbone_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_fishbone_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `fishbone_categories`
--
ALTER TABLE `fishbone_categories`
  ADD CONSTRAINT `fk_fishbone_cat_analysis` FOREIGN KEY (`analysis_id`) REFERENCES `fishbone_analyses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `fishbone_causes`
--
ALTER TABLE `fishbone_causes`
  ADD CONSTRAINT `fk_fishbone_cause_analysis` FOREIGN KEY (`analysis_id`) REFERENCES `fishbone_analyses` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fishbone_cause_category` FOREIGN KEY (`category_id`) REFERENCES `fishbone_categories` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_fishbone_cause_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_fishbone_parent` FOREIGN KEY (`parent_cause_id`) REFERENCES `fishbone_causes` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `markov_models`
--
ALTER TABLE `markov_models`
  ADD CONSTRAINT `fk_markov_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_markov_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `markov_states`
--
ALTER TABLE `markov_states`
  ADD CONSTRAINT `fk_markov_states_model` FOREIGN KEY (`model_id`) REFERENCES `markov_models` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `markov_transitions`
--
ALTER TABLE `markov_transitions`
  ADD CONSTRAINT `fk_markov_transitions_from` FOREIGN KEY (`from_state_id`) REFERENCES `markov_states` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_markov_transitions_model` FOREIGN KEY (`model_id`) REFERENCES `markov_models` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_markov_transitions_to` FOREIGN KEY (`to_state_id`) REFERENCES `markov_states` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `pareto_analyses`
--
ALTER TABLE `pareto_analyses`
  ADD CONSTRAINT `fk_pareto_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_pareto_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `pareto_items`
--
ALTER TABLE `pareto_items`
  ADD CONSTRAINT `fk_pareto_items_analysis` FOREIGN KEY (`analysis_id`) REFERENCES `pareto_analyses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `root_cause_analyses`
--
ALTER TABLE `root_cause_analyses`
  ADD CONSTRAINT `fk_rca_capa` FOREIGN KEY (`capa_id`) REFERENCES `capas` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_rca_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `root_cause_candidates`
--
ALTER TABLE `root_cause_candidates`
  ADD CONSTRAINT `fk_rcc_analysis` FOREIGN KEY (`analysis_id`) REFERENCES `root_cause_analyses` (`id`) ON DELETE CASCADE;
COMMIT;

