-- Farm Management System Backup
-- Created: 2026-02-26 21:04:02



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 22:53:12');
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', 'assets/images/logo_1772074022.png', '2026-02-26 05:47:02');
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', '777111264', '2026-02-26 22:53:12');
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 `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO audit_logs (id, user_id, action, details, created_at) VALUES ('1', '6', 'مزامنة الأرصدة', 'تمت إعادة احتساب أرصدة الخزنات بناءً على سجلات المالية.', '2026-02-26 20:10:50');
INSERT INTO audit_logs (id, user_id, action, details, created_at) VALUES ('2', '6', 'تحويل مالي', 'تحويل مبلغ 45000 من جزنه مبيعات الالبان إلى الخزينة الرئيسية', '2026-02-26 20:11:31');
INSERT INTO audit_logs (id, user_id, action, details, created_at) VALUES ('3', '6', 'مزامنة الأرصدة', 'تمت إعادة احتساب أرصدة الخزنات بناءً على سجلات المالية.', '2026-02-26 20:11:33');
INSERT INTO audit_logs (id, user_id, action, details, created_at) VALUES ('4', '6', 'Accounting Error', 'There is already an active transaction', '2026-02-26 22:51:19');


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=4 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');
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 ('3', 'c-103', 'مرشد', '1', 'Male', '2024-01-08', '2026-02-26', NULL, NULL, '800.00', 'Active', '0', '', '2026-02-26 05:13:19', 'cattle_1772071999_699fac3f4fdf5.webp');


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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cattle_breeding_records (id, mother_id, father_id, mating_date, expected_due_date, actual_birth_date, offspring_count, status, notes, created_at) VALUES ('1', '2', '3', '2026-02-26', '2026-10-21', NULL, '0', 'Pregnant', '', '2026-02-26 05:14:15');


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=4 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');
INSERT INTO cattle_dry_periods (id, cattle_id, start_date, expected_end_date, actual_end_date, reason, notes, created_at) VALUES ('3', '1', '2026-02-26', '2026-02-27', '2026-02-26', 'Health', NULL, '2026-02-26 22:55:58');


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=7 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');
INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('3', NULL, '2', '25.00', '250.00', '6250.00', '2026-02-26', '', '2026-02-26 05:35:03');
INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('4', '3', '2', '30.00', '250.00', '7500.00', '2026-02-26', '', '2026-02-26 05:35:24');
INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('5', NULL, '5', '20.00', '250.00', '5000.00', '2026-02-26', '', '2026-02-26 22:20:28');
INSERT INTO cattle_feeding_logs (id, cattle_id, inventory_item_id, quantity, unit_cost, total_cost, feeding_date, notes, created_at) VALUES ('6', NULL, '3', '20.00', '150.00', '3000.00', '2026-02-26', '', '2026-02-26 22:20:39');


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=4 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');
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 ('3', '3', 'Treatment', 'التهاب معوي', 'تلقيح رقم 1', '', '0', '2026-02-26', '2026-03-12', '2500.00', '', '', '2026-02-26 15:47:20');


CREATE TABLE `chart_of_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('Asset','Liability','Equity','Revenue','Expense') NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `chart_of_accounts_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('1', '1000', 'الأصول (Assets)', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('2', '1100', 'النقد وما في حكمه', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('3', '1101', 'الخزينة الرئيسية', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('4', '1102', 'البنك', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('5', '1200', 'المدينون (العملاء)', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('6', '1300', 'المخزون', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('7', '1400', 'الأصول الثابتة (الماشية والآلات)', 'Asset', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('8', '2000', 'الخصوم (Liabilities)', 'Liability', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('9', '2100', 'الدائنون (الموردون)', 'Liability', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('10', '2200', 'التزامات قصيرة الأجل', 'Liability', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('11', '3000', 'حقوق الملكية (Equity)', 'Equity', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('12', '3100', 'رأس المال', 'Equity', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('13', '3200', 'الأرباح المحتجزة', 'Equity', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('14', '4000', 'الإيرادات (Revenue)', 'Revenue', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('15', '4100', 'مبيعات الحليب', 'Revenue', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('16', '4200', 'مبيعات المواشي', 'Revenue', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('17', '4300', 'إيرادات متنوعة', 'Revenue', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('18', '5000', 'المصاريف (Expenses)', 'Expense', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('19', '5100', 'مصاريف الأعلاف', 'Expense', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('20', '5200', 'مصاريف الرعاية الصحية والبيطرية', 'Expense', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('21', '5300', 'الأجور والرواتب', 'Expense', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('22', '5400', 'المصاريف الإدارية والعمومية', 'Expense', NULL, NULL, '1', '2026-02-26 22:41:24');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('23', '1401', 'Ù…Ø¬Ù…Ø¹ Ø§Ù„Ø¥Ù‡Ù„Ø§Ùƒ (Ø§Ù„Ø£ØµÙˆÙ„ Ø§Ù„Ø«Ø§Ø¨ØªØ©)', 'Asset', NULL, NULL, '1', '2026-02-26 22:44:41');
INSERT INTO chart_of_accounts (id, code, name, type, parent_id, description, is_active, created_at) VALUES ('24', '5500', 'Ù…ØµØ§Ø±ÙŠÙ Ø§Ù„Ø¥Ù‡Ù„Ø§Ùƒ ÙˆØ§Ù„Ø§Ø³ØªÙ‡Ù„Ø§Ùƒ', 'Expense', NULL, NULL, '1', '2026-02-26 22:44:41');


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

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


CREATE TABLE `cost_centers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cost_centers (id, name, code, description, created_at) VALUES ('1', 'Ù‚Ø³Ù… Ø§Ù„Ø£Ø¨Ù‚Ø§Ø± (Cattle Dept)', 'CC-CAT', NULL, '2026-02-26 22:45:42');
INSERT INTO cost_centers (id, name, code, description, created_at) VALUES ('2', 'Ù‚Ø³Ù… Ø§Ù„Ø£ØºÙ†Ø§Ù… (Sheep Dept)', 'CC-SHP', NULL, '2026-02-26 22:45:42');
INSERT INTO cost_centers (id, name, code, description, created_at) VALUES ('3', 'Ø§Ù„Ø¥Ø¯Ø§Ø±Ø© Ø§Ù„Ø¹Ø§Ù…Ø© (General Admin)', 'CC-GEN', NULL, '2026-02-26 22:45:42');


CREATE TABLE `depreciation_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset_id` int(11) NOT NULL,
  `journal_entry_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `period_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `asset_id` (`asset_id`),
  KEY `journal_entry_id` (`journal_entry_id`),
  CONSTRAINT `depreciation_logs_ibfk_1` FOREIGN KEY (`asset_id`) REFERENCES `fixed_assets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `depreciation_logs_ibfk_2` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO depreciation_logs (id, asset_id, journal_entry_id, amount, period_date, created_at) VALUES ('1', '2', '2', '52083.33', '2026-02-26', '2026-02-26 22:53:22');


CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_code` varchar(20) DEFAULT NULL,
  `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(),
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `emp_code` (`emp_code`),
  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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


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,
  `voucher_no` varchar(50) DEFAULT NULL,
  `voucher_type` enum('Receipt','Payment','Other') DEFAULT 'Other',
  `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,
  `inventory_item_id` int(11) DEFAULT NULL,
  `cost_center_id` int(11) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `related_sheep_id` (`related_sheep_id`),
  KEY `fk_finance_cattle` (`related_cattle_id`),
  KEY `treasury_id` (`treasury_id`),
  KEY `contact_id` (`contact_id`),
  KEY `fk_finance_cost_center` (`cost_center_id`),
  CONSTRAINT `finance_ibfk_1` FOREIGN KEY (`related_sheep_id`) REFERENCES `sheep` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_10` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_11` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_12` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_13` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_2` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_4` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_5` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_6` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_7` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_8` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `finance_ibfk_9` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`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_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_finance_treasury` FOREIGN KEY (`treasury_id`) REFERENCES `treasuries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('1', 'Expense', 'أعلاف', '1', NULL, NULL, 'Payment', NULL, NULL, '1500.00', '', '2026-02-09', '1', NULL, '2026-02-09 20:06:53', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('2', 'Expense', 'شراء أغنام', '1', NULL, NULL, 'Payment', NULL, NULL, '40.00', 'شراء نعجة رقم: 00002', '2026-02-10', '2', NULL, '2026-02-10 08:44:32', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('3', 'Expense', 'شراء أغنام', '1', NULL, NULL, 'Payment', NULL, NULL, '40.00', 'شراء نعجة رقم: 00003', '2026-02-10', '3', NULL, '2026-02-10 08:48:06', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('4', 'Expense', 'Feed Purchase', '1', NULL, NULL, 'Payment', NULL, NULL, '1000.00', 'Feed Purchase', '2026-02-25', NULL, NULL, '2026-02-25 23:01:12', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('5', 'Expense', 'Salaries', '1', NULL, NULL, 'Payment', '5', 'Payroll', '500000.00', 'صرف راتب الموظف: HAMDI YAHIA ALI ALQABILI - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 02:31:28', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('6', 'Expense', 'Salaries', '1', NULL, NULL, 'Payment', '6', 'Payroll', '50000.00', 'صرف راتب الموظف: HAMDI YAHIA ALI ALQABILI - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 02:32:03', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('7', 'Expense', 'أعلاف', '1', '1', NULL, 'Payment', NULL, NULL, '5000.00', 'شراء أعلاف للماشية', '2026-02-26', NULL, NULL, '2026-02-26 02:40:47', NULL, NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('8', 'Income', 'Sales', '1', NULL, NULL, 'Receipt', NULL, NULL, '3755000.00', 'بيع حليب: 10 لتر لـ Test Buyer', '2026-02-26', NULL, NULL, '2026-02-26 03:22:56', '1', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('9', 'Income', 'Sales', '3', NULL, NULL, 'Receipt', NULL, NULL, '40875.00', 'بيع حليب: 109 لتر ', '2026-02-26', NULL, NULL, '2026-02-26 15:03:27', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('10', 'Income', 'Sales', '3', '3', 'RV-2026-0001', 'Receipt', NULL, NULL, '7312.50', 'بيع حليب: 19.5 لتر ', '2026-02-26', NULL, NULL, '2026-02-26 20:06:21', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('11', 'Expense', 'تحويل مالي', '3', NULL, 'PV-2026-0001/TR', 'Payment', NULL, NULL, '45000.00', 'تحويل إلى: الخزينة الرئيسية - ', '2026-02-26', NULL, NULL, '2026-02-26 20:11:31', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('12', 'Income', 'تحويل مالي', '1', NULL, 'PV-2026-0001/TR', 'Receipt', NULL, NULL, '45000.00', 'تحويل من: جزنه مبيعات الالبان - ', '2026-02-26', NULL, NULL, '2026-02-26 20:11:31', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('13', 'Expense', 'Salaries', '1', NULL, 'PV-2026-0002', 'Payment', '7', 'Payroll', '123123.00', 'صرف راتب الموظف: هاشم - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 21:59:26', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('14', 'Expense', 'Salaries', '1', NULL, 'PV-2026-0003', 'Payment', '8', 'Payroll', '123123.00', 'صرف راتب الموظف: هاشم - شهر 02-2026', '2026-02-26', NULL, NULL, '2026-02-26 22:02:40', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('15', 'Income', 'Sales', '3', NULL, 'RV-2026-0002', 'Receipt', NULL, NULL, '5625.00', 'بيع حليب: 15 لتر ', '2026-02-26', NULL, NULL, '2026-02-26 22:06:21', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('16', 'Income', 'Sales', '3', NULL, 'RV-2026-0003', 'Receipt', NULL, NULL, '3750.00', 'بيع حليب: 10 لتر ', '2026-02-26', NULL, NULL, '2026-02-26 22:08:45', '6', NULL, NULL, NULL);
INSERT INTO finance (id, type, category, treasury_id, contact_id, voucher_no, voucher_type, related_id, related_type, amount, description, transaction_date, related_sheep_id, related_cattle_id, created_at, created_by, inventory_item_id, cost_center_id, attachment) VALUES ('17', 'Income', 'Sales', '3', '3', 'RV-2026-0004', 'Receipt', NULL, NULL, '15000.00', 'بيع حليب: 40 لتر ', '2026-02-26', NULL, NULL, '2026-02-26 22:29:18', '6', NULL, NULL, NULL);


CREATE TABLE `fixed_assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset_name` varchar(255) NOT NULL,
  `category` enum('Machinery','Buildings','Livestock','Vehicles','Equipment','Other') NOT NULL,
  `purchase_date` date NOT NULL,
  `purchase_value` decimal(15,2) NOT NULL,
  `salvage_value` decimal(15,2) DEFAULT 0.00,
  `useful_life_years` int(11) NOT NULL,
  `depreciation_method` enum('Straight-Line','Declining-Balance') DEFAULT 'Straight-Line',
  `account_id` int(11) DEFAULT NULL,
  `depreciation_account_id` int(11) DEFAULT NULL,
  `accumulated_depreciation_account_id` int(11) DEFAULT NULL,
  `status` enum('Active','Disposed','Sold') DEFAULT 'Active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `fixed_assets_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO fixed_assets (id, asset_name, category, purchase_date, purchase_value, salvage_value, useful_life_years, depreciation_method, account_id, depreciation_account_id, accumulated_depreciation_account_id, status, notes, created_at) VALUES ('2', 'حراثه 6 بسطون', 'Machinery', '2026-02-26', '23250000.00', '17000000.00', '10', 'Straight-Line', NULL, NULL, NULL, 'Active', '', '2026-02-26 22:51:53');


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,
  `treatment_cost` decimal(10,2) DEFAULT 0.00,
  `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', '375.00', 'حبه', '250.00', '50.00', NULL, '2026-02-26 05:35:24');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('3', 'عجور يابس', 'Feed', '1', '370.00', 'حبه', '150.00', '10.00', NULL, '2026-02-26 22:20:39');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('4', 'تلقيح رقم 1', 'Medicine', '1', '99.00', 'حبه', '2500.00', '10.00', '2027-10-12', '2026-02-26 15:47:20');
INSERT INTO inventory (id, item_name, category, warehouse_id, quantity, unit, unit_price, reorder_level, expiry_date, updated_at) VALUES ('5', 'عجور اخضر', 'Feed', '2', '30.00', 'حبه', '250.00', '50.00', NULL, '2026-02-26 22:20:28');


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=9 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');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('4', '2', 'Out', '25.00', 'تغذية أبقار', '2026-02-26 05:35:03');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('5', '2', 'Out', '30.00', 'تغذية أبقار (رقم 3)', '2026-02-26 05:35:24');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('6', '4', 'Out', '1.00', 'استخدام طبي للبقرة (ID: 3)', '2026-02-26 15:47:20');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('7', '5', 'Out', '20.00', 'تغذية أبقار', '2026-02-26 22:20:28');
INSERT INTO inventory_transactions (id, item_id, transaction_type, quantity, notes, transaction_date) VALUES ('8', '3', 'Out', '20.00', 'تغذية أبقار', '2026-02-26 22:20:39');


CREATE TABLE `journal_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `reference_no` varchar(50) DEFAULT NULL,
  `source_module` varchar(50) DEFAULT NULL,
  `source_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `journal_entries_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO journal_entries (id, entry_date, description, reference_no, source_module, source_id, created_at, created_by) VALUES ('1', '2026-02-26', 'شراء أصل ثابت: حراثه 6 بسطون', NULL, 'Assets', '2', '2026-02-26 22:51:53', '6');
INSERT INTO journal_entries (id, entry_date, description, reference_no, source_module, source_id, created_at, created_by) VALUES ('2', '2026-02-26', 'إهلاك أصل: حراثه 6 بسطون - شهر 2026-02', NULL, 'Depreciation', '2', '2026-02-26 22:53:22', '6');


CREATE TABLE `journal_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `journal_entry_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `cost_center_id` int(11) DEFAULT NULL,
  `debit` decimal(15,2) DEFAULT 0.00,
  `credit` decimal(15,2) DEFAULT 0.00,
  `memo` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journal_entry_id` (`journal_entry_id`),
  KEY `account_id` (`account_id`),
  KEY `fk_journal_cost_center` (`cost_center_id`),
  CONSTRAINT `fk_journal_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `journal_items_ibfk_1` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_items_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO journal_items (id, journal_entry_id, account_id, cost_center_id, debit, credit, memo) VALUES ('1', '1', '7', NULL, '23250000.00', '0.00', 'شراء أصل ثابت: حراثه 6 بسطون');
INSERT INTO journal_items (id, journal_entry_id, account_id, cost_center_id, debit, credit, memo) VALUES ('2', '1', '3', NULL, '0.00', '23250000.00', 'شراء أصل ثابت: حراثه 6 بسطون');
INSERT INTO journal_items (id, journal_entry_id, account_id, cost_center_id, debit, credit, memo) VALUES ('3', '2', '24', NULL, '52083.33', '0.00', 'إهلاك أصل: حراثه 6 بسطون - شهر 2026-02');
INSERT INTO journal_items (id, journal_entry_id, account_id, cost_center_id, debit, credit, memo) VALUES ('4', '2', '23', NULL, '0.00', '52083.33', 'إهلاك أصل: حراثه 6 بسطون - شهر 2026-02');


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,
  `is_cold_storage` tinyint(1) DEFAULT 0,
  `wasted_quantity` decimal(10,2) DEFAULT 0.00,
  `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=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('1', '1', '2026-02-25', 'AM', '12.50', '0', '0.00', 'Acceptable', NULL, NULL, '', '2026-02-25 22:37:08');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('2', '2', '2026-02-25', 'PM', '20.00', '0', '0.00', 'Acceptable', '', NULL, '', '2026-02-25 23:05:39');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('3', '2', '2026-02-25', 'PM', '20.00', '0', '0.00', 'Acceptable', '', NULL, '', '2026-02-25 23:06:55');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('4', '2', '2026-02-26', 'AM', '22.00', '0', '0.00', 'Acceptable', '', '8.00', '', '2026-02-26 02:37:43');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('5', '1', '2026-02-26', 'AM', '25.00', '0', '0.00', 'Acceptable', '', NULL, '', '2026-02-26 02:58:04');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('6', '1', '2026-02-26', 'AM', '20.00', '0', '10.00', 'Acceptable', '', NULL, '', '2026-02-26 14:57:34');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('7', '1', '2026-02-26', 'PM', '20.00', '1', '11.00', 'Acceptable', '', NULL, '', '2026-02-26 15:26:48');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('8', '2', '2026-02-26', 'PM', '20.00', '1', '0.00', 'Acceptable', '', NULL, '', '2026-02-26 15:29:19');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('9', '1', '2026-02-26', 'PM', '35.00', '1', '0.00', 'Acceptable', '', NULL, '', '2026-02-26 22:05:51');
INSERT INTO milk_production (id, cattle_id, production_date, session, quantity, is_cold_storage, wasted_quantity, status, waste_reason, fat_percentage, quality_notes, created_at) VALUES ('10', '2', '2026-02-26', 'PM', '33.00', '1', '0.00', 'Acceptable', '', NULL, '', '2026-02-26 22:06:01');


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=20 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');
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 ('12', '2026-02-26', '3', '', '109.00', '375.00', '40875.00', '3', '', '9', '6', '2026-02-26 15:03:27');
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 ('16', '2026-02-26', '3', '', '19.50', '375.00', '7312.50', '3', '', '10', '6', '2026-02-26 20:06:21');
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 ('17', '2026-02-26', NULL, '', '15.00', '375.00', '5625.00', '3', '', '15', '6', '2026-02-26 22:06:21');
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 ('18', '2026-02-26', NULL, '', '10.00', '375.00', '3750.00', '3', '', '16', '6', '2026-02-26 22:08:45');
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 ('19', '2026-02-26', '3', '', '40.00', '375.00', '15000.00', '3', '', '17', '6', '2026-02-26 22:29:18');


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=9 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');
INSERT INTO payroll (id, employee_id, amount, payment_date, month_year, payment_method, notes, created_at) VALUES ('7', '2', '123123.00', '2026-02-26', '02-2026', 'Cash', '', '2026-02-26 21:59:26');
INSERT INTO payroll (id, employee_id, amount, payment_date, month_year, payment_method, notes, created_at) VALUES ('8', '2', '123123.00', '2026-02-26', '02-2026', 'Cash', '', '2026-02-26 22:02:40');


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

INSERT INTO permissions (id, code, display_name, group_name) VALUES ('1', 'sheep_view', 'عرض سجل الأغنام', 'الأغنام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('2', 'sheep_add', 'إضافة أغنام جديدة', 'الأغنام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('3', 'sheep_edit', 'تعديل بيانات الأغنام', 'الأغنام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('4', 'sheep_delete', 'حذف من سجل الأغنام', 'الأغنام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('5', 'cattle_view', 'عرض سجل الأبقار', 'الأبقار');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('6', 'cattle_add', 'إضافة أبقار جديدة', 'الأبقار');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('7', 'cattle_edit', 'تعديل بيانات الأبقار', 'الأبقار');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('8', 'cattle_delete', 'حذف من سجل الأبقار', 'الأبقار');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('9', 'health_view', 'عرض السجلات الصحية', 'الصحة والبيطرة');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('10', 'health_add', 'إضافة سجلات صحية', 'الصحة والبيطرة');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('11', 'health_edit', 'تعديل السجلات الصحية', 'الصحة والبيطرة');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('12', 'health_delete', 'حذف سجلات صحية', 'الصحة والبيطرة');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('13', 'breeding_view', 'عرض سجلات التكاثر', 'التكاثر والولادات');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('14', 'breeding_add', 'تسجيل ولادات/تلقيح', 'التكاثر والولادات');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('15', 'breeding_edit', 'تعديل سجلات التكاثر', 'التكاثر والولادات');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('16', 'breeding_delete', 'حذف سجلات التكاثر', 'التكاثر والولادات');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('17', 'dairy_view', 'عرض سجلات الألبان', 'إدارة الألبان');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('18', 'dairy_add', 'تسجيل إنتاج/مبيعات الحليب', 'إدارة الألبان');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('19', 'dairy_edit', 'تعديل قيود الألبان', 'إدارة الألبان');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('20', 'dairy_delete', 'حذف قيود الألبان', 'إدارة الألبان');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('21', 'nutrition_view', 'عرض سجلات التغذية', 'التغذية والأعلاف');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('22', 'nutrition_add', 'تسجيل وجبات/تغذية', 'التغذية والأعلاف');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('23', 'nutrition_edit', 'تعديل سجلات التغذية', 'التغذية والأعلاف');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('24', 'nutrition_delete', 'حذف سجلات التغذية', 'التغذية والأعلاف');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('25', 'finance_view', 'عرض السجل المالي', 'المالية');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('26', 'finance_add', 'إضافة معاملات مالية', 'المالية');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('27', 'finance_edit', 'تعديل معاملات سابقة', 'المالية');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('28', 'finance_delete', 'حذف معاملات مالية', 'المالية');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('29', 'inv_view', 'عرض المخزون', 'المخزن');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('30', 'inv_add', 'إضافة أصناف/مشتريات', 'المخزن');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('31', 'inv_edit', 'تعديل الكميات/الأصناف', 'المخزن');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('32', 'inv_delete', 'حذف أصناف من المخزن', 'المخزن');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('33', 'emp_view', 'عرض سجل الموظفين', 'الموظفين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('34', 'emp_add', 'إضافة موظف جديد', 'الموظفين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('35', 'emp_edit', 'تعديل بيانات الموظفين', 'الموظفين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('36', 'emp_delete', 'حذف موظف', 'الموظفين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('37', 'contacts_view', 'عرض جهات الاتصال', 'العملاء والموردين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('38', 'contacts_add', 'إضافة عميل/مورد', 'العملاء والموردين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('39', 'contacts_edit', 'تعديل بيانات الجهات', 'العملاء والموردين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('40', 'contacts_delete', 'حذف جهة اتصال', 'العملاء والموردين');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('41', 'reports_view', 'عرض تقارير النظام', 'النظام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('42', 'settings_manage', 'إدارة إعدادات النظام', 'النظام');
INSERT INTO permissions (id, code, display_name, group_name) VALUES ('43', 'audit_view', 'عرض سجل العمليات', 'النظام');


CREATE TABLE `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '1');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '2');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '3');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '4');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '5');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '6');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '7');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '8');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '9');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '10');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '11');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '12');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '13');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '14');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '15');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '16');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '17');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '18');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '19');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '20');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '21');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '22');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '23');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '24');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '25');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '26');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '27');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '28');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '29');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '30');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '31');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '32');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '33');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '34');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '35');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '36');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '37');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '38');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '39');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '40');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '41');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '42');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('1', '43');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '13');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '14');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '15');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '21');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '22');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('3', '23');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '1');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '5');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '9');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '13');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '17');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '21');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '25');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '29');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '33');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '37');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '41');
INSERT INTO role_permissions (role_id, permission_id) VALUES ('16', '43');


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=17 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');
INSERT INTO roles (id, name, description, created_at) VALUES ('16', 'موظف', 'صلاحيات محدودة', '2026-02-26 06:28:22');


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 `sheep_feeding_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sheep_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 `sheep_id` (`sheep_id`),
  KEY `inventory_item_id` (`inventory_item_id`),
  CONSTRAINT `sheep_feeding_logs_ibfk_1` FOREIGN KEY (`sheep_id`) REFERENCES `sheep` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sheep_feeding_logs_ibfk_2` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



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(),
  `account_id` int(11) DEFAULT NULL,
  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, account_id) VALUES ('1', 'الخزينة الرئيسية', 'Cash', '2996174.00', '1', '2026-02-26 02:34:46', NULL);
INSERT INTO treasuries (id, name, type, balance, is_default, created_at, account_id) VALUES ('2', 'حساب بنكي', 'Bank', '0.00', '0', '2026-02-26 02:38:58', NULL);
INSERT INTO treasuries (id, name, type, balance, is_default, created_at, account_id) VALUES ('3', 'جزنه مبيعات الالبان', 'Cash', '27562.50', '0', '2026-02-26 03:25:14', NULL);


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,
  `code` varchar(20) DEFAULT NULL,
  `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`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO warehouses (id, code, name, location, capacity_info, created_at) VALUES ('1', 'WR-001', 'مخزن صنعاء', 'صنعاء الاصبحي', 'هنجر رقم 0001', '2026-02-26 01:41:21');
INSERT INTO warehouses (id, code, name, location, capacity_info, created_at) VALUES ('2', 'WR-002', 'مخزن رقم 2 الحديده', 'مزره االضحي', 'خلف مخطه الفقيه', '2026-02-26 01:42:48');
INSERT INTO warehouses (id, code, name, location, capacity_info, created_at) VALUES ('3', 'WR-003', 'مستودع الكدن المركزي', 'المدن بالقرب من الشراع الرايسي', '', '2026-02-26 21:58:48');
