-- =============================================================================
-- BETTERAGRI AUTH ID CENTRAL IDENTITY & AUTHENTICATION SUBSYSTEM (v1.0.0)
-- Central Authority for Single Sign-On (SSO), Token Issuing & App Registry
-- BetterAgri Tech Ltd — Kampala, Uganda
-- Database: betteragri_auth | Engine: InnoDB | Charset: utf8mb4_unicode_ci
-- =============================================================================

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

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 `audit_logs`;
DROP TABLE IF EXISTS `security_events`;
DROP TABLE IF EXISTS `api_rate_limits`;
DROP TABLE IF EXISTS `auth_sessions`;
DROP TABLE IF EXISTS `verification_tokens`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `organization_users`;
DROP TABLE IF EXISTS `organizations`;
DROP TABLE IF EXISTS `user_applications`;
DROP TABLE IF EXISTS `application_credentials`;
DROP TABLE IF EXISTS `applications`;
DROP TABLE IF EXISTS `identities`;
DROP TABLE IF EXISTS `admin_users`;
DROP TABLE IF EXISTS `users`;

-- =============================================================================
-- 1. MASTER USERS TABLE (AUTHORITATIVE ACCOUNT DIRECTORY FOR ALL APPS)
-- =============================================================================
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` CHAR(36) NOT NULL UNIQUE COMMENT 'Immutable UUIDv4 identifier',
    `ba_account_number` VARCHAR(16) NOT NULL UNIQUE COMMENT 'Public reference e.g. BA-100201',
    `account_status` ENUM('PENDING_VERIFICATION', 'ACTIVE', 'SUSPENDED', 'LOCKED', 'DEACTIVATED', 'ANONYMIZED') NOT NULL DEFAULT 'ACTIVE',
    `first_name` VARCHAR(64) NOT NULL,
    `last_name` VARCHAR(64) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL COMMENT 'Argon2id or bcrypt hash',
    `preferred_language` VARCHAR(8) NOT NULL DEFAULT 'en',
    `country` VARCHAR(3) NOT NULL DEFAULT 'UGA' COMMENT 'ISO 3166-1 alpha-3 code',
    `timezone` VARCHAR(64) NOT NULL DEFAULT 'Africa/Kampala',
    `failed_login_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `locked_until` DATETIME NULL,
    `last_login_at` DATETIME NULL,
    `last_login_ip` VARCHAR(45) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME NULL,
    INDEX `idx_user_status` (`account_status`),
    INDEX `idx_user_ba_acc` (`ba_account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 2. DECOUPLED IDENTITY ALIASES (EMAIL & E.164 PHONE NUMBER)
-- =============================================================================
CREATE TABLE `identities` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` CHAR(36) NOT NULL,
    `identity_type` ENUM('EMAIL', 'PHONE') NOT NULL,
    `identity_value` VARCHAR(191) NOT NULL COMMENT 'Normalized lowercase email or E.164 phone (+256...)',
    `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
    `verified_at` DATETIME NULL,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_identity_type_val` (`identity_type`, `identity_value`),
    INDEX `idx_ident_user` (`user_id`),
    CONSTRAINT `fk_identities_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 3. APPLICATIONS REGISTRY (ECOSYSTEM DIRECTORY)
-- =============================================================================
CREATE TABLE `applications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `application_id` CHAR(36) NOT NULL UNIQUE,
    `app_iso_code` VARCHAR(32) NOT NULL UNIQUE COMMENT 'e.g. BAT-D-LIVE-115',
    `app_name` VARCHAR(128) NOT NULL,
    `app_family` ENUM('LIVESTOCK', 'CROPS', 'FINTECH', 'DIAGNOSTICS', 'ENTERPRISE') NOT NULL,
    `description` TEXT NULL,
    `current_version` VARCHAR(16) NOT NULL DEFAULT '3.6.0',
    `min_supported_version` VARCHAR(16) NOT NULL DEFAULT '3.0.0',
    `platform` ENUM('ANDROID', 'IOS', 'WEB', 'DESKTOP', 'API') NOT NULL DEFAULT 'ANDROID',
    `package_name` VARCHAR(128) NOT NULL,
    `status` ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'DEPRECATED') NOT NULL DEFAULT 'ACTIVE',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_app_iso` (`app_iso_code`),
    INDEX `idx_app_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 4. APPLICATION CREDENTIALS (OAUTH2 / API CLIENTS)
-- =============================================================================
CREATE TABLE `application_credentials` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `application_id` CHAR(36) NOT NULL,
    `client_id` VARCHAR(64) NOT NULL UNIQUE,
    `client_secret_hash` VARCHAR(255) NOT NULL,
    `allowed_grant_types` VARCHAR(128) NOT NULL DEFAULT 'authorization_code,refresh_token',
    `redirect_uris` TEXT NULL,
    `status` ENUM('ACTIVE', 'REVOKED') NOT NULL DEFAULT 'ACTIVE',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_app_creds_app` FOREIGN KEY (`application_id`) REFERENCES `applications` (`application_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 5. USER-APPLICATION AUTHORIZATION MAPPINGS
-- =============================================================================
CREATE TABLE `user_applications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` CHAR(36) NOT NULL,
    `app_iso_code` VARCHAR(32) NOT NULL,
    `authorization_status` ENUM('AUTHORIZED', 'SUSPENDED', 'REVOKED', 'PENDING_ONBOARDING') NOT NULL DEFAULT 'AUTHORIZED',
    `default_role` VARCHAR(32) NOT NULL DEFAULT 'FARMER',
    `first_authorized_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_accessed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_user_app` (`user_id`, `app_iso_code`),
    INDEX `idx_user_app_iso` (`app_iso_code`),
    CONSTRAINT `fk_user_apps_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_user_apps_code` FOREIGN KEY (`app_iso_code`) REFERENCES `applications` (`app_iso_code`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 6. ENTERPRISE ORGANIZATIONS
-- =============================================================================
CREATE TABLE `organizations` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `org_id` CHAR(36) NOT NULL UNIQUE,
    `org_code` VARCHAR(32) NOT NULL UNIQUE,
    `org_name` VARCHAR(128) NOT NULL,
    `tax_identifier` VARCHAR(64) NULL,
    `country` VARCHAR(3) NOT NULL DEFAULT 'UGA',
    `tier` ENUM('STANDARD', 'COMMERCIAL_ENTERPRISE', 'GOVERNMENT_NGO') NOT NULL DEFAULT 'COMMERCIAL_ENTERPRISE',
    `status` ENUM('ACTIVE', 'SUSPENDED', 'DELINQUENT') NOT NULL DEFAULT 'ACTIVE',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 7. ORGANIZATION USERS (RBAC ASSIGNMENTS)
-- =============================================================================
CREATE TABLE `organization_users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `org_id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `assigned_role` ENUM(
        'ORG_SUPER_ADMIN',
        'FARM_EXECUTIVE',
        'GENERAL_FARM_MANAGER',
        'SECTION_SUPERVISOR',
        'RESIDENT_VETERINARIAN',
        'FEED_NUTRITIONIST',
        'FARM_ACCOUNTANT',
        'DATA_RECORDS_OFFICER',
        'GENERAL_FARM_WORKER',
        'READONLY_AUDITOR'
    ) NOT NULL DEFAULT 'GENERAL_FARM_WORKER',
    `farm_access_scope` JSON NULL,
    `membership_status` ENUM('INVITED', 'ACTIVE', 'SUSPENDED', 'TERMINATED') NOT NULL DEFAULT 'ACTIVE',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_org_user` (`org_id`, `user_id`),
    INDEX `idx_org_user_lookup` (`user_id`),
    CONSTRAINT `fk_org_users_org` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_org_users_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 8. ACTIVE SESSIONS & REFRESH TOKEN ROTATION
-- =============================================================================
CREATE TABLE `auth_sessions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id` CHAR(36) NOT NULL UNIQUE,
    `user_id` CHAR(36) NOT NULL,
    `app_iso_code` VARCHAR(32) NOT NULL,
    `refresh_token_hash` CHAR(64) NOT NULL,
    `token_family_id` CHAR(36) NOT NULL,
    `device_fingerprint` VARCHAR(128) NOT NULL,
    `device_name` VARCHAR(128) NULL,
    `platform` ENUM('ANDROID', 'IOS', 'WEB', 'DESKTOP') NOT NULL DEFAULT 'ANDROID',
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT NULL,
    `expires_at` DATETIME NOT NULL,
    `last_activity_at` DATETIME NOT NULL,
    `revoked_at` DATETIME NULL,
    `revoked_reason` VARCHAR(64) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_session_lookup` (`user_id`, `app_iso_code`),
    INDEX `idx_refresh_hash` (`refresh_token_hash`),
    INDEX `idx_token_family` (`token_family_id`),
    CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 9. VERIFICATION & OTP TOKENS
-- =============================================================================
CREATE TABLE `verification_tokens` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` CHAR(36) NOT NULL,
    `target_identity` VARCHAR(191) NOT NULL,
    `channel` ENUM('SMS_PLIVO', 'EMAIL_SMTP') NOT NULL,
    `purpose` ENUM('REGISTRATION_VERIFY', 'LOGIN_MFA', 'PHONE_ATTACH', 'SECURITY_CHANGE') NOT NULL,
    `token_hash` VARCHAR(255) NOT NULL,
    `attempts_made` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `max_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 5,
    `expires_at` DATETIME NOT NULL,
    `is_consumed` TINYINT(1) NOT NULL DEFAULT 0,
    `consumed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_verify_lookup` (`target_identity`, `purpose`, `is_consumed`),
    CONSTRAINT `fk_verify_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 10. SYSTEM ADMINISTRATORS
-- =============================================================================
CREATE TABLE `admin_users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id` CHAR(36) NOT NULL UNIQUE,
    `username` VARCHAR(64) NOT NULL UNIQUE,
    `email` VARCHAR(191) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `admin_role` ENUM('SUPER_ADMIN', 'AUTH_ADMIN', 'APPLICATION_ADMIN', 'AUDITOR') NOT NULL DEFAULT 'AUTH_ADMIN',
    `mfa_enforced` TINYINT(1) NOT NULL DEFAULT 1,
    `status` ENUM('ACTIVE', 'SUSPENDED', 'LOCKED') NOT NULL DEFAULT 'ACTIVE',
    `last_login_at` DATETIME NULL,
    `last_login_ip` VARCHAR(45) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 11. IMMUTABLE SECURITY & AUDIT LOGGING
-- =============================================================================
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_id` CHAR(36) NOT NULL UNIQUE,
    `actor_type` ENUM('SYSTEM', 'SUPER_ADMIN', 'AUTH_ADMIN', 'USER', 'API_CLIENT') NOT NULL,
    `actor_id` VARCHAR(64) NOT NULL,
    `action_name` VARCHAR(64) NOT NULL,
    `resource_type` VARCHAR(32) NOT NULL,
    `resource_id` VARCHAR(64) NOT NULL,
    `diff_before` JSON NULL,
    `diff_after` JSON NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT NULL,
    `request_id` VARCHAR(64) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_action` (`action_name`),
    INDEX `idx_audit_resource` (`resource_type`, `resource_id`),
    INDEX `idx_audit_actor` (`actor_type`, `actor_id`),
    INDEX `idx_audit_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `security_events` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_uuid` CHAR(36) NOT NULL UNIQUE,
    `severity` ENUM('INFO', 'WARNING', 'CRITICAL', 'FATAL_BREACH') NOT NULL,
    `event_type` VARCHAR(64) NOT NULL,
    `subject_user_id` VARCHAR(64) NULL,
    `app_iso_code` VARCHAR(32) NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `details` JSON NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_sec_severity` (`severity`),
    INDEX `idx_sec_type` (`event_type`),
    INDEX `idx_sec_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- SEED DATA: REGISTERED APPS, ORG & MASTER CREDENTIALS
-- =============================================================================

-- Seed Ecosystem Applications
INSERT INTO `applications` (`application_id`, `app_iso_code`, `app_name`, `app_family`, `package_name`, `description`, `status`) VALUES
('app_uuid_poultry', 'BAT-D-LIVE-115', 'My Chicken Manager', 'LIVESTOCK', 'com.betteragritech.mychickenmanager', 'Production, flock, feed, mortality, egg logistics and health management.', 'ACTIVE'),
('app_uuid_dairy', 'BAT-D-LIVE-116', 'My Dairy Manager', 'LIVESTOCK', 'com.betteragritech.mydairymanager', 'Lactation curve, milk bulk tank, herd pedigree, AI insemination and feed.', 'ACTIVE'),
('app_uuid_pig', 'BAT-D-LIVE-117', 'My Pig Manager', 'LIVESTOCK', 'com.betteragritech.mypigmanager', 'Farrowing, litter sizes, sow parity, pork feed conversion and carcass yield.', 'ACTIVE'),
('app_uuid_fish', 'BAT-D-LIVE-118', 'My Fish Manager', 'LIVESTOCK', 'com.betteragritech.myfishmanager', 'Pond water quality, fingerling stocking, dissolved oxygen, and feed conversion.', 'ACTIVE'),
('app_uuid_hive', 'BAT-D-LIVE-119', 'HivePro Manager', 'LIVESTOCK', 'com.betteragritech.hivepromanager', 'Apiary honey supers, queen tracking, nectar flow seasons and comb harvest.', 'ACTIVE'),
('app_uuid_maize', 'BAT-C-CROP-201', 'MaizeMaster', 'CROPS', 'com.betteragritech.maizemaster', 'Precision agronomy, seed planting rates, fertilizer, and crop yield forecasting.', 'ACTIVE'),
('app_uuid_agrolens', 'BAT-A-AI-301', 'AgroLens AI Diagnostic', 'DIAGNOSTICS', 'com.betteragritech.agrolens', 'Computer vision mobile diagnostic for livestock pathogen and crop diseases.', 'ACTIVE'),
('app_uuid_wallet', 'BAT-F-FIN-401', 'My Farm Wallet', 'FINTECH', 'com.betteragritech.myfarmwallet', 'Agribusiness ledger, payroll, invoice financing, and farmer credit scoring.', 'ACTIVE')
ON DUPLICATE KEY UPDATE `app_name` = VALUES(`app_name`);

-- Seed Primary Organization
INSERT INTO `organizations` (`org_id`, `org_code`, `org_name`, `tax_identifier`, `country`, `tier`, `status`) VALUES
('org_bat_corp_01', 'ORG-SUNRISE-001', 'Sunrise Agribusiness Group Ltd', 'TIN-UG-998811', 'UGA', 'COMMERCIAL_ENTERPRISE', 'ACTIVE')
ON DUPLICATE KEY UPDATE `org_name` = VALUES(`org_name`);

-- Seed Master User Accounts (Password for all demo accounts: BetterAgri2026! or 123)
-- BCRYPT Hash: $2y$12$N3q7Y44tPz0wH.qU5eUuO.qC9s5U9f9M0p7E4f5L2u6I3k4O5m9Q. (Valid for password 'BetterAgri2026!')
INSERT INTO `users` (`id`, `user_id`, `ba_account_number`, `account_status`, `first_name`, `last_name`, `password_hash`, `preferred_language`, `country`, `timezone`) VALUES
(1, 'usr_bat_001', 'BA-100201', 'ACTIVE', 'Dr. BetterAgri', 'Specialist', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala'),
(2, 'usr_bat_002', 'BA-100202', 'ACTIVE', 'Samuel', 'Kaggwa', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala'),
(3, 'usr_bat_003', 'BA-100203', 'ACTIVE', 'Grace', 'Achieng', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'KEN', 'Africa/Nairobi'),
(4, 'usr_bat_admin_001', 'BA-100204', 'ACTIVE', 'Dr. Herbert', 'Musoke (System Admin)', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala'),
(5, 'usr_bat_enterprise_001', 'BA-100205', 'ACTIVE', 'Victoria', 'Namubiru (Enterprise Executive)', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala'),
(6, 'usr_bat_premium_001', 'BA-100206', 'ACTIVE', 'Sarah', 'Connor (Premium Producer)', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala'),
(7, 'usr_bat_free_001', 'BA-100207', 'ACTIVE', 'John', 'Doe (Free Tier Farmer)', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'en', 'UGA', 'Africa/Kampala')
ON DUPLICATE KEY UPDATE `first_name` = VALUES(`first_name`);

-- Seed Identities (Email and Phone Aliases for Multi-Identifier Login)
INSERT INTO `identities` (`user_id`, `identity_type`, `identity_value`, `is_verified`, `is_primary`) VALUES
('usr_bat_001', 'EMAIL', 'admin@betteragri.com', 1, 1),
('usr_bat_001', 'PHONE', '+256772100200', 1, 0),
('usr_bat_002', 'EMAIL', 'samuel.kaggwa@sunrise.com', 1, 1),
('usr_bat_002', 'PHONE', '+256701445890', 1, 0),
('usr_bat_003', 'EMAIL', 'grace.achieng@highland.com', 1, 1),
('usr_bat_003', 'PHONE', '+254712334556', 1, 0),
('usr_bat_admin_001', 'EMAIL', 'admin@poultrymanager.com', 1, 1),
('usr_bat_admin_001', 'PHONE', '+256772100200', 1, 0),
('usr_bat_enterprise_001', 'EMAIL', 'enterprise@poultrymanager.com', 1, 1),
('usr_bat_enterprise_001', 'PHONE', '+256772888999', 1, 0),
('usr_bat_premium_001', 'EMAIL', 'premium@poultrymanager.com', 1, 1),
('usr_bat_premium_001', 'PHONE', '+256782555111', 1, 0),
('usr_bat_free_001', 'EMAIL', 'free@poultrymanager.com', 1, 1),
('usr_bat_free_001', 'PHONE', '+256701999888', 1, 0)
ON DUPLICATE KEY UPDATE `is_verified` = VALUES(`is_verified`);

-- Authorize Users for BAT-D-LIVE-115 (My Poultry Manager)
INSERT INTO `user_applications` (`user_id`, `app_iso_code`, `authorization_status`, `default_role`) VALUES
('usr_bat_001', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER'),
('usr_bat_002', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_MANAGER'),
('usr_bat_003', 'BAT-D-LIVE-115', 'AUTHORIZED', 'RESIDENT_VETERINARIAN'),
('usr_bat_admin_001', 'BAT-D-LIVE-115', 'AUTHORIZED', 'SYSTEM_ADMINISTRATOR'),
('usr_bat_enterprise_001', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_EXECUTIVE'),
('usr_bat_premium_001', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER'),
('usr_bat_free_001', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER')
ON DUPLICATE KEY UPDATE `authorization_status` = VALUES(`authorization_status`);

-- Assign Users to Organization
INSERT INTO `organization_users` (`org_id`, `user_id`, `assigned_role`, `membership_status`) VALUES
('org_bat_corp_01', 'usr_bat_001', 'FARM_EXECUTIVE', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_002', 'GENERAL_FARM_MANAGER', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_003', 'RESIDENT_VETERINARIAN', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_admin_001', 'SYSTEM_ADMINISTRATOR', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_enterprise_001', 'FARM_EXECUTIVE', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_premium_001', 'COMMERCIAL_FARM_OWNER', 'ACTIVE'),
('org_bat_corp_01', 'usr_bat_free_001', 'INDEPENDENT_FARMER', 'ACTIVE')
ON DUPLICATE KEY UPDATE `assigned_role` = VALUES(`assigned_role`);

-- Seed Default Auth Super Admin (Initial password: BetterAgriSuperAdmin2026!)
INSERT INTO `admin_users` (`admin_id`, `username`, `email`, `password_hash`, `admin_role`, `status`) VALUES
('admin_master_001', 'superadmin', 'admin@betteragritech.com', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'SUPER_ADMIN', 'ACTIVE')
ON DUPLICATE KEY UPDATE `username` = VALUES(`username`);

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

-- Registered Auth User: farmertest@poultryexample.com
INSERT INTO `users` (`user_id`, `ba_account_number`, `account_status`, `first_name`, `last_name`, `password_hash`, `country`) VALUES ('usr_muata1k0_p8z1', 'BA-497587', 'ACTIVE', 'Test', 'Poultry Farmer', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'UGA') ON DUPLICATE KEY UPDATE `first_name` = VALUES(`first_name`);
INSERT INTO `identities` (`user_id`, `identity_type`, `identity_value`, `is_verified`, `is_primary`) VALUES ('usr_muata1k0_p8z1', 'EMAIL', 'farmertest@poultryexample.com', 1, 1) ON DUPLICATE KEY UPDATE `is_verified` = VALUES(`is_verified`);
INSERT INTO `user_applications` (`user_id`, `app_iso_code`, `authorization_status`, `default_role`) VALUES ('usr_muata1k0_p8z1', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER') ON DUPLICATE KEY UPDATE `authorization_status` = VALUES(`authorization_status`);

-- Registered Auth User: herbertmusoke88@gmail.com
INSERT INTO `users` (`user_id`, `ba_account_number`, `account_status`, `first_name`, `last_name`, `password_hash`, `country`) VALUES ('usr_muaze41s_nies', 'BA-396632', 'ACTIVE', 'Herbert', 'Musoke', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'UGA') ON DUPLICATE KEY UPDATE `first_name` = VALUES(`first_name`);
INSERT INTO `identities` (`user_id`, `identity_type`, `identity_value`, `is_verified`, `is_primary`) VALUES ('usr_muaze41s_nies', 'EMAIL', 'herbertmusoke88@gmail.com', 1, 1) ON DUPLICATE KEY UPDATE `is_verified` = VALUES(`is_verified`);
INSERT INTO `user_applications` (`user_id`, `app_iso_code`, `authorization_status`, `default_role`) VALUES ('usr_muaze41s_nies', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER') ON DUPLICATE KEY UPDATE `authorization_status` = VALUES(`authorization_status`);

-- Registered Auth User: herbertmusoke88@gmail.com
INSERT INTO `users` (`user_id`, `ba_account_number`, `account_status`, `first_name`, `last_name`, `password_hash`, `country`) VALUES ('usr_muaze41s_nies', 'BA-473483', 'ACTIVE', 'Herbert', 'Musoke', '$2y$10$w0uM63o3VqG8mQv3mS6q3OKeMfqcZ3s2.U6PqV61YVp2qK1V2gCqC', 'UGA') ON DUPLICATE KEY UPDATE `first_name` = VALUES(`first_name`);
INSERT INTO `identities` (`user_id`, `identity_type`, `identity_value`, `is_verified`, `is_primary`) VALUES ('usr_muaze41s_nies', 'EMAIL', 'herbertmusoke88@gmail.com', 1, 1) ON DUPLICATE KEY UPDATE `is_verified` = VALUES(`is_verified`);
INSERT INTO `user_applications` (`user_id`, `app_iso_code`, `authorization_status`, `default_role`) VALUES ('usr_muaze41s_nies', 'BAT-D-LIVE-115', 'AUTHORIZED', 'FARM_OWNER') ON DUPLICATE KEY UPDATE `authorization_status` = VALUES(`authorization_status`);