-- ============================================================================
-- AIMS - Agricultural Information and Market Linkage System
-- Enhanced Complete Database Schema - MySQL
-- Student: Musumba Jonathan (21/BCS/010/UMC)
-- ============================================================================

CREATE DATABASE IF NOT EXISTS aims_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE aims_db;

-- ============================================================================
-- 1. USERS TABLE
-- ============================================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE DEFAULT NULL,
    district VARCHAR(50) DEFAULT 'Luuka',
    subcounty VARCHAR(50),
    parish VARCHAR(50),
    village VARCHAR(50),
    language_preference ENUM('en', 'lusoga') DEFAULT 'en',
    profile_picture VARCHAR(255) DEFAULT NULL,
    user_type ENUM('farmer', 'buyer', 'extension_officer', 'admin') DEFAULT 'farmer',
    is_verified TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_phone (phone_number),
    INDEX idx_email (email),
    INDEX idx_user_type (user_type),
    INDEX idx_district (district)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. OTP VERIFICATION TABLE
-- ============================================================================
CREATE TABLE otp_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(15) NOT NULL,
    otp_code VARCHAR(6) NOT NULL,
    purpose ENUM('registration', 'login', 'password_reset') DEFAULT 'registration',
    is_verified TINYINT(1) DEFAULT 0,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone_otp (phone_number, otp_code),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. FARMS TABLE
-- ============================================================================
CREATE TABLE farms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    farm_name VARCHAR(100),
    total_acres DECIMAL(10,2) DEFAULT 0.00,
    location_lat DECIMAL(10,8) DEFAULT NULL,
    location_lng DECIMAL(11,8) DEFAULT NULL,
    soil_type VARCHAR(50) DEFAULT NULL,
    water_source VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 4. ENTERPRISES TABLE
-- ============================================================================
CREATE TABLE enterprises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    farm_id INT NOT NULL,
    enterprise_type ENUM('sugar_cane', 'poultry', 'maize', 'vegetables', 'coffee', 'beans', 'cassava', 'bananas', 'dairy', 'piggery', 'other') NOT NULL,
    enterprise_name VARCHAR(100),
    size_acres DECIMAL(10,2) DEFAULT 0.00,
    quantity INT DEFAULT 0,
    planting_date DATE DEFAULT NULL,
    expected_harvest_date DATE DEFAULT NULL,
    status ENUM('planning', 'planted', 'growing', 'harvesting', 'completed') DEFAULT 'planning',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
    INDEX idx_farm (farm_id),
    INDEX idx_type (enterprise_type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. PRODUCTS TABLE
-- ============================================================================
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seller_id INT NOT NULL,
    enterprise_id INT DEFAULT NULL,
    product_name VARCHAR(100) NOT NULL,
    category ENUM('sugar_cane', 'poultry', 'maize', 'vegetables', 'coffee', 'beans', 'cassava', 'bananas', 'dairy', 'livestock', 'other') NOT NULL,
    description TEXT,
    quantity DECIMAL(10,2) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    price_per_unit DECIMAL(10,2) NOT NULL,
    location VARCHAR(100),
    images TEXT DEFAULT NULL,
    status ENUM('available', 'sold', 'reserved', 'expired') DEFAULT 'available',
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (enterprise_id) REFERENCES enterprises(id) ON DELETE SET NULL,
    INDEX idx_seller (seller_id),
    INDEX idx_category (category),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 6. PRODUCT INQUIRIES TABLE (NEW)
-- ============================================================================
CREATE TABLE product_inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    buyer_id INT NOT NULL,
    message TEXT NOT NULL,
    status ENUM('pending', 'responded', 'closed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_buyer (buyer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 7. ORDERS TABLE (NEW)
-- ============================================================================
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE NOT NULL,
    product_id INT NOT NULL,
    buyer_id INT NOT NULL,
    seller_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    delivery_address TEXT,
    status ENUM('pending', 'confirmed', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
    payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    payment_method VARCHAR(50) DEFAULT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_order_number (order_number),
    INDEX idx_buyer (buyer_id),
    INDEX idx_seller (seller_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. MARKET PRICES TABLE
-- ============================================================================
CREATE TABLE market_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_type VARCHAR(50) NOT NULL,
    market_location VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    source VARCHAR(100) DEFAULT 'Manual Entry',
    price_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_date (product_type, price_date),
    INDEX idx_location (market_location),
    INDEX idx_date (price_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. WEATHER DATA TABLE (NEW)
-- ============================================================================
CREATE TABLE weather_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(100) NOT NULL,
    temperature DECIMAL(5,2),
    humidity DECIMAL(5,2),
    rainfall DECIMAL(10,2),
    weather_condition VARCHAR(50),
    forecast_date DATE NOT NULL,
    is_forecast TINYINT(1) DEFAULT 0,
    source VARCHAR(100) DEFAULT 'API',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_location_date (location, forecast_date),
    INDEX idx_forecast (is_forecast, forecast_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. AGRICULTURAL TIPS TABLE (NEW)
-- ============================================================================
CREATE TABLE agricultural_tips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    category ENUM('crop_management', 'pest_control', 'soil_health', 'irrigation', 'harvesting', 'storage', 'general') NOT NULL,
    crop_type VARCHAR(50) DEFAULT NULL,
    season VARCHAR(50) DEFAULT NULL,
    language ENUM('en', 'lusoga') DEFAULT 'en',
    media_url VARCHAR(255) DEFAULT NULL,
    created_by INT DEFAULT NULL,
    is_published TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_category (category),
    INDEX idx_crop (crop_type),
    INDEX idx_language (language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 11. EXTENSION SERVICES TABLE (NEW)
-- ============================================================================
CREATE TABLE extension_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    officer_id INT NOT NULL,
    service_type ENUM('farm_visit', 'training', 'consultation', 'demonstration') NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    location VARCHAR(100),
    scheduled_date DATE NOT NULL,
    scheduled_time TIME DEFAULT NULL,
    duration_hours INT DEFAULT 2,
    max_participants INT DEFAULT NULL,
    status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (officer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_officer (officer_id),
    INDEX idx_date (scheduled_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 12. SERVICE PARTICIPANTS TABLE (NEW)
-- ============================================================================
CREATE TABLE service_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL,
    user_id INT NOT NULL,
    attendance_status ENUM('registered', 'attended', 'absent') DEFAULT 'registered',
    feedback TEXT DEFAULT NULL,
    rating INT DEFAULT NULL CHECK (rating BETWEEN 1 AND 5),
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_id) REFERENCES extension_services(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_participation (service_id, user_id),
    INDEX idx_service (service_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 13. CHATS TABLE
-- ============================================================================
CREATE TABLE chats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_type ENUM('group', 'one_to_one') NOT NULL,
    chat_name VARCHAR(100) DEFAULT NULL,
    chat_avatar VARCHAR(255) DEFAULT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_type (chat_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 14. CHAT MEMBERS TABLE
-- ============================================================================
CREATE TABLE chat_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('admin', 'member') DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_read_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_membership (chat_id, user_id),
    INDEX idx_user (user_id),
    INDEX idx_chat (chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 15. MESSAGES TABLE
-- ============================================================================
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_type ENUM('text', 'image', 'document', 'audio', 'system') DEFAULT 'text',
    content TEXT NOT NULL,
    media_url VARCHAR(255) DEFAULT NULL,
    is_system_message TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_chat_date (chat_id, created_at),
    INDEX idx_sender (sender_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 16. FRIENDSHIPS TABLE
-- ============================================================================
CREATE TABLE friendships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    friend_id INT NOT NULL,
    status ENUM('pending', 'accepted', 'blocked') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    accepted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_friendship (user_id, friend_id),
    INDEX idx_user_status (user_id, status),
    INDEX idx_friend (friend_id),
    CHECK (user_id != friend_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 17. TRANSACTIONS TABLE
-- ============================================================================
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    enterprise_id INT DEFAULT NULL,
    transaction_type ENUM('income', 'expense') NOT NULL,
    category VARCHAR(50),
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    transaction_date DATE NOT NULL,
    receipt_url VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (enterprise_id) REFERENCES enterprises(id) ON DELETE SET NULL,
    INDEX idx_user_date (user_id, transaction_date),
    INDEX idx_type (transaction_type),
    INDEX idx_enterprise (enterprise_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 18. NOTIFICATIONS TABLE
-- ============================================================================
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'danger') DEFAULT 'info',
    icon VARCHAR(50) DEFAULT 'bell',
    link VARCHAR(255) DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 19. AI CONVERSATIONS TABLE
-- ============================================================================
CREATE TABLE ai_conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_id VARCHAR(100) DEFAULT NULL,
    message TEXT NOT NULL,
    response TEXT NOT NULL,
    context_data JSON DEFAULT NULL,
    language VARCHAR(10) DEFAULT 'en',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_session (session_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 20. PRICE ALERTS TABLE
-- ============================================================================
CREATE TABLE price_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_type VARCHAR(50) NOT NULL,
    target_price DECIMAL(10,2) NOT NULL,
    alert_condition ENUM('above', 'below') NOT NULL,  -- Renamed to avoid reserved keyword
    is_active TINYINT(1) DEFAULT 1,
    last_triggered TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_active (user_id, is_active),
    INDEX idx_product (product_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 21. ACTIVITY LOG TABLE
-- ============================================================================
CREATE TABLE activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, created_at),
    INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 22. FAVORITES/WISHLIST TABLE (NEW)
-- ============================================================================
CREATE TABLE favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_favorite (user_id, product_id),
    INDEX idx_user (user_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 23. REVIEWS/RATINGS TABLE (NEW)
-- ============================================================================
CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reviewer_id INT NOT NULL,
    reviewed_user_id INT NOT NULL,
    order_id INT DEFAULT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_reviewer (reviewer_id),
    INDEX idx_reviewed (reviewed_user_id),
    INDEX idx_rating (rating)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 24. REPORTS TABLE (NEW)
-- ============================================================================
CREATE TABLE reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reporter_id INT NOT NULL,
    report_type ENUM('product', 'user', 'message', 'other') NOT NULL,
    reported_item_id INT NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending', 'reviewing', 'resolved', 'dismissed') DEFAULT 'pending',
    admin_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_reporter (reporter_id),
    INDEX idx_status (status),
    INDEX idx_type (report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 25. SYSTEM SETTINGS TABLE (NEW)
-- ============================================================================
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SAMPLE DATA - FOR TESTING
-- ============================================================================

-- Insert Admin User (password: admin123)
INSERT INTO users (phone_number, password_hash, full_name, email, user_type, is_verified) VALUES
('+256787842061', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyB0.jRpWQXO', 'Musumba Jonathan', 'jmprossy@gmail.com', 'admin', 1);

-- Insert Sample Extension Officer
INSERT INTO users (phone_number, password_hash, full_name, district, user_type, is_verified) VALUES
('+256700000001', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyB0.jRpWQXO', 'John Mwesigwa', 'Luuka', 'extension_officer', 1);

-- Insert Sample Market Prices
INSERT INTO market_prices (product_type, market_location, price, unit, source, price_date) VALUES
('Sugar Cane', 'Kakira Sugar - Direct', 180000, 'ton', 'Direct Contact', CURDATE()),
('Sugar Cane', 'Mayuge Sugar - Direct', 175000, 'ton', 'Direct Contact', CURDATE()),
('Sugar Cane', 'Local Middlemen', 120000, 'ton', 'Market Survey', CURDATE()),
('Eggs', 'Luuka Town', 12000, 'tray', 'Market Survey', CURDATE()),
('Chicken', 'Luuka Market', 15000, 'bird', 'Market Survey', CURDATE()),
('Tomatoes', 'Kampala Market', 3500, 'kg', 'Market Survey', CURDATE()),
('Cabbages', 'Iganga Market', 1500, 'piece', 'Market Survey', CURDATE()),
('Maize', 'Luuka Market', 1800, 'kg', 'Market Survey', CURDATE()),
('Beans', 'Jinja Market', 4000, 'kg', 'Market Survey', CURDATE()),
('Coffee', 'Jinja Coffee Mill', 8500, 'kg', 'Market Survey', CURDATE()),
('Bananas', 'Iganga Market', 25000, 'bunch', 'Market Survey', CURDATE());

-- Insert Sample Agricultural Tips
INSERT INTO agricultural_tips (title, content, category, crop_type, language) VALUES
('Best Time to Plant Maize', 'Plant maize at the beginning of the rainy season (March-April or September-October). Ensure soil moisture is adequate before planting.', 'crop_management', 'maize', 'en'),
('Sugar Cane Pest Control', 'Monitor for stem borers and apply appropriate pesticides early. Keep the field clean and remove infected plants immediately.', 'pest_control', 'sugar_cane', 'en'),
('Poultry Disease Prevention', 'Vaccinate birds regularly, maintain clean housing, provide clean water, and isolate sick birds immediately.', 'pest_control', 'poultry', 'en');

-- Insert System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('site_name', 'AIMS - Agricultural Information & Market Linkage', 'string', 'Application name'),
('max_upload_size', '5242880', 'number', 'Maximum file upload size in bytes (5MB)'),
('enable_notifications', 'true', 'boolean', 'Enable push notifications'),
('ai_model', 'claude-sonnet-4-5', 'string', 'AI model to use for assistance'),
('price_alert_frequency', '86400', 'number', 'Price alert check frequency in seconds (24 hours)');

-- ============================================================================
-- STORED PROCEDURES
-- ============================================================================

-- Get Dashboard Statistics
DELIMITER //
CREATE PROCEDURE GetDashboardStats(IN userId INT)
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM enterprises WHERE farm_id IN (SELECT id FROM farms WHERE user_id = userId)) as total_enterprises,
        (SELECT COALESCE(SUM(total_acres), 0) FROM farms WHERE user_id = userId) as total_acres,
        (SELECT COUNT(*) FROM products WHERE seller_id = userId AND status = 'available') as active_listings,
        (SELECT COUNT(*) FROM notifications WHERE user_id = userId AND is_read = 0) as unread_notifications,
        (SELECT COALESCE(SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END), 0) FROM transactions WHERE user_id = userId AND MONTH(transaction_date) = MONTH(CURDATE())) as monthly_income,
        (SELECT COALESCE(SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END), 0) FROM transactions WHERE user_id = userId AND MONTH(transaction_date) = MONTH(CURDATE())) as monthly_expenses,
        (SELECT COUNT(*) FROM orders WHERE buyer_id = userId AND status = 'pending') as pending_orders,
        (SELECT COUNT(*) FROM favorites WHERE user_id = userId) as wishlist_count;
END //
DELIMITER ;

-- Get Unread Message Count
DELIMITER //
CREATE PROCEDURE GetUnreadMessageCount(IN userId INT)
BEGIN
    SELECT 
        c.id as chat_id,
        c.chat_name,
        c.chat_type,
        COUNT(m.id) as unread_count,
        MAX(m.created_at) as last_message_time
    FROM chats c
    INNER JOIN chat_members cm ON c.id = cm.chat_id
    LEFT JOIN messages m ON c.id = m.chat_id 
        AND m.created_at > COALESCE(cm.last_read_at, '2000-01-01')
        AND m.sender_id != userId
    WHERE cm.user_id = userId
    GROUP BY c.id, c.chat_name, c.chat_type
    HAVING unread_count > 0
    ORDER BY last_message_time DESC;
END //
DELIMITER ;

-- Get User Rating
DELIMITER //
CREATE PROCEDURE GetUserRating(IN userId INT)
BEGIN
    SELECT 
        COALESCE(AVG(rating), 0) as average_rating,
        COUNT(*) as total_reviews
    FROM reviews
    WHERE reviewed_user_id = userId;
END //
DELIMITER ;

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Update chat timestamp when new message is sent
DELIMITER //
CREATE TRIGGER update_chat_timestamp 
AFTER INSERT ON messages
FOR EACH ROW
BEGIN
    UPDATE chats SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.chat_id;
END //
DELIMITER ;

-- Notify friend request
DELIMITER //
CREATE TRIGGER notify_friend_request
AFTER INSERT ON friendships
FOR EACH ROW
BEGIN
    IF NEW.status = 'pending' THEN
        INSERT INTO notifications (user_id, title, message, type, icon, link)
        SELECT 
            NEW.friend_id,
            'New Friend Request',
            CONCAT((SELECT full_name FROM users WHERE id = NEW.user_id), ' sent you a friend request'),
            'info',
            'person-plus',
            '/friends/requests.php'
        FROM DUAL;
    END IF;
END //
DELIMITER ;

-- Notify new order
DELIMITER //
CREATE TRIGGER notify_new_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Notify seller
    INSERT INTO notifications (user_id, title, message, type, icon, link)
    VALUES (
        NEW.seller_id,
        'New Order Received',
        CONCAT('You have a new order #', NEW.order_number),
        'success',
        'shopping-cart',
        CONCAT('/orders/view.php?id=', NEW.id)
    );
    
    -- Notify buyer
    INSERT INTO notifications (user_id, title, message, type, icon, link)
    VALUES (
        NEW.buyer_id,
        'Order Placed Successfully',
        CONCAT('Your order #', NEW.order_number, ' has been placed'),
        'success',
        'check-circle',
        CONCAT('/orders/view.php?id=', NEW.id)
    );
END //
DELIMITER ;

-- Notify product inquiry
DELIMITER //
CREATE TRIGGER notify_product_inquiry
AFTER INSERT ON product_inquiries
FOR EACH ROW
BEGIN
    INSERT INTO notifications (user_id, title, message, type, icon, link)
    SELECT 
        p.seller_id,
        'New Product Inquiry',
        CONCAT((SELECT full_name FROM users WHERE id = NEW.buyer_id), ' asked about your product'),
        'info',
        'message-circle',
        CONCAT('/products/view.php?id=', NEW.product_id)
    FROM products p
    WHERE p.id = NEW.product_id;
END //
DELIMITER ;

-- Update product views
DELIMITER //
CREATE TRIGGER increment_product_views
AFTER INSERT ON activity_log
FOR EACH ROW
BEGIN
    IF NEW.action = 'view_product' THEN
        UPDATE products 
        SET views = views + 1 
        WHERE id = CAST(SUBSTRING_INDEX(NEW.description, ':', -1) AS UNSIGNED);
    END IF;
END //
DELIMITER ;

-- ============================================================================
-- VIEWS
-- ============================================================================

-- View for active products with seller info
CREATE VIEW v_active_products AS
SELECT 
    p.*,
    u.full_name as seller_name,
    u.phone_number as seller_phone,
    u.village,
    u.subcounty,
    u.district,
    (SELECT AVG(rating) FROM reviews WHERE reviewed_user_id = u.id) as seller_rating,
    (SELECT COUNT(*) FROM reviews WHERE reviewed_user_id = u.id) as seller_review_count
FROM products p
INNER JOIN users u ON p.seller_id = u.id
WHERE p.status = 'available';

-- View for user statistics
CREATE VIEW v_user_statistics AS
SELECT 
    u.id as user_id,
    u.full_name,
    u.user_type,
    COUNT(DISTINCT f.id) as total_farms,
    COUNT(DISTINCT e.id) as total_enterprises,
    COUNT(DISTINCT p.id) as total_products,
    COUNT(DISTINCT CASE WHEN fr.status = 'accepted' THEN fr.id END) as total_friends,
    COALESCE(AVG(r.rating), 0) as average_rating,
    COUNT(DISTINCT r.id) as total_reviews
FROM users u
LEFT JOIN farms f ON u.id = f.user_id
LEFT JOIN enterprises e ON f.id = e.farm_id
LEFT JOIN products p ON u.id = p.seller_id
LEFT JOIN friendships fr ON (u.id = fr.user_id OR u.id = fr.friend_id)
LEFT JOIN reviews r ON u.id = r.reviewed_user_id
GROUP BY u.id, u.full_name, u.user_type;

-- View for recent market prices
CREATE VIEW v_recent_market_prices AS
SELECT 
    product_type,
    market_location,
    price,
    unit,
    source,
    price_date,
    ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY price_date DESC) as rn
FROM market_prices
WHERE price_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- View for upcoming extension services
CREATE VIEW v_upcoming_services AS
SELECT 
    es.*,
    u.full_name as officer_name,
    u.phone_number as officer_phone,
    COUNT(sp.id) as registered_participants
FROM extension_services es
INNER JOIN users u ON es.officer_id = u.id
LEFT JOIN service_participants sp ON es.id = sp.service_id
WHERE es.status = 'scheduled' 
    AND es.scheduled_date >= CURDATE()
GROUP BY es.id;

-- View for order summary
CREATE VIEW v_order_summary AS
SELECT 
    o.*,
    p.product_name,
    p.category,
    buyer.full_name as buyer_name,
    buyer.phone_number as buyer_phone,
    seller.full_name as seller_name,
    seller.phone_number as seller_phone
FROM orders o
INNER JOIN products p ON o.product_id = p.id
INNER JOIN users buyer ON o.buyer_id = buyer.id
INNER JOIN users seller ON o.seller_id = seller.id;

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Additional composite indexes for common queries
CREATE INDEX idx_messages_chat_sender ON messages(chat_id, sender_id);
CREATE INDEX idx_products_category_status ON products(category, status);
CREATE INDEX idx_transactions_user_type_date ON transactions(user_id, transaction_type, transaction_date);
CREATE INDEX idx_notifications_user_read_created ON notifications(user_id, is_read, created_at DESC);
CREATE INDEX idx_orders_buyer_status ON orders(buyer_id, status);
CREATE INDEX idx_orders_seller_status ON orders(seller_id, status);

-- ============================================================================
-- EVENTS FOR AUTOMATED TASKS
-- ============================================================================

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;

-- Auto-expire old products (runs daily)
DELIMITER //
CREATE EVENT IF NOT EXISTS expire_old_products
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
BEGIN
    UPDATE products 
    SET status = 'expired'
    WHERE expires_at IS NOT NULL 
        AND expires_at < NOW() 
        AND status = 'available';
END //
DELIMITER ;

-- Clean old OTP codes (runs hourly)
DELIMITER //
CREATE EVENT IF NOT EXISTS clean_expired_otps
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM otp_verifications 
    WHERE expires_at < NOW();
END //
DELIMITER ;

-- Check price alerts (runs every 6 hours)
DELIMITER //
CREATE EVENT IF NOT EXISTS check_price_alerts
ON SCHEDULE EVERY 6 HOUR
DO
BEGIN
    -- Insert notifications for triggered price alerts
    INSERT INTO notifications (user_id, title, message, type, icon, link)
    SELECT 
        pa.user_id,
        'Price Alert Triggered',
        CONCAT(pa.product_type, ' is now ', 
               IF(pa.condition = 'above', 'above', 'below'), 
               ' UGX ', FORMAT(pa.target_price, 0)),
        'warning',
        'trending-up',
        '/market/prices.php'
    FROM price_alerts pa
    INNER JOIN (
        SELECT product_type, AVG(price) as avg_price
        FROM market_prices
        WHERE price_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        GROUP BY product_type
    ) mp ON pa.product_type = mp.product_type
    WHERE pa.is_active = 1
        AND (
            (pa.condition = 'above' AND mp.avg_price > pa.target_price)
            OR (pa.condition = 'below' AND mp.avg_price < pa.target_price)
        )
        AND (pa.last_triggered IS NULL OR pa.last_triggered < DATE_SUB(NOW(), INTERVAL 24 HOUR));
    
    -- Update last triggered timestamp
    UPDATE price_alerts pa
    INNER JOIN (
        SELECT product_type, AVG(price) as avg_price
        FROM market_prices
        WHERE price_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        GROUP BY product_type
    ) mp ON pa.product_type = mp.product_type
    SET pa.last_triggered = NOW()
    WHERE pa.is_active = 1
        AND (
            (pa.condition = 'above' AND mp.avg_price > pa.target_price)
            OR (pa.condition = 'below' AND mp.avg_price < pa.target_price)
        );
END //
DELIMITER ;

-- ============================================================================
-- GRANT PERMISSIONS (Optional - for production)
-- ============================================================================

-- CREATE USER 'aims_user'@'localhost' IDENTIFIED BY 'AiMS_2025_SecurePass!';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON aims_db.* TO 'aims_user'@'localhost';
-- GRANT EXECUTE ON aims_db.* TO 'aims_user'@'localhost';
-- FLUSH PRIVILEGES;

-- ============================================================================
-- DATABASE SETUP COMPLETE
-- Version: 1.0
-- Last Updated: January 2025
-- ============================================================================

-- Verify setup
SELECT 'Database setup completed successfully!' as status;
SELECT COUNT(*) as total_tables FROM information_schema.tables WHERE table_schema = 'aims_db';
SELECT COUNT(*) as total_procedures FROM information_schema.routines WHERE routine_schema = 'aims_db' AND routine_type = 'PROCEDURE';
SELECT COUNT(*) as total_triggers FROM information_schema.triggers WHERE trigger_schema = 'aims_db';
SELECT COUNT(*) as total_views FROM information_schema.views WHERE table_schema = 'aims_db';
SELECT COUNT(*) as total_events FROM information_schema.events WHERE event_schema = 'aims_db'