-- Migration 1015: Right Sidebar Context Panel Tables
-- Creates tables needed for the right sidebar component
-- Includes: activity_log, user_action_history, user_preferences extensions

-- Activity Log Table
CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    record_type VARCHAR(50) NOT NULL COMMENT 'customer, sales_order, product, etc.',
    record_id INT NOT NULL COMMENT 'ID of the record being tracked',
    action VARCHAR(255) NOT NULL COMMENT 'Description of action taken',
    details TEXT COMMENT 'Additional details in JSON or text format',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_record (record_type, record_id),
    INDEX idx_user (user_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Action History (for Quick Actions tracking)
CREATE TABLE IF NOT EXISTS user_action_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    action_name VARCHAR(100) NOT NULL COMMENT 'Display name of action',
    action_url VARCHAR(255) NOT NULL COMMENT 'URL of action',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_date (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add sidebar actions to page_actions table (if column doesn't exist)
-- Note: page_actions table should already exist from previous migrations
-- This adds sample sidebar actions for customer detail page

INSERT INTO page_actions (page_identifier, action_name, icon, action_type, action_target, group_name, display_order, is_active)
VALUES
('customer_detail', 'Create Sales Order', 'fas fa-shopping-cart', 'link', 'sales-orders/create?customer_id={id}', 'sidebar', 1, 1),
('customer_detail', 'Record Payment', 'fas fa-dollar-sign', 'link', 'payments/create?customer_id={id}', 'sidebar', 2, 1),
('customer_detail', 'View Invoices', 'fas fa-file-invoice', 'link', 'invoices?customer_id={id}', 'sidebar', 3, 1),
('customer_detail', 'Create Opportunity', 'fas fa-handshake', 'link', 'opportunities/create?customer_id={id}', 'sidebar', 4, 1)
ON DUPLICATE KEY UPDATE action_name=action_name;

-- Migration complete
SELECT 'Migration 1015 complete: Right sidebar tables created' as status;