CREATE TABLE IF NOT EXISTS communication_assignment_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    priority INT NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    origin ENUM('internal', 'whatsapp') NULL,
    channel_type ENUM('direct', 'group', 'announcement') NULL,
    integration_connection_id BIGINT UNSIGNED NULL,
    strategy ENUM('round_robin', 'user', 'team_round_robin', 'least_load') NOT NULL DEFAULT 'round_robin',
    assign_user_id BIGINT UNSIGNED NULL,
    team_id BIGINT UNSIGNED NULL,
    last_assigned_user_id BIGINT UNSIGNED NULL,
    escalate_on_unassigned TINYINT(1) NOT NULL DEFAULT 1,
    escalate_on_sla TINYINT(1) NOT NULL DEFAULT 1,
    escalation_user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_comm_assignment_rules_lookup (tenant_id, is_active, priority),
    CONSTRAINT fk_comm_assignment_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_comm_assignment_rules_connection FOREIGN KEY (integration_connection_id) REFERENCES integration_connections(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_rules_user FOREIGN KEY (assign_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_rules_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_rules_last_user FOREIGN KEY (last_assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_rules_escalation_user FOREIGN KEY (escalation_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_assignment_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    previous_user_id BIGINT UNSIGNED NULL,
    assigned_user_id BIGINT UNSIGNED NULL,
    action_type ENUM('auto_assign', 'sla_escalation', 'manual_assign') NOT NULL DEFAULT 'auto_assign',
    reason VARCHAR(160) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_comm_assignment_logs_tenant (tenant_id, created_at),
    CONSTRAINT fk_comm_assignment_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_comm_assignment_logs_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_comm_assignment_logs_rule FOREIGN KEY (rule_id) REFERENCES communication_assignment_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_logs_prev_user FOREIGN KEY (previous_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_logs_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_comm_assignment_logs_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO communication_assignment_rules (
    tenant_id,
    name,
    priority,
    is_active,
    origin,
    channel_type,
    strategy,
    escalate_on_unassigned,
    escalate_on_sla
)
SELECT t.id, 'WhatsApp round robin', 100, 1, 'whatsapp', 'direct', 'round_robin', 1, 1
FROM tenants t
WHERE NOT EXISTS (
    SELECT 1
    FROM communication_assignment_rules r
    WHERE r.tenant_id = t.id
      AND r.origin = 'whatsapp'
      AND r.channel_type = 'direct'
);
