-- HRM Employee Table Setup
CREATE TABLE IF NOT EXISTS `tbl_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL COMMENT 'Link to branch',
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `mother_name` varchar(255) DEFAULT NULL,
  `education` varchar(255) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `experience` varchar(100) DEFAULT NULL COMMENT 'e.g., 2 Years, 6 Months',
  `phone` varchar(20) NOT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pincode` varchar(10) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `user_id` varchar(100) DEFAULT NULL COMMENT 'Login username',
  `password` varchar(255) DEFAULT NULL COMMENT 'Login password (plain text)',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_branch_id` (`branch_id`),
  KEY `idx_status` (`status`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Master Table';

-- Insert sample data
INSERT INTO `tbl_employees`
(`branch_id`, `name`, `age`, `email`, `father_name`, `mother_name`, `education`, `salary`, `experience`, `phone`, `address`, `city`, `pincode`, `state`, `country`, `status`, `user_id`, `password`, `created_by`)
VALUES
(1, 'Rajesh Kumar', 28, 'rajesh.kumar@example.com', 'Mohan Kumar', 'Sita Devi', 'B.Tech Computer Science', 45000.00, '3 Years', '9876543210', '123, MG Road', 'Bangalore', '560001', 'Karnataka', 'India', 'active', 'rajesh.kumar', 'password123', 1),
(2, 'Priya Sharma', 25, 'priya.sharma@example.com', 'Amit Sharma', 'Sunita Sharma', 'MBA Marketing', 38000.00, '2 Years', '9876543211', '456, Sector 15', 'Noida', '201301', 'Uttar Pradesh', 'India', 'active', 'priya.sharma', 'password123', 1);
