-- Employee-to-Employee Chat System Migration
-- Creates tables for internal team messaging between employees
-- Run with: mysql -u whats_main -p whats_main < database/employee_chat_tables.sql

-- =====================================================
-- EMPLOYEE CONVERSATIONS TABLE
-- Stores conversation metadata (DM or Group)
-- =====================================================
CREATE TABLE IF NOT EXISTS employee_conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL COMMENT 'The client/company user_id who owns these conversations',
    type ENUM('dm', 'group') NOT NULL DEFAULT 'dm' COMMENT 'dm=direct message, group=group chat',
    name VARCHAR(100) DEFAULT NULL COMMENT 'Name for group conversations, NULL for DMs',
    created_by INT NOT NULL COMMENT 'User ID of the employee who created this conversation',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_company_type (company_id, type),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- EMPLOYEE CONVERSATION MEMBERS TABLE
-- Tracks which users (employees or clients) are part of each conversation
-- Note: user_id can be positive (employee ID) or negative (client ID * -1)
-- =====================================================
CREATE TABLE IF NOT EXISTS employee_conversation_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    user_id INT NOT NULL COMMENT 'Positive = employee ID, Negative = client ID (users.id * -1)',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_read_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Last time this user read messages in this conversation',
    FOREIGN KEY (conversation_id) REFERENCES employee_conversations(id) ON DELETE CASCADE,
    -- NO foreign key on user_id - can be employee or client (negative ID)
    UNIQUE KEY unique_member (conversation_id, user_id),
    INDEX idx_user (user_id),
    INDEX idx_conversation (conversation_id),
    INDEX idx_user_last_read (user_id, last_read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- EMPLOYEE MESSAGES TABLE
-- Stores all messages in team conversations
-- Note: sender_id can be positive (employee ID) or negative (client ID * -1)
-- =====================================================
CREATE TABLE IF NOT EXISTS employee_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    sender_id INT NOT NULL COMMENT 'Positive = employee ID, Negative = client ID (users.id * -1)',
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES employee_conversations(id) ON DELETE CASCADE,
    -- NO foreign key on sender_id - can be employee or client (negative ID)
    INDEX idx_conversation (conversation_id),
    INDEX idx_sender (sender_id),
    INDEX idx_conversation_created (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- TEAM CHAT SETTING
-- Add team_chat_enabled setting to chat_assignment_settings table
-- =====================================================

-- First check if chat_assignment_settings table exists and add the column
-- If the table doesn't exist, create a new client_settings table

-- Option 1: Add to existing chat_assignment_settings table (if it exists)
ALTER TABLE chat_assignment_settings
ADD COLUMN IF NOT EXISTS team_chat_enabled TINYINT(1) DEFAULT 1 COMMENT 'Enable/disable employee-to-employee chat feature';

-- Option 2: Create a dedicated client_settings table if needed
-- Uncomment the below if you prefer a separate settings table:
/*
CREATE TABLE IF NOT EXISTS client_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_user_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_setting (client_user_id, setting_key),
    INDEX idx_client (client_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default team_chat_enabled setting for existing clients
INSERT INTO client_settings (client_user_id, setting_key, setting_value)
SELECT id, 'team_chat_enabled', '1' FROM users WHERE role = 'client'
ON DUPLICATE KEY UPDATE setting_key = setting_key;
*/

-- =====================================================
-- HELPER VIEWS (Optional)
-- =====================================================

-- View to get unread message count per conversation per user
-- Uncomment if needed:
/*
CREATE OR REPLACE VIEW employee_unread_counts AS
SELECT
    ecm.user_id,
    ecm.conversation_id,
    COUNT(em.id) as unread_count
FROM employee_conversation_members ecm
LEFT JOIN employee_messages em ON em.conversation_id = ecm.conversation_id
    AND em.created_at > COALESCE(ecm.last_read_at, '1970-01-01')
    AND em.sender_id != ecm.user_id
GROUP BY ecm.user_id, ecm.conversation_id;
*/

-- To run this migration:
-- mysql -u whats_main -p whats_main < database/employee_chat_tables.sql
