-- Audit Log Table for Bill Delete Actions
-- This table logs all bill delete actions with details

CREATE TABLE IF NOT EXISTS `tbl_audit_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_action_type` varchar(100) NOT NULL COMMENT 'Type of action (e.g., BILL_DELETE, PAYMENT_DELETE)',
  `log_action_details` text NOT NULL COMMENT 'Detailed description of the action',
  `log_user_id` varchar(100) NOT NULL COMMENT 'Logged user ID',
  `log_user_name` varchar(255) NOT NULL COMMENT 'Logged user name',
  `log_user_type` varchar(10) NOT NULL COMMENT 'User type (1=Admin, 2=Operator, etc)',
  `log_ip_address` varchar(50) NOT NULL COMMENT 'IP address of the user',
  `log_date` date NOT NULL COMMENT 'Date of action',
  `log_time` time NOT NULL COMMENT 'Time of action',
  `log_page_name` varchar(255) NOT NULL COMMENT 'Page name where action occurred',
  `log_page_url` text NOT NULL COMMENT 'Full URL of the page',
  `log_bill_id` varchar(100) DEFAULT NULL COMMENT 'Bill ID affected',
  `log_customer_id` varchar(100) DEFAULT NULL COMMENT 'Customer ID affected',
  `log_month` varchar(20) DEFAULT NULL COMMENT 'Month (for bulk deletes)',
  `log_year` varchar(20) DEFAULT NULL COMMENT 'Year (for bulk deletes)',
  `log_area` varchar(100) DEFAULT NULL COMMENT 'Area ID (for area-wise deletes)',
  `log_amount` varchar(50) DEFAULT NULL COMMENT 'Amount involved',
  `log_company` varchar(255) DEFAULT NULL COMMENT 'Company name',
  `log_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  PRIMARY KEY (`log_id`),
  KEY `log_user_id` (`log_user_id`),
  KEY `log_date` (`log_date`),
  KEY `log_action_type` (`log_action_type`),
  KEY `log_bill_id` (`log_bill_id`),
  KEY `log_customer_id` (`log_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Audit log for tracking bill delete actions';
