-- Farm Management System Backup
-- Created: 2026-02-26 02:55:42



CREATE TABLE `app_settings` (
  `setting_key` varchar(50) NOT NULL,
  `setting_value` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('base_currency', 'YER', '2026-02-25 23:06:23');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('base_currency_symbol', 'RY', '2026-02-25 23:07:27');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('farm_address', '', '2026-02-26 02:56:38');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('farm_email', '', '2026-02-26 02:56:38');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('farm_logo', '', '2026-02-26 02:56:38');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('farm_name', 'مزرعتي', '2026-02-26 02:56:38');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('farm_phone', '', '2026-02-26 02:56:38');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('milk_price_per_liter', '375', '2026-02-25 23:10:05');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('sar_exchange_rate', '140', '2026-02-25 23:06:24');
INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES ('usd_exchange_rate', '530', '2026-02-25 23:10:05');


CREATE TABLE `breeding_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mother_id` int(11) NOT NULL,
  `father_id` int(11) NOT NULL,
  `mating_date` date NOT NULL,
  `expected_due_date` date DEFAULT NULL,
  `actual_birth_date` date DEFAULT NULL,
  `offspring_count` int(11) DEFAULT 0,
  `status` enum('Pregnant','Delivered','Failed','Aborted') DEFAULT 'Pregnant',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `mother_id` (`mother_id`),
  KEY `father_id` (`father_id`),
  CONSTRAINT `breeding_records_ibfk_1` FOREIGN KEY (`mother_id`) REFERENCES `sheep` (`id`) ON DELETE CASCADE,
  CONSTRAINT `breeding_records_ibfk_2` FOREIGN KEY (`father_id`) REFERENCES `sheep` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `breeds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO breeds (id, name, description) VALUES ('1', 'Najdi', NULL);
INSERT INTO breeds (id, name, description) VALUES ('2', 'Nuaimi', NULL);
INSERT INTO breeds (id, name, description) VALUES ('3', 'Harri', NULL);
INSERT INTO breeds (id, name, description) VALUES ('4', 'Awassi', NULL);
INSERT INTO breeds (id, name, description) VALUES ('5', 'Merino', NULL);
INSERT INTO breeds (id, name, description) VALUES ('6', 'Dorper', NULL);


CREATE TABLE `cattle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` varchar(50) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `breed_id` int(11) DEFAULT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `dob` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `father_id` int(11) DEFAULT NULL,
  `mother_id` int(11) DEFAULT NULL,
  `weight` decimal(10,2) DEFAULT NULL,
  `status` enum('Active','Sold','Deceased','Culled','Sick') DEFAULT 'Active',
  `is_dry` tinyint(1) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_id` (`tag_id`),
  KEY `breed_id` (`breed_id`),
  KEY `father_id` (`father_id`),
  KEY `mother_id` (`mother_id`),
  CONSTRAINT `cattle_ibfk_1` FOREIGN KEY (`breed_id`) REFERENCES `cattle_breeds` (`id`) ON DELETE SET NULL,
  CONSTRAINT `cattle_ibfk_2` FOREIGN KEY (`father_id`) REFERENCES `cattle` (`id`) ON DELETE SET NULL,
  CONSTRAINT `cattle_ibfk_3` FOREIGN KEY (`mother_id`) REFERENCES `cattle` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle (id, tag_id, name, breed_id, gender, dob, purchase_date, father_id, mother_id, weight, status, is_dry, notes, created_at, image) VALUES ('1', 'C-001', 'حلوب', '1', 'Female', '2023-12-31', '2026-02-25', NULL, NULL, NULL, 'Active', '0', '', '2026-02-25 22:35:09', 'cattle_1772068960_699fa06094e48.png');
INSERT INTO cattle (id, tag_id, name, breed_id, gender, dob, purchase_date, father_id, mother_id, weight, status, is_dry, notes, created_at, image) VALUES ('2', 'c102', 'رمانه', '1', 'Female', '2024-06-25', '2026-02-25', NULL, NULL, '700.00', 'Active', '0', '', '2026-02-25 22:59:59', 'cattle_1772068941_699fa04d4a599.jpg');


CREATE TABLE `cattle_breeding_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mother_id` int(11) NOT NULL,
  `father_id` int(11) NOT NULL,
  `mating_date` date NOT NULL,
  `expected_due_date` date DEFAULT NULL,
  `actual_birth_date` date DEFAULT NULL,
  `offspring_count` int(11) DEFAULT 0,
  `status` enum('Pregnant','Delivered','Failed','Aborted') DEFAULT 'Pregnant',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `mother_id` (`mother_id`),
  KEY `father_id` (`father_id`),
  CONSTRAINT `cattle_breeding_records_ibfk_1` FOREIGN KEY (`mother_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cattle_breeding_records_ibfk_2` FOREIGN KEY (`father_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `cattle_breeds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle_breeds (id, name, description) VALUES ('1', 'Holstein', NULL);
INSERT INTO cattle_breeds (id, name, description) VALUES ('2', 'Jersey', NULL);
INSERT INTO cattle_breeds (id, name, description) VALUES ('3', 'Angus', NULL);
INSERT INTO cattle_breeds (id, name, description) VALUES ('4', 'Hereford', NULL);
INSERT INTO cattle_breeds (id, name, description) VALUES ('5', 'Brahman', NULL);
INSERT INTO cattle_breeds (id, name, description) VALUES ('6', 'Guernsey', NULL);


CREATE TABLE `cattle_dry_periods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cattle_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `expected_end_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `reason` varchar(255) DEFAULT 'Pregnancy',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cattle_id` (`cattle_id`),
  CONSTRAINT `cattle_dry_periods_ibfk_1` FOREIGN KEY (`cattle_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle_dry_periods (id, cattle_id, start_date, expected_end_date, actual_end_date, reason, notes, created_at) VALUES ('1', '1', '2026-02-25', '2026-03-25', '2026-02-25', '', NULL, '2026-02-25 22:47:01');
INSERT INTO cattle_dry_periods (id, cattle_id, start_date, expected_end_date, actual_end_date, reason, notes, created_at) VALUES ('2', '1', '2026-02-25', '2026-04-25', '2026-02-26', 'Pregnancy', NULL, '2026-02-25 22:52:15');


CREATE TABLE `cattle_feeding_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cattle_id` int(11) DEFAULT NULL,
  `inventory_item_id` int(11) NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `unit_cost` decimal(10,2) NOT NULL,
  `total_cost` decimal(10,2) GENERATED ALWAYS AS (`quantity` * `unit_cost`) STORED,
  `feeding_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cattle_id` (`cattle_id`),
  KEY `inventory_item_id` (`inventory_item_id`),
  CONSTRAINT `cattle_feeding_logs_ibfk_1` FOREIGN KEY (`cattle_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cattle_feeding_logs_ibfk_2` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('1', '2', '2', '20.00', '2.00', '40.00', '2026-02-25', '', '2026-02-25 23:20:23');
INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('2', '2', '3', '10.00', '150.00', '1500.00', '2026-02-25', '', '2026-02-25 23:31:09');


CREATE TABLE `cattle_health_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cattle_id` int(11) NOT NULL,
  `record_type` enum('Vaccination','Treatment','Checkup') NOT NULL,
  `diagnosis` varchar(255) DEFAULT NULL,
  `treatment` varchar(255) DEFAULT NULL,
  `dosage` varchar(100) DEFAULT NULL,
  `withdrawal_period` int(11) DEFAULT 0,
  `date_administered` date NOT NULL,
  `next_due_date` date DEFAULT NULL,
  `treatment_cost` decimal(10,2) DEFAULT 0.00,
  `vet_name` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cattle_id` (`cattle_id`),
  CONSTRAINT `cattle_health_records_ibfk_1` FOREIGN KEY (`cattle_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle_health_records (id, cattle_id, record_type, diagnosis, treatment, dosage, withdrawal_period, date_administered, next_due_date, treatment_cost, vet_name, notes, created_at) VALUES ('1', '1', 'Treatment', 'Sample Check', '', '', '0', '2026-02-26', NULL, '500.00', '', '', '2026-02-26 00:08:09');
INSERT INTO cattle_health_records (id, cattle_id, record_type, diagnosis, treatment, dosage, withdrawal_period, date_administered, next_due_date, treatment_cost, vet_name, notes, created_at) VALUES ('2', '2', 'Checkup', 'فحص دوري', 'cvb', '', '5', '2026-02-25', '2026-03-12', '1500.00', 'gfhfgh', 'قصر مناعه', '2026-02-26 00:10:39');


CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('Supplier','Customer') NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `balance` decimal(15,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO contacts (id, name, type, phone, balance, notes, created_at) VALUES ('1', 'الريف للأعلاف', 'Supplier', '', '-5000.00', '', '2026-02-26 02:39:52');
INSERT INTO contacts (id, name, type, phone, balance, notes, created_at) VALUES ('2', 'شركه اليمن للاعلاف', 'Supplier', '7845654', '0.00', '', '2026-02-26 02:41:26');
INSERT INTO contacts (id, name, type, phone, balance, notes, created_at) VALUES ('3', 'مصنع الالبان', 'Customer', '787878788', '0.00', 'يشتري الالبان', '2026-02-26 03:07:30');


CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `role_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `join_date` date DEFAULT NULL,
  `status` enum('Active','Inactive','OnLeave') DEFAULT 'Active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_employee_role` (`role_id`),
  CONSTRAINT `fk_employee_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO employees (id, name, role_id, role, phone, salary, join_date, status, notes, created_at) VALUES ('1', 'HAMDI YAHIA ALI ALQABILI', '1', 'مدير المزرعه', '777111264', '500000.00', '2026-02-25', 'Active', 'مهام الموضف \r\nالاشراف علي المزرعه من كل النواحي ورفع تقارير مفصله عن المخازن والاعلاف والابقار والانتاج واتخذا القرارات في الاوقات الحرجه ومراجعه كل شي', '2026-02-26 01:45:22');


CREATE TABLE `finance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('Income','Expense') NOT NULL,
  `category` varchar(100) NOT NULL,
  `treasury_id` int(11) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `related_id` int(11) DEFAULT NULL,
  `related_type` varchar(50) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` text DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `related_sheep_id` int(11) DEFAULT NULL,
  `related_cattle_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `related_sheep_id` (`related_sheep_id`),
  KEY `fk_finance_cattle` (`related_cattle_id`),
  KEY `fk_fin_treas_v2_unique` (`treasury_id`),
  KEY `fk_fin_cont_v2_unique` (`contact_id`),
  CONSTRAINT `finance_ibfk_1` FOREIGN KEY (`related_sheep_id`) REFERENCES `sheep` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_fin_cont_v2_unique` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_fin_treas_v2_unique` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_finance_cattle` FOREIGN KEY (`related_cattle_id`) REFERENCES `cattle` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_finance_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_finance_treasury` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('1', 'Expense', 'أعلاف', '1', NULL, NULL, NULL, '1500.00', '', '2026-02-09', '1', NULL, '2026-02-09 20:06:53', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('2', 'Expense', 'شراء أغنام', '1', NULL, NULL, NULL, '40.00', 'شراء نعجة رقم: 00002', '2026-02-10', '2', NULL, '2026-02-10 08:44:32', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('3', 'Expense', 'شراء أغنام', '1', NULL, NULL, NULL, '40.00', 'شراء نعجة رقم: 00003', '2026-02-10', '3', NULL, '2026-02-10 08:48:06', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('4', 'Expense', 'Feed Purchase', '1', NULL, NULL, NULL, '1000.00', 'Feed Purchase', '2026-02-25', NULL, NULL, '2026-02-25 23:01:12', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('5', 'Expense', 'Salaries', '1', NULL, '5', 'Payroll', '500000.00', 'صرف راتب الموظف: HAMDI YAHIA ALI ALQABILI - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 02:31:28', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('6', 'Expense', 'Salaries', '1', NULL, '6', 'Payroll', '50000.00', 'صرف راتب الموظف: HAMDI YAHIA ALI ALQABILI - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 02:32:03', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('7', 'Expense', 'أعلاف', '1', '1', NULL, NULL, '5000.00', 'شراء أعلاف للماشية', '2026-02-26', NULL, NULL, '2026-02-26 02:40:47', NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by) VALUES ('8', 'Income', 'Sales', '1', NULL, NULL, NULL, '3755000.00', 'بيع حليب: 10 لتر لـ Test Buyer', '2026-02-26', NULL, NULL, '2026-02-26 03:22:56', '1');


CREATE TABLE `health_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sheep_id` int(11) NOT NULL,
  `record_type` enum('Vaccination','Treatment','Checkup') NOT NULL,
  `diagnosis` varchar(255) DEFAULT NULL,
  `treatment` varchar(255) DEFAULT NULL,
  `dosage` varchar(100) DEFAULT NULL,
  `withdrawal_period` int(11) DEFAULT 0,
  `date_administered` date NOT NULL,
  `next_due_date` date DEFAULT NULL,
  `vet_name` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `sheep_id` (`sheep_id`),
  CONSTRAINT `health_records_ibfk_1` FOREIGN KEY (`sheep_id`) REFERENCES `sheep` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `inventory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(100) NOT NULL,
  `category` enum('Feed','Medicine','Equipment','Other') NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `unit` varchar(20) NOT NULL,
  `unit_price` decimal(10,2) DEFAULT 0.00,
  `reorder_level` decimal(10,2) DEFAULT 10.00,
  `expiry_date` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('1', 'Concentrate Feed', 'Feed', '1', '500.00', 'kg', '0.00', '10.00', NULL, '2026-02-26 01:41:21');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('2', 'عجور اخضر', 'Feed', '1', '430.00', 'حبه', '250.00', '50.00', NULL, '2026-02-26 01:47:52');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('3', 'عجور يابس', 'Feed', '1', '390.00', 'حبه', '150.00', '10.00', NULL, '2026-02-26 01:41:21');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('4', 'تلقيح رقم 1', 'Medicine', '1', '100.00', 'حبه', '2500.00', '10.00', '2027-10-12', '2026-02-26 01:41:21');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('5', 'عجور اخضر', 'Feed', '2', '50.00', 'حبه', '250.00', '50.00', NULL, '2026-02-26 01:47:52');


CREATE TABLE `inventory_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `transaction_type` enum('In','Out') NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `transaction_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `inventory_transactions_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('1', '2', 'Out', '20.00', 'تغذية أبقار (رقم 2)', '2026-02-25 23:20:23');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('2', '3', 'Out', '10.00', 'تغذية أبقار (رقم 2)', '2026-02-25 23:31:09');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('3', '3', 'Out', '100.00', '', '2026-02-26 00:17:41');


CREATE TABLE `milk_production` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cattle_id` int(11) NOT NULL,
  `production_date` date NOT NULL,
  `session` enum('AM','PM') NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `status` enum('Acceptable','Damaged','ExternalSale','InternalUse') DEFAULT 'Acceptable',
  `waste_reason` varchar(255) DEFAULT NULL,
  `fat_percentage` decimal(4,2) DEFAULT NULL,
  `quality_notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cattle_id` (`cattle_id`),
  CONSTRAINT `milk_production_ibfk_1` FOREIGN KEY (`cattle_id`) REFERENCES `cattle` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('1', '1', '2026-02-25', 'AM', '12.50', 'Acceptable', NULL, NULL, '', '2026-02-25 22:37:08');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('2', '2', '2026-02-25', 'PM', '20.00', 'Acceptable', '', NULL, '', '2026-02-25 23:05:39');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('3', '2', '2026-02-25', 'PM', '20.00', 'Acceptable', '', NULL, '', '2026-02-25 23:06:55');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('4', '2', '2026-02-26', 'AM', '22.00', 'Acceptable', '', '8.00', '', '2026-02-26 02:37:43');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('5', '1', '2026-02-26', 'AM', '25.00', 'Acceptable', '', NULL, '', '2026-02-26 02:58:04');


CREATE TABLE `milk_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_date` date NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `customer_name_manual` varchar(255) DEFAULT NULL,
  `quantity_liters` decimal(10,2) NOT NULL,
  `price_per_liter` decimal(10,2) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `treasury_id` int(11) NOT NULL,
  `notes` text DEFAULT NULL,
  `finance_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `treasury_id` (`treasury_id`),
  CONSTRAINT `milk_sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `milk_sales_ibfk_2` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO milk_sales (id, sale_date, customer_id, customer_name_manual, quantity_liters, price_per_liter, total_amount, treasury_id, notes, finance_id, created_by, created_at) VALUES ('11', '2026-02-26', NULL, 'Test Buyer', '10.00', '375500.00', '3755000.00', '1', '', '8', '1', '2026-02-26 03:22:56');


CREATE TABLE `payroll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `month_year` varchar(20) DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO payroll (id, employee_id, amount, payment_date, month_year, payment_method, notes, created_at) VALUES ('5', '1', '500000.00', '2026-02-26', '02-2026', 'Cash', '', '2026-02-26 02:31:28');
INSERT INTO payroll (id, employee_id, amount, payment_date, month_year, payment_method, notes, created_at) VALUES ('6', '1', '50000.00', '2026-02-26', '02-2026', 'Cash', '', '2026-02-26 02:32:03');


CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `group_name` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('1', 'manage_cattle', 'إدارة الأبقار', 'Cattle', '2026-02-26 02:56:38');
INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('2', 'manage_milk', 'إدارة الألبان', 'Dairy', '2026-02-26 02:56:38');
INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('3', 'manage_finance', 'إدارة المالية', 'Finance', '2026-02-26 02:56:38');
INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('4', 'manage_inventory', 'إدارة المخازن', 'Inventory', '2026-02-26 02:56:38');
INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('5', 'manage_hr', 'إدارة الموظفين', 'HR', '2026-02-26 02:56:38');
INSERT INTO permissions (id, slug, display_name, group_name, created_at) VALUES ('6', 'manage_settings', 'إدارة الإعدادات', 'System', '2026-02-26 02:56:38');


CREATE TABLE `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '1');


CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (id, name, description, created_at) VALUES ('1', 'مدير', 'مدير النظام - صلاحيات كاملة', '2026-02-26 02:56:38');
INSERT INTO roles (id, name, description, created_at) VALUES ('2', 'محاسب', 'محاسب - إدارة المالية والتقارير', '2026-02-26 02:56:38');
INSERT INTO roles (id, name, description, created_at) VALUES ('3', 'طبيب بيطري', 'طبيب بيطري - إدارة السجلات الصحية', '2026-02-26 02:56:38');
INSERT INTO roles (id, name, description, created_at) VALUES ('4', 'مشرف', 'مشرف - مراقبة الإنتاج والموظفين', '2026-02-26 02:56:38');
INSERT INTO roles (id, name, description, created_at) VALUES ('5', 'عامل', 'عامل - تسجيل البيانات اليومية', '2026-02-26 02:56:38');


CREATE TABLE `sheep` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` varchar(50) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `breed_id` int(11) DEFAULT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `dob` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `purchase_price` decimal(10,2) DEFAULT 0.00,
  `father_id` int(11) DEFAULT NULL,
  `mother_id` int(11) DEFAULT NULL,
  `weight` decimal(10,2) DEFAULT NULL,
  `status` enum('Active','Sold','Deceased','Culled','Sick') DEFAULT 'Active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_id` (`tag_id`),
  KEY `breed_id` (`breed_id`),
  KEY `father_id` (`father_id`),
  KEY `mother_id` (`mother_id`),
  CONSTRAINT `sheep_ibfk_1` FOREIGN KEY (`breed_id`) REFERENCES `breeds` (`id`) ON DELETE SET NULL,
  CONSTRAINT `sheep_ibfk_2` FOREIGN KEY (`father_id`) REFERENCES `sheep` (`id`) ON DELETE SET NULL,
  CONSTRAINT `sheep_ibfk_3` FOREIGN KEY (`mother_id`) REFERENCES `sheep` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO sheep (id, tag_id, name, breed_id, gender, dob, purchase_date, purchase_price, father_id, mother_id, weight, status, notes, created_at, image) VALUES ('1', '00001', 'ساره', '4', 'Female', '2026-09-02', '2026-09-02', '300.00', NULL, NULL, '16.00', 'Active', '', '2026-02-09 20:05:14', NULL);
INSERT INTO sheep (id, tag_id, name, breed_id, gender, dob, purchase_date, purchase_price, father_id, mother_id, weight, status, notes, created_at, image) VALUES ('2', '00002', 'حسينه', '4', 'Female', '2025-04-08', '2026-02-10', '40.00', NULL, NULL, '22.00', 'Active', '', '2026-02-10 08:44:32', NULL);
INSERT INTO sheep (id, tag_id, name, breed_id, gender, dob, purchase_date, purchase_price, father_id, mother_id, weight, status, notes, created_at, image) VALUES ('3', '00003', 'منيرة', '4', 'Female', '2025-06-26', '2026-02-10', '40.00', NULL, NULL, '18.00', 'Active', '', '2026-02-10 08:48:06', NULL);


CREATE TABLE `stock_transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `from_warehouse_id` int(11) NOT NULL,
  `to_warehouse_id` int(11) NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `notes` text DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `from_warehouse_id` (`from_warehouse_id`),
  KEY `to_warehouse_id` (`to_warehouse_id`),
  CONSTRAINT `stock_transfers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_transfers_ibfk_2` FOREIGN KEY (`from_warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_transfers_ibfk_3` FOREIGN KEY (`to_warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO stock_transfers (id, item_id, from_warehouse_id, to_warehouse_id, quantity, status, notes, requested_at, processed_at) VALUES ('1', '2', '1', '2', '50.00', 'Approved', '', '2026-02-26 01:47:47', '2026-02-26 01:47:52');


CREATE TABLE `treasuries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('Cash','Bank') DEFAULT 'Cash',
  `balance` decimal(15,2) DEFAULT 0.00,
  `is_default` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO treasuries (id, name, type, balance, is_default, created_at) VALUES ('1', 'الخزينة الرئيسية', 'Cash', '-557580.00', '1', '2026-02-26 02:34:46');
INSERT INTO treasuries (id, name, type, balance, is_default, created_at) VALUES ('2', 'حساب بنكي', 'Bank', '100000.00', '0', '2026-02-26 02:38:58');
INSERT INTO treasuries (id, name, type, balance, is_default, created_at) VALUES ('3', 'جزنه مبيعات الالبان', 'Cash', '0.00', '0', '2026-02-26 03:25:14');


CREATE TABLE `treasury_transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_treasury_id` int(11) NOT NULL,
  `to_treasury_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `transfer_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `from_treasury_id` (`from_treasury_id`),
  KEY `to_treasury_id` (`to_treasury_id`),
  CONSTRAINT `treasury_transfers_ibfk_1` FOREIGN KEY (`from_treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `treasury_transfers_ibfk_2` FOREIGN KEY (`to_treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `warehouses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `capacity_info` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO warehouses (id, name, location, capacity_info, created_at) VALUES ('1', 'مخزن صنعاء', 'صنعاء الاصبحي', 'هنجر رقم 0001', '2026-02-26 01:41:21');
INSERT INTO warehouses (id, name, location, capacity_info, created_at) VALUES ('2', 'مخزن رقم 2 الحديده', 'مزره االضحي', 'خلف مخطه الفقيه', '2026-02-26 01:42:48');
