-- ==============================================================================
-- BETTERAGRI MY POULTRY MANAGER (BAT-D-LIVE-115) DATABASE SUBSYSTEM
-- Operational Schema & Seed Dataset (Flocks, Farms, Nutrition, Health, Finance)
-- BetterAgri Tech Ltd — Kampala, Uganda
-- Database: betteragri_bat115 | Engine: InnoDB | Charset: utf8mb4_unicode_ci
-- ==============================================================================
-- NOTE: Authentication, passwords, and central identities are managed by
-- betteragri_auth (/public_root/bat_auth/). No auth credentials exist in this schema.
-- ==============================================================================

CREATE DATABASE IF NOT EXISTS `betteragri_bat115` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `betteragri_bat115`;

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;

-- Clean Drop for Clean Idempotent Imports
DROP TABLE IF EXISTS `breeding_records`;
DROP TABLE IF EXISTS `hatchery_records`;
DROP TABLE IF EXISTS `biosecurity_audits`;
DROP TABLE IF EXISTS `telemetry_logs`;
DROP TABLE IF EXISTS `flock_tasks`;
DROP TABLE IF EXISTS `health_records`;
DROP TABLE IF EXISTS `daily_performance`;
DROP TABLE IF EXISTS `feed_records`;
DROP TABLE IF EXISTS `egg_records`;
DROP TABLE IF EXISTS `feed_inventory`;
DROP TABLE IF EXISTS `inventory_items`;
DROP TABLE IF EXISTS `finance_records`;
DROP TABLE IF EXISTS `flocks`;
DROP TABLE IF EXISTS `farms`;
DROP TABLE IF EXISTS `ecosystem_apps`;
DROP TABLE IF EXISTS `partner_organizations`;
DROP TABLE IF EXISTS `promotions`;
DROP TABLE IF EXISTS `recommendations`;
DROP TABLE IF EXISTS `ad_policies`;
DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `payment_transactions`;
DROP TABLE IF EXISTS `subscription_events`;
DROP TABLE IF EXISTS `subscriptions`;
DROP TABLE IF EXISTS `plan_limits`;
DROP TABLE IF EXISTS `plan_features`;
DROP TABLE IF EXISTS `plans`;
DROP VIEW IF EXISTS `users`;
DROP TABLE IF EXISTS `app_users`;
DROP VIEW IF EXISTS `organizations`;
DROP TABLE IF EXISTS `app_organizations`;
DROP TABLE IF EXISTS `application_iso_codes`;
DROP TABLE IF EXISTS `applications`;

-- ==============================================================================
-- 1. LOCAL ORGANIZATIONS DIRECTORY (BAT-D-LIVE-115)
-- ==============================================================================
CREATE TABLE `app_organizations` (
  `organization_id` VARCHAR(64) PRIMARY KEY,
  `org_code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(200) NOT NULL,
  `country` VARCHAR(100) NOT NULL,
  `industry` VARCHAR(100) NOT NULL,
  `contact_email` VARCHAR(150) NOT NULL,
  `contact_phone` VARCHAR(50) DEFAULT NULL,
  `status` ENUM('ACTIVE', 'SUSPENDED', 'TRIAL', 'EXPIRED') DEFAULT 'ACTIVE',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW `organizations` AS SELECT * FROM `app_organizations`;

-- ==============================================================================
-- 2. APP USER PROFILES (STAFF DIRECTORY — NO PASSWORD REPETITION)
-- ==============================================================================
-- Notice: Authentication is handled by betteragri_auth (JWT / PasswordService).
-- This table only stores application-level profile metadata & assigned farms.
CREATE TABLE `app_users` (
  `id` VARCHAR(64) PRIMARY KEY COMMENT 'Matches central betteragri_auth.users.user_id',
  `organization_id` VARCHAR(64) DEFAULT NULL,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `phone_number` VARCHAR(50) DEFAULT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT 'Worker',
  `primary_app_iso` VARCHAR(50) DEFAULT 'BAT-D-LIVE-115',
  `subscription_status` ENUM('FREE', 'TRIAL', 'ACTIVE', 'PAST_DUE', 'GRACE_PERIOD', 'EXPIRED', 'CANCELLED', 'SUSPENDED', 'PENDING', 'REVOKED') DEFAULT 'ACTIVE',
  `expiry_date` DATE DEFAULT '2027-12-31',
  `last_renewal` DATE DEFAULT '2026-01-01',
  `assigned_farms` TEXT DEFAULT NULL, -- JSON array of farm IDs
  `status` ENUM('Active', 'Suspended', 'Pending') DEFAULT 'Active',
  `department` VARCHAR(100) DEFAULT NULL,
  `biometric_enabled` TINYINT(1) DEFAULT 0,
  `biometric_type` VARCHAR(20) DEFAULT NULL,
  `auth_lock_code` VARCHAR(10) DEFAULT NULL,
  `auth_lock_code_updated_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_app_user_role` (`role`),
  INDEX `idx_app_user_status` (`status`),
  CONSTRAINT `fk_app_user_org` FOREIGN KEY (`organization_id`) REFERENCES `app_organizations`(`organization_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backward compatibility view for legacy BAT115 endpoints querying 'users'
CREATE OR REPLACE VIEW `users` AS SELECT * FROM `app_users`;

-- ==============================================================================
-- 3. SEED ORGANIZATIONS AND APP PROFILES FIRST (BEFORE FARMS)
-- ==============================================================================
INSERT INTO `app_organizations` (`organization_id`, `org_code`, `name`, `country`, `industry`, `contact_email`, `contact_phone`, `status`) VALUES
('org_bat_corp_01', 'ORG-SUNRISE-001', 'Sunrise Agribusiness Group Ltd', 'Uganda', 'Commercial Poultry Production', 'contact@sunrise.com', '+256 701 445 890', 'ACTIVE')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

INSERT INTO `app_users` (`id`, `organization_id`, `username`, `email`, `phone_number`, `full_name`, `role`, `primary_app_iso`, `subscription_status`, `expiry_date`, `last_renewal`, `status`, `department`) VALUES
('usr_bat_001', 'org_bat_corp_01', 'admin@betteragri.com', 'admin@betteragri.com', '+256 772 100 200', 'Dr. BetterAgri Lead Specialist', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2027-12-31', '2026-01-01', 'Active', 'Executive Management'),
('usr_bat_002', 'org_bat_corp_01', 'samuel.kaggwa', 'samuel.kaggwa@sunrise.com', '+256 701 445 890', 'Samuel Kaggwa', 'Farm Manager', 'BAT-D-LIVE-115', 'ACTIVE', '2027-12-31', '2026-01-01', 'Active', 'Operations & Feed Management'),
('usr_bat_003', 'org_bat_corp_01', 'grace.achieng', 'grace.achieng@highland.com', '+254 712 334 556', 'Dr. Grace Achieng', 'Veterinarian', 'BAT-D-LIVE-115', 'ACTIVE', '2027-12-31', '2026-01-01', 'Active', 'Veterinary & Biosecurity'),
('usr_bat_admin_001', 'org_bat_corp_01', 'admin@poultrymanager.com', 'admin@poultrymanager.com', '+256 772 100 200', 'Dr. Herbert Musoke (System Admin)', 'Administrator', 'BAT-D-LIVE-115', 'ACTIVE', '2030-12-31', '2026-01-01', 'Active', 'System Administration & Executive'),
('usr_bat_enterprise_001', 'org_bat_corp_01', 'enterprise@poultrymanager.com', 'enterprise@poultrymanager.com', '+256 772 888 999', 'Victoria Namubiru (Enterprise Executive)', 'Executive', 'BAT-D-LIVE-115', 'ACTIVE', '2030-12-31', '2026-01-01', 'Active', 'Enterprise Poultry Operations & Strategy'),
('usr_bat_premium_001', 'org_bat_corp_01', 'premium@poultrymanager.com', 'premium@poultrymanager.com', '+256 782 555 111', 'Sarah Connor (Premium Producer)', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2027-12-31', '2026-01-01', 'Active', 'Commercial Broiler Production'),
('usr_bat_free_001', 'org_bat_corp_01', 'free@poultrymanager.com', 'free@poultrymanager.com', '+256 701 999 888', 'John Doe (Free Tier Farmer)', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2036-12-31', '2026-01-01', 'Active', 'Family Layer Flock')
ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`);

-- ==============================================================================
-- 4. POULTRY OPERATIONAL TABLES (FARMS, FLOCKS, FEED, EGGS, HEALTH)
-- ==============================================================================

-- 4.1 Production Facilities (Farms)
CREATE TABLE `farms` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `organization_id` VARCHAR(64) DEFAULT 'org_bat_corp_01',
  `name` VARCHAR(150) NOT NULL,
  `farm_code` VARCHAR(50) NOT NULL,
  `country` VARCHAR(100) NOT NULL,
  `state_province` VARCHAR(100) NOT NULL,
  `district` VARCHAR(100) NOT NULL,
  `address` TEXT NOT NULL,
  `gps_latitude` DECIMAL(10, 8) DEFAULT NULL,
  `gps_longitude` DECIMAL(11, 8) DEFAULT NULL,
  `farm_type` VARCHAR(50) NOT NULL,
  `ownership` VARCHAR(100) NOT NULL,
  `capacity` INT NOT NULL DEFAULT 0,
  `biosecurity_level` VARCHAR(50) NOT NULL,
  `registration_details` TEXT DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_farm_org` (`organization_id`),
  INDEX `idx_farm_user` (`user_id`),
  CONSTRAINT `fk_farms_user` FOREIGN KEY (`user_id`) REFERENCES `app_users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_farms_org` FOREIGN KEY (`organization_id`) REFERENCES `app_organizations`(`organization_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.2 Seed Production Facilities (Farms)
INSERT INTO `farms` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `organization_id`, `name`, `farm_code`, `country`, `state_province`, `district`, `address`, `gps_latitude`, `gps_longitude`, `farm_type`, `ownership`, `capacity`, `biosecurity_level`, `registration_details`, `synced`) VALUES
('farm_001', 'farm_001', 'srv_farm_001', 'usr_bat_001', 'org_bat_corp_01', 'Sunrise Main Layer Farm', 'SUN-001', 'Uganda', 'Central', 'Wakiso', 'Gayaza Road, Block 14', 0.44890000, 32.61050000, 'Layer', 'Commercial', 12000, 'High', 'REG-AGRI-2024-001', 1),
('farm_1786636205864_771', 'farm_1786636205864_771', 'srv_farm_002', 'usr_bat_001', 'org_bat_corp_01', 'Highland Broiler & Breeder Farm', 'HBD-002', 'Kenya', 'Rift Valley', 'Nakuru', 'Lanet Sector 4', -0.28330000, 36.06670000, 'Broiler', 'Commercial', 15000, 'Maximum Biosecurity', 'REG-KE-9921', 1),
('farm_demo_03', 'farm_demo_03', 'srv_farm_003', 'usr_bat_001', 'org_bat_corp_01', 'Victoria Dual-Purpose Free-Range Station', 'VIC-003', 'Uganda', 'Eastern', 'Jinja', 'Mbuya Hill Agri Zone', 0.42440000, 33.20410000, 'Dual Purpose', 'Cooperative', 8000, 'Standard', 'REG-UG-8823', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `capacity` = VALUES(`capacity`);

-- 4.3 Flocks
CREATE TABLE `flocks` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `batch_number` VARCHAR(100) NOT NULL,
  `species` VARCHAR(50) NOT NULL DEFAULT 'Chicken',
  `breed` VARCHAR(100) NOT NULL,
  `production_system` VARCHAR(50) NOT NULL,
  `source` VARCHAR(100) NOT NULL,
  `hatch_date` DATE NOT NULL,
  `arrival_date` DATE NOT NULL,
  `quantity` INT NOT NULL DEFAULT 0,
  `mortality` INT NOT NULL DEFAULT 0,
  `current_population` INT NOT NULL DEFAULT 0,
  `target_market` VARCHAR(100) DEFAULT NULL,
  `housing_info` TEXT DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'Active',
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_flock_farm` (`farm_id`),
  INDEX `idx_flock_status` (`status`),
  CONSTRAINT `fk_flocks_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_flocks_user` FOREIGN KEY (`user_id`) REFERENCES `app_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.4 Seed Flocks
INSERT INTO `flocks` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `batch_number`, `species`, `breed`, `production_system`, `source`, `hatch_date`, `arrival_date`, `quantity`, `mortality`, `current_population`, `target_market`, `housing_info`, `status`, `synced`) VALUES
('flock_001', 'flock_001', 'srv_flock_001', 'usr_bat_001', 'farm_001', 'BAT-2026-01', 'Chicken', 'Isa Brown (Commercial Layer)', 'Deep Litter with Perches', 'Kampala Chicks Ltd', '2025-08-15', '2025-08-16', 5000, 120, 4880, 'Grade A Table Eggs Wholesale', 'Shed 1 - Climate Controlled', 'Active', 1),
('flock_002', 'flock_002', 'srv_flock_002', 'usr_bat_001', 'farm_1786636205864_771', 'BAT-2026-02', 'Chicken', 'Cobb 500 (Fast Broiler)', 'Floor Litter', 'KenChic Kenya Ltd', '2026-01-10', '2026-01-11', 3500, 85, 3415, 'Dressed Meat & Supermarket Wholesale', 'Shed 2 - Tunnel Ventilated', 'Active', 1),
('flock_003', 'flock_003', 'srv_flock_003', 'usr_bat_001', 'farm_demo_03', 'BAT-2026-03', 'Chicken', 'Kuroiler (Dual Purpose)', 'Free Range & Pasture', 'National Livestock Resources Institute', '2025-11-01', '2025-11-02', 1500, 45, 1455, 'Organic Eggs & Free-Range Table Meat', 'Shed 3 - Pasture Enclosure', 'Active', 1)
ON DUPLICATE KEY UPDATE `batch_number` = VALUES(`batch_number`), `current_population` = VALUES(`current_population`), `mortality` = VALUES(`mortality`);

-- 4.5 Feed Silos & Storage Inventory
CREATE TABLE `feed_inventory` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `feed_type` VARCHAR(100) NOT NULL,
  `current_stock_kg` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `reorder_level_kg` DECIMAL(10, 2) NOT NULL DEFAULT 100.00,
  `cost_per_kg` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `supplier` VARCHAR(150) NOT NULL DEFAULT 'AgriFeeds Ltd',
  `last_restocked` DATE DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_feed_inv_farm` (`farm_id`),
  CONSTRAINT `fk_feed_inv_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `feed_inventory` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `feed_type`, `current_stock_kg`, `reorder_level_kg`, `cost_per_kg`, `supplier`, `last_restocked`, `synced`) VALUES
('fsi_001', 'fsi_001', 'srv_fsi_001', 'usr_bat_001', 'farm_001', 'Layer Phase 1 Mash', 3250.00, 500.00, 0.48, 'AgriFeeds Ltd', '2026-02-28', 1),
('fsi_002', 'fsi_002', 'srv_fsi_002', 'usr_bat_001', 'farm_1786636205864_771', 'Broiler Finisher Pellets', 4100.00, 600.00, 0.52, 'Quality Feeds Kenya', '2026-03-01', 1),
('fsi_003', 'fsi_003', 'srv_fsi_003', 'usr_bat_001', 'farm_demo_03', 'Grower Mash Standard', 1800.00, 400.00, 0.42, 'AgriFeeds Ltd', '2026-02-25', 1),
('fsi_004', 'fsi_004', 'srv_fsi_004', 'usr_bat_001', 'farm_001', 'Pre-Starter Crumbles', 220.00, 300.00, 0.65, 'Novus Nutrition', '2026-02-15', 1)
ON DUPLICATE KEY UPDATE `current_stock_kg` = VALUES(`current_stock_kg`), `cost_per_kg` = VALUES(`cost_per_kg`);

-- 4.6 Egg Harvest Records
CREATE TABLE `egg_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `total_eggs` INT NOT NULL DEFAULT 0,
  `good_eggs` INT NOT NULL DEFAULT 0,
  `broken_eggs` INT NOT NULL DEFAULT 0,
  `dirty_eggs` INT NOT NULL DEFAULT 0,
  `hatchable_eggs` INT NOT NULL DEFAULT 0,
  `commercial_sales` INT NOT NULL DEFAULT 0,
  `home_consumption` INT NOT NULL DEFAULT 0,
  `waste_eggs` INT NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_egg_farm` (`farm_id`),
  INDEX `idx_egg_flock` (`flock_id`),
  INDEX `idx_egg_date` (`date`),
  CONSTRAINT `fk_eggs_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eggs_flock` FOREIGN KEY (`flock_id`) REFERENCES `flocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `egg_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `date`, `total_eggs`, `good_eggs`, `broken_eggs`, `dirty_eggs`, `hatchable_eggs`, `commercial_sales`, `home_consumption`, `waste_eggs`, `notes`, `synced`) VALUES
('egg_rec_01', 'egg_rec_01', 'srv_egg_01', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 13 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Morning collection 2400, afternoon 2100', 1),
('egg_rec_02', 'egg_rec_02', 'srv_egg_02', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 12 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Peak morning output', 1),
('egg_rec_03', 'egg_rec_03', 'srv_egg_03', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 11 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Even laying distribution across tiers', 1),
('egg_rec_04', 'egg_rec_04', 'srv_egg_04', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 10 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Regular daily collection', 1),
('egg_rec_05', 'egg_rec_05', 'srv_egg_05', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 9 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Excellent shell thickness observed', 1),
('egg_rec_06', 'egg_rec_06', 'srv_egg_06', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 8 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Stable laying performance', 1),
('egg_rec_07', 'egg_rec_07', 'srv_egg_07', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Weekend wholesale dispatch completed', 1),
('egg_rec_08', 'egg_rec_08', 'srv_egg_08', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 6 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Monday morning inspection passed', 1),
('egg_rec_09', 'egg_rec_09', 'srv_egg_09', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Consistent 92.6% lay velocity', 1),
('egg_rec_10', 'egg_rec_10', 'srv_egg_10', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 4 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Midweek collection on schedule', 1),
('egg_rec_11', 'egg_rec_11', 'srv_egg_11', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Egg collection trays sanitized', 1),
('egg_rec_12', 'egg_rec_12', 'srv_egg_12', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'High uniformity Grade A harvest', 1),
('egg_rec_13', 'egg_rec_13', 'srv_egg_13', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Full automated belt collection', 1),
('egg_rec_14', 'egg_rec_14', 'srv_egg_14', 'usr_bat_001', 'farm_001', 'flock_001', CURDATE(), 4500, 4320, 90, 60, 0, 3600, 60, 30, 'Current daily collection logged', 1)
ON DUPLICATE KEY UPDATE `total_eggs` = VALUES(`total_eggs`), `good_eggs` = VALUES(`good_eggs`);

-- 4.7 Feed Dispensing & Nutrition Records
CREATE TABLE `feed_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `feed_type` VARCHAR(100) NOT NULL,
  `quantity_kg` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `cost_per_kg` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `total_cost` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `supplier` VARCHAR(150) NOT NULL,
  `administered_by` VARCHAR(100) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_feed_farm` (`farm_id`),
  INDEX `idx_feed_flock` (`flock_id`),
  CONSTRAINT `fk_feed_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_feed_flock` FOREIGN KEY (`flock_id`) REFERENCES `flocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `feed_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `date`, `feed_type`, `quantity_kg`, `cost_per_kg`, `total_cost`, `supplier`, `administered_by`, `notes`, `synced`) VALUES
('fr_log_01', 'fr_log_01', 'srv_fr_01', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 13 DAY), 'Layer Phase 1 Mash', 550.00, 0.48, 264.00, 'AgriFeeds Ltd', 'Samuel Kaggwa', 'Daily ration morning/afternoon split', 1),
('fr_log_02', 'fr_log_02', 'srv_fr_02', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 13 DAY), 'Broiler Finisher Pellets', 480.00, 0.52, 249.60, 'Quality Feeds Kenya', 'Peter Ochieng', 'High density growth ration', 1),
('fr_log_03', 'fr_log_03', 'srv_fr_03', 'usr_bat_001', 'farm_demo_03', 'flock_003', DATE_SUB(CURDATE(), INTERVAL 13 DAY), 'Grower Mash Standard', 160.00, 0.42, 67.20, 'AgriFeeds Ltd', 'Jane Namubiru', 'Free range pasture supplement', 1),
('fr_log_04', 'fr_log_04', 'srv_fr_04', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Layer Phase 1 Mash', 550.00, 0.48, 264.00, 'AgriFeeds Ltd', 'Samuel Kaggwa', 'Weekly scheduled dispensing', 1),
('fr_log_05', 'fr_log_05', 'srv_fr_05', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Broiler Finisher Pellets', 480.00, 0.52, 249.60, 'Quality Feeds Kenya', 'Peter Ochieng', 'Finisher cycle feed', 1),
('fr_log_06', 'fr_log_06', 'srv_fr_06', 'usr_bat_001', 'farm_demo_03', 'flock_003', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Grower Mash Standard', 160.00, 0.42, 67.20, 'AgriFeeds Ltd', 'Jane Namubiru', 'Free range ration', 1),
('fr_log_07', 'fr_log_07', 'srv_fr_07', 'usr_bat_001', 'farm_001', 'flock_001', CURDATE(), 'Layer Phase 1 Mash', 550.00, 0.48, 264.00, 'AgriFeeds Ltd', 'Samuel Kaggwa', 'Today layer mash distribution', 1),
('fr_log_08', 'fr_log_08', 'srv_fr_08', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', CURDATE(), 'Broiler Finisher Pellets', 480.00, 0.52, 249.60, 'Quality Feeds Kenya', 'Peter Ochieng', 'Today broiler pellets distribution', 1)
ON DUPLICATE KEY UPDATE `quantity_kg` = VALUES(`quantity_kg`), `total_cost` = VALUES(`total_cost`);

-- 4.8 Veterinary & Health Records
CREATE TABLE `health_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `description` TEXT NOT NULL,
  `disease_suspected` VARCHAR(150) DEFAULT NULL,
  `medication_used` VARCHAR(150) DEFAULT NULL,
  `dosage` VARCHAR(100) DEFAULT NULL,
  `cost` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `administered_by` VARCHAR(100) DEFAULT NULL,
  `withdrawal_days` INT DEFAULT 0,
  `withdrawal_end_date` DATE DEFAULT NULL,
  `veterinarian_name` VARCHAR(150) DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_health_farm` (`farm_id`),
  INDEX `idx_health_flock` (`flock_id`),
  CONSTRAINT `fk_health_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_health_flock` FOREIGN KEY (`flock_id`) REFERENCES `flocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `health_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `date`, `category`, `title`, `description`, `disease_suspected`, `medication_used`, `dosage`, `cost`, `administered_by`, `withdrawal_days`, `withdrawal_end_date`, `veterinarian_name`, `synced`) VALUES
('hlth_001', 'hlth_001', 'srv_hlth_001', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 12 DAY), 'Vaccination', 'Newcastle Lasota Booster', 'Scheduled booster via drinking water across all layer lines', NULL, 'Lasota Strain Live Vaccine', '1 dose/bird in 50L skim milk water', 180.00, 'Dr. Grace Achieng', 0, CURDATE(), 'Dr. Grace Achieng', 1),
('hlth_002', 'hlth_002', 'srv_hlth_002', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 6 DAY), 'Vaccination', 'Gumboro IBD Second Dose', 'Intermediate strain ocular and oral administration', NULL, 'IBD Live Vaccine', '1 dose per broiler chick', 160.00, 'Dr. Grace Achieng', 0, CURDATE(), 'Dr. Grace Achieng', 1),
('hlth_003', 'hlth_003', 'srv_hlth_003', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'Deworming', 'Routine Piperazine Deworming', 'Helminthiasis prevention program for floor layers', 'Intestinal roundworms', 'Piperazine Citrate 100%', '2g per liter water for 48 hrs', 95.00, 'Samuel Kaggwa', 3, DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'Dr. Grace Achieng', 1),
('hlth_004', 'hlth_004', 'srv_hlth_004', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Supplement', 'Egg Shell Calcium & Vitamin AD3E', 'Electrolyte and calcium fortifier to sustain peak production', NULL, 'Calci-Boost Ultra Oral Liquid', '5ml per liter for 5 days', 145.00, 'Samuel Kaggwa', 0, CURDATE(), 'Dr. Grace Achieng', 1)
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`), `cost` = VALUES(`cost`);

-- 4.9 Daily Performance Monitoring
CREATE TABLE `daily_performance` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `mortality_count` INT NOT NULL DEFAULT 0,
  `culled_count` INT NOT NULL DEFAULT 0,
  `feed_consumed_kg` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `water_consumed_liters` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `average_weight_grams` DECIMAL(10, 2) DEFAULT NULL,
  `temperature_celsius` DECIMAL(5, 2) DEFAULT NULL,
  `humidity_percent` DECIMAL(5, 2) DEFAULT NULL,
  `health_status` VARCHAR(50) DEFAULT 'Normal',
  `notes` TEXT DEFAULT NULL,
  `action_taken` TEXT DEFAULT NULL,
  `recorded_by` VARCHAR(100) DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_dp_farm` (`farm_id`),
  INDEX `idx_dp_flock` (`flock_id`),
  INDEX `idx_dp_date` (`date`),
  CONSTRAINT `fk_dp_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_dp_flock` FOREIGN KEY (`flock_id`) REFERENCES `flocks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `daily_performance` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `date`, `mortality_count`, `feed_consumed_kg`, `water_consumed_liters`, `average_weight_grams`, `temperature_celsius`, `humidity_percent`, `health_status`, `recorded_by`, `synced`) VALUES
('dp_001', 'dp_001', 'srv_dp_001', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 2, 550.00, 1150.00, 1820.00, 24.50, 62.00, 'Normal', 'Samuel Kaggwa', 1),
('dp_002', 'dp_002', 'srv_dp_002', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 1, 480.00, 980.00, 2150.00, 23.00, 58.00, 'Normal', 'Peter Ochieng', 1),
('dp_003', 'dp_003', 'srv_dp_003', 'usr_bat_001', 'farm_demo_03', 'flock_003', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 0, 160.00, 340.00, 1420.00, 25.00, 65.00, 'Normal', 'Jane Namubiru', 1),
('dp_004', 'dp_004', 'srv_dp_004', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1, 550.00, 1140.00, 1825.00, 24.20, 60.00, 'Normal', 'Samuel Kaggwa', 1),
('dp_005', 'dp_005', 'srv_dp_005', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 2, 480.00, 990.00, 2180.00, 23.50, 59.00, 'Normal', 'Peter Ochieng', 1),
('dp_006', 'dp_006', 'srv_dp_006', 'usr_bat_001', 'farm_001', 'flock_001', CURDATE(), 1, 550.00, 1160.00, 1830.00, 24.00, 61.00, 'Normal', 'Samuel Kaggwa', 1),
('dp_007', 'dp_007', 'srv_dp_007', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', CURDATE(), 1, 480.00, 975.00, 2200.00, 23.20, 57.00, 'Normal', 'Peter Ochieng', 1)
ON DUPLICATE KEY UPDATE `mortality_count` = VALUES(`mortality_count`), `feed_consumed_kg` = VALUES(`feed_consumed_kg`);

-- 4.10 Flock Gestation Protocols & Tasks
CREATE TABLE `flock_tasks` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) DEFAULT NULL,
  `title` VARCHAR(150) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `category` VARCHAR(50) NOT NULL DEFAULT 'ROUTINE',
  `priority` VARCHAR(20) NOT NULL DEFAULT 'MEDIUM',
  `due_date` DATE NOT NULL,
  `due_time` VARCHAR(10) DEFAULT NULL,
  `assigned_to` VARCHAR(100) DEFAULT NULL,
  `completed` TINYINT(1) NOT NULL DEFAULT 0,
  `completed_at` DATETIME DEFAULT NULL,
  `completed_by` VARCHAR(100) DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_task_farm` (`farm_id`),
  INDEX `idx_task_due` (`due_date`),
  CONSTRAINT `fk_tasks_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `flock_tasks` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `title`, `description`, `category`, `priority`, `due_date`, `due_time`, `assigned_to`, `completed`, `completed_at`, `metadata_json`, `synced`) VALUES
('tsk_001', 'tsk_001', 'srv_tsk_001', 'usr_bat_001', 'farm_001', 'flock_001', 'Disinfect Footbath Stations', 'Refill Virkon-S broad spectrum disinfectant at coop entries', 'BIOSECURITY', 'HIGH', CURDATE(), '06:30 AM', 'Samuel Kaggwa', 1, NOW(), '{"is_mandatory":false,"protocol_phase":"Routine Biosecurity"}', 1),
('tsk_002', 'tsk_002', 'srv_tsk_002', 'usr_bat_001', 'farm_001', 'flock_001', 'Newcastle Disease (Lasota) Booster', 'MANDATORY VACCINE: Administer Lasota strain via morning drinking water with skimmed milk stabilizer.', 'VACCINATION', 'CRITICAL', DATE_ADD(CURDATE(), INTERVAL 1 DAY), '07:00 AM', 'Dr. Grace Achieng', 0, NULL, '{"is_mandatory":true,"advance_warning_hours":48,"auto_generated":true}', 1),
('tsk_003', 'tsk_003', 'srv_tsk_003', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', 'Anti-Coccidiosis Medication Course', 'MANDATORY COURSE: Administer prophylactic Amprolium in drinking water for 3 consecutive days.', 'MEDICATION', 'HIGH', CURDATE(), '08:00 AM', 'Samuel Kaggwa', 0, NULL, '{"is_mandatory":true,"advance_warning_hours":48,"auto_generated":true}', 1),
('tsk_004', 'tsk_004', 'srv_tsk_004', 'usr_bat_001', 'farm_001', 'flock_001', 'Sample Bird Weighing (5% Uniformity Audit)', 'Random sample weighing across front, center, and back pens to evaluate pullet growth curve.', 'ROUTINE', 'MEDIUM', DATE_ADD(CURDATE(), INTERVAL 2 DAY), '09:00 AM', 'Samuel Kaggwa', 0, NULL, '{"is_mandatory":false,"advance_warning_hours":48}', 1),
('tsk_005', 'tsk_005', 'srv_tsk_005', 'usr_bat_001', 'farm_demo_03', 'flock_003', 'Fowl Pox Wing-Web Vaccination & Deworming', 'MANDATORY VACCINE: Wing-web double puncture with live attenuated fowl pox vaccine.', 'VACCINATION', 'CRITICAL', DATE_ADD(CURDATE(), INTERVAL 3 DAY), '07:30 AM', 'Dr. Grace Achieng', 0, NULL, '{"is_mandatory":true,"advance_warning_hours":48,"auto_generated":true}', 1)
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`), `completed` = VALUES(`completed`);

-- 4.11 Financial Ledger (Cash Flow, Incomes & Expenses)
CREATE TABLE `finance_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) DEFAULT NULL,
  `type` ENUM('Income', 'Expense', 'Payroll') NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `date` DATE NOT NULL,
  `reference_no` VARCHAR(100) DEFAULT NULL,
  `payment_method` VARCHAR(50) DEFAULT 'Cash',
  `payee_payer` VARCHAR(150) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_fin_farm` (`farm_id`),
  INDEX `idx_fin_date` (`date`),
  CONSTRAINT `fk_fin_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `finance_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `type`, `category`, `amount`, `date`, `reference_no`, `payment_method`, `payee_payer`, `notes`, `synced`) VALUES
('fin_001', 'fin_001', 'srv_fin_001', 'usr_bat_001', 'farm_001', 'flock_001', 'Income', 'Egg Sales', 5880.00, DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'INV-2026-081', 'Bank Transfer', 'Sunrise Wholesale Supermarkets', '1,680 standard trays commercial Grade A egg delivery', 1),
('fin_002', 'fin_002', 'srv_fin_002', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', 'Income', 'Meat / Live Bird Sales', 3250.00, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'INV-2026-082', 'Bank Transfer', 'Lanet Meat Processors Ltd', '500 processed broiler birds wholesale delivery', 1),
('fin_003', 'fin_003', 'srv_fin_003', 'usr_bat_001', 'farm_001', 'flock_001', 'Income', 'Organic Fertilizer', 450.00, DATE_SUB(CURDATE(), INTERVAL 8 DAY), 'REC-2026-041', 'Mobile Money', 'Wakiso Horticultural Cooperative', '45 bags dry composted poultry manure', 1),
('fin_004', 'fin_004', 'srv_fin_004', 'usr_bat_001', 'farm_demo_03', 'flock_003', 'Income', 'Cull Bird Sales', 1100.00, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'REC-2026-042', 'Cash', 'Jinja Central Market Vendors', '110 mature heritage birds at end of test cycle', 1),
('fin_005', 'fin_005', 'srv_fin_005', 'usr_bat_001', 'farm_001', 'flock_001', 'Expense', 'Feed & Nutrition Purchase', 4500.00, DATE_SUB(CURDATE(), INTERVAL 10 DAY), 'EXP-2026-101', 'Bank Transfer', 'AgriFeeds Ltd', 'Bulk restocking 10 tonnes layer and broiler mash', 1),
('fin_006', 'fin_006', 'srv_fin_006', 'usr_bat_001', 'farm_001', 'flock_001', 'Expense', 'Veterinary & Medicine', 780.00, DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'EXP-2026-102', 'Bank Transfer', 'Highland AgroVet Solutions', 'Lasota Newcastle live vaccines and vitamins', 1),
('fin_007', 'fin_007', 'srv_fin_007', 'usr_bat_001', 'farm_001', 'flock_001', 'Expense', 'Utilities (Water & Power)', 420.00, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'EXP-2026-103', 'Mobile Money', 'National Water & Power Board', 'Monthly coop borehole and automated lighting power', 1),
('fin_008', 'fin_008', 'srv_fin_008', 'usr_bat_001', 'farm_001', 'flock_001', 'Expense', 'Packaging & Crates', 380.00, DATE_SUB(CURDATE(), INTERVAL 6 DAY), 'EXP-2026-104', 'Mobile Money', 'Standard Packaging Mills', '2,000 heavy duty molded pulp egg trays', 1),
('fin_009', 'fin_009', 'srv_fin_009', 'usr_bat_001', 'farm_001', NULL, 'Payroll', 'Farm Attendants Wages', 1200.00, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'PAY-2026-001', 'Bank Transfer', 'Farm Labor Crew (4 Attendants)', 'Bi-weekly labor distribution and coop maintenance', 1),
('fin_010', 'fin_010', 'srv_fin_010', 'usr_bat_001', 'farm_001', NULL, 'Payroll', 'Management Stipend', 800.00, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'PAY-2026-002', 'Bank Transfer', 'Samuel Kaggwa (Farm Manager)', 'Monthly operations management stipend', 1)
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`), `type` = VALUES(`type`);

-- 4.12 Biosecurity Audits
CREATE TABLE `biosecurity_audits` (
  `id` VARCHAR(64) PRIMARY KEY,
  `farm_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `audit_date` DATE NOT NULL,
  `auditor_name` VARCHAR(150) NOT NULL,
  `score` DECIMAL(5, 2) NOT NULL DEFAULT 85.00,
  `compliance_status` VARCHAR(30) NOT NULL DEFAULT 'PASSED',
  `footbath_disinfection_active` TINYINT(1) DEFAULT 1,
  `visitor_log_maintained` TINYINT(1) DEFAULT 1,
  `protective_clothing_enforced` TINYINT(1) DEFAULT 1,
  `vehicle_disinfection_spray` TINYINT(1) DEFAULT 1,
  `wild_bird_rodent_proofed` TINYINT(1) DEFAULT 1,
  `mortality_disposal_compliant` TINYINT(1) DEFAULT 1,
  `water_sanitization_active` TINYINT(1) DEFAULT 1,
  `quarantine_unit_available` TINYINT(1) DEFAULT 1,
  `corrective_actions` TEXT DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `metadata_json` LONGTEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_bio_farm` (`farm_id`),
  CONSTRAINT `fk_bio_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `biosecurity_audits` (`id`, `farm_id`, `user_id`, `audit_date`, `auditor_name`, `score`, `compliance_status`, `footbath_disinfection_active`, `visitor_log_maintained`, `protective_clothing_enforced`, `vehicle_disinfection_spray`, `wild_bird_rodent_proofed`, `mortality_disposal_compliant`, `water_sanitization_active`, `quarantine_unit_available`, `notes`, `synced`) VALUES
('bio_001', 'farm_001', 'usr_bat_001', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'Dr. Grace Achieng', 94.50, 'PASSED', 1, 1, 1, 1, 1, 1, 1, 1, 'Coop 1 biosecurity barrier integrity verified. Perimeter fence clear.', 1),
('bio_002', 'farm_1786636205864_771', 'usr_bat_001', DATE_SUB(CURDATE(), INTERVAL 8 DAY), 'Dr. Grace Achieng', 91.00, 'PASSED', 1, 1, 1, 1, 0, 1, 1, 1, 'Recommend netting inspection on eastern ventilation vents.', 1)
ON DUPLICATE KEY UPDATE `score` = VALUES(`score`), `compliance_status` = VALUES(`compliance_status`);

-- 4.12.1 Breeding & Parent Stock Records
CREATE TABLE `breeding_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `flock_id` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `breeding_pen` VARCHAR(100) NOT NULL DEFAULT 'Pen A',
  `male_count` INT NOT NULL DEFAULT 10,
  `female_count` INT NOT NULL DEFAULT 100,
  `mating_ratio` VARCHAR(50) NOT NULL DEFAULT '1:10',
  `eggs_collected` INT NOT NULL DEFAULT 0,
  `damaged_eggs` INT NOT NULL DEFAULT 0,
  `fertility_rate` DECIMAL(5, 2) NOT NULL DEFAULT 85.00,
  `notes` TEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_brd_farm` (`farm_id`),
  INDEX `idx_brd_flock` (`flock_id`),
  CONSTRAINT `fk_brd_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `breeding_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `flock_id`, `date`, `breeding_pen`, `male_count`, `female_count`, `mating_ratio`, `eggs_collected`, `damaged_eggs`, `fertility_rate`, `notes`, `synced`) VALUES
('brd_001', 'brd_001', 'srv_brd_001', 'usr_bat_001', 'farm_001', 'flock_001', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Pen A - Pureline Sasso', 12, 120, '1:10', 98, 2, 88.50, 'Parent stock vigorous; high fertility and egg cleanliness.', 1),
('brd_002', 'brd_002', 'srv_brd_002', 'usr_bat_001', 'farm_1786636205864_771', 'flock_002', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'Pen B - Kuroiler Cross', 10, 100, '1:10', 82, 3, 86.00, 'Adequate mating activity observed. Feed supplemented with vitamin E.', 1)
ON DUPLICATE KEY UPDATE `fertility_rate` = VALUES(`fertility_rate`);

-- 4.12.2 Hatchery & Incubation Batches
CREATE TABLE `hatchery_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `batch_code` VARCHAR(100) NOT NULL,
  `incubator_no` VARCHAR(100) NOT NULL DEFAULT 'Incubator Unit 1',
  `setting_date` DATE NOT NULL,
  `expected_hatch_date` DATE NOT NULL,
  `eggs_set` INT NOT NULL DEFAULT 500,
  `candling_day` INT NOT NULL DEFAULT 7,
  `fertile_eggs` INT NOT NULL DEFAULT 440,
  `infertile_eggs` INT NOT NULL DEFAULT 60,
  `hatched_count` INT NOT NULL DEFAULT 395,
  `cull_count` INT NOT NULL DEFAULT 12,
  `hatchability_rate` DECIMAL(5, 2) NOT NULL DEFAULT 79.00,
  `status` VARCHAR(50) NOT NULL DEFAULT 'HATCHED',
  `notes` TEXT DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_hatch_farm` (`farm_id`),
  CONSTRAINT `fk_hatch_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `hatchery_records` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `batch_code`, `incubator_no`, `setting_date`, `expected_hatch_date`, `eggs_set`, `candling_day`, `fertile_eggs`, `infertile_eggs`, `hatched_count`, `cull_count`, `hatchability_rate`, `status`, `notes`, `synced`) VALUES
('hatch_001', 'hatch_001', 'srv_hatch_001', 'usr_bat_001', 'farm_001', 'HB-2026-081', 'Petersime Setter Unit 01', DATE_SUB(CURDATE(), INTERVAL 24 DAY), DATE_SUB(CURDATE(), INTERVAL 3 DAY), 1200, 7, 1080, 120, 972, 18, 81.00, 'HATCHED', 'Successful hatch with 972 Grade-A vigorous chicks dispatched.', 1),
('hatch_002', 'hatch_002', 'srv_hatch_002', 'usr_bat_001', 'farm_001', 'HB-2026-082', 'Petersime Setter Unit 02', DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_ADD(CURDATE(), INTERVAL 11 DAY), 1500, 7, 1365, 135, 0, 0, 0.00, 'INCUBATING', 'Day 7 candling completed. 91% fertility confirmed. Transferred to setting trays.', 1)
ON DUPLICATE KEY UPDATE `hatchability_rate` = VALUES(`hatchability_rate`), `status` = VALUES(`status`);

-- 4.13 General Farm Inventory
CREATE TABLE `inventory_items` (
  `id` VARCHAR(64) PRIMARY KEY,
  `local_unique_id` VARCHAR(100) NOT NULL UNIQUE,
  `server_unique_id` VARCHAR(100) DEFAULT NULL,
  `user_id` VARCHAR(64) DEFAULT 'usr_bat_001',
  `farm_id` VARCHAR(64) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `item_name` VARCHAR(150) NOT NULL,
  `quantity` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `unit` VARCHAR(50) NOT NULL,
  `unit_cost` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `reorder_level` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `location` VARCHAR(100) DEFAULT NULL,
  `synced` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_inv_farm` (`farm_id`),
  CONSTRAINT `fk_inv_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventory_items` (`id`, `local_unique_id`, `server_unique_id`, `user_id`, `farm_id`, `category`, `item_name`, `quantity`, `unit`, `unit_cost`, `reorder_level`, `location`, `synced`) VALUES
('inv_001', 'inv_001', 'srv_inv_001', 'usr_bat_001', 'farm_001', 'Packaging', 'Molded Pulp Egg Trays (30-Egg)', 2400.00, 'pieces', 0.12, 500.00, 'Store Room A', 1),
('inv_002', 'inv_002', 'srv_inv_002', 'usr_bat_001', 'farm_001', 'Biosecurity', 'Virkon-S Disinfectant Granules', 25.00, 'kg', 18.50, 10.00, 'Chemical Cabinet', 1),
('inv_003', 'inv_003', 'srv_inv_003', 'usr_bat_001', 'farm_1786636205864_771', 'Equipment', 'Bell Drinkers Commercial 5L', 60.00, 'units', 8.00, 15.00, 'Equipment Shed', 1)
ON DUPLICATE KEY UPDATE `quantity` = VALUES(`quantity`);

-- 4.14 Telemetry and GPS Stamps
CREATE TABLE `telemetry_logs` (
  `id` VARCHAR(64) PRIMARY KEY,
  `user_id` VARCHAR(64) DEFAULT NULL,
  `farm_id` VARCHAR(64) DEFAULT NULL,
  `flock_id` VARCHAR(64) DEFAULT NULL,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` VARCHAR(64) NOT NULL,
  `action` VARCHAR(20) NOT NULL DEFAULT 'CREATE',
  `latitude` DECIMAL(10, 8) DEFAULT NULL,
  `longitude` DECIMAL(11, 8) DEFAULT NULL,
  `accuracy_meters` DECIMAL(8, 2) DEFAULT NULL,
  `device_id` VARCHAR(100) DEFAULT NULL,
  `session_id` VARCHAR(100) DEFAULT NULL,
  `platform` VARCHAR(50) DEFAULT NULL,
  `is_mobile` TINYINT(1) DEFAULT 0,
  `screen_resolution` VARCHAR(50) DEFAULT NULL,
  `hardware_concurrency` INT DEFAULT NULL,
  `device_memory_gb` DECIMAL(4, 2) DEFAULT NULL,
  `network_type` VARCHAR(50) DEFAULT NULL,
  `battery_level` INT DEFAULT NULL,
  `client_timestamp` VARCHAR(50) DEFAULT NULL,
  `telemetry_json` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_tel_farm` (`farm_id`),
  CONSTRAINT `fk_tel_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 4.15 DYNAMIC ECOSYSTEM APPS & PROMOTIONS REPOSITORY
-- ==============================================================================
CREATE TABLE `ecosystem_apps` (
  `id` VARCHAR(64) PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `tagline` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `icon_name` VARCHAR(50) DEFAULT 'Sparkles',
  `icon_url` VARCHAR(255) DEFAULT NULL,
  `bg_image_url` VARCHAR(500) DEFAULT NULL,
  `target_url` VARCHAR(500) NOT NULL,
  `badge` VARCHAR(50) NOT NULL DEFAULT 'BetterAgri Suite',
  `app_iso_code` VARCHAR(50) DEFAULT 'BAT-ECOSYSTEM',
  `category` VARCHAR(100) DEFAULT 'General Agriculture',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `activated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `activation_timestamp` BIGINT DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ecosystem_apps` (`id`, `name`, `tagline`, `description`, `icon_name`, `bg_image_url`, `target_url`, `badge`, `app_iso_code`, `category`, `is_active`, `display_order`) VALUES
('app_bat_102', 'BetterAgri Crop & Feed Manager', 'Precision feed milling, silage & grain storage tracker', 'Formulate custom poultry rations, track maize & soya inventory, and optimize feed conversion efficiency.', 'Wheat', 'https://images.unsplash.com/photo-1500382017468-9049fed747ef?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/apps/bat-102', 'BetterAgri Suite', 'BAT-D-LIVE-102', 'Nutrition & Milling', 1, 1),
('app_bat_105', 'BetterAgri Livestock & Dairy Manager', 'Ruminant herd tracking, milk yields & disease surveillance', 'Integrated dairy & livestock tracking with automated veterinary quarantine logs and calving records.', 'Milk', 'https://images.unsplash.com/photo-1548550023-2bdb3c5beed7?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/apps/bat-105', 'BetterAgri Suite', 'BAT-D-LIVE-105', 'Livestock & Health', 1, 2),
('app_bat_108', 'BetterAgri Farm Books & Multi-Ledger', 'Unified agribusiness finance, payroll, and cash flow', 'Consolidated financial reports, multi-currency accounting, and bank reconciliation across all farm branches.', 'Wallet', 'https://images.unsplash.com/photo-1554224155-6726b3ff858f?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/apps/bat-108', 'BetterAgri Finance', 'BAT-D-LIVE-108', 'Financial Accounting', 1, 3),
('app_bat_112', 'BetterAgri Cold Chain & Egg Logistics', 'Traceability, cold storage humidity & delivery dispatch', 'End-to-end egg batch tracking from grading shed to supermarkets with temperature alert monitoring.', 'PackageCheck', 'https://images.unsplash.com/photo-1582722872445-44dc5f7e3c8f?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/apps/bat-112', 'Supply Chain', 'BAT-D-LIVE-112', 'Logistics & Supply', 1, 4)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- ==============================================================================
-- 4.16 VERIFIED PARTNER ORGANIZATIONS DIRECTORY
-- ==============================================================================
CREATE TABLE `partner_organizations` (
  `id` VARCHAR(64) PRIMARY KEY,
  `organization_name` VARCHAR(150) NOT NULL,
  `tagline` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `partner_type` VARCHAR(100) NOT NULL DEFAULT 'Feed & Nutrition',
  `badge` VARCHAR(50) NOT NULL DEFAULT 'Verified Agri-Partner',
  `logo_url` VARCHAR(255) DEFAULT NULL,
  `bg_image_url` VARCHAR(500) DEFAULT NULL,
  `website_url` VARCHAR(500) NOT NULL,
  `cta_text` VARCHAR(50) NOT NULL DEFAULT 'Visit Partner',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `activated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `activation_timestamp` BIGINT DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `partner_organizations` (`id`, `organization_name`, `tagline`, `description`, `partner_type`, `badge`, `bg_image_url`, `website_url`, `cta_text`, `is_active`, `display_order`) VALUES
('partner_001', 'Ugachick Poultry Feeds & Concentrates', 'High-protein broiler & layer mash formulations', 'Nutritionally balanced poultry rations enriched with amino acids and essential minerals for peak lay rates. Order 50+ bags for free farm delivery.', 'Feed & Concentrates', 'Verified Agri-Partner', 'https://images.unsplash.com/photo-1516467508483-a7212febe31a?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/partners/ugachick', 'View Feed Catalog', 1, 1),
('partner_002', 'AgroVet Solar Egg Incubators', 'Zero-grid digital solar setters & hatchers', 'High hatchability automated egg incubators with digital microclimate regulation, automatic turning, and 24-hour battery backup storage.', 'Incubation & Equipment', 'Equipment Partner', 'https://images.unsplash.com/photo-1522071820081-009f0129c71c?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/partners/agrovet', 'Explore Incubators', 1, 2),
('partner_003', 'Boehringer Avian Vaccines & Health', 'Certified cold-chain Newcastle & Gumboro biologics', 'Protect your flock from Newcastle, Gumboro, and Infectious Bronchitis with cold-chain verified vaccines through certified regional veterinary stockists.', 'Veterinary Biosecurity', 'Veterinary Partner', 'https://images.unsplash.com/photo-1584515979956-d9f6e5d09982?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/partners/boehringer', 'Find Vet Stockist', 1, 3),
('partner_004', 'Bukoola Biosecurity & Disinfection', 'Hospital-grade virucidal boot baths & coop sanitizers', 'Broad-spectrum DEFRA-approved virucidal disinfectants for pen washdowns, boot footbaths, and vehicular disinfection gates.', 'Farm Biosecurity', 'Biosecurity Partner', 'https://images.unsplash.com/photo-1587293852726-70cdb56c2866?auto=format&fit=crop&w=1200&q=80', 'https://betteragritech.com/partners/bukoola', 'Order Disinfectants', 1, 4)
ON DUPLICATE KEY UPDATE `organization_name` = VALUES(`organization_name`);

-- ==============================================================================
-- 5. PLANS & SUBSCRIPTIONS (BAT-D-LIVE-115 TIER DEFINITIONS)
-- ==============================================================================
CREATE TABLE `plans` (
  `plan_id` VARCHAR(64) PRIMARY KEY,
  `plan_code` VARCHAR(50) NOT NULL UNIQUE,
  `plan_name` VARCHAR(100) NOT NULL,
  `tier` ENUM('FREE', 'PREMIUM', 'ENTERPRISE') NOT NULL,
  `scope` ENUM('APPLICATION', 'ECOSYSTEM', 'ORGANIZATION') DEFAULT 'APPLICATION',
  `description` TEXT NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
  `billing_cycle` ENUM('lifetime', 'monthly', 'annual', 'contract') NOT NULL,
  `status` ENUM('ACTIVE', 'ARCHIVED', 'CUSTOM') DEFAULT 'ACTIVE',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `plans` (`plan_id`, `plan_code`, `plan_name`, `tier`, `scope`, `description`, `price`, `currency`, `billing_cycle`, `status`) VALUES
('plan_free_001', 'FREE', 'BetterAgri Free Community', 'FREE', 'APPLICATION', 'Essential farm tracking, 1 farm, 2 flocks, basic production & feed.', 0.00, 'USD', 'lifetime', 'ACTIVE'),
('plan_prem_mo_002', 'PREMIUM_MONTHLY', 'BetterAgri Premium Monthly', 'PREMIUM', 'APPLICATION', 'Unlimited flocks & farms, AI advisor, PDF reports, cloud backup.', 5.00, 'USD', 'monthly', 'ACTIVE'),
('plan_prem_yr_003', 'PREMIUM_ANNUAL', 'BetterAgri Premium Annual', 'PREMIUM', 'APPLICATION', 'Best Value: 12 months full Premium access ($50/yr), priority support.', 50.00, 'USD', 'annual', 'ACTIVE'),
('plan_ent_004', 'ENTERPRISE_ANNUAL', 'BetterAgri Enterprise Multi-Farm', 'ENTERPRISE', 'ORGANIZATION', 'Commercial agribusiness suite with multi-branch, RBAC, and consolidated KPIs.', 250.00, 'USD', 'annual', 'ACTIVE')
ON DUPLICATE KEY UPDATE `plan_name` = VALUES(`plan_name`);

CREATE TABLE `subscriptions` (
  `subscription_id` VARCHAR(64) PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `organization_id` VARCHAR(64) DEFAULT NULL,
  `app_iso_code` VARCHAR(50) NOT NULL DEFAULT 'BAT-D-LIVE-115',
  `plan_id` VARCHAR(64) NOT NULL,
  `subscription_status` ENUM('FREE', 'TRIAL', 'ACTIVE', 'PAST_DUE', 'GRACE_PERIOD', 'EXPIRED', 'CANCELLED', 'SUSPENDED', 'PENDING', 'REVOKED') DEFAULT 'ACTIVE',
  `billing_cycle` VARCHAR(20) NOT NULL DEFAULT 'annual',
  `start_date` DATE NOT NULL,
  `expiry_date` DATE NOT NULL,
  `last_renewed_date` DATE DEFAULT NULL,
  `next_billing_date` DATE DEFAULT NULL,
  `auto_renew` TINYINT(1) DEFAULT 1,
  `payment_provider` VARCHAR(50) DEFAULT 'Google Play Billing',
  `external_subscription_id` VARCHAR(150) DEFAULT NULL,
  `transaction_reference` VARCHAR(150) DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT 'USD',
  `amount` DECIMAL(10, 2) DEFAULT 50.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sub_user` FOREIGN KEY (`user_id`) REFERENCES `app_users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sub_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans`(`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Subscriptions for Test Accounts
INSERT INTO `subscriptions` (`subscription_id`, `user_id`, `organization_id`, `app_iso_code`, `plan_id`, `subscription_status`, `billing_cycle`, `start_date`, `expiry_date`, `last_renewed_date`, `auto_renew`, `payment_provider`, `currency`, `amount`) VALUES
('sub_bsep_lead_01', 'usr_bat_001', 'org_bat_corp_01', 'BAT-D-LIVE-115', 'plan_ent_004', 'ACTIVE', 'annual', '2026-01-01', '2030-12-31', '2026-01-01', 1, 'Enterprise Invoicing', 'USD', 250.00),
('sub_bsep_admin_live_01', 'usr_bat_admin_001', 'org_bat_corp_01', 'BAT-D-LIVE-115', 'plan_ent_004', 'ACTIVE', 'annual', '2026-01-01', '2030-12-31', '2026-01-01', 1, 'Enterprise Invoicing', 'USD', 250.00),
('sub_bsep_enterprise_test_01', 'usr_bat_enterprise_001', 'org_bat_corp_01', 'BAT-D-LIVE-115', 'plan_ent_004', 'ACTIVE', 'annual', '2026-01-01', '2030-12-31', '2026-01-01', 1, 'Enterprise Invoicing', 'USD', 250.00),
('sub_bsep_premium_test_01', 'usr_bat_premium_001', 'org_bat_corp_01', 'BAT-D-LIVE-115', 'plan_prem_yr_003', 'ACTIVE', 'annual', '2026-01-01', '2027-12-31', '2026-01-01', 1, 'Google Play Billing', 'USD', 50.00),
('sub_bsep_free_test_01', 'usr_bat_free_001', 'org_bat_corp_01', 'BAT-D-LIVE-115', 'plan_free_001', 'ACTIVE', 'lifetime', '2026-01-01', '2036-12-31', '2026-01-01', 0, 'Complimentary Free Tier', 'USD', 0.00)
ON DUPLICATE KEY UPDATE `subscription_status` = VALUES(`subscription_status`);

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

-- Registered App User: farmertest@poultryexample.com
INSERT INTO `app_users` (`id`, `organization_id`, `username`, `email`, `phone_number`, `full_name`, `role`, `primary_app_iso`, `subscription_status`, `expiry_date`, `last_renewal`, `status`, `department`) VALUES ('usr_muata1k0_p8z1', 'org_bat_corp_01', 'farmertest', 'farmertest@poultryexample.com', '', 'Test Poultry Farmer', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2099-12-31', '2026-09-21', 'Active', 'Commercial Poultry') ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`);

-- Registered App User: herbertmusoke88@gmail.com
INSERT INTO `app_users` (`id`, `organization_id`, `username`, `email`, `phone_number`, `full_name`, `role`, `primary_app_iso`, `subscription_status`, `expiry_date`, `last_renewal`, `status`, `department`) VALUES ('usr_muaze41s_nies', 'org_bat_corp_01', 'herbertmusoke', 'herbertmusoke88@gmail.com', '', 'Herbert Musoke', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2099-12-31', '2026-09-21', 'Active', 'Commercial Poultry') ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`);

-- Registered App User: herbertmusoke88@gmail.com
INSERT INTO `app_users` (`id`, `organization_id`, `username`, `email`, `phone_number`, `full_name`, `role`, `primary_app_iso`, `subscription_status`, `expiry_date`, `last_renewal`, `status`, `department`) VALUES ('usr_muaze41s_nies', 'org_bat_corp_01', 'herbertmusoke', 'herbertmusoke88@gmail.com', '', 'Herbert Musoke', 'Farm Owner', 'BAT-D-LIVE-115', 'ACTIVE', '2099-12-31', '2026-09-21', 'Active', 'Commercial Poultry') ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`);